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 

 

 

 

 

 

 

 

Posted by Y&S