[엑셀] 엑셀에서 자주 발생하는 오류(#VALUE!, #REF!)

'#N/A', '#NAME?', '#DIV/0!'에 이어 엑셀에서 자주 발생하는 오류인 '#VALUE!'와 '#REF!'의 발생원인과 해결방법에 대해 알아보겠습니다.

 

#VALUE! : 수식 입력에 오류가 있거나 참조하는 셀이 잘못되었을 겨우 발생하는 오류

아마 엑셀 오류 중에 제일 애매한 오류이지 않을까 싶습니다. 다른 오류들은 확실히 발생하는 상황이 한정적인데 #VALUE! 오류는 어떻게 딱 정의하기가 어려운 오류인 것 같습니다. 일반적으로 수식에 숫자만 포함이 되어야 하는데 문자가 있을 경우 주로 발생을 하지만 이 외에도 여러 상황에서 발생을 합니다. 대표적인 상황 두 가지만 알아보도록 하겠습니다.

 

각 물건별 단가와 판매량을 곱하여 판매금액이 나오도록 수식을 걸었습니다. C열의 숫자와 D열의 숫자를 곱한 값이 E열에 나오는데 5행의 경우에는 D5셀에 숫자가 아닌 문자(이십개)가 입력되어 있습니다. '숫자×문자'는 성립할 수 없으므로 E5열에 '#VALUE!' 오류가 나타납니다. 이런 경우에는 D5셀에 '이십개'라는 문자가 아닌 '20'이라는 숫자를 입력해 주면 오류가 해결됩니다.

 

#VALUE! 오류가 뜨면 각종 연산에 숫자만 들어가야 하는 상황에 문자가 들어가 있는지 확인해보셔야 합니다.

 

이런 표를 만든다고 가정해 보겠습니다. D열에는 MID 함수를 이용하여 생년월일에서 '태어난 달'만 뽑아내려고 합니다. 사용된 MID 함수를 해석해 보면 'C3'셀의 '3'번째 문자열부터 '2'글자를 따오는 함수입니다. 즉 2번째, 3번째 인수에는 반드시 숫자가 들어가야 하는 함수입니다.

 

2번째, 3번째 인수에는 숫자가 들어가야 하지만 D5셀의 경우에는 2번째 인수에 "삼"이라는 문자가 들어가 있습니다. 이런 경우에도 #VALUE! 오류가 발생합니다. 이렇게 수식에 숫자가 들어가야 하는 자리에 문자가 들어가 있는지 확인한 다음 수정해 주시면 됩니다. 위와 같은 간단한 표에서는 오류를 금방 찾을 수 있지만 수식이 복잡한 경우에는 #VALUE! 오류를 찾기 어려울 수 있으므로 꼼꼼하게 수식을 점검해봐야 합니다.

 

#REF! : 참조하는 셀이 삭제되거나 존재하지 않아 참조할 수 없을 때 발생하는 오류

실무에서 정말 많이 사용하는 함수가 VLOOKUP 함수이고 그 VLOOKUP 함수에서 정말 많이 발생하는 오류가 '#REF! '입니다. Reference(참조) 단어의 앞자리를 사용한 오류명으로 단어 의미처럼 참조하고자 하는 셀에서 값을 참조할 수 없을 때 발생합니다. 기존에 사수가 만들어 놓은 엑셀 자료를 대충 짜깁기만 해서 손 안 대고 코 풀려고 할 때 자주 발생합니다.

 

I2 셀의 수식을 보면 선수의 어시스트 수 값을 나타내도록 수식을 걸려있습니다. A2~F8셀의 첫 번째 열에서 값을 찾고 그 값이 속한 행의 6번째 값을 나타내는 표입니다. 기존에는 이런 표를 사용하다가 더 이상 선수들의 '소속' 데이터가 필요가 없다고 가정하고 B열을 지워보겠습니다.

 

수식은 그대로인데 B열(소속)을 삭제했습니다. 수식은 A2~E8셀의 첫 열에서 값을 찾고 해당 값이 있는 행의 6번째 열의 값을 나타내는 수식인데 A2~E8 범위에서는 열이 5개(A, B, C, D, E)이므로 6번째 열이 없습니다. 즉 6번째 열의 값을 가져오고 싶어도 없어서 못 가져오는 상황입니다. 이럴 때는 VLOOKUP 함수의 3번째 인수를 수정해 주면 오류가 해결됩니다.

 

함수의 세 번째 인수를 '6'에서 '5'로 바꿔 오류가 해결된 것을 확인할 수 있습니다. 실무에서 정말 많이 쓰는 함수가 VLOOKUP 같은 참조 함수인데 이런 수식이 많이 들어가 있는 표를 자르고 붙여 넣는 등 여러 수정 과정을 거치면 자주 발생하는 오류입니다. 발생하는 원인을 숙지하신다면 오류 해결도 금방 하실 수 있습니다.