Перегляд відео - Пошук та виділення даних за допомогою умовного форматування
Якщо ви працюєте з великими наборами даних, може виникнути необхідність створити функцію пошуку, яка дозволяє швидко виділяти клітинки/рядки для пошукового терміну.
Хоча в Excel немає прямого способу зробити це, ви можете створити функцію пошуку за допомогою умовного форматування.
Наприклад, припустимо, що у вас є набір даних, як показано нижче (на зображенні). У ньому є стовпці для назви товару, торгового представника та країни.
Тепер ви можете використовувати умовне форматування для пошуку ключового слова (ввівши його у клітинку С2) та виділивши всі клітинки, які мають це ключове слово.
Щось, як показано нижче (де я вводю назву елемента в клітинку В2 і натискаю Enter, весь рядок виділяється):
У цьому підручнику я покажу вам, як створити цей пошук та виділити функціональні можливості в Excel.
Далі у цьому підручнику ми трохи перейдемо до розгляду та побачимо, як зробити його динамічним (щоб він виділявся під час введення тексту у вікні пошуку).
Натисніть тут, щоб завантажити файл прикладу і слідкуйте за ним.
Пошук та виділення відповідних клітинок
У цьому розділі. Я покажу вам, як шукати та виділяти лише відповідні клітинки в наборі даних.
Щось, як показано нижче:
Нижче наведено кроки для пошуку та виділення всіх клітинок із відповідним текстом:
- Виберіть набір даних, до якого потрібно застосувати умовне форматування (A4: F19 у цьому прикладі).
- Натисніть вкладку Домашня сторінка.
- У групі Стилі натисніть Умовне форматування.
- У випадаючих параметрах натисніть Нове правило.
- У діалоговому вікні "Нове правило форматування" натисніть на опцію "Використовувати формулу, щоб визначити, які клітинки відформатувати".
- Введіть таку формулу: = A4 = $ B $ 1
- Натисніть кнопку «Форматувати …».
- Вкажіть форматування (для виділення клітинок, які відповідають пошуковому слову).
- Натисніть OK.
Тепер введіть що -небудь у клітинку B1 і натисніть клавішу Enter. Він виділить відповідні клітинки в наборі даних, які містять ключове слово в B1.
Як це працює?
Умовне форматування застосовується кожного разу, коли формула, вказана в ньому, повертає TRUE.
У наведеному вище прикладі ми перевіряємо кожну клітинку за формулою = A4 = $ B $ 1
Умовне форматування перевіряє кожну клітинку та перевіряє її, що вміст у комірці такий самий, як у клітинці В1. Якщо це однаково, формула повертає ІСТИНУ, і клітинка підсвічується. Якщо це не те саме, формула повертає FALSE, і нічого не відбувається.
Натисніть тут, щоб завантажити файл прикладу і слідкуйте за ним.
Пошук і виділення рядків з відповідними даними
Якщо ви хочете виділити весь рядок, а не лише відповідні клітинки, ви можете зробити це, трохи змінивши формулу.
Нижче наведено приклад, коли весь рядок виділяється, якщо тип товару збігається з типом у комірці В1.
Ось кроки для пошуку та виділення всього рядка:
- Виберіть набір даних, до якого потрібно застосувати умовне форматування (A4: F19 у цьому прикладі).
- Натисніть вкладку Домашня сторінка.
- У групі Стилі натисніть Умовне форматування.
- У випадаючих параметрах натисніть Нове правило.
- У діалоговому вікні «Нове правило форматування» натисніть на опцію «Використовувати формулу, щоб визначити, які клітинки відформатувати».
- Введіть таку формулу: = $ B4 = $ B $ 1
- Натисніть кнопку «Форматувати …».
- Вкажіть форматування (для виділення клітинок, які відповідають пошуковому слову).
- Натисніть OK.
Наведені вище кроки будуть шукати вказаний елемент у наборі даних, і якщо він знайде відповідний елемент, він виділить весь рядок.
Зауважте, що це буде перевіряти лише стовпець елемента. Якщо ви введете тут ім’я торгового представника, це не працюватиме. Якщо ви хочете, щоб це працювало для імені торгового представника, потрібно змінити формулу на = $ C4 = $ B $ 1
Примітка: Причиною того, що він виділяє весь рядок, а не лише відповідну клітинку, є те, що ми використовували знак $ перед посиланням на стовпець ($ B4). Тепер, коли умовне форматування аналізує клітинки підряд, воно перевіряє, чи дорівнює значення у стовпці В цього рядка значенню у комірці В1. Тому навіть при аналізі A4, B4 або C4 тощо, він перевіряє лише значення B4 (оскільки ми заблокували стовпець B за допомогою знака долара).
Детальніше про абсолютні, відносні та змішані посилання можна прочитати тут.
Рядки пошуку та виділення (на основі часткової відповідності)
У деяких випадках ви можете виділити рядки на основі часткової відповідності.
Наприклад, якщо у вас є такі елементи, як Біла дошка, Зелена дошка та Сіра дошка, і ви хочете виділити все це на основі слова Дошка, то ви можете зробити це за допомогою функції ПОШУК.
Щось, як показано нижче:
Нижче наведено кроки для цього.
- Виберіть набір даних, до якого потрібно застосувати умовне форматування (A4: F19 у цьому прикладі).
- Натисніть вкладку Домашня сторінка.
- У групі Стилі натисніть Умовне форматування.
- У випадаючих параметрах натисніть Нове правило.
- У діалоговому вікні «Нове правило форматування» натисніть на опцію «Використовувати формулу, щоб визначити, які клітинки відформатувати».
- Введіть таку формулу: = AND ($ B $ 1 ””, ISNUMBER (ПОШУК ($ B $ 1, $ B4)))
- Натисніть кнопку «Форматувати …».
- Вкажіть форматування (для виділення клітинок, які відповідають пошуковому слову).
- Натисніть OK.
Як це працює?
- Функція SEARCH шукає рядок пошуку/ключове слово у всіх клітинках поспіль. Він повертає помилку, якщо ключове слово пошуку не знайдено, і повертає число, якщо знаходить відповідність.
- Функція ISNUMBER перетворює помилку у FALSE, а числові значення у TRUE.
- Функція AND перевіряє наявність додаткової умови - що клітинка C2 не повинна бути порожньою.
Тож тепер, коли ви вводите ключове слово у клітинку B1 та натискаєте Enter, воно виділяє всі рядки, у яких є клітинки, що містять це ключове слово.
Бонусна порада: Якщо ви хочете зробити регістр пошуку чутливим, використовуйте функцію FIND замість SEARCH.
Натисніть тут, щоб завантажити файл прикладу і слідкуйте за ним.
Функції динамічного пошуку та підсвічування (Основні моменти під час введення тексту)
Використовуючи ті ж хитрощі умовного форматування, описані вище, ви також можете зробити крок далі і зробити його динамічним.
Наприклад, ви можете створити панель пошуку, де відповідні дані виділяються під час введення тексту в рядку пошуку.
Щось, як показано нижче:
Це можна зробити за допомогою елементів керування ActiveX і може бути гарною функціональністю для створення звітів або інформаційних панелей.
Нижче наведено відео, де я показую, як це створити:
Чи знайшов цей підручник корисним? Дайте мені знати ваші думки в розділі коментарів.
Вам також можуть сподобатися такі підручники Excel:
- Динамічний фільтр Excel - витягує дані під час введення тексту.
- Створіть розкривний список із пропозицією пошуку.
- Створення теплової карти в Excel.
- Виділити рядки на основі значення комірки в Excel.
- Виділіть активну рядок і стовпець у діапазоні даних у Excel.
- Як виділити порожні клітинки в Excel.
