엑셀의 SUBTOTAL함수는 목록이나 데이터베이스의 부분합(집계결과)을 구해준다.
이름만 보면 합계만 구해줄 것 같지만 합계뿐만 아니라 평균, 최대값, 최소값 등 다양한 집계결과를 구해준다.
그런데 합계(SUM), 평균(AVERAGE), 최대값(MAX) 등 집계결과를 구하는 함수가 이미 있는데 굳이 이 함수를 이용하는 이유는 무엇일까? 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함수를 이용하여 집계결과를 구해보자.
합계를 구하기 위해 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 - 최소값(숨겨진 값 무시(제외))
수식이 정상적으로 입력되었으면 다음과 같이 집계결과가 표시된다.
[ 엑셀 수학 및 삼각 함수 ]
- 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 함수 - 합계 구하기
