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

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

Відео

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

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

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

Створення розкривного списку з можливістю пошуку (для 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")

   

Крок 2. Переналаштуйте розкривний список

1. Виберіть клітинку розкривного списку (у цьому випадку я вибираю клітинку A2 аркуша Sheet2), а потім перейдіть до вибору дані > Перевірка достовірності даних > Перевірка достовірності даних.
   
2. У Перевірка достовірності даних діалогове вікно, потрібно налаштувати наступним чином.
   1. Відповідно до Налаштування вкладку, натисніть кнопку кнопка в Source коробка
      
   2. Команда Перевірка достовірності даних діалогове вікно буде переспрямовано на Аркуш1, виберіть клітинку (наприклад, D2) із формулою з кроку 1, додайте # і натисніть на близько кнопки.
      
   3. Перейти до Повідомлення про помилку вкладка зніміть прапорець Показувати попередження про помилку після введення недійсних даних прапорець і, нарешті, натисніть OK кнопка, щоб зберегти зміни.
      

Результат

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

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

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

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

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

Результат

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

Створення розкривного списку з можливістю пошуку за допомогою поля зі списком і 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 для додатків вікна.

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

Статті по темі: