[엑셀 실무] 수식에 잘못된 셀 참조가 포함된 경우, #REF! 오류

엑셀 #REF! 오류는 수식에 잘못된 셀 참조가 포함된 경우에 발생하는 오류 메시지입니다. reference 의 앞 세자리를 추출하여 얻은 에러명입니다.

엑셀 #REF! 오류 발생 원인 2가지

1) 엑셀 수식이 이전 삭제한 셀을 참조할 때
2) 수식에서 참조하는 셀 주소가 엑셀 스프레드시트 범위를 벋어날 때

이렇듯 위 두 오류의 원인을 하나로 본다면 엑셀 #REF! 오류는 그 오류명이 의미하듯 엑셀 수식에서 참조하는 셀 주소가 존재하지 않는다면 발생하는 오류입니다. 기억하십시오. 셀에 데이터 존재여부가 아니라 참조한 셀 주소의 존재여부에 따라 발생하는 오류입니다.

엑셀 #REF! 오류 발생 수식 예제

[그림1] 엑셀 #REF! 오류의 첫번째 원인 - 참조셀 삭제

오류 예제1 : D4 셀에서 E4 셀과 F4 셀의 합을 구합니다.
오류 수식1 : =E4 + F4
오류 결과1 : 30

E4 셀과 F4 셀의 합을 D4 셀에서 위의 수식으로 구합니다. E4 셀의 값, 10과 F4 셀의 값 20을 더하기에 30 결과가 오류없이 반환될 것입니다. 오류 예제라 이름 붙였지만 아직까지 이는 오류가 아닙니다. 동일한 수식의 다음 예제를 확인합니다.

오류 예제2 : 예제1의 수식과 동일한 방법으로 E5 셀의 값과 F5 셀의 값을 더합니다. 그 후  F열을 선택 후 마우스 우클릭하고 '삭제' 하거나 F5 셀을 선택 후 역시 마우스 우클릭하고 '삭제' 를 선택합니다. D5 셀의 결과는 어떻게 바뀔까요?
오류 수식2 : =E5 + #REF!
오류 결과2 : #REF!

F열을 삭제하건 F5 셀을 삭제하건 D5 셀의 수식에서 참조하고 있던 F5 셀의 주소가 삭제된 것입니다. 예제의 수식은 원래 위와 같지 않았습니다. 원래는 =E5 + F5 로 예제1과 같은 형식이었습니다. 그러나 참조하던 F5 셀의 주소가 삭제되는 순간 위와 같은 오류의 결과를 반환하게 됩니다. 참조하던 셀 주소가 사라졌기때문입니다. 물론 그 주소는 다시 생성됩니다. 하지만 수식에서 참조할 때와는 상황이 달라졌기에 발생된 오류가 자동 수정되지는 않습니다.
하지만 이 오류의 수정은 간단합니다. 이때는 D5 셀의 수식에서 오류가 발생한 셀 참조를 갱신해 주시면 오류는 간단히 해결됩니다.

[그림2] 엑셀 셀 주소 삭제하기

참고1 : 자, 여기서 한가지 참고하시기 바랍니다. 만일 F5 셀을 DEL 키를 이용하여 삭제하였다면 #REF! 오류는 발생하지 않습니다. 엑셀에서 DEL 키를 이용한 삭제는 그 셀 주소의 데이터를 삭제하는 것이기에 주소는 그대로 유효하지요. 오류가 생길 이유가 없습니다. 하지만 위 예제에서와 같이 엑셀의 삭제기능을 이용한 셀 삭제는 그 주소를 삭제하는 것입니다. 참조하던 주소가 무효해 지기에 오류가 발생하는 것입니다. DEL 키 삭제와 마우스 우클릭 삭제를 확실히 이해해 주시기 바랍니니다.

[그림3] 엑셀 #REF! 오류의 두번째 원인 - 시트 경계 초과

오류 예제3 : A2 셀에서 D2 셀의 범위에서 공백의 수를 D4 셀에 구합니다.
오류 수식3 : =COUNTBLANK(A2:D2)
오류 결과3 : 4

A2:D2 의 범위에서 공백 셀의 수를 구합니다. 엑셀 COUNTBLANK 함수는 공백의 수를 세는 함수입니다. A2:D2 범위는 모두가 공백이지요. 4가 결과로 떨어집니다. 이 역시 오류를 발생하는 수식은 아닙니다. 다음 단계가 #REF! 오류를 발생하지요.

오류 예제4 : D4 셀의 수식을 C4 셀로 수식 복사한다면 어떻게 될까요?
오류 수식4 : =COUNTBLANK(#REF!)
오류 결과4 : #REF!

D4 셀보다 열 위치가 작은 셀에 수식을 복사합니다. 예로 D4 셀의 수식을 C4 에 수식 복사하는 것입니다. D4 셀의 수식은 예제3과 같습니다. 참조 범위 A2:D2가 상대 참조되고 있습니다. D열에서 C열로 수식의 위치가 좌측 이동되었습니다. 참조 범위였던 A2:D2 도 열위치가 1씩 줄어야 합니다. 그러나 D열은 C열로 줄어들수 있지만 A열은 더이상 줄어들 셀 주소가 없습니다. 잘못된 참조가 되는 것입니다. #REF! 오류를 발생합니다.

이상의 설명은 C5 셀에 수식과 D5 셀에 결과로 보여드립니다. 이번 오류의 원인은 상대 참조되던 범위가 수식 복사로 이동되면서 무효한 주소가 전달된 것입니다. 이 두번째 #REF! 오류를 방지하기 위해서는 상대참조를 절대참조로 변환하면 됩니다.

엑셀 #REF! 오류 역시 수정이 쉬운편에 속하는 엑셀 오류입니다. 다른 오류와는 달리 이 오류는 참조하는 셀이 무효해진다면 위 예제 들에서와 같이 수식내에도 그 오류 메시지가 표시되기 때문에 인수 부분을 다시 참조해 주시면 쉽게 문제 해결 됩니다.

[엑셀 실무] 0 으로 나누어 생기는 엑셀 오류, #DIV/0!

마늘빵.