Създаването на падащи списъци в Excel рационализира въвеждането на данни и минимизира грешките. Но с по-големи набори от данни превъртането през дългите списъци става тромаво. Не би ли било по-лесно просто да напишете и бързо да намерите вашия артикул? а "падащ списък с възможност за търсене" предлага това удобство. Това ръководство ще ви преведе през четири метода за създаване на такъв списък в 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. Под Настройки , кликнете върху бутон в източник кутия.
      
   2. - Потвърждаване на данните диалоговият прозорец ще пренасочи към Sheet1, изберете клетката (напр. D2) с формулата от стъпка 1, добавете # и щракнете върху Близо бутон.
      
   3. Отидете в Предупреждение за грешка , премахнете отметката от Показване на предупреждение за грешка след въвеждане на невалидни данни отметка и накрая щракнете върху OK , за да запазите промените.
      

Резултат

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

Лесно създаване на падащ списък с възможност за търсене (за всички версии на Excel)

Предвид различните ограничения на горните методи, ето един много ефективен инструмент за вас - Kutools за Excel's Направете падащия списък достъпен за търсене, автоматично изскачащособеност. Тази функция е налична във всички версии на Excel и ви позволява лесно да търсите желания елемент в падащия списък с проста настройка.

След изтегляне и инсталиране на Kutools за Excelизберете Kutools > Падащ списък > Направете падащия списък достъпен за търсене, автоматично изскачащ за да активирате тази функция. В Направете падащия списък достъпен за търсене диалогов прозорец, трябва да:

1. Изберете диапазона, съдържащ падащите списъци, които трябва да бъдат зададени като падащи списъци с възможност за търсене.
2. Кликнете OK за да завършите настройките.
   

Резултат

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

Създайте падащ списък с възможност за търсене с разгъващ се списък и VBA (по-сложен)

Ако просто искате да създадете падащ списък с възможност за търсене, без да указвате конкретен тип падащ списък. Този раздел предоставя алтернативен подход: използване на комбинирано поле с VBA код за постигане на задачата.

Да предположим, че имате списък с имена на държави в колона A, както е показано на екранната снимка по-долу, и сега искате да ги използвате като изходни данни за падащите списъци на searchale, можете да направите следното, за да го направите.

Трябва да вмъкнете разгъващ се списък вместо падащ списък за валидиране на данни във вашия работен лист.

1. Ако Софтуерен Инженер не се показва на лентата, можете да активирате Софтуерен Инженер раздел, както следва.
   1. В Excel 2010 или по-нови версии щракнете досие > Настроики, И в Опции на Excel кликнете върху Персонализирайте лентата в левия прозорец. Отидете в списъчното поле Персонализиране на лентата, отметнете Софтуерен Инженер и след това щракнете върху OK бутон. Вижте екранна снимка:
      
   2. В Excel 2007 щракнете Office бутон> Опции на Excel. В Опции на Excel кликнете върху Популярен в левия панел проверете Показване на раздела Разработчик в лентата и накрая щракнете върху OK бутон.
      
2. След показване на Софтуерен Инженер кликнете върху Софтуерен Инженер > Поставете > Комбинирана кутия.
   
3. Начертайте комбинирано поле в работния лист, щракнете с десния бутон върху него и след това изберете Имоти от менюто с десен бутон.
   
4. в Имоти диалогов прозорец, трябва да:
   1. Изберете Фалшив в AutoWordSelect поле;
   2. Посочете клетка в Свързана клетка поле. В този случай въвеждаме A12;
   3. Изберете 2-fmMatchEntryNone в MatchEntry поле;
   4. Тип Падащ списък в ListFillRange поле;
   5. Затвори Имоти диалогов прозорец. Вижте екранна снимка:
      
5. Сега изключете режима на проектиране, като щракнете Софтуерен Инженер > Режим на проектиране.
6. Изберете празна клетка като C2, въведете формулата по-долу и натиснете Въведете. Те плъзгат манипулатора за автоматично попълване надолу до клетка C9, за да попълнят автоматично клетките със същата формула. Вижте екранна снимка:

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   бележки:

   1. $12 $ е клетката, която сте посочили като Свързана клетка в стъпка 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. Сега трябва да създадете диапазон от имена. Моля щракнете Формула > Определете име.
   
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 за приложения прозорец.

Отсега нататък, когато символ бъде въведен в падащото поле, той ще извърши размито търсене и след това ще изброи съответните стойности в списъка.

Още по темата: