엑셀 SUBTOTAL 함수 완전정리: 업무 효율성을 높이는 활용법

 

 

 

엑셀에서 데이터를 관리하다 보면 합계, 평균, 개수 등을 계산할 일이 많습니다.

이때 일반 SUM이나 AVERAGE 함수를 사용하면 편리하지만, 필터링된 데이터에서는 불필요한 값까지 계산되는 문제가 있습니다.

 

이럴 때 유용한 함수가 바로 SUBTOTAL 함수입니다.

---

SUBTOTAL 함수란?

SUBTOTAL 함수는 엑셀에서 제공하는 범위 내 합계, 평균, 개수, 최대값, 최소값 등 다양한 통계값을 계산할 수 있는 함수입니다. 특히 필터링이나 숨김 행을 무시하고 계산할 수 있어 실무에서 매우 유용합니다.

 

기본 구조

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

• 함수번호: 어떤 계산을 할지 정하는 번호(1~11, 101~111)
  • 예: 1 = AVERAGE, 2 = COUNT, 9 = SUM
  • 1~11: 숨겨진 행 포함
  • 101~111: 숨겨진 행 제외
• 범위: 계산할 셀 범위

예를 들어, =SUBTOTAL(9, B2:B10)는 B2부터 B10까지 합계를 구합니다.

숨겨진 행이 있다면 포함 여부는 함수번호에 따라 달라집니다.

---

함수번호별 주요 예시

번호	의미
1 / 101	AVERAGE(평균)
2 / 102	COUNT(숫자 개수)
3 / 103	COUNTA(데이터 개수)
9 / 109	SUM(합계)
4 / 104	MAX(최댓값)
5 / 105	MIN(최솟값)

TIP: 숨겨진 행을 무시하려면 101~111 번호 사용, 필터링된 데이터에만 적용하려면 101~111을 꼭 활용하세요.

업무 활용 예제

( 1 ) 매출 합계 계산

회사에서 월별 매출 데이터를 관리한다고 가정합니다.

거래처	금액	상태
A사	500	정상
B사	700	정상
C사	300	취소
D사	400	정상

필터를 통해 '정상' 표시하고 합계를 계산하고 싶다면:

=SUBTOTAL(109, B2:B5)

• 필터에서 '정상'만 남기면 500 + 700 + 400 = 1600만 계산
• 취소 거래는 자동 제외

( 2 ) 평균 단가 계산

제품명	단가	카테고리
제품A	120	전자
제품B	150	전자
제품C	200	가전
제품D	180	전자

필터를 통해 '전자'  표시하고 평균단가 를 계산하고 싶다면:

 =SUBTOTAL(101, B2:B5)

• 설명:
  • 101번: 숨김 행 제외, 필터 적용된 데이터만 계산
  • 결과: (120 + 150 + 180) / 3 = 150

( 3 ) 회의 참석자 수 카운트

직원 출석 기록 관리 시, 휴가나 결근으로 숨긴 행을 제외하고 참석 인원만 계산 가능:

이름	출석상태
김민희	출석
박철수	결근
이영희	출석
최준호	휴가

 

필터를 통해 '출석' 표시하고 회의 참석사 수 를 계산하고 싶다면:

=SUBTOTAL(103, D2:D30)

• COUNTA(데이터 개수) 기능 활용
• 필터로 결근자 숨기면 자동 반영

( 4 ) 프로젝트 진행률 계산 예제

Gantt 차트나 일정표에서 특정 단계의 진행 현황 합계를 계산할 때, 필터로 완료 항목만 표시하고 SUBTOTAL로 합계/평균을 계산하면 진행률 관리가 효율적입니다.

작업명	상태	진행률 (%)
기획	완료	100
디자인	진행	50
개발	진행	30
테스트	예정	0

필터를 통해 "진행" 중 항목만 평균 진행률 계산하고 싶다면:

=SUBTOTAL(101, C2:C5)

• 방법: ‘진행’ 상태만 필터 후 계산
• 결과: (50 + 30) / 2 = 40%

---

SUBTOTAL 활용 꿀팁

1. 자동 합계보다 안전: 필터링 후에도 정확한 합계를 원하면 SUBTOTAL 사용
2. 숨김 행 고려: 101~111 번호로 숨김 행 제외 가능
3. 다중 범위 계산: =SUBTOTAL(9, B2:B10, B12:B20)처럼 여러 범위 합계 가능

 

엑셀 SUBTOTAL 함수는 단순히 합계를 내는 함수를 넘어 실무에서 필터링, 숨김, 부분 집계를 정확하게 관리할 수 있는 강력한 도구입니다.

 

매출, 프로젝트, 인원 관리 등 다양한 업무에서 SUBTOTAL 함수를 활용하면 데이터 관리 효율과 정확성을 동시에 잡을 수 있습니다.