엑셀로 데이터를 정리하다 보면 특정 조건에 맞는 데이터만 합계를 내거나 평균을 구해야 할 때가 많아요. 특히 필터링된 데이터에서만 계산하고 싶을 때 일반적인 SUM 이나 AVERAGE 함수로는 한계가 있죠. 이럴 때 정말 유용한 것이 바로 SUBTOTAL 함수예요!
📌 핵심: SUBTOTAL 함수는 필터링된 데이터나 숨겨진 행을 자동으로 인식해서 보이는 데이터만 계산해주는 똑똑한 함수입니다.
🎯 SUBTOTAL 함수란?
엑셀 SUBTOTAL 함수는 데이터 범위에서 다양한 집계 기능(합계, 평균, 개수, 최대값, 최소값 등)을 수행하는 함수예요. 가장 큰 장점은 필터링된 데이터나 숨겨진 행을 자동으로 제외하고 계산한다는 점이에요.
함수 구문
=SUBTOTAL(집계함수번호, 범위1, [범위2], ...)매개변수 설명
- 집계함수번호: 수행할 집계 함수의 번호 (1-11 또는 101-111)
- 범위1: 집계할 첫 번째 데이터 범위
- 범위2: (선택사항) 추가 데이터 범위
집계함수번호 종류
| 번호 | 함수 | 설명 | 숨겨진 행 포함 여부 |
|---|---|---|---|
| 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 | 모집단 분산 | 포함/제외 |
📝 주의사항: 1-11번은 숨기기로 숨겨진 행도 포함하여 계산하고, 101-111번은 숨기기한 행을 제외하고 계산해요. 필터링으로 제외된 행은 항상 제외되요.
💡 SUBTOTAL 함수 활용 예제
예제 1: 기본 사용법 - 급여 관리
HR 부서에서 급여 데이터를 관리할 때 활용하는 예제예요:
사원번호,성명,부서,기본급,수당,급여
E001,김철수,영업부,3500000,500000,4000000
E002,이영희,마케팅부,3200000,300000,3500000
E003,박민수,개발부,4000000,600000,4600000
E004,최수지,인사부,3000000,200000,3200000
E005,정하늘,재무부,3800000,400000,4200000
E006,강바다,영업부,3300000,450000,3750000
E007,윤별빛,개발부,4200000,700000,4900000
E008,임구름,마케팅부,3100000,250000,3350000
E009,조나무,재무부,3600000,350000,3950000
E010,한산들,인사부,2900000,180000,3080000
E011,신바위,영업부,3400000,480000,3880000
E012,오하늘,개발부,4100000,650000,4750000전체 급여 합계:
=SUBTOTAL(9, F2:F13)
첫번째 인수 9 는 SUM 함수를 사용하고, 범위는 급여 범위 F2:F13 이에요.
실무 포인트: 특정 부서 직원만 보고 싶을 때 부서 컬럼을 필터링하면 해당 부서의 급여 합계만 자동으로 계산돼요!
예제 2: 중급 활용 - 재고 관리 시스템
창고 관리자가 재고 현황을 파악할 때 사용하는 실무 예제예요:
품목코드,상품명,분류,현재고,안전재고,단가,재고금액
ITM001,프린터용지,사무용품,2500,500,800,2000000
ITM002,볼펜,사무용품,1200,300,500,600000
ITM003,모니터,전자제품,85,20,350000,29750000
ITM004,노트북,전자제품,45,10,800000,36000000
ITM005,책상,가구,25,5,150000,3750000
ITM006,의자,가구,40,8,80000,3200000
ITM007,복합기,전자제품,12,3,1200000,14400000
ITM008,파일박스,사무용품,800,200,2000,1600000
ITM009,캐비닛,가구,18,4,250000,4500000
ITM010,키보드,전자제품,150,30,45000,6750000
ITM011,마우스,전자제품,200,50,25000,5000000
ITM012,화이트보드,사무용품,30,10,35000,1050000ㄱ. 전체 재고 금액: 첫번째 인수 9 일때와 같지만, 숨기기된 행이 있다면, 9는 숨기기 포함/109는 숨기기 제외
=SUBTOTAL(109, G2:G13)ㄴ. 분류 "사무용품" 품목만 필터링 후 "안전재고" 수량 평균:
=SUBTOTAL(101, E2:E13)
A1:G13 범위를 선택하고 필터링(단축키: CTRL+SHIFT+L) 후 "사무용품" 만 선택, AVERAGE 함수를 바로 사용하면 필터링이 적용되지 않아요. 숨겨진 값들까지 모두 포함하는 거지요. 그래서, SUBTOTAL 함수를 사용하는 거에요.
예제 3: 다양한 집계 함수 활용 (고급)
필터링된 데이터에서 여러 통계값을 한 번에 구하기:
지역,판매원,월매출,분기목표,달성률
서울,김철수,2500000,7500000,33.3
부산,이영희,3200000,9000000,35.6
대구,박민수,1800000,6000000,30.0
인천,최수지,2900000,8500000,34.1
광주,정하늘,2100000,7000000,30.0
대전,강바다,2600000,8000000,32.5
울산,윤별빛,1900000,6500000,29.2
세종,임구름,2400000,7200000,33.3
경기,조나무,3100000,9500000,32.6
강원,한산들,1700000,5800000,29.3
충북,신바위,2300000,7100000,32.4
충남,오하늘,2800000,8400000,33.3ㄱ. 최고 월매출: MAX 함수의 기능을 하는 인수번호 104
=SUBTOTAL(104, C2:C13)
ㄴ. 최저 월매출: MIN 함수의 기능을 하는 인수번호 105
=SUBTOTAL(105, C2:C13)ㄷ. 판매원 수: COUNTA 함수의 기능을 하는 인수번호 103
=SUBTOTAL(103, B2:B13)
위 그림의 지역만 필터링하면 결과는 6 , 하지만 =COUNTA(B2:B13) 의 결과는 보이지 않는 셀 수까지 계산, 그래서 12.
수시로 조건이 바뀌고 필터링 적용이 필요할 때는 SUBTOTAL 함수를 사용합니다.
이렇게 SUBTOTAL 함수는 실시간 데이터 분석이 필요한 모든 업무에서 활용할 수 있는 정말 강력한 도구예요! 💪
⚠️ SUBTOTAL 함수 사용 시 주의사항
1. 텍스트 데이터 처리
=SUBTOTAL(109, A1:A10) // 텍스트가 섞여 있으면 0으로 처리숫자가 아닌 텍스트나 공백은 0으로 처리되니 주의하세요.
2. 잘못된 집계함수번호
=SUBTOTAL(0, A1:A10) // 오류 발생유효하지 않은 집계함수번호를 사용하면 #VALUE! 오류가 발생해요.
🔧 오류 해결 방법
#VALUE! 오류
- 집계함수번호가 1-11, 101-111 범위에 있는지 확인
- 범위가 올바르게 지정되었는지 확인
#REF! 오류
- 참조하는 셀이 삭제되었는지 확인
- 범위가 유효한지 확인
예상과 다른 결과
- 숨겨진 행 포함 여부 확인 (1-11번 vs 101-111번)
- 필터링 상태 확인
🔗 관련 함수들
SUBTOTAL 함수와 유사하거나 함께 사용하면 좋은 함수들이에요:
- AGGREGATE: SUBTOTAL보다 더 많은 집계 함수 지원
- SUMIF/SUMIFS: 조건부 합계
- AVERAGEIF/AVERAGEIFS: 조건부 평균
- COUNTIF/COUNTIFS: 조건부 개수
- FILTER: 조건에 맞는 데이터 필터링 (Office 365)
📝 마무리
SUBTOTAL 함수는 필터링된 데이터를 자동으로 인식해서 계산해주는 정말 유용한 함수예요. 특히 대용량 데이터를 다루거나 동적 대시보드를 만들 때 없어서는 안 될 기능이죠!
여러분도 이제 SUBTOTAL 함수를 활용해서 더 효율적인 엑셀 작업을 해보세요! 💪
이 글이 도움이 되셨다면 공감과 공유 부탁드려요! 궁금한 점이 있으시면 언제든지 댓글로 물어보세요~ 😊
작성자: 마늘빵
📱 모바일용 빠른 학습 가이드
SUBTOTAL 함수 핵심 정리
🎯 용도
- 필터링된 데이터 자동 집계
- 숨겨진 행 제외 계산
📋 기본 문법
=SUBTOTAL(집계번호, 범위)💡 자주 사용하는 번호
- 합계: 9(숨기기된 셀 계산포함) / 109(제외)
- 평균: 1(포함) / 101(제외)
- 개수: 3(포함) / 103(제외)
⚡ 빠른 팁
- 101-111번 = 숨겨진 행 제외
- 1-11번 = 수동으로 숨긴 행 포함
- 필터링 시 자동 재계산
❌ 주의사항
- 텍스트는 0 으로 처리
- 함수 중첩 사용 시 중복 주의
- 올바른 집계번호 사용
