Перейти до основного матеріалу

Повний посібник із розкривного списку з можливістю пошуку в Excel

Створення розкривних списків у Excel спрощує введення даних і мінімізує помилки. Але з більшими наборами даних прокручування довгих списків стає громіздким. Хіба не було б простіше просто ввести та швидко знайти свій предмет? A "розкривний список з можливістю пошуку" пропонує таку зручність. У цьому посібнику описано чотири способи створення такого списку в Excel.


Відео


Розкривний список із можливістю пошуку в Excel 365

Excel 365 представив довгоочікувану функцію для своїх розкривних списків перевірки даних: можливість пошуку в списку. Завдяки функції пошуку користувачі можуть швидко знаходити та вибирати елементи більш ефективним способом. Після вставлення розкривного списку, як зазвичай, просто клацніть клітинку з розкривним списком і почніть вводити текст. Список миттєво фільтрується відповідно до введеного тексту.

У цьому випадку я друкую Сан - у клітинці, а розкривний список відфільтровує міста, які починаються з пошукового терміну Сан -, Такі, як Сан Франциско та Сан - Дієго. Потім ви можете вибрати результат за допомогою миші або скористатися клавішами зі стрілками та натиснути Enter.

примітки:
  • Команда пошук починається з першої літери кожного слова у спадному списку. Якщо ви введете символ, який не збігається з початковим символом будь-якого слова, у списку не відображатимуться відповідні елементи.
  • Ця функція доступна лише в останній версії Excel 365.
  • Якщо ваша версія Excel не підтримує цю функцію, тут ми рекомендуємо Розкривний список для пошуку особливість Kutools для Excel. Немає обмежень щодо версії Excel, і після ввімкнення ви можете легко шукати потрібний елемент у розкривному списку, просто ввівши відповідний текст. Перегляньте докладні кроки.

Створення розкривного списку з можливістю пошуку (для Excel 2019 і новіших версій)

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

Припустімо, що ви створили розкривний список у комірці A2 аркуша Sheet2 (зображення праворуч), використовуючи дані в діапазоні A2:A8 аркуша Sheet1 (зображення ліворуч), виконайте ці дії, щоб зробити список доступним для пошуку.

Крок 1. Створіть допоміжний стовпець із переліком елементів пошуку

Тут нам потрібен допоміжний стовпець для переліку елементів, які відповідають вашим вихідним даним. У цьому випадку я створю допоміжний стовпець у колонка D of Sheet1.

  1. Виберіть першу клітинку D1 у стовпці D і введіть заголовок стовпця, наприклад "Результати пошуку" в цьому випадку.
  2. Введіть наступну формулу в клітинку D2 і натисніть Що натомість? Створіть віртуальну версію себе у .
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
примітки:
  • У цій формулі A2: A8 діапазон вихідних даних. Аркуш2!А2 це розташування розкривного списку, що означає, що розкривний список розташований у форматі A2 аркуша Sheet2. Будь ласка, змініть їх відповідно до ваших власних даних.
  • Якщо зі спадного списку в A2 аркуша Sheet2 не вибрано жодного елемента, формула відображатиме всі елементи з вихідних даних, як показано на зображенні вище. І навпаки, якщо вибрано елемент, D2 відобразить цей елемент як результат формули.
Крок 2. Переналаштуйте розкривний список
  1. Виберіть клітинку розкривного списку (у цьому випадку я вибираю клітинку A2 аркуша Sheet2), а потім перейдіть до вибору дані > Перевірка достовірності даних > Перевірка достовірності даних.
  2. У Перевірка достовірності даних діалогове вікно, потрібно налаштувати наступним чином.
    1. Відповідно до Налаштування вкладку, натисніть кнопку кнопка в Source коробка
    2. Команда Перевірка достовірності даних діалогове вікно буде переспрямовано на Аркуш1, виберіть клітинку (наприклад, D2) із формулою з кроку 1, додайте # і натисніть на близько кнопки.
    3. Перейти до Повідомлення про помилку вкладка зніміть прапорець Показувати попередження про помилку після введення недійсних даних прапорець і, нарешті, натисніть OK кнопка, щоб зберегти зміни.
Результат

Розкривний список у клітинці A2 аркуша Sheet2 тепер доступний для пошуку. Введіть текст у клітинку, клацніть стрілку розкривного меню, щоб розгорнути розкривний список, і ви побачите, що список миттєво відфільтровано відповідно до введеного тексту.

примітки:
  • Цей метод доступний лише для Excel 2019 і новіших версій.
  • Цей метод одночасно працює лише з однією клітинкою розкривного списку. Щоб розкривні списки можна було шукати в комірках від A3 до A8 на аркуші Sheet2, потрібно повторити вищезгадані кроки для кожної комірки.
  • Коли ви вводите текст у клітинку розкривного списку, розкривний список не розгортається автоматично, вам потрібно клацнути стрілку спадного меню, щоб розгорнути його вручну.

Легко створюйте розкривний список із можливістю пошуку (для всіх версій Excel)

Враховуючи різні обмеження наведених вище методів, ось дуже ефективний інструмент для вас - Kutools для Excel's Розкривний список для пошуку функція. Ця функція доступна в усіх версіях Excel і працює не лише з усіма розкривними списками в межах зазначеного діапазону, а й з усіма розкривними списками на поточному аркуші чи поточній книзі, дозволяючи вам легко шукати потрібні елементи в розкривні списки.

після завантаження та встановлення Kutools для Excelвиберіть Кутулс > Випадаючий список > Розкривний список для пошуку щоб увімкнути цю функцію. В Розкривний список для пошуку у діалоговому вікні потрібно:

  1. Вкажіть потрібну опцію в Встановити область випадаючий список.
    У цьому випадку мені потрібно зробити доступним для пошуку розкривний список у вказаному діапазоні, тому я вибираю Вкажіть діапазон і виберіть діапазон.
  2. Натисніть OK.
Результат

Коли ви клацаєте клітинку розкривного списку в указаному діапазоні, праворуч з’являється поле зі списком. Введіть текст, щоб миттєво відфільтрувати список, а потім виберіть елемент або скористайтеся клавішами зі стрілками та натисніть Enter, щоб додати його до клітинки.

примітки:
  • Ця функція надає три корисні параметри:
    • Вкажіть діапазон: якщо вибрати цей параметр і вказати діапазон комірок, лише розкривні списки в цьому вказаному діапазоні доступні для пошуку.
    • Поточний робочий зошит: якщо вибрати цей параметр, усі розкривні списки в поточній книзі будуть доступними для пошуку.
    • Поточний аркуш: Якщо вибрати цей параметр, пошук доступний лише в розкривних списках на поточному аркуші.
  • Ця функція підтримує пошук з будь-якої позиції в словах. Це означає, що навіть якщо ви введете символ у середині або в кінці слова, відповідні елементи все одно будуть знайдені та відображені, забезпечуючи більш повний і зручний пошук.
  • Щоб дізнатися більше про цю функцію, будь ласка visit this page.
  • Щоб застосувати цю функцію, будь ласка завантажте та встановіть Kutools для Excel перший.

Створення розкривного списку з можливістю пошуку за допомогою поля зі списком і VBA (більш складний)

Якщо ви просто хочете створити розкривний список із можливістю пошуку, не вказуючи певний тип розкривного списку. Цей розділ пропонує альтернативний підхід: використання поля зі списком із кодом VBA для вирішення завдання.

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

Вам потрібно вставити поле зі списком замість розкривного списку перевірки даних у вашому робочому аркуші.

  1. Якщо Розробник вкладка не відображається на стрічці, ви можете ввімкнути Розробник вкладку наступним чином.
    1. В Excel 2010 або новіших версіях натисніть філе > Опції. І в Параметри Excel діалогове вікно натисніть кнопку Налаштувати стрічку на лівій панелі. Перейдіть до списку Налаштувати стрічку та поставте прапорець Розробник , а потім клацніть на OK кнопку. Дивіться знімок екрана:
    2. У програмі Excel 2007 натисніть Office кнопка> Параметри Excel, в Параметри Excel діалогове вікно натисніть кнопку популярний на лівій панелі перевірте Показати вкладку розробника на стрічці і нарешті клацніть на OK кнопки.
  2. Після показу Розробник вкладка, клацніть Розробник > Insert > Комбінована коробка.
  3. Намалюйте поле зі списком на аркуші, клацніть його правою кнопкою миші та виберіть властивості з меню, що клацне правою кнопкою миші.
  4. У властивості у діалоговому вікні потрібно:
    1. Select Помилковий в AutoWordSelect поле;
    2. Укажіть клітинку в LinkedCell поле. У цьому випадку ми вводимо A12;
    3. Select 2-fmMatchEntryNone в MatchEntry поле;
    4. тип Випадаючий список в ListFillRange поле;
    5. Закрити властивості діалогове вікно. Дивіться знімок екрана:
  5. Тепер вимкніть режим дизайну, клацнувши Розробник > Режим дизайну.
  6. Виберіть порожню клітинку, наприклад C2, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у . Вони перетягують його маркер автозаповнення вниз до клітинки C9, щоб автоматично заповнити клітинки тією ж формулою. Дивіться знімок екрана:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    примітки:
    1. $ 12 $ це клітинка, яку ви вказали як LinkedCell на кроці 4;
    2. Після завершення вищевказаних кроків ви можете перевірити: введіть літеру C у полі зі списком, і тоді ви побачите, що клітинки формули, які посилаються на клітинки, що містять символ C, заповнені цифрою 1.
  7. Виберіть комірку D2, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у . Потім перетягніть його маркер автозаповнення вниз до клітинки D9.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. Виберіть клітинку E2, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у . Потім перетягніть його маркер автозаповнення до E9, щоб застосувати ту саму формулу.
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. Тепер вам потрібно створити діапазон імен. Будь ласка, натисніть Formula > Визначте ім’я.
  10. У Нове ім'я діалогове вікно введіть Випадаючий список в ІМ'Я введіть формулу нижче в поле Відноситься до , а потім клацніть на OK кнопки.
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. Тепер увімкніть режим дизайну, клацнувши Розробник > Режим дизайну. Потім двічі клацніть поле зі списком, щоб відкрити Microsoft Visual Basic для додатків вікна.
  12. Скопіюйте та вставте наведений нижче код VBA в редактор коду.
    Код VBA: зробіть пошук у розкривному списку
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. Натисніть інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

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

примітки: Вам потрібно зберегти цю книгу як файл книги Excel з підтримкою макросів, щоб зберегти код VBA для подальшого використання.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.