엑셀_ Index_Indirect_Small_배열함수_조건에 맞는 값 순서대로 추출하기

좀 난이도가 있는 내용이긴 한데... 

아래 내용 읽어보시고, 첨부 파일에서 몇 번 연습해보시면 금방 따라하실 수 있을 겁니다.

 

조건에 만족하는 값을 순서대로가져오기.xls

0.04MB

 

첨부 파일을 보시면 세 사람이 각각의 날짜에 치른 시험 성적이 있다. G1 셀에 대상자를 선택하여, 그의 시험 성적을 순서대로 추출하려면 어떻게 해야 할까?

​

​

 

H3 에 사용된 (배열) 수식은 다음과 같다.

​

{=INDEX($D$3:$D$14,SMALL(IF($B$3:$B$14=$G$1,ROW(INDIRECT("1:"&COUNTA($D$3:$D$14)))),F3))}

 

Index 함수가 나왔으니, 참조된 범위 (여기서는 시험 점수 값이 들어있는 D3:D14)에서 Small 함수의 결과로 나올 n 번째 행의 값을 추출하라는 수식임을 알 수 있다. 따라서, 대상자를 Minsoo 로 선택했다면, small 함수 결과 값이 1, 4, 7, 10 이 나와야 Minsoo 의 시험성적/ 날짜가 제대로 나오겠지?

 

참고로, Small 함수는 아래의 간단한 예시처럼 지정된 범위나 배열에서 n 번째로 작은 값을 돌려 달라는 함수이다.

 

​

{=INDEX($D$3:$D$14,SMALL(IF($B$3:$B$14=$G$1,ROW(INDIRECT("1:"&COUNTA($D$3:$D$14)))),F3))}

​

다시 이 복잡한 수식의 설명으로 돌아가보자. 사실, 배열 함수는 IF 함수 이후의 수식을 위해 사용된 것이다.

 

일단, 머릿속으로 B3:B14에 있는 이름들이 하나씩, G1 에 선택된 이름과 동일한지 검사한다고 생각해보자. 현재 선택된 이름이 Minsoo 이므로, B3는 맞고(True), B4 는 틀리고 (False), B5 도 틀리고 (False), B6는 맞고(True)…. 이렇게 B14 까지…

 

앞의 로직 검사에서 True 값이 나오면 ROW(INDIRECT("1:"&COUNTA($D$3:$D$14))) 식 계산으로 넘어간다. 

Indirect 함수 때문에 복잡하게 보이지만, 결국 {1,2,3,4,5,6,7,8,9,10,11,12} 라는 배열을 만들기 위해 row(1:12) 이라는 수식을 사용한 것이다. 그리고, 나중에 데이터 값이 많아질 경우를 대비하여 (자동화를 위하여) counta 함수를 사용한 것이다. 

 

해보면 알겠지만, 수식에 직접 Row(“1:”& COUNTA($D$3:$D$14)) 을 입력하는 것은 불가능하다.

따라서, 텍스트 문자열로 지정된 참조의 값을 반환해주는 Indirect 를 활용하여ROW(INDIRECT("1:"&COUNTA($D$3:$D$14))) 의 형태를 갖게 된 것이다.  

 

사실, 위의 Indirect 함수의 쓰임새와 결과는 통상적으로 볼 수 있는 것이 아니다. 원래라면 아래의 간단한 예시처럼 텍스트로 지정된 참조 영역이 가지고 있는 "값"을 돌려 줘야 하는데… 여기서는 그냥 입력된 텍스트를 돌려줘 버린다. 배열 함수에서 쓸 수 있는 Row-Indirect 함수의 조합이라고 생각하면 될까? - 엑셀 프로그래머들은 왜 이런 식으로 작동하는지 알겠지...

 

​ 

어찌 되었건, 앞에 로직에서 True 값을 받은 녀석들 (Minsoo 가 선택된 현재, B3:B14 범위에 있는 모든 Minsoo들), row 함수를 통해 1, 4, 7, 10 을 돌려주게 되고… 나머지 False 값을 받은 녀석들을 False 값을 그대로 반환하여… 최종적으로 다음의 배열이 나오게 된다.

​

{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE;FALSE;10;FALSE;FALSE}

​

여기서 Small 함수를 통해, 위 배열에서 제일 작은 값 1, 두번째 작은 값 4, 세번째 작은 값 7 이 나와버리니.. 시험 날짜도, 점수도 순서대로 추출될 수밖에...