엑셀 시리즈 #20 함수 - SUBTOTAL로 자동 합계와 필터 합계 구분하기

엑셀 시리즈 #20 함수 - SUBTOTAL로 자동 합계와 필터 합계 구분하기

안녕하세요, 7옥타브고양이 입니다 😊 오늘은 실무에서 자주 사용하는 SUBTOTAL 함수를 통해 “필터를 걸었을 때 자동으로 합계가 바뀌는 보고서”를 만들어보겠습니다.

엑셀 시리즈 #20 함수 - SUBTOTAL로 자동 합계와 필터 합계 구분하기

---

1️⃣ SUBTOTAL 함수가 필요한 이유

사무직 실무에서는 매출표, 인사자료, 재고표 등 다양한 데이터를 다루게 되죠. 특히 “필터를 걸면 자동으로 해당 조건의 합계만 보고 싶다”는 요구가 많습니다.

보통 =SUM(B2:B100)으로 합계를 구하면, 필터로 숨긴 데이터도 포함되어 잘못된 값이 표시되죠.

이럴 때 바로 SUBTOTAL 함수를 사용하면 문제 해결! 숨겨진 행은 자동으로 제외하고, 보이는 데이터만 계산할 수 있습니다.

2️⃣ SUBTOTAL 함수 기본 구조

=SUBTOTAL(함수_번호, 범위)

함수_번호에 따라 어떤 계산을 할지 지정할 수 있습니다.

함수 번호	기능	예시
1	AVERAGE (평균)	=SUBTOTAL(1, B2:B10)
2	COUNT (숫자 개수)	=SUBTOTAL(2, B2:B10)
9	SUM (합계)	=SUBTOTAL(9, B2:B10)
101~111	숨긴 행 무시 버전	=SUBTOTAL(109, B2:B10)

Tip 💡 번호가 1~11인 경우 수동으로 숨긴 행도 계산되고, 101~111은 수동 숨김행도 제외됩니다. 실무에서는 보통 =SUBTOTAL(109,범위)를 많이 사용합니다.

3️⃣ 예제 데이터로 실습하기

첨부파일을 다운로드 받아 실습하세요 ^^

엑셀_시리즈_20_실무데이터.xlsx

0.01MB

아래와 같은 매출 집계표를 기준으로 예제를 진행하겠습니다 👇

지점	담당자	상품	매출액	월
서울	김하늘	노트북	2,500,000	1월
부산	이민수	키보드	380,000	1월
서울	박정우	마우스	250,000	2월
광주	최지연	노트북	2,200,000	2월
부산	이민수	모니터	1,100,000	3월
서울	김하늘	노트북	2,600,000	3월

4️⃣ 필터별 자동 합계 구하기

매출액 합계를 구하려면 단순히 =SUM(D2:D7)로 계산할 수 있지만, 이 상태에서는 필터를 걸어도 합계가 변하지 않습니다.

아래와 같이 SUBTOTAL 함수를 사용해 보세요 👇

=SUBTOTAL(9, D2:D7)

이제 “지점”을 서울만 필터하면, 서울 지점의 매출만 자동으로 합계됩니다.

지점 서울만 필터했을때 SUBTOTAL 결과

 

💡 추가 팁: 수동으로 숨긴 행까지 제외하고 싶다면 =SUBTOTAL(109, D2:D7) 사용!

반응형

5️⃣ 자동 보고서에 활용하는 방법

SUBTOTAL은 단순한 합계용이 아니라, “자동 보고서” 제작 시 함수 조합으로 매우 유용합니다.

예를 들어 다음과 같은 구조의 자동 합계 보고서를 만들 수 있습니다 👇

지점	합계
서울	=SUMPRODUCT(SUBTOTAL(9,OFFSET($D$2:$D$7,ROW($D$2:$D$7)-ROW($D$2),0,1)),--($A$2:$A$7=H2))
부산	=SUMPRODUCT(SUBTOTAL(9,OFFSET($D$2:$D$7,ROW($D$2:$D$7)-ROW($D$2),0,1)),--($A$2:$A$7=H3))
광주	=SUMPRODUCT(SUBTOTAL(9,OFFSET($D$2:$D$7,ROW($D$2:$D$7)-ROW($D$2),0,1)),--($A$2:$A$7=H4))

=SUMPRODUCT(SUBTOTAL(9,OFFSET($D$2:$D$7,ROW($D$2:$D$7)-ROW($D$2),0,1)),--($A$2:$A$7=H2))

SUBTOTAL 자동 보고서에 활용하는 방법

6️⃣ 실무에서 자주 쓰는 조합

• SUBTOTAL + FILTER: 필터 후 자동 합계 확인
• SUBTOTAL + DATE: 기간별 합계 자동 계산
• SUBTOTAL + OFFSET: 확장형 보고서 제작
• SUBTOTAL + INDIRECT: 여러 시트 합계 자동 연결

7️⃣ 마무리하며

SUBTOTAL 함수는 겉보기엔 단순하지만, 보고서 자동화의 기본이 되는 핵심 함수입니다.

특히 부서별 매출 보고서, 기간별 실적표, 필터형 요약표 등을 만드는 사무직에게는 반드시 익혀야 하는 함수이죠.

다음 포스팅에서는 엑셀 시리즈 #21 시각화 - 데이터 변경 시 자동 업데이트되는 차트 만들기을 이어가 보겠습니다.

© 7옥타브고양이