엑셀(excel) 함수 TIP 33 - subtotal 함수로 11개 값 구하기

,,., 2018. 9. 23. 08:05

엑셀(excel) 함수 TIP 33 - subtotal 함수로 11개 값 구하기

목록이나 데이터베이스의 부분합(subtotal)을 구합니다. 합계, 평균, 개수, 최대, 최소, 표준편차 등 함수 하나로 구할 수 있는 값이 11가지나 됩니다. subtotal함수는 1부터 11까지 또는 101부터 111까지의 함수 번호를 이용해서 해당되는 함수로 계산을 수행합니다. 이 함수의 특징은 숨겨져 있는 셀을 계산에 포함시키거나 제외할 수 있도록 지정할 수 있습니다.

▶ SUBTOTAL 함수 형식

형식 : SUBTOTAL(function_num, ref1. ref2,....)

- 데이터목록(ref)에서 지정함 함수(function)를 구하시오

사용예 : SUBTOTAL(4,A1:B2, C1:G2)

- [A1:B2], [C1:G2]의 범위에서 4(최대값/MAX)을 구하시오

▶ SUBTOTAL 함수입력창 구조

▶ SUBTOTAL함수의 FUNCTION-NUM에 따른 함수값

▶ SUBTOTAL함수의 사용예

하단과 같이 [박희망]학생의 [학년학기별] [과목별] 점수가 있습니다. 이 점수에 대한 각각의 SUBTOTAL값을 구하는 방법입니다. 하단의 자료에서는 숨겨진 셀이 없는 경우의 계산 방법(숨겨진 값이 있는 경우에는 숨겨진 값도 포함)입니다. 해당 값들은 하나의 숫자로만 표시되어야 합니다.

▶CHOOSE함수를 사용하기

CHOOSE함수를 사용한 이유는 [함수번호]란에 간단하게 번호를 입력하면 해당 함수가 나타나고 그에 따른 결과값을 알수가 있습니다. [C3]에 들어간 CHOOSE함수에는 SUBTOTAL함수에 사용되는 함수들이 순서대로 들어가있습니다.

SUBTOTAL(B3,C6:F11)이란 구문은 [C6:F11]의 범위에서 B3의 함수(SUM)를 구하시오라는 뜻입니다. 하단과 같이 16개의 시트에 있는 전체의 값들이 더해졌습니다.

▶ 숨겨진 셀에 대한 값

하단에서 우측에 보이는 것처럼 7,9행의 사이에 8행(우측의 겨울학기)이 숨겨져 있습니다. 함수번호가 1~11번을 이용하면 숨겨진 셀도 포함을 시킵니다. 그결과 실제로 보이는 셀이 24개라 하더라도 숨겨진 4개의 셀값이 있기 때문에 COUNT는 결과값이 28로 나타납니다. 만약 번호를 101~109번호로 바꾸어서 함수를 사용한다면 숨겨진 셀을 제외하기 때문에 COUNT에 대한 결과값은 24가 될 것입니다.

함수번호를 1,2,4,7,9번호로 바꾸면 해당하는 함수AVERAGE, COUNT, MAX, STDEB, SUM함수에 대한 결과값이 표시가 됩니다. 이처럼 CHOOSE함수와 SUBTOTAL함수를 이용하여서 11가지 함수에 대한 값을 한번에 계산할 수 있습니다.