[엑셀] subtotal 함수로 필터링 된 데이터만 계산하는 방법

엑셀에서 작업하는 표들은 대부분은 위와 같은 형태를 취하고 있을 것이라고 생각합니다. 어떠한 순서에 따라 데이터를 추가해서 작성을 하는 형태인데  엑셀에서는 각종 기능들이 세로 방향으로 데이터를 추가하는 식으로 작성했을 때 정상적으로 작동하기 때문에 이러한 형태로 작성되는 경우가 많습니다.

 

이렇게 데이터를 추가해서 작성한 다음 원하는 데이터만 필터링해서 볼 때는 필터 기능을 자주 활용하는데 이에 대해서는 아래의 포스팅을 참고해 주시면 되겠습니다.

[엑셀] 필터 설정 및 활용 방법

그런데 이렇게 필터 기능을 사용하면 원하는 기준에 해당되지 않는 데이터는 숨김 처리되어 화면에 보이지 않습니다. 이 상태에서 각종 함수를 사용하면 사용자로 하여금 혼란을 불러일으킬 때가 있습니다.

 

F14셀을 보면 F2셀부터 F12셀까지의 합계 값을 나타내도록 수식이 입력돼 있습니다. 이 상태에서 필터 기능을 활용하여 표에서 주문지점이 '서울지점'인 데이터만 필터링해보겠습니다.

 

주문지점이 '서울지점'인 데이터만 필터링했습니다. 필터 기능을 활용하면 기준에 해당되지 않는 항목들은 그저 '숨김' 처리만 되는 것이라서 화면에만 보이지 않는 것이라 F14셀의 합계값은 변하지 않습니다. 화면에 보이는 셀들의 합계값만 계산되는 것이라면 '4,560,000'이 계산되어야 하지만 F14셀의 값은 그대로 '8,372,500'입니다. 화면에 보이는 서울지점의 데이터뿐만 아니라 숨겨져 있는 다른 데이터들도 다 계산되었기 때문입니다.

 

이렇게 필터 기능을 사용했을 때 화면에 나타나는 데이터들의 값만 구하기 위해서 매번 새롭게 수식을 입력해야 한다면 매우 번거로울 겁니다. 이럴 때 사용하는 함수가 subtotal 함수인데 위의 상황에서 자동으로 필터링된 데이터들의 합계만 계산되도록 수식을 다시 입력해 보겠습니다.

 

F14셀에 '=subtotal(' 까지 입력하면 첫 번째 인수의 목록이 나타납니다. 각 번호마다 기능이 전부 다른데 이걸 외울 필요는 없습니다. 창을 보면 숫자의 번호 옆에 함수명이 나타나는데 평균을 구하려면 평균을 구하는 함수인 AVERAGE가 표시되어 있는 '1'을, 최대값을 구하려면 최대값을 구하는 함수인 MAX가 표시되어 있는'4'를, 합계를 구하려면 합계를 구하는 함수인 SUM이 표시되어 있는 '9'를 선택하면 됩니다.

 

1부터 11까지 원하는 인수를 선택하면 됩니다. 창 하단을 보면 101부터 111까지의 인수도 선택할 수 있는데 이에 대해서는 나중에 다시 설명드리겠습니다. 

 

첫 번째 인수를 선택한 다음 합계값을 구할 데이터의 범위를 선택합니다.

 

이렇게 수식을 입력한 다음 필터 기능을 사용하여 주문지점이 '서울지점'인 데이터만 필터링해보겠습니다.

 

단순히 SUM 함수만 사용했을 때는 필터 결과와는 상관없이 전체 범위의 합계값이 구해지는 것과는 달리 필터링된 데이터의 합계값만 계산됩니다.

 

subtotal 함수 첫 번째 인수를 100번대로 입력했을 때 차이

subtotal 함수의 첫 번째 인수는 1번대(1~11)로 입력할 수도 있고 100번대(101~111)로 입력할 수 있습니다. 만약 필터 기능만 사용해서 필터링된 데이터들의 값만을 기준으로 계산을 할 때는 어떻게 입력해도 상관없습니다. 위의 예시에서는 첫 번째 인수를 '9'로 입력하나 '109'로 입력하나 차이는 없습니다. 하지만 만약 필터가 아니라 사용자가 직접 일부 행을 숨김처리 할 때 차이가 있습니다.

 

F14셀에 '=SUBTOTAL(9,F2:F12)'이 입력된 상태에서 주문지점이 서울지점이 아닌 모든 지점을 직접 선택해서 숨겨보겠습니다.

 

주문지점이 부산지점, 대구지점, 인천지점인 행들을 모두 선택한 다음 행 머리글을 우클릭한 다음 '숨기기(H)'를 클릭합니다. 참고로 이렇게 서로 떨어진 데이터를 선택할 때는 Ctrl키를 누른 채로 선택하면 한 번에 선택할 수 있습니다.

 

subtotal 함수를 사용했음에도 불구하고 화면에 나타난 데이터의 합계값만 계산되는 것이 아니라 숨겨진 행까지 모두 포함된 값이 계산됩니다. 

 

이 상태에서 F14셀에 입력된 subtotal의 첫 번째 인수를 '9'에서 '109'로 수정해 보겠습니다.

 

첫 번째 인수를 '109'로 바꾸자 직접 숨긴 행은 제외하고 화면에 나타난 데이터의 합계값만 계산됩니다.

 

즉 첫 번째 인수를 1번대(1~11)로 입력하면 사용자가 직접 숨긴 행까지 모두 포함해서 계산이 되는 반면, 100번대(101~111)로 입력하면 숨긴 행은 제외하고 계산이 됩니다. 만약 필터 기능만 사용해서 필터링된 데이터만 계산할 것이라면 어떻게 입력해도 상관이 없지만 숨겨진 행을 포함할지, 아니면 제외하고 계산할지에 따라 값이 달라지는 상황이라면 첫 번째 인수를 상황에 맞게 선택해야 합니다.