1일1함수 (44) SUBTOTAL함수 : 부분합구하기

부분합은 저도 잘 모르기 때문에, 아는 것만 설명 드리겠습니다. 더 자세히 아시는 분은 보충 설명 부탁드립니다. 일단 도움말부터 보시겠습니다.

 

구문 SUBTOTAL(function_num, ref1, ref2, ...) function_num  목록에서 부분합을 계산하는 데 사용할 함수를 지정하며 1에서 11(숨겨진 값 포함) 사이 또는 101에서 111(숨겨진 값 무시) 사이의 값입니다. function_num (숨겨진 값 포함) function_num (숨겨진 값 무시) 함수 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP ref1, ref2  부분합을 계산할 참조 또는 범위로서, 1개에서 254개까지 지정할 수 있습니다. 주의 ref1, ref2,... 안에 다른 부분합이 있으면, 즉 중첩된 부분합이면 이중으로 계산되는 것을 피하기 위해 중첩된 부분합을 무시합니다. function_num 상수가 1에서 11 사이인 경우 SUBTOTAL 함수는 홈 탭의 셀 그룹에서 서식 명령의 숨기기 및 숨기기 취소 하위 메뉴에 있는 행 숨기기 명령으로 숨겨진 행의 값을 포함합니다. 목록에서 숨겨진 값과 숨겨지지 않은 값의 부분합을 구할 때 이러한 상수를 사용하십시오. function_num 상수가 101에서 111 사이인 경우 SUBTOTAL 함수는 행 숨기기 명령으로 숨겨진 행의 값을 무시합니다. 목록에서 숨겨지지 않은 값의 부분합만 구할 때 이러한 상수를 사용하십시오. 사용하는 function_num 값에 관계없이 SUBTOTAL 함수는 필터 결과에 포함되지 않은 행을 모두 무시합니다. SUBTOTAL 함수는 데이터 열이나 세로 범위에 사용되며 데이터 행이나 가로 범위에는 사용되지 않습니다. 예를 들어 SUBTOTAL(109,B2:G2)과 같이 101 이상의 function_num을 사용하여 가로 범위의 부분합을 구할 때 열을 숨겨도 부분합이 영향을 받지 않습니다. 그러나 세로 범위의 부분합을 구할 때 열을 숨기면 부분합이 영향을 받습니다. 참조 중 3차원 참조가 있으면 #VALUE! 오류 값이 반환됩니다.

 

밑줄친 세 부분이 한번은 짚고 넘어가야할 사안입니다. SUBTOTAL함수에 제일 처음 나오는 인수는 SUBTOTAL함수가 부분"합"뿐만 아니라 다른 함수의 기능들도 수행한다는 것을 보여줍니다. 그러므로 각 인수가 어떤 함수를 의미하는지 알아둘 필요가 있습니다.

100단위의 인수와 1단위의 인수는 숨겨진 행을 계산에 포함시키느냐 마느냐의 차이인데, 자주 쓸 일이 있을 것 같지는 않아보입니다만, 일단 알아두는 것이 좋을 듯 합니다.

SUBTOTAL함수가 가로방향으로는 적용되지 않고 세로방향만 적용된다는 것도 알아두셔야할 것 같네요.

그보다는 두번째 내용, 필터결과에 포함되지 않은 행을 모두 무시한다는 이 부분이 자주 쓰이는 부분입니다.

학생들의 성적뿐만 아니라, 각 문항에 학생들이 응답한 내용까지 나타내주는 표가 여기 있습니다.

필터를 이용해서, 1번 문항에 정답(마침표가 정답을 의미합니다)을 맞힌 학생을 골라볼 수 있다는 것은 다들 알고 계실 것입니다. 그럼, 1번 문항에 정답을 맞힌 학생들의 총점 평균은 몇점일까요? 1번에서 마침표를 필터하고, 나온 결과를 다른 시트에 붙여넣기해서, AVERAGE함수를 쓰는 방법도 있겠습니다만, 좀 더 간단한 방법을 쓰려면 SUBTOTAL함수를 사용합니다.

수식은 다음과 같습니다.

=SUBTOTAL(1,$B$2:$B$11)

맨처음 인수로 1을 사용했으므로, 범위의 셀들의 평균을 구합니다.지금 상태에서는 모든 학생들의 평균점수가 91점이라는 것을 알 수 있습니다.

 

이제 필터를 적용해 보겠습니다.

지금 상태는 1번에 마침표(.)가 표시된, 즉, 1번을 맞힌 학생들만 보여주는 필터를 건 상태입니다.

1번에 정답을 맞힌 학생들만 필터를 걸고나면, 해당학생들의 점수의 평균이 나옵니다. 92.8점이라는 점수는 1번문제에 정답을 맞힌 학생들의 점수만 평균을 낸 것입니다.

이 방법을 이용하면, 몇번 문항과 몇번 문항을 동시에 맞히거나 틀린 학생들의 숫자나 평균등을 구할 수 있습니다.

정기고사 결과분석할 때 SUBTOTAL함수와 필터를 함께 이용하시면 상당히 많은 도움이 될 것입니다.

 

참고로, SUBTOTAL함수에 포함된 다른 함수들 중 우리가 다루지 않은 함수들의 의미는 다음과 같습니다.

PRODUCT 인수로 지정된 숫자를 모두 곱한 결과를 표시합니다.

STDEV 표본의 표준 편차를 예측합니다.

STDEVP 전체 모집단의 표준 편차를 계산합니다

VAR 표본의 분산을 예측합니다.

VARP 모집단 전체의 분산을 계산합니다.