Створення вікна пошуку в Excel
Ідея створити вікно пошуку в Excel, щоб ми продовжували писати потрібні дані, і відповідно він фільтрував дані та відображав лише стільки даних. У цій статті ми покажемо вам, як створити вікно пошуку та відфільтрувати дані в Excel.
15 простих кроків для створення динамічного вікна пошуку в Excel
Завантажити цей шаблон вікна пошуку Excel можна тут - Шаблон вікна пошуку ExcelДля створення динамічного вікна пошуку в Excel. ми будемо використовувати наведені нижче дані. Ви можете завантажити книгу та слідувати разом з нами, щоб створити її самостійно.
Дотримуйтесь наведених нижче кроків, щоб створити динамічне поле пошуку в Excel.
- Крок 1: Спочатку потрібно створити унікальний список « Місто імен» шляхом видалення дублікатів в новому аркуші.
- Крок 2: Для цього унікального списку міст дайте назву “ CityList ”
- Крок 3: Перейдіть на вкладку Розробник у програмі excel, а з вкладки вставте поле « Combo Box »
- Крок 4: Намалюйте це поле « Combo » на вашому аркуші, де знаходяться дані.
- Крок 5: Клацніть правою кнопкою миші на цьому “Combo Box” та виберіть опцію “ Properties ”.
- Крок 6: Це відкриє варіанти властивостей, як показано нижче.
- Крок 7: Ми маємо тут кілька властивостей, оскільки властивість “ Linked Cell ” дає посилання на комірку D2 .
- Крок 8: Для властивості “ Список заповнення діапазону ” вкажіть назву, надану унікальному списку “Міста”.
- Крок 9: Для властивості “ Match Entry ” виберіть 2-fmMatchEntryNone, оскільки, ввівши ім’я у поле зі списком, воно не буде автоматично заповнювати речення.
- Крок 10: Ми закінчили з властивостями, що входять до “Combo Box”. Перейдіть на вкладку " Розробник " і зніміть прапорець у режимі " Дизайн " у "Combo Box".
- Крок 11: Тепер із поля зі списком ми можемо побачити назви міст у випадаючому списку в Excel.
Насправді ми можемо ввести ім’я всередині поля зі списком, і воно також відображатиме вбудовану комірку D2.
- Крок 12: Тепер нам потрібно написати формули для фільтрування даних, коли ми вводимо назву міста у поле зі списком. Для цього нам потрібно мати три допоміжні стовпці, для першого допоміжного стовпця нам потрібно знайти номери рядків за допомогою функції ROWS.
- Крок 13: У другому допоміжному стовпці нам потрібно знайти відповідні пошукові назви міст, і якщо вони збігаються, нам потрібні номери рядків цих міст для цього, щоб ввести формулу нижче.
Ця формула буде шукати назву міста в головній таблиці, якщо вона збігається, вона повертає номер рядка зі стовпця “Helper 1”, а то поверне порожню комірку.
Наприклад, зараз я наберу “ Лос-Анджелес ”, і скрізь, де в головній таблиці наведено назву міста, ми отримаємо номер рядка.
- Крок 14: Коли номери рядків із введеної або вибраної назви міста стануть доступними, нам потрібно з’єднати ці номери рядків один під одним, тому в третьому допоміжному стовпці нам потрібно скласти всі ці номери рядків із введеної назви міста.
Для отримання цих номерів рядків ми будемо використовувати формулу комбінації “ IFERROR в Excel ” та функції “ SMALL ” в Excel.
Ця формула буде шукати найменше значення у списку відповідних міст на основі фактичних номерів рядків, і вона буде складати перший найменший, другий найменший, третій найменший тощо. Як тільки всі малі значення складені разом, функція SMALL видає значення помилки, тому, щоб уникнути цього, ми використовували функцію IFERROR, і якщо значення помилки надходить, то в результаті поверне порожню комірку.
- Крок 15: Тепер створіть ідентичний формат таблиці, як наведений нижче.
У цій новій таблиці нам потрібно відфільтрувати дані на основі назви міста, яку ми вводимо у вікні пошуку Excel. Це можна зробити, використовуючи комбінацію функцій IFERROR, INDEX і COLUMNS в Excel. Нижче наведена формула, яку потрібно застосувати.
Скопіюйте формулу та вставте до всіх інших комірок нової таблиці.
Гаразд, ми закінчили проектувати деталь, давайте навчимося користуватися нею.
Введіть назву міста у поле зі списком, і наша нова таблиця відфільтрує лише введені дані міста.
Як бачите, я щойно набрав “LO”, і всі пов’язані результати пошуку фільтруються у новому форматі таблиці.
Що слід пам’ятати тут
- Вам потрібно вставити комбіноване поле в Excel з «ActiveX Form Control» на вкладці «Розробник».
- Поле зі списком, що відповідає всім відповідним алфавітам, повертає результат.