엑셀 실무] 배열 수식 기본 사용법

제공되는 함수만을 사용해서 원하는 결과를 구할 수 없는 경우가 있습니다.

그럴 때 배열 수식을 사용하면 해결할 수 있는 데이터들이 있어 준비했습니다.

 

처음부터 실무에 바로 적용하는 사례는 어려워 배열 수식 기본 작성법부터 소개하겠습니다.

먼저 데이터를 보겠습니다.

 

실습 파일 다운로드

배열수식기본.xlsx

0.20MB

완성 파일 다운로드

배열수식기본_완성.xlsx

0.20MB

 

○○○ 지점 실적 현황에서 개통구분 별로 신규, 번호이동, 기기변경 건의 개수와 매출액 합을 집계하려고 합니다.

이때 개수는 조건에 일치하는 개수를 구하는 COUNTIFS 함수를 매출은 SUMIFS 함수를 사용하면 되지만 배열 수식 기본 원리를 익히기 위해 함수 대신 배열 수식으로 집계해 보겠습니다.

 

배열 수식으로 조건에 일치하는 개수 구하기

배열 수식을 작성하면 개수와 매출액 모두 SUM 함수를 사용하여 구할 수 있습니다.

먼저 개통구분이 신규인 개수를 구하는 수식입니다.

=SUM(($F$7:$F$22=H2)*1)

배열 수식은 작성 후 <Ctrl + Shift + Enter>를 눌러야 합니다.

저는 오피스365를 사용하는데 <Enter>를 눌러도 결과를 구할 수 있네요.

이전 버전은 모두 <Ctrl + Shift + Enter>를 눌러주세요.

 

수식을 복사해서 번호이동, 기기변경 개수도 구합니다.

 

SUM 함수는 인수로 합계를 구할 범위를 입력 받습니다..

지금처럼 조건식을 인수로 사용할 수 있는 것은 배열 수식으로 작성하기 때문에 가능합니다.

 

이해를 돕기 위해 다시 아래 그림을 보세요.

[F7:F22] 셀 범위를 순차적으로 [H2] 셀과 비교합니다.

 

아래 그림과 같이 조건이 참이면 True, 거짓이면 False입니다.

True, False와 같은 논리 값은 합계를 낼 수 없기 때문에 1을 곱해야 합니다.

True * 1 = 1이 되고 False * 1 = 0이 됩니다.

이렇게 나온 결과를 SUM 함수가 합계를 구해 개수가 구해지는 겁니다.

이번에는 합계를 구해보겠습니다.

아래와 같이 수식을 작성하고 <Ctrl + Shift + Enter>를 누릅니다.

=SUM(($F$7:$F$22=H2)*$J$7:$J$22)

결과가 구해지면 번호이동, 기기변경까지 수식을 복사합니다.

 

개통구분이 신규인지 비교합니다.

결과가 True면 1*매출액을 한것과 같습니다.

False라면 매출액과 곱하기 하더라도 결과는 0이겠죠?

0*매출액과 같습니다.

SUM 함수가 결과를 모두 합쳐서 신규 매출액 합계를 구합니다.

 

다중 조건으로 개수를 구하는 배열 수식 작성하기

이번에는 조건을 더 적용해 보겠습니다.

개통구분과 할인구분에 따른 개수와 매출액 집계를 구해보겠습니다.

먼저 [I3] 셀을 선택하고 아래와 같이 수식을 작성한 후 <Ctrl + Shift + Enter>를 누릅니다.

=SUM(($F$9:$F$24=I2)*($G$9:$G$24=$G$3))

 

조건을 괄호 안에 각각 작성하고 곱하기 합니다.

조건이 두 개 이상인 경우 앞에서처럼 1을 곱하기 할 필요가 없습니다.

 

이번에는 신규이면서 공시지원에 해당하는 매출액의 합을 구해보겠습니다.

아래와 같이 수식을 입력하고 <Ctrl + Shift + Enter>를 누릅니다.

=SUM(($F$9:$F$24=I2)*($G$9:$G$24=G3)*$K$9:$K$24)

 

두 조건이 모두 일치하는 경우에 매출액과 곱하기 해서 매출액의 합계를 구합니다.

아래의 그림을 보면 쉽게 이해할 수 있을 겁니다.

 

어떤가요? 배열 수식 사용하는 방법이 조금 이해가 되셨나요?

이번에 든 예는 COUNTIFS, SUMIFS 함수를 사용하여 구할 수 있지만 배열 수식의 기본 사용법을 익히기 위해 가장 쉽게 접근할 수 있는 방법으로 소개했습니다.

 

 

 

기본 사용법을 익혀 다음 번 강좌에서 소개하는 실무 사례를 익히는데 도움이 되었으면 합니다.

강좌 마치겠습니다.