[태그:] REF오류

엑셀 오류는 #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NULL!, #NUM! 일곱 가지가 기본이고 여기에 #SPILL!, #CALC!, #####도 들어 있다. 열 가지가 저마다 원인이 다르고 고치는 방법도 다르다. 마이크로소프트는 #NAME? 오류를 IFERROR 같은 오류 처리 함수로 가리지 말라고 분명히 밝혔다.

오류가 뜬 표는 그래도 나은 편이다. 정말 곤란한 표는 오류 하나 없이 틀린 숫자만 들어 있는 표다.

1. 엑셀 오류 코드 10가지와 뜻

엑셀에서 뜨는 오류 코드 10가지와 뜻

#DIV/0!: 0이거나 빈 셀로 나눴다

#N/A: 찾는 값이 조회 범위에 없다

#VALUE!: 인수의 데이터 형식이 맞지 않는다

#REF!: 참조하던 셀이나 시트가 사라졌다

#NAME?: 함수 이름이나 정의된 이름을 못 알아본다

#NULL!: 교차하지 않는 두 범위를 공백으로 이었다

#NUM!: 계산이 불가능하거나 엑셀이 다루는 숫자 범위를 벗어났다

#SPILL!: 결과가 표시될 셀에 다른 값이 들어 있다

#CALC!: 동적 배열 함수가 돌려줄 값이 없다

#####: 열 너비가 좁거나 날짜가 음수다

#####는 수식 오류가 아니다. 열 머리글 사이를 두 번 클릭하면 사라진다. 다만 지난 날짜에서 앞선 날짜를 뺀 것처럼 결과가 음수 날짜면 너비를 아무리 늘려도 그대로다. 엑셀은 음수 날짜를 표시하지 않는다.

#SPILL!과 #CALC!는 Microsoft 365와 Excel 2021부터 나온다. 2019 이하를 쓴다면 이 두 개는 볼 일이 없다.

2. 값 입력 전 #DIV/0!·#N/A, 수식 문제 5가지

입력을 기다리는 중이라 뜨는 오류

#DIV/0!: 분모 셀이 아직 비어 있다

#N/A: 조회 목록에 아직 등록이 안 됐다

수식이나 참조를 잘못 써서 뜨는 오류

#VALUE!: 숫자 자리에 글자가 들어갔다

#REF!: 참조를 지웠다

#NAME?: 철자가 틀렸다

#NULL!: 콜론 대신 공백을 넣었다

#NUM!: 수식에 $1,000처럼 서식이 붙은 숫자를 넣었다

앞의 둘은 값이 채워지면 저절로 없어진다. 뒤의 다섯은 수식이나 참조를 고치기 전까지 그대로다. IFERROR는 이 열 가지를 구분 없이 전부 잡아낸다. 그래서 표 전체를 IFERROR로 감싸면 데이터가 아직 없어서 뜬 것인지 수식을 잘못 쓴 것인지 알 방법이 사라진다.

나눗셈이라면 =IFERROR(A2/A3,0)보다 =IF(A3,A2/A3,0)이 낫다. A3에 값이 있을 때만 계산하라는 뜻이라 나눗셈에서 생긴 문제만 처리한다. 조회 실패만 걸러 내려면 IFNA를 쓴다. 작업하는 동안은 오류를 그대로 두고, 원인을 다 찾은 다음 마지막 정리에서만 씌우는 편이 안전하다.

3. 되돌리기 말고는 복구가 없는 #REF!

행이나 열을 지운 뒤 #REF!가 보이면 바로 Ctrl+Z를 누른다. 원래 참조하던 셀 주소는 영영 사라지기 때문이다. 시트를 지운 경우는 더 심각하다. 삭제된 워크시트는 복구할 수 없다고 마이크로소프트가 밝혔다.

처음부터 #REF!가 안 나오게 하려면 수식을 다르게 써야 한다. =SUM(B2,C2,D2)에서 C열을 지우면 수식은 =SUM(B2,#REF!,C2)가 된다. =SUM(B2:D2)로 적어 두면 가운데 열을 지워도 엑셀이 알아서 범위를 줄인다. 그래서 마이크로소프트는 함수에 셀을 하나씩 나열하지 말라고 권한다.

VLOOKUP에서도 자주 난다. =VLOOKUP(A8,A2:D5,5,FALSE)는 범위가 A부터 D까지 4열인데 5번째 열을 달라고 한 수식이다. 범위를 A2:E5로 늘리거나 열 번호를 4로 줄이면 된다.

4. VLOOKUP은 FALSE, MATCH는 0으로 정확히 조회

VLOOKUP의 마지막 인수를 TRUE로 두면 정확히 일치하는 값이 아니라 비슷한 값을 찾는다. 조회 목록에 Banana가 없으면 #N/A가 뜬다. 그런데 같은 표에서 Pear는 오류도 없이 엉뚱한 가격을 가져온다. 알파벳순으로 Pear가 Peach 앞에 오기 때문이다. FALSE로 바꾸면 Pear는 제 가격을 찾아오고 Banana만 #N/A로 남는다. MATCH 함수라면 match_type을 0으로 둔다.

마지막 인수를 비워 두는 습관이 제일 위험하다. 오류가 안 뜨면 틀린 값을 발견하기 어렵다.

값이 분명히 있는데도 #N/A가 뜬다면 셋 중 하나다. 앞뒤에 공백이 붙었거나, 한쪽은 숫자인데 다른 쪽은 텍스트로 저장됐거나, 배열 수식의 참조 범위가 서로 행 수가 다르다. 공백은 TRIM으로 벗겨 낸다. 형식은 셀을 고른 뒤 Ctrl+1로 확인하고, 열 전체를 한꺼번에 바꿔야 하면 데이터 탭의 텍스트 나누기로 마침을 누른다.

5. 눈에 안 보이는 공백이 만드는 #VALUE!

공백 하나가 들어간 셀은 빈 셀처럼 보인다. 그 셀이 =E2+E3+E4+E5 안에 있으면 합계 전체가 #VALUE!가 된다. 어느 셀인지 찾으려면 수식 탭의 수식 계산을 눌러 한 단계씩 계산해 본다. 문제가 된 값이 따옴표 두 개 사이의 공백으로 드러난다. ISTEXT를 옆 열에 넣어 TRUE가 나오는 셀을 찾는 방법도 있다.

더하기 빼기 대신 함수를 쓰면 이 오류 자체가 잘 안 난다. =A2+B2+C2를 =SUM(A2:C2)로, =A2*B2를 =PRODUCT(A2,B2)로 바꾼다. 함수는 텍스트 값을 건너뛰고 숫자만 계산한다.

윈도우에서 가장 기본적인 뺄셈에도 #VALUE!가 뜬다면 엑셀이 아니라 윈도우 지역 설정이 원인이다. 새 통합 문서를 열고 A1에 2, B1에 4를 넣은 다음 C1에 =B1-A1을 입력해 본다. 여기서도 오류가 나면 제어판의 지역 설정에서 추가 설정으로 들어가 목록 구분 기호를 확인한다. 그 값이 빼기 기호로 설정돼 있으면 쉼표나 세미콜론으로 바꾼다.

6. 파일 전체에서 오류 셀 한 번에 찾기

현재 시트는 Ctrl+G를 누르고 옵션에서 수식과 오류를 고른 뒤 확인을 누른다. 오류가 있는 셀이 한꺼번에 선택되고 Tab으로 하나씩 옮겨 다닐 수 있다. 하나도 없으면 찾는 셀이 없다는 메시지가 뜬다.

이 방법은 현재 시트 하나에서만 오류를 찾는다. 파일 전체에서 오류를 찾으려면 Ctrl+F를 누르고 이렇게 넣는다.

통합 문서 전체 오류 검색 설정

찾을 내용: #*!

범위: 통합 문서

찾는 위치: 값

여기서 모두 찾기를 누르면 시트 이름이 들어 있는 오류 목록이 나온다. 별표는 아무 글자나 뜻하는 기호라서 #으로 시작해 !로 끝나는 값을 모두 찾는다.

한 가지가 빠진다. #N/A는 !로 끝나지 않아서 이 방법으로는 안 잡힌다. #N/A만 따로 한 번 더 찾아야 한다. 조회 함수가 많은 표일수록 #N/A가 더 많이 나온다.

오류 코드마다 틀린 원인이 다르다. #DIV/0!이면 분모, #N/A면 조회 값과 데이터 형식, #REF!면 방금 지운 행과 열, #NAME?이면 철자. 코드에 맞는 원인만 고치면 된다. IFERROR는 원인을 다 찾은 다음에 붙여도 늦지 않다.