Скрытие значений и индикаторов ошибок в ячейках
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, что в формулах с электронными таблицами есть ошибки, которые вы ожидаете и которые не нужно исправлять, но вы хотите улучшить отображение результатов. Существует несколько способов скрытие значений ошибок и индикаторов ошибок в ячейках.
Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 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.
-
В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Антон Tpega Пользователь Сообщений: 7 |
Добрый день, коллеги! На нашел решения на форуме для подобного вопроса. Заранее спасибо! |
New Пользователь Сообщений: 4718 |
#2 22.10.2021 12:58:45 не знаю сработает с условным форматированием или нет, но попробуйте так. Вместо D подставьте название своего столбца
|
||
Пытливый Пользователь Сообщений: 4676 |
#Н/Д в одном столбце, или в разных столбцах строк могут быть? Кому решение нужно — тот пример и рисует. |
Антон Tpega Пользователь Сообщений: 7 |
|
Пытливый Пользователь Сообщений: 4676 |
Если в одном, то можно через УФ решить. Например, у вас диапазон, строки которого должны выделяться А1:Е6. А ошибки #Н/Д появляются в столбце В. Кому решение нужно — тот пример и рисует. |
Антон Tpega Пользователь Сообщений: 7 |
#6 01.11.2021 06:42:46
использовал данную функцию |
||
Антон Tpega Пользователь Сообщений: 7 |
#7 01.11.2021 06:50:46
спасибо, помогло |
||
Антон Tpega Пользователь Сообщений: 7 |
#8 01.11.2021 06:55:04
подскажите, а как теперь выделить все строки без #Н/Д? |
||
vikttur Пользователь Сообщений: 47199 |
Антон Tpega, свои сообщения можно дополнять. Все три можно было разместить в одном |
New Пользователь Сообщений: 4718 |
Антон Tpega, нажмите кнопку F5 (или Ctrl+G) — Выделить… и поиграйтесь с галочками. Когда определите какие галочки вам нужны — включите запись макроса и выделите то, что вам нужно — макрос запишет эту комбинацию |
Антон Tpega Пользователь Сообщений: 7 |
Кнопка цитирования не для бездумного копирования [МОДЕРАТОР] Согласно этому описанию, получилось выделить ячейки без #Н/Д Изменено: vikttur — 01.11.2021 09:31:12 |
New Пользователь Сообщений: 4718 |
Антон Tpega, |
Антон Tpega Пользователь Сообщений: 7 |
#13 01.11.2021 08:41:23 New, |
Если вы создаете формулы на своем листе, неизбежно появятся некоторые значения ошибок. Можете ли вы выделить сразу все эти ячейки, содержащие значения ошибок на вашем листе? В Условное форматирование утилита в Excel может помочь вам решить эту проблему.
Выделите все ячейки с ошибками с помощью условного форматирования в Excel
Выберите и выделите все ячейки с ошибками с помощью Kutools for Excel
Выделите все ячейки с ошибками с помощью условного форматирования в Excel
Следующие шаги покажут вам, как подать заявку Условное форматирование чтобы выделить все ячейки, содержащие ошибки.
1.Выберите диапазон или весь лист, к которому вы хотите применить Условное форматирование.
2. Затем нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:
3. В Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант, а затем введите эту формулу = ISERROR (A1) (A1 указывает на первую ячейку в выбранном диапазоне, вы можете изменить ее по своему усмотрению) в Формат значений, где эта формула истинна текстовое окно. Смотрите скриншот:
4. Затем нажмите Формат кнопку, а в Формат ячеек диалоговом окне выберите один цвет, который вы хотите использовать под Заполнять вкладку, см. снимок экрана:
5. Выбрав цвет, нажмите OK > OK чтобы закрыть диалоговые окна, и все ячейки значений ошибки были выделены, как показано на следующем снимке экрана:
Внимание: Условное форматирование Инструмент является динамической функцией, если вы создадите другие значения ошибок в выбранном диапазоне, они также будут выделены.
Выберите и выделите все ячейки с ошибками с помощью Kutools for Excel
Если вы хотите выделить и выделить ячейки ошибок, вы можете использовать Kutools for ExcelАвтора Выберите ячейки со значением ошибки утилиту, а затем выделите их своим любимым цветом.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите ячейки, в которых вы хотите найти ячейки с ошибками, нажмите Kutools > Выберите > Выберите ячейки со значением ошибки. Смотрите скриншот:
2. Затем выбираются все ячейки с ошибками и нажимаются Главная > Цвет заливки , чтобы выбрать цвет для окрашивания ячеек с ошибками. Смотрите скриншот:
Быстро выбрать все ошибки
Статьи по теме:
Как выделить ячейки с внешними ссылками в Excel?
Как выделить / условное форматирование ячеек с формулами в Excel?
Как выделить четные или нечетные числа в Excel?
Как выбрать ячейки со значениями ошибок в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (1)
Оценок пока нет. Оцените первым!
Используем
Условное форматирование
для подачи сигнала пользователю при вводе ошибочного значения. Под «ошибочным значением» здесь будем понимать значение, не принадлежащее заданному диапазону.
Предположим, что на листе EXCEL производятся некие вычисления. В качестве исходных данных пользователь должен ввести числовой параметр из определенного интервала (например, от 1 до 5). При вводе числа, не принадлежащего этому интервалу, подадим сигнал пользователю с помощью
Условного форматирования
(ячейка будет выделена красным фоном).
Для этого:
- выделите ячейку, в которую пользователем будет вводиться исходный параметр;
-
вызовите инструмент
Условное форматирование
(
);
-
выберите
Форматировать только ячейки, которые содержат;
-
в полях раздела
Форматировать только ячейки, для которых выполняется следующее условие:
выберите
значение ячейки
;
вне
;
и введите:
1
;
5
;
- выберите требуемый формат, например, красный цвет фона.
Теперь при вводе значения вне интервала [1, 5] ячейка будет выделена красным (см.
Файл примера
).
Также контроль ввода неверных данных можно организовать с помощью формул (см. статью
Контроль ввода ОШИБочных значений. Часть 3 — с применением формул
) или
Проверки данных
(см. статью
Контроль ввода ОШИБочных значений. Часть 2 — с применением Проверки данных
). В отличие от
Условного форматирования
и формул, которые лишь информируют о допущенных ошибках ввода, инструмент
Проверка данных
позволяет запретить сам ввод ошибочных значений.
СОВЕТ:
Чтобы найти все ячейки на листе, к которым применены правила
Условного форматирования
необходимо:
-
на вкладке
Главная
в группе
Редактирование
щелкните стрелку рядом с командой
Найти и выделить
; -
выберите в списке пункт
Условное форматирование;
-
будут выделены все ячейки, которым применены правила
Условного форматирования
.
Условное форматирование в Microsoft Excel – одна из базовых функций, управление которой должны освоить все пользователи, создающие или редактирующие электронные таблицы. С помощью условного форматирования вы можете менять оформление ячеек в зависимости от находящихся в них данных. Это может быть подсветка определенных значений, выделение только некоторых или цветной градиент, переходящий от меньшего к большему числу. Настраивается все это вручную, о чем и пойдет речь далее.
Правила выделения ячеек
«Правила выделения ячеек» отвечают за выделение только тех ячеек, которые соответствуют условию. Условие выбирает сам юзер, как и его диапазон.
-
Выделите группу ячеек, к которой хотите применить правило, разверните меню «Условное форматирование» и наведите курсор на «Правила выделения ячеек». Названия всех правил соответствуют их действию. Например, при выборе «Больше» правило затронет только те клетки, значение в которых будет больше указанного. Точно так же работают и остальные варианты.
-
После выбора укажите само число, от которого и должно отталкиваться правило.
-
Затем разверните список с вариантами подсветок и выберите подходящую. Если среди них нет подходящего цвета, всегда можно нажать на «Пользовательский формат» и выбрать другую заливку или цвет текста.
-
Как только изменения вступают в силу, вы сразу же видите результат условного форматирования. При этом правило имеет динамический режим работы, то есть при изменении значения в ячейке изменится и ее подсветка (если она подпадает под действие правила).
-
Если в меню выбора типа правила указать вариант «Другие правила», откроется новое окно, в котором вы можете более детально разобраться с форматированием, выбрать подходящий тип и задать условие. При этом ниже сразу же отобразится образец, от которого стоит отталкиваться при настройке.
С этим правилом все понятно – оно выделяет только конкретные ячейки из списка, попадающие под заданное пользователем условие. Удобно, когда нужно найти в диапазоне определенные числа, дату или текст.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Правила отбора первых и последних значений
Все числа в выбранном диапазоне ячеек имеют последовательность по возрастанию, которая и используется при выборе правила отбора первых и последних значений. Так, вы можете подсвечивать только самые большие или маленькие числа, те, которые выше либо ниже среднего, или попадающие под 10%.
-
Для этого в том же меню условного форматирования наведите курсор на второе правило и ознакомьтесь с его типами. В предыдущем абзаце я уже перечислил их все, поэтому вам остается выбрать только подходящее. Уточню, что под «Первые 10 элементов» подразумевается не их порядок в таблице, а сам результат, записанный в ячейке.
-
Хоть правила и называются «Первые 10…», «Последние 10…», количество ячеек для форматирования вы выбираете самостоятельно, указывая подходящий номер в счетчике. После этого нужно развернуть список с вариантами оформления и выбрать среди них подходящий.
-
Я настроил подсветку первых шести элементов, а это значит, что теперь в моем диапазоне будут подсвечены первые 6 самых больших чисел.
-
Вариант «Выше среднего» или «Ниже среднего» не имеет дополнительных настроек, поскольку среднее число из диапазона определяется автоматически. Вам остается выбрать только стиль форматирования.
-
Как видно на следующем изображении, программа сама определила, какие значения выше среднего в диапазоне, и подсветила их выбранным мною цветом.
Применяйте данные типы правил, когда нужно выделить максимальные значения в диапазоне, минимальные или только те, которые превышают среднее число. При финансовых операциях или при учете склада такой стиль условного форматирования будет особенно полезен.
Гистограммы
Рассмотрим следующее правило под названием «Гистограммы». Оно имеет два разных типа, обеспечивающих градиентную или сплошную заливку. Гистограммы появятся на всех ячейках, но их размер напрямую будет зависеть от величины значения в диапазоне.
-
Наведите курсор на правило «Гистограммы» и выберите подходящий тип оформления. По умолчанию предлагается 12 вариантов.
-
Никаких дополнительных настроек это правило не имеет, поэтому после применения вы сразу видите сформированные гистограммы – от минимального к максимальному значению диапазона.
-
Если выбрать вариант «Другое правило», то вы самостоятельно сможете настроить, какие значения стоит задеть и какую заливку к ним применить. Настройки интуитивно понятны, поэтому не стесняйтесь и экспериментируйте с различными стилями оформления.
Цветовые шкалы
По своему действию тип условного форматирования «Цветовые шкалы» имеет некоторые сходства с предыдущим правилом, однако обеспечивает совершенно другое оформление ячеек. Шкалы формируются из разных цветов и по градиенту можно быстро найти минимальное и максимальное значение в диапазоне.
-
Откройте список всех типов цветовых шкал и выберите среди них подходящий цветовой градиент.
-
После его применения вы сразу увидите результат. Я выбрал тип условного форматирования – от зеленого к красному цвету. Зеленым подсвечиваются минимальные значения, а красным – максимальные. Соответственно, чем выше число, тем краснее будет цвет, начиная от классического зеленого и переходя к салатовому, желтому и так далее.
-
На предложенных градиентах варианты оформления не заканчиваются. Вы можете открыть уже знакомое окно с другими правилами, самостоятельно выбрать шкалу и цвета для нее.
Наборы значков
Последний тип правил условного форматирования, довольно интересный в плане визуального оформления, связан с добавлением значков для диапазона значений. Например, если число больше среднего, появится зеленая стрелка вверх, а если меньше – красная вниз. Есть критерии оценок в виде шкал, круговых диаграмм и звездочек. Все это позволяет определить динамику роста и узнать другую полезную информацию в таблице.
-
Откройте наборы значков и ознакомьтесь со всеми доступными. Присутствуют различные направляющие в виде стрелок, фигуры разных цветов, индикаторы и оценки.
-
Настроек для этого правила по умолчанию нет, поэтому при выборе значки сразу же появляются в своих клетках. Я выбрал шкалу с несколькими делениями, которые заполняются в зависимости от величины значения в текущем диапазоне. Вы же можете протестировать абсолютно все наборы значков.
-
В окне настройки данного правила обязательно укажите тип, а затем выберите любой из типов значков и условия, при которых они будут появляться. Это позволит игнорировать некоторые значения, которые вы бы не хотели подсвечивать.
Создание своего правила
Окно создания правил условного форматирования уже фигурировало в предыдущих разделах статьи, однако затрагивались только определенные типы. Сейчас вкратце рассмотрим то, как осуществляется создание правила с нуля.
-
Разверните «Условное форматирование» и выберите «Создать правило».
-
Выберите из списка тип правила под ваши условия. Для этого достаточно ознакомиться с его описанием, чтобы найти подходящее.
-
Затем посмотрите на второй блок с настройками и настройте само условие форматирования. Например, можно указать значение между конкретными числами.
-
Для определенных типов форматирования поддерживаются настройки шкалы, градиентов и гистограмм точно в таком же виде, как это было рассмотрено выше.
-
Примените свое правило и посмотрите, как теперь отображаются выбранные ячейки. Измените значения в некоторых из них для проверки динамики. В любой момент можно вернуться к настройкам и поменять тип условного форматирования.
Подводя итоги, следует отметить, что условное форматирование в Excel – крайне удобная функция, при помощи которой достигается желаемый вид диапазона ячеек с наглядной демонстрацией выбранного условия. Обязательно используйте эту возможность при оформлении таблицы, создавая свои правила или применяя уже существующие.