Как найти и выделить ячейки с условным форматированием
Иногда нужно найти ячейки с условным форматированием. Ведь Excel ни как их не отмечает, они визуально могут ничем не отличаться от обычных ячеек. Но есть очень легкий способ быстро найти ячейки, к которым присвоено условное форматирование.
Поиск ячеек с условным форматированием
Excel предлагает на выбор сразу 2 способа:
- Сразу найти все ячейки с активным условным форматированием.
- Щелкнуть по любой ячейке и автоматически выделить только те, которые имеют те самые условия.
Сначала разберем первый способ:
Допустим у нас есть таблица с условным форматированием. Но так как пока ни одно значение в таблице не соответствует критериям правил в условном форматировании, то ячейки не отличаются по формату между собой.
- Выберите инструмент: «Главная»-«Редактирование»-«Найти и выделить»-«Перейти» (или нажмите на клавишу F5 на клавиатуре или CTRL+G).
- В появившемся окне «Переход» нажмите на кнопку «Выделить».
- В окне «Выделение группы ячеек» укажите на опцию «условные форматы» и сразу будут доступны ниже еще 2 опции, в которых должно быть отмечено «всех».
- Нажмите «ОК».
В результате на листе Excel будут выделены все диапазоны ячеек с условным форматированием.
Как выделить ячейки с одинаковым условным форматированием
Второй способ позволяет нам найти ячейки, на которые наложено такое же условное форматирование с такими же критериями:
- Выделите исходную ячейку (например, B2).
- Снова откройте диалоговое окно «Переход» клавишей F5 и там же нажмите на кнопку «Выделить».
- В этот раз отмечаем опцию «условные форматы», но с дополнительной опцией «этих же».
Как вы можете наблюдать, автоматически отметились диапазоны с одинаковыми критериями условного форматирования.
Теперь глядя на данную таблицу отчета можно предположить, что планы продаж установлены относительно количества проданного товара и не выражаются в денежном эквиваленте.
Главное преимущество данных уроков по условному форматированию в том, что все примеры можно с легкостью применить для своих данных и отчетов в Excel.
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Access 2007 Еще…Меньше
С помощью команды Перейти можно быстро найти и выбрать все ячейки, содержащие определенные типы данных, например формулы. Кроме того, с помощью функции Перейти можно найти только ячейки, которые соответствуют определенным условиям, например последнюю ячейку на лист, которая содержит данные или форматирование.
Сделайте следующее:
-
Для начала с одного из следующих начните:
-
Чтобы найти конкретные ячейки на всем ячеек, щелкните любую из них.
-
Чтобы найти определенные ячейки в определенной области, выберите нужные диапазон, строки или столбцы. Дополнительные сведения см. в статье Выбор ячеек, диапазонов, строк и столбцов на сайте.
Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.
-
-
На вкладке Главная нажмите кнопку & выберите > Перейти (в группе Редактирование).
Сочетания клавиш: Нажмите CTRL+G.
-
Нажмите кнопку Дополнительный.
-
В диалоговом окне Перейти к специальным выберите один из следующих параметров:
|
Команда |
Чтобы выделить |
|---|---|
|
Примечания |
Ячейки с прикомментами. |
|
Константы |
Ячейки, содержащие константы. |
|
Формулы |
Ячейки, содержащие формулы. Примечание: Тип формулы определяется флажками под формулами. |
|
Пробелы |
Пустые ячейки. |
|
Текущая область |
Список текущая область, например весь список. |
|
Текущий массив |
Если массив массив содержится активная ячейка массиве, это может быть целая активная ячейка. |
|
Объекты |
Графические объекты, в том числе диаграммы и кнопки, на месте и в текстовых полях. |
|
Различия между строками |
Все ячейки, отличающиеся от активной ячейки в выбранной строке. В области выделения всегда есть одна активная ячейка , будь то диапазон, строка или столбец. Нажимая клавишу ВВОД или TAB, можно изменить расположение активной ячейки, которая по умолчанию является первой ячейкой в строке. Если выбрано несколько строк, для каждой отдельной строки этого выделения будет сравнено сравнение, а ячейка, используемая для сравнения для каждой дополнительной строки, будет расположена в том же столбце, что и активная ячейка. |
|
Различия между столбцами |
Все ячейки, отличающиеся от активной ячейки в выбранном столбце. В области выделения всегда есть одна активная ячейка, будь то диапазон, строка или столбец. Нажимая клавишу ВВОД или TAB, можно изменить расположение активной ячейки , которая по умолчанию является первой ячейкой в столбце. Если выбрано несколько столбцов, сравнение делается для каждого отдельного столбца в этом столбце. Ячейка, используемая для сравнения для каждого дополнительного столбца, находится в той же строке, что и активная ячейка. |
|
Прецеденты |
Ячейки, на которые ссылается формула в активной ячейке. В области Зависимыесделайте следующее:
|
|
Иждивенцев |
Ячейки с формулами, которые ссылаются на активную ячейку. Выполните одно из указанных ниже действий.
|
|
Последняя ячейка |
Последняя ячейка на этом сайте с данными или форматированием. |
|
Только видимые ячейки |
Только ячейки, видимые в диапазоне, который пересекает скрытые строки или столбцы. |
|
Условные форматы |
Применяются только ячейки с условным форматированием. В области Проверка данныхсделайте следующее:
|
|
Проверка данных |
Только ячейки с примененными правилами проверки данных. Выполните одно из указанных ниже действий.
|
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Вы можете отформатировать некоторые ячейки на листе, и теперь вы хотите найти и выбрать эти ячейки в указанном диапазоне. В этой статье будет показано несколько способов выбора ячеек с аналогичным форматированием и выбора всех отформатированных ячеек в Excel.
Выделение ячеек с похожим форматированием с помощью функции поиска и замены
Выберите ячейки с похожим форматированием, Kutools for Excel
Выберите все отформатированные ячейки, Kutools for Excel
Выделение ячеек с похожим форматированием с помощью функции поиска и замены
Выделение ячеек с похожим форматированием с помощью функции поиска и заменыФункция «Найти и заменить» Microsoft Excel поддерживает поиск ячеек со специальным форматированием. Поэтому с помощью этой функции мы можем легко выбирать ячейки с аналогичным форматированием в Excel.
1. Выберите диапазон, в котором вы будете выбирать ячейки с аналогичным форматированием, и нажмите Ctrl + F одновременно с клавишами, чтобы открыть диалоговое окно «Найти и заменить».
2. В открывшемся диалоговом окне «Найти и заменить» щелкните значок Опции кнопку, чтобы развернуть диалоговое окно, а затем нажмите кнопку Формат кнопку.
3. в Найти формат диалоговом окне выберите указанное форматирование, с помощью которого вы будете искать ячейки, и нажмите кнопку OK кнопку.
В нашем примере мы проверяем зеленый цвет фона на вкладке «Заливка». См. Снимок экрана ниже:
4. Теперь вы вернетесь в диалоговое окно «Найти и заменить». Пожалуйста, нажмите на Найти все кнопку.
Затем вы увидите, что все ячейки в выбранном диапазоне с указанным форматированием перечислены в нижней части диалогового окна «Найти и заменить».
5. Выберите все результаты поиска в диалоговом окне «Найти и заменить», нажав кнопку Ctrl + A одновременно, и вы увидите, что все найденные ячейки выделены в диапазоне. См. Снимок экрана выше.
Выберите ячейки с похожим форматированием, Kutools for Excel
Если у вас есть Kutools for Excel установлен, его Выбрать ячейки с форматом Утилита также поможет вам легко выделить ячейки с похожим форматированием в Excel.
1. Выберите диапазон, в котором вы будете выбирать ячейки с аналогичным форматированием, и щелкните значок Кутулс > Выберите > Выбрать ячейки с форматом.
2. В открывшемся диалоговом окне «Выбор ячеек с форматом» вам необходимо:
(1) Щелкните значок Выбрать формат из ячейки кнопку, чтобы открыть другое диалоговое окно Выбрать ячейки с форматированием, выберите ячейку, содержащую указанное форматирование, которое вы будете искать, и нажмите кнопку OK кнопку.
(2) Снимите флажок Тип вариант;
(3) Проверяйте только указанное форматирование, которое вы будете искать. В нашем случае мы проверяем Цвет фона опцию.
3. Нажмите Ok кнопку.
Затем появится диалоговое окно, в котором указано, сколько ячеек будет выбрано. Просто нажмите на Да кнопку.
Затем в выбранном диапазоне выбираются сразу все ячейки с заданным форматированием.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Выберите все отформатированные ячейки, Kutools for Excel
Если вам нужно выбрать все отформатированные ячейки, кроме простых ячеек в диапазоне, вы можете сделать это с помощью Kutools for ExcelАвтора Выбрать ячейки с форматом полезности и Выбрать помощника по диапазону утилита. Пожалуйста, сделайте следующее:
1. Выберите диапазон, в котором будут выбраны все отформатированные ячейки, и щелкните значок Кутулс > Выберите > Выбрать ячейки с форматом.
2. В дебюте Выбрать ячейки с форматом диалоговое окно, вам необходимо:
(1) Нажмите Выбрать формат из ячейки и выберите ячейку без форматирования.
(2) В формате напишите коробка, снимите отметку Число вариант и центровка вариант, а затем отметьте все шрифт вариантов, Заполнять варианты и Protection параметры. См. Снимок экрана.
3. Нажмите Ok кнопка. Затем появится диалоговое окно, в котором указано, сколько ячеек будет выбрано. Просто нажмите на Да кнопку.
Теперь все ячейки без форматирования выбираются в указанном диапазоне.
4. Нажмите Кутулс > Выберите > Выбрать помощника по диапазону.
5. В открывшемся диалоговом окне Select Range Helper установите флажок Обратный выбор вариант, а затем выберите исходный диапазон, который вы выбрали на первом шаге.
Тогда вы увидите, что все ячейки со специальным форматированием сразу выбраны в указанном диапазоне. См. Снимок экрана ниже:
6. Закрой Выбрать помощника по диапазону диалоговое окно.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Достаточно часто в таблицах бывает такая ошибка, когда формула случайно заменяется значением. Этот тип ошибок обычно трудно определяется, особенно если лист содержит большое количество формул. В этом приеме описывается два способа быстро идентифицировать формулы в листе, выделяя их. Затем при уменьшении масштаба вы могли бы определить пробелы в группах формул.
Использование окна Выделение группы ячеек
Этот метод выявления ячеек с формулами легкий, но не динамический. Другими словами, он хорош для единичной проверки.
- Выберите одну ячейку в листе.
- Выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек для открытия диалогового окна Выделение группы ячеек.
- В окне Выделение группы ячеек установите переключатель в положение формулы и убедитесь, что все флажки ниже установлены.
- Нажмите кнопку ОК. Excel выберет все ячейки с формулами.
- Нажмите кнопку Цвет заливки в группе Шрифт вкладки Главная. Выберите любой цвет, который еще не используется.
- Используйте элемент управления Масштаб и задайте для своего листа небольшой масштаб (например, 25%).
- Внимательно проверьте лист и посмотрите, какие ячейки остались невыделенными. Возможно, это формула, которая была перезаписана значением.
Если вы не делали никаких изменений, то можете нажать кнопку Отменить (или нажать Ctrl+Z) для отмены цветовой заливки, которую применили в шаге 6.
Использование условного форматирования
Этот метод определения ячеек с формулами потребует небольшой настройки, но он имеет явное преимущество по сравнению с предыдущим, так как является динамическим. Ячейки с формулами определяются сразу, как только заполняются.
Чтобы настроить условное форматирование, выполните следующие действия.
- Выберите Формулы ► Определенные имена ► Присвоить имя для открытия диалогового окна Создание имени.
- В окне Создание имени введите следующую строку в поле Имя:
CellHasFormula. - Введите такую формулу в поле Диапазон:
=ПОЛУЧИТЬ.ЯЧЕЙКУ(48;ДВССЫЛ("rc";ЛОЖЬ)). - Нажмите кнопку ОК, чтобы закрыть диалоговое окно Создание имени.
- Выделите все ячейки, к которым хотите применить условное форматирование. Как правило, они составляют диапазон от А1 до правого нижнего угла используемой области листа.
- Выберите Главная ► Стили ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
- В верхней части окна выберите пункт Использовать формулу для определения форматируемых ячеек.
- Введите следующую формулу в поле диалогового окна (рис. 196.1):
=CellHasFormula. - Нажмите кнопку Формат для открытия диалогового окна Формат ячеек и выберите тип форматирования для ячеек, содержащих формулу.
- Нажмите кнопку OК, чтобы закрыть окно Формат ячеек, и снова нажмите OК, чтобы закрыть окно Создание правила форматирования.
Рис. 196.1. Окно для установки условного форматирования для выделения ячеек с формулами
После выполнения этих шагов каждая ячейка, которая содержит формулу и находится в пределах выбранного в шаге 5 диапазона, будет отформатирована по вашему выбору. Кроме того, при вводе формулы в диапазон, для которого задано условное форматирование, ячейка немедленно поменяет вид. Это форматирование позволяет вам легко определить ячейку, которая должна содержать формулу, но не содержит.
Формула, которую вы вводили в шаге 3, — макрос XLM. Следовательно, вам необходимо сохранить книгу с расширением с поддержкой макросов (используя расширение XLSM). Если вы сохраните книгу в виде XLSX-файла, Excel удалит имя CellHasFormula.
На листах книги может использоваться одновременно сколько угодно правил условного форматирования. Для удобства просмотра и управления правилами можно включить «Диспетчер правил условного форматирования».
- В окне открытой книги перейдите к вкладке «Главная».
- В группе «Стили» раскройте меню кнопки «Условное форматирование» и выберите пункт «Управление правилами».
- В окне «Диспетчер правил условного форматирования» (рис. 3.42) в графе «Показать правила форматирования для:» раскройте список и выберите фрагмент или лист для просмотра использованных правил условного форматирования.
- Для корректировки правила необходимо выбрать его из списка и щелкнуть по кнопке «Изменить правило».
- Затем в окне «Изменение правила форматирования» введите новые правила форматирования и закройте окно кнопкой «ОК».
- Для перемещения выбранного правила по списку правил используйте кнопки «Вверх» и «Вниз».
- Для удаления выбранного правила используйте кнопку «Удалить правило».
- После нужных действий закройте окно кнопкой «ОК».
Рис. 3.42. Окно «Диспетчер правил условного форматирования»
Как быстро перенести условное форматирование на другие ячейки?
- В окне открытого листа выделите ячейку с созданным условным форматированием.
- Перейдите к вкладке «Главная» и в группе «Буфер обмена» щелкните по кнопке «Формат по образцу» (рис. 3.43).
- При нажатой левой кнопке протащите курсором по нужным ячейкам, к которым необходимо применить условное форматирование.
Рис. 3.43. Вкладка «Главная». Кнопка «Формат по образцу»
Как найти и выделить все ячейки на листе с условным форматированием?
- В окне открытого листа перейдите к вкладке «Главная».
- В группе «Редактирование» раскройте меню кнопки «Найти и выделить» и выберите пункт «Условное форматирование» (рис. 3.44).
- На табличном поле листа выделятся все ячейки с условным форматированием.
Рис. 3.44. Вкладка «Главная». Меню кнопки «Найти и выделить». Пункт «Условное форматирование»
Как найти и выделить ячейки на листе с нужным условным форматированием?
- В окне открытого листа выделите ячейку с нужным форматированием и перейдите к вкладке «Главная».
- В группе «Редактирование» раскройте меню кнопки «Найти и выделить» и выберите пункт «Перейти».
- В окне «Переход» нажмите кнопку «Выделить».
- В окне «Выделение группы ячеек» активируйте пункт «Условные форматы».
- Для поиска ячеек с одинаковым условным форматированием включите пункт «Этих же».
- Закройте окно кнопкой «ОК».