SUBTOTAL 함수

1.SUBTOTAL 함수 정의 란?

SUBTOTAL 함수는 주어진 범위에서 주어진 함수(예: 합, 평균, 카운트 등)를 사용하여 부분 결과를 계산하는 함수입니다. 이 함수는 주로 데이터베이스와 같은 큰 데이터셋에서 부분 합계 및 총계를 계산할 때 유용합니다.

구문:

excelCopy code

SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num: 부분 합계 또는 총계를 계산하는 데 사용할 함수의 번호를 지정합니다.
• ref1, ref2, ...: 계산할 범위를 지정합니다.

function_num 매개변수에는 다양한 함수가 할당되어 있으며, 이 숫자는 특정 함수를 나타냅니다. 예를 들어, 합계를 계산하려면 function_num에 9를 지정하면 됩니다.

일반적인 function_num 값:

• 1: AVERAGE
• 2: COUNT
• 3: COUNTA
• 4: MAX
• 5: MIN
• 6: PRODUCT
• 7: STDEV.S
• 8: STDEV.P
• 9: SUM
• 10: VAR.S
• 11: VAR.P

예를 들어, A1부터 A10까지의 범위에서의 평균을 계산하려면 다음과 같이 사용할 수 있습니다.

excelCopy code

=SUBTOTAL(1, A1:A10)

이렇게 하면 범위 내에서 숨겨진 행은 무시되고 표시된 행만 고려하여 부분 합계를 계산합니다.

2.예시

먼저, SUBTOTAL 함수의 두 번째 매개변수에 들어갈 함수 번호를 알아봅시다. 예를 들어, 평균을 계산하려면 AVERAGE 함수의 번호인 1을 사용합니다.

이제 데이터가 있는 열을 가정하겠습니다. 아래는 데이터가 들어있는 A열의 예시입니다.

cssCopy code

A 1 10 2 15 3 20 4 25 5 30

이제 SUBTOTAL 함수를 사용하여 A열의 숨겨진 값들을 무시하고 표시된 값들을 이용해 부분 평균을 계산하는 예시입니다.

excelCopy code

=SUBTOTAL(1, A1:A5)

이 함수는 A1부터 A5까지의 값들을 이용하여 평균을 계산합니다. 만약 A3이 숨겨져 있다면, A1, A2, A4, A5의 값만을 사용하여 평균을 계산하게 됩니다.

3.사용방법

SUBTOTAL 함수는 주어진 범위에서 숨겨진 행이나 열을 무시하고 다양한 함수(합계, 평균, 최대값 등)를 계산하는데 사용됩니다. SUBTOTAL 함수의 일반적인 구문은 다음과 같습니다.

excelCopy code

=SUBTOTAL(function_num, ref1, [ref2], ...)

• function_num: 계산할 함수의 번호입니다. 이 번호는 숨겨진 행/열을 무시하고 계산할 함수를 나타냅니다. 예를 들어, 합계를 계산하려면 9를 사용하고, 평균을 계산하려면 1을 사용합니다. 함수 번호에 대한 자세한 내용은 Excel 도움말에서 확인할 수 있습니다.
• ref1, ref2, ...: 함수가 적용될 범위나 셀 참조들입니다. 여러 범위를 지정할 수 있습니다.

예를 들어, A1부터 A10까지의 숫자 중에서 숨겨진 행을 무시하고 합계를 계산하려면 다음과 같이 사용할 수 있습니다:

excelCopy code

=SUBTOTAL(9, A1:A10)

이 함수는 A1부터 A10까지의 값 중에서 숨겨진 행을 무시하고 합계를 계산합니다. 함수 번호 9는 숨겨진 행을 무시하고 합계를 계산하는 함수를 나타냅니다.

4.함수 사용팁.

SUBTOTAL 함수를 사용할 때 고려해야 할 몇 가지 사용 팁은 다음과 같습니다:

1. 함수 번호 이해: SUBTOTAL 함수의 첫 번째 인수에는 함수 번호를 지정해야 합니다. 이 함수 번호는 숨겨진 행/열을 무시하고 계산할 특정 함수를 나타냅니다. 함수 번호에 대한 이해가 필요하며, 함수 번호에 따라 다양한 함수가 적용됩니다.
2. 범위 지정: SUBTOTAL 함수의 두 번째 인수 이후에는 계산에 사용할 범위를 지정합니다. 여러 범위를 지정할 수 있습니다. 범위는 주로 셀 참조이며, 필요에 따라 다른 셀 참조를 추가할 수 있습니다.
3. 자동 필터링과 함께 사용: 특히 데이터가 테이블 형태로 정리되어 있고 자동 필터링이 적용된 경우 SUBTOTAL 함수를 활용할 수 있습니다. 이를 통해 특정 조건에 따라 숨겨진 행을 무시하고 계산할 수 있습니다.
4. 상위 수준의 계산: SUBTOTAL 함수를 사용하면 계층적인 계산을 할 수 있습니다. 예를 들어, 주요 합계와 세부 합계를 계산할 때 특정 범위에 대해 SUBTOTAL 함수를 두 번 사용하여 각 수준의 합계를 계산할 수 있습니다.
5. 자동 업데이트: 표의 데이터가 변경될 때마다 SUBTOTAL 함수는 자동으로 업데이트됩니다. 따라서 데이터가 동적으로 변할 때도 정확한 결과를 얻을 수 있습니다.
6. 다양한 함수 적용: SUBTOTAL 함수를 사용하면 다양한 함수를 적용할 수 있습니다. 합계, 평균, 최대값, 최소값 등 다양한 함수를 사용하여 유연하게 계산할 수 있습니다.

이러한 팁을 활용하여 SUBTOTAL 함수를 더 효과적으로 활용할 수 있습니다.

 

5.주의사항

SUBTOTAL 함수를 사용할 때 주의해야 할 몇 가지 사항이 있습니다.

1. 함수 번호 선택: SUBTOTAL 함수의 첫 번째 인수에는 함수 번호를 지정해야 합니다. 이 함수 번호에 따라 다양한 함수가 적용되며, 올바른 함수 번호를 선택해야 합니다. 잘못된 함수 번호를 선택하면 원하는 결과를 얻을 수 없습니다.
2. 필터링된 범위 선택: SUBTOTAL 함수를 사용할 때 계산에 사용되는 범위는 일반적으로 셀 참조입니다. 필터링된 범위를 선택할 때 주의가 필요하며, 필터링된 데이터에 대해 올바른 결과를 얻기 위해 주의깊게 선택해야 합니다.
3. 자동 필터링과의 관련성 이해: SUBTOTAL 함수는 주로 자동 필터링된 데이터에 사용되는데, 이 경우 숨겨진 행을 무시하고 계산합니다. 자동 필터링이 적용되지 않은 경우에는 일반 함수와 동일하게 동작하므로 주의가 필요합니다.
4. 다중 범위 사용 시 주의: SUBTOTAL 함수의 두 번째 인수 이후에는 여러 범위를 지정할 수 있습니다. 그러나 여러 범위를 사용할 때 각 범위의 크기와 일치하는지 확인해야 합니다. 크기가 다르면 예상치 못한 결과가 발생할 수 있습니다.
5. 다른 함수와의 혼용: SUBTOTAL 함수는 주로 다른 함수와 함께 사용되는데, 이때 다른 함수의 결과와 SUBTOTAL 함수의 결과를 정확하게 이해하고 혼동을 피해야 합니다.
6. 계산의 명확성: SUBTOTAL 함수를 사용할 때는 계산의 명확성을 유지하는 것이 중요합니다. 함수 번호와 범위 선택이 명확해야 하며, 다른 사용자나 후속 작업을 위해 주석을 추가하는 것이 좋습니다.

이러한 주의사항을 고려하여 SUBTOTAL 함수를 사용하면 정확하고 일관된 결과를 얻을 수 있습니다.