엑셀 실무] SUM, YEAR, MONTH 함수를 배열 수식으로 사용한 조건에 일치하는 개수와 합계 구하기

앞서 배열 수식 기본 작성법에 대해 소개를 했습니다. 

이번에는 실무에서 배열 수식을 사용하는 사례를 소개하겠습니다. 

 

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

○○○ 지점 실적 현황이 있습니다. 년도와 월을 목록에서 선택하면 신규, 번호이동, 기기변경 개수와 매출액, 누적총계가 자동으로 계산되도록 집계표를 만들려고 합니다.

참고로 엑셀은 데이터베이스를 다양한 방법으로 집계할 수 있는 기능을 제공합니다.

데이터를 제공하신 구독자의 부탁대로 수식을 사용하여 현재 모양 그대로 집계하는 방법을 소개하겠습니다.

 

 

실습 파일 다운로드

배열수식사용예_실습파일.xlsx

0.10MB

완성 파일 다운로드

배열수식사용예_완성.xlsx

0.10MB

 

 

유효성 검사 목록

 

먼저 연도와 월을 선택할 수 있도록 유효성 검사 목록을 설정하겠습니다.

1. [G3] 셀을 선택하고 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 선택합니다.

[데이터 유효성] 대화 상자가 실행되면 [설정] 탭에서 [제한 대상] → [목록]을 선택합니다.

 

 

2. 원본 입력창에 2019,2020,2021을 입력합니다.

이때 원본 데이터 사이는 쉼표(,)로 구분해서 입력합니다.

 

 

3. [설명 메시지] 탭을 눌러 목록에서 선택하세요.라고 설명 메시지를 입력합니다.

 

 

[G3] 셀을 선택하면 메시지가 보이나요?

목록에서 2019를 선택해봅시다.

 

 

같은 방법으로 1~12월까지 나타나도록 유효성을 설정해봅시다.

 

4. [H3] 셀을 선택하고 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 선택합니다.

[데이터 유효성] 대화 상자가 실행되면 [설정] 탭에서 [제한 대상] → [목록]을 선택합니다.

 

이때 주의해야 할 점은 1월, 2월, 3월과 같이 월을 입력하면 안 됩니다.

년, 월은 수식에서 참조할 데이터이기 때문에 문자로 입력되면 안 됩니다.

아래와 같이 1,2,3,4,5,6,7,8,9,10,11,12를 입력합니다. 

 

 

2019년, 1월과 같이 년, 월이 표시되게 하려면 표시 형식을 지정하면 됩니다.

[G3] 셀을 선택하고 단축키 <Ctrl + 1>을 눌러 [셀 서식] 대화 상자를 엽니다.

[표시 형식] 탭에서 [사용자 지정] 범주를 선택하고 형식 입력창에 0 “년”을 입력하면 2019년과 같이 표시됩니다. 

같은 방법으로 [H3] 셀을 선택하고 0 “월”로 서식을 지정합니다.

 

 

이름 정의

 

개통일자와 개통 구분, 매출액 범위는 10000행으로 임의 설정하겠습니다.

동적 범위를 사용하지 않고 데이터가 계속 추가되더라도 결과에 반영되기 위해서 입니다.

필요하다면 더 많이 지정해도 됩니다.

하지만 마지막 행까지 설정하는 건 처리 속도를 늦추는 요인이 될 수 있습니다.

수식을 작성할 때 편리함을 위해 범위를 이름으로 정의해 놓겠습니다.

이름 상자에 B8:B10000을 입력하고 <Enter>를 누릅니다.

[B8:B10000] 셀 범위가 선택이 됩니다.

 

 

2. 다시 이름 상자에 개통일자라고 입력하고 <Enter>를 누릅니다.

그러면 [B8:B10000] 셀 주소 대신 개통일자로 대신 수식에서 사용할 수 있습니다.

같은 방법으로 개통구분 범위와 매출액 범위도 이름을 정의합니다.

 

 

개통일자, 개통구분, 매출액까지 이름이 정의되었습니다.

 

 

배열 수식 작성

 

이제 수식을 작성해서 신규, 번호이동, 기기변경, 매출액을 구해보겠습니다.

 

먼저 신규 개수를 구해보죠.

데이터를 보면 개통구분에는 신규, 번호이동, 기기변경이 있습니다.

그중 2019년 4월에 해당되는 신규 개수를 구하는 수식을 작성해 보겠습니다.

 

개통일자의 연도가 2019년, 4월 그리고 개통구분이 신규인, 이렇게 개수를 구하기 위한 조건은 총 3개입니다.

다중 조건에 대한 개수를 구하는 COUNTIFS 함수를 사용하면 되겠지!라고 생각했다면 구할 수 없습니다.

왜냐하면 개통일자는 연-월-일 형식의 데이터이고 조건은 2019와 4입니다.

YEAR 함수를 사용하여 개통일자에서 연도를 추출해서 2019인지 비교해야 하고 MONTH 함수를 사용하여 개월을 추출한 다음 4인지 비교를 해야 합니다.

하지만 COUNTIF 함수의 첫 번째 인수 Range는 조건이 맞는지 검사할 범위를 참조해야 하는데 YEAR 함수를 중첩해서 사용할 수 없습니다.

개월도 마찬가지입니다.

 

이런 경우 배열 수식을 사용해야 합니다. 

결과를 구할 [I3] 셀을 선택하고 아래와 같이 수식을 작성하고 <Ctrl + Shift + Enter>를 누릅니다. (오피스 365를 사용하는데 <Enter>를 눌러도 됩니다.)

=SUM((YEAR(개통일자)=G3)*(MONTH(개통일자)=H3)*(개통구분=I2))

 

 

수식을 한 번 보겠습니다.

배열 수식을 작성하면 개수도 COUNTIFS 함수를 사용할 필요 없이 SUM 함수를 사용하여 구할 수 있습니다.

먼저 첫 번째 조건을 보겠습니다.

 

(YEAR(개통일자)=G3)은 YEAR 함수로 B8 셀의 개통일자에서 연도를 추출합니다.

그런 다음 G3 셀의 2019와 같은지 비교합니다.

같으면 True, 다르면 False입니다.

그 다음 B9 셀에서 연도를 추출하여 다시 G3 셀과 비교합니다.

이렇게 B23 셀까지 연도를 추출하여 G3 셀과 비교하는 겁니다.

그러면 아래 그림에 표시한 것과 같이 True 또는 False 결과를 구합니다.

 

두 번째 조건도 같습니다.

MONTH 함수를 사용하여 먼저 B8 셀에서 개월을 추출합니다.

그런 다음 H3 셀과 비교해서 True 또는 False 결과를 구하는 거죠.

두 조건의 결과를 곱하면 True * True인 경우에만 True가 됩니다.

논리 결과를 곱하면 조건 모두 True인 경우에만 True 즉 1이 됩니다.

 

최종적으로 SUM 함수가 결과를 모두 합을 내어 개수를 구하는 거죠.

이때 조건식은 괄호 안에 작성해야 합니다.

*(곱하기) 연산자가 비교 연산자 =(같다)보다 우선순위를 가지므로 조건을 먼저 비교하도록 괄호 안에 작성해야 합니다.

아래 그림만으로 충분히 이해가 되죠?

 

같은 방법으로 번호이동과 기기변경 개수를 구하면 됩니다.

=SUM((YEAR(개통일자)=G3)*(MONTH(개통일자)=H3)*(개통구분=J2))

=SUM((YEAR(개통일자)=G3)*(MONTH(개통일자)=H3)*(개통구분=K2))

 

이제 매출액을 구하는 수식을 보겠습니다.

2019년 4월에 해당하는 두 조건이 일치하는 매출액의 합을 구하는 수식입니다.

 

마지막으로 누적총계는 =SUM(매출액)으로 작성하면 됩니다.

매출액 이름 범위에 값이 입력되면 누적총계에 반영됩니다.

 

실무에 사용되는 데이터를 가지고 배열 수식을 활용하는 방법에 대해 소개했는데 처음 배열 수식을 접하는 분들이라면 어려울 수 있습니다. 

앞서 올린 강좌를 참고하셔서 배열 수식을 적용하는 방법이 필요하셨던 분들이라면

이번 기회에 꼭 익혀 보시길 바랍니다. 

 

https://hantip.net/236?category=676450

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

 

 

필요하신 분들에게 이 강좌가 도움이 되길 바랍니다.