Скрытие значений и индикаторов ошибок в ячейках
Предположим, что в формулах с электронными таблицами есть ошибки, которые вы ожидаете и которые не нужно исправлять, но вы хотите улучшить отображение результатов. Существует несколько способов скрытие значений ошибок и индикаторов ошибок в ячейках.
Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 не допускается, и если ввести формулу =1/0, Excel возвращает #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!.
Преобразование ошибки в нулевое значение и использование формата для скрытия значения
Чтобы скрыть значения ошибок, можно преобразовать их, например, в число 0, а затем применить условный формат, позволяющий скрыть значение.
Создание примера ошибки
Откройте чистый лист или создайте новый.
Введите 3 в ячейку B1, в ячейку C1 — 0, а в ячейку A1 — формулу =B1/C1.
The #DIV/0! в ячейке A1.
Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.
После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR(
Переместите курсор в конец формулы.
Введите ,0 ), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится =ЕСЛИERROR(B1/C1 ;0).
Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.
Применение условного формата
Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.
Выберите команду Создать правило.
В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.
Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
Нажмите кнопку Формат.
На вкладке Число в списке Категория выберите пункт (все форматы).
В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.
Для форматирования ячеек с ошибками используйте следующую процедуру, чтобы текст в них отображался белым шрифтом. В этом случае текст ошибки в этих ячейках практически невидим.
Выделите диапазон ячеек, содержащих значение ошибки.
На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования.
Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования.
В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.
Нажмите кнопку Формат и откройте вкладку Шрифт.
Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.
Иногда вы не хотите, чтобы в ячейках появлялись оценки ошибок и вместо них должна отображаться текстовая строка, например "#N/Д", тире или строка "0". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД, как показано в примере ниже.
ЕСЛИERROR С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.
НД Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис = NA().
Выберите отчет сводной таблицы.
Появится область "Инструменты для работы со pivottable".
Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.
Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат выберите значение ошибки Показывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
В левом верхнем углу ячейки с формулой, которая возвращает ошибку, появляется треугольник (индикатор ошибки). Чтобы отключить его отображение, выполните указанные ниже действия.
Ячейка с ошибкой в формуле
В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл > Параметры > Формулы.
In Excel 2007: Click the Microsoft Office button > Excel Options > Formulas.
В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.
Как скрыть значения ошибок в Microsoft Excel
Я бывший учитель математики, которому часто давали любую работу, требующую использования Excel. Таким образом, я научился многим трюкам.Иногда при использовании Micro oft Excel вы обнаруживаете, что в
Содержание:
- Н / Д появляется в электронной таблице Excel
- Скрытие значений ошибок
- Пример
- Метод 1. Использование форматирования для скрытия ошибки
- Использование функции ЕСЛИОШИБКА
- Использование функции = ЕСЛИОШИБКА
- Использование условного форматирования
- Метод 2 — превратить текст в белый цвет
Я бывший учитель математики, которому часто давали любую работу, требующую использования Excel. Таким образом, я научился многим трюкам.
Н / Д появляется в электронной таблице Excel
Скрытие значений ошибок
Иногда при использовании Microsoft Excel вы обнаруживаете, что в одной из ваших ячеек появилось значение ошибки. Это может быть связано с тем, что вы используете формулу, которая пыталась разделить на 0 (в этом случае появится # DIV / 0), или, возможно, ваша формула связана с функцией ВПР, которая не может найти совпадение для ваших данных (в котором case # N / A появится). Это всего лишь два из нескольких кодов ошибок, которые могут появиться в Excel.
Но зачем их скрывать? Почему бы просто не удалить формулу? Что ж, особенно в случае ошибки # N / A, вы, возможно, настроили свою электронную таблицу с формулами, готовыми для данных, которые вы будете вводить в будущем. В этом случае вам нужны готовые формулы, но если на всем листе написано # N / A, это может выглядеть неаккуратно и непрофессионально. В этой статье я приведу несколько различных способов решения этой проблемы.
Пример
Взгляните на таблицу, которую я установил на картинке выше. В таблице показаны восемь школьников и их целевые оценки на конец года (розовый столбец). В столбце «Семестр 1» показаны их фактические оценки за этот семестр. Следующий столбец заполняется формулой, включающей функцию ВПР.
Я использовал функцию ВПР и таблицу справа, чтобы преобразовать буквенные оценки учащихся в числа, чтобы затем я мог отображать разницу между их целевыми и фактическими оценками в столбце под названием «Разница».
Это сработало для большинства учащихся первого семестра. Мы можем быстро увидеть разницу между их целевыми и фактическими оценками, а также определить, кому нужна помощь, кто выше целевого и т. Д.
К сожалению, Джон Сёртиз отсутствовал, когда класс проводил тест в конце семестра, поэтому у него нет фактической оценки за семестр 1. Затем в столбце «Разница» было возвращено # N / A, поскольку формула ВПР не может найти ничего для соответствовать. Термины 2 и 3 столбца одинаковы для всех.
Это выглядит беспорядочно, и я хочу скрыть все сообщения об ошибках, не удаляя формулу из всех этих ячеек.
Давайте посмотрим, как это сделать.
Метод 1. Использование форматирования для скрытия ошибки
Используя функцию ЕСЛИОШИБКА, вы можете преобразовать любые ошибки в числа, а затем использовать условное форматирование, чтобы скрыть их.
Использование функции ЕСЛИОШИБКА
Использование функции = ЕСЛИОШИБКА
Функция = ЕСЛИОШИБКА работает, возвращая элемент по вашему выбору, если ваша формула возвращает ошибку.
В моем примере на картинке выше я ввел следующую формулу:
= ЕСЛИОШИБКА (ВПР ($ C6; $ N $ 2: $ O $ 10,2; ЛОЖЬ) -ВПР (D6; $ N $ 2: $ O $ 10,2; ЛОЖЬ); 100)
У меня та же формула ВПР, что и раньше, заключенная в скобки функции = ЕСЛИОШИБКА:
= ЕСЛИОШИБКА (моя формула,
Затем следует запятая и 100. Я выбрал 100, чтобы Excel возвращал в случае появления ошибки. Я мог бы выбрать что угодно и поставить его после запятой, но вы сразу поймете, почему я выбрал номер.
Окончательная формула выглядит так:
= ЕСЛИОШИБКА (моя формула, 100).
Excel теперь вернет ответ на мою формулу, если она работает, или 100, если она не работает.
Чтобы завершить исправление, нам теперь нужно добавить некоторое форматирование, чтобы любые 100 были скрыты.
Использование условного форматирования
1. Выделите все свои ячейки и нажмите «Условное форматирование», затем «Новое правило».
2. Выберите «Форматировать только ячейки, которые содержат», затем убедитесь, что в раскрывающемся списке под ним указано «равно», и введите рядом с ним «100». (Это указывает Excel форматировать любое поле, содержащее 100).
3. Щелкните "Формат", а затем вкладку "Число". В меню категории нажмите «Пользовательский» и введите «;;;» в поле типа. Это говорит Excel взять любое поле, содержащее 100, и оставить его пустым.
4. Щелкните "ОК". Все ячейки # Н / Д теперь должны быть пустыми, но продолжат работать в обычном режиме, как только вы введете данные, позволяющие формуле иметь ответ.
Метод 2 — превратить текст в белый цвет
Этот метод немного менее удовлетворителен, но намного быстрее и проще. Значок # N / A по-прежнему будет отображаться, но, поскольку текст будет белым, вы не сможете его увидеть.
1. На главной вкладке выберите «Условное форматирование», а затем нажмите «Новое правило», как мы это делали в способе 1.
2. Выберите «Форматировать только содержащиеся ячейки», а затем выберите «Ошибки» в раскрывающемся списке.
3. Щелкните «Форматировать» и перейдите на вкладку «Шрифт». В раскрывающемся цветном меню измените его на «белый» (или в зависимости от цвета фона вашей ячейки), а затем нажмите «ОК».
Ваши сообщения об ошибках исчезнут.
Эта статья точна и правдива, насколько известно автору. Контент предназначен только для информационных или развлекательных целей и не заменяет личного или профессионального совета по деловым, финансовым, юридическим или техническим вопросам.