Microsoft Excel에서 수식을 자주 다루다 보면 한 번쯤 마주치게 되는 것이 바로 #VALUE! 오류입니다. 이 오류는 매우 포괄적인 성격을 띠고 있어 원인을 파악하기 어려울 때가 많습니다. 예를 들어 숫자 계산 수식에 텍스트 값이 섞여 있으면 이 오류가 발생하는데, 그 이유는 더하기나 빼기 같은 연산에서 Excel은 숫자만 사용할 것으로 기대하기 때문입니다.
#VALUE! 오류를 피하는 가장 간단한 방법은 수식에 오탈자가 없는지, 올바른 데이터를 사용하고 있는지 항상 확인하는 것입니다. 하지만 현실적으로 이것이 항상 가능한 것은 아니기 때문에, 이 글에서는 Excel에서 #VALUE! 오류를 해결할 수 있는 여러 가지 방법을 소개합니다.
참고로, 엑셀 활용 능력을 한 단계 끌어올려 주는 생산성 향상 팁도 함께 익혀두면 업무 효율이 크게 향상됩니다.
#VALUE! 오류의 주요 발생 원인
Excel에서 수식을 사용할 때 #VALUE! 오류가 발생하는 데에는 여러 가지 이유가 있습니다. 대표적인 원인은 다음과 같습니다.
- 예상치 못한 데이터 유형: 특정 데이터 유형에 맞춰 작성된 수식을 사용했는데, 워크시트의 하나 이상의 셀에 다른 유형의 데이터가 들어 있는 경우 Excel은 수식을 실행하지 못하고 #VALUE! 오류를 반환합니다.
- 공백 문자: 눈으로 보기에 비어 있는 셀이지만 실제로는 공백 문자가 들어 있는 경우가 있습니다. 화면상으로는 비어 있어 보여도 Excel은 공백을 인식하기 때문에 수식을 처리하지 못합니다.
- 보이지 않는 문자: 공백과 마찬가지로 눈에 보이지 않는 숨겨진 문자나 인쇄되지 않는 문자가 셀에 포함되어 있으면 수식 계산이 중단될 수 있습니다.
- 잘못된 수식 구문: 수식의 일부가 누락되었거나 순서가 잘못되면 함수의 인수가 올바르지 않게 됩니다. 이 경우 Excel은 수식을 인식하거나 처리하지 못합니다.
- 잘못된 날짜 형식: 날짜를 다룰 때 숫자가 아닌 텍스트로 입력되어 있으면 Excel이 값을 제대로 이해하지 못합니다. 프로그램이 해당 날짜를 유효한 날짜가 아닌 텍스트 문자열로 취급하기 때문입니다.
- 호환되지 않는 범위 크기: 수식이 서로 다른 크기나 모양의 여러 범위를 참조해야 하는 경우 계산을 수행할 수 없습니다.
#VALUE! 오류의 원인을 파악하면 어떻게 수정해야 할지 명확해집니다. 이제 사례별로 #VALUE! 오류를 없애는 구체적인 방법을 살펴보겠습니다.
잘못된 데이터 유형으로 인한 #VALUE! 오류 해결
일부 Excel 수식은 특정 데이터 유형에서만 작동하도록 설계되어 있습니다. 이것이 #VALUE! 오류의 원인이라고 의심된다면, 참조된 셀 중 잘못된 데이터 유형을 사용하는 곳이 없는지 확인해야 합니다.
예를 들어 숫자를 계산하는 수식을 사용하고 있는데 참조 셀 중 하나에 텍스트 문자열이 들어 있다면 수식이 정상적으로 작동하지 않습니다. 결과 대신 선택한 셀에 #VALUE! 오류가 표시됩니다.
가장 전형적인 예는 덧셈이나 곱셈 같은 단순한 수학 계산을 수행하려는데 값 중 하나가 숫자가 아닌 경우입니다.
이 오류를 해결하는 방법은 여러 가지가 있습니다.
- 누락된 숫자를 직접 입력한다.
- 텍스트 문자열을 무시하는 Excel 함수를 사용한다.
- IF 문을 작성한다.
위 예시에서는 PRODUCT 함수를 활용할 수 있습니다: =PRODUCT(B2,C2)
이 함수는 공백이 있거나 데이터 유형이 잘못되었거나 논리값이 들어 있는 셀을 무시하고, 해당 참조값에 1을 곱한 것처럼 결과를 반환합니다.
또는 두 셀 모두 숫자 값을 포함하고 있을 때만 곱셈을 수행하고, 그렇지 않으면 0을 반환하는 IF 문을 만들 수도 있습니다. 다음 수식을 사용하세요.
=IF(AND(ISNUMBER(B2),ISNUMBER(C2)),B2*C2,0)
공백 및 숨겨진 문자로 인한 #VALUE! 오류 해결
일부 수식은 셀에 숨겨진 문자나 보이지 않는 공백이 있으면 작동하지 않습니다. 겉보기에는 비어 있는 셀처럼 보여도 내부에 공백이나 인쇄되지 않는 문자가 포함되어 있을 수 있습니다. Excel은 공백을 텍스트 문자로 간주하기 때문에, 앞서 살펴본 데이터 유형 문제와 마찬가지로 #VALUE! 오류를 일으킬 수 있습니다.
위 예시에서 C2, B7, B10 셀은 비어 있는 것처럼 보이지만 실제로는 여러 개의 공백이 들어 있어 곱셈을 시도할 때 #VALUE! 오류가 발생합니다.
이 오류를 해결하려면 해당 셀이 실제로 비어 있는지 확인해야 합니다. 셀을 선택한 후 키보드의 DELETE 키를 눌러 보이지 않는 문자나 공백을 제거하세요.
또는 텍스트 값을 무시하는 Excel 함수를 사용할 수도 있습니다. 대표적인 함수가 SUM 함수입니다.
=SUM(B2:C2)
호환되지 않는 범위로 인한 #VALUE! 오류 해결
여러 범위를 인수로 받는 함수를 사용할 때, 해당 범위들의 크기와 모양이 동일하지 않으면 함수가 작동하지 않습니다. 이 경우 수식은 #VALUE! 오류를 반환합니다. 셀 참조 범위를 수정하면 오류가 사라집니다.
예를 들어 FILTER 함수를 사용하여 A2:B12 범위와 A3:A10 범위를 필터링하려는 경우를 생각해 보겠습니다. =FILTER(A2:B12,A2:A10="Milk") 수식을 사용하면 #VALUE! 오류가 발생합니다.
이때 범위를 A3:B12와 A3:A12로 변경해야 합니다. 범위의 크기와 모양이 일치하면 FILTER 함수가 문제없이 계산을 수행합니다.
잘못된 날짜 형식으로 인한 #VALUE! 오류 해결
Microsoft Excel은 다양한 날짜 형식을 인식할 수 있습니다. 하지만 Excel이 날짜 값으로 인식하지 못하는 형식을 사용하면 해당 값을 텍스트 문자열로 취급합니다. 이런 날짜를 수식에 사용하면 #VALUE! 오류가 반환됩니다.
이 문제를 해결하는 유일한 방법은 잘못된 날짜 형식을 Excel이 인식할 수 있는 올바른 형식으로 변환하는 것입니다.
잘못된 수식 구문으로 인한 #VALUE! 오류 해결
계산 과정에서 잘못된 수식 구문을 사용하면 #VALUE! 오류가 발생합니다. 다행히 Microsoft Excel에는 수식 점검에 도움이 되는 감사 도구(Auditing Tools)가 내장되어 있습니다. 리본 메뉴의 '수식 검사' 그룹에서 찾을 수 있으며, 사용 방법은 다음과 같습니다.
- #VALUE! 오류를 반환하는 수식이 있는 셀을 선택합니다.
- 리본 메뉴에서 수식 탭을 엽니다.
- '수식 검사' 그룹에서 오류 검사 또는 수식 평가를 찾아 선택합니다.
Excel은 해당 셀에서 사용한 수식을 분석하고, 구문 오류를 발견하면 해당 부분을 강조 표시합니다. 감지된 구문 오류는 손쉽게 수정할 수 있습니다.
예를 들어 =FILTER(A2:B12,A2:A10="Milk") 수식을 사용하면 범위 값이 올바르지 않아 #VALUE! 오류가 발생합니다. 수식에서 문제가 되는 위치를 찾으려면 오류 검사를 클릭하고 대화 상자에 표시되는 결과를 확인하세요.
수식 구문을 =FILTER(A2:B12,A2:A12="Milk")로 수정하면 #VALUE! 오류가 해결됩니다.
XLOOKUP 및 VLOOKUP 함수의 #VALUE! 오류 해결
Excel 워크시트나 통합 문서에서 데이터를 검색하고 불러올 때는 일반적으로 XLOOKUP 함수나 그 이전 버전의 VLOOKUP 함수를 사용합니다. 이 함수들 역시 특정 조건에서 #VALUE! 오류를 반환할 수 있습니다.
XLOOKUP에서 #VALUE! 오류가 발생하는 가장 흔한 원인은 반환 배열의 크기가 서로 맞지 않는 것입니다. LOOKUP 배열이 반환 배열보다 크거나 작은 경우에도 오류가 발생할 수 있습니다.
예를 들어 =XLOOKUP(D2,A2:A12,B2:B13) 수식을 사용하면 조회 배열과 반환 배열의 행 수가 달라 #VALUE! 오류가 반환됩니다.
수식을 =XLOOKUP(D2,A2:A12,B2:B12)로 조정하면 됩니다.
IFERROR 또는 IF 함수로 #VALUE! 오류 처리하기
오류 자체를 처리하도록 설계된 수식도 활용할 수 있습니다. #VALUE! 오류의 경우 IFERROR 함수 또는 IF와 ISERROR 함수의 조합을 사용하면 됩니다.
예를 들어 IFERROR 함수를 사용하면 #VALUE! 오류를 더 의미 있는 텍스트 메시지로 대체할 수 있습니다. 아래 예시에서 도착 날짜를 계산하면서, 잘못된 날짜 형식으로 인해 발생하는 #VALUE! 오류를 "날짜를 확인하세요"라는 메시지로 바꾸고 싶다고 가정해 보겠습니다.
다음 수식을 사용합니다: =IFERROR(B2+C2,"날짜를 확인하세요")
오류가 없는 경우 위 수식은 첫 번째 인수의 계산 결과를 그대로 반환합니다.
같은 결과는 IF와 ISERROR 수식의 조합으로도 얻을 수 있습니다.
=IF(ISERROR(B2+C2),"날짜를 확인하세요",B2+C2)
이 수식은 먼저 반환 결과가 오류인지 확인합니다. 오류라면 첫 번째 인수(날짜를 확인하세요)를 반환하고, 오류가 아니라면 두 번째 인수(B2+C2)를 반환합니다.
IFERROR 함수의 유일한 단점은 #VALUE! 오류뿐만 아니라 모든 종류의 오류를 잡아낸다는 점입니다. 즉, #N/A, #DIV/0!, #VALUE!, #REF! 같은 오류들을 구분하지 못합니다.
Excel은 방대한 함수와 기능을 통해 데이터 관리와 분석의 무한한 가능성을 제공합니다. Microsoft Excel에서 #VALUE! 오류를 이해하고 극복하는 것은 스프레드시트 활용 능력에서 반드시 갖춰야 할 핵심 기술입니다. 이런 사소한 장애물들은 답답하게 느껴질 수 있지만, 이 글에서 소개한 지식과 기법을 활용하면 문제를 진단하고 해결할 준비가 충분히 되어 있을 것입니다.