Скрытие значений и индикаторов ошибок в ячейках

Скрытие значений и индикаторов ошибок в ячейках

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

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

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

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

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

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

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

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

  4. После знака равенства (=) введите ЕСЛИОШИБКА с открывающей круглой скобкой:
    ЕСЛИОШИБКА(

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

  6. Введите ,0), т. е. запятую с нулем и закрывающей круглой скобкой.
     Формула =B1/C1 примет вид =ЕСЛИОШИБКА(B1/C1,0).

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

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

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

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

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

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

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

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

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

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

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

  2. На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
     Появится диалоговое окно Диспетчер правил условного форматирования.

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

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

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

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

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

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

Пример

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

ЕСЛИОШИБКА   . С помощью этой функции можно определить, содержит ли ячейка или возвращает ли формула ошибку.

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

  1. Щелкните отчет сводной таблицы.
     Появится вкладка Работа со сводными таблицами.

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

    Excel 2010 и Excel 2007: на вкладке Параметры в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.

  3. Откройте вкладку Разметка и формат и выполните одно или оба указанных ниже действия.

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

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

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

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

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

  1. В Excel 2016, Excel 2013 и Excel 2010: выберите Файл > Параметры >Формулы.

    В Excel 2007 нажмите кнопку Microsoft Office  Изображение кнопки Office и выберите пункты Параметры Excel > Формулы.

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

См. также

Функция ЕСЛИОШИБКА

Функция НД

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×