엑셀의 SUBTOTAL 함수는 목록이나 데이터베이스의 부분합(집계 결과)을 구해줍니다.
이름만 보면 합계만 구해줄 것 같지만 합계뿐만 아니라 평균, 최대값, 최소값 등 다양한 집계 결과를 구해줍니다.
그런데 합계(SUM), 평균(AVERAGE), 최대값(MAX) 등 집계 결과를 구하는 함수가 이미 있는데 굳이 이 함수를 이용하는 이유는 무엇일까요?
SUBTOTAL 함수의 첫 번째 인수를 "숨겨진 값 무시(제외)" 하도록 지정하면 숨겨진 행은 집계 결과에서 제외할 수 있는데 행을 "숨기기", "숨기기 취소" 하면서 집계 결과를 비교해 보는 장점이 있습니다.
※ 참고 – SUBTOTAL 함수를 대체할 수 있는 함수가 있습니다.
엑셀 2010 이상 버전부터 사용할 수 있는 AGGREGATE 함수는 SUBTOTAL 함수를 대체할 수 있는 함수입니다. SUBTOTAL 함수와 비교해보면 값에 오류가 있으면 제외할 수 있고 처리할 수 있는 함수가 8개(MEDIAN, LARGE, PERCENTILE.INC 등)가 추가되어서 총 19개의 함수를 지원합니다.
구문(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 함수를 이용하여 집계 결과를 구해보겠습니다.
[C16] 셀에 다음 수식을 입력하여 합계를 구합니다.
=SUBTOTAL(9,$E$6:$E$13)
수식의 첫 번째 인수는 집계 함수의 종류이고 두 번째 인수는 집계 범위입니다.
합계 외 평균 등 다른 함수의 집계 결과도 구해보겠습니다.
=SUBTOTAL(1,$E$6:$E$13) (1 - 평균)
=SUBTOTAL(4,$E$6:$E$13) (4 - 최대값)
=SUBTOTAL(5,$E$6:$E$13) (5 - 최소값)
집계 결과 :
2) 숨겨진 값 무시(제외)하고 집계 결과 구하기
이번에는 첫 번째 인수를 "숨겨진 값 무시(제외)"하도록 지정하여 숨겨진 행을 집계 결과에서 제외시켜 보겠습니다.
이 기능은 행을 "숨기기", "숨기기 취소" 하면서 집계 결과를 비교해 보는 장점이 있습니다.
아래 범위에서 9~12행을 행 숨기기(제외)하고 집계 결과를 구해보겠습니다.
행을 숨긴 후, [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 - 최소값(숨겨진 값 제외))
집계결과 :
[ 엑셀 수학 및 삼각 함수 ]
- 엑셀 수학 및 통계 함수 알아보기
- AGGREGATE 함수 - 목록,데이터베이스 집계
- SUMPRODUCT 함수로 조건을 만족하는 자료만 계산하기
- RANDARRAY 함수 - 배열 형태로 난수 구하기
- SEQUENCE 함수 - 연속된 숫자목록 만들기
- LCM 함수 - 최소공배수 구하기
- GCD 함수 - 최대공약수 구하기
- ODD 함수 - 가장 가까운 홀수로 올림,내림하기
- EVEN 함수 - 가장 가까운 짝수로 올림,내림하기
- SUBTOTAL 함수 - 부분합 구하기
- SUMIFS 함수로 월별 합계 구하기
- SIGN 함수 - 수의 부호 구하기
- MMULT 함수 - 두 배열의 행렬 곱 구하기
- MINVERSE 함수 - 정방행렬의 역행렬 구하기
- MDETERM 함수 - 배열의 행렬식 구하기
- TRUNC 함수 - 숫자 절사하기
- POWER 함수 – 숫자의 거듭제곱 구하기
- PI(파이) 함수 - 원주율 구하기
- FACT 함수 - 팩토리얼(계승값) 구하기
- INT 함수 - 숫자를 가장 가까운 정수로 내림하기
- SQRT 함수 - 양의 제곱근 구하기
- LOG,LOG10,LN 함수 - 로그값 구하기
- EXP 함수 - 상수 e를 지수만큼 거듭제곱하기
- BASE 함수 - 10진수를 다른 진수로 바꾸기
- MROUND 함수 - 배수값으로 숫자 반올림하기
- CEILING, FLOOR 함수 - 배수값으로 숫자 올림,내림하기
- RAND, RANDBETWEEN 함수 - 난수 구하기
- QUOTIENT, MOD 함수 - 몫과 나머지 구하기
- ROUND, ROUNDUP, ROUNDDOWN 함수로 숫자 반올림,올림,내림하기
- ABS 함수 - 절대값 구하기
- SUMPRODUCT 함수 - 숫자를 곱하고 합계 구하기
- PRODUCT 함수 - 숫자를 모두 곱하기
- SUMIFS 함수 - 여러 조건을 만족하는 범위의 합계 구하기
- SUMIF 함수 - 조건을 만족하는 범위의 합계 구하기
- SUM 함수 - 합계 구하기