Please Enable JavaScript!
Gon[ Enable JavaScript ]

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

엑셀(Excel)/Excel
반응형

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

 

 

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

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

 

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

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

 

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

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

 

 ※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다.
엑셀 Excel 필터 설정과 해제하는 방법
엑셀 Excel 사용자가 지정한 영역에 데이터 필터링을 적용하는 2가지 방법
엑셀 Excel 필터 선택 숫자와 텍스트 추출하기
엑셀 Excel 필터기능 날짜 데이터 걸러내는 방법
셀(Excel) 필터 기능으로 조건에 해당하는 데이터 추출하기

 

 

 

제품번호로 데이터를 필터링 해보겠습니다. “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
 ※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다.
엑셀 Excel 필터 설정과 해제하는 방법
엑셀 Excel 사용자가 지정한 영역에 데이터 필터링을 적용하는 2가지 방법
엑셀 Excel 필터 선택 숫자와 텍스트 추출하기
엑셀 Excel 필터기능 날짜 데이터 걸러내는 방법
셀(Excel) 필터 기능으로 조건에 해당하는 데이터 추출하기
반응형
Posted by 녹두장군1
,