SUBTOTAL 함수를 활용한 엑셀 필터링 데이터의 합 구하기

 

 

질문

SUM함수를 주로 사용하는데 필터를 적용하게 되면 합은 변경되지 않아요. 필터를 적용해서 출력된 값들만 더하고 싶은데 방법이 없을까요?

 

답

SUBTOTAL 함수를 사용해서 필터링 된 데이터의 합을 구하면된다.

 

 

SUBTOTAL 함수는 전체 데이터가 동적으로 변경 될 수 있는 경우, 즉 필터링, 자동 필터, 그룹화 또는 하이라이트를 사용하여 열린 데이터베이스 파일에 대한 합계, 평균, 최대, 최소 등의 기능을 수행하는데 사용합니다. SUBTOTAL 함수를 사용하여 필터링된 데이터를 기반으로 계산하므로 전체 범위 데이터에 비해 더 유연하게 작동합니다. SUBTOTAL 함수는 계산할 범위에 대한 함수 번호를 입력하여 작동합니다. 즉, 원하는 계산에 대해 함수를 선택합니다.

예를 들어, 9는 누적 합계를 나타내므로 필터링된 범위 데이터에 대한 누적 합계를 계산 할 수 있습니다. 함수 번호는 1-11 범위 내의 일부 숫자로 제한되며, 세부사항은 아래와 같습니다.

 

1 : COUNT : 지정한 범위 내에서 숫자 셀의 수 계산

2 : SUM : 범위 내 셀의 합계를 계산

3 : AVERAGE : 지정한 범위 내에서 평균값 계산

4 : MAX : 지정한 범위 내에서 최대값 계산

5 : MIN : 지정한 범위 내에서 최소값 계산

6 : PRODUCT : 범위 내 셀의 곱 계산

7 : COUNTA : 지정한 범위 내에서 비어 있지 않은 셀 수 계산

8 : STDDEV : 범위 내 값의 표준 편차 계산

9 : SUM 누계

10 : VAR : 범위 내 값의 분산 계산

11 : VARA : 범위 내 값의 편차 계산 함수를 설정한 후 CONCATENATE 함수와 같은 다른 함수와 조합하여 사용할 수 있습니다.

 

 

 

이 함수를 다양한 합계 및 요약 레벨에서 사용하려면 항상 첫 번째 인수의 요소 번호를 사용하여 구조를 유지하는 것이 좋습니다. 이렇게 하면 필터 될 때마다 정확한 결과를 만들어냅니다. 결론적으로, SUBTOTAL 함수는 필터링된 데이터에서 합계, 평균, 최대, 최소, 분산, 표준 편차, 곱 등을 쉽고 빠르게 계산할 수 있는 기능입니다. 함수 번호를 결정하고 범위를 제시함으로써 유연하게 작동할 수 있습니다. 초기 계산이 변경될 때마다 동적으로 재배열되는 데이터 집합에서 호출하기에 가장 적합하고 빈번한 조작을 수행합니다.

 

SUBTOTAL 함수는 엑셀에서 원하는 영역에서 계산하는 함수 중 하나입니다. SUBTOTAL 함수의 장점은 이 함수를 사용하여 새로 생성된 결과가 새로운 값을 만든 것처럼 표시되어 필터링과 함께 사용될 수 있다는 것입니다.

SUBTOTAL 함수의 요소번호(argument 특성값)를 사용하여 함수를 설정할 수 있으며, 이중에서 9는 누적 합계를 나타냅니다. 따라서 필터링된 영역의 합계를 구하는 것이 가능합니다. 이렇게 하면 스크린이 무엇을 필터링하고 있는지에 상관없이 합계가 나타날 것입니다.

 

 

예제

=SUBTOTAL(9,B2:B100)

9는 위에서 설명한 1부터 11까지의 함부 번호입니다. 합을 나타냅니다. 아래는 실제 운영하는 스마트스토어, 쿠팡 등 주문내역을 정리하여 정산예정금액과 실제 마진을 사이트별로 적용하여 사용하고 있습니다.

판매처 A1열에 필터를 적용하면 판매처별로 정산예정 금액의 합을 확인할 수 있습니다.

 

SUBTOTAL 함수는 대상 데이터를 전체 데이터와 각 분할에 대한 구조적 구조를 구분해야 합니다. FILTER 함수가 필터링된 대상 데이터와 구조를 동일하게 유지하지 못하기 때문에 이로 인해 문제가 생길 수 있습니다. 이 경우, SUBTOTAL 함수가 FILTER 함수보다 더 적합하다고 할 수 있습니다. 예를 들어, "A" 컬럼의 데이터를 필터링한 후 "B" 컬럼의 값들의 합을 구하고자 한다면, 위의 예제와 같은 수식을 사용할 수 있습니다. 또한, 필터링된 상태에서 해당 범위의 평균이나 최소값, 최대값 등을 구하기도 매우 쉽습니다.

 

 

물론 SUBTOTAL 함수는 필터링된 데이터만을 계산하므로, 전체 범위 내에서 계산했을 때와 다르게 값이 나올 수도 있습니다. 하지만 필터 기능을 활용한다면 특정 조건을 만족하는 데이터만을 쉽게 선별하고, 그 데이터에 대한 다양한 연산 결과를 빠르게 확인할 수 있다는 장점이 있습니다. 결론적으로, SUBTOTAL 함수는 필터링된 데이터의 연산 결과를 쉽고 빠르게 확인할 수 있는 강력한 기능입니다. 상황에 따라서 올바른 요소번호를 설정하여 SUBTOTAL 함수를 사용하여 필요한 계산값을 쉽게 얻을 수 있습니다.