반응형
구글 시트나 엑셀에서 소계가 포함된 계산을 할 때 요긴하게 사용할 수 있는 함수 중에 SUBTOTAL 함수가 있습니다. 이 함수는 구글 시트뿐 아니라 엑셀에도 동일하게 존재합니다.
SUBTOTAL() 함수가 필요한 상황
시트의 열(세로)에 대한 부분 합을 계산해 줍니다. 아래의 2개의 동일한 합계를 구하는데, 첫 번째의 합계는 1,600,000원이고 두 번째의 합계는 1,200,000원입니다. 정상적이라면 오른쪽처럼 1,200,000원이 나오는 것이 맞습니다. 체험비와 식사비는 소계가 적용되어 있기 때문에 모두 더한다면 중복 계산이 되기 때문입니다.
- 1,600,000 : 소계까지 더해진 금액이므로 틀린 값입니다.
- 1,200,000 : 소계가 중복 계산되지 않기 때문에 맞는 값입니다.
이렇게 소계가 포함된 합산식에서 매우 편하게 사용할 수 있는 함수입니다.
SUBTOTAL() 함수 사용 방법
SUBTOTAL은 꼭 범위의 합산만 구하는 함수는 아닙니다. 아래와 같이 다양한 값을 구할 수 있으며 그래도 일반적으로 가장 많이 사용하는 9번 또는 109번인 SUM() 정도는 기억해 놓는 것이 좋습니다.
구문
SUBTOTAL(함수 코드 번호, 범위 1, [범위 2, ...])
함수 코드 번호
| Code (숨겨진 행 포함) | Code (숨겨진 행을 무시) | 기능 | 번역 |
|---|---|---|---|
| 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 | 분산 |
사용 예제
가장 많이 사용되는 SUM에 대해서만 사용 설명을 해보겠습니다.
- 체험비는 번지점프, 레일바이크, 우도 잠수함, 9.81파크 이용에 대한 소계 비용입니다. 사실 요기에서는 SUM() 함수나 SUBTOTAL() 함수나 동일한 결괏값이 나옵니다.
=SUBTOTAL (9, F5:F8) - 식사비도 한라산 비빔밥, 흑돈가, 고기 국수에 대한 소계 비용입니다. 마찬가지로 여기도 SUM() 함수나 SUBTOTAL() 함수나 동일한 결괏값이 나옵니다.
=SUBTOTAL (9, F10:F12) - 중요한 부분이 합계를 구하는 부분인데, 여기서도 SUM() 함수가 아닌 SUBTOTAL() 함수를 사용하게 됩니다. 범위는 전체 범위를 잡으시면 됩니다. 그러면 SUBTOTAL() 함수가 자동으로 소계에 사용된 SUBTOTAL() 함숫값을 알아서 제외해 줍니다. 그래서 최종 결괏값이 정상적으로 나오게 됩니다.
=SUBTOTAL (9, F3:F14)
