엑셀 필터 사용시 통계(합계, 평균, 개수..) 함수 - SUBTOTAL 첫번째

엑셀의 SUBTOTAL함수는 필터 사용 시 목록이나 데이터베이스 등 선택한 범위에서 필터링 후의 집계 값을 구해줍니다. 집계 결과로 합계(SUM), 평균(AVERAGE), 최대값(MAX), 최소값(MIN), 개수(COUNT, COUNTA) 등 총 11가지 형태의 집계함수를 사용할 수 있으며 필터링뿐만 아니라 숨김 처리한 데이터까지 제외시킬 수 있습니다.

예제

 

아래 예제 파일을 보시면 지금까지 해온 다팔아 쇼핑몰의 주문내역입니다.

이번에는 I4셀에 SUM함수를 이용하여 판매된 금액의 합계를 냈습니다. 흔하게 사용하는 형태입니다. 보통은 합계를 표의 마지막에 표시하기도 하지만 지금처럼 표가 긴 경우라면 예제처럼 상단에 표시하는 편이 좋습니다. 바로 확인할 수 있을 테니까요.

 

이제 하고자 하는 것은 서울지역을 필터링하고 합계를 알고 싶습니다.

필터를 적용하고 서울로 필터링해도 되지만 지금에 경우라면 좀 더 간단하게 가능합니다.

 

필터 적용

 

1. 필터링하고자 하는 셀을 선택 후 우클릭
2. 팝업메뉴에서 필터 > 선택한 셀 값으로 필터링

하면 간단하게 서울로 필터링됩니다. 이렇게 단순한 형태의 필터링은 마우스 우클릭으로 사용하면 좀 더 빠르게 필터를 적용할 수 있습니다.

 

필터 적용시 합계반영 안됨

 

필터링은 잘되었습니다. 하지만 합계 값의 변화는 없습니다. 왜냐하면 합계의 범위는 표의 전체 범위이기 때문입니다.

"합계까지 필터링된 값들로 계산이 되어 나온다면 좋을 텐데"라고 바로 생각하게 되죠

이 경우에 사용할 수 있는 것이 SUBTOTAL 함수입니다.

 

 

 

 

 

SUBTOTAL

목록이나 데이터베이스의 부분합을 반환합니다.(집계 관련된 함수들과 같이 사용)

SUBTOTAL(function_num,ref1,[ref2],...)

• function_num - 실제로 적용할 11가지 함수들의 번호입니다.
• ref1 - function_num으로 적용될 함수의 첫번째 인수
• ref2 - function_num으로 적용될 함수의 두번째 인수

 

보통 함수는 어떤 결과값을 돌출하기 위해 쓰입니다. 하지만 SUBTOTAL은 값 자체를 반환한다고 생각하지 않는 편이 좋습니다. 실제로 반환하는 값은 인수로 쓰인 function_num이 결정합니다. 단순하게 반환된 값에서 불필요한 데이터를 제거한다고 보시는 게 맞습니다.

 

그럼 function_num의 정체를 보겠습니다. 

 

SUBTOTAL 함수의 funtion_num 인수

 

1. 정체 확인을 위해 일단 빈 셀을 아무거나 선택합니다.(B4셀 선택)
2. 수식입력줄에 =SUBTOTAL( 까지 입력합니다.
3. SUBTOTAL의 첫 번째 인수인 function_num이 표시됩니다.

function_num의 힌트를 보게 되면 숫자와 함께 함수명이 보입니다. 1번은 AVERAGE, 2번은 COUNT... 9번이 SUM이네요 

즉 이 숫자들은 실제로 적용할 함수의 번호입니다. 함수를 일일이 입력할 수고를 덜어주는 역할을 하는 것이죠. 

스크롤을 쭉 내려 보면 101번부터 다시 반복합니다. 101번이 AVERAGE, 102번이 COUNT... 동일한 함수들입니다. 모두 100이 더해진 체로 같은 함수의 반복입니다. 1~11번까지를 한 묶음, 101~111까지를 한 묶음으로 보시면 되겠습니다.

 

이 함수들이 실제로 계산될 함수들입니다. 1로 선택한다면 평균값 계산, 9로 선택하신다면 합계 계산이 되게 됩니다.

그런데 AVERAGE 던 지 SUM 이던지 이 함수도 자체의 인수들이 필요합니다. 그 인수의 역할을 하는 것이 SUBTOTAL함수의 두 번째 인수인 ref1입니다. 그리고 선택된 함수의 두 번째 인수가 ref2가 되는 것입니다. 

인수를 3개로 하는 함수번호를 선택했다면 세 번째가 ref3이 되겠죠

 

SUBTOTLA 함수의 이해

 

이렇게 SUBTOTAL은 사실상 실제 함수인 function_num에서 불필요한 요소를 제거하는 역할을 하는 것이죠

다시 예제 파일로 돌아가 ESC로 B4셀의 입력을 취소하고 데이터탭> 필터를 클릭하여 필터를 해제합니다.

SUM이 적용된 셀에 SUBTOTAL을 적용하면 이제 원하는 형태로 합계를 구할 수 있습니다.

 

SUBTOTAL 함수 적용

 

1. I4셀 선택
2. 수식입력줄에 함수작성 => =SUBTOTAL(
3. 합계를 구해야 하므로 function_num을 9(SUM)로 선택 => =SUBTOTAL(9
4. 콤마 입력 후 function_num(9:SUM) 함수의 인수 범위를 지정(I7셀 클릭  > ctrl+shift+↓(아래방향키) ) 
   =>  =SUBTOTAL(9,I7:I52
5. ENTER로 작성 완료 => =SUBTOTAL(9,I7:I52)

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

 

 

 

현재는 필터를 적용한 상태가 아니므로 합계 값은 완전히 SUM과 동일한 상태입니다.

다시 지역열에 서울을 우클릭 > 필터 > 선택한 값으로 필터링을 선택합니다.

 

결과

SUBTOTLA 적용시 결과

 

필터링된 것들만의 합이 계산되어 나옵니다.

다양하게 필터링해 보세요...

 

다음 강좌로 이어집니다.

 

 

subtotal.xlsx

0.01MB

 

 

 

 

엑셀 SUBTOTLA 함수, 필터링 된 값 집계를 위한 SUBTOTLA 함수, 엑셀 SUBTOTLA 함수의 function_num 인수