이번 시간은 엑셀의 배열함수에 대해서 알아보려고 한다. 나는 배열함수가 '함수의 끝판왕' 이라고 생각한다. '배열' 이라는 단어부터 생소한 여러분께 배열함수에 대해서 소개하려고 한다.

먼저 이 글을 읽기 위한 사전 지식을 쓰려고 한다. 먼저 함수를 단 한번도 사용해 본적이 없으신 분은 이 글을 닫길 바랍니다. sum, sumif, sumifs 중 두 개 이상 사용해 보신 분들이 읽었으면 좋겠다. 앞으로 저 함수들과 비교해가며 진행해 나갈 계획이다. 물론 sum 말고 average 같은 비슷한 계열의 함수여도 상관없다.

sumifs 의 한계

먼저 sum, sumif, sumifs 함수를 복습해보자.

  • SUM: 영역의 값을 더한다.
  • SUMIF: 영역에서 조건에 맞는 값을 더한다.
  • SUMIFS: 영역에서 여러 조건을 모두 만족하는 값을 더한다.

초점을 맞추고 싶은 함수는 sumifs 이다. 과연 우리는 sumifs 함수로 모든 합을 다 구해낼 수 있을까? '3학년 학생의 수학 점수를 모두 구하세요' 와 같은 문제만 주어지면 좋겠지만 실무는 그렇지 않다. 조금만 복잡해져도 sumifs는 처리할 수 없다. 아래 예제를 보자.

붉은색으로 처리된 부분이 함수를 쓴 부분이다. 언뜻 봐도 알 수 있지만 한번 더 각 셀의 의미에 대해서 설명해본다.

  • A: 수범이의 수학 점수 합계를 구하라.
  • B: 근영이의 평균 점수를 구하라.
  • C: 학생들의 과학 평균 점수를 구하라.
  • D: 슬기의 2016-05-20 시험 점수의 합계를 구하라.
  • E: 승현이의 5월 평균 점수를 구하라.

E를 제외한 A~D 까지는 큰 무리 없이 답을 구했으면 한다. 모두 sumifs, averageifs 함수로 구할 수 있는 값들이다. 그런데 E 는 좀 다르다. sumifs, averageifs 함수의 조건이라고 하면 보통 = (Equal) 을 의미한다. 물론 부등호도 쓸 수 있다. 하지만 E 값은 시험 월을 조건으로 줘야 한다. 조건영역(criteria_range)에 특별한 조건을 줘야 한다는 뜻이다. 안타깝게도 이런 기능은 averageifs 에서 지원하지 않는다.

=averageifs ( D2:D25, month ( A2:A25 ), G15 )
=averageifs ( 점수영역, month ( 시험일영역 ), 조건월:5 )

이 수식을 보면 5월 시험 점수의 평균을 구해줄 것 같다. 안타깝게도 이런 수식은 유효하지 않다. (미래의 엑셀은 지원해줄지도 모른다.) 조건범위(criteria_range)는 셀영역이 들어가야 한다. month(시험일영역) 은 셀영역이 아니라 함수의 결과이다. 이런 상황에서 사용할 수 있는 기법이 바로 배열함수이다.

배열함수

왜 배열(array)인지 너무 깊게 고민하지 말자. 일단 E값을 구하면서 배열함수를 어떻게 사용하는지 살펴보자. 승현이의 5월 점수 평균은 아직은 어려우니 전체 학생의 5월 점수 평균을 구해보자. sum 을 사용한 예제를 만들려고 했으나 average 함수가 더 적합한 것 같아 average 함수를 사용하였다. 난이도에 차이는 없다.

사용할 함수: average, if, month
작전: 만약(if) 5월시험(month)이면 평균(average)을 내자. (너무 당연한가?)
함수: =average ( if ( month ( A2:A25 ) = G15, D2:D25 )) Ctrl + Shift + Enter

함수를 모두 작성하고 꼭 Ctrl + Shift + Enter 를 눌러서 수식을 완성하기 바란다. 수식입력창에서 수식 양쪽 끝에 중괄호 {} 가 붙어있으면 정상이다. 직접 중괄호를 입력하면 안 된다.

이제 함수를 뜯어보자.

If ( month ( A2:A25 ) = G15, D2:D25 ) 를 보자. 각 행 별로 A열 (A2:A25) 이 5 (G15 셀) 이면 D열 (D2:D25) 의 값을 반환한다. 위에서 averageifs 함수 안에서는 month 함수를 쓸 수 없었는데 배열함수의 if 안에서는 가능하다. 이것 때문에 배열함수가 강력한 것이다. average 함수는 if 함수가 주는 값들로 평균을 계산한다. 눈에 보이진 않지만 각 행에 대해서 함수가 실행된다고 이해하면 쉽다.

배열함수는 주로 sum + if, average + if, count + if, max + if 이런 형태로 특정조건을 만족하는 어떤 값을 구하게 된다. 연습문제로 rank 함수를 max + if 함수로 대체해 구현해 보는 것도 좋겠다.

아직 승현이의 5월 평균 점수를 구하진 못했다. 고급 편에서 이어서 알아보자.