엑셀 SUBTOTAL 함수로 필터링된 값의 합계 구하는 방법 알아보자

엑셀에서 데이터를 다룰 때 필터 기능은 매우 유용합니다. 특정 조건에 맞는 데이터만 선택하여 분석할 수 있는데, 이때 합계를 구하는 것이 필요한 경우가 많습니다. 일반적인 합계 함수인 SUM은 필터링된 값이 아닌 전체 데이터의 합계를 계산하기 때문에, SUBTOTAL 함수가 필요합니다. 이 함수는 필터링된 데이터에 대해서만 계산을 수행하여 정확한 결과를 제공합니다. 엑셀에서 필터 값을 이용한 합계를 손쉽게 구하는 방법을 아래 글에서 자세하게 알아봅시다.

엑셀에서 SUBTOTAL 함수의 이해

SUBTOTAL 함수란?

SUBTOTAL 함수는 엑셀에서 데이터 분석을 보다 효율적으로 할 수 있도록 돕는 다기능 함수입니다. 이 함수는 다양한 통계 값을 계산할 수 있도록 설계되었으며, 특히 필터링된 데이터에 대해서만 결과를 제공하는 점이 큰 장점입니다. 일반적인 SUM 함수와의 가장 큰 차이점은, SUBTOTAL 함수를 사용할 경우 사용자가 설정한 필터 조건에 맞춰 남아있는 데이터에 대한 합계를 계산할 수 있다는 것입니다. 예를 들어, 특정 카테고리의 판매 데이터를 필터링하고 그 합계를 알고 싶을 때 유용하게 활용할 수 있습니다.

SUBTOTAL 함수의 구문

SUBTOTAL 함수는 다음과 같은 구문으로 사용됩니다: `=SUBTOTAL(function_num, ref1, [ref2], …)`. 여기서 function_num은 수행하려는 계산의 종류를 지정하며, ref1부터는 계산할 범위를 지정합니다. function_num에는 1부터 11까지의 숫자가 들어가며 각각 평균, 개수, 최대값 등의 다양한 계산 기능을 제공합니다. 예를 들어, SUM을 원할 경우 function_num에 9를 입력하면 됩니다.

필터와 함께 사용하는 방법

실제로 필터와 함께 SUBTOTAL 함수를 사용하는 방법은 매우 간단합니다. 먼저 원하는 데이터를 선택하여 필터를 적용한 후, SUBTOTAL 함수를 사용하는 셀에 해당 범위를 지정하면 됩니다. 이렇게 하면 필터로 숨겨진 데이터는 합산하지 않고 오직 표시된 데이터만 합쳐져 정확한 결과를 보여줍니다. 따라서 보고서를 작성하거나 데이터를 분석할 때 신뢰성 있는 정보를 얻을 수 있게 됩니다.

실제 예시로 알아보자

예시 데이터 준비하기

우선 실습을 위해 간단한 예시 데이터를 준비해 보겠습니다. 아래 표는 판매 데이터로 구성되어 있습니다:

상품명	카테고리	판매량	가격
A상품	전자기기	10	1000
B상품	가전제품	5	1500
C상품	전자기기	8	2000
D상품	가전제품	12	1200
E상품	전자기기	7	3000

위 표에서 ‘카테고리’ 열을 기준으로 전자기기로 필터링하였다고 가정해 봅시다.

SUBTOTAL 사용하기 위한 단계적 접근법

1. 먼저 ‘카테고리’ 열에서 전자기기를 선택하여 필터링합니다.
2. 그런 다음 ‘판매량’ 또는 ‘가격’ 열 옆에 새로운 셀을 선택하고 `=SUBTOTAL(9,C2:C6)`라고 입력합니다.
– 여기서 C2:C6 범위는 판매량이 있는 셀 범위를 나타냅니다.
– function_num인 9는 SUM 기능을 의미합니다.
3. 이 공식을 입력하면 필터링된 전자기기의 총 판매량이 자동으로 계산됩니다.

SORT 및 FILTER와 함께 사용하기

엑셀에서는 SORT 기능과 FILTER 기능도 함께 사용할 수 있어 더욱 강력한 분석 도구로 변모합니다. 만약 특정 가격 이상인 전자기기를 찾고 싶다면 FILTER함수를 통해 가격 조건으로 전자기기를 추출하고, 그 결과에 대해 다시 SUBTOTAL 함수를 적용하여 최종적인 합계를 구하는 방식입니다. 이를 통해 다양한 조건부 분석이 가능해집니다.

SIMPLE EXAMPLE: SUM 및 AVERAGE 비교하기

SIMPLE DATA SET 생성하기

아래와 같이 간단한 숫자 데이터를 만들어 보겠습니다:

< td >10%

< tr >< td >A002 < td >15%< tr >< td >A003 < td >20%

ID 번호	% 증가율
A001

위 표와 같은 데이터셋에서 전체 % 증가율의 평균과 총합을 비교해 보겠습니다.

SIMPLE COMPARISON: 일반 SUM vs SUBTOTAL

일반 SUM 함수를 사용할 경우 전체 % 증가율의 총합과 평균은 다음과 같습니다:
– Total Increase = 일반 SUM 사용 시 % 증가율 모두 더함
– Average Increase = 일반 AVERAGE 사용 시 % 증가율 모두 평균 낸 값

하지만 SUBTOTAL 함수 이용 시에는 특정 조건(예: ID 번호 A001 제외)으로 인해 다른 결과가 나올 것입니다. 이렇게 두 가지 방법을 통해 동일한 데이터셋에서도 서로 다른 결과를 얻어낼 수 있습니다.

SPECIAL CASE: 오류 처리하기

엑셀에서는 때때로 오류가 발생할 수 있는데, 이럴 경우 IFERROR 또는 ISERROR 함수를 조합하여 효과적으로 오류를 처리하는 것이 좋습니다. 예를 들어 `=IFERROR(SUBTOTAL(9,C2:C6), “데이터 없음”)`라고 입력하면 오류 발생 시 “데이터 없음”이라는 문구가 출력되어 사용자에게 친절한 안내 메시지를 제공할 수 있습니다.

SUBTOTAL 활용 팁 및 주의사항

SORTING 후 주의사항

데이터 정렬 후에도 SUBTOTAL 함수는 정상 작동하지만 주의해야 할 점은 정렬된 항목들이 올바르게 그룹화되어야 한다는 것입니다. 그렇지 않으면 잘못된 데이터 집합에 대해 합산이 이루어질 위험이 있습니다.

Slicing & Dicing: 여러 조건 추가하기 < / h 3 >

여러 개의 조건부 필터가 필요한 경우에는 각 조건별로 별도의 SUBTOTAL 함수를 사용하는 것도 좋은 접근법입니다. 여러 개의 카테고리를 동시에 고려하여 복잡한 분석 작업이 가능합니다.

PIVOT TABLE과 결합하기 < / h 3 >

마지막으로 PIVOT TABLE과 결합하여 활용하는 것도 추천드립니다. PIVOT TABLE 안에서도 SUBTOTAL 기능이 포함되어 있으므로 보다 복잡한 데이터 집계 작업도 손쉽게 처리할 수 있습니다.

마지막으로 정리하면서

SUBTOTAL 함수는 엑셀에서 데이터 분석을 보다 효율적으로 진행할 수 있게 해주는 중요한 도구입니다. 필터링된 데이터에 대해서만 계산 결과를 제공하는 기능 덕분에, 사용자는 필요한 정보에만 집중할 수 있습니다. 다양한 통계 값을 지원하며, 오류 처리 기능과 결합하면 더욱 효과적인 데이터 분석이 가능합니다. 이 함수를 활용하면 보고서 작성 및 의사결정 과정에서 신뢰성 있는 데이터를 얻을 수 있습니다.

참고할만한 추가 자료

1. Microsoft 공식 엑셀 도움말 페이지: SUBTOTAL 함수 설명

2. 유튜브 튜토리얼: 엑셀 SUBTOTAL 함수 사용법 소개

3. 블로그 포스트: 엑셀에서 데이터 필터링과 SUBTOTAL의 활용 사례

4. 온라인 강의: 데이터 분석을 위한 엑셀 기초부터 고급까지

5. 전자책: 엑셀로 배우는 데이터 분석 기법 모음집

중요 포인트 요약

1. SUBTOTAL 함수는 필터링된 데이터에 대해서만 계산 결과를 제공한다.

2. 다양한 통계 값(합계, 평균 등)을 지원하여 유용하게 활용할 수 있다.

3. 오류 처리 기능을 통해 보다 사용자 친화적인 결과를 얻을 수 있다.

4. SORT와 FILTER 기능과 결합하여 복잡한 데이터 분석이 가능하다.

5. PIVOT TABLE과 함께 사용하면 더욱 강력한 데이터 집계가 가능하다.

조금 더 자세히 보기 1

조금 더 자세히 보기 2