엑셀 subtotal 함수 사용법 및 오류 해결 팁 예제 활용 총정리

안녕하세요, 엑셀 사용자 여러분! 오늘은 엑셀의 강력한 함수 중 하나인 SUBTOTAL 함수에 대해 자세히 알아보겠습니다. 엑셀에서 데이터 분석과 정리를 할 때 매우 유용한 이 함수를 어떻게 사용하고, 어떤 오류를 해결할 수 있는지 예제와 함께 알아보겠습니다.

엑셀 SUBTOTAL 함수란?

엑셀에서 SUBTOTAL 함수는 데이터의 부분합계를 계산하는 데 사용됩니다. 필터링된 데이터 집합에서도 정확하게 작동하는 장점이 있어, 데이터 분석 시 매우 유용합니다. 이 함수는 여러 가지 계산 유형을 지원하여 다양한 방식으로 데이터를 집계할 수 있습니다.

SUBTOTAL 함수의 주요 특징

필터링된 데이터에서도 정확한 계산
다양한 계산 옵션 제공 (합계, 평균, 개수 등)
특정 데이터 집합에 대한 부분 합계 제공

SUBTOTAL 함수는 여러 가지 이유로 유용합니다. 첫째, 데이터를 필터링할 때, 필터링된 데이터만을 대상으로 합계, 평균 등을 계산할 수 있습니다. 둘째, 데이터를 숨기거나 그룹화할 때도 숨겨진 데이터는 계산에서 제외하여 정확한 결과를 제공합니다.

엑셀 SUBTOTAL(9)

SUBTOTAL 함수는 다양한 함수 번호와 함께 사용되는데, 그 중 (9)는 합계를 계산하는 옵션입니다. 즉, SUBTOTAL(9, 데이터 범위)는 지정된 데이터 범위의 합계를 계산합니다.

예제

=SUBTOTAL(9, B2:B10)

위 함수는 B2에서 B10까지의 셀 합계를 계산합니다. 필터를 적용한 경우, 필터된 데이터만 합산됩니다.

이 함수는 판매 데이터를 분석할 때 매우 유용합니다. 예를 들어, 특정 제품의 월별 판매량을 계산할 때 필터를 적용하여 특정 달의 판매량만 합산할 수 있습니다.

실전 예제

월별 판매 데이터를 분석하는 경우:

=SUBTOTAL(9, C2:C100)

위 함수는 C2에서 C100까지의 셀 중 필터링된 데이터의 합계를 계산합니다. 이렇게 하면 특정 달의 판매 데이터를 필터링하여 해당 달의 총 판매량을 쉽게 확인할 수 있습니다.

엑셀 SUBTOTAL(109)

SUBTOTAL(109)는 SUBTOTAL(9)와 유사하지만, 숨겨진 데이터도 무시하고 계산합니다. 이는 데이터를 숨기거나 필터링할 때 더 유용하게 사용할 수 있습니다.

예제

=SUBTOTAL(109, B2:B10)

이 함수는 필터링되거나 숨겨진 데이터를 제외한 B2에서 B10까지의 셀 합계를 계산합니다.

예를 들어, 직원의 월별 근무 시간을 계산할 때, 특정 직원의 데이터를 숨기고 계산하려면 SUBTOTAL(109)를 사용하면 됩니다.

실전 예제

직원 근무 시간 데이터를 분석하는 경우:

=SUBTOTAL(109, D2:D100)

위 함수는 D2에서 D100까지의 셀 중 필터링되거나 숨겨진 데이터를 제외한 근무 시간의 합계를 계산합니다. 이렇게 하면 특정 직원의 근무 시간을 숨기고 나머지 직원의 총 근무 시간을 정확하게 계산할 수 있습니다.

엑셀 SUBTOTAL 사용법

SUBTOTAL 함수의 사용법은 매우 간단합니다. 기본적인 구조는 SUBTOTAL(함수 번호, 데이터 범위)입니다. 함수 번호는 1에서 11, 그리고 101에서 111까지 있으며, 각각 다른 유형의 계산을 의미합니다.

함수 번호 설명

1 또는 101: AVERAGE(평균)
2 또는 102: COUNT(숫자 개수)
3 또는 103: COUNTA(비어 있지 않은 셀 개수)
4 또는 104: MAX(최대값)
5 또는 105: MIN(최소값)
6 또는 106: PRODUCT(곱)
7 또는 107: STDEV(표준편차)
8 또는 108: STDEVP(모집단 표준편차)
9 또는 109: SUM(합계)
10 또는 110: VAR(분산)
11 또는 111: VARP(모집단 분산)

예제

데이터 범위 A2에서 A10까지의 평균을 구하고 싶다면,

=SUBTOTAL(1, A2:A10)

필터링된 데이터의 합계를 구하려면,

=SUBTOTAL(109, A2:A10)

이처럼 SUBTOTAL 함수는 데이터 분석 시 매우 유용하게 사용할 수 있습니다.

실전 예제

데이터 분석에서 특정 범위의 평균을 구할 때:

=SUBTOTAL(1, A2:A10)

위 함수는 A2에서 A10까지의 데이터 범위의 평균을 계산합니다. 필터링된 데이터만을 대상으로 하므로, 필터를 적용하여 특정 조건을 만족하는 데이터의 평균을 쉽게 구할 수 있습니다.

엑셀 SUBTOTAL SUM 차이

SUBTOTAL과 SUM 함수의 가장 큰 차이점은 SUBTOTAL이 필터링된 데이터에 대해서만 계산한다는 것입니다. SUM 함수는 모든 데이터를 합산하지만, SUBTOTAL은 필터링된 데이터만 계산하여 더 정확한 결과를 제공합니다.

예제

=SUM(B2:B10)
=SUBTOTAL(9, B2:B10)

위 두 함수는 필터를 적용하지 않았을 때는 같은 결과를 반환하지만, 필터를 적용하면 SUBTOTAL만이 정확한 필터된 합계를 제공합니다.

실전 예제

판매 데이터 분석 시:

=SUM(C2:C100)
=SUBTOTAL(9, C2:C100)

이 두 함수는 필터를 적용하지 않았을 때 동일한 결과를 반환하지만, 필터를 적용하면 SUBTOTAL 함수가 필터링된 데이터만을 합산하여 더 정확한 결과를 제공합니다.

엑셀 SUBTOTAL COUNTIF

SUBTOTAL 함수는 직접적으로 COUNTIF를 지원하지 않지만, 특정 조건을 만족하는 데이터의 개수를 세고 싶다면 이를 결합해서 사용할 수 있습니다. 필터링된 데이터에서 특정 조건을 만족하는 개수를 세려면 SUBTOTAL과 COUNTIF를 함께 사용하면 됩니다.

예제

필터링된 데이터에서 특정 조건을 만족하는 셀 개수를 세는 방법:

=SUMPRODUCT(SUBTOTAL(3, OFFSET(B2, ROW(B2:B10)-MIN(ROW(B2:B10)), 0)), --(B2:B10="조건"))

실전 예제

판매 데이터에서 특정 제품의 개수를 세는 경우:

=SUMPRODUCT(SUBTOTAL(3, OFFSET(C2, ROW(C2:C100)-MIN(ROW(C2:C100)), 0)), --(C2:C100="제품A"))

위 함수는 필터링된 데이터에서 “제품A”에 해당하는 셀 개수를 계산합니다. 이렇게 하면 특정 제품의 판매 개수를 쉽게 확인할 수 있습니다.

SUBTOTAL 함수

SUBTOTAL 함수는 다양한 경우에 유용하게 사용될 수 있습니다. 특히 데이터 분석 및 보고서 작성 시 필터링된 데이터의 통계를 정확하게 계산할 수 있어 매우 유용합니다.

실전 예제

예를 들어, 판매 데이터를 분석할 때 필터를 적용하여 특정 지역의 판매량만 합산하거나, 특정 기간의 평균 판매량을 계산할 수 있습니다.

=SUBTOTAL(109, C2:C100)

위 함수는 C2에서 C100까지의 셀 중 필터링된 데이터의 합계를 계산합니다. 이렇게 하면 특정 지역의 판매 데이터를 필터링하여 해당 지역의 총 판매량을 쉽게 확인할 수 있습니다.

엑셀 SUBTOTAL 오류

SUBTOTAL 함수 사용 중 발생할 수 있는 오류와 그 해결 방법에 대해 알아보겠습니다. 가장 흔한 오류는 데이터 범위에 문제가 있거나, 함수 번호를 잘못 입력한 경우입니다.

흔한 오류 및 해결 방법

#VALUE! 오류: 함수에 잘못된 인수가 사용된 경우 발생합니다.

해결 방법: 함수 번호와 데이터 범위를 확인하고 올바르게 입력했는지 확인합니다.

#REF! 오류: 참조 범위가 잘못되었을 때 발생합니다.

해결 방법: 참조 범위가 올바르게 설정되었는지 확인합니다.

#DIV/0! 오류: 데이터가 비어있거나 0으로 나누는 경우 발생합니다.

해결 방법: 데이터 범위에 유효한 데이터가 있는지 확인합니다.

실전 예제

#VALUE! 오류 해결:

=SUBTOTAL("9", A2:A10)  // 잘못된 인수
=SUBTOTAL(9, A2:A10)    // 올바른 인수

이처럼 잘못된 인수를 사용하면 오류가 발생할 수 있으며, 올바른 인수를 사용하여 문제를 해결할 수 있습니다.

subtotal 함수 사용하는 이유

왜 SUBTOTAL 함수를 사용하는지에 대해 알아보겠습니다. SUBTOTAL 함수는 다양한 이유로 매우 유용합니다.

사용 이유

필터링된 데이터의 정확한 계산: 필터링된 데이터만 계산하여 더 정확한 분석 결과를 제공합니다.
다양한 계산 옵션: 다양한 함수 번호를 통해 합계, 평균, 최대값 등 다양한 계산을 할 수 있습니다.
숨겨진 데이터 무시: 숨겨진 데이터를 무시하고 계산할 수 있어 보고서 작성 시 유용합니다.

실전 예제

보고서 작성 시 특정 데이터 집합의 평균을 계산하는 경우:

=SUBTOTAL(101, B2:B100)

위 함수는 B2에서 B100까지의 셀 중 숨겨진 데이터를 제외한 평균을 계산합니다. 이렇게 하면 보고서 작성 시 더 정확한 데이터를 제공합니다.

자주 묻는 질문 (FAQ)

1. `SUBTOTAL` 함수와 `SUM` 함수의 차이점은 무엇인가요?

SUBTOTAL 함수는 필터링된 데이터만 계산하고, SUM 함수는 모든 데이터를 계산합니다. 따라서 필터링된 데이터 집합에서 정확한 결과를 얻기 위해서는 SUBTOTAL 함수를 사용하는 것이 좋습니다.

2. `SUBTOTAL(9)`와 `SUBTOTAL(109)`의 차이점은 무엇인가요?

SUBTOTAL(9)는 필터링된 데이터만 계산하고, SUBTOTAL(109)는 필터링된 데이터와 숨겨진 데이터를 무시하고 계산합니다. 이는 숨겨진 데이터가 있는 경우 SUBTOTAL(109)를 사용하여 정확한 결과를 얻을 수 있습니다.

3. `SUBTOTAL` 함수에서 사용할 수 있는 함수 번호는 무엇이 있나요?

SUBTOTAL 함수는 1에서 11, 101에서 111까지의 함수 번호를 사용할 수 있으며, 각각 다른 계산 유형을 나타냅니다. 예를 들어, 1 또는 101은 평균을 계산하고, 9 또는 109는 합계를 계산합니다.

4. `SUBTOTAL` 함수를 사용할 때 발생할 수 있는 오류는 무엇이 있나요?

가장 흔한 오류는 #VALUE!, #REF!, #DIV/0! 오류입니다. 이는 잘못된 인수, 잘못된 참조 범위, 데이터 부족 등이 원인일 수 있습니다. 올바른 인수와 참조 범위를 사용하여 이러한 오류를 해결할 수 있습니다.

5. `SUBTOTAL` 함수는 어떤 경우에 사용하면 좋나요?

필터링된 데이터의 부분합을 계산할 때, 숨겨진 데이터를 무시하고 계산할 때, 데이터 분석 시 다양한 통계를 계산할 때 유용합니다. 예를 들어, 판매 데이터를 분석할 때 특정 조건을 만족하는 데이터의 합계를 계산하는 데 매우 유용합니다.

이렇게 해서 엑셀의 SUBTOTAL 함수에 대해 전반적으로 알아보았습니다. 데이터 분석 시 유용하게 사용할 수 있는 이 함수를 통해 더 정확하고 효율적인 데이터 관리를 하시길 바랍니다. 감사합니다!

마이크로소프트 엑셀지원센터