엑셀 SUBTOTAL 함수 사용법: 부분합과 필터 제외 합계

=SUBTOTAL(9,C2:C8)는 SUM처럼 C2:C8의 숫자를 더하지만 두 가지가 다릅니다. 범위 안의 다른 SUBTOTAL 수식을 건너뛰고, 필터로 숨긴 행을 건너뜁니다. 첫 번째 인수 9는 어떤 계산을 할지 정합니다.

수식초기화

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

C8의 총합계는 부분합 행을 포함한 열 전체에 걸쳐 있지만 여전히 445를 보여 줍니다. C4와 C7에는 SUBTOTAL 수식이 있으므로 SUBTOTAL이 그 둘을 뺍니다. F2는 같은 일을 SUM으로 해서 모든 매출을 두 번 센 890을 보여 줍니다. 모든 합계 행에 SUBTOTAL을 쓰면 총합계를 다시 쓰지 않고도 그룹을 추가하거나 옮길 수 있습니다.

SUBTOTAL 함수 번호

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

=SUBTOTAL(을 입력하면 엑셀이 이 목록을 보여 주므로 외울 필요는 없습니다. 가장 많이 쓰는 것은 9와 109(SUM), 1(AVERAGE), 103(보이는 행 세기)입니다.

수식초기화

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

여기서는 숨긴 것이 없으므로 각 줄이 일반 함수와 같습니다. 평균 88.33, 행 6개, MAX 200, MIN 30, SUM 530입니다. 차이는 행이 숨겨질 때만 나타나며, 다음 섹션이 그 내용입니다.

SUBTOTAL 9와 109, 그리고 필터된 행

데이터 > 필터(Ctrl+Shift+L, Mac에서는 Cmd+Shift+F)로 필터를 켜고 Region 드롭다운에서 North를 고르세요. 다른 지역의 행이 숨겨집니다:

• =SUM(C2:C7)는 여전히 여섯 행을 모두 더합니다.
• =SUBTOTAL(9,C2:C7)와 =SUBTOTAL(109,C2:C7)는 보이는 North 행만 더합니다.
• =SUBTOTAL(103,A2:A7)는 화면에 남은 행을 세어 2를 냅니다. 상태 표시줄의 "2 of 6 records found"(6개 중 2개 레코드 찾음)와 같은 숫자입니다.

두 계열은 직접 숨긴 행(행 선택 후 마우스 오른쪽 버튼 > 숨기기)에서만 다릅니다. 9는 그 행을 여전히 더하고 109는 더하지 않습니다. 합계가 항상 화면과 같아야 하면 109를 쓰세요. 보기만 정리하려고 행을 숨기고 합계에는 여전히 넣고 싶다면 9를 쓰세요.

SUBTOTAL은 행에만 적용됩니다. 숨긴 열은 항상 포함되므로, 행을 가로지르는 =SUBTOTAL(109,B2:G2)는 숨긴 열도 더합니다.

SUBTOTAL을 가장 빨리 넣는 방법은 필터를 켠 상태에서 자동 합계 버튼을 누르는 것입니다. 엑셀이 SUM 대신 =SUBTOTAL(9,...)을 써 줍니다. 데이터 > 부분합은 한 걸음 더 나아갑니다. 어떤 열로 정렬된 목록에서 그룹마다 아래에 합계 행을, 맨 아래에 총합계를 모두 SUBTOTAL로 넣고, 그룹을 접을 수 있는 윤곽 단추도 추가합니다.

AGGREGATE: 오류를 건너뛸 수 있는 SUBTOTAL

범위의 셀 하나에 오류가 있으면 SUM과 SUBTOTAL은 그 오류를 반환합니다. AGGREGATE(Excel 2010 이후)는 옵션 인수가 하나 더 있는 SUBTOTAL이며, 옵션 6은 오류 값을 무시합니다.

수식초기화

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

C3에 #N/A가 있으므로 F2도 #N/A를 보여 줍니다. F3은 그것을 무시하고 나머지 다섯 개를 더해 450을 냅니다. 첫 번째 인수는 SUBTOTAL과 같은 번호를 씁니다(9는 SUM, 4는 MAX). 다른 옵션도 있습니다. 5는 숨긴 행을, 7은 숨긴 행과 오류를, 3은 숨긴 행, 오류, 중첩된 SUBTOTAL과 AGGREGATE 수식을 무시합니다. C3을 숫자로 바꾸면 F2는 F3과 같은 합계를 보여 줍니다.

연습: 부분합 위의 총합계

수식초기화

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

직접 해 보세요: 목록에는 지역마다 아래에 부분합이 있습니다. 부분합 행을 두 번 세지 않으면서 C2:C8을 덮는 총합계를 C9에 넣으세요.

확인정답 보기

SUBTOTAL 합계가 여전히 틀리는 이유

• 그룹 합계에 SUM을 썼습니다. SUBTOTAL은 범위 안의 다른 SUBTOTAL 수식은 건너뛰지만 SUM 수식은 건너뛰지 않습니다. =SUM(C2:C3)로 쓴 그룹 합계는 다시 세어집니다. 모든 합계 행을 SUBTOTAL로 바꾸세요.
• 행을 직접 숨겼는데 함수 번호가 9입니다. 109를 쓰세요.
• 데이터가 행이 아니라 열 방향입니다. 숨긴 열은 절대 건너뛰지 않습니다.
• 필터가 아니라 조건이 필요합니다. SUBTOTAL은 필터가 숨긴 것을 따릅니다. 필터 없이 North 합계를 구하려면 SUMIF를 쓰세요. 모든 그룹의 요약을 한 번에 보려면 피벗 테이블이 데이터에 합계 행 없이 해 줍니다.