원인 모를 수식 깨짐 해결과 #N/A, #VALUE! 오류 대처법(메인 키워드: 엑셀 수식 오류)

엑셀로 정성껏 수식을 짜고 엔터를 눌렀을 때, 원하는 결과 대신 #N/A, #VALUE!, #REF! 같은 해독할 수 없는 기호가 나타나면 누구나 당황하기 마련입니다. 특히 중요한 매출 보고서나 고객 정산서를 작성하는 중이었다면 식은땀이 흐르기도 합니다.

이러한 에러 표시들은 엑셀이 우리에게 "수식에 문제가 있으니 확인해 달라"고 보내는 일종의 SOS 신호입니다. 원인만 정확히 알면 아주 간단하게 해결할 수 있지만, 초보 시절에는 어디서부터 손을 대야 할지 몰라 수식 전체를 지우고 처음부터 다시 작성하곤 합니다. 오늘은 실무에서 가장 자주 마주치는 대표적인 엑셀 수식 오류 3가지의 발생 원인과, 이를 3분 만에 깔끔하게 해결하고 숨기는 실전 대처법을 공유하겠습니다.

 

1. 찾으려는 값이 없을 때 만나는 첫 번째 장벽: #N/A 오류
VLOOKUP이나 XLOOKUP, MATCH 함수를 사용할 때 가장 흔하게 보는 오류가 바로 #N/A(Not Available)입니다. 이 오류는 말 그대로 "네가 찾으라고 지정한 데이터가 원본 테이블에 존재하지 않는다"는 뜻입니다.

눈으로 보기에는 분명히 원본 데이터에 존재하는 이름이나 코드인데도 이 오류가 뜬다면, 1편에서 다루었던 '눈에 보이지 않는 공백' 때문일 확률이 90% 이상입니다. 검색하려는 셀에는 "A001"이라고 적혀 있는데, 참조 범위 데이터에는 "A001 "처럼 뒤에 공백이 들어가 있으면 엑셀은 두 값을 완전히 다른 것으로 인식하여 값을 찾지 못합니다.

해결 방법: 참조 범위와 검색할 데이터 양쪽 모두에 TRIM 함수를 적용해 불필요한 공백을 제거해 주어야 합니다. 또한, VLOOKUP 함수의 마지막 인수를 정확하게 일치하는 값을 찾도록 0 또는 FALSE로 지정했는지 재차 확인해 보세요. 이 인수를 누락하면 엑셀이 유사한 값을 찾다가 길을 잃고 오류를 뿜어내기도 합니다.

 

2. 데이터의 성격이 서로 맞지 않을 때: #VALUE! 오류
#VALUE! 오류는 함수나 수식에 잘못된 유형의 데이터가 입력되었을 때 발생합니다. 가장 대표적인 상황은 '숫자'를 계산해야 하는 자리에 '텍스트'가 들어가 있는 경우입니다.

예를 들어, 단가와 수량을 곱하는 수식을 짰는데 수량 셀에 '10개'처럼 텍스트가 섞여 있거나, 매출 합계를 구하려는 범위에 문자열이 포함되어 있으면 이 오류가 발생합니다. 가끔 눈으로 보기에는 분명히 숫자인데 오류가 난다면, 그 숫자가 엑셀 내부적으로 '텍스트 형식으로 저장된 숫자'로 지정되어 있기 때문입니다. 셀 왼쪽 상단에 작은 초록색 삼각형 표시가 있다면 100% 텍스트 형식 숫자입니다.

해결 방법: 오류가 난 셀의 범위를 지정한 뒤, 마우스 우클릭 후 셀 서식에 들어가 표시 형식을 '숫자' 또는 '일반'으로 변경해 주어야 합니다. 텍스트 형식 숫자의 경우, 셀 옆에 뜨는 노란색 경고 창 아이콘을 클릭하고 숫자로 변환을 누르면 단번에 해결됩니다. 수식 내에 공백 문자("")가 들어가서 계산을 방해하는지도 확인하는 것이 좋습니다.

 

3. 참조하던 방이 통째로 사라졌을 때: #REF! 오류
개인적으로 실무에서 가장 위험하다고 생각하는 오류가 바로 #REF!(Reference)입니다. 이 오류는 수식이 참조하고 있던 셀이나 행, 열이 물리적으로 삭제되어 "바라볼 주소가 완전히 사라졌다"는 의미입니다.

예를 들어, A열과 B열을 더한 값을 C열에 함수로 구해놓았는데, 나중에 필요 없다고 판단하여 B열을 마우스 우클릭으로 '삭제'해 버리면 C열의 수식은 갈 곳을 잃고 일제히 #REF!로 변하게 됩니다. 한 번 발생하면 원본 데이터를 기억하지 못하는 이상 되돌리기(Ctrl+Z) 외에는 수식을 복구하기 어렵기 때문에 주의가 필요합니다.

해결 방법: 데이터를 삭제할 때는 원본 열을 무작정 지우기보다, 수식이 걸려 있는 결과 열을 복사(Ctrl+C)한 뒤 그 자리에 값으로 붙여넣기를 하여 수식을 일반 텍스트 데이터로 고정하는 작업이 선행되어야 합니다. 그 이후에 원본 데이터를 지우면 오류 없이 안전하게 문서를 관리할 수 있습니다.

 

4. 보기에 흉한 오류 메시지를 깔끔하게 숨기는 팁 (IFERROR 함수)
데이터가 실제로 없어서 #N/A가 뜨는 것이 정상인 경우에도, 보고서에 기호들이 가득하면 문서의 완성도가 떨어져 보입니다. 이때 수식 전체를 IFERROR 함수로 감싸주면 오류 대신 깔끔한 빈칸이나 다른 대체 문구로 표시할 수 있습니다.

기존에 작성한 수식이 =VLOOKUP(A2, B:C, 2, 0) 이라면, 이 수식 앞뒤에 다음과 같이 입혀보세요.
=IFERROR(VLOOKUP(A2, B:C, 2, 0), "")

이 수식은 "VLOOKUP을 실행하되, 만약 에러가 발생하면 에러 기호 대신 뒤에 지정한 큰따옴표 두 개("") 즉, 빈칸을 출력하라"는 뜻입니다. 빈칸 대신 "데이터 없음"이라는 안내 문구를 넣고 싶다면 "" 사이에 해당 텍스트를 입력하면 됩니다.

주의할 점은 IFERROR 함수를 처음부터 무작정 쓰면 안 된다는 것입니다. 수식을 짜는 과정에서 발생한 심각한 구조적 오류나 오타까지 모두 숨겨버리기 때문에, 데이터 검증이 완벽히 끝난 최종 보고서 단계에서 가독성을 높이는 마감용으로만 활용하는 것을 권장합니다.