이번 강좌에 알아볼 것은 SUBTOTAL 함수입니다. 미리 말씀드리면 조금 어려운 개념일 수 있습니다. 좀 쉬운 함수를 먼저 알아보는 것도 방법이겠지만, 기본 함수를 알아보았으니, 좀 어렵더라도 이번에 알아보는 것이 맞는 순서인 것 같습니다. 굉장히 활용도가 높은 함수이므로 꼭 기억하시길 바랍니다. 이 함수와 필터를 사용하면, 어렵고 까다로운 함수로 처리해야 할 통계도 쉽게 파악할 수 있습니다.
엑셀에서 데이터를 표로 만들고 나면 가장 많이 하는 작업을 뭘까요?
뭐니 뭐니 해도 통계(합계, 평균, 개수, 최대, 최소 등)를 알아보고 현 상태의 전체적인 현황을 파악하는 것일 겁니다. 바로 기본 함수들로 할 수 있는 작업들이죠. 그러고 나면 다음 단계는 자연스럽게 특정 데이터의 부분적인 현황 파악하는 작업으로 이어집니다. 필터링 작업인 것이죠. 그런데 필터링 작업은 통계 함수 적용 시 문제가 있습니다.
예제 파일을 열고 확인하겠습니다.
예제 파일에 합계 셀(I4)은 SUM 함수로 금액(원) 합을 반환하고 있습니다. 금액(원) 항목의 전체 합계가 잘 반환된 상태죠.
하지만, 강원 지역의 합계를 보기 위해 필터를 적용하면,
지역이 강원인 것들의 금액(원) 합이 반환될 것 같지만 그림처럼 반환되지 않습니다. 여전히 금액(원) 항목의 전체 합계가 반환되죠.
필터의 사용법을 모르시는 분들이라면 아래 두 강좌를 확인하고 지역인 강원인 데이터로 필터링해 주세요. 필터는 매우 중요한 기능이니 가능한 필터링 편을 모두 보시는 것이 좋습니다.
이렇게 필터를 적용하면 필터링된 데이터만 합계를 내어주면 좋을 텐데, 불행히도 SUM 함수는 그런 기능이 없습니다. SUM 함수만 그런 것도 아닙니다. AVERAGE, COUNT, MAX, MIN 등 통계에 사용되는 모든 함수가 다 마찬가지입니다. 그래서 여기에 필요한 기능은, 필터로 제외된 데이터가 통계 함수(SUM, AVERAGE, COUNT, ...)의 인수 범위에도 제외되는 기능이란 것을 알 수 있습니다. 바로 이런 기능을 하는 함수가 SUBTOTAL 함수입니다. 필요하고 중요할 수밖에 없죠.
SUBTOTAL목록이나 데이터베이스의 부분합을 반환합니다.
SUBTOTAL(function_num,ref1,[ret2],...)| function_num | 실제 사용할 11가지 함수. 번호로 함수를 대치합니다. |
| ref1 | function_num으로 사용한 실제 함수의 첫 번째 인수 |
| ref2 | [선택] function_num으로 사용한 실제 함수의 두 번째 인수. 없다면 불필요 |
함수의 정리는 위와 같이 됩니다. SUBTOTAL을 처음 접하시면 함수의 정리만으로는 좀 어려울 것으로 압니다. 저도 그랬으니까요.
보통 함수는 어떤 결과를 반환할 때 쓰입니다. SUM은 합계를, AVERAGE는 평균을, COUNT는 개수를, MAX와 MIN은 최대, 최소값을 반환합니다. 이것뿐 아니라 대부분의 함수가 어떤 결과를 반환(return)해 주죠. 하지만 특이하게 SUBTOTAL은 어떤 값을 반환하지 않습니다. 위에서 설명했듯이 단지 불필요한 데이터를 제외하는 기능만 하죠. 그렇다는 것은 값을 반환할 실제 함수가 또 있어야 한다는 얘기가 됩니다. 바로 그 실제 함수가 SUBTOTAL 함수의 첫 번째 인수 function_num 입니다.
SUBTOTAL 함수의 function_num 인수를 일단 확인하겠습니다.
먼저 필터 탭 > 필터를 클릭하여 필터 사용을 해제하고 원래의 상태로 돌아옵니다.
- function_num 확인을 위해 일단 SUBTOTLAL 함수를 사용해 봐야 합니다. 비어 있는 셀을 아무거나 클릭하여 선택합니다.
- 수식 입력줄에서 다시 수식 작성을 시작(=)하고 함수명을 작성합니다. => =SUBTOTAL(
- 함수의 첫 번째 인수 힌트(function_num)를 확인합니다.
여기까지 확인하면 function_num 인수에서 선택할 수 있는 함수가 숫자와 함께 보입니다. 번호로 보면 1~11, 101~111이 보입니다. 총 22가지입니다.
그런데 잘 확인하면, 1번과 101번이 AVERAGE, 2번과 102번이 COUNT 함수임을 알 수 있습니다. 나머지도 3과 103, 4와 104, ...이 모두 동일한 함수로 확인됩니다. 실제 적용할 수 있는 함수는 11개라는 것이죠. 1번대와 100번대 2개 그룹으로 나누어져 있다는 것은 두 그룹이 뭔가 다르게 작동한다는 의미겠죠. 이건 나중에 다시 알아보겠습니다.
어쨌든 function_num이 실제로 값을 반환하는 함수이고 대부분 익숙한 함수들입니다. 지금처럼 합계를 반환할 것이라면 9번 SUM을 사용하면 됩니다. 평균을 반환하고 싶다면 1번 AVERAGE를 사용하면 되겠죠. 번호를 입력하셔도 좋고 방향키와 탭키를 이용하여 선택해도 됩니다. 그럼 function_num 인수는 작성이 완료됩니다. 그런데 function_num으로 작성한 것도 실제로는 함수죠. 함수라면 당연히 그 함수의 인수가 필요하겠죠. 바로 그 인수가 SUBTOTAL의 2번째 인수인 ref1입니다. 그리고 function_num으로 사용된 함수가 2번째 인수도 필요로 한다면 그 2번째 인수가 ref2가 되는 것입니다. 필요 없다면 사용하지 않습니다. 그래서 ref2부터는 선택 사항일 수밖에 없습니다.
즉, 실제 함수를 SUBTOTAL 함수로 감싼 형태입니다. 하나의 함수지만 실제로는 함수 안에 함수로 2개의 함수가 적용된 형태죠.
SUBTOTAL의 각 인수를 확인했으니, 확인을 위해 작성했던 셀은 ESC를 눌러 작성을 취소합니다.
이제 실제로 합계 셀(I4)를 지우고 다시 작성하겠습니다.
- 합계 셀(I4)을 클릭하여 선택한 후 delete키를 눌러 셀 값을 지웁니다.
- 수식 입력줄에서 다시 수식을 시작(=)하고 함수명을 작성합니다. => =SUBTOTAL(
- 함수의 첫 번째 인수 힌트(functiion_num)를 확인하고 실제 사용할 함수 SUM을 선택하거나 해당 번호 9를 입력합니다. => =SUBTOTAL(9
- 쉼표로 구분, 두 번째 인수 힌트(ref1)를 확인하고 실제 사용한 함수 SUM의 인수인 금액(원) 항목의 범위를 클릭 드래그하여 참조합니다. => =SUBTOTAL(9,I7:I69
- 더 이상 인수는 필요 없으므로 그냥 엔터(하나의 함수에서 닫힌 괄호 자동 생성 후 셀 완성) => =SUBTOTAL(9,I7:I69)
결과, 합계 값의 변화는 당연히 없습니다. 필터링 전이니, 전체 합계를 받아오는 것이 맞겠죠. 강원 지역으로 필터링해 보겠습니다.
결과, 필터링된 데이터만 금액(원)의 합계가 계산되어 반환하는 것을 확인할 수 있습니다. 다른 필터링은 직접 해보시기 바랍니다.
고급 필터까지 모두 잘 적용됩니다.
의견글 입니다.
()
