엑셀 SUBTOTAL 함수 사용 방법 및 실전 활용법

SUBTOTAL 함수는 엑셀의 함수 중 속도가 가장 빠른 엑셀 더하기 함수입니다. PIVOT의 단계 이전에 데이터 분석의 효율을 높일 수 있는 엑셀 필수 함수입니다. 

SUBTOTAL 함수의 정의

정의

SUBTOTAL함수는 선택한 범위 내에서 특정 기능의 계산을 수행한 결과를 값으로 보여주는 엑셀 함수입니다. 

활용의 범위로 따진다면 엑셀 함수 중 필수라고 볼 수 있습니다. 가장 큰 이유는 빅데이터 분석에 있어 필터 이후 원하는 값을 즉각적으로 도출할 수 있는 방법이 필요합니다. 이때 쓰이는 방법 중 하나가 SUBTOTAL 인 것입니다. 

 

SUBTOTAL 구문

=SUBTOTAL(11개 함수 중 사용할 함수의 번호, 사용할 전체 데이터 범위)

11개 함수에 대해서는 아래에 추가적으로 설명하도록 하겠습니다.

사용할 전체 데이터 범위는 반드시 필터가 걸려 있는 해당 열의 전체 데이터 범위로 실시 입력합니다.

11 개의 엑셀 함수 정리

총 11개의 함수와 함수번호는 아래와 같습니다.

함수 번호 "1" = AVERAGE (평균, 필터 된 데이터의 평균을 보여줍니다.)

함수 번호 "2" = COUNT (숫자 개수, 필터 된 데이터의 숫자의 개수를 보여줍니다.)

함수 번호 "3" = COUNTA (숫자 + 문자 개수, 셀에 빈칸이 아니라면 COUNT 합니다.)

함수 번호 "4" = MAX (최댓값, 필터 된 데이터 안에서 최댓값을 보여줍니다.)

함수 번호 "5" = MIN (최솟값, 필터 된 데이터 안에서 최솟값을 보여줍니다.)

함수 번호 "6" = PRODUCT (곱셉, 필터 된 데이터의 모든 숫자를 하나씩 곱한 값을 보여 줍니다.)

함수 번호 "7" = STDEV (표준집단 표준편차, 필터 된 데이터 중 숫자의 값의 차이는 절댓값으로 비교한 평균의 값입니다.)

함수 번호 "8" = STDEVP (모집단 표준편차, 모집단의 숫자뿐만 아니라 텍스트의 형식도 포함하여 평균한 값입니다.)

함수 번호 "9" = SUM (합계, 필터 된 데이터의 합계를 즉시 계산하여 보여줍니다.)

함수 번호 "10" = VAR.S (표본집단 분산, 숫자만의 분산)

함수 번호 "11" = VAR.P (모집단 분산, 모든 범위의 분산)

 

SUBTOTAL 함수 사용 방법

함수 사용 방법

SUBTOTAL의 가장 큰 장점은 필터의 활용이 자유롭다는 것입니다.

즉, 필터링된 데이터를 무시하고 계산할 수 있어, 필터에 따른 결괏값이 변화를 그 즉시 볼 수 있다는 장점이 있습니다. 

PIVOT으로 들어가기 전 가장 활용도가 높을 수 있는 함수 중 하나입니다. 

SUBTOTAL 실전 활용법

SUBTOTAL의 11개의 함수 즉, 1번부터 11번의 함수의 기능을 사용하면서 한계를 느낄 때가 있을 수 있습니다. 

필터만의 기능으로 즉시 원하는 값을 보지만, 필터 이후 여러 요청 사항이 있어 더욱더 데이터 내의 추가 필터가 필요할 때는 어떻게 해야 할까요? 

모든 필터를 다시 푼 후 새로운 필터를 만든다거나, 아니면 1개의 열을 추가하여 필터 될 수 있는 데이터를 추가 등록하여하는 방법 등이 있을 것입니다. 하지만 그 즉시 원하는 값을 보기 위해서는 필터 이후 필요 없는 데이터에 대해서 "숨기기 기능"을 사용하여 원하는 값을 구할 수 있는 방법이 있습니다. 

 

11개의 함수 번호에 100을 더한 숫자를 SUBTOTAL 함수에 입력한다면, 필터링된 이후의 데이터 만을 계산하는 것뿐만 아니라, "숨기기 기능"으로 숨겨진 데이터 이후의 값을 계산한다는 기능을 활용하는 방법입니다. 

(SUM의 번호 9번을 입력하여 사용 중인 SUBTOTAL 함수에서, 필터 이후 숨기기까지 실행하여 변화된 값을 보고 싶다면, 9번에 100을 더한 109번을 입력하면 됩니다.)