Предположим, что в формулах электронной таблицы есть ожидаемые ошибки, которые не нужно исправлять, но вы хотите улучшить отображение результатов. Существует несколько способов скрыть значения ошибок и индикаторы ошибок в ячейках.
Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 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, чтобы изменить формулу.
- После знака равенства (=) введите IFERROR , а затем открываемую скобку.
IFERROR( - Переместите курсор в конец формулы.
- Введите , 0) — то есть запятая, за которой следует ноль и закрывающая скобка.
Формула =B1/C1 становится =IFERROR(B1/C1,0). - Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.
Применение условного формата
- Выберите ячейку, содержащую ошибку, и на вкладке Главная выберите Условное форматирование.
- Выберите Создать правило.
- В диалоговом окне Новое правило форматирования выберите Форматировать только ячейки, содержащие.
- Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
- Нажмите кнопку Формат .
- Перейдите на вкладку Число , а затем в разделе Категория выберите Пользовательский.
- В поле Тип введите ;; (три точки с запятой), а затем нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это происходит из-за ;;; Пользовательский формат приводит к тому, что любые числа в ячейке не отображаются. Однако фактическое значение (0) по-прежнему хранится в ячейке.
Скрытие значений ошибок путем изменения цвета текста на белый
Используйте следующую процедуру для форматирования ячеек, содержащих ошибки, чтобы текст в этих ячейках отображался белым шрифтом. Это делает текст ошибки в этих ячейках практически невидимым.
- Выделите диапазон ячеек, содержащих значение ошибки.
- На вкладке Главная щелкните стрелку рядом с элементом Условное форматирование , а затем выберите Управление правилами.
Откроется диалоговое окно Диспетчер правил условного форматирования. - Выберите Создать правило.
Откроется диалоговое окно Создание правила форматирования. - В разделе Выбор типа правила выберите Форматировать только ячейки, содержащие.
- В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.
- Выберите Формат, а затем перейдите на вкладку Шрифт .
- Щелкните стрелку, чтобы открыть список Цвет , и в разделе Цвета темы выберите белый цвет.
Отображение прочерка, строки "#Н/Д" или "НД" вместо значения ошибки
В некоторых случаях вы не хотите, чтобы поля ошибок отображались в ячейках, и вы предпочитаете, чтобы вместо этого отображалась текстовая строка, например "#N/A", дефис или строка "NA". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.
Описание функций
IFERROR Эта функция позволяет определить, содержит ли ячейка ошибку или результаты формулы возвращают ошибку.
NA Эта функция используется для возврата строкового #N/A в ячейке. Синтаксис : =NA().
Скрытие значений ошибок в отчете сводной таблицы
Выберите отчет сводной таблицы.
На вкладке Анализ сводной таблицы в группе Сводная таблица щелкните стрелку рядом с элементом Параметры, а затем выберите Параметры.
Выберите вкладку Макет & Формат , а затем выполните одно или несколько из следующих действий:
- Отображение ошибки изменения Установите флажок Отображать значения ошибок в разделе Формат. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Изменение отображения пустой ячейки Установите флажок Показывать пустые ячейки . Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
Скрытие индикаторов ошибок в ячейках
В левом верхнем углу ячейки с формулой, которая возвращает ошибку, появляется треугольник (индикатор ошибки). Чтобы отключить его отображение, выполните указанные ниже действия.
Ячейка с ошибкой в формуле
- На вкладке Файл выберите Параметры и Формулы.
- В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.