엑셀 수식 오류 해결, #VALUE! #REF! 원인 정리

엑셀 수식 오류는 #VALUE!, #REF!처럼 #으로 시작하는 코드로 표시되고, 코드마다 원인이 정해져 있습니다. 코드만 보면 어디를 고쳐야 할지 절반은 알 수 있다는 뜻입니다. 반대로 오류가 난 셀을 IFERROR로 감싸 화면에서 지우는 것은 해결이 아닙니다. 글자만 사라질 뿐 합계는 여전히 틀린 채로 남고, 그 숫자가 월말 원가 보고서나 견적 단가에 그대로 들어갑니다.

아래에서 오류 코드별 원인을 먼저 정리하고, 코드마다 고치는 법, 수백 행짜리 파일에서 오류 셀을 한 번에 찾는 순서, IFERROR를 써도 되는 기준까지 차례로 다룹니다.

엑셀 수식 오류 코드와 원인

셀 왼쪽 위의 초록 삼각형은 오류 코드와 다릅니다. 계산은 끝났지만 "숫자가 텍스트로 저장됨"이나 "인접한 수식과 다름"처럼 확인해 보라는 표시입니다. 셀을 클릭하면 옆에 느낌표 단추가 뜨고, 눌러 보면 무엇을 의심하는지 알려 줍니다.

오류 코드별 해결 방법

#VALUE!: 텍스트와 공백 정리

수량이나 금액 칸에 "5개", "12,000원"처럼 단위가 붙어 있거나, 눈에 안 보이는 공백이 들어 있으면 더하기와 곱하기가 멈춥니다. Microsoft 지원 문서의 #VALUE! 해결 안내도 빈 셀처럼 보이지만 공백이 든 셀을 가장 흔한 원인으로 꼽습니다.

단위는 머리글로 옮기고 값은 숫자만 남깁니다. 공백은 TRIM, 인쇄되지 않는 문자는 CLEAN으로 지우고, 웹에서 복사한 표라면 =SUBSTITUTE(A2,CHAR(160),"")로 줄바꿈 없는 공백을 걷어 냅니다.

=A2+B2 대신 =SUM(A2:B2)를 쓰면 텍스트를 건너뛰고 더하므로 오류는 사라집니다. 하지만 건너뛴 값이 원래 더해야 할 숫자였다면 결과가 조용히 작아집니다. 오류를 없애는 것보다 텍스트를 숫자로 바꾸는 쪽이 맞습니다.

텍스트로 저장된 숫자는 표시 형식을 '일반'으로 바꾼 뒤 F2, Enter로 다시 입력해야 숫자가 됩니다. 셀이 많으면 열을 선택하고 [데이터] → [텍스트 나누기]에서 아무것도 바꾸지 않고 [마침]을 누르면 열 전체가 한 번에 숫자로 바뀝니다.

#REF!: 삭제 직후에는 Ctrl+Z

참조하던 열이나 시트를 지우면 수식 안의 주소가 #REF!로 바뀝니다. 지운 직후라면 Ctrl+Z가 가장 확실합니다. 저장하고 닫은 뒤라면 수식 입력줄에서 #REF!가 들어간 자리를 보고 원래 무엇을 참조했는지 같은 행의 다른 수식에서 추정해 다시 씁니다. 범위를 Ctrl+T로 표로 바꿔 표1[운송비] 같은 구조적 참조를 쓰면 열을 옮기거나 지워도 이런 일이 훨씬 적습니다.

#NAME?: 오타와 따옴표

SUMM 같은 함수 이름 오타, =IF(A2=완료,1,0)처럼 텍스트에 따옴표를 뺀 경우, 지운 이름 정의를 참조하는 경우, =SUM(A2A10)처럼 콜론을 빠뜨린 경우 순서로 봅니다. 함수를 입력할 때 자동 완성 목록에서 Tab으로 고르면 철자 오류가 거의 없어집니다. XLOOKUP, FILTER처럼 Microsoft 365와 Excel 2021 이후에 생긴 함수를 Excel 2016에서 열어도 #NAME?이 납니다.

#DIV/0!: 분모 확인

빈 셀도 0으로 계산됩니다. 이번 달 생산이 없었던 품목처럼 분모가 0인 것이 실제 상황이라면 =IF(C2=0,"-",B2/C2)처럼 분모를 먼저 확인해 "-"를 표시합니다. 분모가 비어 있으면 안 되는 자리라면 빈 셀을 채우는 것이 먼저입니다.

#N/A: 찾는 값이 없을 때

VLOOKUP이나 XLOOKUP이 찾는 값을 못 찾으면 #N/A가 납니다. 정말 없는 값이라면 정상적인 결과이고, 분명히 있는데 #N/A라면 찾는 값 앞뒤의 공백, 한쪽만 텍스트로 저장된 숫자 코드, VLOOKUP 네 번째 인수가 FALSE인지를 확인합니다.

#SPILL!, #NUM!, #NULL!, 순환 참조

#SPILL!은 Microsoft 365의 동적 배열 함수(FILTER, UNIQUE, SORT 등) 결과가 펼쳐질 자리에 값이나 병합 셀이 있을 때 납니다. 수식 셀을 클릭하면 펼쳐질 범위가 점선으로 보이므로 그 안을 비웁니다. Microsoft 지원 문서는 표 안에서는 배열이 펼쳐지지 않는다는 점도 원인으로 꼽습니다. #NUM!은 =SQRT(-1)처럼 정의되지 않는 계산이나 수렴하지 않는 IRR에서 나고, #NULL!은 =SUM(A1:A5,C1:C5)의 쉼표를 공백으로 쓴 경우입니다. 순환 참조는 합계 범위에 결과 셀 자신이 들어간 경우가 대부분이며, [수식] → [오류 검사] → [순환 참조]에서 셀 주소를 바로 찾을 수 있습니다.

오류 셀을 한 번에 찾고 원인 좁히기

시트가 여러 장이고 행이 수백 개라면 눈으로 오류를 찾지 않습니다. 엑셀에 오류 셀만 골라 주는 기능이 있습니다.

1. 시트를 열고 Ctrl+G(또는 F5)를 누른 뒤 [옵션]을 눌러 [이동 옵션]을 엽니다.
2. '수식'을 고르고 아래 체크 중 '오류'만 남긴 뒤 [확인]을 누릅니다.
3. 오류가 난 수식 셀만 한꺼번에 선택됩니다. 이 상태에서 채우기 색을 주면 어디에 몰려 있는지 바로 보입니다.

그다음 [수식] 탭 → [수식 분석] 그룹의 도구로 원인을 좁힙니다. [오류 검사]는 시트의 오류를 하나씩 보여 주고 [계산 단계 표시]로 연결됩니다. [수식 계산]은 중첩된 수식을 한 단계씩 풀어 보여 주므로, 처음 오류가 나타나는 단계가 원인입니다. [참조되는 셀 추적]은 이 수식이 어떤 셀을 보고 있는지 화살표로 그려, 오류가 다른 셀에서 넘어온 것인지 구분해 줍니다. Microsoft 지원 문서의 수식 오류 감지 안내에 각 도구의 쓰임이 정리되어 있습니다.

원가 분석 파일의 오류 정리하기 (예시)

대영식품 원가 담당자는 월말마다 품목별 원가 분석 파일을 정리합니다. 생산, 구매, 물류 담당자가 각자 시트를 고쳐 쓰는 파일이라 이번 달에는 열자마자 오류가 40개 넘게 보였습니다.

1. 시트마다 오류 셀을 칠합니다

시트별로 이동 옵션의 '수식 → 오류'로 오류 셀을 선택해 노란색으로 칠합니다. '구매' 시트에 #REF! 18개, '생산' 시트에 #DIV/0! 9개와 #VALUE! 12개, '요약' 시트에 #NAME? 3개가 나왔습니다.

2. 연쇄 오류부터 끊습니다

'요약' 시트의 오류 일부는 '구매' 시트의 #REF!를 참조해서 생긴 것입니다. [참조되는 셀 추적]으로 따라가 보면 출발점은 '구매' 시트 하나입니다. 이번 달 누가 운송비 열을 지우면서 18개 수식이 함께 깨졌고, 운송비는 '물류' 시트에 남아 있으므로 그 열을 참조하도록 첫 수식을 고친 뒤 아래로 채웁니다. 이 하나로 '요약' 시트의 오류 일부도 같이 사라집니다.

3. 데이터 오류를 고칩니다

'생산' 시트의 #VALUE!는 수량 칸에 "5 ea"처럼 단위가 붙은 값이었습니다. 찾기 및 바꾸기로 " ea"를 지우고 숫자로 바꿉니다. #DIV/0!은 이번 달 생산이 없는 품목이라 =IF(생산량=0,"-",원가/생산량)으로 바꿉니다. '요약' 시트의 #NAME?은 VLOOKUPP 오타 세 개였습니다.

4. 합계를 비교합니다

고치기 전과 후의 시트별 합계를 나란히 적어 두면, 원가 보고서의 숫자가 왜 바뀌었는지 설명할 수 있습니다. 운송비가 다시 들어가면서 품목 원가 합계가 2.1% 올라갔다는 식입니다. 마지막으로 이동 옵션을 한 번 더 돌려 남은 오류가 없는지 확인합니다.

👉 엑셀 수식 복사, 참조 고정과 채우기 방법

IFERROR는 검산이 끝난 수식에만

=IFERROR(원래수식,대체값)은 모든 오류를 대체값으로 바꿉니다. IFERROR 함수는 편리하지만, #REF!나 #NAME?처럼 수식이 망가졌다는 신호까지 똑같이 가립니다.

그래서 IFERROR는 수식이 맞다는 것을 확인한 뒤, 예상할 수 있는 오류만 처리할 때 씁니다. 조회 실패만 처리하려면 #N/A만 잡는 IFNA를, 0으로 나누는 경우만 처리하려면 IF로 분모를 먼저 확인하는 편이 다른 오류를 가리지 않습니다.

대체값으로 ""를 쓰면 눈에는 비어 보여도 진짜 빈 셀이 아니라서 COUNTA는 값으로 세고, 차트에서는 0으로 찍힐 수 있습니다. 차트에 쓰는 열이라면 NA()를 대체값으로 두면 선이 끊긴 채로 그려집니다.

수식이 글자 그대로 보일 때

수식이 계산되지 않고 =B3+C3 글자로 보인다면 네 가지 중 하나입니다. 셀 서식이 '텍스트'이면 '일반'으로 바꾸고 F2, Enter로 다시 입력합니다. 시트 전체가 수식으로 보이면 수식 표시 모드가 켜진 것이니 Ctrl과 숫자 1 왼쪽의 물결표(~) 키를 함께 눌러 끕니다. 값을 바꿔도 결과가 그대로라면 [수식] → [계산 옵션]이 '수동'이니 '자동'으로 바꾸거나 F9를 누릅니다. 수식 앞에 공백이나 작은따옴표(')가 있으면 수식 입력줄에서 지우고 Enter를 누릅니다.

버전별로 다른 오류: #CALC!, #SPILL!, _xlfn

Microsoft 365와 Excel 2021부터는 동적 배열이 들어오면서 오류 코드가 늘었습니다. FILTER로 조건에 맞는 행이 하나도 없으면 #CALC!가 나는데, 수식이 틀린 것이 아니라 결과가 비어 있다는 뜻입니다. =FILTER(A2:C500,B2:B500="반품","없음")처럼 세 번째 인수에 결과가 없을 때 보여 줄 글자를 넣으면 해결됩니다.

이런 함수로 만든 파일을 Excel 2016이나 2019에서 열면 수식 앞에 _xlfn.이 붙고 결과가 #NAME?으로 바뀝니다. 그 버전에 없는 함수라는 표시이므로 고칠 방법은 함수를 바꾸는 것뿐입니다. 반대로 예전 버전에서 만든 파일을 Microsoft 365에서 열면 일부 수식 앞에 @가 붙어 보이는데, 예전처럼 값 하나만 돌려주라는 표시라 계산 결과는 그대로입니다. 거래처나 협력사와 파일을 주고받는다면 상대가 쓰는 엑셀 버전을 한 번 물어 두면 이런 오류를 미리 피할 수 있습니다.

엑셀 수식 오류 자주 묻는 질문 (FAQ)

Q. 수식 결과가 계속 0으로 나오는 이유는 무엇인가요?

참조 셀의 숫자가 텍스트로 저장되어 SUM이 건너뛰는 경우가 가장 흔합니다. 같은 범위에 COUNT와 COUNTA를 걸어 결과가 다르면 텍스트 숫자가 섞여 있다는 뜻입니다. 계산 옵션이 수동인지도 확인합니다.

Q. #N/A와 #VALUE!는 무엇이 다른가요?

#N/A는 찾는 값이 없다는 결과이고 수식 자체는 정상입니다. #VALUE!는 자료형이 맞지 않아 계산을 시작하지 못한 상태라 참조 셀의 텍스트와 공백을 정리해야 합니다.

Q. 다른 사람 PC에서만 #NAME?이 나는 이유는 무엇인가요?

상대 PC의 엑셀 버전에 그 함수가 없기 때문입니다. XLOOKUP, FILTER, UNIQUE, TEXTJOIN 같은 함수는 Excel 2016에 없으므로, 함께 쓰는 파일이라면 VLOOKUP, INDEX/MATCH처럼 양쪽에 다 있는 함수로 바꿉니다.

Q. 오류 셀만 빼고 합계를 구할 수 있나요?

=AGGREGATE(9,6,D2:D500)은 오류 셀을 건너뛰고 합계를 냅니다. 다만 건너뛴 셀만큼 합계가 작아지므로, 원인을 고치기 전 임시로 상황을 볼 때만 씁니다.

inline AI로 수식 오류 정리하기

inline AI는 원가 분석 파일을 연 채 "모든 시트의 오류를 원인별로 묶고, 분명한 것은 고쳐 줘"라고 말하면, 시트를 훑어 오류를 #REF!, #VALUE!, #DIV/0!처럼 원인별로 정리하고 고친 셀과 합계 변화를 함께 보여 줍니다. ARP를 함께 쓰면 원가가 생산과 구매 기록으로 바로 계산되어, 여러 사람이 고쳐 쓰는 원가 파일 자체가 필요 없어집니다.