기초 편에 이어서 승현이의 5월 평균 점수를 구해 보자. 배열 함수가 뭔지 모르는 사람은 기초 편을 보고 오면 좋겠다.

이전 함수부터 시작하자.

=average ( if ( month ( A2:A25 ) = G15, D2:D25 ))

month ( A2:A25 ) = G15 이 부분이 조건에 해당한다. 조건에 맞는 행의 D 열 값으로 평균을 구한다. 학생들의 5월 성적의 평균값을 구하는 함수이다. 여기에 승현이라는 조건을 추가하면 우리가 원하는 승현이의 5월 점수 평균을 구할 수 있다. 승현이라는 조건을 추가해보자.

=average ( if ( AND ( B2:B25 = "승현", month ( A2:A25 ) = G15 ), D2:D25 ))

함수의 기본에 충실하다면 이렇게 풀 수 있을 것이다. 안타깝게도 AND함수는 배열함수에서 적절하지 않다. AND는 값 한 개를 반환하는 함수이기 때문이다. 배열함수에서는 곱하기와 더하기로 ANDOR를 대체한다.

=average ( if ( (B2:B25 = "승현") * (month ( A2:A25 ) = G15), D2:D25 ))

괄호가 많아서 좀 헷갈리겠지만 잘 이겨내길 바란다. 이렇게 함수를 쓰면 정확히 승현이의 5월 평균점수를 구할 수 있다. 생각보다 어렵지 않다고 생각했으면 좋겠다.

큰 차이

눈치챈 독자도 있겠지만 sumifs, averageifs 함수와의 큰 차이는 조건부분을 다양하게 정할 수 있다는 것이다. 기존 함수는 항상 and 조건으로 판정된다. 반면에 배열함수는 OR 조건도 가능하다. 그리고 괄호를 사용하여 복잡한 조건도 설정할 수 있다. OR 대신에 sumifs를 두 번 쓰면 되지 않냐는 생각을 할 수도 있겠지만 그렇지 않다. OR 조건과 sumifs 두 번 쓰는 것은 논리적으로 완전히 다르기 때문이다.

한 번이라도 참가한 사람에게 10,000 원 씩 돌려주기로 하였다. 배열함수를 모른다면 아래처럼 각 사람 별로 한번이라도 참가했는지 체크하고 그 수를 샐 것이다. (이해를 돕기 위해 약간 편집했다. 엑셀에서 여러 셀에 동시에 커서를 넣을 순 없다.)

배열함수가 없다면 반드시 열 하나를 추가해서 계산해야만 한다. 이때 배열함수는 수식 한 줄로 멋지게 값을 구할 수있다.

물론 한번이라도 참가한 것을 알 수 있도록 체크 함수가 필요하다고 얘기할 수도 있겠다. 하지만 그 주장은 이번 예제의 논점에서 조금 벗어났다. 추가 행을 사용하지 않고 원하는 값을 구할 수 있다는 점이 중요한 것이다. 실무에서 어떤 상황을 만날지 모르기 때문이다.

한 걸음 더

if 함수는 조건부(logical_test), 참영역(value_if_true), 거짓영역(value_if_false) 으로 구성되어 있다. 지금까지는 logical_test, value_if_true 영역만 사용했다. 그리고 value_if_true 영역도 단조롭게 사용했다. 상황에 따라서 value_if_false 영역도 활용할 수 있다. 그리고 if 함수 안에 또 if 함수를 넣을 수도 있다.

속도

배열함수의 단점은 데이터 양이 많아지면 속도가 느려진다는 것이다. 이 부분에 대해선 다음 포스팅에서 이어가겠다.