엑셀 SUBTOTAL 함수 - 필터링된 데이터만 부분합 계산하기

엑셀 SUBTOTAL 함수 – 필터링된 데이터만 부분합 계산하기

엑셀에서 데이터를 수집하고 관리할 때가 많이 있습니다. 데이터를 SUM, COUNT, AVERAGE, MAX, MIN 등 자주 사용하는 함수를 사용하여 데이터를 집계하여 사용합니다. 그런데 데이터를 필터링하여 필터링된 데이터만 부분합으로 계산해야 할 때가 많이 있습니다. 그럴 때 SUBTOTAL 함수를 사용하시면 됩니다.

그럼 SUBTOTAL 함수를 사용하여 필터링된 데이터만 부부합으로 계산하는 방법에 대해서 알아보죠.

◎ 포스트 기준 – Microsoft 365 앱

1. 함수 구문

SUBTOTAL 함수 구문 (마이크로소프트 문서)

▶ 목록이나 데이터베이스의 부분합을 반환합니다.

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

  • function_num : 필수 요소입니다. 소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111입니다. 1-11에는 수동으로 숨겨진 행이 포함되는 반면 101-111은 해당 행을 제외합니다. 필터링된 셀은 항상 제외됩니다.
  • ref1 : 필수 요소입니다. 부분합을 계산할 첫 번째 명명된 범위 또는 참조입니다.
  • ref2,… : 선택 요소입니다. 부분합을 계산할 명명된 범위 또는 참조로서 2개에서 254개까지 지정할 수 있습니다.


▼ function_num

Function_num
(숨겨진 행 포함)
Function_num
(숨겨진 행을 무시)
함수
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP


▼ 엑셀에서 함수를 입력하면 자동으로 function_num에 해당되는 함수를 보여줍니다.

subtotal 함수를 입력하게 되면 자동을 function_num에 해당되는 함수를 보여줍니다.


☞ 만약에 필터링된 점수 합계를 구할 때 SUM 함수를 사용하게 되면 매번 필터링 후에 SUM 함수의 범위를 지정해야 되겠죠. 그럴 경우에 SUMTOTAL 함수를 사용하시면 됩니다. 그리고 데이터에서 행이 숨겨진 경우가 있는데요 숨기진 행을 포함할 때에는 1~10 숫자를 입력하고 숨겨진 행을 제외할 거면 101~111 숫자를 입력하시면 됩니다. 전 숫자 중에서 101, 109을 가장 많이 사용하는 것 같습니다. 예제로 한번 알아보죠.

2. 사용 예제

▼ 필터링된 데이터에 대해서 점수 합계 및 평균 구하기 (숨긴 행 포함)

필터링 된 데이터에 대해서 부분 합계를 구할 때에는 subtotal 함수를 사용하시면 됩니다.


1. 필터링된 점수 합계 구하기 (숨겨진 행 포함)

[B14] : =SUBTOTAL(9, D2:D11)

→ 9 숫자는 SUM이고 D2:D11는 점수 범위입니다.


2. 필터링된 점수 평균 구하기 (숨겨진 행 포함)

[B15] : =SUBTOTAL(1, D2:D11)

→ 1 숫자는 AVERAGE이고 D2:D11는 점수 범위입니다.


▼ 예시로 성별에서 “남”, “여” 필터링을 하게 되면 자동으로 계산이 됩니다.

필터링을 하게 되면 되면 자동으로 계산이 됩니다.


▼ 데이터에서 5행, 6행 숨겨진 경우에 숨겨진 행을 포함할 건지 제외할 건지에 따라 값은 달라집니다.

숨긴 행을 제외 할 때에는 101~111 숫자를 사용합니다.

관련 이전 게시글


이 글이 도움이 되셨다면 공유를 부탁 드립니다. 다음 글에서 뵙겠습니다!

위로 스크롤