1. 배열함수 예제
이번에는 여러 엑셀 함수 (Index, Indirect, Small, Counta 등)을 조합한 배열함수로, 지정한 조건에 맞는 값을 순서대로 추출하는 방법을 한 번 보겠습니다. 아래 첨부 파일을 열어 보시면,
아래와 같은 데이터와 테이블을 확인하실 수 있습니다. 데이터를 잘 보시면, 세 사람이 각각의 날짜에 치른 시험 성적들을 정리해 놓은 것입니다. 만약 G1 셀에 응시자를 선택하면, 선택한 사람의 시험 성적을 응시날짜순으로 추출하려면 어떻게 해야 할까요?
2. 배열함수 설명
예제 파일에서 셀 H3 에 사용된 배열함수 수식은 아래와 같습니다.
{=INDEX($D$3:$D$14,SMALL(IF($B$3:$B$14=$G$1,ROW(INDIRECT(“1:”&COUNTA($D$3:$D$14)))),F3))}
수식 입력하고 Ctrl + Shift + Enter 눌러서 배열함수로 만들어 주는 거 까먹지 마시구요!
3. Index 함수와 Small 함수 조합
배열함수 수식 제일 앞에 보시면 Index 함수를 사용했으며, 이 Index 함수에서 참조된 범위 (시험 점수 값이 들어있는 D3:D14)에서 Small 함수의 결과값으로로 나올 n 번째 행의 값을 가지고 오라는 명령임을 알 수 있습니다. 예를 들어, 위에 스크린 샷에서처럼 응시자를 Minsoo 로 선택했다면, small 함수 결과 값이 1, 4, 7, 10 이 나와야 Minsoo 의 시험성적/ 날짜가 제대로 나오겠지요? D3:D14 범위에서 Minsoo 의 시험성적/ 날짜가 들어가 있는 행은 첫번째, 네번째, 일곱번째, 열번째 이니까요.
여기까지 이해 되셨나요?
Small 함수는 아래 예시에서 보여드리는 것 처럼, 지정된 범위나 배열에서 n 번째로 작은 값을 반환하는 함수입니다. B2:B6 범위에서 2번째로 작은 숫자는 20이겠죠.
4. IF 함수 이후의 배열함수 수식 설명
{=INDEX($D$3:$D$14,SMALL(IF($B$3:$B$14=$G$1,ROW(INDIRECT(“1:”&COUNTA($D$3:$D$14)))),F3))}
앞에 Index 와 Small 함수가 어떻게 작동하는지는 이해하셨다면, 이제 Small 함수를 구성하고 있는 인수들을 살펴봅시다. 사실 이 배열함수 수식은, IF 함수 이후의 수식에서 배열을 만들기 위해 사용된 것입니다.
일단, B3:B14에 있는 이름들이 하나씩 순서대로, G1 에 선택된 이름과 동일한지 검사한다고 생각해봅시다. 현재 선택된 이름이 Minsoo 이므로, B3는 맞고(True), B4 는 틀리고(False), B5 도 틀리고(False), B6는 맞고(True)…. 이렇게 B14 까지 테스트가 진행됩니다.
If 함수의 작동 원리를 이해하시는 분은 아시겠지만, 해당 If 함수의 로직 검사에서 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) 이라는 수식이 필요했던 것입니다. 다만, 나중에 데이터 행이 14행 이상으로 많아질 경우를 대비하여 (자동화를 위해서) 데이터 의 마지막 행의 숫자를 넣어줄 counta 함수를 사용했던 것입니다.
또한 Row 함수는 인수로 무조건 범위를 넣어야 하기 때문에 텍스트 문자열로 지정된 참조의 값을 반환해주는 Indirect 함수를 활용하여 ROW(INDIRECT(“1:”&COUNTA($D$3:$D$14))) 의 형태를 최종적으로 갖게 된 것입니다. 이 부분이 조금 어려울 수 있겠지만, 쉽게 말해 Indirect 함수를 통해 1:12의 범위를 인수로 만들어 Row 함수에 넣어주었다… 라고 이해하시면 되겠습니다.
그리하여, 앞에 로직에서 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 함수의 마지막 인수 – 위에 수식에서 F3 로 되어 있는 “시험 순서를 참조하는” 부분 – 위 배열에서 제일 작은 값 1, 두번째 작은 값 4, 세번째 작은 값 7 이 나와버리니.. 시험 날짜와 점수가 시험 순서대로 추출되게 되었습니다.
사실 이 정도의 배열함수면 엑셀 함수 중에서도 상당히 난이도가 있다고 말할 수 있겠는데요. 그래도 포기 하지 마시고, 첨부 파일 예제를 찬찬히 뜯어 보시면서 공부하시기를 바랍니다!
배열함수 이외에 다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!