직장에서 엑셀 보고자료 작성 중 간혹 엑셀 오류 또는 엑셀 오류값 표기로 인해 난감할 때가 있다.
공들여 만든 엑셀자료에 엑셀 오류값이 군데군데 섞여 모르고 지나칠때가 있다. 엑셀 수식 오류 등 다양한 원인으로 엑셀 오류값(#N/A, #VALUE!, #DIV/0!, #NULL!)이 표기되는데 수식 적용시 오류값 숨기기가 가능하며 엑셀 오류에 음영 등 색인하여 찾아 해결할 수도 있다.
먼저, 엑셀 오류 각 유형과 원인에 대해 살펴보고 해결방법을 알아보자.
엑셀 오류 또는 엑셀 오류값은 #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE! 등의 값이 표시되며 오류값 찾기와 해결방법도 물론 가능하다.
##### 엑셀 오류값
Excel에서 열 너비가 좁아 셀의 일부 문자를 표시할 수 없거나 셀에 음수로 된 날짜 또는 시간 값이 포함된 경우 표시되는 오류값. 예를 들어 =06/15/2008-07/01/2008과 같이 과거의 날짜에서 미래의 날짜를 빼는 수식은 음수 날짜 값을 반환
○ 오류 수정(해결방법)
① 전체 텍스트가 표시되도록 열너비를 늘림
- 열 머리글을 클릭하여 열을 선택 후 홈 탭의 셀 그룹에서 서식을 클릭
- 홈 탭의 셀 그룹 셀 크기 아래에서 열너비 자동맞춤을 클릭
② 내용 텍스트가 열너비에 맞도록 텍스트 크기를 줄임
- 열 머리글을 클릭하여 열을 선택 후 홈 탭의 맞춤 그룹에서 맞춤 옆에 있는 대화상자 표시 아이콘 단추모양을 클릭
- 홈 탭의 맞춤 그룹 텍스트 조정 아래에서 셀에 맞춤 확인란을 선택
③ 다른 숫자 서식 또는 날짜 서식 적용
- 홈 탭의 표시 형식 그룹에서 표시 형식 옆에 있는 대화상자 표시 아이콘 단추모양을 클릭
- 홈 탭의 숫자 그룹 범주 상자에서 날짜 또는 시간 서식이 아닌 숫자 서식을 선택
#DIV/0! 엑셀 오류값
Excel에서 값이 포함되지 않은 셀이나 영(0)으로 숫자를 나누면 표시되는 오류값
○ 오류 수정(해결방법)
① 함수 또는 수식에서 나누는 수가 영(0)이거나 비어 있으면 안 되며 영이나 빈 값이 포함되지 않은 다른 셀을 참조하도록 수식의 셀 참조를 변경
② IF함수로 오류값 숨기기, 0 또는 임의의 문자열로 결과표시 가능하다. 예를 들어 오류를 발생시키는 수식이 =A1/A2이면 =IF(A2=0,"",A1/A2)를 사용하여 빈 문자열을 반환하거나 =IF(A2=0,0,A1/A2)를 사용하여 0을 반환한다.
#N/A 엑셀 오류값
Excel에서 함수나 수식에 값을 사용할 수 없는 경우 표시되는 오류값
○ 오류 수정(해결방법)
① 셀에 #N/A를 직접 입력한 경우 실제 데이터를 사용할 수 있으면 #N/A를 해당 데이터로 바꾼다.
예를 들어 데이터를 아직 사용할 수 없는 셀에 #N/A를 입력한 경우 해당 셀을 참조하는 수식에서는 값을 계산하려고 하는 대신 #N/A를 반환하여 해당 수식이 포함된 셀의 오류가 해결
② HLOOKUP, LOOKUP, MATCH 또는 VLOOKUP함수에 입력한 lookup_value 인수가 올바른 형식의 값인지 확인
예를 들어 범위 참조 대신 값 또는 셀 참조를 입력했는지 확인한다.
③ 기본적으로 표에서 정보를 찾는 함수는 오름차순으로 정렬되어야 하지만 VLOOKUP과 HLOOKUP 워크시트 함수에는 표가 정렬되지 않았어도 단어 단위로 정확하게 일치하는 내용을 찾도록 지시하는 range_lookup 인수가 있다. 단어 단위로 정확하게 일치하는 내용을 찾으려면 range_lookup 인수를 FALSE로 설정한다.
④ MATCH함수에는 일치하는 내용을 찾기 위해 목록의 정렬순서를 지정하는 match_type 인수가 일치하는 내용을 찾지 못하면 match_type 인수를 바꿔 보아야 하며 단어 단위로 정확하게 일치하는 내용을 찾으려면 match_type 인수를 0으로 설정한다.
⑤ 오류를 반환하는 함수에 필요한 인수를 모두 입력
⑥ 함수가 있는 통합 문서가 열려 있고 함수가 제대로 실행되는지 확인
⑦ 함수의 인수가 정확한지 및 올바른 위치에서 사용되고 있는지 확인
#NAME? 엑셀 오류값
Excel에서 수식의 텍스트를 인식할 수 없는 경우 표시되는 오류값. 예를 들어 범위 이름이나 함수 이름을 잘못 입력한 경우가 여기에 해당됨
○ 오류 해결방법은 아래 사항이 존재하는지 확인 후 수정
- 유로화 도구 추가 기능을 로드하지 않고 수식에서 EUROCONVERT 함수를 사용하는 경우
- 수식에서 없는 이름을 참조하는 경우
- 수식에서 맞춤법이 잘못된 이름을 참조하는 경우
- 수식에 사용된 함수 이름의 철자가 틀린 경우
- 텍스트를 큰따옴표로 묶지 않고 수식에 텍스트를 입력한 경우
- 범위 참조에서 콜론(:)이 생략된 경우
- 작은따옴표(')로 묶지 않은 다른 시트를 참조하는 경우
- 통합 문서에서 사용할 수 없는 UDF(사용자 정의 함수)를 호출하는 경우
#NULL! 엑셀 오류값
Excel에서 교차하지 않는 두 영역의 논리곱을 지정한 경우 표시되는 오류값
논리곱 연산자는 수식에서 참조를 구분하는 공백 문자로 예를 들어 영역 A1:A2와 C3:C5는 교차하지 않으므로 수식 =SUM(A1:A2 C3:C5)를 입력하면 #NULL! 오류가 반환됨
○ 오류 수정(해결방법)
① 잘못된 범위 연산자가 사용된 경우 올바른 범위 연산자로
- 연속된 셀 범위를 나타내려면 범위의 첫 셀과 마지막 셀에 대한 참조를 콜론(:)으로 구분. 예를 들어 SUM(A1:A10)은 A1 셀부터 A10 셀까지의 범위를 나타냅니다.
- 교차되지 않는 두 영역을 참조하려면 통합 연산자인 쉼표(,)를 사용합니다. 예를 들어 수식에서 두 범위의 합을
구할 때는 쉼표로 두 범위를 구분합니다(SUM(A1:A10,C1:C10)).
② 수식에서 지정한 범위가 교차되지 않는 경우 범위가 교차하도록 참조를 변경.
교차점은 워크시트에서 둘 이상의 범위가 "교차"하는 점으로 교차하는 범위가 포함된 수식의 예로 =CELL("address",(A1:A5 A3:C3))을 들 수 있는데 이 수식에서 CELL 함수는 두 범위가 교차하는 셀 주소 A3을 반환한다. 수식을 입력하거나 편집하면 해당 셀 주위의 경계선과 셀 참조가 색으로 구분됨
#NUM! 엑셀 오류값
Excel에서 수식이나 함수에 잘못된 숫자 값이 포함되어 있으면 표시되는 오류값
① 숫자 인수가 필요한 함수에 잘못된 데이터 형식을 제공한 경우
- 함수에 사용되는 인수는 숫자여야 함. 예를 들어 입력하려는 값이 1,000,000원이라도 수식에는 1000000을 입력
② 수식에서 IRR 또는 RATE와 같이 반복계산을 수행하는 함수를 사용하지만 해당 함수를 통해 결과를 찾을 수 없다.
- 함수의 시작 값을 다르게 지정
- 수식 반복 계산 횟수를 변경
> 파일 탭 클릭 -> 옵션 클릭 ->수식 범주 클릭
> 계산 옵션 아래에서 반복 계산 사용 확인란 선택
> 반복 계산할 최대 횟수를 설정하려면 최대 반복 횟수 상자에 반복 횟수를 입력
> 계산 결과 사이에 허용할 변화 한도를 설정하려면 변화 한도값 상자에 값을 입력
③ 엑셀에서 표시할 수 없는 너무 크거나 작은 숫자가 수식의 결과로 반환되는 경우
- 결과가 -1*10307과 1*10307 사이의 값이 되도록 수식을 변경
#REF! 엑셀 오류값
셀 참조가 유효하지 않은 경우 표시되는 오류값. 예를 들어 다른 수식에서 참조하는 셀을 삭제하거나 다른 수식에서 참조하는 셀 위에 이동된 셀을 붙여 넣었을 수 있습니다.
예제처럼 데이터를 빈 워크시트에 복사한 다음 D열(전체 열)을 삭제하면 처음에 E열에 있는 수식이 D열로 이동되고 모두 #REF! 오류가 표시됨. D2셀을 선택하면 수식 입력줄에 =SUM(B2,C2,#REF!)가 표시됨.
E열의 수식이 삭제된 D열을 참조하여 수식이 더 이상 유효하지 않으므로 오류가 표시되는데 이 경우 D2의 수식에서 ",#REF!"를 제거한 다음 수식을 아래의 셀로 끌어 수식을 고친다.
#VALUE! 엑셀 오류값
Excel에서 수식에 여러 데이터 형식이 포함된 셀이 있는 경우 표시되는 오류값.
수식에 대한 오류 검사 기능이 설정된 경우 화면 설명에 "수식에 사용한 값의 데이터 형식이 잘못되었습니다."가 표시되며 일반적으로 이 문제는 수식을 조금만 변경하면 수정할 수 있다.
① 수식에 포함된 하나 이상의 셀에 텍스트가 들어 있는데 이러한 셀에서 표준 산술 연산자(+, -, * 및 /)를 사용하여 수학 연산을 수행하는 경우
예를 들어 수식 =A1+B1(여기서 A1은 문자열 "Hello"를 포함하고 B1은 숫자 3을 포함)은 #VALUE! 오류를 반환한다.
산술 연산자를 사용하는 대신 SUM, PRODUCT 또는 QUOTIENT 같은 함수를 사용하여 텍스트가 포함된 셀에 대한 산술 연산을 수행하고 함수에서 산술 연산자를 사용하지 않고 쉼표를 사용하여 인수를 구분한다.
예를 들어 =A2+A3+A4 또는 =SUM(A2+A3+A4) 수식 대신 =SUM(A2,A3,A4)를 사용
② SUM, PRODUCT 또는 QUOTIENT와 같은 수학 함수를 사용하는 수식에 숫자가 아닌 텍스트 문자열 인수가 포함되는 경우. 예를 들어 수식 PRODUCT(3,"Hello")의 경우 PRODUCT 함수의 인수로 숫자가 필요하므로 #VALUE! 오류를 반환한다.
SUM, PRODUCT, QUOTIENT 등과 같은 수학 함수에 텍스트를 직접 인수로 포함하는 인수가 없어야 한다. 수식에서 함수를 사용하고 해당 함수가 텍스트를 포함하는 셀을 참조하는 경우 해당 셀은 무시되고 오류가 표시되지 않음
③ 통합 문서에서 사용하는 데이터 연결을 사용할 수 없는 경우
통합 문서에서 데이터 연결을 사용하는 경우 데이터 연결을 복원하는 데 필요한 단계를 수행하거나 가능한 경우 데이터를 가져온다.
