SUBTOTAL 숨김시트 제외 합계 소계 부분합 구하기

설명

SUBTOTAL 함수 명령어는 엑셀에서 시트 목록이나 데이터베이스의 보이는 부분만의 합(부분합, 소계)을 구합니다.

보통 "필터" 기능을 켜고 필터로 보여지는 시트들의 합계를 구하는데 많이 쓰여집니다.

먼저 SUBTOTAL(소계, 부분합) 함수를 이용하여 소계를 구하는 방법부터 살펴보도록 하겠습니다.

위 그림에서 자동 합계는 일반 합계에서는 "SUM"값이 나오는데, "필터"를 적용한 상태에서 "자동 합계"를 누르면 SUBTOTAL(소계, 부분합) 함수가 자동으로 입력이 됩니다.

SUBTOTAL(소계, 부분합) 값이 나온 모습입니다. 수동으로 "=SUBTOTAL(function_num, 범위)를 입력해도 되지만 위와 같이 자동 합계를 누르면 편하게 구하실 수 있습니다.

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

SUBTOTAL 함수 구문에는 다음과 같은 인수(function_num)가 사용됩니다.

function_num은 필수 요소입니다. 소계에 사용할 함수를 지정하는 숫자 1-11 또는 101-111입니다. 1-11에는 "수동"으로 숨겨진 행이 포함(SUM)되는 반면 101-111은 수동으로 숨겨진 행을 제외합니다. 필터링된 셀은 항상 제외됩니다.

위 예제에서 8행과 10행 사이에 수동으로 숨긴 9행이 있습니다.

이렇게 숨겼을 겨우 아래 "소계(1)=SUBTOTAL(109,D3:D15)"과 "소계(2)=SUBTOTAL(9,D3:D15)" 값이 다른 것을 볼 수 있습니다.

function_num값을 "109"로 주면 숨김행이 5,000이 계산이 안된 것을 볼 수 있습니다.

위 예시와 같이 function_num 값에 따라 어떻게 변하는지 살펴보았습니다.

위와 같이 예제(3)에서 과일색 옆 깔때기 모양을 누르고 아래 빨간색을 체크하고 확인을 누르면 아래와 같이 과일 색이 빨간색만 필터로 표시가 됩니다.

위의 예제에서 아래 빨간 테두리를 보면

"합계 =SUM(D3:D15)" 값은 43,500원으로,

"소계(1)=SUBTOTAL(109,D3:D15)" 값은 14,000원입니다.

과일 색이 빨간색 행의 합계 값만 나온 겁니다.

(필터를 적용하면 function_num값에 상관없이(모두 숨겨지므로) 숨겨진 행의 값은 제외하고 합을 구하게 됩니다.

지금까지 엑셀에서 필터를 적용하고 SUBTOTAL(소계, 부분합)를 구하는 법을 알아보았습니다. SUBTOTAL(소계, 부분합)에 대해 몇 가지 사용법이 더 있는데 많이 사용하지 않아서 생략하고 꼭 필요한 부분만 다뤄봤습니다.

오늘은 여기까지 하겠습니다. 다음엔 더욱 알찬 블로그로 찾아뵙겠습니다.

도움이 되셨다면 추천 꾹 부탁드립니다. ^^

Excel VBA 기초, 엑셀 메크로, 엑셀 프로그래밍 기초 (0)	2021.07.21