엑셀 SUBTOTAL 함수: 장단점, 제약사항 및 활용 예시

엑셀의 SUBTOTAL 함수는 표나 필터링된 데이터에서 필요한 집계만 골라 계산할 수 있는 강력한 도구입니다. 데이터 분석 및 보고서 작성 시 범용적으로 사용되며, 필터 적용, 숨김 행 무시 등 동적 집계가 필요한 상황에 특히 유용합니다.

---

1. SUBTOTAL 함수란?

SUBTOTAL 함수는 지정한 집계 유형(합계·평균·개수 등)과 영역을 입력하면, 필터나 수동 숨김 처리된 행을 자동으로 무시하거나 포함하여 결과를 반환합니다.

• 구문:
  =SUBTOTAL(함수번호, 참조1, [참조2], ...)
• 함수번호: 111 → 숨김 및 필터 무시 / 101111 → 필터만 무시
  예) 9 또는 109 = 합계, 1 또는 101 = 평균, 3 또는 103 = 개수

반응형

---

2. 주요 장점

• 필터 적용 결과만 집계: 필터로 숨겨진 행은 자동으로 제외되어 정확한 분석 가능
• 수동 숨김 제어: 111 사용 시 수동 숨김 행까지 무시하고, 101111 사용 시 수동 숨김 포함
• 다양한 집계 함수 지원: 합계, 평균, 개수, 최대값, 최소값, 표준편차 등 11가지
• 동적 업데이트: 원본 데이터 변경이나 필터 조작 시 실시간으로 결과 반영

---

3. 주요 제약사항

• 다중 영역 참조 제한: 참조 영역마다 한 번씩 함수 호출해야 하므로 복잡한 다중 영역 집계 시 수식이 길어짐
• 배열 수식 사용 불가: SUBTOTAL 자체로 배열 상수를 처리하는 데 제약이 있음
• 숨김 기준 혼동: 함수번호 구분을 정확히 모르면 원하는 결과(수동 숨김 제외 vs 포함)를 얻지 못함
• 피벗테이블과 중복 기능: 피벗테이블이 더 강력한 집계를 제공하므로 겹치는 부분이 존재

---

4. 활용 예시

4.1. 필터 적용된 데이터 합계 구하기

상품별 매출 데이터에서 특정 카테고리만 필터링한 뒤 합계를 구하고 싶을 때

=SUBTOTAL(109, C2:C100)

• 109는 필터된 행만 계산(수동 숨김 포함)하는 합계 함수

4.2. 숨김된 데이터 제외한 평균 계산

수동으로 숨긴 행까지 모두 제외하고 평균을 산출하려면

=SUBTOTAL(1, D2:D50)

• 1은 수동 숨김까지 모두 제외한 평균

4.3. 가시 셀 개수 세기

필터 또는 숨김 행 제외 후 표시된 셀의 개수를 구해 대시보드에 활용

=SUBTOTAL(3, A2:A200)

• 3은 수동 숨김까지 제외한 셀 개수

4.4. 다중 영역 합계

여러 열을 동시에 집계할 때

=SUBTOTAL(109, C2:C100, E2:E100)

• 두 개 영역의 합계를 한번에 계산

4.5. 중첩 SUBTOTAL로 전체 합계 표시

표 전체 아래에 전체 합계(필터 무시)를 표시하면서도 필터링된 합계를 함께 보여주기

셀 G2: =SUBTOTAL(109, C2:C100)    // 필터 적용 합계  
셀 G3: =SUBTOTAL(9, C2:C100)      // 전체 합계(수동 숨김 포함)  

---

5. 사용 팁

• 함수번호 선택: 수동 숨김까지 제외하려면 1–11, 필터만 제외하려면 101–111을 기억
• 동적 범위: 테이블 형식(Excel Table)으로 변환하면 범위 추가/삭제 시 자동 업데이트
• 피벗 vs SUBTOTAL: 복잡한 그룹화나 다차원 분석이면 피벗테이블, 간단한 요약은 SUBTOTAL로 가볍게 처리
• 조건부 집계: SUMPRODUCT, AGGREGATE 함수와 조합하면 더욱 유연한 집계 가능
• 대시보드 적용: 필터 조작에 따라 실시간으로 변하는 KPI 카드에 활용

---

엑셀 SUBTOTAL 함수는 필터링된 데이터를 동적으로 집계할 때 빛을 발하는 도구입니다. 표와 함께 사용하면 손쉽게 대시보드와 보고서를 자동화할 수 있어 데이터 분석 효율을 크게 높여줄 것입니다. 필요에 따라 함수번호만 잘 선택하고, 테이블 기능과 결합해 보세요!