필터에 걸린 값만 더해주는 SUBTOTAL 함수

Today's Agenda

SUBTOTAL함수를 통해 필터에 걸린 값만 더해보자

2014년 새해가 밝았습니다. 좋은 일만 가득한 2014 되시기를 바랍니다. ^^

엑셀 좀 한다는 소리를 듣기 위해서, 관리해야 할 데이타가 많으신 분들이라면 꼭 알아두셔야 할 SUBTOTAL함수를 소개드리며, 새해 첫 글을 시작할까 합니다. 

이름에서 유추하실 수 있듣이 SUBTOTAL함수는 범위 내의 값들을 더해주는 기능을 합니다. 엑셀의 가장 기초 함수인 SUM함수와 일견 유사하죠. 

 아래 예제를 보실까요. 어느 학교의 학생 수를 더 하는 예제인데요.  SUM함수와 SUBTOTAL함수 모두 D6부터 D11까지의 값을 더 하게 했더니, 176으로 같은 숫자가 나왔습니다. 

SUM함수는 SUM(D6:D11)

SUBTOTAL은 SUBTOTAL( 9, D6:D11) 이렇게 입력했습니다. 

  - 여기서 9의 의미는 잠시 후에 설명 드리겠습니다.

응? SUM과 같은 함수라면 굳이 왜 쓰는지? 또 왜 이렇게 강조를 할까요?

그건 바로 '필터' 기능과 함께 쓸 때 서로 다른 값을 보여주기 때문입니다. 

위의 예제에서 2학년 학생수만 구하려면 어떻게 해야 할까요?

5행부터 11행까지의 데이터에 필터를 걸어주고 학년 조건을 2학년으로 걸어주면 되겠죠? 아래 예제 처럼 말이죠. 

그랬더니, 이번엔 SUM함수와 SUBTOTAL함수의 결과 값이 달라졌습니다. 

SUM은 여전히 176(=전 학년 학생수)인데 반해 SUBTOTAL은 필터에 걸린 값, 2학년 학생수인 72를 보여 주고 있습니다. 

즉, SUBTOTAL함수는 범위 내의 숫자를 더하는데, 필터 조건에 부합하는 값만 더해줍니다. 

엑셀에서 제공하는 도움말에는 다음과 같이 멋있게 써놨네요. "SUBTOTAL함수는 필터 결과에 포함되지 않은 행을 모두 무시합니다."

이제 SUBTOTAL이 어떤 함수인지는 대략 감이 잡히시죠? 그런데 SUM함수와는 다르게 데이터들의 범위만 입력한게 아니라, 숫자 9를 앞에 함께 입력했던 것 기억나시나요?

조금 의아하실 수도 있겠지만 여기서 9의 의미는 '더하라'는 의미입니다!!! 

그러면 더하는 것 말고도 된다는 말인가? 생각이 드실텐데 맞습니다. SUBTOTAL함수는 단순히 더하는 게 아니라 평균, 갯수새기, 최대값 등등을 할 수 있습니다. 9 대신에 다른 숫자를 써준다면 말이죠.

아래 테이블에 상세 내용이 있으니 참조하시면 됩니다. 평균을 구하기 위해서는 9대신 1을 쓰면 되겠죠? 

( '숨겨진 값 포함'과 '숨겨진 값 무시'의 차이는 필터 조건을 걸었을 때 말고, 사용자가 인위로 행을 숨겼을 때 숨겨진 행의 값을 포함할 것인지 말것인지를 결정하게 됩니다. 통상적인 경우에는 1~11값을 써주셔도 무방합니다.)