Створіть розкривний список Excel із пропозиціями пошуку

Ми всі використовуємо Google як частину свого повсякденного життя. Однією з його функцій є пропозиція пошуку, де Google діє розумно і дає нам список пропозицій під час набору тексту.

У цьому посібнику ви дізнаєтесь, як створити розкривний список, який можна шукати в Excel-тобто розкривний список, який відображатиме відповідні елементи під час введення тексту.

Нижче наведено відео цього підручника (на випадок, якщо ви вважаєте за краще дивитися відео, а не читати текст).

Випадаючий список, який можна шукати в Excel

Для цілей цього підручника я використовую дані 20 найкращих країн за ВВП.

Намір полягає у створенні випадаючого списку Excel із механізмом пропозиції пошуку, таким чином, щоб він показував спадне меню з параметрами відповідності, коли я вводжу текст у рядку пошуку.

Щось, як показано нижче:

Щоб продовжити, завантажте файл прикладу звідси

Створення розкривного списку для пошуку в Excel буде тришаровим процесом:

1. Налаштування вікна пошуку.
2. Налаштування даних.
3. Написання короткого коду VBA, щоб він працював.

Крок 1 - Налаштування вікна пошуку

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

Нижче наведено кроки для цього.

1. Перейдіть на вкладку Розробник -> Вставка -> Елементи керування ActiveX -> Комбіноване вікно (елемент керування ActiveX).
   • Існує ймовірність, що ви не знайдете вкладку розробника на стрічці. За замовчуванням він прихований і його потрібно ввімкнути. Натисніть тут, щоб дізнатися, як отримати вкладку розробника на стрічці в Excel.
2. Наведіть курсор на область робочого аркуша і клацніть будь -де. Він вставить поле зі списком.
3. Клацніть правою кнопкою миші на поле зі списком і виберіть Властивості.
4. У діалоговому вікні властивостей внесіть такі зміни:
   • AutoWordSelect: помилковий
   • LinkedCell: В3
   • ListFillRange: DropDownList (ми створимо іменований діапазон з такою назвою на кроці 2)
   • MatchEntry: 2 - fmMatchEntryNone

(Клітинка B3 пов’язана з Combo Box, що означає, що все, що ви вводите в Combo Box, вводиться в B3)

4. Перейдіть на вкладку «Розробник» і натисніть «Режим дизайну». Це дозволить вам вводити текст у поле зі списком. Крім того, оскільки комірка B3 пов’язана з полем зі списком, будь-який текст, який ви вводите у поле зі списком, також відображатиметься у B3 у режимі реального часу.

Крок 2 - Налаштування даних

Тепер, коли вікно пошуку все налаштоване, нам потрібно отримати дані на місці. Ідея полягає в тому, що як тільки ви вводите що -небудь у вікні пошуку, він показує лише ті елементи, у яких є цей текст.

Для цього ми будемо використовувати

• Три колони -помічники.
• Один динамічний іменований діапазон.

Колонка помічника 1

Вставте таку формулу в комірку F3 і перетягніть її для всього стовпця (F3: F22)

=-ISNUMBER (ПОМИЛКА (ПОШУК ($ B $ 3, E3,1), ""))

Ця формула повертає 1, якщо текст у полі зі списком є ​​у назві країни ліворуч. Наприклад, якщо ви вводите UNI, то тільки значення для Університетted штатів і Університетted Kingdom - 1, а всі інші значення - 0.

Колонка помічника 2

Вставте таку формулу в комірку G3 і перетягніть її для всього стовпця (G3: G22)

= IF (F3 = 1, COUNTIF ($ F $ 3: F3,1), "") 

Ця формула повертає 1 для першого випадку, коли текст Combo Box відповідає назві країни, 2 для другого входження, 3 для третього тощо. Наприклад, якщо ви введете UNI, клітинка G3 відображатиме 1, як вона відповідає США, а G9 - 2, як вона відповідає Великобританії. Решта клітинок будуть порожніми.

Колонка помічника 3

Вставте таку формулу в комірку H3 і перетягніть її для всього стовпця (H3: H22)

= IFERROR (ІНДЕКС ($ E $ 3: $ E $ 22, MATCH (РЯДИ ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

Ця формула складає всі відповідні назви разом без порожніх клітинок між ними. Наприклад, якщо ви введете UNI, у цьому стовпці буде показано 2 та 9 разом, а решта всі клітинки будуть порожніми.

Створення динамічного іменованого діапазону

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

Примітка: На кроці 1 ми ввели DropDownList у опції ListFillRange. Тепер ми створимо іменований діапазон з такою ж назвою.

Ось кроки для його створення:

1. Перейдіть до Формули -> Менеджер імен.
2. У діалоговому вікні менеджера імен натисніть Створити. Відкриється діалогове вікно «Нова назва».
3. У полі імені введіть DropDownList
4. У Посилання на поле введіть формулу: = $ H $ 3: ІНДЕКС ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Крок 3 - Введення в дію коду VBA

Ми майже там.

Заключна частина - написання короткого коду VBA. Цей код робить спадне меню таким динамічним, що показує відповідні елементи/імена під час введення тексту у вікні пошуку.

Щоб додати цей код до книги:

1. Клацніть правою кнопкою миші на вкладці Робочий лист і виберіть Переглянути код.
2. У вікні VBA скопіюйте та вставте такий код:

   Приватний підкомбокс ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub

Це воно!!

У вас є власна панель пошуку типу Google, яка відображає відповідні елементи під час введення тексту.

Для кращого вигляду і відчуття ви можете накрити комірку B3 Combo Box і сховати всі допоміжні стовпці. Тепер ви можете трохи похвалитися цим чудовим трюком Excel.

Щоб продовжити, завантажте файл звідси

Що ти думаєш? Чи могли б ви використовувати цей розкривний список пропозицій пошуку у своїй роботі? Дайте мені знати ваші думки, залишивши коментар.

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

• Динамічний фільтр - Виймайте відповідні дані під час введення тексту.
• Витяг даних на основі вибору розкривного списку.
• Створення залежних випадаючих списків у Excel.
• Остаточний посібник із використання функції Excel VLOOKUP.
• Як зробити кілька виділень у розкривному списку в Excel.
• Як вставити та використовувати прапорець у Excel.