엑셀 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 함수 – 조건별 합계 구하기
이 글이 도움이 되셨다면 공유를 부탁 드립니다. 다음 글에서 뵙겠습니다!