엑셀 부분합 기능 — 그룹별 소계를 자동으로 만드는 법
재무팀 마감 때 제가 제일 자주 받는 질문 중 하나가 이거예요. “필터 걸었는데 왜 합계가 그대로예요?” 화면에는 서울 지사만 보이는데, 맨 아래 숫자는 전국 합계가 찍혀 있거든요. 보고서를 그대로 올렸다가 부장님한테 “이거 전체 아니에요?” 한마디 듣고 40분을 다시 쓴 적이 있습니다.
원인은 거의 항상 SUM이에요. SUM은 숨긴 행·필터된 행까지 다 더합니다. 필터 화면에 맞는 합계를 원하면 SUBTOTAL을 써야 해요. 숫자 하나가 보고서 신뢰를 갈라요. 오늘은 function_num 표, 7단계 따라하기, 그룹 윤곽, 피벗·표 합계 행과 역할을 나눠서 정리합니다.
이 글을 따라 하면 얻는 것
– SUBTOTAL function_num 1~11 / 101~111 선택 기준
– 필터된 매출 표 7단계 따라하기
– 그룹 윤곽·소계 행 실무 팁
– SUM·피벗·SUMIFS와 언제 무엇을 쓸지 비교
따라 할 예제 데이터 (지사·월별 매출)
원본 시트 A1:D13에 아래 표를 넣으세요.
| A: 지사 | B: 월 | C: 담당 | D: 매출(만원) |
|---|---|---|---|
| 서울 | 1월 | 김대리 | 1200 |
| 서울 | 2월 | 김대리 | 980 |
| 서울 | 3월 | 이과장 | 1100 |
| 부산 | 1월 | 박대리 | 750 |
| 부산 | 2월 | 박대리 | 820 |
| 부산 | 3월 | 최대리 | 690 |
| 대구 | 1월 | 정대리 | 540 |
| 대구 | 2월 | 정대리 | 610 |
| 대구 | 3월 | 한대리 | 580 |
| 서울 | 1월 | 김대리 | 1050 |
| 부산 | 2월 | 박대리 | 900 |
| 대구 | 3월 | 한대리 | 620 |
D열 합계를 필터로 지사별로 볼 때 SUM과 SUBTOTAL 차이를 바로 확인할 수 있어요.
1. SUBTOTAL이 필요한 순간 — 보이는 행만 더하기
SUBTOTAL(함수번호, 범위)는 부분합 함수입니다. Microsoft SUBTOTAL 함수 문서도 “목록·데이터베이스의 부분합”으로 정의해요.
핵심은 두 가지예요.
- 자동 필터·표 필터로 숨긴 행은 빼고 계산 (function_num 101~111 계열)
- 같은 열에 이미 SUBTOTAL이 있으면 중복 합산에서 제외 — 소계 행 여러 개를 한 번에 합칠 때 유리
반대로 SUM(D2:D13)은 필터로 서울만 남겨도 12행 전체를 더합니다. 화면과 숫자가 어긋나는 전형적인 패턴이에요.
강의 때 이 장면을 보여주면 반응이 확 옵니다. 필터 켠 채로 SUM 셀을 더블클릭해 보면, 파란 테두리가 숨긴 행까지 잡혀 있다는 걸 눈으로 확인하거든요.
2. 30초 진단 — 지금 SUM을 써야 하나, SUBTOTAL인가
보고서 합계 셀을 클릭했을 때 아래에 해당하면 SUBTOTAL 후보예요.
| 증상 | 원인 추정 | 바로 시도 |
|---|---|---|
| 필터 켰는데 합계가 안 줄어듦 | SUM이 숨긴 행까지 합산 |
SUBTOTAL(109, 범위)로 교체 |
| 소계 행 여러 개를 한 번에 합침 | SUM이 소계까지 이중 합산 |
상위 합계도 SUBTOTAL(109) |
| 표 합계 행과 수동 합계가 다름 | SUM·SUBTOTAL 혼용 | 하나만 남기기 |
| 필터는 맞는데 #VALUE! | D열에 텍스트 숫자 | VALUE 오류 먼저 |
진단은 30초면 끝나요. 필터 켠 상태에서 합계 셀을 더블클릭해 파란 범위가 숨긴 행까지 가리키면, 거의 확정입니다.
3. function_num 한눈에 — 9와 109의 차이
첫 번째 인수가 헷갈리는 사람이 많아요. 표로 정리합니다.
| 번호 | 의미 | 숨긴 행(필터·수동) | 보통 쓰는 상황 |
|---|---|---|---|
| 1 / 101 | AVERAGE | 1·101 = 포함 / 101 = 제외 | 필터 평균 |
| 9 / 109 | SUM | 9 = 포함 / 109 = 제외 | 필터 합계 ← 가장 많이 씀 |
| 2 / 102 | COUNT | 개수 | 건수 |
| 5 / 105 | MAX | 최댓값 | 필터 후 최고 매출 |
| 4 / 104 | MAX | (동일 계열) | 필터 후 최댓값 |
| 6 / 106 | MIN | 최솟값 | 필터 후 최저 매출 |
기억법: 끝자리가 1이면 “보이는 것만” (101, 109, 102…). 9는 SUM이지만 숨긴 행 포함이라 필터 화면과 안 맞을 때가 많아요.
function_num 전체 참고 (자주 쓰는 것만)
| 번호 | 함수 | 번호 | 함수 |
|---|---|---|---|
| 1 / 101 | AVERAGE | 9 / 109 | SUM |
| 2 / 102 | COUNT | 10 / 110 | VAR |
| 3 / 103 | COUNTA | 11 / 111 | VARP |
| 4 / 104 | MAX | 5 / 105 | MIN |
| 6 / 106 | PRODUCT | 7 / 107 | STDEV |
| 8 / 108 | STDEVP |
마감 보고서에서 90%는 109예요. 평균 단가를 필터 반영하려면 SUBTOTAL(101, 범위), 건수는 SUBTOTAL(102, 범위). 나머지는 필요할 때 표에서 찾아 쓰면 됩니다.
실무에서는 매출 합계에 SUBTOTAL(109, D2:D13)을 거의 고정으로 씁니다. 평균이면 101, 건수면 102. 저도 처음엔 9를 썼다가 필터 테스트에서 합계가 안 바뀌어서 한참 헤맸어요.
=SUBTOTAL(109, D2:D13)
지사 열에 자동 필터(Ctrl+Shift+L)를 걸고 서울만 남기면, 위 수식 결과가 서울 매출만 반영됩니다. FILTER·동적 배열로 목록을 뽑는 방식과도 비교해 보세요.
4. 필터된 표에 SUBTOTAL 넣기 — 7단계
- A1:D13 범위 선택 →
Ctrl+T로 표 만들기 (선택 사항이지만 필터·합계 행과 궁합이 좋음) - D14 빈 셀에
=SUBTOTAL(109, D2:D13)입력 (표면매출[매출]구조적 참조도 가능) - A1 머리글에서 자동 필터 켜기
- 지사에서 서울만 체크 → D14 값이 줄어드는지 확인
- 부산만 선택해 다시 확인
- 필터 해제 후 전체 합계와 일치하는지 비교
- 보고서 시트에는 D14를 링크만 걸어 두고, 원본에서 필터만 바꿔 쓰기
7단계에서 수강생이 “표 합계 행이 있는데 또 SUBTOTAL을 쓰나요?”라고 물으면, 표 합계 행도 내부적으로 SUBTOTAL을 씁니다. 직접 쓰는 이유는 합계 위치·여러 블록을 내가 통제하고 싶을 때예요.
4-1. SUM과 나란히 두고 비교해 보기 (5분 실습)
E14에 =SUM(D2:D13), F14에 =SUBTOTAL(109, D2:D13)을 동시에 넣어 보세요. 필터 없을 때는 두 값이 같습니다. 지사에서 서울만 남기면 F14만 줄어듭니다. 이 차이를 한 번 보면 “왜 보고서에 SUBTOTAL이냐”는 질문이 사라져요.
| 상태 | E14 (SUM) | F14 (SUBTOTAL 109) |
|---|---|---|
| 필터 없음 | 전체 합 | 전체 합 (동일) |
| 서울만 | 전체 합 (변화 없음) | 서울만 합 |
| 부산+대구 | 전체 합 | 부산+대구만 합 |
저는 강의실 프로젝터에 이 표를 띄운 뒤, 필터를 바꿀 때마다 E열은 가만히 있고 F열만 움직이는 걸 보여줍니다. “아, SUM은 눈속임이었네요” 하는 순간이 SUBTOTAL을 기억하는 타이밍이에요.
4-2. 이름 정의로 범위 고정하기
매출 데이터가 아래로 늘어나면 D2:D13을 매번 수정해야 합니다. 표로 만들었다면 매출[매출] 구조적 참조가 낫고, 일반 범위면 이름 정의 매출열 = 원본!$D$2:$D$500 후 =SUBTOTAL(109, 매출열)을 쓰세요. 새 행을 중간에 넣어도 합계 범위를 덜 건드립니다.
5. 그룹 윤곽·소계 행 — 부서별 한 화면에
지사·월처럼 계층이 있는 긴 표는 데이터 탭 → 윤곽 → 그룹화로 묶을 수 있어요.
- 정렬: A열 지사 → B열 월 순
- 데이터 → 윤곽 → 그룹화 (열 기준 또는 행 기준 — 버전에 따라 “자동 윤곽”이 빠를 때도 있음)
- 왼쪽
+/-로 접었다 펼치기 - 각 그룹 아래 소계가 필요하면
SUBTOTAL(109, …)행을 그룹마다 삽입
윤곽 소계와 피벗테이블의 차이는 원본 레이아웃 유지 여부예요. 윤곽은 같은 시트·같은 열 구조를 유지한 채 접기만 합니다. 피벗은 필드를 끌어다 놓는 새 집계 화면이죠.
제 경험상 “부장님이 원본 표 양식을 고집”하면 윤곽+SUBTOTAL, “탐색적 분석”이면 피벗이 더 빨리 끝납니다.
5-2. 윤곽 전 정렬이 소계를 깔끔하게 만든다
그룹화 전에 지사 → 월 → 담당 순으로 정렬하지 않으면, 같은 지사 행이 흩어져 소계 범위를 손으로 잡아야 합니다. 데이터 → 정렬에서 여러 수준을 지정한 뒤 윤곽을 걸면, +/-만으로 보고서 단위가 맞아요. 정렬을 건너뛰고 윤곽만 켰다가 “소계가 이상해요”라고 오는 경우가 강의에서도 자주 있습니다.
5-3. 피벗테이블로 넘어가야 할 때
다음 중 두 가지 이상이면 피벗을 검토하세요.
- 지사·월·담당·품목처럼 축이 3개 이상이고 매번 조합을 바꿈
- 같은 원본으로 여러 보고서 레이아웃을 주말마다 새로 만듦
- SUBTOTAL 소계 행이 20개를 넘어 범위 관리가 버거움
반대로 “양식은 이 표 그대로, 합계만 필터에 맞게”면 SUBTOTAL이 유지보수 비용이 낮아요. 엑셀 필수 함수 TOP 20 허브에서 집계 함수 전체 흐름도 같이 보시면 선택이 빨라집니다.
5-1. 지사별 소계 + 전체 합계 한 번에
같은 열에 소계 행이 여러 개일 때 SUBTOTAL의 두 번째 특징이 빛납니다. 지사마다 SUBTOTAL(109, …)을 넣고, 맨 아래 전체 행에도 SUBTOTAL(109, D2:D20)처럼 소계 행까지 포함한 범위를 주면, Excel이 중간 소계를 이중으로 더하지 않아요.
서울 소계 =SUBTOTAL(109, D2:D4)
부산 소계 =SUBTOTAL(109, D5:D7)
...
전체 합계 =SUBTOTAL(109, D2:D13) ← 소계 행이 없을 때
소계 행을 삽입한 구조라면, 전체 합계 범위에 소계 셀을 포함해도 SUBTOTAL끼리는 겹치지 않습니다. SUM으로 같은 구조를 만들면 손으로 범위를 쪼개야 해서 실수가 늘어요.
6. SUBTOTAL vs SUM vs 피벗 vs SUMIFS — 선택 기준
| 방법 | 장점 | 단점 | 추천 상황 |
|---|---|---|---|
SUM |
단순 | 필터·숨김 반영 안 함 | 전체 고정 합계 |
SUBTOTAL(109,…) |
필터·숨김 반영, 소계 중복 방지 | function_num 헷갈림 | 같은 표에서 필터 합계 |
| 피벗테이블 | 드래그 집계, 다차원 | 원본 양식과 다름 | 탐색·보고용 재가공 |
| SUMIFS | 조건 열 고정 | 필터 UI 없음 | 조건이 수식으로 고정될 때 |
| 표 합계 행 | 클릭 한 번 | 위치·열 제한 | 단일 표 하단 합계 |
“필터로 눈으로 확인하면서 합계” → SUBTOTAL. “지사=서울 AND 월=1월을 수식으로” → SUMIFS. “매달 같은 보고서 레이아웃” → 피벗 또는 윤곽.
세 가지를 섞지 마세요. 같은 열에 SUM 합계 행과 SUBTOTAL 합계 행이 동시에 있으면, 보고서 읽는 사람이 어느 숫자를 믿어야 할지 모릅니다.
6-1. 보고서 시트에 고정하는 패턴
원본 원본 시트에서 필터·SUBTOTAL을 관리하고, 보고 시트 D5에는 =원본!D14만 링크하는 방식을 씁니다. 부장님 보고용 파일은 보고 시트만 PDF로 저장하고, 원본은 숨겨 두면 필터 실수도 줄어요. 회사 실데이터가 들어간 통합 문서는 외부 AI·개인 메일에 올리지 마세요. 가명 예제로 연습한 뒤 사내 파일에 적용하는 게 안전합니다.
7. 자주 하는 실수 5개
- function_num 9 사용 — 필터 반영이 안 될 때가 많음. 109부터 테스트.
- 범위에 합계 행 포함 —
D2:D14처럼 소계 셀까지 범위에 넣으면 이중 계산. 데이터만D2:D13. - SUM과 SUBTOTAL 혼용 — 같은 보고 블록에 두 종류 합계. 하나로 통일.
- 표 합계 행 + 수동 SUBTOTAL 이중 — 표 기능 합계 행 켠 뒤 같은 열에 또 SUM 입력.
- 텍스트 매출 — ERP에서 받은 매출이 텍스트면
#VALUE!. 엑셀 #VALUE! 오류 글처럼*1또는 값 붙여넣기로 숫자화 후 SUBTOTAL.
네 번째 실수는 제가 강의 자료 만들다가 직접 겪었어요. 합계 행이 켜진 표에 제가 습관적으로 =SUM(매출)을 한 줄 더 넣었는데, 합계가 두 배로 보이는 줄 알고 20분 동안 필터를 의심했습니다. 표 디자인 탭에서 합계 행만 켜거나, 수동 SUBTOTAL만 쓰거나 — 하나만 선택하세요.
실수 예방 체크리스트 (저장 전 1분)
[ ] 합계 수식이 SUBTOTAL(109 또는 101·102)인가
[ ] 범위에 소계·합계 행이 섞이지 않았는가
[ ] 같은 블록에 SUM 행이 남아 있지 않은가
[ ] 필터 켠 상태에서 합계가 눈에 보이는 행과 일치하는가
[ ] 매출 열이 숫자 형식인가 (#VALUE! 없음)
마감 직전에 이 다섯 줄만 훑어도 “필터는 맞는데 숫자가 이상해요” 메일이 확 줄어요.
8. FAQ
Q. SUBTOTAL과 AGGREGATE 차이는?
AGGREGATE는 오류 셀·다른 소계 함수까지 더 세밀하게 무시할 수 있어요. 단순 필터 합계면 SUBTOTAL으로 충분하고, #DIV/0!가 섞인 열이면 AGGREGATE(9, 5, 범위)를 검토하세요.
Q. 여러 필터 조건(지사+월)에도 109면 되나요?
네. 자동 필터로 여러 열 조건을 걸어도, 숨긴 행은 109에서 제외됩니다. 조건을 수식으로 박아 두려면 SUMIFS가 낫습니다.
Q. 피벗테이블 없이 그룹 소계만 빠르게?
정렬 후 윤곽 → 그룹화가 제일 빠릅니다. 소계 숫자는 각 그룹 아래 SUBTOTAL(109, …) 한 줄이면 됩니다.
Q. 표 합계 행은 어떤 function_num을 쓰나요?
표 합계 행은 열 메뉴에서 합계·평균 등을 고르면 Excel이 SUBTOTAL을 넣어 줍니다. 필터와 함께 쓸 때도 보이는 행 기준이에요.
Q. SUBTOTAL 범위에 다른 통합 문서 참조를 넣어도 되나요?
가능은 하지만, 파일이 닫혀 있으면 결과가 달라질 수 있어요. 마감 보고서는 같은 통합 문서 안 범위를 권장합니다. 외부 링크 표는 Power Query로 끌어온 뒤 SUBTOTAL을 거는 편이 안전해요.
Q. 2016·2019·365에서 동작이 다른가요?
SUBTOTAL 자체는 오래된 버전부터 동일하게 지원됩니다. 차이는 윤곽·표·동적 배열 쪽이에요. 365면 FILTER로 뽑은 범위에 SUBTOTAL을 얹는 패턴도 가능하고, 2016은 필터+SUBTOTAL 조합이 가장 무난합니다. 파일을 아래 버전으로 저장해 배포할 때는 현장 PC에서 한 번 새로 고침해 보세요.
9. 한 줄 요약
필터·숨김 행을 반영한 합계는 SUBTOTAL(109, 범위). function_num 끝이 1인 101·109 계열이 “보이는 행만”. 피벗·SUMIFS·표 합계행과 역할을 나누고, 같은 블록에 SUM과 SUBTOTAL을 섞지 마세요. 다음에 비슷한 집계가 필요하면 SUMIFS 조건 합계 글도 이어서 보시면 됩니다.
10. 관련 글
- 엑셀 표(Table) 기능 — 합계 행·필터
- SUMIFS·COUNTIFS 다중 조건 — 수식 조건 합계
- FILTER·SORT·UNIQUE 동적 배열 — 365 필터 대안
- 엑셀 필수 함수 TOP 20 — 함수 허브
- 엑셀 #VALUE! 오류 — 텍스트 숫자 함정
바이라인: 전 대기업 재무팀 12년차 직무교육 강사 최지원 — 예제는 Excel Microsoft 365에서 직접 확인했습니다. 회사 실매출·거래처명은 가명 데이터입니다. SUBTOTAL(109)과 필터 조합은 2016 이상 공통으로 동일하게 동작했어요. 현장 PC에서도 꼭 한 번 검증하세요.
작성자 소개
