Как найти нужное слово в таблице в excel и выделить его цветом

Обновлено: 03.07.2024

Условное форматирование обычно проверяет значение в одной ячейке и применяет форматирование к другим ячейкам. Отличным применением условного форматирования является выделение всей строки или нескольких строк на основе значения ячейки и условия, указанного в формуле.

Это очень полезно, потому что набор данных с огромным количеством ценности становится громоздким для анализа, просто читая данные. Итак, если мы выделим несколько строк на основе некоторых условий, пользователю станет легче делать выводы из набора данных. Например, в колледже некоторые студенты были занесены в черный список из-за какой-то незаконной деятельности. Таким образом, в записи Excel администратор может выделить строки, в которых присутствуют эти записи учащихся.

В этой статье мы рассмотрим, как выделить строки на основе значений ячеек, используя подходящий пример в реальном времени, показанный ниже:

Пример. Рассмотрим данные о сотрудниках компании. Следующая таблица состоит из данных о проектах, назначенных сотрудникам, их возрасте и идентификаторе. Сотрудник может работать над несколькими проектами. Пустая ячейка в проекте означает, что этому сотруднику не назначен ни один проект.

Выделение строк

<р>1. На основе совпадения текста:

Цель: выделить все строки, где имя сотрудника — «Сришти».

Шаги:

<р>1. В нашем случае выберите весь набор данных от A3 до D14.

<р>2. На вкладке «Главная» выберите «Условное форматирование». Откроется раскрывающееся меню.

<р>3. Выберите «Новое правило» в раскрывающемся списке. Откроется диалоговое окно.

<р>4. В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек» в параметре «Выбрать тип правила».

<р>5. В поле формулы напишите формулу:

Сначала формула проверяет, присутствует ли имя «Сришти» в ячейке B2. Теперь, поскольку ячейка B заблокирована, в следующий раз проверка будет выполняться из ячейки B3 и так далее, пока не будет выполнено условие.

<р>6. В поле «Предварительный просмотр» выберите «Формат», затем перейдите к «Заливка», затем выберите подходящий цвет для выделения и нажмите «ОК».

<р>7. Теперь нажмите OK, и строки будут выделены.

Выделенные строки

<р>2. Нетекстовое сопоставление на основе числа:

Цель: выделить все строки, в которых возраст меньше 25 лет.

Подход:

Повторите описанные выше шаги, как описано, и в формуле напишите:

Выделенные строки

<р>3. На основе условия ИЛИ/И:

ИЛИ, И используются, когда у нас есть несколько условий. Это логические операторы, которые работают со значением True.

И: если все условия ИСТИНА, И возвращает ИСТИНА.
ИЛИ: хотя бы одно из условий должно быть ИСТИНА, чтобы вернуть значение ИСТИНА.

Цель: выделить все строки сотрудников, которые работают над проектом 1 или над проектом 4.

Подробности проекта указаны в столбце D. Таким образом, формула будет выглядеть так:

Цель: Предположим, сотрудник Ришаб завершил проект P-3. Администратора просят выделить строку и вести учет завершенных проектов.

Название находится в столбце B, а сведения о проекте — в столбце D. Формула будет следующей:

Выделенная строка

<р>4. На основе любой пустой строки:

Цель: проверить, есть ли пустые строки. Если существуют, то выделите их.

Сведения о проекте приведены в столбце D. Мы будем использовать функцию СЧЁТЕСЛИ( ) для проверки количества пустых записей. Формула будет:

Приведенная выше формула проверяет все столбцы один за другим и определяет, есть ли хотя бы одна пустая строка. Если значение формулы больше нуля, будет выполнено выделение, иначе СЧЁТЕСЛИ вернёт значение ЛОЖЬ и выделение не будет выполнено.

<р>5. На основе нескольких условий, и каждое условие имеет разный цвет:

Цель. Предположим, компания хочет различать сотрудников по возрасту. Сотрудники, возраст которых превышает 25 лет, являются старшими сотрудниками, а чей возраст превышает 20, но меньше или равен 25 годам, — младшими разработчиками или стажерами. Итак, администратора просят выделить эти две категории разными цветами.

Реализация:

В поле Формула напишите формулу:

При этом будут выделены все строки, в которых возраст превышает 25 лет.

Снова в поле Формула напишите формулу:

Это выделит все строки больше 20. На самом деле это изменит цвет строк, где возраст больше 25. Так как, если число больше 20, то оно определенно больше 25. Таким образом, все ячейки старше 20 лет будут выделены тем же цветом.

Отмените предыдущие шаги, нажав CTRL+Z.
Выберите весь набор данных.
Перейдите к разделу «Условное форматирование», а затем «Управление правилами».

Необходимо изменить порядок условия. Верхнее условие будет иметь больший приоритет, чем нижнее. Итак, нам нужно переместить второе условие в начало первого с помощью значка вверх после выбора условия.

Можно заметить, что строки теперь разделены на две категории: желтый цвет предназначен для сотрудников старшего уровня, а зеленый цвет — для младших сотрудников и стажеров в компании в зависимости от их возраста.

Например, у нас есть таблица покупок в Excel, теперь мы хотим узнать заказы на покупку яблока, а затем выделить все строки, в которых находятся заказы яблока, как показано на снимке экрана слева. Мы можем легко сделать это с помощью команды условного форматирования или функций Kutools for Excel в Excel.

Выделить строку, если ячейка содержит определенный текст/значение/пробел с условным форматированием

Чтобы выделить целые строки ячеек, содержащих определенный текст, значение или просто пустые, с помощью команды условного форматирования в Excel, вы можете сделать следующее:

<р>1. Выберите таблицу покупок без заголовков столбцов.

<р>2. Щелкните Главная > Условное форматирование > Новое правило. См. первый снимок экрана ниже:

<р>3. В появившемся диалоговом окне «Новое правило форматирования» (см. второй снимок экрана выше):
(1) Щелкните, чтобы выбрать «Использовать формулу для определения форматируемых ячеек» в поле «Выбрать тип правила»;
(2) В поле Формат значений, где эта формула верна, введите =$B2="Apple" ;
(3) Нажмите кнопку Формат.
Примечания.
(1) В формуле =$B2="Apple" $B2 – это ячейка, которую вы проверяете, содержит ли она конкретный текст или значение, а " Apple» — это конкретный текст, вы можете изменить их по своему усмотрению. И эта формула может обнаруживать только ячейки, содержащие определенный текст или значение.
(2) Если вы хотите выделить строки, если ячейки пусты, введите =$B2="" на шаге 3-(2).
(3) Если вы хотите выделить строки, если ячейки начинаются с определенного текста, вам нужно ввести =LEFT($B2,5)="Apple" ; или, чтобы выделить строки, если ячейки заканчиваются определенным текстом, введите =RIGHT($B2,5)="Apple" .

<р>4. Теперь открывается диалоговое окно «Формат ячеек». Перейдите на вкладку «Заливка», укажите цвет фона и нажмите кнопку «ОК».

<р>5. Нажмите кнопку «ОК», чтобы закрыть диалоговое окно «Новое правило форматирования».

Затем выделяются все строки, содержащие определенные ячейки содержимого в выбранном диапазоне.

Выбрать целые строки, если ячейки содержат определенный текст/значения в Excel

Kutools для Excel Функция «Выбрать определенные ячейки» может помочь вам быстро выбрать ячейки, целые строки или целые столбцы, если ячейки содержат определенное значение в Excel. Полнофункциональная бесплатная пробная версия на 30 дней!

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

Выделите строку, если ячейка содержит определенный текст/значение с помощью Kutools for Excel

С помощью функции Kutools for Excel «Выбрать определенные ячейки» мы можем выбирать строки, если ячейки содержат определенный текст или значение, а затем легко выделять эти строки в Excel.

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — объединяет более 300 расширенных функций и инструментов для Microsoft Excel

<р>1. Выберите столбец, в котором вы найдете ячейки, содержащие определенный текст или значение.

<р>2. Нажмите Kutools > Выбрать > Выбрать определенные ячейки.

<р>3. В открывшемся диалоговом окне «Выбор определенных ячеек» (см. снимок экрана выше)
(1) установите флажок «Вся строка» в разделе «Тип выбора»;
(2) в разделе «Конкретный тип» щелкните первое поле и укажите Содержит из раскрывающегося списка, а затем введите конкретный текст в следующее поле.
(3) Нажмите кнопку OK.

<р>4. Во втором диалоговом окне «Выбор конкретной ячейки» нажмите кнопку «ОК». Затем выбираются все ячейки со всеми строками, содержащими определенный текст или значение.

<р>5. Нажмите кнопку «Цвет заливки» на вкладке «Главная», а затем укажите цвет выделения из раскрывающегося списка.

Выделить всю строку, если ячейка содержит одно из определенных значений в другом столбце

Иногда может потребоваться выделить всю строку, только если ее ячейка содержит одно из определенных значений в другом столбце. Утилита сравнения диапазонов Kutools for Excel предоставляет еще один обходной путь для выделения целых строк, если ячейки легко содержат одно из определенных значений.

Kutools for Excel — объединяет более 300 расширенных функций и инструментов для Microsoft Excel

<р>1. Нажмите Kutools > Выбрать > Выбрать одинаковые и разные ячейки.

<р>2. В диалоговом окне «Выбор одинаковых и разных ячеек» выполните следующие действия:

(1) В поле «Найти значения в» укажите столбец, ячейки которого вы будете проверять, если они содержат одно из определенных значений;
(2) в поле «Согласно» укажите столбец, содержащий конкретные значения. ;
(3) В разделе "На основе" установите флажок "Каждая строка";
(4) В разделе «Поиск» установите флажок «Та же значения»;
(4) В диалоговом окне "Обработка результатов" установите флажок "Цвет фона заливки" и укажите цвет заливки;
(5) Установите флажок Выбрать строки целиком.

<р>3. Нажмите кнопку OK, чтобы применить эту утилиту. А затем появляется еще одно диалоговое окно «Сравнить диапазоны» и показывает нам, сколько строк было выбрано. Просто нажмите кнопку ОК, чтобы закрыть его.

И тогда вы увидите, что если ячейка содержит одно из определенных значений в другом столбце, вся строка, содержащая эту ячейку, будет выделена и заполнена определенным цветом.

Демонстрация: выделение строки, если ячейка содержит определенное значение или одно из указанных значений

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!

Автоматическое выделение активной строки и столбца с помощью Kutools for Excel

При выборе ячейки в Excel обычно выделяется только активная ячейка. Тем не менее, Kutools for Excel's Reading Layout утилита может автоматически выделять всю строку и столбец активной ячейки автоматически. Полнофункциональная бесплатная пробная версия на 30 дней!

Иногда вам может понадобиться выделить измененные ячейки во время редактирования листа/книги, чтобы следить за изменениями, но как это сделать в Excel?

Выделите измененные ячейки с помощью Track Changes

В Excel функция отслеживания изменений может выделять изменения.

<р>1. Сохраните книгу, прежде чем применять функцию отслеживания изменений, а затем нажмите Обзор > Отслеживание изменений > Выделить изменения .

<р>2. В диалоговом окне «Выделить изменения» установите флажок «Отслеживать изменения при редактировании». Это также позволяет поделиться вашей книгой и выделить изменения в параметрах экрана. Кроме того, вы можете указать ограничение по времени, пользователям и диапазону при отслеживании изменений.

<р>3. Нажмите «ОК». Появится диалоговое окно Microsoft Excel с напоминанием о сохранении книги.

<р>4. Нажмите «ОК». Теперь новое изменение будет выделено углом.

< img class="lazyload" data-src="https://cdn.extendoffice.com/images/stories/doc-excel/highlight-modified-cells/doc-highlight-modified-cells-5.jpg" alt=" документ выделяет измененные ячейки 5" />

Совет: эта функция работает для всей книги.

Выделите измененные ячейки кодом VBA

Если вам нужен код VBA для обработки задания, вы можете сделать следующее:

<р>1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.

<р>2. Щелкните Insert > Module , а затем дважды щелкните ThisWorkbook на панели VBAProject и вставьте приведенный ниже код в скрипт.

VBA: копирование и вставка адреса ячейки

<р>3. Сохраните код и вернитесь в рабочую книгу. Пока вы изменяли лист, ячейки будут выделены фоновым цветом.

Примечание. Если вы просто хотите применить выделение на текущем листе, просто щелкните правой кнопкой мыши вкладку листа и выберите "Просмотр кода" в контекстном меню, а затем вставьте приведенный ниже код в скрипт.

Выделите измененные ячейки в таблицах с помощью объединения таблиц

Если вы хотите обновить основную таблицу на основе другой, а затем выделить измененные/новые данные в основной таблице, вы можете попробовать функцию слияния таблиц Kutools for Excel.

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

<р>1. Выберите основную таблицу, которую необходимо изменить или обновить другой таблицей, и нажмите Kutools Plus > Объединение таблиц.

<р>2. Выберите диапазон таблиц, на основе которого вы хотите обновить основную таблицу, в мастере объединения таблиц.

<р>3. Нажмите "Далее" и выберите ключевой столбец, на основе которого вы хотите сравнить две таблицы.

<р>4. Нажмите «Далее», выберите столбцы, которые вы хотите обновить.

<р>5. Нажмите «Далее», в разделе «Параметры выделения» выберите цвет фона и цвет шрифта, которые вы хотите использовать для измененных ячеек.

<р>6.Нажмите «Готово» и «Да», чтобы закрыть диалоговые окна. Теперь измененные ячейки выделены.

В статье показано, как находить и выделять пробелы в Excel с помощью условного форматирования и VBA. В зависимости от ваших потребностей вы можете закрасить только действительно пустые ячейки или те, которые также содержат строки нулевой длины.

Когда вы получаете файл Excel от кого-то или импортируете его из внешней базы данных, всегда рекомендуется проверять данные, чтобы убедиться, что в них нет пробелов или отсутствующих точек данных. В небольшом наборе данных вы легко найдете все пробелы своими глазами. Но если у вас есть огромный файл, содержащий сотни или даже тысячи строк, определить пустые ячейки вручную практически невозможно.

Это руководство научит вас 4 быстрым и простым способам выделения пустых ячеек в Excel, чтобы вы могли их визуально идентифицировать. Какой метод лучше? Ну, это зависит от структуры данных, ваших целей и вашего определения "пустых мест".

Выберите и выделите пустые ячейки с помощью «Перейти к специальному»

Этот простой метод выбирает все пустые ячейки в заданном диапазоне, которые затем можно заполнить любым цветом по вашему выбору.

Чтобы выбрать пустые ячейки в Excel, вам нужно сделать следующее:

Функция Перейти к специальному выбирает только действительно пустые ячейки, т. е. ячейки, которые абсолютно ничего не содержат. Ячейки, содержащие пустую строку, пробелы, символы возврата каретки, непечатаемые символы и т. д., не считаются пустыми и не выделяются. Чтобы выделить ячейки с формулами, возвращающими в результате пустую строку (""), используйте либо условное форматирование, либо макрос VBA.
Этот метод является статическим, и его лучше всего использовать в качестве одноразового решения. Изменения, которые вы сделаете позже, не будут отражены автоматически: новые пробелы не будут выделены, а прежние пробелы, заполненные значениями, останутся окрашенными. Если вы ищете динамическое решение, вам лучше использовать подход условного форматирования.

Фильтрация и выделение пробелов в определенном столбце

Если вам не нужны пустые ячейки в таблице, а нужно найти и выделить ячейки или целые строки, содержащие пробелы в определенном столбце, Excel Filter может быть правильным решением.

Чтобы это сделать, выполните следующие действия:

Выберите любую ячейку в наборе данных и нажмите Сортировка и фильтрация > Фильтр на вкладке Главная. Или нажмите сочетание клавиш CTRL + Shift + L, чтобы включить автофильтры.
Нажмите стрелку раскрывающегося списка для целевого столбца и отфильтруйте пустые значения. Для этого снимите флажок Выбрать все и выберите (Пробелы).
Выберите отфильтрованные ячейки в ключевом столбце или целых строках и выберите цвет Fill, который вы хотите применить.

В нашем образце таблицы мы можем фильтровать, а затем выделять строки, в которых ячейки SKU пусты, следующим образом:

В отличие от предыдущего метода, этот подход рассматривает формулы, которые возвращают пустые строки (""), как пустые ячейки.
Это решение не подходит для часто изменяемых данных, поскольку вам придется очищать и снова выделять их при каждом изменении.

Как выделить пустые ячейки в Excel с помощью условного форматирования

Оба рассмотренных ранее метода просты и лаконичны, но у них есть существенный недостаток: ни один из них не реагирует на изменения, внесенные в набор данных. В отличие от них, условное форматирование — это динамическое решение, то есть вам нужно настроить правило только один раз. Как только пустая ячейка будет заполнена каким-либо значением, цвет тут же исчезнет. И наоборот, как только появится новое пустое место, оно будет выделено автоматически.

Пример 1. Выделение всех пустых ячеек в диапазоне

Чтобы выделить все пустые ячейки в заданном диапазоне, настройте правило условного форматирования Excel следующим образом:

Выберите диапазон, в котором вы хотите выделить пустые ячейки (в нашем случае A2:E6).
На вкладке Главная в группе Стили нажмите Новое правило >Используйте формулу, чтобы определить, какие ячейки следует форматировать .
В поле Формат значений, где эта формула верна, введите одну из следующих формул, где A2 – это верхняя левая ячейка выбранного диапазона:

Чтобы выделить абсолютно пустые ячейки, которые ничего не содержат:

Чтобы также выделить кажущиеся пустыми ячейки, содержащие строки нулевой длины (""), возвращаемые вашими формулами:

Пример 2. Выделение строк с пробелами в определенном столбце

В ситуации, когда вы хотите выделить все строки с пустыми ячейками в определенном столбце, просто внесите небольшое изменение в формулы, описанные выше, чтобы они ссылались на ячейку в этом конкретном столбце, и обязательно заблокируйте координата столбца со знаком $.

Например, чтобы выделить строки с пробелами в столбце B, выберите всю таблицу без заголовков столбцов (в данном примере A2:E6) и создайте правило с одной из следующих формул:

Чтобы выделить абсолютно пустые ячейки:

Чтобы выделить пробелы и ячейки, содержащие пустые строки:

В результате выделяются только те строки, в которых ячейка SKU пуста:

Выделите, если пусто, с помощью VBA

Если вы увлекаетесь автоматизацией, вам могут пригодиться следующие коды VBA для окрашивания пустых ячеек в Excel.

Макрос 1: закрашивание пустых ячеек

Этот макрос может помочь вам выделить действительно пустые ячейки, которые абсолютно ничего не содержат.

Чтобы закрасить все пустые ячейки в выбранном диапазоне, вам понадобится всего одна строка кода:

Чтобы выделить пробелы на предопределенном рабочем листе и в диапазоне (диапазон A2:E6 на листе 1 в приведенном ниже примере), используйте следующий код:

Вместо цвета RGB можно применить один из 8 основных базовых цветов, введя "vb" перед названием цвета, например:

Или вы можете указать индекс цвета, например:

Макрос 2: Цветные пробелы и пустые строки

Чтобы распознать визуально пустые ячейки, содержащие формулы, которые возвращают пустые строки как пробелы, проверьте, установлено ли для свойства Текст каждой ячейки в выбранном диапазоне значение "", и если оно TRUE, то примените цвет.< /p>

Вот код для выделения всех пробелов и пустых строк в выбранном диапазоне:

Как вставить и запустить макрос

Чтобы добавить макрос в книгу, выполните следующие действия:

Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
В обозревателе проектов слева щелкните правой кнопкой мыши целевую книгу и выберите Вставить >Модуль.
В окне кода справа вставьте код VBA.

Чтобы запустить макрос, вам нужно сделать следующее:

Для получения подробных пошаговых инструкций см.:

Вот как найти, выбрать и выделить пустые ячейки в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Читайте также: