Если вы создаете формулы на своем листе, неизбежно появятся некоторые значения ошибок. Можете ли вы выделить сразу все эти ячейки, содержащие значения ошибок на вашем листе? В Условное форматирование утилита в Excel может помочь вам решить эту проблему.
Выделите все ячейки с ошибками с помощью условного форматирования в Excel
Выберите и выделите все ячейки с ошибками с помощью Kutools for Excel 
Выделите все ячейки с ошибками с помощью условного форматирования в Excel
Выделите все ячейки с ошибками с помощью условного форматирования в ExcelСледующие шаги покажут вам, как подать заявку Условное форматирование чтобы выделить все ячейки, содержащие ошибки.
1.Выберите диапазон или весь лист, к которому вы хотите применить Условное форматирование.
2. Затем нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:
3. В Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант, а затем введите эту формулу = ISERROR (A1) (A1 указывает на первую ячейку в выбранном диапазоне, вы можете изменить ее по своему усмотрению) в Формат значений, где эта формула истинна текстовое окно. Смотрите скриншот:
4. Затем нажмите Формат кнопку, а в Формат ячеек диалоговом окне выберите один цвет, который вы хотите использовать под Заполнять вкладку, см. снимок экрана:
5. Выбрав цвет, нажмите OK > OK чтобы закрыть диалоговые окна, и все ячейки значений ошибки были выделены, как показано на следующем снимке экрана:
Внимание: Условное форматирование Инструмент является динамической функцией, если вы создадите другие значения ошибок в выбранном диапазоне, они также будут выделены.
Выберите и выделите все ячейки с ошибками с помощью Kutools for Excel
Если вы хотите выделить и выделить ячейки ошибок, вы можете использовать Kutools for ExcelАвтора Выберите ячейки со значением ошибки утилиту, а затем выделите их своим любимым цветом.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите ячейки, в которых вы хотите найти ячейки с ошибками, нажмите Кутулс > Выберите > Выберите ячейки со значением ошибки. Смотрите скриншот:
2. Затем выбираются все ячейки с ошибками и нажимаются Главная > Цвет заливки , чтобы выбрать цвет для окрашивания ячеек с ошибками. Смотрите скриншот:
Быстро выбрать все ошибки
Статьи по теме:
Как выделить ячейки с внешними ссылками в Excel?
Как выделить / условное форматирование ячеек с формулами в Excel?
Как выделить четные или нечетные числа в Excel?
Как выбрать ячейки со значениями ошибок в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (1)
Оценок пока нет. Оцените первым!
Скрытие значений и индикаторов ошибок в ячейках
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. -
В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.
> Excel Options >Formulas.Нужна дополнительная помощь?
|
Антон Tpega  Пользователь Сообщений: 7 |
Добрый день, коллеги! На нашел решения на форуме для подобного вопроса. Заранее спасибо! |
|
New Пользователь Сообщений: 4581 |
#2 22.10.2021 12:58:45 не знаю сработает с условным форматированием или нет, но попробуйте так. Вместо D подставьте название своего столбца
|
||
|
Пытливый  Пользователь Сообщений: 4586 |
#Н/Д в одном столбце, или в разных столбцах строк могут быть? Кому решение нужно — тот пример и рисует. |
|
Антон Tpega Пользователь Сообщений: 7 |
|
|
Пытливый Пользователь Сообщений: 4586 |
Если в одном, то можно через УФ решить. Например, у вас диапазон, строки которого должны выделяться А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 Пользователь Сообщений: 4581 |
Антон Tpega, нажмите кнопку F5 (или Ctrl+G) — Выделить… и поиграйтесь с галочками. Когда определите какие галочки вам нужны — включите запись макроса и выделите то, что вам нужно — макрос запишет эту комбинацию |
|
Антон Tpega Пользователь Сообщений: 7 |
Кнопка цитирования не для бездумного копирования [МОДЕРАТОР] Согласно этому описанию, получилось выделить ячейки без #Н/Д Изменено: vikttur — 01.11.2021 09:31:12 |
|
New Пользователь Сообщений: 4581 |
Антон Tpega, |
|
Антон Tpega Пользователь Сообщений: 7 |
#13 01.11.2021 08:41:23 New, |
Excel doesn’t do as good of a job as some text editors when it comes to spellchecking, and we know how frustrating this can be. Let us show you how to highlight misspelled cells in Excel using VBA.
Spelling check feature has been a staple in Microsoft Office products for a long time. However, this can be a bit tricky to use in Excel. CheckSpelling method under the Application object returns a Boolean value based on the value of a specified text. We can use a For Each…Next loop to check every cell in a worksheet. A simple If condition is enough to use the Boolean value that returns from the CheckSpelling method.
After you get the range, you can use the ColorIndex property to highlight that range. Here are some index numbers for colors:
- 3: Red
- 5: Blue
- 6: Yellow
- 0: No Fill
We suggest you to use On Error Resume Next at the start of your code to account for worksheets that don’t have any cells with errors.
Before continuing the codes here is a simple explanation about them. Firstly; you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
Highlighting misspelled cells
Sub HighlightMissspelledCells() Dim rng As Range For Each rng In ActiveSheet.UsedRange If Not Application.CheckSpelling(rng.Text) Then rng.Interior.ColorIndex = 6 End If Next rng End Sub
Выявление элементов в электронной таблице может быть намного быстрее, если вы используете цвета, которые прыгают за пределы экрана. С помощью условного форматирования в Microsoft Excel вы можете упростить поиск пустых ячеек или ошибок формул.
Когда у вас есть электронная таблица, полная данных, которые, как вы ожидаете, заполнят каждую ячейку, вы можете легко пропустить пустые ячейки. И отсутствие данных может быть так же вредно, как и неправильные данные. Вот как можно увидеть пустые ячейки с помощью условного форматирования.
Откройте лист и выберите ячейки, к которым вы хотите применить форматирование. Перейдите на вкладку «Главная» и нажмите «Условное форматирование» в группе «Стили» на ленте. Выберите «Новое правило».
В появившемся окне «Новое правило форматирования» выберите «Форматировать только содержащиеся ячейки» в разделе «Выбрать тип правила» вверху.
Внизу выберите «Пробелы» в раскрывающемся списке «Форматировать только ячейки с». Затем нажмите «Форматировать» справа от предварительного просмотра, чтобы выбрать способ форматирования пустых ячеек.
В окне «Формат ячеек» используйте вкладки вверху «Шрифт», «Граница» и «Заливка», чтобы выбрать форматирование. Нажмите «ОК». В нашем примере мы воспользуемся Заливкой, чтобы окрасить пустые ячейки в ярко-желтый цвет.
Вы вернетесь в окно «Новое правило форматирования», где вы увидите предварительный просмотр форматирования пустых ячеек. Если вас это устраивает, нажмите «ОК», чтобы применить условное форматирование.
После этого вы должны увидеть все пустые ячейки в выбранном диапазоне, выделенные с выбранным форматированием.
Как автоматически выделять ошибки в Excel
Несмотря на то, что Microsoft Excel неплохо сообщает вам об ошибках, они могут быть не заметны, если у вас есть большой лист для прокрутки. Условное форматирование поможет вам быстро увидеть ошибки.
Фактически вы будете следовать тому же процессу, который вы использовали в предыдущем разделе, чтобы выделить пробелы, но с одним отличием.
Перейдите на вкладку «Главная», нажмите «Условное форматирование» и выберите «Новое правило».
В окне «Новое правило форматирования» выберите «Форматировать только содержащиеся ячейки» вверху. Но на этот раз выберите «Ошибки» в раскрывающемся списке «Форматировать только ячейки с» внизу.
Затем нажмите «Форматировать», чтобы выбрать форматирование. В этом примере настройте параметры шрифта, чтобы ячейки с ошибками были жирными и красными. Нажмите «ОК» после выбора форматирования и «ОК» еще раз, чтобы применить правило.
Теперь эти ошибки исчезают с экрана!
Вы можете использовать этот тип форматирования в Microsoft Excel и для многих других целей. Узнайте, как использовать условное форматирование для выделения строки на основе содержимого или как создать индикатор выполнения в Excel.