자동필터로 데이터를 추출하고SUBTOTAL 요약하기

엑셀 분석 작업

많은양의 데이터에서 원하는 데이터를 추출할 때 가장 빠르게 사용할 수 있는 기능은 자동필터가 있습니다

자동필터로 원하는 데이터를 추출한 후 화면에 보이는 데이터의 합계나 평균, 개수 등으로 요약을 하고 싶을 때가 있을것입니다 예를 들어서 경기지점의 재고 현황을 추출한 후에 경기 지점의 재고금액의 합계를 알고 싶습니다

이럴때 사용하는 함수가 SUBTOTAL입니다

SUBTOTAL 함수는 자동필터가 추출한 데이터의 집계를 알려주는 함수라고 보면 됩니다

SUBTOTAL 함수의 인수를 살펴보면   

SUBTOTAL(function_num, ref1)

function_num : 사용할 함수를 지정할 숫자                    

ref1 : 함수 계산을 할 범위

자동필터와 SUBTOTAL 함수를 이용한 예제를 보면서 알아보겠습니다

지점별 의류 재고 관리’ 표가 있습니다 

개수[H3], 재고수량합계[I3], 재고금액합계[J3] 셀에 SUBTOTAL 함수를 이용해서 자동필터 결과를 나타낼 것입니다. 

    

① 개수[H3]를 선택하고 =SUBTOTAL(3,$E$6:$E$27) 라고 입력합니다

상품명[E6:E27] 필드의 개수를 셀 것입니다. (상품명 필드가 문자이므로 3번 함수 COUNTA를 선택하고 콤마(,)하고 두 번째 인수로 상품명 필드 E6:E27 범위를 드래그하고 F4키 눌러서 절대주소로 바꾼 후 괄호 닫고 엔터 합니다)

② 같은 방법으로 재고수량합계[I3]를 선택하고 =SUBTOTAL(9,$I$6:$I$27) 라고 입력합니다. 

재고수량 필드의 합계를 계산합니다. (합계는 함수번호 9입니다)

③ 이어서 재고금액합계[J3]를 선택하고 =SUBTOTAL(9,$J$6:$J$27) 라고 입력합니다

   재고금액 필드의 합계를 계산합니다. (합계는 함수번호 9입니다)

④ 이제 필터를 사용하겠습니다. 표 안에 아무 곳이나 클릭합니다

⑤ [데이터]-[필터]를 클릭합니다

⑥ 필드명 오른쪽에 있는 필터 기호(▼)를 이용해서 원하는 데이터를 추출합니다     

예를 들어 ‘남성복’의 개수, 재고수량합계, 재고금액합계를 해보겠습니다

구분 필드 오른쪽에 있는 필터 기호(▼)를 클릭해서 남성복을 선택하면 남성복 데이터만 추출되고 SUBTOTAL 함수 결과가 H3, I3, J3 셀에 나옵니다

남성복 데이터만 추출되고 개수 9, 재고수량합계 290, 재고금액합계 19,137,800으로 결과가 나왔습니다. 

이외에도 다양하게 필터를 적용해서 SUBTOTAL 함수 결과를 확인해보세요     

자동필터는 SUBTOTAL과 잘 어울립니다

이제부턴 자동필터하고 나서 SUBTOTAL 함수를 사용하세요