지난 시간에는 여러분들이 꼭 알아두어야 하는 필수 함수부터 예시까지 설명드렸습니다. 여러 가지 함수를 사용하시다 보면 자주 나오는 오류를 보셨을 건데요. 처음엔 보자마자 거부감이 들기도 하지만, 엑셀 자체에서 원인과 해결법을 알려준다고 해도 이해하기 쉽지 않습니다. 그래서 오늘은 엑셀을 사용할 때 흔히 발생하는 오류인 #N/A, #VALUE!, #REF! 이 세 가지를 중점으로 각 발생 원인, 그리고 해결 방법까지 보도록 하겠습니다.
1. #N/A 오류: 원인과 해결법
#N/A 오류는 값을 찾을 수 없을 때 나옵니다. 영어로는 Not Available의 줄임말인데요. 여러분들이 주로 VLOOKUP, HLOOKUP, MATCH와 같은 검색 함수를 쓸 때 발생하고 엑셀에서 원하는 값을 찾지 못하면 나타납니다. 그럼 #N/A 오류가 발생하는 주요 원인과 해결 방법을 알아보겠습니다:
1) 잘못된 검색 값: 검색하려는 값이 데이터에 없으면 이 오류가 나오게 됩니다. 예를 들어, "=VLOOKUP(101, A1:B10, 2, FALSE)"에서 "101"이 A1:A10에 없으면 #N/A 오류가 발생합니다. 이렇게 말로 풀어서 설명하니 조금 이해가 가시죠?
해결법: 검색 값을 확인하고, 데이터가 정확히 입력되었는지 확인하는 과정을 한 번씩 거쳐줘야 합니다. 즉 여러분들이 찾고자 하는 데이터가 실제로 다른 시트나 셀에 입력되어 있는지를 보셔야 합니다. 이 방법으로 해결되지 않는다면 데이터를 오름차순으로 정렬하거나, 유사한 값까지 같이 허용하도록 VLOOKUP의 마지막 인수를 TRUE로 설정해 봅시다.
2) 공백이나 오타: 데이터에 불필요한 공백이나 오타가 있으면 값이 일치하지 않아 오류가 발생합니다. 예를 들어, "Home" 대신 "Home "로 마지막에 공백이 하나 있어도 오류가 나올 수 있습니다. 우리 눈에는 별 거 아닌거 같아 보여도 컴퓨터 입장에서는 굉장히 불편한 사항일 수 있습니다.
해결법: TRIM 함수를 사용해 우리가 미처 보지 못한 데이터 공백을 제거해 봅시다. "=TRIM(A1)"을 사용해 불필요한 공백을 제거해보세요.
3) 범위 문제: 검색 범위가 잘못 지정되었을 때 발생합니다. 예를 들어, VLOOKUP에서 범위를 "A1:B5"로 지정했지만 실제 데이터는 "A1:B10"에 있다면 컴퓨터에서 그 데이터를 찾지 못하게 되는 것이니까, 오류가 발생하게 됩니다.
해결법: VLOOKUP이나 MATCH 함수에서 실제로 일치하는 범위를 지정했는지 확인하고, 필요한 경우 범위를 확장하면 됩니다. F2를 누르면 쉽게 범위를 볼 수 있다는 것 참고하시고요.
#N/A 오류는 데이터를 정확히 입력하고, 검색 조건을 명확히 설정하기만 해도 쉽게 해결할 수 있습니다. 특히 VLOOKUP이나 MATCH 함수의 입력값을 꼼꼼히 검토하는 습관을 길러봅시다.
2. #VALUE! 오류: 원인과 해결법
#VALUE! 오류는 "잘못된 데이터의 유형"으로 인해 발생합니다. 숫자가 필요한 곳인데 텍스트가 입력되었거나, 함수의 인수가 잘못되면 나타나게 됩니다. 그럼 주요 원인과 해결법 한 번 익혀보겠습니다:
1) 숫자와 텍스트 혼합: 숫자 계산을 해야 하는 셀에 텍스트가 포함되어 있을 경우 오류가 납니다. 예를 들어, "=A1+B1"에서 A1이 "100"이라는 텍스트로 지정됐다면 잘못된 것입니다.
해결법: 숫자가 텍스트로 저장된 경우에는 VALUE 함수를 사용해서 바로 숫자 변환 시킬 수 있습니다. 예를 들어, "=VALUE(A1)"을 사용해 "100"을 숫자로 변환할 수 있습니다. 아니면 사용자 지정 서식을 선택해 직접 바꿔줄 수도 있습니다.
2) 잘못된 인수: 함수에 올바르지 않은 함수 인수를 적게 되면 발생합니다. 예를 들어서 텍스트 인수를 이용하는 MID 함수에 텍스트 대신 숫자를 입력하면 바로 오류가 나타납니다.
해결법: 함수의 인수를 올바르게 설정하고, 필요시 인수를 변환합니다. 텍스트가 필요한 곳에 숫자가 입력되었는지 꼼꼼하게 확인해 보세요.
3) 빈 셀: 계산에 포함된 셀이 비어 있을 경우 나타날 수 있습니다. 예를 들어 볼까요, "=A1/B1"에서 B1이 비어 있으면 오류가 나타납니다.
해결법: 셀이 비어 있는지 먼저 확인해 보고 기본값을 설정하거나, IFERROR 함수로 오류를 처리해 봅니다. 예를 들어, "=IFERROR(A1/B1, 0)"을 사용해 오류 대신 0이 나온 거면 오류가 발생했구나 하고 알 수 있습니다.
#VALUE! 오류는 일단 데이터 유형을 정확히 구분하고, 함수의 인수를 올바르게 설정하면 방지할 수 있습니다. 여기서 또 새로운 함수 하나 배웠죠? 함수를 사용하는 과정에서는 항상 세심한 주의가 필요합니다.
3. #REF! 오류: 원인과 해결법
#REF! 오류는 "참조가 잘못됨(Reference Error)"이라는 뜻입니다. 주로 참조된 셀이 삭제되었거나 유효하지 않은 참조를 사용할 때 발생합니다. 역시 예시를 바로 보는 게 좋겠습니다:
1) 삭제된 셀 참조: 기존에 참조하던 셀이나 범위를 삭제했을 때 발생합니다. 데이터를 수정하다 보면 나도 모르게 함수에 포함되어 있는 값들을 삭제할 때가 있습니다. 예시로 "=A1+B1"에서 B1을 삭제하면 오류가 나타납니다.
해결법: 삭제된 셀을 복구하거나, 수식을 수정해 올바른 참조를 지정합니다. 수식을 작성할 때 셀이 삭제될 가능성을 고려해 INDIRECT 함수를 사용하는 것도 방법입니다.
2) 잘못된 범위 이동: 여러분들이 데이터를 복사하거나 이동하는 과정에서 참조 자체가 손상되면 오류가 나타나기도 합니다. 예를 들어, "=SUM(A1:A10)"을 다른 위치로 이동했는데, 실제로는 데이터가 이동되지 않았다면 오류가 나타날 수 있습니다.
해결법: 데이터와 참조가 일치하도록 수정할 수도 있고, 상대 참조 대신 절대 참조(예: $A$1:$A$10)를 사용해 참조 오류를 미리 방지하면 됩니다.
3) 잘못된 이름 정의: 이름 관리(Name Manager라고도 합니다)에서 잘못된 범위가 설정되었을 때 발생합니다. 예를 들어, "매출"이라는 이름이 "B1:B10"을 참조하도록 설정되었지만, 해당 범위를 모르고 삭제한 거면 오류가 나타납니다.
해결법: 이름 관리에서 정의된 범위를 마지막으로 확인하고, 맞는 범위를 다시 설정하면 끝입니다. 애초에 이름을 정의할 때는 고정된 데이터 범위를 사용하는 것이 좋습니다.
#REF! 오류는 수식 작성 시 참조를 정확하게 설정하고, 데이터를 삭제하거나 이동할 때 항상 지정해 놨던 함수 요소들을 확인하면 웬만한 오류는 방지할 수 있습니다.
결론
이번에는 엑셀 작업 중 발생하는 #N/A, #VALUE!, #REF! 오류들의 원인과 해결 방법을 알아봤습니다. 중간중간 오류를 해결할 추가 함수들도 적어놨으니 잊지 말고 기억해 두시면, 꼭 쓸 곳이 있을 겁니다. 보통 데이터를 정확히 입력하고, 수식을 꼼꼼하게 확인만 해도 쉽게 해결할 수 있습니다. 오류 발생 시 당황하지 마시고, 이번 글에서 소개한 해결법들을 활용해 문제를 해결해 보세요. 나중에는 오류가 나와도 당황하지 않는 엑셀 고수가 되어 있으실 겁니다. 감사합니다.