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

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

Автор: Силувій Остання зміна: 2024-11-19

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

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



Відео: Створення розкривного списку з можливістю пошуку

 


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

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

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

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

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

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

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

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

зразкові дані

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

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

  1. Виберіть першу клітинку D1 у стовпці D і введіть заголовок стовпця, наприклад "Результати пошуку" в цьому випадку.
  2. Введіть наступну формулу в клітинку D2 і натисніть Enter.
    =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 for Excel's Зробити розкривний список доступним для пошуку, автоматично спливатифункція. Ця функція доступна в усіх версіях Excel і дозволяє легко шукати потрібний елемент у розкривному списку за допомогою простого налаштування.

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

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

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

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

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

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

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

зразкові дані

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

  1. Якщо Розробник вкладка не відображається на стрічці, ви можете ввімкнути Розробник вкладку наступним чином.
    1. В Excel 2010 або новіших версіях натисніть Файл > Опції. І в Параметри Excel діалогове вікно натисніть кнопку Налаштувати стрічку на лівій панелі. Перейдіть до списку Налаштувати стрічку та поставте прапорець Розробник , а потім клацніть на OK кнопку. Дивіться знімок екрана:
      кроки, щоб увімкнути вкладку «Розробник».
    2. У програмі Excel 2007 натисніть Office кнопка> Параметри Excel, в Параметри Excel діалогове вікно натисніть кнопку популярний на лівій панелі перевірте Показати вкладку розробника на стрічці і нарешті клацніть на OK .
      кроки, щоб увімкнути вкладку «Розробник» у Excel 2007
  2. Після показу Розробник вкладка, клацніть Розробник > Insert > Комбінована коробка.
    натисніть «Розробник» > «Вставити» > «Поле зі списком».
  3. Намалюйте поле зі списком на аркуші, клацніть його правою кнопкою миші та виберіть властивості з меню, що клацне правою кнопкою миші.
    Намалюйте поле зі списком, клацніть його правою кнопкою миші та виберіть «Властивості».
  4. Перейдіть на вкладку властивості у діалоговому вікні потрібно:
    1. Виберіть Помилковий , AutoWordSelect поле;
    2. Укажіть клітинку в LinkedCell поле. У цьому випадку ми вводимо A12;
    3. Виберіть 2-fmMatchEntryNone , MatchEntry поле;
    4. тип Випадаючий список в ListFillRange поле;
    5. Закрити властивості діалогове вікно. Дивіться знімок екрана:
      встановити параметри в діалоговому вікні «Властивості».
  5. Тепер вимкніть режим дизайну, клацнувши Розробник > Режим дизайну.
  6. Виберіть порожню клітинку, наприклад C2, введіть формулу нижче та натисніть Enter. Вони перетягують його маркер автозаповнення вниз до клітинки C9, щоб автоматично заповнити клітинки тією ж формулою. Дивіться знімок екрана:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    застосувати формулу
    примітки:
    1. $ 12 $ це клітинка, яку ви вказали як LinkedCell на кроці 4;
    2. Після завершення вищевказаних кроків ви можете перевірити: введіть літеру C у полі зі списком, і тоді ви побачите, що клітинки формули, які посилаються на клітинки, що містять символ C, заповнені цифрою 1.
  7. Виберіть комірку D2, введіть формулу нижче та натисніть Enter. Потім перетягніть його маркер автозаповнення вниз до клітинки D9.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
    застосувати іншу формулу
  8. Виберіть клітинку E2, введіть формулу нижче та натисніть Enter. Потім перетягніть його маркер автозаповнення до 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 в редактор коду
    Код VBA: зробіть пошук у розкривному списку
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. Натисніть інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

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

розкривний список може шукати

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

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

Kutools for Excel - Допоможе вам виділитися з натовпу

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

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


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

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