엑셀 오류표시(#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!)와 해결방법

공개됨 글쓴이 Admin댓글 한 개

엑셀에서 함수나 수식을 입력하다 보면 가끔 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE! 와 같이  “#”이 붙으면서 이상한 값이 표시되는 경우가 있다.

이것은 내가 입력한 수식에 문제가 있다는 뜻이다. 각각의 오류를 살펴보고 어떻게 해결해야 하는지 알아보자.

눈으로만 보면 이해가 어려울 수 있으니 아래의 실습용 파일을 다운로드 받아서 따라해 보자.


1. #DIV/0!

#DIV/0! 의 DIV는 Divide(나누다)를 줄여쓴 것이다.
숫자는 0(분모)으로 나눌 수 없는데 아래와 같이 0또는 빈값으로 나누는 수식(분모가 0이 되는 경우)을 입력해서 오류가 발생한 것이다.

 

이때는 IF함수를 이용하여 나누는 값이 0이면 나누지 말고 0을 표시하고 0이 아닐때만 나누도록 다음처럼 수식을 수정한다.

=IF(C10=0,0,D10/C10)

 

2. #N/A

N/A는 영어로 Not Available, No Answer등을 의미한다. “해당사항 없음”, “값 없음”정도로 해석할 수 있다.
엑셀에서는 VLOOKUP, MATCH등 찾기 함수를 사용했을 때  찾는 값이 없으면 #N/A가 표시된다.

=VLOOKUP(E18,$B$18:$C$22,2,FALSE)

왼쪽의 사번/이름 테이블에 “1007”에 해당하는 사번이 존재하지 않으므로 값을 찾지 못하고 #N/A오류가 표시된다.

#N/A오류가 표시되면 제대로 찾을 수 있도록 참조 범위를 바꿔주거나, 아래와 같이 IFNA함수를 이용하여 처리한다.

=IFNA(VLOOKUP(E26,$B$18:$C$22,2,FALSE),”찾는 값이 없음”)

 

3. #NAME?

#NAME? 오류는 사용자가 입력한 이름이 인식이 안될 때 발생한다.

구체적으로 다음의 경우에 발생한다.

  • 함수의 이름을 잘못 입력했을 때
  • 참조영역의 이름을 잘못 입력했을 때
  • 텍스트 값을 입력할때 쌍따옴표를 누락(쌍따옴표가 없으므로 참조영역을 입력한 것으로 인식하는데 입력한 이름에 해당하는 참조영역이 없으므로 이름이 잘못입력되었다고 인식함)했을 때

 

1) 함수의 이름을 잘못 입력했을 때 예시

=SUMX(C36:C38)

SUMX는 존재하지 않는 함수이다.

 

2) 참조범위 이름을 잘못 입력했을 때 예시

=SUM(C43:AAA)

AAA는 참조할 수 있는 범위가 아니라서 오류가 발생했다.

 

#NAME? 오류 해결방법 :

  • 함수이름이 정확한지
  • 참조영역이 정확한지
  • 텍스트를 입력할때 쌍따옴표를 빼 먹었는지 확인해 본다.

 

4. #NULL!

NULL은 값이 없는 상태, 알수 없는 상태를 의미한다.
엑셀에서는 실제로 교차하지 않는 두 범위를 교차범위라고 입력할 때 발생한다.

=SUM(C52 C54)
엑셀함수에서 참조범위 사이에 공백을 넣으면 교차범위로 인식을 하는데 위의 참조범위 C52와 C54는 교차하지 않으므로 #NULL! 오류가 발생한다.

 

#NULL! 오류 해결방법 :

  • 위의 경우는 원래 =SUM(C52:C54)인데 중간에 “:”을 빼먹어서 오류가 발생했다. 공백 대신에 “:”을 넣으면 해결된다.
  • 그러나 실제로 교차하는 범위에 있는 값을 SUM하는 경우였다면 범위가 교차하도록 범위를 수정해 주면 해결된다.

 

5. #NUM!

오류표시 #NUM!에서NUM은 NUMBER를 줄여서 쓴 것이다.

함수나 수식에 잘못된 숫자 값을 입력하거나(해당 수식에 계산이 불가능한 숫자값을 입력) 계산의 결과가 엑셀이 처리할 수 있는 숫자보다 크거나 작을 때 발생한다.

1) 함수나 수식에 잘못된 숫자값을 입력할 때

=SQRT(-4)

음수는 제곱근을 구하는 인수로 사용될 수 없다

 

1) ‘함수나 수식이 너무 크거나 작은 값은 숫자값을 반환할 때

=1000^1000

1000의 1000승은 엑셀이 처리할 수 있는 숫자범위를 벗어나므로 #NUM!오류가 발생한다.

 

*엑셀이 처리할 수 있는 숫자 범위는 다음 링크를 참조한다.
https://support.office.com/ko-kr/article/excel-%EC%82%AC%EC%96%91-%EB%B0%8F-%EC%A0%9C%ED%95%9C-1672b34d-7043-467e-8e27-269d656771c3

 

6. #REF!

함수나 수식에서 다음처럼 참조가 잘못된 경우에 발생한다.

아래의 두가지 예를 보고 확인해 보자.

1) 함수나 수식에서 참조가 잘못된 경우(원래 있던 셀을 삭제)

B71셀을 삭제하기 전에는 수식이 정상으로 표시된다. 

 

B71셀을 삭제해 보자. 원래 있던 B71셀을 삭제하면 참조할 수 있는 셀이 사라지므로 오류가 발생한다.

 

 

2) 존재하지 않는 시트의 이름을 참조하거나 원래 있던 시트를 삭제

“Sheet1″시트를 삭제하기 전에는 수식이 정상으로 표시된다.

 

“Sheet1″을 삭제해 보자. 원래 있던 Sheet1을 삭제하면 참조할 수 있는 셀이 사라지므로 오류가 발생한다.

 

#REF! 오류 해결방법 :

  • 참조범위를 다시 설정
  • SUM함수와 같이 셀범위를 지정할 수 있는 수식은 =SUM(B71,C71)의 형태로 입력하지 말고 =SUM(B71:C71)의 형태로 입력한다. 이렇게 입력하면 B71셀을 삭제해도 수식은 자동으로 =SUM(C71:C71)로 바뀌고 오류가 발생하지 않는다.

 

7. #VALUE!

함수나 수식에서 참조하는 값의 유형이 잘못된 경우에 발생한다. 숫자만 입력되어야 되는데 문자가 입력된 경우에 많이 발생한다.

아래 그림의 경우 판매실적에 숫자만 입력되어야 하는데 실수로 문자값인 “AAA”가 입력되어 이익율이 제대로 표시되지 않고 #VALUE! 오류가 발생했다.

문자값을 숫자로 바꾸면 이익율이 정상으로 표시될 것이다.

 

* 엑셀 오류처리 시 주의사항

지금까지 엑셀의 오류유형과 오류처리 방법을 알아보았다.

그런데 오류처리시 오류가 발생하면 값이 보이지 않게 처리하는 경우가 있는데 좋지 못한 방법이다. 오류가 나면 원인이 있는데 오류가 발생하지 않은 것처럼 보이기 때문에 문제가 숨어 있고 나오지 않는 것이다.

나중에 문제가 될 수 있으므로 가능하면 오류가 표시되도록 하고 원인을 찾아서 각 오류별로 적절한 방법을 찾아 해결하는 것이 좋다.

 

[ 시간을 아껴주는 엑셀 꿀팁 ]

엑셀 오류표시(#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!)와 해결방법에 1개의 응답

댓글 남기기

이메일은 공개되지 않습니다.