엑셀 SUBTOTAL 함수로 필터링 & 숨기기 행 제외 값 집계하기(부분합)

엑셀에서 강력한 무기 중 하나는 필터링이 아닐까? 생각해 봅니다.

일반적으로 필터링 후 화면에 보이는 데이터만 계산하기 위해서는 SUBTOTAL 함수를 사용합니다.

SUBTOTAL 함수가 언제부터 기능이 추가 되었는지 모르지만 전에 보지 못한 Function_num가 추가 되어 있어 도움말을 봤는데.. 쉽게 이해가 되지 않아 몇가지 실험을 해보았습니다.

결론부터 말씀 드리면 그동안 사용하던 1~9까지의 function_num은 필터링을 해야지 데이터를 집계 했는데 101~111까지는 필터링 없이 숨기기만 해도 화면에 보이는 데이터를 집계한다는 것입니다.

SUBTOTAL 함수

필터링 또는 숨기기한 목록이나 데이터베이스의 부분합을 반환합니다. 

수식 = SUBTOTAL(function_num,ref1,[ref2],...)

☞ SUBTOTAL 함수의 인수

1. function_num  : 부분합을 계산을 위해 지정하는 숫자입니다.(필수)

※ 주의

① 여기서 숨기기는 사용자가 직접 행을 숨긴것을 의미 함

② Type2는 오피스버전에 따라 지원

2. ref1 : 부분합을 계산할 첫 번째 범위 또는 참조입니다. (필수)

3. ref2 : 부분합을 계산할 범위 또는 참조로서 2개에서 254개까지 지정할 수 있습니다. (선택)

SUBTOTAL 함수 비교 (필터링만 한 경우)

1. 그림과 같이 판매월에서 1월을 필터링을 해보겠습니다.

2. 일반적인 함수(AVERAGE, COUNT, SUM)는 필터링을 해도 표 전체의 값을 집계 합니다.

3. 반면에 SUBTOTAL 함수를 사용했을 경우는 화면에 보이는 데이터만 집계를 합니다.

4. 필터링만 한 경우에는 Type1(1~11)과 Type2(101~111)이 차이가 없습니다.

※ SUBTOTAL 함수는 위 예와 같이 필터링 후 화면에 보이는 데이터만 Function_num에 따라 계산을 합니다.

Function_num에 따른 적용함수는 제일 상단의 표를 참조 하세요.

SUBTOTAL 함수 비교 (필터링과 숨기기를 한 경우)

1. 필터링과 숨기기를 한 결과를 보겠습니다.

2. 여전히 Type1과 Type2가 별 차이 없이 같습니다.

SUBTOTAL 함수 비교 (숨기기만 한 경우)

1. 이번에는 필터링을 빼 보겠습니다. 단순히 특정 행들을 숨기기 한 결과를 보겠습니다.

2. 이번에는 Type1과 Type2가 결과값에서 차이를 나타냅니다.

3. 숨기기만 한경우 Type1은 전체 범위를 집계하지만 Type2는 화면에 보이는 데이터만 집계를 하고 있습니다.

※ 결론은 Type2의 경우 필터링을 하지 않아도 숨기기만 해도 집계가 된다는 것!!! 

첨부파일 보기

SUBTOTAL 함수.xlsx