엑셀에서 오류가 난 셀이 있어도 계산할 수 있는 방법
AGGREGATE 함수
엑셀에서 자주 쓰는 함수라고 하면 `SUM`, `AVERAGE`, `MAX` 등이 떠오르죠.
하지만 잘 알려지지 않았지만, 상황에 따라 훨씬 강력하게 쓸 수 있는 함수가 있습니다.
바로 AGGREGATE 함수입니다.
- 1. AGGREGATE 함수란?
`AGGREGATE` 함수는 합계, 평균, 최대/최소, 순위 등 여러 계산을 하면서 오류값이나 숨겨진 셀을 무시할 수 있는 함수입니다.
즉, 일반 합계(`SUM`)나 평균(`AVERAGE`)보다 훨씬 유연하게 데이터를 처리할 수 있습니다.
- 2. 기본 문법
=AGGREGATE(함수번호, 옵션, 배열, [k])
– 함수번호: 어떤 계산을 할지 정합니다.
– 1 = AVERAGE
– 2 = COUNT
– 4 = MAX
– 9 = SUM
– 14 = LARGE
– 15 = SMALL
– 옵션: 무시할 조건을 선택합니다.
– 1 = 숨겨진 행 무시
– 2 = 오류값 무시
– 3 = 숨겨진 행 + 오류값 무시
– 배열: 계산할 범위
– k: 특정 위치값이 필요한 경우 (예: n번째 큰 값, 작은 값)
- 3. 예제
다음과 같은 데이터가 있다고 해봅시다.
| 점수 |
|——|
| 80 |
| 90 |
| #DIV/0! (오류) |
| 100 |
| 70 (숨김 처리됨) |
– 단순히 `=AVERAGE(A1:A5)`를 쓰면 오류 때문에 계산이 되지 않습니다.
– 하지만 `AGGREGATE` 함수를 쓰면 해결됩니다:
=AGGREGATE(1, 6, A1:A5)
– `1` → AVERAGE(평균)
– `6` → 오류값 무시 + 숨겨진 행 무시
– 결과 = (80 + 90 + 100) / 3 = **90**
- 4. 활용 포인트
– 대량 데이터에서 숨김 처리된 행을 빼고 합계/평균 을 낼 수 있습니다.
– 설문 조사나 시험 점수처럼 오류값이 섞여 있어도 계산 가능합니다.
– `LARGE`, `SMALL`과 함께 쓰면 n번째 큰 값/작은 값도 쉽게 구할 수 있습니다.