Как заменить ошибки формул (например, #ЗНАЧ!, #ДЕЛ/0! и другие) на 0, пустые ячейки или заданный текст в Excel?
Пользователи Excel нередко сталкиваются с ошибками формул — такими как #ДЕЛ/0!, #ЗНАЧ!, #ССЫЛ!, #Н/Д, #ЧИСЛО!, #ИМЯ? и #ПУСТО! — в своих таблицах или результатах вычислений. Эти ошибки не только ухудшают читаемость отчётов, но и могут нарушить последующую обработку, анализ и совместное использование данных. Чтобы улучшить представление информации или обеспечить корректную логику дальнейших расчётов, часто требуется заменить все или некоторые типы ошибок на листе на ноль, пустые ячейки или понятную другим пользователям текстовую строку.
В этой статье представлены практичные и простые в использовании решения для поиска и замены ошибок формул (#) в ячейках Excel. Используя приведённую ниже таблицу в качестве примера, мы покажем, как эффективно устранять эти ошибки в соответствии с вашими потребностями и рабочим процессом.
Замените ошибки формул на 0, любые конкретные значения или пустые ячейки
Замена ошибок формул (#) на 0, любые конкретные значения или пустые ячейки с помощью функции IFERROR
Excel предоставляет функцию IFERROR, специально предназначенную для перехвата всех распространённых типов ошибок и позволяющую заменить их любым значением или сообщением по вашему выбору. Это упрощает обработку ошибок при вычислениях и делает лист более наглядным.
Чтобы использовать её, введите =IFERROR(value, value_if_error) в соответствующую ячейку. Если value содержит ошибку, функция вернёт указанное вами значение value_if_error; если же value не является ошибкой, она просто вернёт результат вычисления.
В приведённом выше примере различные ошибки формул, такие как #Н/Д, были заменены либо на пустую ячейку, либо на числовое значение 0, либо на пользовательское текстовое сообщение. Вы можете настроить параметр value_if_error в соответствии с вашими требованиями — как показано ниже: укажите фактическое значение, пустую строку («») для пустой ячейки или описательный текст при необходимости:
Примечание: В формуле =IFERROR(value, value_if_error) аргумент value — это основное выражение или вычисление (может быть формулой или прямой ссылкой), а value_if_error определяет, что отображать, если это выражение приводит к ошибке. Если вы хотите использовать отображаемый текст, заключите его в двойные кавычки («Text»). Для пустой ячейки укажите пустую строку («»), а для нуля или другого числового значения — просто введите число.
Этот подход идеален, когда вы создаёте формулы и хотите быть уверены, что ошибки не появятся в итоговых таблицах, отчётах, панелях мониторинга или при передаче данных другим пользователям. Практический совет: оборачивайте все сложные или нестабильные вычисления в IFERROR — это поможет сохранить безупречный вид листа.
Имейте в виду: если вам нужно обрабатывать только определённые типы ошибок (например, исключительно #Н/Д), используйте IFNA или комбинируйте функции IF с ISERROR/ISERR для более точного контроля. Кроме того, не забудьте скопировать формулу во все нужные ячейки, чтобы охватить весь массив данных.
Замена ошибок формул (#) на конкретные числа с помощью функции ERROR.TYPE
Функция ERROR.TYPE — ещё одна встроенная функция Excel, которая позволяет идентифицировать различные ошибки по уникальным числам, соответствующим каждому типу ошибки. Это особенно полезно, когда нужно точно определить тип ошибки для дальнейшего использования в условной логике формул.
В приведённом ниже примере применение функции ERROR.TYPE в пустой ячейке рядом с ошибкой формулы возвращает код (от 1 до 8).
№ | Ошибки | Формулы | Преобразовано в |
1 | #ПУСТО! | =ERROR.TYPE(#NULL!) | 1 |
2 | #ДЕЛ/0! | =ERROR.TYPE(#DIV/0!) | 2 |
3 | #ЗНАЧ! | =ERROR.TYPE(#VALUE!) | 3 |
4 | #ССЫЛКА! | =ERROR.TYPE(#REF!) | 4 |
5 | #ИМЯ? | =ERROR.TYPE(#NAME?) | 5 |
6 | #ЧИСЛО! | =ERROR.TYPE(#NUM!) | 6 |
7 | #Н/Д | =ERROR.TYPE(#N/A) | 7 |
8 | #ПОЛУЧЕНИЕ_ДАННЫХ | =ERROR.TYPE(#GETTING_DATA) | 8 |
9 | другие | =ERROR.TYPE(1) | #Н/Д |
Использование маркера заполнения 
позволяет применить формулу ERROR.TYPE к диапазону. Однако имейте в виду, что ERROR.TYPE主要用于 анализа или сопоставления типа ошибки, а не для их прямой замены. Обычно её комбинируют с IF или CHOOSE, чтобы выводить более удобные альтернативы. Кроме того, для запоминания каждого кода ошибки может потребоваться обращение к документации или приведённой выше таблице.
Если ваш сценарий предполагает индивидуальную обработку в зависимости от типа ошибки, вы можете использовать функцию ERROR.TYPE внутри формулы IF или CHOOSE, чтобы выводить соответствующее сообщение для каждого конкретного условия ошибки.
Поиск и замена ошибок формул (#) на 0, любые конкретные значения или пустые ячейки с помощью команды «Перейти к»
Этот метод идеально подходит пользователям, которым нужно выполнить пакетную обработку и напрямую перезаписать ячейки с ошибками в уже существующем диапазоне — особенно после завершения вычислений. С помощью встроенной команды Excel «Перейти к» → «Выделить» можно быстро найти все ячейки с ошибками в выделенном диапазоне и заменить их одновременно.
1.Сначала выделите на листе диапазон, содержащий возможные ошибки в формулах.
2. Нажмите клавишу F5на клавиатуре (или)Ctrl + G), чтобы открыть диалоговое окно «Перейти к».
3. Нажмите кнопку «Выделить», чтобы открыть окно параметров «Перейти к» → «Выделить».
4. Выберите только параметр «Формулы» и убедитесь, что внутри него отмечен лишь флажок «Ошибки». Это действие выделит все ячейки с ошибками в выбранном диапазоне.
5. Нажмите OK, и Excel автоматически выделит все ячейки с ошибками.
6.Введите напрямую 0 или любое выбранное вами значение для замены, а затем нажмите Ctrl + Enter, чтобы Excel заполнил этим значением все выделенные ячейки с ошибками.
Чтобы полностью очистить эти ячейки с ошибками, просто выделите их и нажмите клавишу Delete, чтобы оставить ячейки пустыми.
Поиск и замена формульных ошибок # на 0, любые конкретные значения или пустые ячейки с помощью Kutools для Excel
Инструмент Мастер форматирования условий ошибок из Kutools для Excel упрощает работу с ячейками, содержащими ошибки. С его помощью вы легко замените все или только определённые типы ошибок на нули, пустые ячейки или собственные сообщения — идеально для презентаций или дальнейшего редактирования. Особенно удобно для тех, кто не силён в формулах, или при работе с большими и сложными наборами данных.
1. Сначала выделите диапазон, в котором нужно заменить значения ошибок. Затем перейдите в меню и нажмите Kutools > Дополнительно > Мастер форматирования условий ошибок.
2.В диалоговом окне Мастер форматирования условий ошибокнастройте параметры следующим образом:
(1) В разделе Тип ошибки выберите, к каким сообщениям применить действие: Все сообщения об ошибках, Только сообщения об ошибке #N/A или Все сообщения об ошибках, кроме #N/A. Выберите подходящий вариант для вашего сценария.
(2) В разделе Отображение ошибки выберите Нет (пустая ячейка), если вы хотите, чтобы ошибки отображались как пустые ячейки.
Чтобы заменить ошибки нулём или собственным сообщением, выберите Текстовое сообщение и введите «0» или нужный текст в поле.
(3) Нажмите ОК, чтобы применить изменения.
Утилита немедленно применит ваш выбор, заменив все ошибки в выделенном диапазоне в соответствии с заданными настройками. Ниже — визуальные результаты:
Заменить все значения ошибок на пустые
Заменить все значения ошибок на ноль
Заменить все значения ошибок на определённый текст
Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.
Функция «Мастер форматирования условий ошибок» в 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 Sub2. Затем запустите макрос, нажав кнопку 
или клавишу F5 в окне VBA. Когда появится запрос, сначала выберите целевой диапазон, а затем укажите желаемую замену: оставьте поле ввода пустым, чтобы очистить ячейки с ошибками (сделать их пустыми), введите «0», чтобы заменить ошибки нулями, или задайте собственный текст для замены.
- Всегда убедитесь, что выделен именно тот диапазон, который нужно обработать. Изменения применяются немедленно и не подлежат отмене после закрытия файла, поэтому перед массовыми операциями настоятельно рекомендуется создать резервную копию.
- Этот макрос нацелен на все ячейки, содержащие ошибки (типа #ДЕЛ/0!, #ЗНАЧ!, #ССЫЛ! и т.д.). Если вы хотите ограничить замену только определёнными типами ошибок, добавьте дополнительную логику внутри цикла (например,)
If cell.Text = "#Н/Д" Then ...). - Если оставить поле замены пустым, ячейки с ошибками будут очищены и отобразятся как пустые. Для замены числовыми значениями (например, 0) просто введите «0» в поле ввода.
Поиск и замена формульных ошибок # на 0 или пустые ячейки с помощью Kutools для Excel
Связанная статья:
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек