엑셀 배열 수식으로 다중 조건의 개수, 합계, 평균 구하기 feat.예제파일

대용량의 데이터를 다루다 보면 하나의 조건 보다 다중 조건으로 수식을 계산해야 하는 경우가 많이 생깁니다. 배열 수식은 수식을 중괄호로 감싸는 독특한 형식을 띠고 있어요. 데이터를 하나로 묶어서 계산하기 때문에 수식이 간결해지고 한 번에 결과를 가져올 수 있습니다.

 

 

다중 조건의 개수

배열수식개수합계평균구하기.xlsx

0.01MB

예제 파일을 내려받기 하시면 아래와 같은 데이터가 있습니다. 이름, 구분, 지역, 구입금액이 있고요. 이름정의로 각각의 데이터 범위를 정의했어요.

각 지역별 '정회원'과 '비회원'의 구매 횟수를 구해보겠습니다. 

{=SUM((구분=H$5)*(지역=$G6))}

[H6] 셀에 위에 수식을 입력하고 [Control] + [Shift] + [Enter] 키를 눌러주면 중괄호({})가 씌워집니다. 배열 수식으로 계산하라는 뜻이지요. 구분과 지역에 셀을 절댓값($)으로 고정을 해줘야 그대로 복사를 해도 정상적으로 계산이 됩니다.

 

다중 조건 금액 합계

합계는 '구매 횟수'를 구하는 수식에 '구입금액' 데이터를 곱해서 합계를 구하는 겁니다. 

{=SUM((구분=J$5)*(지역=$G6)*구입금액)}

배역 수식을 이용하면 간단하게 다중 조건의 합계를 구할 수 있지요. [J6] 셀에 수식을 보면 지역은 '서울'이고, 구분이 '정회원'인 구입금액이 나타나게 됩니다. [Control] + [Shift] + [Enter] 키를 입력해 주면 배열 수식이 완성됩니다.

 

다중 조건의 평균

평균을 구할 때는 AVERAGE와 IF 함수 조합해서 배열 수식을 적용했습니다.

{=AVERAGE(IF((구분=L$5)*(지역=$G6),구입금액))}

[L6] 셀을 기준으로 '서울'이고, '정회원'인 사람의 구입금액의 평균을 구하는 겁니다. 간단하죠? 잘 익혀두면 다양하게 활용할 수 있는 수식입니다.

 

마무리

배열 수식으로 다중 조건의 개수, 합계, 평균을 구해 봤습니다. 예제 파일에는 두 가지 조건을 했지만 조건을 더 추가해도 됩니다. 아래 SUMPRODUCT 함수와 같이 익혀두시면 유용하게 잘 사용할 수 있을 겁니다.

 

엑셀 함수 SUMPRODUCT 다중 조건의 개수와 합계 feat. 예제 파일