엑셀에서 함수나 수식을 입력하다 보면 가끔 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #SPILL!, #CALC! 와 같이 "#"이 붙으면서 이상한 값이 표시되는 경우가 있습니다.
내가 입력한 수식에 문제가 있다는 뜻입이다. 각각의 오류를 살펴보고 어떻게 해결해야 하는지 알아보겠습니다.
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(E19,$B$18:$C$22,2,FALSE)
왼쪽의 사번/이름 테이블에 "1007"에 해당하는 사번이 존재하지 않으므로 값을 찾지 못하고 #N/A오류가 표시됩니다.
#N/A오류가 표시되면 제대로 찾을 수 있도록 참조 범위를 바꿔주거나, 아래와 같이 IFNA함수를 이용하여 처리합니다.
=IFNA(VLOOKUP(E27,$B$26:$C$30,2,FALSE),"찾는 값이 없음")
3. #NAME?
#NAME? 오류는 사용자가 입력한 '이름'이 인식이 안될 때 발생합니다.
구체적으로 다음의 경우에 발생합니다.
- 함수의 이름을 잘못 입력했을 때
- 참조영역의 이름을 잘못 입력했을 때
- 이름정의에 없는 이름을 입력했을 때
- 텍스트 값을 입력할 때 쌍따옴표를 누락(쌍따옴표가 없으므로 참조영역 또는 이름정의에 있는 이름을 입력한 것으로 인식하는데 입력한 이름에 해당하는 것이 없으므로 이름이 잘못 입력되었다고 판단함)했을 때
1) 함수의 이름을 잘못 입력했을 때
=SUMX(C36:C38)
SUMX는 엑셀에는 없는 함수입니다.
2) 참조영역의 이름을 잘못 입력했을 때
=SUM(C43:AAA)
AAA는 참조할 수 있는 셀 주소가 아니라서 오류가 발생했습니다.
#NAME? 오류 해결방법 :
- 함수이름이 정확한지
- 참조하는 셀 주소가 정확한지
- 입력한 이름이 있는 지 (Ctrl+F3를 눌러서[이름관리자]에 있는지 확인)
- 텍스트를 입력할 때 쌍따옴표를 빼 먹었는지 확인해 봅니다.
4. #NULL!
NULL은 값이 없는 상태, 알 수 없는 상태를 의미합니다.
엑셀에서는 실제로 교차하지 않는 두 범위를 교차범위로 입력할 때 발생합니다.
=SUM(C52 C54)
엑셀함수에서 참조범위 사이에 공백을 넣으면 교차범위로 인식을 하는데 위의 참조범위 [C52]와 [C54]는 교차하지 않으므로 #NULL! 오류가 발생합니다.
#NULL! 오류 해결방법 :
- 위의 경우는 원래 =SUM(C52:C54)인데 중간에 :(콜론)을 빼먹어서 오류가 발생했습니다. 공백 대신에 :(콜론)을 넣으면 해결됩니다.
- 그러나 실제로 교차하는 범위에 있는 값을 SUM하는 경우였다면 범위가 교차하도록 범위를 수정해 주면 해결됩니다.
5. #NUM!
NUM은 NUMBER를 줄여서 쓴 것입니다.
함수나 수식에 잘못된 숫자 값을 입력하거나(해당 수식에 계산이 불가능한 숫자 값을 입력) 계산의 결과가 엑셀이 처리할 수 있는 숫자보다 크거나 작을 때 발생합니다.
1) 함수나 수식에 잘못된 숫자 값을 입력할 때
=SQRT(-4)
음수는 제곱근을 구하는 인수로 사용될 수 없습니다.
2) 함수나 수식이 너무 크거나 작은 값은 숫자 값을 반환할 때
=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!
REF는 참조를 뜻하는 Reference를 줄여서 쓴 것입니다.
이 오류는 함수나 수식에서 참조가 잘못된 경우에 발생합니다.
아래의 두가지 예를 보고 확인해 보겠습니다.
1) 함수나 수식에서 참조가 잘못된 경우(원래 있던 셀을 삭제)
B71셀을 삭제하기 전에는 수식이 정상으로 표시됩니다.
B71셀을 삭제해 보겠습니다. 원래 있던 셀을 삭제하면 참조할 수 있는 셀이 사라지므로 오류가 발생합니다.
2) 존재하지 않는 시트의 이름을 참조하거나 원래 있던 시트를 삭제
[Sheet1]시트를 삭제하기 전에는 수식이 정상으로 표시됩니다.
[Sheet1] 시트를 삭제합니다. 참조할 수 있는 시트가 사라지므로 오류가 발생합니다.
#REF! 오류 해결방법 :
- 참조범위를 다시 설정
- SUM함수와 같이 셀범위를 지정할 수 있는 수식은 =SUM(B71,C71)의 형태로 입력하지 말고 =SUM(B71:C71)의 형태로 입력합니다. 이렇게 입력하면 [B71] 셀을 삭제해도 수식은 자동으로 =SUM(C71:C71)로 바뀌고 오류가 발생하지 않습니다.
7. #VALUE!
함수나 수식에서 참조하는 값의 유형이 잘못된 경우에 발생합니다. 숫자만 입력되어야 되는데 문자가 입력된 경우에 많이 발생합니다.
아래 그림의 경우 판매실적에 숫자만 입력되어야 하는데 실수로 문자 값인 "AAA"가 입력되어 이익율이 제대로 표시되지 않고 #VALUE! 오류가 발생했습니다.
문자 값을 숫자로 바꾸면 이익율이 정상으로 표시됩니다.
8. #SPILL!
SPILL은 엎지르다, 쏟아붓다 등을 의미합니다. 엑셀에서는 범위에 결과를 쏟아붓다, 유출하다 정도로 해석할 수 있는데 정확히는 결과를 동적 배열의 형태로 가져오는 것을 말합니다.
1) 동적배열 수식으로 자료 참조
아래와 같이 거래처별 판매실적 자료가 있을 때 [B15] 셀에 =B5:E7 이라는 수식을 입력하면 판매실적 자료를 참조하여 결과를 배열형태로 가져옵니다.
(이 기능은 Office 365 Excel, Excel 2021, 웹용 Excel, 모바일용 Excel등에서만 제한적으로 제공됩니다).
그런데 결과를 가져올 범위에 이미 다른 값이 존재한다면 결과를 가지오지 못하고 다음과 같이 #SPILL! 오류가 발생합니다. 동적배열 형태로 결과를 가져올 때는 가져올 범위가 비어 있는 지 미리 확인하는 것이 중요합니다.
2) FILTER함수로 자료 가져오기
아래의 예는 FILTER함수를 이용하여 판매실적에서 '노트'인 것만 찾아서 결과를 배열형태로 가져옵니다.
=FILTER($B$5:$E$12,($D$5:$D$12="노트"),"찾는 자료가 없음")
(FILTER함수는 Office 365 Excel, Excel 2021, 웹용 Excel, 모바일용 Excel등에서만 제한적으로 제공됩니다).
첫번째 경우와 마찬가지로, 만약 결과를 가져올 범위에 이미 다른 값이 존재한다면 결과를 가지오지 못하고 다음과 같이 #SPILL! 오류가 발생합니다.
※ FILTER함수 사용법은 다음 링크를 참조하세요.
[ 엑셀함수 FILTER - 원하는 조건으로 데이터 조회(필터링)하기 ]
8. #CALC!
CALC는 계산을 뜻하는 Calculation을 줄여서 쓴 것입니다.
#CALC! 오류는 동적배열 함수가 결과를 반환할 수 없을 때 발생합니다.
아래의 예는 FILTER함수를 이용하여 판매실적에서 상품이 '망치'인 것만 찾아서 결과를 배열형태로 가져오고 만약 찾는 자료가 없다면 '찾는 자료가 없음'으로 표시해 줍니다.
=FILTER($B$5:$E$12,($D$5:$D$12="망치"),"찾는 자료가 없음")
판매실적 중에 상품이 '망치'인 것은 없으므로 결과가 '찾는 자료가 없음'으로 표시되었습니다.
만약 실수로 다음과 같이 FILTER함수의 마지막 인수(찾는 값이 없을 때 표시할 값)를 빼 먹었다면 #CALC! 오류가 발생합니다.
=FILTER($B$5:$E$12,($D$5:$D$12="망치"))
#CALC! 오류는 동적배열 함수가 결과를 반환할 수 없을 때 발생합니다.
따라서 수식을 다음과 같이 작성하면 '망치'가 없어도 '찾는 자료가 없음'이라는 텍스트를 반환하지만 마지막 인수를 빼 먹으면 아예 반환할 값이 없으므로 #CALC! 오류가 발생한 것입니다.
=FILTER($B$5:$E$12,($D$5:$D$12="망치"),"찾는 자료가 없음")
* 엑셀 오류처리 시 주의사항
앞서 설명한 오류를 다음과 같이 IFERROR 함수를 사용하여 보이지 않게 처리하는 방법도 있습니다.
=IFERROR(D6/C6,"")
위 수식은 오류가 발생하면 아무것도 표시하지 않습니다, 판매실적이 0이어서 아무것도 표시 안되는지 수식이 삭제되어 아무것도 표시 안되는지 알 수 없는 문제가 있습니다.
위 오류는 이 글의 첫 부분에서 설명한대로 IF 함수를 이용하여 나누는 값이 0이면 나누지 말고 0을 표시하고, 0이 아닐 때만 나누도록 수식을 =IF(C14=0,0,D14/C14)로 수정하면 됩니다.
오류를 보이지 않게 처리하는 방법은 좋지 않은 방법입니다. 오류가 보이지 않는다고 문제가 해결된 것이 아니므로, 위와 같이 오류의 원인을 찾아서 오류별로 적절한 방법을 찾아 해결하는 것이 좋습니다.
[ 시간을 아껴주는 엑셀 꿀팁 ]
좋은 팁 감사해요!!!
제 블로그 글에 링크 좀 걸어도 괜찮을까요!??
🙂
넵! 괜찮습니다.
감사합니다.
감사합니다.!!
공부 잘 했습니다~^^