엑셀에서 COUNTIFS 함수를 활용하여 여러 조건에 대응하는 통계를 내보자

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제

 

 

01. 문제

Q. 다음과 같은 데이터 베이스가 있을 때, 산출 시트와 같은 결과를 도출하고 싶다.

 

(데이터 베이스)

데이터베이스 시트

(산출 결과)

산출 시트

 

문제를 정리하면 다음과 같다.

 

1. 데이터베이스에는 년도, 분기, 과일명, 구분(상담이 개인인지 그룹인지 표기)에 대한 정보가 있다.

2. 데이터 베이스를 이용하여 연도, 분기, 과일명이 주어졌을 때, 개인상담수, 그룹상담수를 카운트하여 채운다.

 

02. 문제 해결 방법

결국 여러 개의 조건을 만족하는 레코드의 수를 세는 것을 목표로 한다.

이럴 때는 COUNTIFS 함수를 사용할 수 있다.

 

COUNTIFS 함수에 대한 자세한 내용은 다음 포스트를 참고하자.

https://eggdrop.tistory.com/42

SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

 

따져야할 여러 개의 조건은 다음과 같다.

(1) 년도(연도)

(2) 분기

(3) 과일명

(4) 구분

 

개인상담횟수, 그룹상담횟수에서 개인상담, 그룹상담만 추출할 필요가 있다.

해당 작업은 LEFT함수를 이용하면 수행 가능하다.

 

LEFT함수에 대한 자세한 설명은 다음 링크를 참고하자.

https://eggdrop.tistory.com/56

엑셀 문자열 처리 (LEFT함수, RIGHT함수, LEN함수, FIND함수)

 

03. 예제

(1) COUNTIFS 함수 작성하기

 

COUNTIFS 함수는 다음과 같은 형태로 값을 입력하면 된다.

=COUNTIFS(비교할 대상1, 비교할 값1, 비교할 대상2, 비교할 값2, .......)

 

 - 년도 조건 추가하기

=COUNTIFS(데이터베이스!$A$2:$A$9,산출!$A2)

데이터 베이스 시트의 A열에는 연도 정보가 입력되어 있다. 위와 같은 방식으로 수식을 적으면, 산출 시트의 A2값에 해당하는 2021년의 레코드수가 반환된다.

 

다음과 같이 8의 값이 반환된다.

이유는 데이터 베이스 시트에 2021년에 대응되는 데이터가 8개 있기 때문이다.

수식에서 비교 대상인 두 번째 인자를 산출!$A2 처럼 열번호에 대해서만 절대주소를 취하는 이유는 해당 수식을 F열에도 복사할 예정이기 때문이다. F열 복사 시 산출 시트의 년도 대신 분기가 들어가는 문제를 방지할 수 있다. 또한 해당 데이터는 F열의 밑으로도 복사할 예정이기 때문에 행번호에 대해서는 상대주소를 사용해야 한다.

 

해당 내용에 대해서 더 알고 싶다면 다음 포스트를 참고하자.

https://eggdrop.tistory.com/50

엑셀 절대주소 vs 상대주소 (절대주소 활용법)

 

 - 분기조건 추가하기

=COUNTIFS(데이터베이스!$A$2:$A$9,산출!$A2, 데이터베이스!$B$2:$B$9, 산출!$B2)

데이터 베이스 시트의 B열에는 분기 정보가 입력되어 있다. 위와 같은 방식으로 수식을 적으면, 산출 시트의 B2값에 해당하는 1분기의 레코드수가 반환된다. 물론 앞에 2021년에 대한 조건이 있기 때문에 둘다 만족하는 2021년 1분기의 데이터 수가 반환된다.

반환된 값은 다음과 같다.

이유는 데이터 베이스 시트에 2021년, 1분기에 대응되는 데이터가 8개 있기 때문이다.

- 과일명 조건 추가하기

 

=COUNTIFS(데이터베이스!$A$2:$A$9,산출!$A2, 데이터베이스!$B$2:$B$9, 산출!$B2, 데이터베이스!$F$2:$F$9, 산출!$C2)

데이터 베이스 시트의 C열에는 과일명 정보가 입력되어 있다. 위와 같은 방식으로 수식을 적으면, 산출 시트의 C2값에 해당하는 과일명 사과인 레코드수가 반환된다. 물론 앞에 2021년 1분기에 대한 조건이 있기 때문에 이 조건들을 모두 만족하는 2021년 1분기 과일명 사과의 데이터 수가 반환된다.

반환된 데이터는 다음 과같이 3이다.

다음과 같이 데이터 베이스에서 2021년 1분기 사과 인 데이터는 3개 이기 때문이다.

 

- 상담구분 조건 추가하기

상담 조건을 추가하기 전에 산출 시트를 살펴보면 연도, 분기, 과일명 데이터와는 달리 1행에 개인상담횟수, 그룹상담횟수라는 레이블이 붙어 있는 것을 확인할 수 있다.

 

여기서 데이터 베이스에 입력되어 있는 개인상담과 그룹상담을 추출해보자.

 

=LEFT(E1, 4) 를 이용하면 E1의 내용인 "개인상담횟수" 에서 "개인상담"만 추출하는 것이 가능하다. 

 

해당 함수를 이용하여 상담 구분 조건을 추가해보자.

=COUNTIFS(데이터베이스!$A$2:$A$9,산출!$A2, 데이터베이스!$B$2:$B$9, 산출!$B2, 데이터베이스!$F$2:$F$9, 산출!$C2, 데이터베이스!$H$2:$H$9, LEFT(산출!E$1,4))

위와 같이 수식을 입력하면, 2021년도, 1분기, 사과, 개인상담에 해당하는 데이터가 집계된다.

 

결과는 다음과 같이 3개가 된다.

이유는 2021년 1분기에 발생한 3개의 사과는 모두 개인상담이기 때문이다.

 

자 이제 이 수식을 산출 시트의 계산 필요영역에 복사해보자.

 

다음과 같은 결과를 확인할 수 있다.

2021년 2분기에 대한 데이터가 없기 때문에 해당 값들은 모두 0으로 집계된다.

위의 데이터 베이스를 살펴보면 귤은 개인상담1건, 그룹상담1건이다.

포토와 딸기는 개인상담 각각 1건이다. 

감의 경우 그룹 상담이 1건이다.

 

모든 데이터가 정확하게 집계되는 것을 확인할 수 있다.