[엑셀 함수] SUBTOTAL로 필터링된 데이터만 계산하기

 

회사에서 엑셀을 사용하다 보면 특정 조건의 데이터를 빠르게 계산해야 할 때가 있습니다. 그때마다 SUMIFS나 배열수식을 사용하는 것은 번거로울 것입니다. 이때 SUBTOTAL 함수를 사용하면 데이터에서 필터링된 부분만 빠르게 계산할 수 있습니다. 

 

#1. SUBTOTAL 함수 소개

▲ 위의 사진처럼, SUBTOTAL 함수는 필터링된 데이터에 대하여 다양한 계산할 수 있습니다. 그리고 SUMIFS 함수 등에 비해 간단하여 신속하게 데이터를 분석할 때 유용합니다. 

 

SUBTOTAL 함수의 구문은 아래와 같습니다.

SUBTOTAL(함수 번호, 범위1, 범위2, ...)

­- 함수번호: 사용할 함수의 유형을 지정하는 숫자입니다. 이 번호는 평균, 합계, 개수 등 다양한 계산 방법을 나타냅니다.

• 1: AVERAGE - 평균
• 2: COUNT - 숫자 셀의 개수
• 3: COUNTA - 비어 있지 않은 셀의 개수
• 4: MAX - 최댓값
• 5: MIN - 최솟값
• 6: PRODUCT - 곱
• 7: STDEV - 표준 편차(샘플)
• 8: STDEVP - 표준 편차(모집단)
• 9: SUM - 합계
• 10: VAR - 분산(샘플)
• 11: VARP - 분산(모집단)

- 범위1, 범위2, ...: 계산할 데이터의 범위입니다. 하나 이상의 범위를 지정할 수 있습니다.

 

728x90

 

#2. 사용예시 ① 필터링된 데이터의 합계 구하기

 

▲ SUBTOTAL(9, B3:B12)를 입력하면 B3:B13 범위 중 필터링된 데이터에 대한 합계(SUM, 함수번호 9)를 구할 수 있습니다. 함수 번호는 수식을 입력할 때 나오기 때문에 별도로 외우실 필요는 없습니다. 

 

▲ 위의 사진처럼 데이터 범위에 필터를 건 후, 홀수 여부에서 "FALSE" 선택하여 데이터를 필터링하면 SUBTOTAL 함수를 사용한 결과, 짝수의 합계만 구할 수 있습니다. SUMIFS 함수를 활용했다면 조건이 바뀔 때마다 수식을 수정해야 하지만, SUBTOTAL을 활용하면 클릭만으로도 조건별로 합계를 구할 수 있습니다.  

 

#3. 사용예시 ② 필터링된 데이터의 평균 구하기

 

▲ SUBTOTAL(1, B3:B12)를 입력하면 평균을 구할 수 있습니다. 즉, 기존의 수식에서 함수 번호만 1로 바꾸면, 데이터 범위의 평균을 구하게 됩니다. 사용할 함수를 손쉽게 바꿀 수 있다는 점도 SUBTOTAL 함수의 장점입니다.