Поиск и выделение данных в Excel (с условным форматированием)

Посмотреть видео - поиск и выделение данных с использованием условного форматирования

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

Хотя в Excel нет прямого способа сделать это, вы можете создать функцию поиска, используя условное форматирование.

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

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

Примерно так, как показано ниже (где я ввожу имя элемента в ячейку B2 и нажимаю Enter, вся строка выделяется):

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

Позже в этом руководстве мы немного углубимся и посмотрим, как сделать его динамическим (чтобы он выделялся, когда вы вводите текст в поле поиска).

Щелкните здесь, чтобы загрузить файл примера и следуйте дальше.

Поиск и выделение совпадающих ячеек

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

Что-то вроде того, что показано ниже:

Вот шаги для поиска и выделения всех ячеек с совпадающим текстом:

1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4: F19 в этом примере).
2. Щелкните вкладку "Главная".
3. В группе «Стили» щелкните «Условное форматирование».
4. В раскрывающемся списке выберите «Новое правило».
   
5. В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. Введите следующую формулу: = A4 = $ B $ 1
7. Нажмите кнопку «Форматировать…».
8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
9. Щелкните ОК.

Теперь введите что-нибудь в ячейку B1 и нажмите Enter. Он выделит совпадающие ячейки в наборе данных, которые содержат ключевое слово в B1.

Как это работает?

Условное форматирование применяется всякий раз, когда указанная в нем формула возвращает значение ИСТИНА.

В приведенном выше примере мы проверяем каждую ячейку по формуле = A4 = $ B $ 1

Условное форматирование проверяет каждую ячейку и проверяет, что ее содержимое совпадает с содержимым ячейки B1. Если это то же самое, формула возвращает ИСТИНА, и ячейка выделяется. Если это не то же самое, формула возвращает ЛОЖЬ, и ничего не происходит.

Щелкните здесь, чтобы загрузить файл примера и следуйте дальше.

Поиск и выделение строк с совпадающими данными

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

Ниже приведен пример, в котором вся строка выделяется, если тип продукта совпадает с типом в ячейке B1.

Вот шаги для поиска и выделения всей строки:

1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4: F19 в этом примере).
2. Щелкните вкладку "Главная".
3. В группе «Стили» щелкните «Условное форматирование».
4. В раскрывающемся списке выберите «Новое правило».
   
5. В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. Введите следующую формулу: = $ B4 = $ B $ 1
7. Нажмите кнопку «Форматировать…».
8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
9. Щелкните ОК.

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

Обратите внимание, что это будет проверять только столбец элемента. Если вы введете здесь имя торгового представителя, это не сработает. Если вы хотите, чтобы это работало для имени торгового представителя, вам нужно изменить формулу на = $ C4 = $ B $ 1

Примечание. Причина, по которой он выделяет всю строку, а не только соответствующую ячейку, заключается в том, что мы использовали знак $ перед ссылкой на столбец ($ B4). Теперь, когда условное форматирование анализирует ячейки в строке, оно проверяет, равно ли значение в столбце B этой строки значению в ячейке B1. Таким образом, даже когда он анализирует A4, B4 или C4 и т. Д., Он проверяет только значение B4 (поскольку мы заблокировали столбец B с помощью знака доллара).

Вы можете узнать больше об абсолютных, относительных и смешанных ссылках здесь.

Поиск и выделение строк (на основе частичного соответствия)

В некоторых случаях вы можете выделить строки на основе частичного совпадения.

Например, если у вас есть такие элементы, как Белая доска, Зеленая доска и Серая доска, и вы хотите выделить их все на основе слова Доска, то вы можете сделать это с помощью функции ПОИСК.

Что-то вроде того, что показано ниже:

Вот как это сделать:

1. Выберите набор данных, к которому вы хотите применить условное форматирование (A4: F19 в этом примере).
2. Щелкните вкладку "Главная".
3. В группе «Стили» щелкните «Условное форматирование».
4. В раскрывающемся списке выберите «Новое правило».
   
5. В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. Введите следующую формулу: = И ($ B $ 1 ””, ISNUMBER (ПОИСК ($ B $ 1, $ B4)))
7. Нажмите кнопку «Форматировать…».
8. Укажите форматирование (чтобы выделить ячейки, соответствующие искомому ключевому слову).
9. Щелкните ОК.

Как это работает?

• Функция ПОИСК ищет строку / ключевое слово поиска во всех ячейках подряд. Он возвращает ошибку, если ключевое слово для поиска не найдено, и возвращает число, если найдено совпадение.
• Функция ЕЧИСЛО преобразует ошибку в ЛОЖЬ, а числовые значения в ИСТИНА.
• Функция И проверяет дополнительное условие - ячейка C2 не должна быть пустой.

Итак, теперь, когда вы вводите ключевое слово в ячейку B1 и нажимаете Enter, он выделяет все строки, в которых есть ячейки, содержащие это ключевое слово.

Бонусный совет: Если вы хотите сделать поиск чувствительным к регистру, используйте функцию НАЙТИ вместо ПОИСКА.

Щелкните здесь, чтобы загрузить файл примера и следуйте дальше.

Функциональность динамического поиска и выделения (выделяется по мере ввода)

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

Например, вы можете создать панель поиска, в которой соответствующие данные выделяются при вводе текста в строке поиска.

Что-то вроде того, что показано ниже:

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

Ниже показано видео, в котором я показываю, как это сделать:

Вы нашли этот урок полезным? Сообщите мне свои мысли в разделе комментариев.

Вам также могут понравиться следующие руководства по Excel:

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