엑셀 #VALUE! #N/A #REF! 오류 뜻과 해결 방법: 원인별 정리
수식을 제대로 입력한 것 같은데 셀에 #VALUE!, #N/A, #REF! 같은 문자가 대신 표시되는 경우가 있습니다. 하나만 생겨도 그 셀을 참조하는 합계나 평균까지 줄줄이 오류로 바뀌기 때문에 원인을 빨리 찾는 것이 중요합니다. 이 글은 엑셀 #VALUE 오류를 포함해 실무에서 가장 자주 보는 세 가지 오류의 뜻, 원인별 해결 방법, 오류를 미리 막는 수식 작성 습관까지 정리합니다.
설명은 Windows용 Microsoft 365 Excel과 Excel 2021, 2024를 기준으로 합니다. XLOOKUP은 Excel 2021 이후 버전에서, IFNA는 Excel 2013 이후 버전에서 쓸 수 있습니다. 메뉴 이름은 버전이나 업데이트 시점에 따라 조금 다르게 보일 수 있습니다.
오류 뜻과 원인을 찾는 기본 도구
세 가지 오류의 뜻
오류 문자는 엑셀이 계산을 끝내지 못한 이유를 알려주는 신호입니다. 종류마다 원인이 다르므로 먼저 어떤 오류인지 구분해야 해결 방향이 정해집니다.
| 오류 | 뜻 | 대표 원인 | 먼저 볼 곳 |
|---|---|---|---|
| #VALUE! | 수식 입력이나 참조한 셀의 값에 문제가 있음 | 숫자 자리에 텍스트, 숨은 공백, 텍스트로 저장된 날짜 | 참조 셀의 데이터 형식 |
| #N/A | 찾으라는 값을 찾지 못함 | 조회 값이 원본에 없음, 숫자와 텍스트 형식 불일치, 앞뒤 공백 | VLOOKUP, XLOOKUP, MATCH 조회 범위 |
| #REF! | 수식이 유효하지 않은 셀을 가리킴 | 참조하던 행이나 열 삭제, 범위를 벗어난 열 번호, 닫힌 파일 참조 | 수식 안의 #REF! 표시 위치 |
오류가 난 셀을 바로 고치기 전에 엑셀에 들어 있는 분석 기능으로 어느 부분에서 계산이 막혔는지 확인하면 시간이 크게 줄어듭니다.
수식 계산으로 단계별 확인
- 오류가 표시된 셀을 선택합니다.
- 수식 탭 > 수식 분석 그룹 > 수식 계산을 선택합니다.
- 계산 단추를 누를 때마다 수식의 각 부분이 차례로 계산되며, 오류가 처음 나타나는 지점이 곧 원인입니다.
예를 들어 =E2+E3+E4+E5 같은 수식에서 E2에 눈에 보이지 않는 공백이 들어 있으면, 수식 계산 창에서는 그 값이 " "처럼 표시되어 바로 알아볼 수 있습니다.
참조되는 셀 추적과 오류 검사
수식 탭 > 수식 분석 그룹 > 참조되는 셀 추적을 누르면 선택한 수식이 어떤 셀을 가져다 쓰는지 화살표로 표시됩니다. 같은 그룹의 오류 검사를 사용하면 시트 안의 오류 셀을 하나씩 이동하면서 원인 설명을 볼 수 있습니다. 셀 왼쪽 위에 녹색 삼각형이 보이면 엑셀이 이미 문제를 감지했다는 뜻이므로, 셀 옆 느낌표 아이콘을 눌러 제안 항목을 확인합니다.
#VALUE! 오류 해결 방법
#VALUE!는 원인이 다양해서 가장 까다로운 오류입니다. 아래 순서대로 확인하면 대부분 해결됩니다.
1. 숨은 공백과 특수 문자 제거
비어 보이는 셀에 공백이 들어 있거나 숫자 뒤에 보이지 않는 문자가 붙어 있으면, 덧셈이나 곱셈 수식이 계산되지 않습니다.
- 수식이 참조하는 열 전체를 선택합니다.
- 홈 탭 > 찾기 및 선택 > 바꾸기(Ctrl+H)를 엽니다.
- 찾을 내용에 공백 한 칸을 입력하고 바꿀 내용은 비워 둡니다.
- 공백이 필요 없는 열이 확실하면 모두 바꾸기를 선택합니다. 이름이나 주소처럼 공백이 필요한 열에는 쓰지 않습니다.
어느 셀이 텍스트인지 모르겠다면 옆 열에 =ISTEXT(E2)를 입력해 TRUE가 나오는 셀을 찾습니다. 특수 문자는 CLEAN 함수로 정리한 뒤 홈 탭 > 붙여넣기 > 값 붙여넣기로 원래 자리에 덮어씁니다.
2. 텍스트로 저장된 숫자와 날짜 바꾸기
숫자나 날짜가 셀 왼쪽에 붙어 있으면 텍스트로 저장된 상태일 가능성이 높습니다. 숫자는 녹색 삼각형 아이콘에서 숫자로 변환을 선택하면 됩니다. 날짜는 열을 선택하고 데이터 탭 > 텍스트 나누기를 실행한 뒤, 마지막 단계의 열 데이터 형식에서 날짜를 고르고 마침을 누릅니다.
3. 연산자 대신 함수 쓰기
+나 * 연산자는 텍스트가 섞인 셀을 만나면 바로 #VALUE!를 반환합니다. SUM, PRODUCT 같은 함수는 텍스트 값을 무시하고 계산하므로 오류를 피할 수 있습니다.
=A2+B2+C2 → =SUM(A2:C2) =A2*B2 → =PRODUCT(A2,B2)
4. 간단한 빼기에서도 오류가 날 때
=B1-A1처럼 단순한 빼기에서도 #VALUE!가 나온다면 Windows의 목록 구분 기호 설정이 원인일 수 있습니다. 윈도우 검색창에 지역을 입력해 제어판의 지역 설정을 열고, 형식 탭 > 추가 설정에서 목록 구분 기호가 빼기 부호(-)로 되어 있다면 쉼표(,)로 바꿉니다.
#N/A 오류 해결 방법
#N/A는 대부분 VLOOKUP, XLOOKUP, MATCH 같은 조회 함수에서 생깁니다. 찾는 값이 실제로 없거나, 있어도 엑셀이 같은 값으로 인식하지 못하는 경우입니다.
조회 값과 원본 데이터 비교
- 조회 값이 원본 표에 실제로 있는지 Ctrl+F로 검색해 봅니다.
- 한쪽은 숫자, 다른 쪽은 텍스트로 저장되어 있지 않은지 확인합니다. 코드 번호나 사번에서 자주 생기며, 형식을 한쪽으로 통일해야 합니다.
- 값 앞뒤에 공백이 있으면 TRIM 함수로 정리합니다.
- VLOOKUP의 네 번째 인수를 생략하면 근사 일치로 동작하므로, 정확히 같은 값을 찾을 때는 FALSE를 넣습니다.
=VLOOKUP(D2,$A$2:$B$100,2,FALSE)
찾지 못했을 때 표시할 값 정하기
신규 품목처럼 원본에 없는 것이 정상인 경우에는 오류 대신 안내 문구를 보여주는 편이 깔끔합니다. XLOOKUP은 마지막 인수 if_not_found에 표시할 값을 바로 넣을 수 있고, VLOOKUP은 IFNA로 감쌉니다.
=XLOOKUP(D2,A2:A100,B2:B100,"없음") =IFNA(VLOOKUP(D2,$A$2:$B$100,2,FALSE),"없음")
#REF! 오류 해결 방법
#REF!는 수식이 가리키던 셀이 사라졌거나 존재하지 않는 위치를 요구할 때 나타납니다.
행이나 열을 지운 직후라면
방금 삭제한 행이나 열 때문이라면 Ctrl+Z로 되돌리는 것이 가장 빠릅니다. 이미 저장하고 닫았다면 수식 입력줄에서 #REF!로 바뀐 부분을 올바른 셀 주소로 다시 입력합니다. =SUM(B2,C2,D2)처럼 셀을 하나씩 나열하면 열 하나만 지워도 오류가 나므로, =SUM(B2:D2)처럼 범위로 쓰면 엑셀이 자동으로 범위를 조정합니다.
열 번호나 행 번호가 범위를 넘을 때
=VLOOKUP(A8,A2:D5,5,FALSE)는 A~D 네 개 열만 지정해 놓고 다섯 번째 열을 요구하므로 #REF!가 됩니다. 범위를 A2:E5로 넓히거나 열 번호를 4 이하로 줄입니다. INDEX 함수도 지정한 범위보다 큰 행이나 열 번호를 넣으면 같은 오류가 납니다.
다른 파일을 참조할 때
INDIRECT 함수로 다른 통합 문서를 참조하는 경우 그 파일이 닫혀 있으면 #REF!가 표시됩니다. 참조하는 파일을 함께 열어 두면 정상 값으로 돌아옵니다.
IFERROR를 쓸 때 주의할 점
IFERROR는 모든 종류의 오류를 지정한 값으로 바꿔 줍니다. 편리하지만 원인을 고치는 것이 아니라 가리는 것이므로, 잘못된 범위나 삭제된 참조까지 함께 숨겨 버립니다. 그 결과 합계가 실제보다 작게 나와도 알아채기 어렵습니다.
| 함수 | 처리하는 오류 | 적합한 상황 |
|---|---|---|
| IFNA | #N/A만 | 조회 값이 없을 수 있는 VLOOKUP, MATCH |
| XLOOKUP if_not_found | 찾지 못한 경우만 | Excel 2021 이후 버전의 조회 수식 |
| IFERROR | 모든 오류 | 수식이 정상임을 확인한 뒤 표시만 정리할 때 |
조회 수식이라면 IFNA나 XLOOKUP의 if_not_found를 먼저 쓰고, IFERROR는 수식 검증이 끝난 뒤에만 사용하는 것이 안전합니다. 대용량 파일에서 수식이 많아 계산이 느려지는 문제는 엑셀 응답 없음과 버벅거림을 줄이는 최적화 방법에서 따로 정리했습니다.
확인 순서 요약
- 오류 종류를 확인합니다(#VALUE!, #N/A, #REF!).
- 수식 탭 > 수식 분석 > 수식 계산으로 오류가 처음 생기는 지점을 찾습니다.
- #VALUE!: 공백, 텍스트 숫자, 텍스트 날짜를 정리하고 연산자를 함수로 바꿉니다.
- #N/A: 조회 값 존재 여부, 데이터 형식, 공백, 정확히 일치(FALSE) 설정을 확인합니다.
- #REF!: 삭제된 참조를 복원하거나 다시 입력하고, 열 번호와 범위를 맞춥니다.
- 원인을 고친 뒤 필요한 경우에만 IFNA나 IFERROR로 표시를 정리합니다.
자주 묻는 질문
Q. 셀은 비어 보이는데 왜 #VALUE!가 나오나요?
공백이나 아포스트로피(') 같은 보이지 않는 문자가 들어 있으면 엑셀은 빈 셀이 아니라 텍스트로 인식합니다. 찾기 및 바꾸기로 공백을 지우거나, 해당 셀을 선택하고 Delete 키로 내용을 완전히 지운 뒤 다시 계산해 봅니다.
Q. 값이 분명히 있는데 VLOOKUP이 #N/A를 반환합니다.
숫자와 텍스트 형식이 다르거나 끝에 공백이 붙은 경우가 가장 흔합니다. 두 셀을 =A2=D2로 비교해 FALSE가 나오면 형식이나 공백 차이가 있는 것이므로 TRIM이나 숫자로 변환으로 맞춥니다.
Q. 파일을 저장하고 닫았는데 #REF!를 되돌릴 수 있나요?
닫은 뒤에는 Ctrl+Z가 동작하지 않습니다. OneDrive나 SharePoint에 저장한 파일이라면 버전 기록에서 이전 버전을 열어 원래 수식을 확인할 수 있고, 그렇지 않다면 수식 입력줄에서 참조를 직접 다시 지정해야 합니다.
Q. 오류를 그냥 IFERROR로 모두 숨겨도 되나요?
권장하지 않습니다. 계산이 틀린 상태를 감추게 되어 결과를 믿을 수 없게 됩니다. 원인을 먼저 고치고, 찾는 값이 없는 것이 정상인 조회 수식에만 IFNA를 쓰는 편이 안전합니다.
정리
#VALUE!는 데이터 형식, #N/A는 조회 값 일치 여부, #REF!는 사라진 참조가 핵심입니다. 오류 종류를 먼저 구분하고 수식 계산 기능으로 막힌 지점을 확인한 다음, 원인에 맞는 방법을 적용하면 대부분 몇 분 안에 해결됩니다. 오류 처리 함수는 원인을 고친 뒤 표시를 정리하는 용도로만 사용하는 것이 좋습니다.
댓글
댓글 쓰기