엑셀 | SUBTOTAL 함수 , 화면에 보이는 셀 만으로 값 구하기

SUBTOTAL 함수

SUBTOTAL 함수는 숨겨진 셀은 제외하고

화면에 보이는 셀만으로 평균, 개수, 합계 등 계산식을 구하는 함수입니다.

 

SUBTOTAL 함수 인수입니다.

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

 

function_num 은 필수 요소입니다.

소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111을 입력합니다.

1-11에는 수동으로 숨겨진 행이 포함되며

101-111은 숨겨진 행은 제외합니다.

반면 필터링된 셀은 1-11 또는 101-111을 지정해도

숨겨진 행은 항상 제외됩니다.

 

ref1 필수 요소입니다.

계산할 첫 번째  범위 또는 참조입니다.

먼저 위 내용으로 C4, F4:F6까지의 값을 계산해보겠습니다.

SUBTOTAL과 비교하기 위하여

C4 셀은 COUNTA, F4:F6셀은 SUM함수로 계산하겠습니다.

C4 셀 제품코드로 구한 제품개수  =COUNTA(B9:B20)

F4 셀 입고량 합 =SUM(D9:D20)

F5 셀 출고량 합 =SUM(E9:E20)

F6 셀 잔여량 합 =SUM(F9:F20)

이번에는 SUBTOTAL함수로 값을 구해보겠습니다.

C4셀 : =SUBTOTAL(  을 입력하면 함수 목록이 표시되며

103-COUNTA 를 더블 클릭합니다.

function_num 에 103을 입력한 겁니다.

계속해서 103뒤에 ,(쉼표)를 입력합니다.

ref1에는 B9:B20 범위를 선택하고 ENTER를 누릅니다.

제품개수가 구해졌습니다.

=SUBTOTAL(103,B9:B20)

 

이번에는 입고량~잔여량까지 구해보겠습니다.

F4셀 : =SUBTOTAL( 을 입력하면 함수 목록이 표시되면

109-SUM 을 더블 클릭합니다.

function_num 에 109를 입력하였습니다.

계속해서 109 뒤에 ,(쉼표) 를 입력후

D9:D20 입고량 범위를 지정합니다.

입고량 =SUBTOTAL(109,D9:D20)

 

같은 방법으로 출고량, 잔여량을 구합니다.

 

출고량 =SUBTOTAL(109,E9:E20)

잔여량 =SUBTOTAL(109,F9:F20)

 

이제 COUNTA, SUM 함수와

SUBTOTAL 함수의 차이를 확인해봐야 겠죠

SUBTOTAL 함수로 구한 값의

9행~14행까지 행숨기기를 했습니다.

C4, F4:F6 셀에 표시된 값은

숨긴 행을 제외한 값만 계산되었습니다.

 

이와 같이 SUBTOTAL 함수는

화면에 표시된 값만 계산이 가능합니다.

 

단, 위에서 언급했듯이

function_num 에 지정하는 숫자

1-11에는 수동으로 숨겨진 행이 포함되어 계산되며

101-111은 숨겨진 행은 제외합니다.

반면 위와 같이 필터링된 데이터에서는

function_num 에 1-11 또는 101-111을 지정해도

숨겨진 행은 항상 제외하고 계산됩니다.

---

[ 함수번호 ]

함수	Function_num (숨겨진 값 포함)	Function_num  (숨겨진 값 무시)
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV	7	107
STDEVP	8	108
SUM	9	109
VAR	10	110
VARP	11	111

 

모든 글과 사진의 무단복제를 금합니다.

ⓒ 2021. anne All rights reserved.