[EXCEL/엑셀] INDEX, SMALL 함수 조건을 충족하는 값의 목록 찾기

INDEX 함수의 활용 4번째!

 

SMALL 함수와 같이 사용해서 조건에 맞는 값을 목록 형태로 출력하기!!

 

이 방법의 공식은

{=INDEX(출력할데이터의범위,SMALL(IF(조건,ROW(조건을충족할때출력할데이터범위)),ROW(열번호)))}

 

이 공식 역시 배열 수식이므로 Ctrl + Shift + Enter로 입력해야한다.

 

아래의 예제 파일로 본격적으로 설명 시작!!

 

INDEX,SMALL_1.xlsx

0.02MB

 

위 파일을 열면 Sheet1 A:B에 아래와 같이 데이터가 있다.

Sheet1 데이터

 

이 데이터를 가지고 오른쪽 E:F에 학생명을 입력하면 수강과목 목록이 출력되게 하는 수식을 만들어 보도록 하자.

 

위의 데이터에 공식을 넣어보면

 

{=INDEX(출력할데이터의범위,SMALL(IF(조건,ROW(조건을충족할때출력할데이터범위)),ROW(열번호)))}

 

F2=INDEX($B$1:$B$75,SMALL(IF($A$1:$A$75=$E$2,ROW($B$1:$B$75)),ROW(A1))) .. F2에 출력할 데이터 범위 입력

 

F2=INDEX($B$1:$B$75,SMALL(IF($A$1:$A$75=$E$2,ROW($B$1:$B$75)),ROW(A1))) .. IF문 첫 인수, 조건은 학생명 목록에서 E2의 값과 맞는 데이터를 찾는것

 

F2=INDEX($B$1:$B$75,SMALL(IF($A$1:$A$75=$E$2,ROW($B$1:$B$75)),ROW(A1))) .. 조건이 맞으면 출력할 데이터 {1;2;3;4;5;6;7;...}

 

>> SMALL의 첫번째 인수 : IF($A$1:$A$75=$E$2,ROW($B$1:$B$75)) 의 값은 {FALSE;FALSE;3;4;FALSE;FALSE;.....}

 

F2=INDEX($B$1:$B$75,SMALL(IF($A$1:$A$75=$E$2,ROW($B$1:$B$75)),ROW(A1))) .. SMALL의 두번째 인수 ROW(A1)의 값은 1, 첫번째 인수에서 1번째로 작은 값을 출력 = 3, ROW의 인수는 A1가 아니더라도 첫행의 아무데나 참조를 걸어줘도 좋다. Z1, X1 등등

 

F2=INDEX($B$1:$B$75,SMALL(IF($A$1:$A$75=$E$2,ROW($B$1:$B$75)),ROW(A1)))

>> $B$1:$B$75 범위에서 3번째의 값을 출력 "고3_수학_김OO"

 

Sheet2 데이터

 

Sheet2의 데이터는 첫 행부터가 아닌 5행부터 시작하게 설정했다. 

이렇게 해서 F2의 값을 찾아보면 해당 공식을 좀 더 잘 이해할 수 있을것이다.

 

{=INDEX(출력할데이터의범위,SMALL(IF(조건,ROW(조건을충족할때출력할데이터범위)),ROW(열번호)))}

 

■ 방법1

F2=INDEX($B$1:$B$79,SMALL(IF($A$5:$A$79=$E$2,ROW($B$5:$B$79)),ROW(A1)))

 

Sheet1과 비교하면 IF문의 범위만 변경되었다.

SMALL은 IF를 충족하는 1번째로 작은 값을 출력하는 것이기 때문에 변경되는 부분은 없고

 

IF($A$5:$A$79=$E$2,ROW($B$5:$B$79))

 

조건 $A$5:$A$79=$E$2 .. {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;.....}

IF가 TRUE면 ROW($B$5:$B$79) {5;6;7;8;9;10;......} ROW(B5)부터니까 데이터의 첫줄이라고 해도 5~79로 출력됨

 

SMALL문

{FALSE;FALSE;7;8;FALSE;FALSE;.....}의 1번째로 작은 수를 출력하니까 7로 출력

 

INDEX($B$1:$B$79,7) = "고3_수학_김OO" .. B1부터의 범위에서 7의 값 : B7의 값을 출력

 

■ 방법2

F2=INDEX($B$5:$B$79,SMALL(IF($A$5:$A$79=$E$2,ROW($B$1:$B$75)),ROW(A1)))

 

IF($A$5:$A$79=$E$2,ROW($B$1:$B$75))

 

조건 $A$5:$A$79=$E$2 .. {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;.....}

IF가 TRUE면 ROW($B$1:$B$75) {1;2;3;4;5;6;......} ROW(B1)부터니까 1부터 75까지 출력

 

SMALL문

{FALSE;FALSE;3;4;FALSE;FALSE;.....}의 1번째로 작은 수를 출력하니까 3으로 출력

 

INDEX($B$5:$B$79,3) = "고3_수학_김OO" .. B5부터의 범위에서 3의 값 : B7의 값을 출력

 

 

다음은 INDEX, SMALL로 다중 조건을 충족하는 값의 목록 구하기~

 

이건 다음에~