SUBTOTAL 함수: 수식 및 Excel에서 SUBTOTAL 함수를 사용하는 방법

Excel의 SUBTOTAL 함수는 무엇입니까 ? Excel의 SUBTOTAL 수식은 무엇입니까 ? LuckyTemplates.com과 함께 알아보세요!

Microsoft Excel에는 합계를 계산하는 데 사용되는 많은 기능이 있으며 SUBTOTAL은 그 중 하나입니다. 그러나 Subtotal은 SUM 함수와 다릅니다. 보통 금액이 아니네요. 대신 목록이나 데이터베이스의 소계를 계산하고 평균을 계산하는 등의 작업에 사용됩니다.

SUBTOTAL 함수에 대한 주요 내용

• SUBTOTAL 기능은 무엇이고 어떤 용도로 사용되나요?
• Excel의 SUBTOTAL 수식
• Excel에서 SUBTOTAL 함수를 사용하는 방법
  • 1. 필터링된 행의 합계 계산 
  • 2. 비어 있지 않은 필터링된 셀 수 계산
  • 3. 중첩된 소계 수식의 값을 무시합니다.
• Excel에서 SUBTOTAL 함수를 사용하기 전에 고려해야 할 사항
• Excel에서 SUBTOTAL 함수를 수행할 때 오류가 발생하는 방법

SUBTOTAL 기능은 무엇이고 어떤 용도로 사용되나요?

언뜻 보면 SUBTOTAL 함수의 목적이 무엇인지 궁금할 것입니다. 결국 SUMIF 또는 AVERAGE 와 같은 기존 함수를 사용하면 셀 범위에서 소계를 쉽게 찾을 수 있습니다 .

SUBTOTAL을 반드시 사용할 필요는 없지만 이를 사용하면 대상 데이터를 훨씬 쉽게 분석할 수 있습니다. SUBTOTAL은 SUBTOTAL 수식이 포함된 다른 셀을 무시하므로 전체 합계에 영향을 주지 않고 셀 범위에 SUBTOTAL을 사용하여 수식을 삽입할 수 있습니다.

SUBTOTAL은 셀 필터링과 같은 다른 Excel 기능과 함께 작동합니다. 값으로 표를 필터링하면 SUBTOTAL 수식이 업데이트되고 필터를 인식하며 해당 셀이 제외됩니다. SUBTOTAL은 다른 함수(예: SUM)에서는 수행할 수 없는 숨겨진 값을 무시하는 데도 유용합니다.

다행히도 SUBTOTAL 수식의 일부로 다른 함수를 사용할 수 있습니다. 11가지 소계 방법을 사용할 수 있으며 각각 기존 Excel 기능에 적합합니다. 필터링된 셀에서 작동하고 다른 SUBTOTAL 수식을 무시하는 SUM 수식을 원할 경우 피벗 테이블도 동일한 방식으로 작동할 수 있지만 SUBTOTAL을 사용하는 것이 가장 좋습니다.

Excel의 SUBTOTAL 수식

SUBTOTAL 함수는 다른 수학 함수를 기반으로 셀 범위의 총 값을 계산하여 작동합니다. SUBTOTAL은 최대 11개의 다양한 수학 함수(AVERAGE에서 VAR.P까지)를 사용하여 함수 매개변수 내에서 작업하는 동안(예: 다른 SUBTOTAL 공식 무시) 적절한 총 값을 찾습니다.

SUBTOTAL 함수의 구문은 =SUBTOTAL(function_num, ref1, [ref2],…) 입니다 . 거기에:

• Function_num: 1~11 및 101~111의 숫자는 소계 계산에 사용할 함수를 지정합니다.
• Ref1, Ref2, ...: 소계를 계산하기 위한 하나 이상의 셀 또는 셀 범위, 최대 254개.

메모:

• SUBTOTAL 함수는 데이터의 세로 열을 계산하도록 설계되었습니다.
• 인수 ref1, ref2,...에 SUBTOTAL 함수가 포함되어 있으면 이중 계산을 피하기 위해 무시됩니다.
• function_num이 1부터 11까지인 경우 SUBTOTAL 함수는 해당 범위를 포함하는 행이 숨겨져 있으므로 데이터 세트에 숨겨진 값을 포함하여 계산합니다. function_num이 101부터 111까지인 경우 SUBTOTAL 함수는 데이터 세트에서 숨겨지지 않은 값만 계산합니다(숨겨진 행의 값은 계산하지 않음).
• 필터로 숨겨진 데이터 영역의 경우 SUBTOTAL은 무시됩니다.

Excel에서 SUBTOTAL 함수를 사용하는 방법

1. 필터링된 행의 합계 계산 

이러한 유형의 합계를 더 잘 이해하려면 Excel에서 필터링된 목록 값의 합계 계산 문서를 참조하세요 . 기본적으로 이 경우 SUBTOTAL 함수는 다음과 같습니다.

=소계(9,_범위)

pham_range는 데이터를 필터링한 후 합산하려는 영역입니다.

2. 비어 있지 않은 필터링된 셀 수 계산

SUBTOTAL 3 또는 SUBTOTAL 103을 사용하겠습니다. 그러나 숨겨진 행이 있는 경우 SUBTOTAL 103을 사용하여 표시되는 비어 있지 않은 셀을 정확하게 계산해야 합니다.

예를 들어, 위의 데이터 테이블은 4행과 5행 2개를 숨깁니다. 소계 3 또는 소계 103을 사용하면 두 가지 다른 결과가 생성됩니다.

수식을 입력하면 Excel에서 기억할 필요 없이 선택할 수 있는 함수 세트를 자동으로 표시합니다.

SUBTOTAL 3을 사용하면 결과는 행의 숨겨진 셀을 포함하여 3이 됩니다.

SUBTOTAL 103 의 경우 숨겨진 행은 무시하고 우리가 보는 비어 있지 않은 셀만 표시합니다.

3. 중첩된 소계 수식의 값을 무시합니다.

예를 들어 창고 A1과 A2에 있는 총 직물 킬로그램의 평균을 계산합니다.

창고 A2의 평균 공식은 SUBTOTAL(1,C2:C4) 이고 결과는 19입니다.

창고 A1 = SUBTOTAL(1,C5:C7) 에 대한 평균 공식의 결과는 38입니다.

다만, 2개 창고의 총 원단 수의 평균을 계산할 때 2개 창고의 평균 결과는 무시됩니다. =SUBTOTAL(1,C2:C9) 공식이 있으며 결과는 이전에 계산된 평균 결과를 자동으로 제거합니다.

Excel에서 SUBTOTAL 함수를 사용하기 전에 고려해야 할 사항

SUBTOTAL에는 장점이 있지만 이 기능을 사용하기 전에 다음을 포함하여 고려해야 할 몇 가지 사항이 있습니다.

• 언급한 대로 function_num 인수에 1-11을 사용하면 SUBTOTAL에 숨겨진 값이 포함되고 101-111은 이를 무시합니다.
• 1-11 또는 101-111 이외의 값을 사용하면 Excel에서 #VALUE! 오류가 반환됩니다. 이는 3D 셀 참조(여러 시트에 걸쳐 동일한 셀이 범위에서 참조되는 경우)에서도 발생합니다.
• A1:D1과 같은 가로 셀 범위를 사용하는 경우 (function_num 인수 값과 관계없이) 숨겨진 값이 자동으로 포함됩니다. 이는 소계 기능의 한계이며 극복할 수 없습니다.
• 필터링된 데이터에 SUBTOTAL을 사용하면 사용된 function_num 인수에 관계없이 숨겨진 값이 항상 무시됩니다.
• SUBTOTAL은 IF 함수가 포함된 수식을 포함하여 다른 함수의 일부로 중첩 함수로 사용될 수 있습니다.
• 다른 SUBTOTAL 수식이 데이터 범위 ref1 내에 있으면 SUBTOTAL은 이를 무시하고 전체 계산에서 제외합니다.
• Excel에서는 소계 수식에 최대 254개의 셀 범위를 사용할 수 있지만 수식이 작동하려면 셀 범위가 1개만 필요합니다.

Excel에서 SUBTOTAL 함수를 수행할 때 오류가 발생하는 방법

Excel에서 SUBTOTAL 함수를 계산할 때 다음과 같은 기본적인 오류가 자주 발생합니다.

• #VALUE!: 기능 식별자가 1-11 또는 101-111 범위에 있지 않거나 3D 참조가 있습니다.
• #DIV/0!: 특정 합계를 0으로 나누어야 할 때 발생합니다(예: 숫자 값이 포함되지 않은 셀 범위의 평균 또는 표준 편차 계산).
• #이름? SUBTOTAL 함수 이름의 철자가 잘못되었습니다.

Microsoft Excel에서 SUBTOTAL 함수를 사용하는 방법에 대한 모를 수 있는 몇 가지 팁

팁 1: 두 창고 모두에서 모든 티셔츠 색상이 모든 사이즈로 제공되는지 확인하고 싶다고 가정해 보겠습니다. 다음 단계를 수행할 수 있습니다.

1단계: 소계 를 클릭합니다 . 이 단계는 기존 소계 데이터에 더 많은 기준을 추가하는 것임을 기억하세요.

2단계: 드롭다운 메뉴에서 COUNT를 선택하고 ' 소계 필드 추가 ' 필드에서 크기를 선택합니다 . 그런 다음 현재 부분합 바꾸기 를 선택 취소합니다 . 확인을 클릭하면 다음과 같은 데이터 테이블이 표시됩니다.

이렇게 하면 다양한 차원이 충분히 확보되고 반복 작업이 필요하지 않은 방식으로 데이터를 분류할 수 있습니다.

팁 2 : 데이터를 열별로 정렬하는 것은 항상 유용합니다.

팁 3: 첫 번째 행에 라벨을 포함하는 데 도움이 됩니다.

팁 4: 데이터를 요약하려면 " 소계 삽입 시 데이터 아래 요약 " 확인란을 선택 취소하세요. - SUBTOTAL 함수 삽입 시 아래 데이터를 요약합니다.

간단히 말해서 Excel의 소계 기능은 무엇입니까?

이름에서 알 수 있듯이 Excel의 SUBTOTAL 함수는 소계를 계산하는 데 사용됩니다. Excel SUBTOTAL 함수는 제공된 값에 대한 집계 결과를 반환합니다. SUBTOTAL은 합계, 평균, 개수, 최대값 등을 계산할 수 있습니다. 이 Excel에서는 숨겨진 행의 값을 포함하거나 제외할 수도 있습니다.

• 목적: 목록이나 데이터베이스에서 소계를 계산합니다.
• 반환값: 소계의 종류를 나타내는 숫자.
• 논쟁:
  • function_num- 목록의 소계를 계산하는 데 사용할 함수를 지정하는 숫자입니다.
  • ref1- 명명된 범위 또는 소계에 대한 참조입니다.
  • ref2- [선택 사항] 명명된 범위 또는 소계에 대한 참조입니다.

SUBTOTAL 기능을 사용하면 더 복잡한 피벗 테이블에 의존하지 않고도 데이터 세트를 빠르게 분석할 수 있습니다. 문제가 있는 경우 리본의 데이터 탭에 있는 소계 기능을 사용하여 소계 수식을 자동으로 생성한 다음 다른 곳에서 편집하거나 복사할 수도 있습니다.

더보기:

• Excel의 SUMPRODUCT 함수: 해당 값의 총 곱을 계산합니다.
• 내용을 잃지 않고 Excel에서 2개의 열 전체 이름과 이름을 결합하는 방법