[엑셀 함수강좌] 필터링된 데이터에 대한 합계 구하기(SUBTOTAL)엑셀교실

[엑셀 함수강좌] 필터링된 데이터에 대한 합계 구하기(SUBTOTAL)엑셀교실

필터링된 데이터에 대한 합계 구하기(SUBTOTAL)

 

=SUBTOTAL(function_num, REF1, [REF2]…..)

 

일반적으로 합계를 구할땐 SUM함수를 사용하곤 합니다. 범위를 지정하여 합계를 구하는 것이지요

그러나 엑셀의 자동필터를 이용하여 필터링된 데이터에 대한 합을 SUM함수를 이용하여 구하게 되면 정확한 값을 구할 수 없습니다.

이때 SUBTOTAL함수를 이용하면 필터링된 데이터에 대해서만 값을 구할 수 있습니다.

 

SUBTOTAL 함수 FUNTION NUMBER

함수번호	기능	계 산
1	Average	주어진 영역의 평균값을 구합니다.
2	Count	숫자를 포함한 셀과 숫자의 개수를 구합니다.
3	CountA	공백이 아닌 셀과 값의 개수를 계산합니다.
4	Max	최대값을 추출합니다.
5	Min	최소값을 추출합니다.
6	Product	인수를 모두 곱한 결과를 표시합니다.
7	Stdev	표본의 표준편차를 예측합니다.
8	Stdevp	모집단 전체의 표준편차를 구합니다.
9	Sum	영역의 합계를 구합니다.
10	Var	표본의 분산을 계산합니다.
11	Varp	모집단 전체의 분산을 구합니다.

 

SUBTOTAL 함수의 function_num 자리에 함수번호를 적으면 해당 기능을 이용 할 수 있습니다.

합계에대한 SUBTOTAL 함수는 =SUBTOTAL(9, 범위) 이런형태로 수식을 작성 할 수 있는 것이지요.

 

자동필터가 적용된 회원 회비관리대장에서 이름이 심규헌인 사람의 회비합계를 구해보세요.

(각각 SUM, SUTOTAL 이용하여 결과 확인)

 

 

 

 

 

 

이름부분에 적용된 필터를 클릭해서 심규헌인 사람을 선택하면 나머지 셀은 숨겨지고 심규헌인 사람만 남게 되는데,

 

이때 E11 셀에 =SUM(E6:E8) 입력, E12 셀에 =SUBTOTAL(9, E6:E8) 입력하게되면, 

 

위 결과에서 알수 있듯이 SUM을 이용시 90,000, SUBTOTAL을 이용시 60,000원이 나오므로 범위는 E6:E8까지로

서로 같지만 필터링된 데이터의 합계를 구하려면 SUBTOTAL을 이용해야 한다는 것을 알 수 있습니다.