Фільтр Excel - одна з найбільш використовуваних функцій при роботі з даними. У цій публікації в блозі я покажу вам, як створити вікно пошуку для фільтра динамічного Excel, щоб воно фільтрувало дані на основі того, що ви вводите у вікні пошуку.
Щось, як показано нижче:
У цьому є подвійна функціональність - ви можете вибрати назву країни зі спадного списку або вручну ввести дані у вікні пошуку, і він покаже вам усі відповідні записи. Наприклад, коли ви вводите "I", він дає вам усі назви країн з алфавітом I.
Перегляд відео - Створення вікна пошуку для динамічного фільтра Excel
Створення вікна пошуку динамічного фільтра Excel
Цей динамічний фільтр Excel можна створити в 3 кроки:
- Отримання унікального списку предметів (країни в даному випадку). Це буде використано при створенні випадаючого меню.
- Створення вікна пошуку. Тут я використав Combo Box (ActiveX Control).
- Налаштування даних. Тут я б використав три допоміжні стовпці з формулами для вилучення відповідних даних.
Ось як виглядають необроблені дані:
КОРИСНА ПОРАДА: Перетворення даних у таблицю Excel майже завжди хороша ідея. Ви можете це зробити, вибравши будь -яку клітинку в наборі даних і скориставшись комбінацією клавіш Control + T.
Крок 1 - Отримання унікального списку предметів
- Виберіть усі країни та вставте їх у новий аркуш.
- Виберіть список країн -> Перейти до Дані -> Видалити дублікати.
- У діалоговому вікні Видалити дублікати виберіть стовпець, у якому є список, і натисніть кнопку ОК. Це видалить дублікати та дасть вам унікальний список, як показано нижче:
- Ще одним кроком є створення іменованого діапазону для цього унікального списку. Зробити це:
- Перейдіть на вкладку Формула -> Визначити назву
- У діалоговому вікні Визначення імені:
- Назва: CountryList
- Область застосування: Робочий зошит
- Посилається на: = UniqueList! $ A $ 2: $ A $ 9 (у мене є список на окремій вкладці з назвою UniqueList у A2: A9. Ви можете звернутись до того місця, де знаходиться ваш унікальний список)
ПРИМІТКА. Якщо ви використовуєте метод "Видалити дублікати" і розширюєте дані, щоб додати більше записів та нові країни, вам доведеться повторити цей крок ще раз. Крім того, ви також можете створити формулу, щоб зробити цей процес динамічним.
Крок 2 - Створення вікна пошуку динамічного фільтра Excel
Щоб ця техніка запрацювала, нам потрібно було б створити «Вікно пошуку» та зв’язати її з клітиною.
Ми можемо використовувати Combo Box в Excel для створення цього фільтра вікна пошуку. Таким чином, щоразу, коли ви вводите що-небудь у вікні комбо, це також відображатиметься у комірці в режимі реального часу (як показано нижче).
Нижче наведено кроки для цього.
- Перейдіть на вкладку Розробник -> Елементи управління -> Вставити -> Елементи керування ActiveX -> Комбіноване вікно (елементи керування ActiveX).
- Якщо ви не бачите вкладку Розробник, виконайте вказівки, щоб увімкнути її.
- Якщо ви не бачите вкладку Розробник, виконайте вказівки, щоб увімкнути її.
- Клацніть будь -де на аркуші. Він вставить Combo Box.
- Клацніть правою кнопкою миші на Combo Box і виберіть Властивості.
- У вікні Властивості внесіть такі зміни:
- Пов'язана клітинка: K2 (ви можете вибрати будь -яку клітинку, де потрібно, щоб вона відображала вхідні значення. Ми будемо використовувати цю клітинку для налаштування даних).
- ListFillRange: CountryList (це іменований діапазон, який ми створили на кроці 1. Буде показано всі країни у спадному меню).
- MatchEntry: 2-fmMatchEntryNone (це гарантує, що слово не завершується автоматично під час введення тексту)
- Вибравши Combo Box, перейдіть на вкладку Developer -> Controls -> Натисніть на Mode Mode (це виведе вас із режиму проектування, і тепер ви можете вводити будь -що в Combo Box. Тепер усе, що ви вводите, відображатиметься у клітинці K2 в реальному часі)
Крок 3 - Налаштування даних
Нарешті, ми пов'язуємо все за допомогою допоміжних стовпців. Тут я використовую три допоміжні стовпці для фільтрації даних.
Колонка 1 помічника: Введіть серійний номер усіх записів (у цьому випадку 20). Для цього можна використати формулу ROWS ().
Колонка помічника 2: У допоміжній колонці 2 ми перевіряємо, чи текст, введений у вікні пошуку, відповідає тексту в осередках у стовпці країни.
Це можна зробити за допомогою комбінації функцій IF, ISNUMBER та SEARCH.
Ось формула:
= IF (ISNUMBER (ПОШУК ($ K $ 2, D4)), E4, "")
Ця формула здійснюватиме пошук вмісту у вікні пошуку (зв'язаному з коміркою K2) у комірці з назвою країни.
Якщо є відповідність, ця формула повертає номер рядка, інакше вона повертає порожнє місце. Наприклад, якщо поле зі списком має значення "США", усі записи з країною як "США" матимуть номер рядка, а всі інші будуть порожніми ("")
Колонка помічника 3: У допоміжній колонці 3 нам потрібно зібрати всі номери рядків із стовпця 2 помічника разом. Для цього ми можемо використовувати комбінацію, якщо IFERROR і SMALL формули. Ось формула:
= ПОМИЛКА (МАЛА ($ F $ 4: $ F $ 23, E4), "")
Ця формула складає разом усі відповідні номери рядків. Наприклад, якщо поле зі списком має значення US, усі номери рядків із "US" у ньому складаються разом.
Тепер, коли у нас є рядки, складені разом, нам просто потрібно витягти дані з цих номерів рядків. Це можна легко зробити за допомогою формули індексу (вставте цю формулу туди, де потрібно витягти дані. Скопіюйте її у верхню ліву клітинку, де потрібно витягти дані, а потім перетягніть її вниз і вправо).
= IFERROR (ІНДЕКС ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Ця формула має 2 частини:
ПОКАЗНИК - Це витягує дані на основі номера рядка.
ПОМИЛКА - Це повертає порожнє місце, коли немає даних.
Ось короткий знімок того, що ви нарешті отримаєте:
Комбіноване вікно - це спадне меню та вікно пошуку. Ви можете приховати вихідні дані та допоміжні стовпці, щоб відображати лише відфільтровані записи. Ви також можете мати вихідні дані та допоміжні стовпці на іншому аркуші та створити цей динамічний фільтр Excel на іншому аркуші.
Будь творчим! Спробуйте деякі варіації
Ви можете спробувати налаштувати його відповідно до ваших вимог. Ви можете створити кілька фільтрів Excel замість одного. Наприклад, ви можете відфільтрувати записи, де представник відділу продажів - Майк, а країна - Японія. Це можна зробити точно за тими самими кроками з деякими змінами у формулі у допоміжних стовпцях.
Іншим варіантом може бути фільтрація даних, які починаються з символів, які ви вводите у поле зі списком. Наприклад, коли ви вводите "I", ви можете витягти країни, що починаються з I (порівняно з поточною конструкцією, де вона також дасть вам Сінгапур та Філіппіни, оскільки вона містить алфавіт I).
Як завжди, більшість моїх статей надихаються запитаннями/відповідями моїх читачів. Я хотів би отримати ваші відгуки та навчитися у вас. Залиште свої думки в розділі коментарів.
Примітка: Якщо ви використовуєте Office 365, ви можете скористатися функцією ФІЛЬТР для швидкої фільтрації даних під час введення. Це простіше, ніж метод, показаний у цьому уроці.
