엑셀 필터별 합계 처리시 subtotal 함수사용

엑셀표에서 보통 합계함수 (=sum())를 써서 많이들 사용을 한다,

종종 전체 합계가 아닌 특정 필터별로 합계(sum)나 평균값(average) ,최대값(max),최소값(min)... 

구할경우들이 실제로 많이들 발생하고, 이런경우 subtotal 함수를 사용하면 편하다.

 

 

subtotal 함수는 function 번호에 따라 11가지 기능이 있지만 , 여기서 9번 합계 기능만 설명한다.

 

[  Excel 도움말 참조 ] 설명 목록이나 데이터베이스의 부분합을 반환합니다. 일반적으로 Excel 데스크톱 응용 프로그램에서 데이터 탭의 윤곽선 그룹에 있는 부분합 명령을 사용하여 부분합 목록을 작성하는 것이 더 쉽습니다. 부분합 목록이 만들어지면 SUBTOTAL 함수를 편집하여 목록을 수정할 수 있습니다.   구문   SUBTOTAL(function_num,ref1,[ref2],...)   SUBTOTAL 함수 구문에는 다음과 같은 인수가 사용됩니다.

function_num     필수 요소입니다. 1-11 또는 101 111 지정 하는 번호 소계를 사용 하는 함수입니다. 1-11 변하면 101 111 제외 하는 동안 수동으로 숨겨진 행이 포함 됩니다. 필터링 된 아웃 셀은 항상 제외 합니다.   ref1     필수 요소입니다. 부분합을 계산할 첫 번째 명명된 범위 또는 참조입니다. ref2,...     선택 요소입니다. 부분합을 계산할 명명된 범위 또는 참조로서 2개에서 254개까지 지정할 수 있습니다.

 

 

아래는 통상적으로 구하는 합계이다, 그냥 현재고수량 전체 합계와 재고금 전체 합계

 엑셀함수 sum 기본 사용

위 표는  현재고 수량 합계 D16 셀에 =sum(D2:D15)와 재고금  합계 E16셀에 =sum(f2:f15)로 해서 통상적으로  구한것이고, 필터링을  건후 어느 지점을  선택하면 D16셀과 F16셀이 눈에 안보이게 처리가 된다  그래서 D16과 F16에 =subtotal() 함수를  대입해주면 된다.

 

우선, 전체 필터링을  걸고, subtotal 함수의 function_number는 9 를 사용합니다.

 

"현재고 수량" 마지막 줄에 D16셀에 "=subtotal(9,d2:15)" 입력후 엔터

• 9 : sum 구하는 의미

• d2:d15 : 구할범위

 

"재고금" 마지막 줄에 F16셀에  "=subtotal(9,f2:f15)" 입력후 엔터

 

1. 선택지점의  모든 제품의 재고수량및 재고금 구하기

"지점" 컬럼에서 "A지점" 선택을 하면 -> 자동으로 재고수량과 재고금이 구해지고,

다른 지점들도 동일하게 지점들만 선택을 해주면 된다.

엑셀 subtotal 의 합계 

D16셀의 수식 =sum(d2:d15) -> =subtotal(9,d2:d15) 로 수식 변경

F16셀의 수식 =sum(F2:F15) -> =subtotal(9,F2:F15) 로 수식 변경 하여, 지점별 필터링을 하게 되면 해당 지점별의 현재고수량과, 재고금을 subtotal함수가 필터링해서 합계를 해준다

 

2. 제품별 전체 재고 수량,재고금 필요시

제품별로 전지점 재고수량및 재고금 구할때는 , 

"제품"컬럼에서 해당 제품 선택을 하면 -> 자동으로 모든 지점의 재고 수량과 재고금액이  나온다,

 

위 예는 필터링별 합계를 구한것이고, 나머지 평균값,최대값,최소값.... 의 function_number를 사용해서

그때그때마다 적용을 해주면 상당히 유용하고, 편리하다.