[태그:] AGGREGATE

엑셀 오류 없애기는 방법마다 셀에 남는 값이 다르다. IFERROR로 오류를 0으로 바꾸면 셀에 숫자 0이 남아서 합계가 다시 나온다. 조건부 서식으로 오류를 흰 글꼴로 칠하면 #DIV/0! 같은 오류 값이 셀에 그대로 남아서 합계 칸도 오류로 남는다.

그래서 방법을 고르는 기준은 보기 좋으냐가 아니다. 그 셀의 값을 다른 수식이 다시 가져다 쓰느냐다.

1. IFERROR는 값 변경, 흰 글꼴은 오류 유지

엑셀 오류 없애는 방법별 셀에 실제로 남는 값

IFERROR(수식,0): 숫자 0

IFERROR(수식,""): 길이 0인 글자

IFERROR 0에 사용자 지정 서식(;;;) 추가: 숫자 0, 화면에만 안 보임

조건부 서식 흰색 글꼴: 원래 오류 값

오류 검사 끄기: 원래 오류 값, 초록 삼각형만 사라짐

IFERROR를 쓴 세 가지는 값을 바꾸고, 흰 글꼴과 오류 검사 끄기는 보이는 모양만 바꾼다. 합계가 다시 나오는 건 IFERROR를 쓴 세 가지뿐이다.

2. IFERROR 0은 합계 계산, 평균은 낮아짐

=IFERROR(A2/B2,0)은 오류가 나면 0을 넣는다. 0은 숫자라서 SUM도, 셀을 하나씩 짚어 더하는 =C2+C3도 그대로 계산된다.

대신 평균이 내려간다. 마이크로소프트 지원 문서의 예에서 숫자 4개의 평균은 22.75인데, 빈 셀 하나가 0으로 바뀌면 18.2가 된다. 매출 합계처럼 더하기만 하는 열이면 0이 가장 탈이 없다. 단가·달성률처럼 평균을 내는 열에는 0을 넣지 않는 편이 낫다.

오류 원인이 나눗셈의 빈 분모 하나라면 IFERROR보다 =IF(B2,A2/B2,0)이 낫다. B2가 0이거나 비었을 때만 0을 넣고, 다른 오류는 그대로 보여 준다. IFERROR는 #N/A부터 #NULL!까지 오류 7가지를 모두 0으로 바꾼다. 열을 지워서 생긴 #REF!까지 조용히 0이 된다.

3. IFERROR 빈칸은 SUM 가능, + 계산은 #VALUE!

=IFERROR(A2/B2,"")는 셀을 비어 보이게 한다. 하지만 셀 안에는 길이 0인 글자가 들어 있다. SUM과 AVERAGE는 범위 안의 글자를 건너뛰니 합계는 나오고, 평균에서도 그 행은 빠진다.

범위 대신 셀을 +로 더하면 결과가 다르다. =C2+C3에서 C3이 ""이면 결과는 #VALUE!다. 오류 하나를 지웠는데 옆 열에 새 오류가 생긴다.

인쇄해서 넘기는 표의 마지막 열이면 ""가 깔끔하다. 다른 수식이 가져다 쓰는 열이면 0이 안전하다.

4. 조건부 서식은 오류를 숨겨도 합계는 오류

수식을 건드리지 않아서 가장 빠르다.

홈 → 조건부 서식 → 규칙 관리 → 새 규칙 → 다음을 포함하는 셀만 서식 지정 → 오류 → 서식 → 글꼴 흰색

셀 안에는 #DIV/0!이 그대로 있다. 이 오류 셀을 더한 합계는 여전히 오류이고, 원인 셀은 흰 글씨라 눈에 안 띈다. 표에서 사라진 오류가 합계 칸에 다시 뜬다. 다섯 가지 중 가장 권하지 않는 방법이다.

피벗 테이블은 수식 없이 옵션에서 바꾼다. 피벗 테이블 분석 → 옵션 → 레이아웃 및 서식 → 오류 값 표시에 체크하고 대신 보일 값을 적는다. 칸을 비워 두면 빈 셀로 보인다.

5. 초록 삼각형은 숫자 변환, #####는 열 너비 조정

셀 왼쪽 위 초록 삼각형은 오류 값이 아니라 오류 표시기다. 텍스트로 저장된 숫자나 옆 수식과 모양이 다른 수식처럼, 엑셀이 의심하는 셀에 붙는다. 셀 하나에서만 없애려면 옆에 뜨는 느낌표에서 오류 무시를 고르면 된다. 모두 끄는 순서는 이렇다.

파일 → 옵션 → 수식 → 오류 검사 → 백그라운드 오류 검사 사용 체크 해제

텍스트로 저장된 숫자에 붙은 삼각형은 끌 대상이 아니다. 숫자로 바꿔야 하는 셀이다. 그 숫자는 SUM 범위 안에 있어도 합계에서 조용히 빠진다.

#####는 수식 오류가 아니다. 열이 좁아서 숫자가 안 들어가거나, 날짜·시간 값이 음수일 때 뜬다. 열 머리글 경계선을 두 번 누르면 너비가 맞춰진다. 넓혀도 남는다면 과거 날짜에서 미래 날짜를 뺀 수식이다.

6. AGGREGATE로 오류 값 빼고 합계 계산

원본 오류를 아직 못 고쳤는데 합계부터 필요하면 AGGREGATE가 있다.

=AGGREGATE(9,6,B2:B10)

9는 SUM, 6은 오류 값 무시다. 원본은 그대로 두고 합계 칸 하나만 바뀐다. 엑셀 2016 이상과 마이크로소프트 365에서 된다.

오류 셀이 그대로 남아 있으니 나중에 고칠 목록도 그대로다. 합계는 급하지만 원본 오류를 아직 고치지 못했을 때 가장 알맞다.

시트 안의 오류 셀은 이 순서로 한꺼번에 선택된다.

Ctrl+G → 옵션 → 수식 → 오류만 체크 → 확인

7. #N/A는 차트에서 숨기고 합계는 AGGREGATE

차트로 그리는 열이면 #N/A는 남겨 둔다. 엑셀 차트는 #N/A를 그리지 않는다. 0으로 바꾸면 아직 값이 없는 달이 바닥에 붙은 선으로 그려지고, 값이 들어온 달에서 선이 치솟는다. 이 열의 #N/A를 0으로 바꾸면 차트가 실제와 달라진다.

값이 아직 없는 칸은 =NA()로 채워 두고, 실제 값이 들어오면 그때 바꾼다. 이 열의 합계를 =AGGREGATE(9,6,범위)로 내면 차트와 합계가 둘 다 맞는다.