엑셀 SUBTOTAL과 AGGREGATE는 필터링이나 숨김 행을 제외하고 집계할 수 있는 매우 똑똑한 함수입니다. 일반 SUM이나 AVERAGE가 숨겨진 데이터도 포함하는 것과 달리 이 함수들은 보이는 데이터만 정확히 계산합니다. 동적 분석에 핵심적인 도구입니다.
SUBTOTAL은 11가지 집계 함수를 코드로 선택할 수 있고 AGGREGATE는 더 강력해 19가지 함수와 다양한 무시 옵션을 제공합니다. 상황에 맞는 함수 선택이 효율적입니다.
SUBTOTAL 기본
SUBTOTAL(함수코드, 범위)로 사용합니다. 함수 코드 9는 SUM, 1은 AVERAGE, 2는 COUNT, 4는 MAX 같은 식으로 지정합니다.
코드가 1~11이면 필터링된 데이터는 제외하지만 수동 숨김 행은 포함합니다. 101~111이면 필터와 수동 숨김 모두 제외해 정확한 보이는 데이터 계산이 가능합니다.
자동 필터와 함께 사용하면 매우 강력합니다. 필터를 변경할 때마다 SUBTOTAL 결과가 자동 갱신되어 동적 분석이 가능합니다.
AGGREGATE 함수
AGGREGATE는 SUBTOTAL의 강화 버전입니다. 19가지 함수와 다양한 무시 옵션을 제공해 더 정밀한 제어가 가능합니다.
오류가 있는 셀을 무시하는 옵션이 매우 유용합니다. 일반 SUM은 오류를 포함하면 결과도 오류가 되지만 AGGREGATE는 오류만 건너뛰고 정상 값으로 집계합니다.
중첩 SUBTOTAL도 무시할 수 있어 다단계 집계 표에서 정확한 결과를 얻을 수 있습니다. 매우 강력한 분석 도구입니다.
필터 동적 집계
필터링된 데이터만 합계하는 것이 SUBTOTAL의 핵심 강점입니다. 사용자가 필터를 변경하면 결과도 즉시 갱신되어 인터랙티브한 분석이 가능합니다.
대시보드 KPI 계산에 매우 효과적입니다. 사용자가 부서나 기간을 선택하면 그에 맞는 KPI가 자동 계산되어 매우 효율적입니다.
일반 SUMIFS와 다른 점은 SUBTOTAL이 화면 보이는 데이터만 계산한다는 것입니다. 화면 필터링과 동기화되는 매우 직관적인 동작입니다.
활용 사례
판매 데이터 분석에 매우 효과적입니다. 사용자가 부서나 제품을 필터링하면 자동으로 그 조건의 합계와 평균이 즉시 표시됩니다.
오류가 있는 데이터의 안전한 집계에 AGGREGATE가 핵심입니다. 일부 셀에 오류가 있어도 정상 데이터로 정확한 분석이 가능합니다.
중첩 SUBTOTAL이 있는 다단계 요약 표에서 최종 합계 계산에 매우 유용합니다. 일반 SUM은 중첩 SUBTOTAL을 중복 계산하는 문제를 SUBTOTAL이 자동 해결합니다.
주의할 점
함수 코드를 정확히 기억해야 합니다. 잘못된 코드는 의도와 다른 집계를 수행합니다. 자주 사용하는 코드는 메모해 두는 것이 좋습니다.
11과 111 같은 코드 차이를 이해해야 합니다. 필터만 제외할지 수동 숨김도 제외할지에 따라 결과가 달라집니다.
대량 데이터에서는 성능 영향이 있을 수 있습니다. AGGREGATE가 더 강력하지만 그만큼 계산 부담도 큽니다.
자주 묻는 질문
Q1. SUM과 차이는?
SUM은 숨김 데이터도 포함하지만 SUBTOTAL은 필터링된 데이터만 계산합니다.
Q2. 함수 코드는?
9는 SUM, 1은 AVERAGE, 2는 COUNT, 4는 MAX, 5는 MIN입니다. 함수 입력 시 도움말이 표시됩니다.
Q3. SUBTOTAL과 AGGREGATE 차이?
AGGREGATE가 더 많은 함수와 무시 옵션을 제공하는 강화 버전입니다.
Q4. 오류 무시는?
AGGREGATE의 무시 옵션 6을 사용하면 오류를 제외하고 집계됩니다.
Q5. 표 기능과 함께?
표의 합계 행이 자동으로 SUBTOTAL을 사용합니다. 매우 편리한 기본 동작입니다.
정리
SUBTOTAL과 AGGREGATE는 필터링이나 숨김 행을 제외하고 집계할 수 있는 매우 똑똑한 함수입니다. 화면에 보이는 데이터만 정확히 계산해 인터랙티브한 동적 분석에 매우 효과적입니다.
AGGREGATE는 SUBTOTAL의 강화 버전으로 더 많은 함수와 무시 옵션을 제공합니다. 오류 무시, 중첩 함수 무시 같은 정밀한 제어가 가능합니다. 대시보드 KPI 계산, 필터 기반 분석 같은 다양한 실무에 매우 강력한 도구입니다.