공식 예제와 함께 Excel 하위 운동 기능

이 자습서는 Excel의 하위 운동 함수의 특이성을 설명하고 소계 공식을 사용하여 가시 셀의 데이터를 요약하는 방법을 보여줍니다.

이전 기사에서는 하위 토탈 기능을 사용하여 Excel에 서브 토탈을 삽입하는 자동 방법에 대해 논의했습니다. 오늘, 당신은 스스로 소계 공식을 작성하는 방법과 이것이 어떤 장점을 제공하는지 배웁니다.

• Excel의 하위 기능 - 구문 및 사용
• Excel에서 하위 운동 기능을 사용해야하는 3 가지 이유
• 소계 공식 예
• Excel Subtotal은 작동하지 않습니다

Excel 하위 운동 기능 - 구문 및 사용

Microsoft는 Excel Subtotal을 목록 또는 데이터베이스에서 하위 운동을 반환하는 함수로 정의합니다. 이러한 맥락에서, "하위 운동"은 정의 된 셀 범위에서 총 숫자가 아닙니다. 하나의 구체적인 일만 수행하도록 설계된 다른 Excel 함수와 달리, 소계는 놀랍도록 다재다능합니다. 셀 계산, 평균 계산, 최소 또는 최대 값 찾기 등과 같은 다른 산술 및 논리적 작업을 수행 할 수 있습니다.

하위 운동 기능은 모든 버전의 Excel 2016, Excel 2013, Excel 2010, Excel 2007 및 Lower의 모든 버전에서 사용할 수 있습니다.

Excel 하위 운동 기능의 구문은 다음과 같습니다.

subtotal (function_num, ref1, [ref2],…)

어디:

• function_num- 하위 토탈에 사용할 기능을 지정하는 숫자.
• ref1, ref2, … -- 하나 이상의 세포 또는 범위에 대한 범위. 첫 번째 Ref 인수는 필요하며, 다른 사람 (최대 254 명)은 선택 사항입니다.

function_num 인수는 다음 세트 중 하나에 속할 수 있습니다.

• 1-11 여과 된 셀을 무시하지만 수동으로 숨겨진 행을 포함합니다.
• 101-111은 모든 숨겨진 셀을 무시합니다 - 수동으로 필터링되고 숨겨진 숨겨집니다.

function_num		기능	설명
1	101	평균	숫자의 평균을 반환합니다.
2	102	세다	숫자 값을 포함하는 세포를 계산합니다.
3	103	카운타	비어있는 세포를 계산합니다.
4	104	맥스	가장 큰 가치를 반환합니다.
5	105	최소	가장 작은 값을 반환합니다.
6	106	제품	세포의 생성물을 계산합니다.
7	107	stdev	숫자 샘플을 기준으로 모집단의 표준 편차를 반환합니다.
8	108	stdevp	전체 수치에 따라 표준 편차를 반환합니다.
9	109	합집합	숫자를 추가합니다.
10	110	var	숫자 샘플을 기반으로 인구의 분산을 추정합니다.
11	111	varp	전체 인구에 기초하여 인구의 분산을 추정합니다.

실제로 모든 기능 번호를 암기 할 필요가 없습니다. 셀 또는 포뮬러 바에서 하위 공식을 입력하기 시작하자마자 Microsoft Excel은 사용 가능한 기능 번호 목록을 표시합니다.

예를 들어, 이것은 셀 C2에서 C8의 값을 요약하기 위해 소계 9 공식을 만들 수있는 방법입니다.

공식에 함수 번호를 추가하려면이를 두 번 클릭 한 다음 쉼표를 입력하고 범위를 지정하고 닫는 괄호를 입력 한 다음 Enter를 누릅니다. 완성 된 공식은 다음과 같습니다.

=SUBTOTAL(9,C2:C8)

비슷한 방식으로, 당신은 소계 1 공식을 작성하여 평균, 하위 토탈 2를 숫자를 가진 셀을 계산하고, 맹장이 아닌 셀을 세우기위한 셀 3 등을 얻을 수 있습니다. 다음 스크린 샷은 몇 가지 다른 공식을 보여줍니다.

메모. 합 또는 평균과 같은 요약 함수를 가진 하위 운동 공식을 사용하면 빈 값을 포함하는 숫자와 셀을 무시하는 숫자를 가진 셀 만 계산합니다.

Excel에서 하위 공식을 만드는 방법을 알았으므로 주요 질문은 왜 배우는 데 어려움을 겪고 싶습니까? Sum, Count, Max 등과 같은 일반 기능을 단순히 사용하지 않겠습니까? 아래에 답을 찾을 수 있습니다.

Excel에서 하위 운동을 사용해야하는 3 가지 이유

전통적인 Excel 함수와 비교하여 Subtotal은 다음과 같은 중요한 이점을 제공합니다.

1. 필터링 된 행에서 값을 계산합니다

Excel 하위 운동 함수는 필터링 행의 값을 무시하기 때문에이를 사용하여 하위 운동 값이 필터에 따라 자동으로 재 계산되는 동적 데이터 요약을 작성할 수 있습니다.

예를 들어, 동부 지역의 판매 만 표시하도록 테이블을 필터링하면 다른 모든 지역이 총계에서 제거되도록 하위 토탈 공식이 자동으로 조정됩니다.

메모. 두 함수 번호 세트 (1-11 및 101-111)는 필터링 된 셀을 무시하기 때문에이 경우 에테르 하위 토탈 9 또는 하위 토탈 109 공식을 사용할 수 있습니다.

2. 가시 세포 만 계산합니다

당신이 기억 하듯이, function_num 101 ~ 111을 갖는 소계 공식은 모든 숨겨진 셀을 무시합니다 - 수동으로 필터링되고 숨겨진 숨겨집니다. 따라서 Excel의 숨기기 기능을 사용하여 View에서 관련없는 데이터를 제거하면 기능 번호 101-111을 사용하여 하위 토탈에서 숨겨진 행의 값을 제외하십시오.

다음 예는 작동 방식에 대한 이해를 더 잘 이해하는 데 도움이됩니다. 하위 토탈 9 vs. Subtotal 109.

3. 중첩 된 하위 운동 공식의 값을 무시하십시오

Excel 소계 공식에 제공된 범위에 다른 하위 변형 공식이 포함되어 있으면 중첩 된 하위 토탈이 무시되므로 동일한 숫자가 두 번 계산되지 않습니다. 굉장하지 않습니까?

아래의 스크린 샷에서, 그랜드 평균 공식 SUBTOTAL(1, C2:C10) 마치 2 개의 별도 범위 AVERAGE(C2:C5, C7:C9) .

Excel에서의 소계 사용 - 공식 예제

처음에 소계를 만나면 복잡하고 까다 롭고 무의미 해 보일 수 있습니다. 그러나 일단 당신이 황동 압정에 도달하면, 당신은 그것이 마스터하기가 어렵지 않다는 것을 알게 될 것입니다. 다음 예제는 몇 가지 유용한 팁과 영감을주는 아이디어를 보여줍니다.

예 1. 서브 토탈 9 대 하위 운동 109

이미 알고 있듯이 Excel Subtotal은 2 개의 함수 숫자 세트를 수용합니다 : 1-11 및 101-111. 두 세트 모두 필터링 된 행을 무시하지만 숫자 1-11에는 수동으로 숨겨진 행이 포함되며 101-111은 제외합니다. 차이점을 더 잘 이해하려면 다음 예를 고려해 봅시다.

총 필터링 행 에 아래 스크린 샷에 표시된 것과 같이 하위 토탈 9 또는 하위 토탈 109 공식을 사용할 수 있습니다.

그러나 홈 탭> 셀 그룹> 형식 > 형식 및 호흡을 마우스 오른쪽 버튼으로 클릭 한 다음 숨기기 를 클릭하여 홈 탭의 숨기기 행 명령을 사용하여 수동으로 무관 한 항목이 숨겨져 있는 경우, 가시적 인 행에서만 총 값만을 원하시면 유일한 옵션입니다.

다른 함수 번호는 같은 방식으로 작동합니다. 예를 들어, 비-냉방 필터링 된 셀을 세는 것은 서브 토탈 3 또는 서브 토탈 103 공식이 수행됩니다. 그러나 범위에 숨겨진 행이 있으면 가시적 인 비 냉방을 올바르게 계산할 수 있습니다.

메모. function_num 101-111을 갖는 Excel subtotal 함수는 숨겨진 행에서 값을 무시하지만 숨겨진 열 에서는 그렇지 않습니다. 예를 들어, SUBTOTAL(109, A1:E1) 과 같은 공식을 수평 범위의 합 수로 사용하는 경우 열을 숨기는 것은 하위 운동에 영향을 미치지 않습니다.

예제 2. 데이터를 동적으로 요약하기 위해 서브 토르가 있다면

다양한 데이터 요약을 표시해야하지만 모든 것을위한 공간이없는 경우 요약 보고서 나 대시 보드를 작성하는 경우 다음 접근 방식은 해결책 일 수 있습니다.

• 한 셀에서는 Total, Max, Min 등과 같은 기능 이름을 포함하는 드롭 다운 목록을 작성하십시오.
• 드롭 다운 옆 셀에서 드롭 다운 목록의 함수 이름에 해당하는 내장 된 하위 운동 함수가있는 중첩 된 IF 공식을 입력하십시오.

예를 들어, 하위 운동의 값이 셀 C2 : C16에 있고 A17의 드롭 다운 목록에는 총 , 평균 , 최대 및 최소 항목이 포함되어 있으며 "동적"하위 요법은 다음과 같습니다.

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

그리고 이제 사용자가 드롭 다운 목록에서 선택한 기능에 따라 해당 하위 운동 기능은 필터링 행의 값을 계산합니다.

팁. 갑자기 드롭 다운 목록과 공식 셀이 워크 시트에서 사라지면 필터 목록에서 선택하십시오.

Excel subtotal 작동하지 않음 - 일반적인 오류

하위 운동 공식이 오류를 반환하는 경우 다음과 같은 이유 중 하나 때문일 수 있습니다.

#VALUE! - function_num 인수는 1-11 또는 101-111 사이의 정수가 아닙니다. 또는 Ref 인수 중 임의의 경우 3D 참조가 포함되어 있습니다.

#DIV/0! - 지정된 요약 함수가 0으로 분할을 수행 해야하는 경우 발생합니다 (예 : 단일 숫자 값을 포함하지 않는 셀 범위에 대한 평균 또는 표준 편차를 계산).

#NAME? - 하위 운동 함수의 이름이 잘못되었습니다 - 수정이 쉬운 오류 :)

팁. 아직 하위 운동 기능에 익숙하지 않은 경우 내장 된 하위 운동 기능을 사용하고 자동으로 공식을 삽입 할 수 있습니다.

이것이 가시 셀의 데이터를 계산하기 위해 Excel의 소계 공식을 사용하는 방법입니다. 예제를보다 쉽게 ​​따라갈 수 있도록 아래 샘플 통합 문서를 아래에서 다운로드 할 수 있습니다. 읽어 주셔서 감사합니다!

연습 통합 문서

Excel subtotal 공식 예제 (.xlsx 파일)

위 내용은 공식 예제와 함께 Excel 하위 운동 기능의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!