엑셀 EXCEL 화면에 보이는 셀만 계산 SUBTOTAL 함수

 

엑셀의 주요 기능 중 가장 많이 사용하는 것이 필터링이 아닐까 싶습니다. 하지만 필터를 하다 보면 합계나, 평균 등 필터에 의해 숨겨진 셀 때문에 계산 값이 달라지게 되는데요. 예를 들어 아래와 같습니다.
 
아래의 표에 SUM 함수를 사용하여 합계를 한 표와 SUBTOTAL 함수를 사용하여 합계를 계산한 표가 있습니다.
필터를 걸기 전에는 합계의 값이 동일합니다.
 

SUM 함수, SUBTOTAL 함수 비교

 
하지만 필터링을 통해 순번 3과 4를 숨겨보도록 하겠습니다.
그러면 결과는 어떨까요?
 

SUM 함수, SUBTOTAL 함수 비교

 
합계가 달라짐을 알 수 있습니다.
 
이처럼 SUM 함수의 경우 숨겨진 셀을 포함하여 계산을 하기 때문에 변동이 없는 고정값인 반면, SUBTOTAL 함수의 경우 숨겨진 셀을 제외하고 계산을 하기 때문에 고정적이지 않고 유동적인 값을 제공합니다.
 
그렇기 때문에 필터링을 고려한 데이터 편집을 할 때에는 반드시 SUBTOTAL 함수를 사용해야 합니다.
 

 

 

300x250

 

1. SUBTOTAL 함수 사용하기

SUBTOTAL 함수를 사용하기는 쉽습니다. 기존의 함수 사용 방법에 기능번호만 입력하여 주면 됩니다.
 

=SUBTOTAL(function_num,ref1,...)
=SUBTOTAL(기능 번호, 기준 값 혹은 범위)

 
예를 들어, SUBTOTAL 함수를 사용해서 합계(SUM 함수)를 구해보도록 하겠습니다.
먼저 =SUBTOTAL( 를 입력한 다음 function_num에 숫자 9를 입력하여 주세요. 숫자 9는 SUM을 가리키는 기능 번호로 SUBTOTAL 함수에서 SUM 기능을 사용하겠다는 의미입니다. 이후, (쉼표)를 입력하여 주시고 합계를 구하고 싶은 범위를 입력하여 주면 됩니다.
 

SUBTOTAL 함수 사용

 
 
 

2. SUBTOTAL 함수를 사용하여 구할 수 있는 값

SUBTOTAL 함수를 사용해서 구할 수 있는 값은 아래와 같습니다. 

 

함수	FUNCTION NUM (필터링으로 숨긴 셀 제외)	FUNCTION NUM (필터링 및 숨겨진 셀 제외)
AVERAGE 평균	1	101
COUNT 숫자가 있는 셀 개수	2	102
COUNTA 값이 있는 셀 개수	3	103
MAX 최대값	4	104
MIN 최소값	5	105
PRODUCT 곱셈	6	106
STDEV.S 표본집단의 표준편차	7	107
STDEV.P 모집단의 표준편차	8	108
SUM 합계	9	109
VAR.S 표본집단의 분산	10	110
VAR.P 모집단의 분산	11	111

 

각 함수의 Function num. 가 두 개로 나뉘어 있는데 이러한 이유는 1,2,3,... 의 경우 필터링으로 숨겨진 셀의 데이터만 제외하는 반면 101,102,103의 경우 필터링뿐만 아니라 임의로 숨긴 셀까지 제외하는 경우입니다.
 
혹시 데이터가 많게 된다면 일일이 숨겨진 셀을 체크할 수 없기 때문에 101,102,103 등 100번 대 단위의 function num. 를 사용하시는 것을 개인적으로 추천드립니다.