엑셀(EXCEL) 중복연산을 피해 부분합을 구하고, 숨긴 셀을 선택하는 SUBTOTAL 함수(분기별 합계, 소계, 총계 구하기)

데이터들의 총합이나 평균을 구하고는 합니다.

이 때 전체 평균, 월 평균을 나누어 구하거나 분기 합계와 전체 합계를 구하기도 합니다.

기존의 SUM이나 AVERAGE 함수로는 불편한 것을 경험해 보셨을 껀데요.

이런 경우에서 편리하게 사용하는 SUBTOTAL을 소개합니다.

1. SUBTOTAL 함수

SUBTOTAL(function_num,ref1,[ref2],...) : 계산할 연산을 선택하고 범위를 설정합니다. 값을 계산할 때 숨긴 셀을 포함할 것인지 선택할 수도 있습니다. 범위 내의 다른 SUBTOTAL 함수는 무시합니다.

• function_num : 소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111입니다. 1-11에는 수동으로 숨겨진 행이 포함되는 반면 101-111은 해당 행을 제외합니다. 함수의 목록은 아래에 표에 정리했습니다.
• ref1, [ref2] : 계산을 수행할 값입니다. 범위로 입력해도 되고, 숫자를 직접 입력해도됩니다. 숫자, 범위, 셀을 따로따로 나누어 입력할 때 254개가 입력이 가능합니다.

데이터베이스 함수로 여러가지 옵션을 지원하는 만능함수이기도 합니다.

표는 function_num에 입력할 수 있는 기능으로 엑셀 도움말에서 가져왔습니다.

2. 숨긴 행에 대해서 연산하기

SUBTOTAL의 function_num에서 SUM을 의미하는 "9"와 "109"의 차이에 대해서 알아보겠습니다.

109는 셀을 숨기면 값이 변하지만, 9는 숨기든 말든 계속계산합니다.

다만 필터로 숨기면 둘 다 포함하지 않습니다.

3. 부분 합 구하기

SUBTOTAL 함수의 중요한 특성은 SUBTOTAL끼리 연산을 하지 않는 것입니다.

이게 얼마나 편리하냐면 하기표를 보시면 이해가 될 것입니다.

표 중간중간에 부분 합 혹은 부분 평균을 SUBTOTAL을 구하면, 전체를 구할때 포함되지 않습니다.

이런 식의 양식은 자주 만들게 되니 사용할 여지가 많은 함수입니다.

편리한 함수이기는 하지만 그냥 SUM이나 + 연산자로도 일단 해결은 됩니다.

그래서 쓰지는 않는 경우가 많지만 문서를 만들다보면 깔끔한 함수구성이 중요한 걸 알 수 있습니다.

알아두고 있다가 사용해 보시길 바랍니다.