[엑셀] 엑셀 SUBTOTAL 함수 활용법, 데이터 집계의 필수 도구

 

들어가며

엑셀에서 데이터를 분석하고 집계할 때, 조건에 따라 유연하게 계산해야 하는 경우가 많습니다. 

이럴 때 SUBTOTAL 함수는 매우 유용한 도구라 할 수 있는데요.   
특히, 필터링된 데이터에서만 합계를 계산하거나 평균을 구하는 등, 단순한 함수로 처리하기 어려운 작업도 손쉽게 해결할 수 있습니다.  
이번 글에서는 SUBTOTAL 함수의 기본 구조, 사용 방법, 다양한 활용 사례를 자세히 설명하겠습니다.

 

SUBTOTAL 함수란?

`SUBTOTAL` 함수는 리스트나 데이터베이스에서 집계 작업을 수행할 때 사용하는 함수입니다.  
다양한 계산(합계, 평균, 개수 등)을 선택적으로 적용할 수 있으며, 필터링된 데이터에서만 작동하도록 설정할 수도 있습니다.

 

SUBTOTAL 함수의 기본 구조

=SUBTOTAL(함수_번호, 범위1, [범위2, ...])

 

* 인수 설명
1. 함수_번호

 계산 방식(합계, 평균, 최대값 등)을 지정하는 번호입니다.  
   - 1~11 : 숨겨진 데이터 포함   
   - 101~111 : 숨겨진 데이터 제외

2. 범위   
   계산할 데이터의 범위입니다. 여러 범위를 선택할 수 있습니다.

* 함수_번호 목록

번호	계산 방식	숨긴 행 포함	숨긴 행 제외
1	AVERAGE (평균)	1	101
2	COUNT (숫자 개수)	2	102
3	COUNTA (모든 데이터 개수)	3	103
4	MAX (최대값)	4	104
5	MIN (최소값)	5	105
6	PRODUCT (곱)	6	106
7	STDEV (표준편차)	7	107
8	STDEVP (전체 표준편차)	8	108
9	SUM (합계)	9	109
10	VAR (분산)	10	110
11	VARP (전체 분산)	11	111

 

SUBTOTAL 함수의 예제 

* 예제 데이터

A	B
카테고리	판매량
전자제품	120
가전제품	80
가구	150
전자제품	90
가전제품	50

 

(1) 전체 합계 계산

 

 * 문제 : 판매량의 총합을 구하려면?  

 =SUBTOTAL(9, B2:B6)

 - 9 : `SUM`(합계)을 의미.  
 - B2:B6 : 판매량 데이터 범위.  
 - 결과 : `490` (120 + 80 + 150 + 90 + 50)

(2) 필터링된 데이터의 합계

* 문제 : 특정 카테고리를 필터링한 후 판매량 합계를 구하려면?  

 

1. 필터 적용 :  
   - 상단 메뉴에서 데이터 > 필터를 클릭.  
   - `카테고리` 열에서 "전자제품"을 필터링.  

2. SUBTOTAL 함수 사용 :  
 =SUBTOTAL(109, B2:B6)
 - 109 : `SUM`(합계), 숨긴 행 제외.  
 - B2:B6 : 판매량 데이터 범위.  
 - 결과 : `210` (120 + 90)

> TIP : 필터링된 데이터에만 작동하려면 100번대 함수 번호를 사용해야 합니다.

(3) 평균 구하기

* 문제 : 전체 판매량의 평균을 구하려면?  
 =SUBTOTAL(1, B2:B6)
 - 1 : `AVERAGE`(평균)을 의미.  
 - B2:B6 : 판매량 데이터 범위.  
 - 결과 : `98` (490 ÷ 5)

 

반응형

(4) 필터링된 데이터의 최대값

 * 문제 : 특정 카테고리(예: "전자제품")의 판매량 최대값을 구하려면?  
 =SUBTOTAL(104, B2:B6)
 - 104 : `MAX`(최대값), 숨긴 행 제외.  
 - B2:B6 : 판매량 데이터 범위.  
 - 결과 : `120`

(5) 필터링된 데이터의 개수
 * 문제 : 필터링된 데이터에서 숫자가 포함된 셀의 개수를 구하려면?  
 =SUBTOTAL(102, B2:B6)
 - 102 : `COUNT`(숫자 개수), 숨긴 행 제외.  
 - B2:B6 : 판매량 데이터 범위.  
 - 결과 : 필터링된 데이터의 개수 반환.

SUBTOTAL 함수의 활용 팁

(1) 필터와 함께 사용하기
SUBTOTAL은 필터링된 데이터에서만 계산 결과를 보여주므로, 데이터 분석과 집계 작업에 매우 유용합니다.  
- 예를 들어, 특정 지역별 판매량이나 특정 카테고리별 평균값을 빠르게 확인할 수 있습니다.

(2) 숨겨진 행 처리
`SUBTOTAL`은 숨겨진 행(필터링된 데이터 또는 수동으로 숨긴 행)을 포함하거나 제외할 수 있습니다.  
- 포함하려면 1~11 번 번호 사용.  
- 제외하려면 101~111 번 번호 사용.

(3) 데이터 분석 자동화
- 데이터가 실시간으로 업데이트되더라도 SUBTOTAL 함수는 동적으로 계산 결과를 반영합니다.  
- 데이터를 정렬하거나 필터를 적용해도 실시간으로 반영되므로, 보고서를 자동화하기에 적합합니다.

SUBTOTAL 과 다른 함수의 차이점

구분	SUBTOTAL	SUM/AVERAGE/MAX 등 일반 함수
필터 반영	필터링된 데이터만 계산 가능	필터와 관계없이 전체 데이터 계산
숨긴 행 처리	포함하거나 제외 가능	숨긴 행 포함
적합한 작업	데이터베이스 분석, 필터링된 데이터 계산	일반 합계 및 평균 계산

 

마무리하며

`SUBTOTAL` 함수는 엑셀에서 필터링된 데이터 집계, 숨긴 행 처리 등 유연한 데이터 분석 작업을 할 때 필수적으로 사용되는 강력한 도구입니다.  

 

- 필터링된 데이터 분석 : 특정 조건의 데이터만 계산.  
- 숨긴 행 제외/포함 : 숨겨진 데이터의 포함 여부를 조정.  
- 다양한 계산 옵션 : 합계, 평균, 최대값, 개수 등 다양한 집계 가능.

엑셀 작업을 효율적으로 수행하려면, SUBTOTAL 함수를 익히고 활용하는 것이 중요합니다. 

여러분도 실전 데이터에 적용해 보고, 업무 속도를 높여보세요! 😊