엑셀] 필터, 숨기기로 셀 숨기고 계산하기 - SUBTOTAL

엑셀에서 유용하게 많이 사용하는 기능 중에 필터 기능이 있다.

필터 기능을 이용하면 원하는 데이터들을 골라서 볼 수 있는 장점이 있다.

그런데 필터 기능을 이용할 경우 SUM함수로 합계를 계산하면 필터로 제외된 셀 값이 여전히 포함되어 계산되기 때문에 원하는 결과를 얻지 못할 수도 있다.

그림에서 보듯이 필터로 일부 데이터만 조회해도 여전히 5명의 급여합계인 17,500,000원으로 나타난다.

이 때 사용하는 함수가 SUBTOTAL 함수이다.

SUBTOTAL 함수는 숨긴 데이터를 포함할지 제외할지 정할 수 있다.

엑셀에서 SUBTOTAL함수를 입력하면 다음처럼 함수를 고를 수 있는 화면이 나타난다.  

스크롤바를 내려보면 1번부터 11번까지의 함수와 101번부터 111번까지의 동일한 함수가 나타나는 것을 볼 수 있다. 

1~11번 함수는 숨긴 데이터를 포함해서 계산하고

101~111번 함수는 숨긴 데이터를 제외해서 계산한다. 

즉, 9번 SUM 함수를 선택하면 숨긴 데이터를 포함하기 때문에 그냥 SUM함수와 동일한 결과를 얻게되고

109번 SUM 함수를 선택하면 숨긴 데이터를 제외해서 합산한다. 

함수 마법사를 사용하는 경우는 어떤 함수가 몇 번 함수인지 알 수 없기 때문에 도움말을 참조해야 해서

SUBTOTAL함수는 함수마법사보다 셀에 직접 입력하는 방식이 더 편하다.

SUBTOTAL함수에서 109번 SUM 함수를 선택했다면

자동필터를 사용한 후에 원하는 결과를 얻을 수 있다.

참고로 SUBTOTLA 함수를 사용할 경우 행이나 열을 숨기기 했을 때도 숨긴 데이터는 포함하지 않고 계산한다.

위 그림은 1~3번까지의 세 사람을 숨기기로 가려놓은 상태에서 계산한 결과이다.