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