Excel #REF! 오류: 누락 합계와 조회·INDEX·INDIRECT 참조 복구하기
참조 오류를 없애는 것과 누락된 데이터를 복원하는 것은 다른 작업입니다. 10·20·30의 합계 60에서 가운데 값 20을 잃은 뒤 남은 값만 합산하면 40이 됩니다. 오류 표시는 없어져도 원래 합계는 돌아오지 않습니다. 이 글은 이런 경우를 포함해 SUM, VLOOKUP, INDEX, INDIRECT의 참조를 순서대로 확인하는 실습입니다.
첨부 practice-115.xlsx는 가상의 값과 제품 4개만 사용합니다. 원본 파일 삭제, 외부 통합문서 연결, 실제 업무 문서 수정 없이 잘못된 참조와 올바른 참조를 나란히 비교합니다.
1. 먼저 오류 셀과 그 셀이 바라보는 범위를 기록하기
#REF!는 수식이 유효하지 않은 셀을 참조할 때 나타납니다. 값을 붙여넣어 오류 표시만 가리지 말고, 사본을 저장한 뒤 수식 입력줄의 참조와 원본 범위가 실제로 존재하는지 확인합니다. 실행 취소나 백업에서 복원할 자료가 있으면 그 경로부터 검토합니다.
SUM참조 B2:D2에는 10·20·30, E2에는 =SUM(B2:D2)가 들어 있으며 결과는 60입니다. B5:C5는 삭제 뒤 남은 값 10·30을 따로 모사한 것입니다. E5의 =SUM(B5:C5)는 40, E6의 차이는 20입니다. 원래 셀을 실제로 삭제한 것이 아니므로 원본 세 값은 그대로 남아 있습니다.
2. 개별 참조가 깨졌을 때 범위만 바꿔 끝내지 않기
다음 문자열은 삭제로 참조가 깨진 모양을 설명하는 예시입니다. 첨부 B7에는 계산식이 아닌 텍스트로 저장했습니다.
=SUM(B2,#REF!,D2)이번에 사용한 LibreOffice는 이 직접 입력 오류 토큰을 다시 저장하면서 다른 오류로 바꾸는 동작을 보였습니다. 이를 정상 결과처럼 숨기지 않기 위해 실행 가능한 비교는 F2에서 별도로 합니다.
=SUM(B2,INDEX(B2:D2,1,4),D2)B2:D2는 3열인데 INDEX가 열4를 요청하므로 F2는 #REF!를 반환합니다. 이 수식은 오류가 SUM으로 전파되는 검산이며, 삭제 사건 자체를 재현했다는 뜻은 아닙니다. 복구에서는 원래 참조해야 할 값과 위치를 확인한 뒤 =SUM(B2:D2)처럼 올바른 범위를 지정합니다. 누락된 20을 모르는 상황에서 남은 10·30을 합친 40을 복구 완료로 취급하지 않습니다.
3. VLOOKUP의 열 번호와 왼쪽 조회를 구분하기
제품 A1:C5의 내용은 다음과 같습니다. 단가는 숫자로 저장돼 있습니다.
| 제품코드 | 제품명 | 단가 |
|---|---|---|
| P001 | 노트북 | 1200000 |
| P002 | 모니터 | 350000 |
| P003 | 키보드 | 89000 |
| P004 | 마우스 | 45000 |
조회!A2의 P001을 찾는 B2는 잘못된 반례, C2는 올바른 열 번호입니다.
=VLOOKUP(A2,'제품'!$A$2:$C$5,4,FALSE)
=VLOOKUP(A2,'제품'!$A$2:$C$5,3,FALSE)Excel 공식 설명에서 VLOOKUP의 반환 열 번호가 범위의 열 수보다 크면 #REF!가 됩니다. 다만 이 파일을 재계산한 LibreOfficeDev 26.8에서는 같은 B2 반례의 XLSX 계산 캐시가 #VALUE!로 저장되고, 시트 렌더에는 Err:502로 표시됐습니다. C2는 1200000으로 일치합니다. Microsoft Excel 앱은 실행하지 않았습니다. 위 문서의 설명과 다른 엔진에서 얻은 B2 계산값을 같은 앱의 실행 결과로 혼용하지 않습니다.
제품명으로 왼쪽의 제품코드를 찾는 작업은 반환 열 번호를 0이나 음수로 바꾸는 문제가 아닙니다. F2와 G2는 각각 다음 식으로 E2의 노트북을 찾아 P001을 반환합니다.
=INDEX('제품'!$A$2:$A$5,MATCH(E2,'제품'!$B$2:$B$5,0))
=XLOOKUP(E2,'제품'!$B$2:$B$5,'제품'!$A$2:$A$5,"없음")XLOOKUP이 지원되지 않는 환경에서는 F2의 INDEX/MATCH를 비교합니다. E3의 없는이름은 F3의 IFNA 처리와 G3의 지정 대체값으로 ‘없음’이 됩니다. 반면 B3의 존재하지 않는 제품코드 P999는 원시 #N/A를 남깁니다. 범위가 잘못된 것과 검색 대상이 없는 것을 구분하기 위한 비교입니다.
4. INDEX의 행·열 수를 실제 표와 맞추기
INDEX범위 B2:E5는 1~16을 행 순서대로 채운 4행×4열 표입니다. F2와 G2는 아래 두 수식을 비교합니다.
=INDEX(B2:E5,5,5)
=INDEX(B2:E5,4,4)F2는 존재하지 않는 다섯 번째 행·열을 요청해 #REF!, G2는 마지막 셀 E5의 16입니다. 이 경우 해결책은 오류를 0으로 바꾸는 것이 아니라 실제 필요한 행·열 번호가 범위 안에 있는지 확인하는 것입니다. 큰 표로 범위를 임의 확장하면 전혀 다른 값을 가져올 수도 있습니다.
5. INDIRECT의 텍스트 주소를 실제 시트와 대조하기
간접참조!A2는 텍스트 '1월'!B2이고 B2의 =INDIRECT(A2)는 실제 1월 시트 B2의 1200000을 반환합니다. A3은 존재하지 않는 '없는시트'!B2여서 B3이 #REF!입니다. INDIRECT의 텍스트가 유효한 셀 참조가 아니면 #REF!를 반환합니다.
사본에서 A3을 '1월'!B2로 고치면 B3이 1200000이 되는지 확인하고, 실습 종료 후 원래 없는시트 텍스트로 되돌립니다. 외부 통합문서를 가리키는 경우에는 별도 조건도 확인해야 합니다. INDIRECT의 외부 참조는 원본 통합문서가 열려 있어야 한다는 것이 공식 설명입니다. 이 첨부는 외부 파일을 열거나 연결하지 않습니다.
6. 복구를 끝내기 전에 확인할 경계
실제 보고서에서 참조를 고쳤다면 합계, 행 수, 조회 대상과 이전 정상 사본을 대조합니다. ‘#REF!가 안 보인다’는 사실만으로 원래 데이터가 복원됐다고 판단하지 않습니다. 원래 참조할 행을 알 수 없으면 추정 숫자를 입력하지 말고 백업·변경 이력·원장 담당자의 확인을 기다립니다.
첨부는 Python 독립 산술·범위 검사와 LibreOffice의 실제 저장 계산값을 비교했습니다. SUM 60/40/차이20, 올바른 단가1200000, INDEX 마지막값16, INDIRECT 유효/무효 참조를 구분해 기록했습니다. 의도한 오류와 VLOOKUP의 엔진 차이는 검증 기록에 남겼으며 Microsoft Excel 실행 성공으로 표시하지 않았습니다.
'1월'!B2로 바꾸면 =INDIRECT(A3)의 기대값은 1,200,000입니다. 실습이 끝나면 A3을 원래 '없는시트'!B2로 되돌려 기대 오류 #REF!를 다시 확인합니다. 실제 문서 복구를 마치기 전에는 누락값과 원래 위치, 유효한 참조, 합계·행 수·조회 대상을 정상 사본과 대조합니다. 가상 데이터로 만든 설명용 도해입니다.본문과 같은 입력으로 연습하기
실습 XLSX에는 본문에서 지정한 셀과 수식이 들어 있습니다. 검산 자료 ZIP에는 같은 XLSX와 입력·수식 CSV, 실습 설명, README 및 오프라인 Python 검산 코드가 있습니다. 원본 업무 파일을 덮어쓰지 말고 사본으로 연습하세요. Microsoft Excel 앱의 실제 실행 결과로 제공하는 파일은 아닙니다.
공식 자료
How to correct a #REF! error | Microsoft Support
INDIRECT function | Microsoft Support
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24.
작성자 소개
