엑셀 함수 > 수학 및 삼각 함수 > SUBTOTAL 함수 - 부분합 구하기

SUBTOTAL 함수 - 부분합 구하기

엑셀의 SUBTOTAL 함수는 목록이나 데이터베이스의 부분합(집계 결과)을 구해줍니다.

이름만 보면 합계만 구해줄 것 같지만 합계뿐만 아니라 평균, 최대값, 최소값 등 다양한 집계 결과를 구해줍니다.

그런데 합계(SUM), 평균(AVERAGE), 최대값(MAX) 등 집계 결과를 구하는 함수가 이미 있는데 굳이 이 함수를 이용하는 이유는 무엇일까요?

SUBTOTAL 함수의 첫 번째 인수를 "숨겨진 값 무시(제외)" 하도록 지정하면 숨겨진 행은 집계 결과에서 제외할 수 있는데 행을 "숨기기", "숨기기 취소" 하면서 집계 결과를 비교해 보는 장점이 있습니다.

※ 참고 – SUBTOTAL 함수를 대체할 수 있는 함수가 있습니다.

엑셀 2010 이상 버전부터 사용할 수 있는 AGGREGATE 함수는 SUBTOTAL 함수를 대체할 수 있는 함수입니다. SUBTOTAL 함수와 비교해보면 값에 오류가 있으면 제외할 수 있고 처리할 수 있는 함수가 8개(MEDIAN, LARGE, PERCENTILE.INC 등)가 추가되어서 총 19개의 함수를 지원합니다.

 

구문(Syntax)

SUBTOTAL

SUBTOTAL(function_num,ref1,[ref2],…)

목록이나 데이터베이스의 부분합(집계 결과)을 구해준다.

인수 :

  • function_num : 집계 함수 번호
    집계함수 종류 숨겨진 값 포함 숨겨진 값 무시(제외)
    AVERAGE – 평균 1 101
    COUNT – 숫자의 개수 2 102
    COUNTA – 비어있지 않은 셀의 개수 3 103
    MAX – 최대값 4 104
    MIN – 최소값 5 105
    PRODUCT – 곱하기 6 106
    STDEV.S – 표본집단의 표준편차 7 107
    STDEV.P – 모집단의 표준편차 8 108
    SUM – 합계 9 109
    VAR.S – 표본집단의 분산 10 110
    VAR.P – 모집단의 분산 11 111
  • ref1 : 부분합을 계산할 첫 번째 범위 또는 참조
  • ref2,… : 부분합을 계산할 두 번째 범위 또는 참조(최대 254개까지 지정가능)

 

사용예 :

1) 숨겨진 값을 포함하여 집계 결과 구하기

다음과 같이 "거래처별 판매실적" 자료가 있다고  할 때 SUBTOTAL 함수를 이용하여 집계 결과를 구해보겠습니다.

SUBTOTAL 함수로 집계 결과 구하기

 

[C16] 셀에 다음 수식을 입력하여 합계를 구합니다.

=SUBTOTAL(9,$E$6:$E$13) 

SUBTOTAL 함수로 합계 구하기

 

수식의 첫 번째 인수는 집계 함수의 종류이고 두 번째 인수는 집계 범위입니다.

엑셀함수 SUBTOTAL - 부분합(집계결과) 구하기

 

합계 외 평균 등 다른 함수의 집계 결과도 구해보겠습니다.

=SUBTOTAL(1,$E$6:$E$13) (1 - 평균)

=SUBTOTAL(4,$E$6:$E$13) (4 - 최대값)

=SUBTOTAL(5,$E$6:$E$13) (5 - 최소값)

집계 결과 :

SUBTOTAL 함수로 합계,평균,최대,최소 구하기

 

 

2) 숨겨진 값 무시(제외)하고 집계 결과 구하기

이번에는 첫 번째 인수를 "숨겨진 값 무시(제외)"하도록 지정하여 숨겨진 행을 집계 결과에서 제외시켜 보겠습니다.
이 기능은 행을 "숨기기",  "숨기기 취소" 하면서 집계 결과를 비교해 보는 장점이 있습니다.

아래 범위에서 9~12행을 행 숨기기(제외)하고 집계 결과를 구해보겠습니다.

SUBTOTAL 함수로 행을 숨기고 결과 구하기

 

행을 숨긴 후, [C22] 셀에 다음 수식을 입력해서 합계를 구합니다.

=SUBTOTAL(109,$E$6:$E$13)

첫번째 인수 "109"는 집계 함수 중 "숨겨진 값을 무시(제외)한 합계"를 의미합니다.

 

합계 외 평균 등 다른 함수의 결과 구하기

=SUBTOTAL(101,$E$6:$E$13) (101 - 평균(숨겨진 값 제외))

=SUBTOTAL(104,$E$6:$E$13) (104 - 최대값(숨겨진 값 제외))

=SUBTOTAL(105,$E$6:$E$13) (105 - 최소값(숨겨진 값 제외))

집계결과 :

SUBTOTAL 함수로 행을 숨기고 결과 구하기

 

 

[ 엑셀 수학 및 삼각 함수 ]

댓글 달기

이메일 주소는 공개되지 않습니다.

Scroll to Top
%d 블로거가 이것을 좋아합니다: