Excel 사용자는 종종 필터나 그룹화에 반응하는 데이터 요약의 유연한 방법이 필요합니다. SUM(), AVERAGE(), COUNT()와 같은 전통적인 함수는 일부 행이 숨겨져 있거나 필터링되었는지 여부에 관계없이 범위 내 모든 셀을 기반으로 결과를 계산합니다.

수십 년 동안 SUBTOTAL() 함수는 현재 보이는 내용에 따라 계산을 자동으로 조정하는 동적 대안을 제공하는 핵심 솔루션이었습니다. 그러나 이를 숙달하려면 복잡한 구문과 함수 코드를 이해해야 합니다. 오늘날 현대적인 AI 도구는 동일한 결과에 이르는 훨씬 더 간단한 경로를 제공하여 수식 대신 평범한 언어를 사용할 수 있게 합니다.

이 가이드에서는 두 가지 방법을 모두 다룰 것입니다. 강력한 SUBTOTAL() 함수에 대한 완전한 분석을 제공한 다음, Excelmatic과 같은 AI 에이전트가 어떻게 훨씬 짧은 시간에 동일한 동적 계산을 달성할 수 있는지 보여드리겠습니다.

Excel에서 SUBTOTAL() 함수란 무엇인가요?

SUBTOTAL() 함수는 데이터 세트에 대한 합계, 평균, 개수 및 기타 통계 연산과 같은 집계 값을 계산합니다. 이를 표준 Excel 함수와 다르게 만드는 것은 동적으로 작동하는 방식입니다—현재 보이거나 필터링된 내용에 따라 결과를 변경합니다. 이 함수는 지정하는 함수 번호에 따라 수동으로 숨겨진 행과 필터를 통해 숨겨진 행을 모두 무시할 수 있습니다.

데이터에 필터를 적용하거나 특정 행을 수동으로 숨기면 SUBTOTAL()은 보이는 셀만 포함하도록 계산을 자동으로 조정합니다. 이 동작은 필터링된 데이터 세트에서 요약 행을 생성하거나, 대화형 대시보드를 구축하거나, 사용자 선택에 따라 업데이트해야 하는 보고서를 생성할 때 탁월한 선택이 됩니다.

이 함수는 합계, 평균, 개수, 최대값, 최소값 및 기타 여러 연산을 포함하여 Excel의 표준 함수와 동일한 통계 연산을 지원하지만, 이를 데이터의 현재 상태에 적응하는 단일의 상황 인식 함수로 패키징합니다.

구문 및 지원되는 연산

SUBTOTAL() 함수는 간단한 구문 구조를 따릅니다:

=SUBTOTAL(function_num, ref1, [ref2], ...)

첫 번째 매개변수인 function_num은 계산 유형과 함수가 숨겨진 행을 처리하는 방식을 모두 결정합니다. 나머지 매개변수(ref1, ref2 등)는 계산에 포함하려는 셀 범위입니다. 최대 254개의 서로 다른 범위를 지정할 수 있습니다.

함수 번호는 수동으로 숨겨진 행을 어떻게 처리하는지에 따라 두 가지 범주로 나뉩니다:

함수 번호	연산	수동 숨김 행 포함	수동 숨김 행 제외
1 / 101	AVERAGE	1	101
2 / 102	COUNT	2	102
3 / 103	COUNTA	3	103
4 / 104	MAX	4	104
5 / 105	MIN	5	105
6 / 106	PRODUCT	6	106
7 / 107	STDEV	7	107
8 / 108	STDEVP	8	108
9 / 109	SUM	9	109
10 / 110	VAR	10	110
11 / 111	VARP	11	111

함수 번호 1-11은 계산에 수동으로 숨겨진 행의 셀을 포함하는 반면, 함수 번호 101-111은 이를 제외합니다. 그러나 두 범위 모두 필터에 의해 숨겨진 행은 항상 무시합니다.

예를 들어, SUBTOTAL(9, A2:A10)은 수동으로 숨겨진 행을 포함하여 A2:A10의 합계를 계산하는 반면, SUBTOTAL(109, A2:A10)은 합계 계산에서 수동으로 숨겨진 행을 제외합니다. 두 경우 모두 필터링된 행은 결과에서 자동으로 제외됩니다.

동적 계산 실제 적용: 두 가지 방법

전자제품 및 가구 매장의 판매 데이터를 사용한 실제 예제를 통해 필터링된 데이터에 대한 요약을 계산하는 방법을 살펴보겠습니다.

샘플 데이터에는 두 가지 범주의 제품 7개가 포함되어 있습니다. 필터가 적용되지 않은 상태에서 SUBTOTAL(109,C2:C8) 수식과 일반 SUM(C2:C8) 수식 모두 동일한 결과를 표시합니다: 3525 (모든 판매의 합계).

이제 데이터를 필터링하여 "Electronics" 제품만 표시해 보겠습니다.

방법 1: 전통적인 SUBTOTAL() 수식

필터를 적용한 후 계산 결과의 주요 차이점을 확인하세요:

• SUBTOTAL 수식은 3000을 표시합니다 - 보이는 "Electronics" 행만 합산하도록 자동으로 조정됩니다.
• 일반 SUM 수식은 여전히 3525를 표시합니다 - 숨겨진 "Furniture" 항목을 계속 포함합니다.

이 나란한 비교는 SUBTOTAL()의 핵심 장점을 보여줍니다: 필터에 동적으로 반응하여 반응형 보고서에 필수적입니다.

방법 2: AI 기반 Excelmatic 접근법

수식을 작성하고 디버깅하는 대신, 간단히 질문함으로써 동일한 결과를 얻을 수 있습니다. Excelmatic과 같은 AI 에이전트를 사용하면 프로세스가 훨씬 더 간단합니다:

1. Excel 파일을 업로드합니다.

2. 평범한 언어로 질문합니다. 이 예제의 경우, 필터링 후 다음과 같이 질문할 수 있습니다:

   보이는 데이터에 대한 'Sales' 열의 합계는 얼마인가요?

Excelmatic은 즉시 보이는 데이터를 분석하고 답변을 제공합니다: 3000.

대조는 분명합니다: SUBTOTAL()은 올바른 구문과 함수 번호(9인가 109인가?)를 알아야 합니다. Excelmatic은 자연어에서 의도를 이해하여 수식 지식 없이도 동일한 정확하고 동적인 결과를 제공합니다.

SUBTOTAL()이 숨겨진 데이터와 필터링된 데이터와 함께 작동하는 방식

SUBTOTAL() 함수는 다양한 유형의 숨겨진 데이터를 특정 방식으로 처리하므로, 수식을 구축할 때 이러한 동작을 이해하는 것이 중요합니다.

필터링된 행과 함께 SUBTOTAL()

데이터 세트에 필터를 적용하면, SUBTOTAL()은 사용하는 함수 번호에 관계없이 계산에서 필터링된 행을 항상 제외합니다. 이 동작은 모든 함수 번호(1-11 및 101-111 범위 모두)에서 자동적이고 일관됩니다.

수동으로 숨겨진 행과 함께 SUBTOTAL()

수동으로 숨겨진 행은 선택한 함수 번호에 따라 다르게 처리됩니다. 행 번호를 마우스 오른쪽 버튼으로 클릭하고 "숨기기"를 선택하면 해당 행을 수동으로 숨기는 것입니다.

• 함수 번호 1-11을 사용하면 계산에 이러한 수동으로 숨겨진 행을 포함합니다.
• 함수 번호 101-111을 사용하면 이를 제외합니다.

이는 세밀한 제어를 제공하지만, 기억해야 할 또 다른 복잡성 계층입니다. 대조적으로, AI 도구는 일반적으로 시각적으로 표시된 내용을 기반으로 계산하거나, 프롬프트에서 "수동으로 숨겨진 행 포함"과 같이 지정하여 더 직접적인 제어를 할 수 있습니다.

수평 및 수직 범위와 함께 SUBTOTAL()

SUBTOTAL() 함수는 수직 데이터 범위(데이터 열)에서 가장 잘 작동합니다. 수평 범위를 처리할 수 있지만, Excel의 필터링 및 행 숨기기 기능이 수직으로 작동하기 때문에 숨김 동작은 행 기반 작업에 최적화되어 있습니다.

동일한 범위 내에 SUBTOTAL() 수식을 중첩하면, 함수는 이중 계산을 피하기 위해 다른 SUBTOTAL() 결과를 자동으로 건너뜁니다. 이는 계층적 요약에 유용합니다.

SUBTOTAL() 함수의 고급 사용법

SUBTOTAL()을 사용한 동적 드롭다운 요약 생성하기

SUBTOTAL()을 IF() 함수와 결합하여 대화형 요약을 생성할 수 있습니다. 이를 위해 먼저 드롭다운 목록(예: 셀 D1에)을 설정하고 "Sum", "Average", "Count" 등의 옵션을 포함합니다.

그런 다음, 계산을 변경하기 위해 복잡한 중첩 IF 수식을 작성합니다:

=IF(D1="Sum",SUBTOTAL(109,A2:A10),
  IF(D1="Average",SUBTOTAL(101,A2:A10),
  IF(D1="Count",SUBTOTAL(103,A2:A10),
  IF(D1="Max",SUBTOTAL(104,A2:A10),"Select Option"))))

AI 대안: Excelmatic을 사용하면 수식을 완전히 건너뜁니다. 각 계산에 대해 새로운 질문을 하기만 하면 됩니다:

• "보이는 행의 판매 합계는 얼마인가요?"
• "보이는 행의 평균 판매 가격은 얼마인가요?"
• "보이는 제품의 개수를 세세요."

이 대화형 접근 방식은 훨씬 더 직관적이며 설정이 전혀 필요하지 않습니다.

Excel 테이블 및 개요에서 SUBTOTAL()

Excel 테이블은 Total Row 기능에서 자동으로 SUBTOTAL()을 사용합니다. 데이터를 테이블로 변환하고(Ctrl+T) Total Row를 활성화하면 Excel은 기본적으로 SUBTOTAL(109, …)을 삽입하며, 이는 필터링할 때 동적으로 업데이트됩니다. 개요 기능(데이터 > 부분합)도 계층적 요약을 생성하기 위해 SUBTOTAL()에 의존합니다. 이들은 함수의 기능을 활용하는 강력한 내장 기능입니다.

SUBTOTAL()의 일반적인 함정과 한계

SUBTOTAL()의 한계를 이해하면 오류를 피하고 현대 도구가 더 나은 경험을 제공하는 부분을 강조하는 데 도움이 됩니다.

• 잘못된 함수 번호로 인한 #VALUE! 오류: 1-11 또는 101-111 범위를 벗어나는 함수 번호를 사용하면 #VALUE! 오류가 반환됩니다. 이는 보고서를 망가뜨릴 수 있는 일반적인 오타입니다.
• 숨겨진 열 대 숨겨진 행 동작: SUBTOTAL()은 항상 숨겨진 열의 데이터를 포함합니다. 행과 동일하게 작동할 것으로 예상한다면 예상치 못한 결과를 초래할 수 있습니다.
• 3D 참조 지원 안 됨: SUBTOTAL()은 여러 워크시트(Sheet1:Sheet3!A1:A10)에 걸친 범위를 참조할 수 없습니다. 이는 통합 보고서 작성에 큰 한계입니다. 그러나 Excelmatic은 여러 업로드된 파일이나 시트의 데이터를 동시에 분석할 수 있어 시트 간 분석을 간단하게 만듭니다.
• 계산에 오류 값이 남아 있음: SUBTOTAL()은 #N/A 또는 #DIV/0!와 같은 오류가 있는 셀을 무시하지 않으므로 전체 결과를 무효화할 수 있습니다. 먼저 데이터를 정리하거나 더 복잡한 AGGREGATE() 함수를 사용해야 합니다.
• 수직 데이터 방향이 가장 잘 작동함: 이 함수는 표준적인 열 기반 데이터를 위해 설계되었으며 수평 레이아웃에서는 예측할 수 없게 작동할 수 있습니다.

Subtotal() vs. AGGREGATE() vs. AI 에이전트

수년 동안 AGGREGATE() 함수는 SUBTOTAL()의 한계에 대한 해결책이었습니다. AGGREGATE()는 더 많은 함수(총 19개)를 제공하며, 결정적으로 오류 값을 무시하도록 구성할 수 있습니다.

• SUBTOTAL(): 깨끗하고 필터링된 데이터에 대한 기본적인 동적 계산에 적합합니다.
• AGGREGATE(): 오류가 있는 데이터 세트나 더 고급 통계 함수(예: MEDIAN 또는 PERCENTILE)가 필요할 때 더 좋습니다.
• AI 에이전트 (Excelmatic과 같은): 가장 간단하고 강력한 옵션입니다. 필터링된 데이터를 처리하고, 종종 오류를 자동으로 무시하며, 여러 시트에서 작동하며, 수식 구문이 필요하지 않습니다—단지 평범한 영어 질문만 필요합니다.

SUBTOTAL() 사용 시 전문가 팁

수식 경로를 선택한다면, 이 팁들이 성공하는 데 도움이 될 것입니다:

항상 먼저 정렬하기

Excel의 내장 데이터 > 부분합 기능을 사용할 때는 먼저 데이터를 그룹화 열로 정렬하여 깨끗한 그룹 구분을 보장하세요.

열의 첫 번째 행에 레이블 사용하기

설명적인 헤더는 보고서를 더 쉽게 해석하게 하고 Excel의 자동화된 도구가 올바르게 작동하도록 돕습니다.

요약 위치(위 또는 아래) 인식하기

Excel의 부분합 도구는 각 그룹 위나 아래에 요약을 배치할 수 있습니다. 보고서의 대상 독자에게 가장 적합한 형식을 선택하세요.

정밀한 제어를 위한 숨기기 vs. 필터링 행

필터링(일시적)과 수동 숨기기(반영구적)의 차이를 이해하여 예상한 결과를 얻고, 그에 따라 함수 번호(예: 109)를 선택하세요.

결론

SUBTOTAL() 사용법을 배우는 것은 탄력적이고 동적인 보고서를 구축할 수 있게 해주는 귀중한 Excel 기술입니다. 이는 SUM()과 같은 정적 함수에서 상당한 발전을 나타냅니다.

그러나 데이터 분석의 환경은 진화하고 있습니다. 구문, 함수 코드 및 오류 처리에 빠져들지 않고 빠르고 정확한 답변이 필요한 전문가를 위해, Excelmatic과 같은 AI 에이전트는 매력적인 대안을 제공합니다. 평범한 언어 질문을 강력한 데이터 분석으로 변환함으로써, 이러한 도구들은 데이터에서 통찰력을 얻는 능력을 민주화하여 수식이 아닌 결과에 집중할 수 있게 합니다. "보이는 행의 합계는 얼마인가요?"라고 질문하고 즉각적이고 정확한 답변을 얻을 수 있는 능력은 효율성에 있어 게임 체인저입니다.

SUBTOTAL()의 복잡성을