엑셀 71강] 활용편 - Subtotal, Vlookup 필터를 활용한 부분합 구하기

 

앞서 Subtotal 함수와 필터 기능을 활용하여 실시간 부분합을 구하는 방법에 대해서 소개했습니다.

 

실습 데이터 다운로드 ↓

Subtotal Vlookup 필터.xlsx

다운로드

 

이번에는 난이도를 조금 높여 자주 사용하는 합계, 평균 등을 목록에서 선택하면 자동으로 부분합이 구해지도록 Subtotal 함수에 Vlookup 함수를 중첩하여 만들어 보겠습니다.

 

 

 

| 함수를 선택하는 유효성 검사 목록 만들기

 

셀을 선택하고 [데이터]-[데이터 유효성 검사] 메뉴를 실행합니다.

 

 

제한 대상 '목록'을 선택하고 원본 입력창에 "합계, 평균, 최대값, 최소값" 이라고 입력하고 확인 버튼을 누릅니다.

Subtotal 함수는 위 4가지 함수 뿐만 아니라 총 11개 함수를 사용할 수 있습니다.

 

 

함수명을 선택할 수 있는 목록이 생겼습니다.

 

 

| Subtotal + Vlookup 함수

 

함수식을 작성하기 전에 Vlookup 함수에서 사용할 테이블을 만들어야 합니다.

Vlookup 함수는 값을 찾아올 테이블이 꼭 있어야 합니다.

아래 그림과 같이 작성합니다.

Subtotal 함수에서 사용할 수 있는 함수는 총 11개이고 각 번호가 정해져 있습니다.

합계는 9, 평균은 1, 최대값 4등... 아래 그림을 참고하세요.

 

 

 

자! 그럼 선택된 함수에 맞는 수량의 합을 구하는 수식을 작성해 보겠습니다.

=Subtotal()이라고 입력하고 함수 삽입 버튼을 눌러 함수 마법사를 엽니다.

 

 

 

첫 번째 인수 Function_num 인수에 Vlookup 함수를 중첩합니다.

그런 후 수식 입력줄에서 중첩한 Vlookup 함수를 마우스로 선택합니다.

 

 

Vlookup 함수의 Lookup_value 인수에 유효성 검사 목록이 적용된 셀을 선택합니다.

이때 참조된 셀은 절대 주소를 바꾸어 줍니다.

수량에 수식을 작성한 후 나머지 금액, 입금액, 미수금액 범위에 수식을 복사하면 참조 셀이 바뀌게 됩니다. 바뀌지 않도록 절대 참조합니다.

 

Table_array 인수에는 작성해둔 함수 번호를 입력해둔 범위를 지정합니다.

이때 참조된 범위도 절대 참조합니다.

 

Col_index_num 인수에는 2를 입력합니다.

선택된 함수명에 맞는 함수 번호는 테이블의 2열에 있습니다.

 

마지막으로 Range_lookup 인수에는 0을 입력합니다.

찾으려고 하는 값이 문자라면 정확하게 일치하는 값 0이 됩니다.

 

 

Vlookup 함수식 작성이 끝났으면 다시 수식 입력줄에서 Subtotal 함수를 선택합니다.

함수 마법사는 Subtotal 함수로 바뀝니다.

 

 

Subtotal 함수의 첫 번째 인수는 목록에서 선택된 함수에 맞는 함수 번호를 찾아오는 수식에 해당됩니다.

두 번째 인수는 부분합을 구할 범위 즉, 수량 범위가 됩니다.

 

 

수량에 작성된 수식을 금액, 입금액, 미수금액 범위까지 복사합니다.

 

 

| 필터 적용

 

필터를 적용해서 조건에 일치하는 데이터의 부분합을 구할 수 있습니다.

필드명 즉, 제목 셀 범위를 블록 지정하고 [데이터]-[필터] 메뉴를 실행합니다.

데이터 내부에 아무 셀이나 선택하고 메뉴를 실행해도 됩니다.

 

 

 

Vlookup 함수에서 사용한 테이블 범위는 숨기기 합니다.

열을 블록 지정하고 마우스 오른쪽 버튼을 눌러 [숨기기] 메뉴를 실행합니다.

 

 

 

| 실시간 부분합 구하기

 

지점명 필터 버튼을 눌러 '해운대점'을 필터 합니다.

 

 

해운대점에 해당하는 합계 금액이 자동으로 구해집니다.

 

 

이번에는 함수를 변경해보겠습니다.

평균을 선택하면 해운대점의 평균이, 최대값을 선택하면 해운대점의 최대값이 구해집니다.

이렇게 데이터를 만들어 관리하면 편리하겠죠?

관심이 있으시면 한 번 해보세요~

 

 

 

 

 

이번 강좌는 여기까지 입니다.

필요하신 분께 도움이 되기를 바랍니다.

 

동영상 강좌도 준비했습니다.

 

https://youtu.be/Bq_lUFmmdx0

 

 

짤막한 강좌 한정희 강사였습니다.