들어가기 전에
엑셀에서 소계를 만들 때 많은 사람들이 Excel의 SUBTOTAL 함수를 사용합니다. AGGREGATE 함수를 사용하면 같은 결과를 더 유연하게 얻을 수 있으며 더욱 다양한 작업을 수행할 수 있습니다. AGGREGATE 함수의 기본 사용법과 SUBTOTAL 함수와 비교한 내용을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Don’t Use SUBTOTAL in Excel: Use AGGREGATE Instead
- URL: https://www.howtogeek.com/microsoft-excel-function-dont-use-subtotal-use-aggregate/
SUBTOTAL 함수
Excel의 SUBTOTAL 함수는 사용하기 매우 간단합니다. 셀 범위에 대한 소계를 계산할 수 있으며, 수동으로 숨긴 행을 결과에 포함할지 여부를 결정할 수 있습니다.
하지만 필터링된 행은 항상 제외되며, 데이터에 오류가 있는 경우 SUBTOTAL 함수는 IFERROR 함수 내에 중첩하지 않는 한 작동하지 않습니다 . 게다가 SUBTOTAL은 11개의 함수만 지원하므로 더 복잡한 통계 계산을 수행하려는 경우에는 사용이 제한됩니다.
SUBTOTAL 함수의 사용 형식은 다음과 같습니다.
=SUBTOTAL(a, b, c)
- a (필수): 계산에 사용하려는 함수를 나타내는 숫자
- b (필수): 소계를 계산할 첫 번째 셀 범위
- c (선택 사항): 최대 252개의 소계를 구할 범위 중 첫 번째 범위를 나타내며, 각 범위는 쉼표로 구분
인수 a 의 경우 1 또는 101 = AVERAGE, 2 또는 102 = COUNT, 3 또는 103 = COUNTA, 4 또는 104 = MAX, 5 또는 105 = MIN, 6 또는 106 = PRODUCT, 7 또는 107 = STDEV, 8 또는 108 = STDEV.P, 9 또는 109 = SUM, 10 또는 110 = VAR, 11 또는 111 = VARP입니다. 인수 a 의 숫자 1~11은 수동으로 숨겨진 행을 결과에 포함시키는 반면, 숫자 101~111은 수동으로 숨겨진 행을 제외합니다.
다음 예에서는 SUBTOTAL 함수를 사용하여 B열 값의 평균을 계산하고, 6행(E팀)은 숨겼습니다. E1 셀의 소계에는 숨겨진 행이 포함되지만(인수 a = 1), E2 셀의 소계에는 숨겨진 행이 포함되지 않습니다(인수 a = 101).
그러나 필터를 사용하여 팀 E의 점수를 숨기면 필터링된 값을 SUBTOTAL에 포함할 방법이 없으므로 두 소계가 모두 같아집니다.
다음 예에서는 SUBTOTAL 함수가 범위에 오류가 있으면 오류를 반환하는 것을 확인할 수 있습니다.
이러한 문제를 방지하려면 대신 AGGREGATE 함수를 사용하면 됩니다.
AGGREGATE 함수
AGGREGATE 함수는 SUBTOTAL 함수와 유사하지만, 만들려는 집계 유형에 대한 옵션이 더 많습니다. 또한 결과에서 제외할 수 있는 항목의 범위가 더 넓어지고, Excel에서 데이터 오류를 무시하도록 설정할 수 있습니다. 즉, SUBTOTAL 함수에 비해 AGGREGATE 함수는 더 강력하고 유연합니다.
AGGREGATE 함수에는 참조용 구문과 배열용 구문의 두 가지가 있습니다. Excel은 사용자가 입력한 인수에 따라 적절한 구문을 선택하므로 어떤 구문을 사용하는지 걱정할 필요가 없습니다.
AGGREGATE 함수의 사용 형식은 다음과 같습니다.
=AGGREGATE(a, b, c, d)
- a (필수): 계산에 사용하려는 함수를 나타내는 숫자
- b (필수): 계산에서 무시할 내용을 정의하는 숫자
- c (필수): 함수가 적용될 셀 범위
- d (선택 사항): 추가 범위를 지정하는 최대 252개의 인수 중 첫 번째
배열을 사용하는 경우 AGGREGATE 함수는 약간 다른 구문을 따릅니다.
- a (필수): 계산에 사용하려는 함수를 나타내는 숫자
- b (필수): 계산에서 무시할 내용을 정의하는 숫자
- c (필수): 함수가 적용될 값의 배열
- d: LARGE, SMALL, PERCENTILE.INC 등의 배열 함수에 필요한 두 번째 인수
SUBTOTAL과 AGGREGATE의 첫 번째 차이점은 후자가 인수 a 에 대해 더 많은 옵션을 제공한다는 것입니다.
- 1: AVERAGE
- 2 = COUNT
- 3 = COUNTA
- 4 = MAX
- 5 = MIN
- 6 = PRODUCT
- 7 = STDEV.S
- 8 = STDEV.P
- 9 = SUM
- 10 = VAR.S
- 11 = VAR.P
- 12 = MEDIAN
- 13 = MODE.SNGL
- 14 = LARGE
- 15 = SMALL
- 16 = PERCENTILE.INC
- 17 = QUARTILE.INC
- 18 = PERCENTILE.EXC
- 19 = QUARTILE.EXC
AGGREGATE가 SUBTOTAL보다 실제로 뛰어난 점은 결과(인수 b )에서 특정 값을 제외할 수 있는 기능입니다.
| 숫자 | 무시되는 것 |
| 0 | 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 1 | 숨겨진 행 및 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 2 | 오류 및 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 3 | 숨겨진 행, 오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 |
| 4 | Nothing |
| 5 | 숨겨진 행만 |
| 6 | 오류만 |
| 7 | 숨겨진 행 및 오류 |
그러면 실제로 어떻게 되는지 살펴보겠습니다. 이 예에서는 다음과 같은 수식을 입력합니다.
=AGGREGATE(1, 7, Team_Scores[Score])
E2 셀에 입력하면 열 B에 표시된 값의 평균(인수 a = 1)이 반환되고, 행 8에 있는 숨겨진 값과 행 7에 있는 오류(인수 b = 7)는 무시됩니다.
이 시나리오에서는 SUBTOTAL 함수만을 사용하여 동일한 결과를 얻을 수 없습니다. IFERROR 수식 내에 중첩되지 않고서는 오류를 무시할 수 없기 때문입니다.
오류를 무시하는 것이 스프레드시트를 더 깔끔하게 보이게 하는 좋은 방법이지만, 오류를 완전히 무시하는 습관에 빠지지 마세요! 오류는 존재하는 데에는 이유가 있으며 문제 해결에 도움이 될 수 있습니다. 반면에 다음 수식을 입력하세요.
=AGGREGATE(1, 6, Team_Scores[Score])
E3 셀에 입력하면 열 B의 모든 값의 평균(인수 a = 1)이 반환됩니다. 여기에는 필터링된 행 8의 값과 오류(인수 b = 6)가 포함됩니다.
SUBTOTAL에서는 이것이 불가능합니다. 결과에는 항상 필터링된 행이 포함되고, 데이터의 오류를 우회할 방법이 없기 때문입니다.
이번에는 셀 E3에서 AGGREGATE 함수를 사용하여 중앙값(인수 a = 12)을 생성합니다. 이는 SUBTOTAL 함수에서 허용하지 않는 여러 계산 중 하나입니다.
=AGGREGATE(12, 6, Team_Scores[Score])
마지막 예에서 인수 d는 두 번째로 큰 값을 생성하는 데 사용되었습니다.
=AGGREGATE(14, 6, Team_Scores[Score], 2)
요약: SUBTOTAL과 AGGREGATE 함수 비교
다음은 Excel의 SUBTOTAL과 AGGREGATE 함수에 대한 요약입니다.
| 특징 | SUBTOTAL | AGGREGATE |
| 간단한 집계에 적합 | Y | Y |
| 복잡한 통계 집계에 적합 | N | Y |
| 지원되는 함수의 수 | 11 | 19 |
| 수동으로 숨겨진 행을 포함하거나 제외 가능 | Y | Y |
| 필터링된 행을 포함하거나 제외 가능 | N | Y |
| 오류 무시 가능 | N | Y |
| 중첩된 SUBTOTAL 또는 AGGREGATE 결과 무시 가능 | Y | Y |
| 배열 함수에 대한 추가 옵션 제공 | N | Y |
마치며
자신의 필요에 맞는 함수를 찾았다고 생각하더라도 그보다 더 나은 기능을 제공하는 다른 함수가 있는지 항상 다시 한번 확인하세요. Microsoft는 종종 기존 함수를 업그레이드한 새로운 함수를 선보입니다. 예를 들어, VLOOKUP 대신 XLOOKUP, MATCH 대신 XMATCH 함수를 사용하면 더욱 편리합니다.
'Excel' 카테고리의 다른 글
| Excel의 MOD 함수를 사용하여 실제 문제를 해결하는 방법 (0) | 2025.09.15 |
|---|---|
| [예고] 지저분한 데이터 한 번에 청소! 함수 없이 3분 만에 끝내는 방법 (0) | 2025.09.13 |
| 새로운 콘텐츠 오픈 안내 (0) | 2025.09.06 |
| Excel에서 MATCH 대신 XMATCH를 사용해야 하는 이유 (0) | 2025.09.05 |
| Excel에서 와일드카드를 사용하여 검색을 구체화하는 방법 (2) | 2025.08.29 |
