데이터 분석, 엑셀 없이는 상상도 못하죠?! 특히 복잡한 데이터를 다룰 때, SUBTOTAL 함수는 정말 숨겨진 보석과 같습니다. SUM 함수와 비슷해 보이지만, 숨겨진 데이터를 제외하고 계산하는 기능 덕분에 실무에선 완전 필수죠! 이 포스팅에서는 SUBTOTAL 함수의 다양한 기능과 활용법을 파헤쳐 보고, 실제 예시를 통해 데이터 분석 능력을 한 단계 업그레이드해보자고요~!
SUBTOTAL 함수: SUM 함수와는 다른 매력
SUBTOTAL 함수, 뭐하는 녀석일까요? 간단히 말하면, 리스트나 데이터베이스에서 부분합을 계산해주는 함수입니다. SUM 함수는 숨겨진 셀까지 모두 계산하지만, SUBTOTAL 함수는 숨겨진 행이나 필터링된 데이터를 제외하고 계산할 수 있다는 점이 가장 큰 차이점입니다. 이 덕분에 보고서 작성 시 특정 조건에 맞는 데이터만 쏙쏙 골라 계산할 수 있어 효율이 엄청나게 올라갑니다. 게다가 합계(SUM)뿐 아니라 평균(AVERAGE), 개수(COUNT), 최댓값(MAX), 최솟값(MIN) 등 총 11가지 기능까지?! 정말 만능 재주꾼이 따로 없네요!
SUBTOTAL 함수의 기본 구문
=SUBTOTAL(function_num, ref1, [ref2], ...)
function_num: 어떤 계산을 할지 정하는 숫자입니다. 1부터 11까지, 그리고 101부터 109까지 총 22가지 옵션이 있어요! 1-11은 숨겨진 값을 포함, 101-109는 숨겨진 값을 제외하고 계산합니다. 합계를 구하고 싶다면 9 또는 109를 사용하면 됩니다.ref1, ref2,...: 계산할 셀 범위를 지정합니다. 여러 범위도 한꺼번에 넣을 수 있어요!
숨겨진 데이터 제외 합계: 실무 예제
자, 이제 실제 데이터로 SUBTOTAL 함수의 위력을 직접 확인해 볼 시간입니다! 아래 표는 지역별 분기 판매량 데이터입니다.
| 지역 | 1분기 | 2분기 | 3분기 | 4분기 |
|---|---|---|---|---|
| 서울 | 100 | 120 | 150 | 180 |
| 부산 | 80 | 90 | 110 | 130 |
| 대구 | 70 | 80 | 100 | 120 |
| 광주 | 60 | 70 | 90 | 110 |
| 대전 | 50 | 60 | 80 | 100 |
SUM 함수의 함정?!
만약 부산 지역 데이터를 숨기고 전체 판매량 합계를 구한다면? SUM 함수는 숨겨진 데이터까지 모두 더해버립니다. (으악!)
=SUM(B2:E6) '숨겨진 행 포함 결과: 1960
SUBTOTAL 함수로 정확하게 계산하기
SUBTOTAL 함수를 사용하면 숨겨진 부산 데이터는 쏙 빼고 계산할 수 있습니다!
=SUBTOTAL(109, B2:E6) '숨겨진 행 제외 결과: 1600
function_num에 109를 넣으면 숨겨진 행은 계산에서 제외됩니다. 필터링이나 수동 숨김으로 제외된 데이터도 마찬가지! 원하는 결과만 깔끔하게 얻을 수 있죠.
SUBTOTAL 함수, 이것만 알면 데이터 분석 마스터!
SUBTOTAL 함수는 합계 외에도 다양한 계산 기능을 제공합니다. 아래 표를 참고해 보세요!
| function_num | 기능 | 숨겨진 값 |
|---|---|---|
| 1 (101) | AVERAGE | 포함/미포함 |
| 2 (102) | COUNT | 포함/미포함 |
| 3 (103) | COUNTA | 포함/미포함 |
| 4 (104) | MAX | 포함/미포함 |
| 5 (105) | MIN | 포함/미포함 |
| 9 (109) | SUM | 포함/미포함 |
| 10 (110) | VAR | 포함/미포함 |
| 11 (111) | STDEV | 포함/미포함 |
필터링된 데이터? 문제없어요!
SUBTOTAL 함수는 필터링된 데이터 분석에도 찰떡궁합입니다. 특정 분기 데이터만 필터링하고 합계를 구할 때, SUBTOTAL 함수는 필터링된 데이터만 계산에 포함합니다. 얼마나 편리한가요?
여러 범위, 한 번에 계산
SUBTOTAL 함수는 여러 범위의 데이터도 한 방에 계산할 수 있습니다. 예를 들어 여러 시트의 특정 범위 데이터 합계를 한 번에 짠! 하고 구할 수 있죠.
SUBTOTAL 함수와 OFFSET 함수의 조합: 동적 범위 계산
SUBTOTAL 함수를 OFFSET 함수와 함께 사용하면 더욱 강력한 기능을 활용할 수 있습니다. OFFSET 함수는 특정 셀을 기준으로 지정된 행과 열만큼 떨어진 셀 또는 범위를 반환하는 함수입니다. 이를 SUBTOTAL 함수와 결합하면 데이터가 추가되거나 삭제될 때 자동으로 범위가 조정되는 동적인 부분합 계산이 가능해집니다. 예를 들어, 아래와 같이 사용할 수 있습니다.
=SUBTOTAL(109,OFFSET(A1,1,0,COUNTA(A:A)-1,1))
이 수식은 A열의 데이터 개수에 따라 자동으로 범위를 조정하여 합계를 계산합니다. A열에 데이터가 추가되거나 삭제되면 SUBTOTAL 함수의 계산 범위도 자동으로 업데이트됩니다.
마치며
SUBTOTAL 함수, 정말 놀랍지 않나요? 단순한 합계 계산을 넘어 숨겨진 데이터, 필터링된 데이터까지 정확하게 처리하여 다양한 계산을 수행할 수 있는 강력한 도구입니다. 데이터 분석, 보고서 작성 시 SUBTOTAL 함수를 적극 활용하여 업무 효율을 높이고 정확한 분석 결과를 얻으세요! 다음 포스팅에서는 SUBTOTAL 함수와 필터 기능을 결합한 고급 활용법을 소개할 예정이니 기대해주세요! 😉