구글 스프레드 시트 6 - 조건부 집계함수(COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, MINIFS)

오늘은 조건부 집계함수에 대해서 알아보겠습니다.

조건부 집계함수는 말 그대로 어떤 집계를 함에 있어서 조건을 걸어서 집계하는 함수인데요.

그야말로 어떤 조건 하에서 더하기, 갯수새기, 평균구하기와 같은 집계를 하는 함수입니다.

 

대표적으로 5가지 조건부 함수가 있습니다! 

함수이름	기능	문법
COUNTIFS	여러 기준에 따른 범위의 수를 반환	=COUNTIFS(기준_범위 1, 기준 1, [기준 범위 2, 기준 2, ...])
SUMIFS	여러 기준에 따라 범위의 합계를 반환	=SUMIFS(합계_범위, 기준_범위1, 기준1, [기준_범위2, 기준2, ...])
AVERAGEIFS	여러 기준에 따른 범위의 평균을 반환	=AVERAGEIFS(평균_범위,기준_범위1, 기준1, [기준_범위2, 기준2, ...])
MAXIFS	기준 집합으로 필터링된 셀 범위에서 최대값을 반환	=MAXIFS(범위, 기준_범위1, 기준1, [기준_범위2, 기준2, …])
MINIFS	기준 집합으로 필터링된 셀 범위에서 최소값을 반환	=MINIFS(범위, 기준_범위1, 기준1, [기준_범위2, 기준2, …])

 

1. COUNTIFS

- countifs 함수는 어떤 조건에 해당하는 셀 갯수를 반환해주는 함수 입니다.

이런 데이터 셋이 있다고 가정해봅시다.

 

갑자기 대표님이 와서 묻습니다.

"우리 회사에 과장이 몇명이지?"

이런경우 각 직책별로 몇명이 있는지 알고 싶다고 한다면 일일이 세는 방법도 있지만
과장일땐 시트에서 과장만 카운트하고 대리일땐 대리만 카운트하는게 필요하겠죠?? 
이런경우 해결해주는게 countifs함수입니다.

=countifs(C2:C10,H2) 
# c2:c10 -> 직책이 있는 구역(기준 범위)
# H2 -> 어떤 조건의 직책을 가져올건지에 대한(기준)

H열에 새로운 기준을 만들고 countifs를 활용해서 직책으로 있는 C열에 과장이 얼마나 있는지 카운팅 해봤습니다.

그래서 대표님께 "과장은 3명입니다!" 라고 보고를 드렸더니

"음 그럼 부서별 직책별로 몇명있는지 요약좀 해주게"

라고 말씀하시네요.

 

이런경우는 어떻게 해결할 수 있을까요?
핵심은 "부서별" 과 "직책별"입니다.

~별의 경우 조건이라는 것인데 이경우 조건이 2개인 것입니다.

 

이경우도 countifs로 해결할 수 있습니다.

 

=COUNTIFS($C$2:$C$10,I$1,$B$2:$B$10,$H2)
# $C$2:$C$10 -> 직책컬럼(조건 범위1)
# I$1 -> 직책(조건1)
# $B$2:$B$10 -> 부서컬럼(조건 범위2)
# $H2 -> 부서(조건2)

H열에는 부서를 I1,J1,K1에는 각각 직책 이름을 설정하고 COUNTIFS 함수를 이용해서 피벗테이블처럼 간이 테이블을 완성했습니다. 이런방식으로 조건을 여러개 설정하여 COUNT를 할 수 있습니다.


2. SUMIFS

- sumifs 함수는 어떤 조건에 해당하는 셀의 합을 구하는 함수입니다.

 

이런 데이터 셋이 있다고 가정합시다.

 

갑자기 대표님이 또 와서 묻습니다.

 

"우리 회사 과장 연봉 총액이 얼마지?"

이런 경우에도 과장일땐 시트에서 과장의 연봉만 sum하고 대리일땐 대리만 sum 하는게 필요하겠죠?? 

=sumifs($F$2:$F$10,$C$2:$C$10,H2)

# $F$2:$F$10 -> 연봉컬럼(합계_범위)
# $C$2:$C$10 -> 직책컬럼(조건_범위)
# H2 -> 직책(조건)

H열에 새로운 기준을 만들고 sumifs를 활용해서 c열에 직책이 과장이라고 되어있는 분들의 연봉을 합계 해봤습니다.
그래서 대표님께 "과장님들의 연봉의 합은 2억 950만원입니다!" 라고 보고를 드릴 수가 있게 되죠.

이렇게 보고를 드렸더니 다시
대표님께서 "음.. 마케팅부서의 남직원에 대한 연봉 총액은 얼마지?"

이렇게 물어보신다면 어떻게 해야할까요?
`마케팅 부서` 와 `남자` 의 두가지 조건을 가지고 `연봉` 의 총액을 구해봐야겠죠?

=sumifs($F$2:$F$10,$B$2:$B$10,H2,$D$2:$D$10,I2)

# $F$2:$F$10 -> 연봉컬럼(합계_범위)
# $B$2:$B$10 -> 부서컬럼(조건_범위1)
# H2 -> 마케팅(조건1)
# $D$2:$D$10 -> 성별컬럼(조건_범위2)
# I2 -> 성별(조건2)

H열과 I열에 각각 조건을 만들어 이런식으로 구할수 있을 것 입니다.

 

3. AVERAGEIFS

- averageifs 함수는 어떤 조건에 해당하는 셀의 평균을 구하는 함수입니다.

 

이런 데이터 셋이 있다고 가정합시다.

 

갑자기 대표님이 또 와서 묻습니다.

 

"우리 회사 대리 연봉 평균이 얼마지?"

이런 경우에도 대리일땐 시트에서 대리의 연봉만 평균내고 과장일땐 과장만 평균내는게 필요하겠죠?? 

=averageifs($F$2:$F$10,$C$2:$C$10,H2)

# $F$2:$F$10 -> 연봉컬럼(평균_범위)
# $C$2:$C$10 -> 직책컬럼(조건_범위)
# H2 -> 직책(조건)

H열에 새로운 기준을 만들고 averageifs 를 활용해서 c열에 직책이 `대리`라고 되어있는 분들의 연봉평균을 내봤습니다.
그래서 대표님께 "대리님들의 연봉의 평균은 5420만원입니다!" 라고 보고를 드릴 수가 있게 되죠.

이렇게 보고를 드렸더니 다시
대표님께서 "음.. 마케팅 부서의 여성직원 평균연봉이 얼마지?"

이렇게 물어보신다면 어떻게 해야할까요? 
`마케팅 부서` 와 `여자` 의 두가지 조건을 가지고 `연봉` 의 평균을 구해봐야겠죠?

=averageifs($F$2:$F$10,$B$2:$B$10,H2,$D$2:$D$10,I2)

# $F$2:$F$10 -> 연봉컬럼(평균_범위)
# $B$2:$B$10 -> 부서컬럼(조건_범위1)
# H2 -> 마케팅(조건1)
# $D$2:$D$10 -> 성별컬럼(조건_범위2)
# I2 -> 성별(조건2)

H열과 I열에 각각 조건을 만들어 이런식으로 구할수 있을 것 입니다.

 

4. MINIFS, MAXIFS

- minifs, maxifs 함수는 어떤 조건에 해당하는 셀의 최소값과 최대값을 구하는 함수입니다.

 

이런 데이터 셋이 있다고 가정합시다.

 

갑자기 대표님이 또 와서 묻습니다.

 

"우리 회사 과장 최소연봉과 최대연봉이 얼마지?"

 

=minifs($F$2:$F$10,$C$2:$C$10,H2)

# $F$2:$F$10 -> 연봉컬럼(최소값을_찾는_범위)
# $C$2:$C$10 -> 직책컬럼(조건_범위)
# H2 -> 직책(조건)

=maxifs($F$2:$F$10,$C$2:$C$10,H2)

# $F$2:$F$10 -> 연봉컬럼(최대값을_찾는_범위)
# $C$2:$C$10 -> 직책컬럼(조건_범위)
# H2 -> 직책(조건)

H열에 새로운 기준을 만들고 minifs, maxifs 함수를 활용해서 c열에 직책이 `과장`라고 되어있는 분들의 연봉 최소값, 최대값을 구했습니다. 그래서 대표님께 "과장님들의 최소연봉은 6850이고 최대연봉은 7100만원입니다!" 라고 보고를 드릴 수가 있게 되죠.

maxifs와 minifs도 조건이 2개이상일때도 countifs, sumifs, averageifs 처럼 계속 조건을 확장해서 사용이 가능합니다.

마치며

그냥 피봇테이블로 해도 되는 작업일 수도 있으나 조건이 많아지면 많아질수록 테이블을 내맘대로 디자인 해야할때가 있습니다. 이럴때 해당 함수들을 활용하면 가독성도 챙기고 필요한 정보만 쏙 뽑아서 볼수 있습니다. 매우매우 중요한 함수들이니 잘 알아두시면 큰 도움이 되실겁니다.