Как заменить ошибки формул # на 0, пустые ячейки или определенный текст в Excel?

Пользователи Excel часто сталкиваются с ошибками формул, такими как #ДЕЛ/0!, #ЗНАЧ!, #ССЫЛКА!, #Н/Д, #ЧИСЛО!, #ИМЯ?, и #ПУСТО! в своих таблицах данных или результатах вычислений. Эти значения ошибок не только влияют на читаемость отчетов, но также могут повлиять на дальнейшую обработку данных, анализ и обмен. Часто для улучшения представления данных или логики последующих вычислений становится необходимым заменить все или определенные типы ошибок в листах на 0 (ноль), пустые ячейки или указанные текстовые строки, которые легче понять другим пользователям.

В этой статье представлены практические и простые решения для поиска и замены ошибок формул # в ячейках Excel. Используя представительную таблицу, показанную ниже, мы продемонстрируем, как эффективно заменить эти значения ошибок в зависимости от ваших потребностей и рабочего процесса.

Замена ошибок формул на 0, любые конкретные значения или пустые ячейки

• С помощью функции ЕСЛИОШИБКА
• С помощью функции ТИП.ОШИБКИ
• С помощью команды ПЕРЕЙТИ
• С помощью Kutools для Excel
• С помощью кода VBA

Замена ошибок формул # на 0, любые конкретные значения или пустые ячейки с помощью ЕСЛИОШИБКА

Excel предоставляет ЕСЛИОШИБКА функцию, которая специально разработана для перехвата всех распространенных типов ошибок и позволяет вам заменить их любым значением или сообщением по вашему выбору. Это может упростить обработку ошибок во время вычислений и улучшить ясность листа.
Чтобы использовать её, введите =ЕСЛИОШИБКА(значение; значение_если_ошибка) в соответствующей ячейке. Если значение является ошибкой, она вернет указанное вами значение_если_ошибка; если значение не является ошибкой, она просто возвращает рассчитанный результат.

В приведенном выше примере различные типы ошибок формул, такие как #Н/Д, были заменены либо пустой ячейкой, либо числом 0, либо пользовательским текстовым сообщением. Вы можете настроить значение_если_ошибка в соответствии с вашими требованиями — как показано ниже, введите фактические значения, пустую строку ("") для пустой ячейки или описательный текст по мере необходимости:

Этот подход наиболее подходит, когда вы создаете формулы и хотите убедиться, что значения ошибок не отображаются в ваших итоговых таблицах, отчетах, информационных панелях или при передаче данных другим людям. Практический совет — обернуть любые сложные или нестабильные вычисления в ЕСЛИОШИБКА, чтобы сохранить непрерывность листа.
Имейте в виду, что если вы хотите обрабатывать только определенные типы ошибок (например, только #Н/Д), рассмотрите использование ЕСЛИНД или комбинации ЕСЛИ и ЕОШИБКА/ЕОШ для более целевого управления. Также обязательно скопируйте свою формулу для всех соответствующих ячеек, чтобы охватить весь набор данных.

Замена ошибок формул # на конкретные числа с помощью ТИП.ОШИБКИ

Функция ТИП.ОШИБКИ — еще одна встроенная функция Excel, которую можно использовать для идентификации различных значений ошибок, возвращая уникальное число, соответствующее каждому типу ошибки. Это особенно полезно, когда вы хотите различать типы ошибок для дальнейшей условной логики в ваших формулах.
В следующем примере использование ТИП.ОШИБКИ в пустой ячейке рядом с ошибкой формулы возвращает код (от 1 до 8).

Использование маркера заполнения позволяет применить формулу ТИП.ОШИБКИ по диапазону. Однако обратите внимание, что ТИП.ОШИБКИ предназначен главным образом для анализа или отображения типов ошибок, а не для их прямой замены. Обычно ее используют вместе с ЕСЛИ или ВЫБОР для вывода более удобных замен. Кроме того, запомнить каждый код ошибки может потребовать обращения к документации или таблице выше.

Если ваш сценарий требует настраиваемых замен на основе типа ошибки, вы можете вложить ТИП.ОШИБКИ в формулу ЕСЛИ или ВЫБОР, чтобы вывести соответствующую информацию для каждого условия ошибки.

Поиск и замена ошибок формул # на 0, любые конкретные значения или пустые ячейки с помощью команды ПЕРЕЙТИ

Этот метод подходит для пользователей, которые хотят пакетно обработать и напрямую перезаписать ячейки с ошибками в существующей области, особенно после окончания расчетов. Используя встроенную команду Excel «Перейти к специальным», вы можете найти все ячейки с ошибками в выбранной области и заменить их сразу.

1. Сначала выберите диапазон листа, содержащий возможные ошибки формул.

2. Нажмите F5 на клавиатуре (или Ctrl + G), чтобы вызвать диалоговое окно «Перейти».

3. Нажмите Специальные, чтобы открыть параметры диалогового окна «Перейти к специальным».

4. Выберите только опцию Формулы, и внутри нее убедитесь, что отмечен только флажок Ошибки. Это действие будет направлено на все ячейки, отображающие результаты ошибок в выбранном диапазоне.

5. Нажмите OK, и Excel автоматически выделит все такие ячейки с ошибками.

6. Непосредственно введите 0 или выбранное значение замены и используйте Ctrl + Enter, чтобы Excel заполнил все выбранные ячейки с ошибками этим значением. 

Если вы хотите полностью очистить эти ячейки с ошибками, просто нажмите клавишу Delete после выбора, чтобы оставить эти ячейки пустыми.

Поиск и замена ошибок формул # на 0, любые конкретные значения или пустые ячейки с помощью Kutools для Excel

Мастер форматирования условий ошибок Kutools для Excel упрощает процесс управления значениями ошибок. С помощью этого инструмента пользователи могут гибко заменить все или определенные типы ошибок на 0, пустые ячейки или персонализированные сообщения для презентации или последующего редактирования. Это особенно удобно для неспециалистов по формулам или пользователей, работающих с большими и сложными наборами данных.

1. Начните с выбора диапазона, где вы хотите заменить значения ошибок. Затем перейдите в меню и нажмите Kutools > Дополнительно > Мастер форматирования условий ошибок.

2. В диалоговом окне Мастер форматирования условий ошибок настройте свои предпочтения следующим образом:

(1) В разделе Типы ошибок выберите, следует ли применять действие ко всем значениям ошибок, только к значению ошибки #Н/Д или ко всем значениям ошибок, кроме #Н/Д. Выберите вариант, подходящий для вашего сценария.

(2) В разделе Отображение ошибок выберите Нет (пустая ячейка), если вы хотите, чтобы ошибки отображались как пустые.

Чтобы заменить ошибки нулем или сообщением, выберите Текстовое сообщение и введите "0" или пользовательский текст в поле.

(3) Нажмите OK, чтобы применить изменения.

Утилита мгновенно обработает ваш выбор, заменяя значения ошибок по всей области в соответствии с вашими настройками. Ниже представлены визуальные результаты:

Заменить все значения ошибок на пустые

Заменить все значения ошибок на ноль

Заменить все значения ошибок на определенный текст

Мастер форматирования условий ошибок в Kutools для Excel очень практичен для повторяющихся задач по очистке. Вы также можете быстро отменить (Ctrl + Z) изменения, если это необходимо. Всегда проверяйте свой выбор перед применением массовых операций, особенно на больших наборах данных.

Заменить все значения ошибок на 0, пустые или указанный текст с помощью кода VBA

Для расширенных сценариев, таких как автоматизация очистки для больших листов или многократная обработка конкретных замен ошибок, использование простого макроса VBA может сэкономить время и ручной труд. Ниже вы найдете пошаговые инструкции по использованию VBA для пакетной замены всех значений ошибок в выбранном диапазоне на предпочитаемую альтернативу — 0, пустую ячейку или определенное сообщение.
Этот подход очень масштабируемый и подходит для пользователей, знакомых с базовыми операциями макросов.

1. Запустите редактор Visual Basic for Applications (VBA), щелкнув Разработчик > Visual Basic. В открывшемся редакторе нажмите Вставка > Модуль и скопируйте-вставьте следующий код в пустое окно модуля:

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 для Excel

Связанная статья:

• Как изменить ошибку #ДЕЛ/0! на читаемое сообщение в Excel?