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

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

 

 

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

 

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

 

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

 

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

 

 

 

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

  

▼ 다른 함수들을 사용하고 싶다면 아래 표를 참고하세요. 총 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) 필터 기능으로 조건에 해당하는 데이터 추출하기