SUBTOTAL 함수란?
목록이나 데이터베이스의 부분합을 구하는 함수이고, [데이터] 메뉴의 [부분합] 명령을 사용하여 부분합 목록을 작성하는 방법과 같이 11가지 기능을 가진 다 기능 함수다. SUBTOTAL로 많이 사용하는 함수는 SUM, AVERAGE, COUNT 등의 함수이다.
SUBTOTAL 함수의 특징
1. SUBTOTAL 함수는 필터 결과에 포함되지 않은 행을 모두 무시하기 때문에 자동 필터와 함께 많이 사용한다.
2. [데이터]-[부분합]으로 부분합을 계산할 경우 사용되는 11가지의 계산이 SUBTOTAL 함수를 이용한 계산이므로 함수를 수정하여 부분합의 계산을 수정할 수 있다.
3. 범위에 다른 부분합이 있으면 이중으로 계산되는 것을 피하기 위해 중첩된 부분합을 무시한다. 즉, 영역 안에 다른 부분합은 계산하지 않는다.
=SUBTOTAL(함수 번호, 계산할 범위)
범위를 함수 번호에 해당하는 함수로 계산한다.
예) =SUBTOTAL(9, B5:B10) => B5:B10 범위에 해당하는 숫자들을 합한다.
함수 번호에 해당하는 함수와 설명
101~111은 행을 숨길 경우 숨긴 행의 값을 계산하지 않을 때 사용하면 된다.
반면에, SUBTOTAL 함수는 숨긴 열에는 영향을 받지 않는다.
SUBTOTAL 활용 1
1. 자동필터를 적용한 후 화면에 표시되는 데이터만 자동으로 계산되는 예제를 만들어보자.
무게와 단가에 대한 합을 H15셀과 I15셀에 SUBTOTAL 함수로 미리 구해보았다.
여기까지는 SUM을 사용했을 때와 답이 다르지 않다.
무게의 합(H15) => SUBTOTAL(9, H3:H14)
단가의 합(I15) => SUBTOTAL(9, I3:I14)
2. 자동 필터를 적용하기 위해
합계 행을 제외한 데이터를 범위 지정한 후
[데이터]-[필터] 명령을 클릭한다.
3. 제목행에 조건에 따라 필터링할 수 있는 단추가 표시된다.
4. '지점명'이 '대륙전자'인 데이터만 필터링하기 위해
'지점명'의 단추를 클릭한 후 '제일전자'를 체크 해제한다.
5. 자동필터가 실행되어 '대륙전자' 데이터만 표시되고,
무게와 단가의 합계가 화면에 표시된 데이터의 합계만 구해진다.
SUM을 사용했다면 여전히 972, 7246000 이 구해졌을 것이다.
이렇게 SUBTOTAL 함수는 자동필터와 함께 자주 사용된다.
SUBTOTAL 활용 2
1. 자동필터링되도 일련번호가 항상 '1, 2, 3...'으로 유지되는 예제를 만들어보자.
B3 셀에 '=SUBTOTAL(3, $C$3:C3)' 식을 입력한 후 아래로 수식을 복사한다.
'=SUBTOTAL(3, $C$3:C3)' 식에서 '3'은 함수 'COUNTA'를 의미한다.
범위에 이렇게 사용한 이유는
'$C$3:C3'에서 앞의 'C3'을 절대참조로 고정하여
'C3:C4', 'C3:C5', 'C3:C6', 'C3:C7'과 같이 참조가 변하도록 하기 위해서다.
참조에 대해 궁금하다면 함 보자!! 엑셀 셀 참조 (상대참조, 절대참조, 혼합참조) by Y
2. 식이 복사되어 아래와 같이 일련번호가 만들어진다.
3. 위의 활용 예제에서와 같이 자동필터를 실행하면
눈에 보이는 범위만 자동으로 재계산하여 정상적으로 일련번호가 표시된다.
이와 같이 SUTOTAL 함수를 자동필터와 함께 사용하면 편리하게 계산할 수 있다.
☞ 일련번호 만드는 다른 방법
2013/04/29 - 엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y
2013/05/14 - 엑셀 자동채우기 (일련번호, 수식복사, 날짜채우기 등 실습하기) by Y
| 엑셀 빈셀/빈칸 없애기, 공백 문자 없애기 by Y (7) |
2013.06.11 |
| 엑셀 함수 19 - REPLACE, TRIM, SUBSTITUTE 함수 (문자 변경 함수) by Y (2) |
2013.06.10 |
| 엑셀 함수 18 - INT, TRUNC, ROUND, ROUNDUP, ROUNDDOWN 함수 by Y (4) |
2013.05.27 |
| 엑셀 자동채우기 (일련번호, 수식복사, 날짜채우기 등 실습하기) by Y (2) |
2013.05.14 |
| 외부 데이터 가져오기 2 (한글 문서를 엑셀로 가져오기) by Y (2) |
2013.05.07 |
| 엑셀 함수 16 - FREQUENCY 함수 (나이대 분포 구하기 예제) by Y (13) |
2013.05.02 |
| 엑셀 함수 15 - ROW 함수 (엑셀 일련번호 만들기) by Y (2) |
2013.04.29 |
| 엑셀 데이터 통합 기능 by Y (6) |
2013.04.20 |
| 엑셀 함수 14 (선택 함수, CHOOSE 함수) by Y (4) |
2013.04.19 |
| 엑셀 목표값 찾기로 데이터를 분석해보자 by Y (0) |
2013.04.10 |
subtotal 함수.xlsx
예제 파일.xlsm
