Предположим, что в формулах электронных таблиц есть ошибки, которые вы ожидаете и которые не нужно исправлять, но вы хотите улучшить отображение результатов. Существует несколько способов скрыть значения и индикаторы ошибок в ячейках.
Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 запрещено, и если ввести формулу =1/0, Excel вернет #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, и #VALUE!.
Преобразование ошибки в нулевое значение и использование формата для скрытия значения
Чтобы скрыть значения ошибок, можно преобразовать их, например, в число 0, а затем применить условный формат, позволяющий скрыть значение.
Создание примера ошибки
- Откройте чистый лист или создайте новый.
- Введите 3 в ячейку B1, 0 в ячейку C1, а в ячейку A1 введите формулу =B1/C1.
Турнир #DIV/0! отображается в ячейке A1. - Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.
- После знака равенства (=) введите функцию ЕСЛИОШИБКА, а затем открывающую круглую скобку.
ЕСЛИОШИБКА( - Переместите курсор в конец формулы.
- Тип ,0) – то есть запятая, за которой следуют нуль и закрывающая круглая скобка.
Формула =B1/C1 меняется на =ЕСЛИОШИБКА(B1/C1;0). - Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.
Применение условного формата
- Выделите ячейку с ошибкой и на вкладке " Главная " выберите "Условное форматирование".
- Выберите "Новое правило".
- В диалоговом окне "Создание правила форматирования " установите флажок "Форматировать только ячейки, которые содержат".
- Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
- Нажмите кнопку "Формат ".
- Откройте вкладку "Число ", а затем в разделе "Категория" выберите "Настраиваемый".
- В поле "Тип" введите ;;; (три точки с запятой), а затем нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это происходит из-за того, что кнопка ;;; При использовании пользовательского формата числа в ячейке не отображаются. Однако фактическое значение (0) по-прежнему хранится в ячейке.
Скрытие значений ошибок путем изменения цвета текста на белый
Чтобы отформатировать ячейки с ошибками, сделайте следующее: текст в них отображается белым шрифтом. Это делает текст ошибки в этих ячейках практически невидимым.
- Выделите диапазон ячеек, содержащих значение ошибки.
- На вкладке " Главная " щелкните стрелку рядом с кнопкой "Условное форматирование " и выберите пункт "Управление правилами".
Откроется диалоговое окно Диспетчер правил условного форматирования. -
Выберите "Новое правило".
Откроется диалоговое окно Создание правила форматирования. - В группе "Выберите тип правила" выберите пункт "Форматировать только ячейки, которые содержатся".
- В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.
- Выберите пункт "Формат" и откройте вкладку "Шрифт".
- Нажмите стрелку, чтобы открыть список цветов , и в разделе "Цвета темы" выберите белый цвет.
Отображение прочерка, строки "#Н/Д" или "НД" вместо значения ошибки
Бывают случаи, когда вы не хотите, чтобы в ячейках отображались долины ошибок, и предпочитаете, чтобы вместо них отображалась текстовая строка, например "#N/A", тире или строка "Н/Д". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.
Описание функций
ЕСЛИОШИБКА Эта функция используется для определения того, содержит ли ячейка ошибку или результаты формулы возвращают ошибку.
Н/Д Эта функция возвращает строку #N/A в ячейке. Синтаксис: =NA().
Скрытие значений ошибок в отчете сводной таблицы
Выберите отчет сводной таблицы.
На вкладке " Анализ сводной таблицы " в группе " Сводная таблица " щелкните стрелку рядом с кнопкой "Параметры" и выберите пункт "Параметры".
Откройте вкладку "Макет & Формат " и выполните одно или несколько из указанных ниже действий.
- Изменение отображения ошибок В разделе "Формат" установите флажок "Для значений ошибок" установите флажок "Проверка". Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Изменение отображения пустой ячейки Установите флажок Для пустых ячеек показывать проверку. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Скрытие индикаторов ошибок в ячейках
В левом верхнем углу ячейки с формулой, которая возвращает ошибку, появляется треугольник (индикатор ошибки). Чтобы отключить его отображение, выполните указанные ниже действия.
Ячейка с ошибкой в формуле
- На вкладке "Файл " нажмите "Параметры " и выберите "Формулы".
- В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.