엑셀 subtotal 함수 사용법(필터, 숨김값 구하기)

오늘은 엑셀 subtotal 함수에 대해서 알아보겠습니다.

목차
1. subtotal 함수란?
2. subtotal 함수 - 단축번호 설명
3. subtotal 함수 - 필터링된 셀을 제외하고 계산하기
4. subtotal 함수 - 숨겨진 셀을 제외하고 계산하기
5. subtotal 함수 - 순번 재 정렬하기

---

1. subtotal 함수란?
부분적인 계산(합계, 평균, 개수 등)을 할 때 사용합니다.

인수값
 - function_num : 함수 번호
 - ref1 : 계산 범위

 

2.subtotal 함수 - 단축번호 설명
subtotal 함수는 처음에 입력하는 함수 번호에 따라 다양한 계산식이 가능해집니다.
모든 함수번호를 숙지할 필요는 없으며 필요할 때마다 찾아서 사용하시면 됩니다.
3 - COUNTA : 값이 있는 셀의 개수를 구함
9 -SUM : 선택된 셀의 합을 구함
101 - AVERAGE : 선택한 셀의 평균을 구합

 

 

3. subtotal 함수 - 필터링된 셀을 제외하고 계산하기
아래는 시험 점수 표입니다. 점수의 합을 계산해 보려고 하는데요.
일반적으로 셀값의 합계를 계산할 때는 SUM 함수를 사용합니다.
하지만 필터링 된 값을 계산하기 위해서는 subtotal 함수를 사용해야 합니다.
왜 사용해야 하는지 아래에 2가지 결과 값을 비교해봅시다.

 

#SUM 함수 사용
SUM 함수를 사용해서 점수의 합계를 구해보았습니다.
필터를 걸지 않았을때는 정상으로 보입니다.
필터를 걸어 주소에서 강남구를 제외시켜보니 필터가 걸리지 않은 숨겨진 셀들의 합계까지 출력됩니다.

 

#subtotal 함수 사용
왼쪽 시트에 점수의 합계는 580으로 보이고 필터를 걸었을 때도 정상적으로 필터가 걸린 값들의 합계를 계산해서 보여줍니다.

 

4. subtotal 함수 - 숨겨진 셀을 제외하고 계산하기
만약 숨긴 셀을 빼고 보이는 항목들에 대한 계산 값을 계산하기를 원한다면 단축번호 앞에 '10'을 붙이면 됩니다. ( 이 방법은 필터 값 선택에도 사용이 가능합니다.)
합계 :=subtotal(109, 범위)
평균 :=subtotal(101, 범위)

5. subtotal 함수 - 순번 재 정렬하기
마지막으로 순번을 재정렬하는 방법에 대해서 알아보겠습니다.
필터를 사용해서 송파구를 제외한 모습입니다. 순서 5~7번 사이에 6번이 보이지 않습니다.

 

필터링을 했을 때도 자동으로 순번이 매겨지게 하기 위해서는 함수 번호 3번을 사용해야 합니다.
수식은 =subtotal(3, 시작 범위 절대값:해당 값 순서)입니다.
=SUBTOTAL(3,$B$4:B4)
순번 1번을 이렇게 적어주시고 나머지는 아래로  드래그하시면 모드 적용됩니다.

순번 함수 설정 후 아까와 동일하게 송파구를 필터링 한 표입니다.
순번이 자동으로 변경되어지는 것을 볼 수 있습니다.

이상으로 엑셀 subtotal 함수 기본 사용법과 
필터 값 및 숨김 값을 제외하고 계산하는 방법에 대해서 알아보았습니다.