Създайте падащ списък на Excel с предложения за търсене

Всички използваме Google като част от ежедневието си. Една от неговите функции е предложение за търсене, при което Google действа умно и ни дава списък с предложения, докато пишем.

В този урок ще научите как да създадете падащ списък с възможност за търсене в Excel-т.е. падащ списък, който ще показва съвпадащите елементи, докато пишете.

По -долу е видео на този урок (в случай, че предпочитате да гледате видео, вместо да прочетете текста).

Падащ списък с възможност за търсене в Excel

За целите на този урок използвам данните на Топ 20 държави по БВП.

Намерението е да се създаде падащ списък на Excel с механизъм за търсене, така че да показва падащо меню с опциите за съвпадение, докато въвеждам в лентата за търсене.

Нещо, както е показано по -долу:

За да продължите, изтеглете примерния файл от тук

Създаването на падащ списък с възможност за търсене в Excel би бил процес от три части:

1. Конфигуриране на полето за търсене.
2. Настройка на данните.
3. Писане на кратък VBA код, за да работи.

Стъпка 1 - Конфигуриране на полето за търсене

В тази първа стъпка ще използвам комбинирано поле и ще го конфигурирам така, че когато въвеждате в него, текстът също се отразява в клетка в реално време.

Ето стъпките за това:

1. Отидете на Раздел Developer -> Insert -> ActiveX Controls -> Combo Box (ActiveX Control).
   • Има възможност да не намерите раздела за програмисти в лентата. По подразбиране той е скрит и трябва да бъде активиран. Щракнете тук, за да знаете как да получите раздела за програмисти на лентата в Excel.
2. Преместете курсора в областта на работния лист и щракнете навсякъде. Той ще постави комбинирано поле.
3. Щракнете с десния бутон върху Combo Box и изберете Properties.
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 (IFERROR (ТЪРСЕНЕ ($ B $ 3, E3,1), ""))

Тази формула връща 1, когато текстът в Combo Box е там в името на страната вляво. Например, ако въведете UNI, тогава само стойностите за Униted States и Униted Kingdom са 1, а всички останали стойности са 0.

Помощна колона 2

Поставете следната формула в клетка G3 и я плъзнете за цялата колона (G3: G22)

= АКО (F3 = 1, COUNTIF ($ F $ 3: F3,1), "") 

Тази формула връща 1 за първото събитие, където текстът на Combo Box съвпада с името на страната, 2 за второто появяване, 3 за третото и т.н. Например, ако въведете UNI, клетката G3 ще покаже 1, тъй като съответства на САЩ, а G9 ще покаже 2, тъй като съответства на Обединеното кралство. Останалите клетки ще бъдат празни.

Помощна колона 3

Поставете следната формула в клетка H3 и я плъзнете за цялата колона (H3: H22)

= IFERROR (INDEX ($ E $ 3: $ E $ 22, MATCH (ROWS ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "") 

Тази формула подрежда всички съвпадащи имена заедно без празни клетки между тях. Например, ако въведете UNI, тази колона ще показва 2 и 9 заедно, а останалата част всички клетки ще бъдат празни.

Създаване на динамичен именован диапазон

Сега, когато помощните колони са на място, трябва да създадем динамичния именен диапазон. Този именован диапазон ще се отнася само за онези стойности, които съвпадат с текста, въведен в комбинираното поле. Ще използваме този динамичен именен диапазон, за да покажем стойностите в падащото меню.

Забележка: В стъпка 1 въведохме DropDownList в опцията ListFillRange. Сега ще създадем именования диапазон със същото име.

Ето стъпките за създаването му:

1. Отидете на Формули -> Мениджър на имена.
2. В диалоговия прозорец на мениджъра на имена щракнете върху Ново. Ще се отвори диалогов прозорец Ново име.
3. В полето за име въведете DropDownList
4. В полето Препратки към полето въведете формулата: = $ H $ 3: INDEX ($ 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.