이번 포스팅에서는 엑셀 subtotal 함수란 무엇인지 sum 함수와의 차이에 대해 자세히 살펴보고, 엑셀 subtotal 함수 사용 방법에 대해 구체적으로 정리해보았습니다.
엑셀은 데이터 입력, 관리, 분석에 있어 굉장히 강력한 기능이 있는 스프레드시트 프로그램입니다. Microsoft Office의 구성 요소 중 하나로, 단순히 표를 그리거나 계산식을 입력하는 용도를 넘어 다양한 기능들을 제공합니다. 특히 기초적인 자료 분석과 통계 작업에 적합하여, 직장인부터 학생, 연구자까지 많은 사람들이 업무나 과제, 프로젝트 등에서 활용도가 높습니다.
엑셀은 다루어야 하는 데이터의 양이 커질수록 한눈에 모든 정보를 파악하기 어려워질 수 있다는 단점이 있습니다. 예를 들어 수백에서 수천 건 이상의 거래내역, 재고 목록, 고객정보 등을 한 번에 처리하려고 하면, 한 화면에 그 모든 데이터를 띄우기도 쉽지 않습니다. 이처럼 대량의 데이터를 관리할 때 기본적으로 많이 쓰이는 기능 중 하나가 바로 합계(sum) 계산입니다.
합계를 구할 때 가장 대표적으로 사용되는 함수가 SUM 함수입니다. 그러나 조금 더 구체적인 용도로, 예를 들면 특정 열의 합계를 구하되 숨겨진 행(row)은 제외하고 싶다거나, 필터가 적용된 상태에서만 합계를 구하고 싶은 경우 등이 있을 수 있습니다. 이러한 상황에서는 SUBTOTAL 함수를 사용하면 보다 편리하게 결과를 얻을 수 있습니다. SUBTOTAL 함수는 Excel의 부분합 기능과 밀접하게 연결되어 있으며, 숨겨진 행을 무시하거나 필터가 적용된 결과만을 계산하는 등 여러 가지 장점과 특징을 지니고 있습니다.
이번 포스팅에서는 이러한 엑셀 SUBTOTAL(부분합) 함수에 대해 구체적으로 알아보고, SUM 함수와 어떤 점에서 다른지, 그리고 실제로 어떻게 사용하면 되는지 예시와 함께 정리해보겠습니다.
엑셀 SUBTOTAL 함수란? SUM과의 차이점
엑셀 SUBTOTAL 함수는 엑셀에서 부분합을 구할 때 사용하는 함수로, 표나 데이터 범위에서 그룹별 합계를 빠르게 계산하거나, 숨겨진 행을 계산에서 제외하고 싶을 때 유용하게 사용할 수 있습니다.
또한, 단순 합계뿐만 아니라 평균, 개수, 최대값, 최소값 등 여러 통계 함수의 기능을 통합적으로 제공하는 것이 특징입니다.
엑셀에서 SUBTOTAL 함수는 다음과 같이 표기합니다.
SUBTOTAL(function_num, ref1, [ref2], …)
- function_num: 어떤 통계 계산을 할 것인지 지정하는 숫자(1부터 11, 101부터 111까지)가 들어갑니다. 예를 들어 1은 평균(AVERAGE), 2는 개수(COUNT), 9는 합계(SUM)에 해당합니다.
- ref1, ref2, …: 부분합을 적용할 범위를 지정합니다.
엑셀 SUBTOTAL 함수 언제 사용할까요?
- 필터 적용 후 보이는 값만 계산하고 싶을 때
필터를 걸어서 특정 조건에 해당하는 행만 남겨두었는데, 일반적인 SUM 함수로 합계를 구하면 숨겨진 행까지 모두 포함되어 계산될 때가 있습니다. 이럴 때 엑셀 SUBTOTAL 함수를 사용하면 필터링된 데이터에 대해서만 합계, 평균 등을 낼 수 있습니다. - 숨겨진 행(행 높이를 수동으로 숨김) 등을 제외하고 계산하고 싶을 때
데이터를 정리하다 보면, 불필요한 행을 숨기고 싶은 경우가 있습니다. 이 상태에서 합계를 구했을 때 숨긴 행의 데이터가 합산에 포함되지 않도록 하고 싶다면 엑셀 SUBTOTAL 함수를 이용합니다. - 다양한 통계 기능을 단일 함수로 간편하게 적용하고 싶을 때
엑셀 SUBTOTAL 함수는 합계(SUM)뿐만 아니라 평균(AVERAGE), 개수(COUNT), 최대(MAX), 최소(MIN), 표준편차(STDEV) 등 다양한 기능을 지원합니다. 하나의 함수로 여러 가지 통계 작업을 손쉽게 구현할 수 있습니다.
SUM과 무엇이 다를까요?
- 숨겨진 행 처리 여부
SUM 함수는 기본적으로 범위에 포함된 모든 셀을 합산합니다. 만약 일부 데이터를 숨기거나 필터링해도 그 값들이 완전히 제거되는 것이 아니기 때문에 계산에서 제외되지 않습니다.
반면에 SUBTOTAL 함수는 설정에 따라 숨겨진 행이나 필터링된 데이터를 제외하고 계산할 수 있습니다. - 다른 통계 연산 가능
SUM 함수는 합계만을 구하지만, SUBTOTAL 함수는 지정 값(function_num)에 따라 합계 외에도 평균, 개수 등 여러 결과를 얻을 수 있습니다.
엑셀 SUBTOTAL 사용 방법
이제 실제 예시를 통해 엑셀 SUBTOTAL 함수를 어떻게 사용하는지 구체적으로 살펴보겠습니다. 가령 아래와 같이 가상의 판매 데이터 테이블을 하나 만들어보겠습니다. 시트의 A열부터 C열까지 아래와 같은 데이터가 있다고 가정합니다.
| A열(상품명) | B열(판매량) | C열(판매단가) |
|---|---|---|
| 사과 | 10 | 1,000 |
| 바나나 | 20 | 2,000 |
| 바나나 | 5 | 1,800 |
| 배 | 10 | 3,000 |
| 사과 | 8 | 1,200 |
| 사과 | 12 | 1,100 |
| 배 | 3 | 2,800 |
| 바나나 | 15 | 2,500 |
(1) 총 판매량 합계 – 일반 SUM
먼저 C열(판매단가)은 무시하고, B열(판매량)에 대해서 일반적인 합계를 구한다고 해봅시다. 가장 간단한 방식은 예를 들어 B9 셀에 다음과 같이 입력하는 것입니다.
=SUM(B2:B9)이렇게 하면 판매량의 총합(10+20+5+10+8+12+3+15=83)이 계산됩니다. 그러나 이 합계는 숨기거나 필터링을 했을 때도 변동 없이 83을 계속 보여줄 것입니다.
(2) SUBTOTAL 함수로 합계 구하기
동일한 위치(B9 셀)에 엑셀 SUBTOTAL 함수를 활용하여 합계를 구해보겠습니다. 합계를 구할 때의 function_num값은 9이므로, 다음과 같이 입력합니다.
=SUBTOTAL(9, B2:B9)결과는 마찬가지로 83이 됩니다. 하지만 이 함수는 몇 가지 이점이 있습니다. 예를 들어 특정 상품만 필터링했을 때, 그 상품들의 판매량만 자동으로 계산해주거나, 숨겨진 행을 자동으로 제외하여 계산할 수 있다는 것입니다.
(3) 필터 적용 후 엑셀 SUBTOTAL 함수 확인하기
실제 엑셀 SUBTOTAL 함수의 활용도를 확인하기 위해 데이터를 필터링해보겠습니다.
- 먼저 A1:C1 영역을 선택한 뒤 상단 메뉴에서 데이터 → 필터를 적용합니다.
- A열(상품명) 셀에서 드롭다운 화살표를 클릭하여 “사과”만 체크합니다. (또는 아래 이미지와 같이 사과만 나타나도록 필터 설정을 합니다.)
그러면 테이블에서 사과 행(A열이 ‘사과’)만 화면에 나타납니다. 이 상태에서 B11 셀의 SUBTOTAL 함수 결과를 보면, 자동으로 사과의 판매량만 합산된 결과가 표시됩니다. 예시 데이터를 기준으로 하면 사과는 (10 + 8 + 12) = 30이므로, 엑셀 SUBTOTAL 함수는 숨겨진 행(바나나, 배)을 제외한 30을 표시하게 됩니다.
만약 SUM 함수를 사용했다면 아무리 화면에서 사과만 남도록 필터링했어도 여전히 83이 표시될 것입니다. 이것이 SUM과 SUBTOTAL의 가장 큰 차이점 중 하나입니다.
(4) 숨기기(행 높이 숨김) 후 SUBTOTAL 함수 확인하기
필터가 아닌 다른 방식으로 행을 숨겨보겠습니다. 예를 들어 B4 셀에 해당하는 배(판매량 10) 행 전체를 선택하고 우클릭 → 숨기기를 해봅니다. 필터가 아닌 일반 숨기기 작업을 한 것입니다. 마찬가지로 B11 셀의 SUBTOTAL 함수 결과가 어떻게 바뀌는지 확인해봅니다.
- 숨기기 전 SUBTOTAL = 83
- B4 행을 숨긴 후 결과를 확인하면, 숨겨진 행을 제외한 나머지 판매량만 더해 표시됩니다.
단, SUBTOTAL 함수의 function_num 선택에 따라 숨긴 행 포함 여부가 달라질 수 있습니다. 예를 들어 9 대신 109를 사용하면 숨겨진 행을 제외하고 계산할 수 있고, 9를 사용하면 수동으로 숨긴 행도 계산에 포함될 수 있습니다.
아래는 주로 사용되는 SUBTOTAL의 function_num 예시입니다.
| function_num | 통계 함수 | 숨김 처리 방식 |
|---|---|---|
| 1 또는 101 | AVERAGE | 평균 |
| 2 또는 102 | COUNT | 개수(숫자) |
| 3 또는 103 | COUNTA | 개수(숫자+문자) |
| 4 또는 104 | MAX | 최대값 |
| 5 또는 105 | MIN | 최소값 |
| 9 또는 109 | SUM | 합계 |
- 1~11: 필터로 숨겨진 행은 제외하지만, 수동으로 숨긴 행(행 높이를 0으로 숨김 등)은 포함하여 계산
- 101~111: 필터로 숨겨진 행 + 수동으로 숨긴 행 모두 제외하여 계산
이 부분은 본인이 원하는 목적에 맞춰서 수치를 선택하면 되겠습니다. 만약 “필터로 숨긴 행 + 수동 숨긴 행 전부 계산에서 빼겠다”고 하면 109, “필터로 숨긴 행만 빼고 수동 숨긴 행은 포함하겠다”고 하면 9를 사용하면 됩니다.
SUBTOTAL 함수를 이용한 빠른 통계
SUBTOTAL 함수를 합계로만 사용할 수도 있지만, 평균이나 개수 등을 구할 때도 동일한 형태로 사용할 수 있습니다. 예를 들어 “이 테이블에서 필터를 적용했을 때의 평균 판매량만 알고 싶다”라고 한다면:
=SUBTOTAL(1, B2:B8)이처럼 function_num 자리에 1을 넣으면 AVERAGE(평균) 함수가 적용되어, 필터로 숨겨진 행을 제외한 나머지 행들의 평균이 계산됩니다.
참고로 마이크로소프트 지원 페이지에서도 엑셀 SUBTOTAL 함수 사용법에 대해 가볍게 안내하고 있습니다.
이번 글에서는 엑셀에서 자주 쓰이지만 은근히 헷갈릴 수 있는 엑셀 SUBTOTAL 함수에 대해 살펴보았습니다. 간단히 정리하자면 다음과 같습니다.
- SUBTOTAL 함수는 합계뿐만 아니라 평균, 개수, 최대/최소 등 여러 통계 함수를 통합적으로 제공하는 함수입니다.
- 숨겨진 행이나 필터링된 결과를 계산에서 배제하거나 포함할 수 있어, 대량의 데이터에서 필요한 부분만 계산할 때 매우 유용합니다.
- SUM 함수와 달리, 필터 또는 수동 숨김 행을 무시하여 실제 화면에 보이는(또는 조건에 맞는) 데이터만 계산할 수 있습니다.
- function_num의 범위를 잘 이해하면, 필터로 숨긴 행만 빼거나 수동 숨김 행까지 모두 빼는 등 원하는 방식으로 부분합을 구할 수 있습니다.
엑셀 작업에서 ‘합계’라는 기능은 가장 기초적이지만 동시에 가장 자주 사용하는 기능입니다. 필요에 따라 SUBTOTAL을 함께 활용하면 필터링된 데이터나 숨겨진 행을 제외하여 정확한 통계를 손쉽게 낼 수 있습니다.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
[Excel 꿀팁] 엑셀 pdf 저장 변환 3가지 방법 3초 해결 (페이지 잘림 해결)
Excel 엑셀 틀 고정 인쇄 방법 1분 해결 (행열 틀고정 반복 프린트 설정법)