엑셀 SUBTOTAL 함수 – 필터링된 데이터만 부분합 계산하기

엑셀에서 데이터를 수집하고 관리할 때가 많이 있습니다. 데이터를 SUM, COUNT, AVERAGE, MAX, MIN 등 자주 사용하는 함수를 사용하여 데이터를 집계하여 사용합니다. 그런데 데이터를 필터링하여 필터링된 데이터만 부분합으로 계산해야 할 때가 많이 있습니다. 그럴 때 SUBTOTAL 함수를 사용하시면 됩니다.

그럼 SUBTOTAL 함수를 사용하여 필터링된 데이터만 부부합으로 계산하는 방법에 대해서 알아보죠.

◎ 포스트 기준 – Microsoft 365 앱

1. 함수 구문

SUBTOTAL 함수 구문 (마이크로소프트 문서)

▶ 목록이나 데이터베이스의 부분합을 반환합니다.

SUBTOTAL(function_num, ref1, [ref2],…)

• function_num : 필수 요소입니다. 소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111입니다. 1-11에는 수동으로 숨겨진 행이 포함되는 반면 101-111은 해당 행을 제외합니다. 필터링된 셀은 항상 제외됩니다.
• ref1 : 필수 요소입니다. 부분합을 계산할 첫 번째 명명된 범위 또는 참조입니다.
• ref2,… : 선택 요소입니다. 부분합을 계산할 명명된 범위 또는 참조로서 2개에서 254개까지 지정할 수 있습니다.

▼ function_num

Function_num (숨겨진 행 포함)	Function_num (숨겨진 행을 무시)	함수
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

▼ 엑셀에서 함수를 입력하면 자동으로 function_num에 해당되는 함수를 보여줍니다.

☞ 만약에 필터링된 점수 합계를 구할 때 SUM 함수를 사용하게 되면 매번 필터링 후에 SUM 함수의 범위를 지정해야 되겠죠. 그럴 경우에 SUMTOTAL 함수를 사용하시면 됩니다. 그리고 데이터에서 행이 숨겨진 경우가 있는데요 숨기진 행을 포함할 때에는 1~10 숫자를 입력하고 숨겨진 행을 제외할 거면 101~111 숫자를 입력하시면 됩니다. 전 숫자 중에서 101, 109을 가장 많이 사용하는 것 같습니다. 예제로 한번 알아보죠.

2. 사용 예제

▼ 필터링된 데이터에 대해서 점수 합계 및 평균 구하기 (숨긴 행 포함)

1. 필터링된 점수 합계 구하기 (숨겨진 행 포함)

[B14] : =SUBTOTAL(9, D2:D11)

→ 9 숫자는 SUM이고 D2:D11는 점수 범위입니다.

2. 필터링된 점수 평균 구하기 (숨겨진 행 포함)

[B15] : =SUBTOTAL(1, D2:D11)

→ 1 숫자는 AVERAGE이고 D2:D11는 점수 범위입니다.

▼ 예시로 성별에서 “남”, “여” 필터링을 하게 되면 자동으로 계산이 됩니다.

▼ 데이터에서 5행, 6행 숨겨진 경우에 숨겨진 행을 포함할 건지 제외할 건지에 따라 값은 달라집니다.

이 글이 도움이 되셨다면 공유를 부탁 드립니다. 다음 글에서 뵙겠습니다!

관련 이전 게시글

엑셀 최대값, 최소값 구하기 – MAX, MIN, LARGE, SMALL 함수

엑셀 PRODUCT, SUMPRODUCT 함수 – 지정 범위 곱하고 더하기

엑셀 AVERAGE, AVERAGEA 함수 – 셀의 평균 구하기

엑셀 COUNTIF, COUNTIFS 함수 – 조건별 셀 개수 구하기

엑셀 SUM, SUMIF, SUMIFS 함수 – 조건별 합계 구하기