Home > 엑셀 함수 > 수학 및 삼각 함수 > 엑셀함수 SUBTOTAL – 목록이나 데이터베이스의 부분합(집계결과) 구하기

엑셀함수 SUBTOTAL – 목록이나 데이터베이스의 부분합(집계결과) 구하기

  • by

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

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

그런데 합계(SUM), 평균(AVERAGE), 최대값(MAX) 등 집계결과를 구하는 함수가 이미 있는데 굳이 이 함수를 이용하는 이유는 무엇일까? SUBTOTAL함수의 첫번째 인수를 “숨겨진 값 무시(제외)”하도록 지정하면 숨겨진 행은 집계결과에서 제외할 수 있는데  행을 “숨기기”,  “숨기기 취소” 하면서 집계결과를 비교해 보는 장점이 있다.

구문(Syntax)

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함수를 이용하여 집계결과를 구해보자.

엑셀 SORT함수

 

합계를 구하기 위해 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 - 부분합(집계결과) 구하기

 

[ 엑셀 수학 및 삼각 함수 ]

댓글 남기기

이메일은 공개되지 않습니다.