Excel에서 # 수식 오류를0, 빈 셀 또는 특정 텍스트로 바꾸는 방법은 무엇인가요?

Excel 사용자는 데이터 표나 계산 결과에서 #DIV/0!, #VALUE!, #REF!, #N/A, #NUM!, #NAME?, #NULL!과 같은 수식 오류를 자주 접하게 됩니다. 이러한 오류 값들은 보고서의 가독성을 저해할 뿐만 아니라, 추가적인 데이터 처리, 분석, 공유에도 영향을 미칠 수 있습니다. 보다 나은 데이터 표현이나 후속 계산 논리를 위해, 워크시트에 나타나는 모든 또는 특정 오류 유형을0(영), 빈 셀, 또는 타인이 이해하기 쉬운 지정 텍스트 문자열로 대체할 필요가 있을 수 있습니다.

이 글에서는 Excel 셀에서 # 수식 오류를 찾고 대체하는 실용적이고 따라하기 쉬운 방법을 다룹니다. 아래의 대표적인 표를 예시로 하여, 필요와 작업 흐름에 따라 오류 값을 효율적으로 바꾸는 여러 가지 방법을 보여드립니다.

수식 오류를0, 특정 값 또는 빈 셀로 대체하는 방법

• IFERROR 함수 사용하기
• ERROR.TYPE 함수 사용하기
• 이동 명령어 사용하기
• Kutools for Excel 사용하기
• VBA 코드 사용하기

IFERROR로 # 수식 오류를0, 원하는 값 또는 빈 셀로 대체하기

Excel에는 IFERROR 함수가 탑재되어 있어, 일반적인 모든 오류 유형을 포착하고 사용자 임의의 값이나 메시지로 바꿀 수 있습니다. 이 기능을 활용하면 계산 시 오류 처리가 한결 간편해지고, 워크시트의 명확성이 향상됩니다.
사용 방법은 다음과 같습니다. =IFERROR(value, value_if_error) 관련 셀에 위 수식을 입력하세요. 만약 value 가 오류인 경우에는 지정한 value_if_error를 반환하며, value 가 오류가 아닐 때에는 계산된 결과값을 그대로 표시합니다.

위 예시에서는 #N/A와 같은 다양한 수식 오류 유형이 빈 셀, 숫자0, 사용자 정의 텍스트 메시지 등으로 대체되었습니다. 필요에 맞게 value_if_error를 조정할 수 있습니다. 아래와 같이 실제 값, 빈 셀을 위한 빈 문자열("") 또는 설명 텍스트를 입력할 수 있습니다:

이 방법은 수식 작성 시, 최종 테이블, 보고서, 대시보드 등에 오류 값이 노출되지 않게 하거나 데이터를 타인과 공유하는 경우에 가장 적합합니다. 복잡하거나 불안정한 계산식은 IFERROR로 감싸면 워크시트의 연속성을 쉽게 보장할 수 있습니다.
특정 오류 유형만 처리하고 싶을 경우(예: #N/A만), IFNA 사용 또는 IF와 ISERROR/ISERR 함수 조합 등 보다 정밀한 처리가 가능합니다. 또한, 전체 데이터셋에 적용하려면 해당 수식을 필요한 모든 셀에 복사하여 적용하세요.

ERROR.TYPE으로 # 수식 오류를 특정 숫자로 바꾸기

Excel에 내장된 또 하나의 기능인 ERROR.TYPE 함수는 각 오류 유형에 해당하는 고유 번호를 반환하여, 서로 다른 오류 값을 식별할 수 있도록 돕습니다. 이는 수식 내에서 오류 유형별 조건부 논리를 구현해야 할 때 특히 유용합니다.
아래 예시와 같이, ERROR.TYPE 함수를 수식 오류 바로 옆 빈 셀에 넣으면1~8 사이의 오류 코드를 반환합니다.

채우기 핸들 활용하기 채우기 핸들을 이용하면 ERROR.TYPE 수식을 범위 전체에 적용할 수 있습니다. 다만 ERROR.TYPE은 실제로 오류를 바꾸기보다는 오류 유형을 분석하거나 매핑할 때에 주로 사용됩니다. 일반적으로 IF나 CHOOSE 함수와 함께 사용해 친숙한 대체 값을 출력한다는 점에 유의하세요. 각 오류 코드가 헷갈린다면 위 표나 공식 문서를 참고하면 됩니다.

오류 유형별 맞춤 대체가 필요할 경우, ERROR.TYPE을 IF 또는 CHOOSE 수식 내부에 삽입해 각 오류 조건마다 알맞은 정보를 출력하도록 만들 수 있습니다.

이동 명령어로 # 수식 오류를0, 원하는 값 또는 빈 셀로 검색 및 대체하기

이 방법은 기존 영역에서 오류 셀을 일괄 수정하고자 하는 사용자가, 특히 계산이 모두 끝난 후 직접 오류 셀 값을 덮어쓰고자 할 때 적합합니다. Excel의 내장 '이동(Go To) 특수' 명령으로, 선택 영역 내 오류 셀을 한 번에 찾아 대체할 수 있습니다.

1. 우선, 수식 오류가 있을 수 있는 시트 범위를 선택하세요.

2. 키보드에서 F5(또는 Ctrl + G)를 눌러 이동(Go To) 대화상자를 엽니다.

3. '특수(Special)' 버튼을 클릭해 이동 특수(Go To Special) 옵션 상자를 엽니다.

4. '수식(Formulas)' 옵션만 선택하고, 그 안에서 '오류(Errors)' 항목만 체크되었는지 확인하세요. 이렇게 하면 선택한 범위에서 오류 결과가 표시된 모든 셀이 대상으로 지정됩니다.

5. 확인(OK)을 클릭하면, Excel이 해당 오류 셀 전체를 자동으로 강조 표시합니다.

6.0이나 원하는 값으로 바로 입력한 뒤, Ctrl + Enter를 눌러 Excel이 선택된 모든 오류 셀을 그 값으로 채우도록 하세요.

해당 오류 셀을 완전히 비우고 싶다면, 선택 후 Delete 키만 눌러 빈 셀로 남길 수 있습니다.

Kutools for Excel로 # 수식 오류를0, 특정 값 또는 빈 셀로 검색 및 대체하기

Kutools for Excel의 오류 조건 마법사 서식은 오류 값 관리를 더욱 간소화합니다. 이 도구로 사용자는0, 빈 칸, 또는 맞춤 메시지 등 모든 또는 특정 오류 유형을 유연하게 대체할 수 있어서, 프레젠테이션이나 후속 편집에 매우 유용합니다. 수식에 익숙하지 않거나 대형/복잡한 데이터셋을 다루는 사람에게 특히 편리합니다.

1. 먼저 오류 값을 대체할 워크시트의 범위를 선택하세요. 메뉴에서 Kutools > 더보기(More) > 오류 조건 마법사 서식(Error Condition Wizard)을 클릭합니다.

2. 오류 조건 마법사 서식(Error Condition Wizard) 대화상자에서 다음과 같이 환경설정을 진행합니다.

(1) 오류 유형(Error types) 항목에서 모든 오류 값(Any error value), #N/A 오류 값만(Only the #N/A error value), 혹은 #N/A를 제외한 오류(Any error value except #N/A) 중 적용 대상을 선택하세요. 작업 목적에 맞게 지정합니다.

(2) 오류 표시(Error display) 구역에서, 오류를 빈 칸으로 보고 싶다면 '없음(빈 셀)(Nothing (a blank cell))'을 선택합니다.

오류를0이나 메시지로 바꾸려면 '메시지(텍스트)(A message (text))'를 선택한 뒤, 입력란에 "0" 또는 원하는 텍스트를 기입합니다.

(3) 확인(OK)을 클릭해 변경 사항을 적용하세요.

유틸리티가 즉시 선택 영역 전체를 설정에 따라 처리하며, 오류 값이 전부 지정한 대로 바뀝니다. 아래는 적용 결과의 예시 화면입니다.

모든 오류 값을 빈 셀로 바꾸기

모든 오류 값을0으로 바꾸기

모든 오류 값을 특정 텍스트로 바꾸기

Kutools for Excel의 오류 조건 마법사 서식은 반복적 오류 정리에 매우 실용적입니다. 필요시 빠르게 실행 취소(Ctrl + Z)도 지원하므로, 특히 대규모 데이터 처리 시에는 적용 전에 반드시 선택 영역을 한 번 더 확인하세요.

VBA 코드로 모든 오류 값을0, 빈 셀, 또는 지정한 텍스트로 일괄 변경하기

대형 워크시트 자동화 정리나 특정 오류 유형 반복 대체 등 고급 시나리오에서는 간단한 VBA 매크로를 활용해 시간과 수작업을 크게 줄일 수 있습니다. 아래에서는 VBA로 지정 범위의 모든 오류 값을 원하는 대체 값(0, 빈 셀, 사용자 라벨 등)으로 일괄 변경하는 단계별 방법을 안내합니다.
이 방법은 매크로 기본 작업에 익숙한 사용자가 활용하기에 확장성이 우수합니다.

1. 상단 메뉴에서 개발 도구(Developer) > Visual Basic을 차례로 클릭해 VBA 편집기를 엽니다. 편집기에서 삽입(Insert) > 모듈(Module)을 클릭한 뒤, 아래 코드를 빈 모듈 창에 복사/붙여넣기 하세요:

Sub ReplaceErrorsWithValue()
    Dim WorkRng As Range
    Dim ReplaceWhat As String
    Dim Prompt As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to process", xTitleId, WorkRng.Address, Type:=8)
    
    Prompt = "Enter the replacement value for errors:" & vbCrLf & "(Leave blank for empty cell; enter 0 or any text string as needed)"
    ReplaceWhat = Application.InputBox(Prompt, xTitleId, "", Type:=2)
    
    If Not WorkRng Is Nothing Then
        Dim cell As Range
        Application.ScreenUpdating = False
        
        For Each cell In WorkRng
            If IsError(cell.Value) Then
                cell.Value = ReplaceWhat
            End If
        Next
        
        Application.ScreenUpdating = True
    End If
End Sub

2.그런 뒤 아래의 버튼을 클릭하거나 F5 를 VBA 창에서 눌러 매크로를 실행합니다. 실행 중에는 대상으로 삼을 범위를 지정하고, 대체할 값을 입력하세요: 입력란을 비워두면 오류 셀은 빈 칸으로, "0"을 입력하면0으로, 원하는 라벨 텍스트를 넣으면 해당 텍스트로 바꿉니다.

Kutools for Excel로 # 수식 오류를0 또는 빈 칸으로 검색 및 대체하기

관련 글:

• Excel에서 #DIV/0! 오류를 읽기 쉬운 메시지로 바꾸는 방법은?