08. 엑셀[Excel] "SUBTOTAL + 자동 필터로 소계" 내기.

08. 엑셀[Excel] "SUBTOTAL + 자동 필터로 소계" 내기.

 

 

복잡하고 많은 양의 데이터의 소계나 집계를 해야 하는 경우가 생길 때 머리가 많이 아프시죠?

 

이번 포스팅의 주제는 SUBTOTAL 함수를 이용하여 자동 필터로 원하는 내용만을 걸러낸 자료들을 집계하거나, 소계를 내어 검토할 수 있는 방법입니다.

 

아래 첨부 사진에서 보이듯 예시 자료를 먼저 만들어 보겠습니다.

1행과 2행은 전체 합계와 선택 항목의 소계(집계)를, 3행부터는 본격 자료를 예시자료로 만들 예정입니다.

만약 원본의 데이터가 있다면 윗 줄에 2개의 행을 추가 삽입하여 SUM과 SUBTOTAL의 수식 등을 넣어 검토할 수 있으니, 참고 부탁드립니다.

 

시작은 A3의 셀부터 제목들을 쭉 입력해두고 날짜가 들어가는 셀에는 간단히 4-1을 입력하고 엔터를 쳐서 04월 01일을 입력한 후 쭉 드래그하여 방대한 양을 만들었습니다. 일반 업무내용과 비슷하게 보이기 위해 단가, 수량, 공급가액, 부가가치세, 합계 등을 입력하였으며, 수식도 걸어두었습니다.

공급가액 계산 수식은 "= 단가 입력 셀 * 수량 입력 셀", 부가가치세 계산 수식은 "= 공급가액 입력 셀 * 10%", 합계 계산 수식은 "= 공급가액 입력 셀 + 부가가치세 입력 셀"로 E4, F4, G4에 입력된 수식을 작은 점을 잡고 쭉 드래그하는 방식으로 복사하여 모든 수식을 완성하였습니다. 

 

이렇게 모든 자료가 완성이 되면 간추려 보기 위해 제목 줄에 필터를 이용하겠습니다. 필터는 제목 줄을 입력한 3행 전체를 선택하고, 메뉴바에 있는 데이터 탭 ▶ 필터를 눌러주시면 됩니다.

 

자, 그럼 본격적으로 SUBTOTAL 함수를 활용하여 소계 내는 방법을 알려드리겠습니다.

 

먼저 1행에는 전체의 합계 금액을, 2행에는 필터 된 항목의 소계를 나타내어 비교하여 보기 위해, 1행에는 SUM이라는 함수를 이용하여 전체 합계 금액을 내는 수식을 만들겠습니다. E1 셀에 F2(편집 단축키) → = → SUM(직접 입력 또는 함수 클릭)을 입력한 후 범위를 지정하고 확인을 누릅니다. 옆 셀 F1, G1의 수식도 동일하기에 E1의 셀을 선택하고 작은 점을 잡고 드래그하여 수식을 복사하여 줍니다. 그럼 전체 내용의 합계 값이 나타나게 됩니다.

 

다음 제일 중요한 SUBTOTAL 함수를 사용하겠습니다. 선택 항목의 소계를 보기 위한 함수로, 2행에 집계되도록 E2 셀에 수식을 넣어보겠습니다. E2 셀에 F2(편집 단축키) → = → SUB까지만 입력하다 보면 오른쪽에 자동 완성되어 보이는 목록 중 SUBTOTAL을 더블클릭하여 선택할 수 있습니다. 물론 직접 입력하거나 함수 추가를 이용하여 사용할 수 있습니다.

 

함수가 선택되었으면, 범위를 지정해야 하는데요. 수식은 =SUBTOTAL(Function_num, Ref1) 이 되도록 입력합니다.

Function 값은 SUBTOTAL 함수를 더블클릭하여 선택하자마자 옆에 자동완성처럼 보기창으로 활성화되는데요. 우리는 소계 즉, 합계를 구하기 위한 수식이기 때문에 9 - SUM을 선택하거나, 109 - SUM을 더블클릭하여 선택하여 줍니다.

여기서 같은 소계인데 왜 9 - SUM과 109 - SUM으로 나타날까 하는 궁금증이 생기시지요?

바로 9 - SUM 은 행 숨김 등으로 숨겨져 있는 값을 모두 포함하여 계산된 합계 값이고, 109 - SUM은 숨겨져 있는 값을 제외하고 보이는 값들의 합계를 내준다는 차이가 있습니다.

Function 값을 109 - SUM으로 더블클릭하거나 109를 직접 입력한 후 콤마 ", "를 찍고 전체 합계의 범위처럼 범위를 드래그하여 지정한 후 괄호 ")"를 닫아주시고 엔터를 칩니다. 마찬가지로 E2 셀의 수식을 복사하기 위해 작은 점을 드래그하여 수식을 복사하면 끝납니다.

 

그럼 확인을 해보셔야겠죠?

품목의 자동 필터를 누르고 (모두 선택) 된 부분을 전체 체크 해제하신 후 원하는 내용만 쏙쏙 선택한 상태에서 확인을 눌러봅니다. 선택한 필터 값의 합계만 집계된 금액이 확인되시나요?

Function의 값을 숨긴 내용을 제외하여 보이는 내용만 계산되도록 109 - SUM을 선택하였으며, 활용도에 따라 숨긴 내용을 모두 포함하여 계산하고 싶으실 경우에는 9 - SUM을 선택하여 계산하시면 편리하답니다.

 

자동 필터를 이용하여 데이터를 모두 선택 해제한 후 원하는 항목만을 모아서 체크한 후 소계를 내어 볼 수 있으며, 일부분의 단어를 입력하고 검색을 통하여 선택하거나, 선택된 내용에 항목을 추가하여 집계 낼 수도 있으니, 적극 활용하셔서 업무에 많은 도움이 되시길 바랍니다.