엑셀 배우기, 필터로 지정한 데이터만 처리하는 만능 SUBTOTAL 함수!

엑셀 화면에 보이는 데이터만 처리하는 SUBTOTAL 함수가 있습니다. 필요에 의해서 삭제 대신에 숨기기한 열이나 행의 데이터는 계산에 포함하지 않는다는 의미입니다. 그래서 엑셀의 필터 기능과 함께 아주 유용하게 사용할 수 있는 함수이기도 합니다. 아래의 간단한 재고관리대장이자 매출관리대장을 확인해 주시기 바랍니다. B2:F25까지를 일단 표서식으로 만들었습니다.

표를 원래의 서식대로 자동으로 확장해줄 뿐만 아니라, 표 내부의 함수 또한 자동으로 복사해주기 때문에 엑셀에서 가장 유용한 기능중의 하나가 바로 표서식이라 할 수 있습니다. 게다가 자동으로 필터를 설정해서, 데이터 메뉴의 필터 기능을 따로 추가해 줄 필요도 없습니다. 표서식에 대한 내용은 아래의 포스팅에서 자세하게 설명하고 있습니다.  

엑셀배우기, 표 자동 확장 및 동적 범위 참조에 유용한 표서식을 알아보자.

H2:J7 범위의 데이터는 상품의 판매 단가 정보입니다. 매출이 발생할 때마다 일일이 가격을 입력하는 대신 아래에서처럼 VLOOKUP 함수로 가격을 자동으로 참조하기 위해서입니다. 참고로 아래 E열에서의 VLOOKUP 함수의 구조, '=IF([@출고]="","",VLOOKUP([@상품명],$H$2:$J$7,2,FALSE))'에서 <IF([@출고]="",""> 부분은 출고 열에 비어있으면 E열도 비워 두라는 의미입니다.

즉, 매출 데이터가 발생하지 않으면, 상품의 판매 단가를 불러올 필요가 없다는 것으로, 각 상품이 입고된 행의 판매단가 열에는 가격 정보가 비어있는 있는 이유이기도 합니다. 마찬가지로 F열의 판매 금액 정보도 출고 열이 비어있는 경우에는 판매금액(출고 * 판매단가)을 계산하지 않습니다.

참고로 아래와 같이 표서식에서 함수를 구성할 경우, 함수의 인수가 표서식내의 셀인 경우에는 그셀만이 아닌 그 셀을 포함하는 열의 이름으로 표시합니다. 예를들어 F11셀의 함수가 표서식이 아닌 일반적으로 구성된 함수였다면, '=IF(D11="","",VLOOKUP(B11,$H$2:$J$7,2,FALSE))'의 구조가되었을 것입니다. D11셀 대신에 '출고'라는 열의 이름을 인수로 사용하고, B11셀 대신에 '상품명'이라는 열의 이름을 사용하고 있는 것입니다.

또한 J열에서 각 상품별로 실시간 잔고 정보를 확인할 수 있습니다. 단일 조건의 합계를 구하는 SUMIF 함수를 활용한 아주 간단한 수식입니다. 각 상품별로 입고량 전체에서 출고량 전체를 빼주었습니다. 참고로 SUMIF 함수의 구조는 '=SUMIF(조건의 범위, 조건, 합계할 범위)'입니다.

이제 SUBTOTAL함수를 사용해서 3행의 수식을 채워 보도록하겠습니다. SUBTOTAL 함수는앞서 간단히 말씀드린대로 엑셀 화면에 보이는 데이터만 처리하는 함수인데, 또다른 중요한 특징으로는 SUBTOTAL 함수 자체적인 함수 기능이 있는 것이 아닌, 다른 여러 함수의 기능을 그대로 사용가능하다는 점입니다.

SUBTOTAL 함수의 구조는 '=(함수 번호, 범위)'로 아주 간단한데, 이 함수 번호 인수에, SUM 함수를 의미하는 9를 입력하면 SUM 함수를 대신하여 사용할 수 있고, AVERAGE 함수를 의미하는 1을 입력하면, AVERAGE 함수 대신으로 사용할 수 있는 것입니다. 이 함수 번호는 SUBTOTAL 함수 구성할 때, 자동으로 보이니, 따로 외워두거나 할 필요는 없습니다.

그래서아래의 B3셀의 함수 구성에는 입고량 전체를 구하기 때문에 SUM 함수의 인수 9번을 사용하고 있습니다. 마찬가지로 SUBTOTAL 함수를 사용하여 C3의 셀은 출고량 전체의 합을 구하고, E3 셀은 판매 총액을 구하고 있습니다.

그리고 표의 상품명 필터를 눌러서 AAA 상품에만 체크를 해주면 아래와 같이, AAA 상품의 데이터만을 보여주게 됩니다. SUBTOTAL로 구성된 3행의 함수 값들이 AAA 상품의 입고량만을 계산하고 출고량과 판매 총액을 계산하고 있습니다. 이 외의 행들의 데이터는 필터를 통해 엑셀 화면에서 숨겨져 있기 때문입니다. SUBTOTAL 함수 대신에, 단순히 SUM 함수를 사용했다면, 숨겨진 행들의 데이터도 모두 계산한 결과를 보여주었을 것입니다. 

 

SUBTOTAL 함수는 함수를 구성하는 방법이 까다롭지 않지만 유용성은 다른 어떠한 함수에 비교해도 뒤떨어지지 않습니다. 필터 기능, 혹은 표서식과 함께 사용하면 최고의 효용을 보여주는 함수이니 꼼꼼하게 이해해 주시기 바랍니다. 혹시 생략한 설명이 있거나 이해가 어려운 부분이 있다면 아래에 댓글 남겨 주시기 바랍니다!