Скрытие значений и индикаторов ошибок в ячейках
Applies ToExcel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 запрещено, и если ввести формулу =1/0, Excel вернет #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!и #VALUE!.

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

Чтобы скрыть значения ошибок, можно преобразовать их, например, в число 0, а затем применить условный формат, позволяющий скрыть значение.

Создание примера ошибки

  1. Откройте чистый лист или создайте новый.

  2. Введите 3 в ячейку B1, введите 0 в ячейку C1, а в ячейке A1 введите формулу =B1/C1.#DIV/0! в ячейке A1 отображается ошибка.

  3. Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

  4. После знака равенства (=) введите IFERROR , а затем открываемую скобку.IFERROR(

  5. Переместите курсор в конец формулы.

  6. Введите , 0) — то есть запятая, за которой следует ноль и закрывающая скобка.Формула =B1/C1 становится =IFERROR(B1/C1,0).

  7. Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.Теперь содержимое ячейки должно отображать 0 вместо #DIV! должно отображаться значение 0.

Применение условного формата

  1. Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.

  2. Выберите команду Создать правило.

  3. В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.

  4. Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

  5. Нажмите кнопку Формат.

  6. На вкладке Число в списке Категория выберите пункт (все форматы).

  7. В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз.Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

  1. Выделите диапазон ячеек, содержащих значение ошибки.

  2. На вкладке Главная щелкните стрелку рядом с элементом Условное форматирование и щелкните Управление правилами.Откроется диалоговое окно Диспетчер правил условного форматирования .

  3. Выберите команду Создать правило.Откроется диалоговое окно Новое правило форматирования .

  4. В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.

  5. В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.

  6. Нажмите кнопку Формат и откройте вкладку Шрифт.

  7. Щелкните стрелку, чтобы открыть список Цвет , и в разделе Цвета темы выберите белый цвет.

В некоторых случаях вы не хотите, чтобы поля ошибок отображались в ячейках, и вы предпочитаете, чтобы вместо этого отображалась текстовая строка, например "#N/A", дефис или строка "NA". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.

Пример

Описание функций

IFERROR     С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.

Н/Д    Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис : =NA().

  1. Выберите отчет сводной таблицы.

  2. На вкладке Анализ сводной таблицы в группе Сводная таблица щелкните стрелку рядом с элементом Параметры и выберите пункт Параметры.

  3. Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

    • Изменение способа отображения ошибок.     Установите флажок Отображать значения ошибок в разделе Формат. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.

    • Изменение способа отображения пустых ячеек     Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

В левом верхнем углу ячейки с формулой, которая возвращает ошибку, появляется треугольник (индикатор ошибки). Чтобы отключить его отображение, выполните указанные ниже действия.

Ячейка с неправильной формулой

Ячейка с ошибкой в формуле

  1. На вкладке Файл выберите Параметры и Формулы.

  2. В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

См. также

ЕСЛИОШИБКА

Функция НД

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.