[ 엑셀 Tip ] subtotal 함수 활용법

엑셀  subtotal 함수를 사용하는 방법을 알아보도록 하겠습니다. 

엑셀에서 필터를 걸었을 때 보이는 값의 합만 구하는 방법입니다. 전체 합계가 아닌 부분의 합을 구하고 싶거나 필터를 걸 때마다 보이는 값을 구하고 싶을 때 사용 가능합니다.

다음과 같이 데이터를 준비해 주었습니다. 일반 sum 함수와 subtotal의 차이를 보기 위해서 같은 값에서 계만 sum 함수와 subtotal 함수를 사용해서 데이터를 준비했습니다.

우선 기본적으로 현재 보이는 계의 값은 동일합니다.

---

"a"의 합만 보고 싶을 때

전체의 합계가 아닌 "a"의 값을 알아보고 싶습니다. 이 때 사용할 수 있는 방법은 아래와 같습니다.

• 마우스로 드래그하여 "a"의 매출만 선택 후 아래쪽의 합계를 본다.
• 피벗테이블을 만들어서 구분별 합계를 구한다.
• subtotal 함수를 만들어서 필터를 걸어서 구분의 원하는 항목의 값의 합을 구한다.

 오늘은 subtotal의 사용법을 알아보기 위해서 맨 마지막의 방법만 사용해서 값을 구해 보도록 하겠습니다.

subtotal의 사용법을 먼저 알아보도록 하겠습니다.

=SUBTOTAL(	9,	C2:C14   )
부분합을 구해줘	합계를	C2:C14의 값에서 보이는 부분의

현재 데이터에는 모든  값이 보이기 때문에 전체의 합계와 동일합니다.

그럼 "a"의 값으로 필터를 걸어 보도록하겠습니다. ctrl + shift + L키를 눌러서 필터를 만들어 주고 구분에서 "a"를 선택해 줍니다.

차이가 보이십니까? sum 함수를 사용한 컬럼은 전체의 값을 그대로 반환하는데 subtotal은 a의 값만 반환하는 것을 볼 수 있습니다. 보이는 숫자의 합만 출력을 하고 있습니다. 그럼 맞는지 검증을 하기 위해서 매출의 값을 드래그해서 아래 값을 확인해 보겠습니다.

선택한 부분의 합계가 아래 나오고 233은 subtotal의 값과 동일하게 나오는 것을 확인 할 수 있습니다.

그럼 "a"는 우연히 맞을 수 있기 때문에 이번에는 "c"를 해 보도록 하겠습니다.

필터 구분에서 "c"를 선택해 주니 subtotal 함수의 값은 126을 반환했고 값을 드래그 하니 아래쪽 합과 동일하게 나오는 것을 볼 수 있습니다. 간단한 결과를 확인하는데 피벗테이블을 사용하지 않아도 될 것 같습니다. 

그런데 subtotal 함수 사용법에서 9를 입력해주고 합계를 이라고 작성하였는데 그럼 다른 숫자는 다른 기능을 할 것 같다는 생각이 들어서 함수의 처음 조건을 다시 한번 살펴보도록 하겠습니다.

subtotal을 입력하고 9라고 입력하니 다양한 조건이 나오는 것을 볼 수 있습니다.

1~ 11번까지 그럼과 같은 수식을 계산할 수 있는 것을 알 수 있습니다. 하나씩 한번 해 보도록 하겠습니다.

---

1 - average

필터가 걸린 값의 평균값을 구하는 방법입니다.

반응형

필터가 걸리지 않았을 때 두 개의 값은 동일하게 나오는 것을 볼 수 있습니다. 그럼 "a"를 필터를 걸어보도록 하겠습니다.

a를 필터를 걸어주고 subtotal의 값을 드래그하여 보았더니 아래의 평균과 동일한 값이 나오는 것을 볼 수 있습니다.

필터 걸린 값의 평균만 잘 반환이 되는 것을 볼 수 있습니다.

 

2 - count

필터를 걸어서 b를 선택하니 매출의 갯수는 3개이고 이 숫자를 눈으로 세봐도 알 수 있기 때문에 따로 검증하지 않도록 하겠습니다.

 

3 - counta

counta는 비어있지 않은 값만 구하는 함수로 b 중간 값을 지우고 결과를 보니 2만 나타나는 것을 볼 수 있습니다.

 

4 - max

4를 입력하면 max 값을 구하는 것인데 subtotal에서 86과 40중 최대값인 86을 반환하는 것을 볼 수 있습니다.

 

5 - min

5를 입력하면 min 값을 반환하고 d의 매출에서 최소값인 56을 반환하는 것을 알 수 있습니다. 지금 하는 것은 계속 눈으로 또는 암산으로 가능한 것이기에 따로 검증을 진행하고 있지 않으니 필요하시면 직접 검증 진행해 보실 것을 추천드립니다.

6 - product

6을 곱셈을 구하는 것인데 숫자가 크기 때문에 검증을 하기 위해서 매출(subtotal)의 값을 다 곱해 보았습니다. 동일한 결과가 나오고 있는 것을 볼 수 있습니다.

7 - stdev.s

7은 표준편차를 의미하는데 검증을 하기 싫어지게 만드는 함수입니다 엑셀을 믿고 살아야 하나 검증을 한번 해 보도록 하겠습니다.

편차 = 변수 - 평균 ( 58.25 ) 1번에서 a의 평균을 구했기 때문에 바로 사용하도록 하겠습니다. ( E 컬럼의 값 )

분산 = ( 편차 ) * ( 편차 ) 편차의 제곱 ( F 컬럼의 값 )

표준편차 = sqrt( 분산 / ( 개수 - 1 ) ) F16의 값입니다.

힘들게 계산하지 않아도 값을 잘 구해 주는 것을 확인할 수 있습니다.

8 - stdev.p

 

모집단의 표준편차를 의미하는데 위의 계산과 동일하고 ( 개수 - 1 )을 ( 개수 )로 변경하면 동일한 값을 구할 수 있습니다.

10 - var.s

 

분산의 평균값을 구하는 것으로 stdev.s에서 sqrt의 값을 구하기 전 단계의 값입니다. 동일한 값을 반환하는 것을 볼 수 있습니다.

11 - var.p

stdev.p에서 sqrt를 하기 전의 값입니다.

※ 정리 : stdev.s = sqrt( var.s = ( 값 - average )  )