엑셀에서 함수 오류나는 값 무시하고 합계 구하는 AGGREGATE 함수 사용 방법
엑셀을 이용을 할때 함수를 많이 사용을 하게 되는데요. 간혹 함수를 사용을 하다가 에러가 나는 경우가 있습니다. 에러가 나는 경우는 굉장히 다양하기 때문에 언제 에러가 뜰지 모르는데요. 이렇게 에러가 나더라도 뒤에 합계라던지 다른 값을 구해야 하는 경우 오류가 나는 부분을 무시하고 합계를 구하는등의 작업을 할수 있는 방법이 있습니다.
엑셀에서 AGGREGATE 함수 사용하는 방법
엑셀에서 AGGREGATE 함수는 이전 포스팅에서 설명 했던 SUBTOTAL 함수에서 조금더 업그레이드 된 버전으로 더 강력한 기능과 유연성을 제공하는 함수라고 보시면 됩니다. 이 함수의 경우에는 오류나 숨겨진 값등을 무시 하는 등의 기능들이 추가가 되었습니다. 그래서 앞에 포스팅 했던 SUBTOTAL 함수 대신 사용을 해도 됩니다.
그럼 지금부터 AGGREGATE 함수의 사용법과 특징, 실무 활용 방법을 쉽고 자세하게 알려드리겠습니다.
✅ 1. AGGREGATE 함수의 기본 형식
=AGGREGATE(함수번호, 옵션, 참조범위, [k])- 함수번호: 어떤 연산을 할지 결정하는 번호 (합계, 평균, 최대값 등)
- 옵션: 숨긴 셀, 오류 값 등을 무시하는 방식 선택
- 참조범위: 계산할 데이터의 범위
- [k] (선택): 특정 함수(예: LARGE, SMALL 등)에서 k번째 값을 찾을 때 사용합니다.
✅ 2. AGGREGATE 함수번호와 의미
| 함수번호 | 함수명 | 설명 | [k] 필요 여부 |
|---|---|---|---|
| 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 | k번째 큰 값 | ✔️ |
| 15 | SMALL | k번째 작은 값 | ✔️ |
| 16 | PERCENTILE.INC | 백분위수 포함 | ✔️ |
| 17 | QUARTILE.INC | 사분위수 포함 | ✔️ |
| 18 | PERCENTILE.EXC | 백분위수 제외 | ✔️ |
| 19 | QUARTILE.EXC | 사분위수 제외 | ✔️ |
✅ 3. 옵션 (계산 제외 조건)
옵션을 통해 오류나 숨겨진 셀을 제외할지 설정할 수 있습니다.
| 옵션번호 | 제외 대상 |
|---|---|
| 0 | 아무것도 제외 안 함 |
| 1 | 숨겨진 행 제외 |
| 2 | 오류 값 제외 |
| 3 | 숨겨진 행 및 오류 값 모두 제외 |
| 4 | 숨겨진 행, 숨겨진 열, 오류 값 제외 (배열용) |
| 5 | 오류 값 제외 (배열용) |
| 6 | 숨겨진 행 제외 (배열용) |
| 7 | 숨겨진 행과 오류 값 모두 제외 (배열용) |
자주 쓰는 옵션은 3(숨겨진 행 및 오류 값 제외)입니다.
✅ 4. AGGREGATE 사용 예시
아래 데이터를 예로 들어 설명하겠습니다.
| A (품목) | B (가격) |
|---|---|
| 사과 | 1000 |
| 배 | #N/A (오류) |
| 바나나 | 3000 |
| 딸기 | (숨김 행) 4000 |
딸기 행은 숨김 처리되어 있다고 가정합니다.
합계 계산 (숨긴 셀, 오류 제외)
=AGGREGATE(9, 3, B2:B5)9: 합계를 계산3: 숨겨진 행, 오류 제외- 결과: 4000 (사과 1000 + 바나나 3000)
평균 계산 (숨김, 오류 제외)
=AGGREGATE(1, 3, B2:B5)- 결과: 2000 (4000 ÷ 2)
최대값 계산 (숨김, 오류 제외)
=AGGREGATE(4, 3, B2:B5)- 결과: 3000 (바나나)
✅ 5. AGGREGATE 활용 (LARGE, SMALL 예시)
| A (이름) | B (점수) |
|---|---|
| 민수 | 80 |
| 지수 | 90 |
| 철수 | 85 |
| 영희 | 95 |
2번째로 높은 점수 찾기
=AGGREGATE(14, 3, B2:B5, 2)- 결과: 90
가장 낮은 점수 찾기
=AGGREGATE(15, 3, B2:B5, 1)- 결과: 80
✅ 6. AGGREGATE 함수의 장점
- 오류 값 자동 제외 가능: #DIV/0!, #N/A 등 오류가 있어도 결과에 영향을 주지 않게 할 수 있습니다.
- 숨김 행 처리 가능: 숨긴 셀을 무시하거나 포함할지 선택 가능하여 정확한 계산을 지원합니다.
- 다양한 통계 처리: 중앙값, 최빈값, 백분위수 등 SUBTOTAL에 없는 통계함수를 사용할 수 있습니다.
✅ 7. AGGREGATE vs SUBTOTAL 비교
| 항목 | AGGREGATE | SUBTOTAL |
|---|---|---|
| 숨김 셀 처리 | 가능 (옵션 선택 가능) | 가능 (함수번호로 선택) |
| 오류값 처리 | 가능 (자동 제외 가능) | 불가능 |
| 통계 함수 수 | 19가지 함수 제공 | 11가지 함수 제공 |
| 배열수식 지원 | 가능 | 불가능 |
AGGREGATE 함수가 SUBTOTAL보다 유연하고 강력한 기능을 제공합니다.
그러면 이제 실제로 제목에서 언급했던 오류나는 값 무시하고 합계를 구하는 방법에 대해서 알아 보도록 하겠습니다. 아래 보시는 것처럼 =aggregate 라고 입력을 하면 옵션들을 선택을 할수가 있습니다. 여기에서는 합계를 구할 예정이기 때문에 9를 눌러서 합계를 구하도록 합니다.
다음으로는 옵션을 선택을 하도록 나오는데요. 여기에서 필요한 옵션을 선택을 해주시면 됩니다. 여기에서는 오류 값 무시 라고 되어 있는 옵션을 선택을 해보도록 하겠습니다. 이렇게 하시면 오류가 발생하는 부분은 무시하고 앞에서 선택했던 함수 기능을 실행을 해줍니다.
오른쪽 이미지에서 보시는 것처럼 실제로 SUBTOTAL 의 경우에는 오류가 있는 부분이 있으면 계산을 못하지만, AGGREGATE 의 경우에는 오류가 있더라도 오류가 난 부분은 제외한체로 계산을 해주게 됩니다.
엑셀에서 데이터의 오류나 숨겨진 값으로 인한 계산 문제를 한 번에 해결하고 싶다면 AGGREGATE 함수를 사용하는 것이 가장 편리한 방법입니다. 앞에서 설명을 했듯이 SUBTOTAL보다 훨씬 다양한 통계 연산을 지원하고, 유연한 옵션을 제공하기 때문입니다.
이번 포스팅에서는 엑셀에서 SUBTOTAL 을 대신할수 있고 더 다양한 기능을 사용을 할수 있는 AGGREGATE 함수를 이용을 해서 오류나는 부분을 제외하고 합계를 구하는 방법을 알아 보았습니다. 엑셀에서 이런 상황이 있을때 활용해 보시면 좋을거 같아요. 도움이 되셨다면 하단에 구독 & 공감 많이 부탁 드립니다.