엑셀 Excel 숨겨진 값, 필터링 데이터 SUBTOTAL 이용해서 구하는 방법

 

환경: Microsoft Excel 2013

 

보통 합계, 평균, 최대값, 최소값 함수인 SUM, AVERAGE, MAX, MIN 등의 함수를 사용하게 되면 필터링해서 데이터를 걸러 냈을 때 화면에 보이지 않는 데이터도 통계에 포함이 됩니다. 숨겨진 데이터를 제외한 화면에 보이는 부분만으로 통계를 낼 수가 없는 것이죠. 하지만 SUBTOTAL 함수는 일반 통계함수와 달리 화면에 보이는 데이터만 통계를 내게 됩니다. 11가지의 함수를 사용할 수 있으며 필터링해서 데이터를 볼 때 유용합니다.  

 

그림과 같이 일반 통계함수인 SUM 을 이용하게 되면 합계를 구할 수 있습니다. 만약 표를 필터링해서 데이터가 사라진다면 SUM 의 결과는 변화가 없습니다. 그럼 화면에 보이는 데이터만 합계를 구하는 방법은 무엇일까요?

엑셀 Excel 숨겨진 값, 필터링 데이터 SUBTOTAL 이용해서 구하는 방법

 

먼저 표에 필터링을 설치해 보도록 하겠습니다. 상단 헤더 셀을 선택하고 [데이터] > [필터] 리본 메뉴를 클릭합니다. 합계가 데이터와 연결되어 있으면 필터링 할 때 같이 사라지므로 띄웠습니다

엑셀 Excel 숨겨진 값, 필터링 데이터 SUBTOTAL 이용해서 구하는 방법

 

SUM 함수와 같은 기능을 하기 위해서는 SUBTOTAL 의 첫 번째 인수 값으로 9 를 넣어야 합니다. 첫 번째 인수의 번호가 어떤 함수와 같은 역할을 하는지는 아래로 내려가 표를 참고하시기 바랍니다

엑셀 Excel 숨겨진 값, 필터링 데이터 SUBTOTAL 이용해서 구하는 방법

 

제품번호로 데이터를 필터링 해보겠습니다. “ZME-001” 만 남긴 결과 46 에서 화면에 나타난 값인 20 으로 변경되었습니다. SUM 함수를 사용했다면 46 으로 그대로 유지했겠죠

엑셀 Excel 숨겨진 값, 필터링 데이터 SUBTOTAL 이용해서 구하는 방법

  

다른 함수들을 사용하고 싶다면 아래 표를 참고하세요. 11가지가 있습니다. 함수 번호는 SUBTOTAL 에서 첫 번째 인수로 들어가는 값입니다. 예를 들어 SUBTOTAL(1, A1:A12) AVERAGE(A1:A12) 함수와 같은 것입니다.

함수번호 (숨겨진 값 계산)

함수번호 (숨겨진 값 제외)

함수명

1

101

AVERAGE

2

102

COUNT

3

103

COUNTA

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SUM

10

110

VAR

11

111

VARP


저작자 표시 비영리 변경 금지
신고
Posted by 녹두장군