거래표를 필터했는데 합계가 그대로인 이유: SUM과 SUBTOTAL

발행

거래표에서 한 동네만 보이도록 필터했는데 합계가 전체 지역 금액으로 남아 있다면 자료가 지워지지 않아서가 아니라 합계 함수의 목적이 다르기 때문일 수 있다. SUM으로 정한 범위를 더하는 것과 필터 결과에 따라 소계를 구하는 것은 서로 다른 계산이다. 화면에 보이는 행과 계산에 들어간 행을 맞춰 봐야 한다.

세 행으로 함수 차이를 확인한다

가상 금액 100, 200, 300을 B2:B4에 넣으면 전체 합계는 600이다. 이 중 300인 행을 필터로 제외했을 때, =SUM(B2:B4)는 여전히 전체 범위를 더한다. =SUBTOTAL(9,B2:B4) 또는 =SUBTOTAL(109,B2:B4)는 필터에서 제외된 행을 빼므로 이 예에서는 300이 된다. 금액 단위는 검증을 위한 임의 값이며 실제 거래 자료가 아니다.

Microsoft 공식 안내에서 9와 109는 모두 합계지만 수동으로 숨긴 행의 취급이 다르다. 9는 수동 숨김 행을 포함하고, 109는 제외한다. 두 코드 모두 필터로 제외된 행은 빼므로, 필터와 메뉴의 행 숨기기를 같은 행동으로 생각하면 차이를 놓치기 쉽다.

숨긴 열에는 같은 규칙을 기대하지 않는다

SUBTOTAL은 세로 목록에 맞춰 설계된 함수다. 가로 범위의 열을 숨긴 경우까지 세로 행 숨김과 똑같이 합계가 달라질 것으로 기대하지 않는다. 거래일, 지역, 금액을 열로 둔 표에서는 금액 열의 세로 범위를 지정하고 필터 범위에 실제 거래 행 전체가 포함되는지 확인한다.

소계 행이 이미 들어간 표도 살펴야 한다. 범위 안의 다른 SUBTOTAL 결과는 중복 합산을 피하도록 무시되지만, 사람이 직접 적어 넣은 합계 숫자까지 같은 방식으로 알아서 구별되는 것은 아니다. 원시 거래 행과 요약 행을 섞지 않는 구성이 계산을 읽기 쉽다.

보고서에는 전체 합계와 현재 필터 합계를 이름부터 나눠 둔다. 필터 해제, 특정 지역 선택, 수동 숨김을 차례로 적용하면서 기대한 값과 실제 값을 비교한다. 화면에서 행이 사라졌다는 사실만으로 총액도 같은 범위가 되었다고 가정하지 않으면 지역별 거래금액을 잘못 보고하는 일을 줄일 수 있다.

출처와 확인일