IF 함수 VALUE 오류 숫자와 문자 같이 계산할 때 해결하는 방법
IF 함수 VALUE 오류 가장 빠른 해결법
IF 함수 VALUE 오류는 계산식에 들어간 셀 중 하나가 숫자처럼 보여도 실제로는 문자(텍스트) 형식으로 저장돼 있을 때 가장 많이 발생합니다. 특히 회계 프로그램이나 웹 페이지, 다른 부서에서 받은 엑셀 파일을 그대로 옮겨 쓸 때 이런 현상이 자주 나타납니다.
제일 빠른 확인법은 셀을 클릭했을 때 값이 왼쪽 정렬이면 문자, 오른쪽 정렬이면 숫자라는 것부터 보는 겁니다. 저도 회사에서 시스템에서 다운받은 정산표에 이 오류가 자주 떠서, 습관적으로 정렬 방향부터 확인합니다. 셀 서식을 따로 열어보지 않아도 한눈에 문제 셀을 골라낼 수 있는 방법이라 가장 먼저 습관을 들이면 좋습니다.
문자로 저장된 셀이라면 빈 셀에 =VALUE(A1)을 입력해 숫자로 바꾼 다음 그 결과를 IF 함수에 넣으면 IF 함수 VALUE 오류가 바로 사라집니다. 예를 들어 A1에 숫자처럼 보이는 문자 “150”이 들어 있고 =IF(A1>100,"초과","미만")을 쓰면 #VALUE! 오류가 나지만, =IF(VALUE(A1)>100,"초과","미만")으로 바꾸면 정상적으로 계산됩니다. 여러 셀을 한 번에 고치고 싶다면 아래 2번 단계의 텍스트 나누기를 쓰는 게 더 빠릅니다.
단계별로 따라 하는 해결 방법
1) 오류 셀이 진짜 문자인지 먼저 확인
빈 셀에 =ISNUMBER(A1)을 입력해 FALSE가 나오면 그 셀은 문자로 저장된 상태입니다. 이때 셀을 더블클릭했을 때 값 앞에 어퍼스트로피(‘)가 보이거나 셀 왼쪽 위에 작은 초록색 삼각형이 표시되는 경우가 많은데, 이는 엑셀이 해당 값을 텍스트로 인식하고 있다는 표시입니다.
- 확인할 셀 옆 빈 셀 선택
=ISNUMBER(A1)입력 후 Enter- FALSE면 문자, TRUE면 숫자 — 문자일 때만 아래 2번 진행
- 확인용 수식 셀은 다 쓰고 나면 삭제해도 원본 데이터에는 영향 없음
확인할 셀이 많을 때는 옆 열 전체에 이 수식을 채우기 핸들로 끌어 내리면 FALSE가 뜨는 행만 한눈에 골라낼 수 있어, 수십에서 수백 개 셀 중 어떤 데이터가 문제인지 빠르게 파악할 수 있습니다.
2) 텍스트 나누기로 한 번에 숫자로 강제 변환
여러 셀을 한꺼번에 고칠 때는 데이터 탭의 텍스트 나누기 기능이 가장 빠릅니다. 원래는 한 셀에 붙어 있는 여러 값을 나누는 기능이지만, 구분 기호 없이 그대로 마침을 눌러도 선택한 범위의 형식을 다시 계산해주는 효과가 있어 문자로 저장된 숫자를 고칠 때도 널리 쓰입니다.
- 문제가 되는 열 전체 선택
- 상단 메뉴 데이터 > 데이터 도구 > 텍스트 나누기 클릭
- 1단계 화면에서 구분 기호로 분리됨 선택 후 다음
- 2단계 구분 기호는 그대로 두고 다음
- 3단계 열 데이터 서식에서 일반 선택 후 마침
이 방법은 셀 안 값을 지우지 않고 형식만 숫자로 바꿔주므로, 수식을 새로 짤 필요 없이 IF 함수 VALUE 오류가 그 자리에서 없어집니다. 다만 열에 날짜 데이터가 섞여 있다면 3단계에서 반드시 ‘일반’ 대신 ‘날짜’를 선택해야 하며, 순수한 숫자 열일 때만 ‘일반’을 골라야 날짜가 이상한 숫자로 바뀌는 것을 막을 수 있습니다.
3) 숫자에 단위·공백이 섞인 경우
"3,000원"처럼 문자가 섞여 있으면 SUBSTITUTE로 문자를 지운 뒤 VALUE로 변환합니다.
예시: =VALUE(SUBSTITUTE(A1,"원","")) 처럼 단위 글자를 빈 값으로 바꾼 다음 숫자로 변환하면 됩니다. 단위나 기호가 하나가 아니라 “1,200원”, “85%”, “3.5kg”처럼 쉼표까지 섞여 있다면 SUBSTITUTE를 중첩해서 =VALUE(SUBSTITUTE(SUBSTITUTE(A1,"kg",""),",",""))처럼 필요한 만큼 겹쳐 쓰면 됩니다. 눈에 안 보이는 공백까지 있다면 =VALUE(TRIM(SUBSTITUTE(A1,"원","")))처럼 TRIM을 한 번 더 씌우세요.
웹 페이지나 이메일 본문에서 복사해온 표는 일반 스페이스가 아닌 줄바꿈 문자나 특수 공백이 숨어 있는 경우가 있는데, 이때는 TRIM만으로 해결되지 않으므로 =VALUE(CLEAN(TRIM(A1)))처럼 CLEAN 함수를 한 겹 더 추가하면 대부분 해결됩니다.
4) TEXT 함수 결과를 다시 계산에 쓰는 경우
TEXT 함수는 결과를 항상 문자로 돌려주므로 그 값을 IF 함수 안에서 다시 더하거나 곱하면 오류가 납니다. 예를 들어 매출 보고서에서 =TEXT(B2,"#,##0")으로 천 단위 구분 기호를 넣어 보기 좋게 만든 값을 다른 수식에서 다시 계산에 쓰려고 하면 이런 문제가 생기는데, TEXT의 결과는 사람이 읽기 편한 문자열일 뿐 실제 숫자가 아니기 때문입니다.
이럴 땐 =VALUE(TEXT(A1,"#,##0"))처럼 VALUE로 한 번 더 감싸거나, 뒤에 *1을 붙여 숫자로 되돌려야 계산이 됩니다.
5) 빈 셀처럼 보이지만 공백만 들어 있는 경우
비어 있는 것처럼 보이는 셀에 스페이스바로 입력한 공백 한 칸만 들어 있어도 IF 함수는 이를 문자로 인식해 오류를 냅니다. =IF(A1="",0,계산식)처럼 빈 문자열만 걸러내면 이런 경우를 놓칠 수 있으므로, =IF(TRIM(A1)="",0,계산식)으로 공백까지 함께 걸러내는 편이 더 안전합니다.
=IFERROR(IF(A1>0,A1*B1,0),"확인필요")처럼 써두면 오류가 나도 파일이 깨지지 않고, 나중에 "확인필요"라고 뜬 줄만 골라 원인을 찾을 수 있습니다. 다만 이는 임시 방편이므로, 매달 반복해서 쓰는 보고서라면 데이터를 받아오는 단계에서부터 텍스트 나누기나 VALUE로 형식을 통일해두는 편이 장기적으로 더 안전합니다.안 될 때 원인별 해결 표
| 증상 | 원인 | 해결 방법 |
|---|---|---|
| ISNUMBER 결과 FALSE | 숫자가 문자 형식으로 저장됨 | 텍스트 나누기 또는 VALUE 함수 |
| 단위·기호가 붙어 있음(원, 개, %) | 문자와 숫자가 한 셀에 섞임 | SUBSTITUTE로 문자 제거 후 VALUE |
| 붙여넣기 직후만 오류 | 보이지 않는 공백·줄바꿈 | TRIM, CLEAN 함수 추가 |
| TEXT 함수 쓴 셀만 오류 | TEXT 결과가 문자로 고정됨 | VALUE로 재변환 또는 *1 |
| 텍스트 나누기 해도 그대로 | 표시 형식이 텍스트로 지정됨 | Ctrl+1 > 표시 형식 > 숫자로 변경 후 셀 더블클릭+Enter |
| 빈 셀인데도 오류남 | 스페이스바 공백만 들어있음 | TRIM으로 공백 제거 후 조건 판단 |
| 다른 시트를 참조할 때만 오류 | 원본 시트 값 자체가 문자 형식 | 원본 시트에서부터 형식을 숫자로 통일 |
같이 알아두면 좋은 팁
수식 자체가 맞는지 헷갈릴 때는 수식 탭의 오류 검사 기능을 켜두면 편합니다. 이 기능을 켜두면 수식에 문제가 있는 셀 왼쪽 위에 작은 녹색 삼각형이 자동으로 표시되어, 파일 전체를 하나씩 눌러보지 않아도 문제 셀을 빠르게 찾을 수 있습니다.
| 상황 | 메뉴 경로 |
|---|---|
| 오류 난 셀 자동으로 찾기 | 수식 > 수식 분석 > 오류 검사 |
| 셀 서식 직접 확인 | 셀 선택 후 Ctrl+1 > 표시 형식 |
| 보이지 않는 공백 일괄 제거 | 홈 > 편집 > 찾기 및 선택 > 바꾸기(Ctrl+H) |
| 소수 셀만 골라 강제 재계산 | 셀 선택 후 F2 눌렀다가 Enter |
VALUE 외에도 비슷한 역할을 하는 함수들이 있어 헷갈리기 쉬운데, 상황에 맞게 골라 쓰면 오류를 더 줄일 수 있습니다.
| 함수 | 역할 | 주의할 점 |
|---|---|---|
| VALUE | 문자로 된 숫자를 진짜 숫자로 변환 | 완전히 숫자로만 이루어진 문자열에만 적용 가능 |
| NUMBERVALUE | 지역별 소수점·구분 기호가 다른 숫자 문자열 변환 | 해외에서 받은 파일의 소수점 표기가 다를 때 유용 |
| TEXT | 숫자를 원하는 서식의 문자로 변환 | 결과는 항상 문자이므로 다시 계산하려면 VALUE 필요 |
| N | 값을 숫자로, 나머지는 0으로 변환 | 텍스트를 0으로 바꿔버리므로 조건 판단용으로는 부적합 |
참고로 IF 함수 VALUE 오류를 고치다가 파일 자체가 먹통이 되는 경우도 있는데, 특히 수식이 아주 많이 걸린 오래된 파일에서 텍스트 나누기를 넓은 범위에 한꺼번에 실행하면 일시적으로 응답이 느려질 수 있습니다. 이때는 저장부터 하고 응답 없는 프로그램을 강제 종료하는 방법을 먼저 확인한 뒤 다시 열어 시도하는 편이 안전합니다.
아래는 오류 원인을 찾는 순서를 그림으로 정리한 흐름도입니다.
자주 묻는 질문
Q1. IFERROR로 감싸면 문제가 완전히 해결된 건가요?
아닙니다. IFERROR는 오류 화면만 가려줄 뿐 문자로 저장된 원인 자체를 고치지는 않으므로, 계산값이 실제로 필요하면 VALUE 함수로 형식을 바꿔야 합니다. 예를 들어 합계나 평균처럼 다른 수식이 그 결과를 다시 참조하는 경우라면 IFERROR만으로는 “확인필요” 같은 문자만 남고 정확한 숫자는 얻을 수 없습니다.
Q2. 텍스트 나누기를 해도 여전히 왼쪽 정렬이면 어떻게 하나요?
Ctrl+1로 셀 서식을 열어 표시 형식이 텍스트로 지정돼 있는지 확인하고, 숫자나 일반으로 바꾼 뒤 셀을 더블클릭하고 Enter를 눌러 강제로 재계산시키면 됩니다. 셀 몇 개만 남았다면 각 셀에서 F2를 누른 뒤 그대로 Enter만 눌러도 같은 효과를 얻을 수 있습니다.
Q3. 수백 개 셀을 하나씩 VALUE로 바꾸기 번거로운데 방법이 없나요?
열 전체를 선택해 데이터 탭의 텍스트 나누기를 한 번만 실행하면 선택한 범위 전체가 동시에 숫자로 바뀌어 셀마다 수식을 따로 넣을 필요가 없습니다. 자주 반복하는 작업이라면 매크로로 기록해두고 단축키로 실행하는 방법도 있습니다.
Q4. IF 함수 안에 여러 조건이 들어 있는데 그중 하나만 문자여도 전체가 오류로 뜨나요?
네, IF 함수는 조건식 전체를 한 번에 계산하므로 AND나 다른 함수와 중첩된 조건 중 단 하나의 셀만 문자여도 수식 전체가 #VALUE! 오류로 표시됩니다. 이럴 때는 조건에 들어가는 셀을 하나씩 ISNUMBER로 점검해 어떤 셀이 문제인지 먼저 좁혀나가는 것이 순서를 헤매지 않는 방법입니다.
정리하면 IF 함수 VALUE 오류는 대부분 데이터 형식 문제이므로, ISNUMBER로 원인부터 확인하고 텍스트 나누기나 VALUE 함수로 형식을 맞춰주면 대부분 그 자리에서 해결됩니다. 더 자세한 내용은 Microsoft 공식 지원 문서에서도 확인할 수 있습니다. 여러분은 이 오류를 어떤 방법으로 해결하고 계신가요, 댓글로 공유해 주세요.
출처: Editlab, https://editlab.luvpp.com
EDITLAB이 매체는 Editlab이 발행합니다분야별 전문 매체 8곳과 무료 데이터 도구를 함께 운영합니다.네트워크 보기 ›
새 글 알림 받기
유용한 정보가 올라올 때 이메일로 알려드립니다.
