Preskoči na glavno vsebino

Najboljši vodnik po spustnem seznamu, ki omogoča iskanje, v Excelu

Ustvarjanje spustnih seznamov v Excelu poenostavi vnos podatkov in zmanjša število napak. Toda z večjimi nabori podatkov postane drsenje po dolgih seznamih okorno. Ali ne bi bilo lažje samo vtipkati in hitro poiskati svoj predmet? A "spustni seznam, po katerem je mogoče iskati" ponuja to udobje. Ta vodnik vas bo vodil skozi štiri metode za nastavitev takega seznama v Excelu.


Video


Iskalni spustni seznam v Excelu 365

Excel 365 je na svoje spustne sezname za preverjanje podatkov uvedel težko pričakovano funkcijo: možnost iskanja po seznamu. S funkcijo iskanja lahko uporabniki hitro najdejo in izberejo elemente na učinkovitejši način. Ko vstavite spustni seznam kot običajno, kliknite celico s spustnim seznamom in začnite tipkati. Seznam se bo takoj filtriral, da bo ustrezal vnesenemu besedilu.

V tem primeru tipkam San v celici in spustni seznam filtrira mesta, ki se začnejo z iskalnim izrazom San, Kot je San Francisco in San Diego. Nato lahko z miško izberete rezultat ali uporabite puščične tipke in pritisnete Enter.

Opombe:
  • O iskanje se začne od prve črke vsake besede na spustnem seznamu. Če vnesete znak, ki se ne ujema z začetnim znakom katere koli besede, seznam ne bo prikazal ustreznih elementov.
  • Ta funkcija je na voljo samo v najnovejši različici programa Excel 365.
  • Če vaša različica Excela ne podpira te funkcije, tukaj priporočamo Spustni seznam, ki ga je mogoče iskati značilnost Kutools za Excel. Omejitev različice Excela ni in ko je omogočena, lahko preprosto poiščete želeni element na spustnem seznamu tako, da preprosto vnesete ustrezno besedilo. Oglejte si podrobne korake.

Ustvari spustni seznam po možnostih iskanja (za Excel 2019 in novejše)

Če uporabljate Excel 2019 ali novejše različice, lahko metodo v tem razdelku uporabite tudi za omogočanje iskanja po spustnem seznamu v Excelu.

Ob predpostavki, da ste ustvarili spustni seznam v celici A2 lista 2 (slika na desni) z uporabo podatkov v obsegu A2:A8 lista 1 (slika na levi), sledite tem korakom, da omogočite iskanje po seznamu.

Korak 1. Ustvarite pomožni stolpec, ki navaja iskalne elemente

Tukaj potrebujemo pomožni stolpec za seznam postavk, ki se ujemajo z vašimi izvornimi podatki. V tem primeru bom ustvaril pomožni stolpec v stolpec D of Sheet1.

  1. Izberite prvo celico D1 v stolpec D in vnesite naslov stolpca, na primer "Rezultati iskanja" v tem primeru.
  2. Vnesite naslednjo formulo v celico D2 in pritisnite Vnesite.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
Opombe:
  • V tej formuli: A2: A8 je obseg izvornih podatkov. List2!A2 je lokacija spustnega seznama, kar pomeni, da se spustni seznam nahaja v A2 lista Sheet2. Spremenite jih glede na svoje podatke.
  • Če s spustnega seznama v A2 lista Sheet2 ni izbran noben element, bo formula prikazala vse elemente iz izvornih podatkov, kot je prikazano na zgornji sliki. Nasprotno, če je izbran element, bo D2 prikazal ta element kot rezultat formule.
2. korak: Ponovno konfigurirajte spustni seznam
  1. Izberite celico spustnega seznama (v tem primeru izberem celico A2 lista Sheet2), nato pojdite na izbiro datum > Preverjanje podatkov > Preverjanje podatkov.
  2. v Preverjanje podatkov pogovorno okno, morate konfigurirati, kot sledi.
    1. Pod Nastavitve Kliknite jeziček gumb v vir škatla.
    2. O Preverjanje podatkov pogovorno okno bo preusmerjeno na Sheet1, izberite celico (npr. D2) s formulo iz 1. koraka, dodajte # in kliknite Zapri gumb.
    3. Pojdi na Opozorilo o napaki jeziček, počistite polje Pokaži opozorilo o napaki po vnosu neveljavnih podatkov potrditveno polje in na koncu kliknite OK Gumb, da shranite spremembe.
Rezultat

Po spustnem seznamu v celici A2 lista Sheet2 je zdaj mogoče iskati. Vnesite besedilo v celico, kliknite spustno puščico, da razširite spustni seznam, in videli boste, da je seznam takoj filtriran, da se ujema z vnesenim besedilom.

Opombe:
  • Ta metoda je na voljo samo za Excel 2019 in novejše različice.
  • Ta metoda deluje samo na eni celici spustnega seznama naenkrat. Če želite omogočiti iskanje po spustnih seznamih v celicah od A3 do A8 v Sheet2, je treba zgoraj omenjene korake ponoviti za vsako celico.
  • Ko vnesete besedilo v celico spustnega seznama, se spustni seznam ne razširi samodejno, morate klikniti spustno puščico, da ga razširite ročno.

Preprosto ustvarite spustni seznam, po katerem je mogoče iskati (za vse različice Excela)

Glede na različne omejitve zgornjih metod je tukaj zelo učinkovito orodje za vas – Kutools za Excel's Spustni seznam, ki ga je mogoče iskati funkcija. Ta funkcija je na voljo v vseh različicah Excela in ne deluje le na vseh spustnih seznamih znotraj določenega obsega, temveč tudi na vseh spustnih seznamih v trenutnem delovnem listu ali trenutnem delovnem zvezku, kar vam omogoča preprosto iskanje želenih elementov v spustni seznami.

po prenos in namestitev Kutools for Exceltako, da izberete Kutools > Spustni seznam > Spustni seznam, ki ga je mogoče iskati da omogočite to funkcijo. V Spustni seznam, ki ga je mogoče iskati pogovorno okno, morate:

  1. Določite možnost, ki jo potrebujete v Nastavite obseg spustni seznam.
    V tem primeru moram omogočiti iskanje po spustnem seznamu v določenem obsegu, zato izberem Določite obseg in izberite obseg.
  2. klik OK.
Rezultat

Ko kliknete celico spustnega seznama v določenem obsegu, se na desni prikaže polje s seznamom. Vnesite besedilo, da takoj filtrirate seznam, nato izberite element ali uporabite puščične tipke in pritisnite Enter, da ga dodate v celico.

Opombe:
  • Ta funkcija ponuja tri uporabne možnosti:
    • Določite obseg: če izberete to možnost in določite obseg celic, je mogoče iskati samo po spustnih seznamih v tem določenem obsegu.
    • Trenutni delovni zvezek: Če izberete to možnost, bo mogoče iskati po vseh spustnih seznamih v trenutnem delovnem zvezku.
    • Trenutni delovni list: če izberete to možnost, je mogoče iskati samo po spustnih seznamih na trenutnem delovnem listu.
  • Ta funkcija podpira iskanje iz katerega koli položaja znotraj besed. To pomeni, da bodo ujemajoči se elementi še vedno najdeni in prikazani, tudi če vnesete znak, ki je na sredini ali koncu besede, kar ponuja obsežnejšo in uporabniku prijaznejšo izkušnjo iskanja.
  • Če želite izvedeti več o tej funkciji, prosim obiščite to stran.
  • Če želite uporabiti to funkcijo, prosim prenesite in namestite Kutools za Excel najprej.

Ustvarite spustni seznam po možnostih iskanja s kombiniranim poljem in VBA (bolj zapleteno)

Če preprosto želite ustvariti spustni seznam, po katerem je mogoče iskati, ne da bi določili določeno vrsto spustnega seznama. Ta razdelek ponuja alternativni pristop: uporaba kombiniranega polja s kodo VBA za doseganje naloge.

Recimo, da imate seznam imen držav v stolpcu A, kot je prikazano na spodnjem posnetku zaslona, ​​in zdaj jih želite uporabiti kot izvorne podatke spustnih seznamov iskanja, lahko storite naslednje, da to storite.

V svoj delovni list morate namesto spustnega seznama za preverjanje veljavnosti vstaviti kombinirano polje.

  1. Če Razvojni zavihek se ne prikaže na traku, lahko omogočite Razvojni zavihek, kot sledi.
    1. V Excelu 2010 ali novejših različicah kliknite file > možnosti. In v Možnosti Excela pogovorno okno, kliknite Prilagodite trak v levem podoknu. Pojdite na seznamsko polje Prilagodi trak in potrdite polje Razvojni in nato kliknite OK . Oglejte si posnetek zaslona:
    2. V Excelu 2007 kliknite Office gumb> Možnosti programa Excel. v Možnosti programa Excel pogovorno okno, kliknite Popular v levem podoknu preverite Pokaži zavihek za razvijalce na traku in na koncu kliknite OK gumb.
  2. Po prikazu Razvojni jeziček, kliknite Razvojni > Vstavi > Kombinirana škatla.
  3. Narišite kombinirano polje na delovnem listu, ga kliknite z desno miškino tipko in nato izberite Nepremičnine v meniju z desnim klikom.
  4. v Nepremičnine pogovorno okno, morate:
    1. Izberite False v AutoWordSelect polje;
    2. Določite celico v Povezana celica polje. V tem primeru vnesemo A12;
    3. Izberite 2-fmMatchEntryNone v MatchEntry polje;
    4. tip Spustni seznam v ListFillRange polje;
    5. Zapri Nepremičnine pogovorno okno. Oglejte si posnetek zaslona:
  5. Zdaj s klikom izklopite način oblikovanja Razvojni > Način oblikovanja.
  6. Izberite prazno celico, kot je C2, vnesite spodnjo formulo in pritisnite Vnesite. Njegovo ročico samodejnega izpolnjevanja povlečejo navzdol v celico C9, da samodejno zapolnijo celice z isto formulo. Glej posnetek zaslona:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    Opombe:
    1. $ A $ 12 je celica, ki ste jo navedli kot Povezana celica v koraku 4;
    2. Ko končate zgornje korake, lahko zdaj preizkusite: v kombinirano polje vnesite črko C in nato vidite, da so celice formule, ki se sklicujejo na celice, ki vsebujejo znak C, zapolnjene s številko 1.
  7. Izberite celico D2, vnesite spodnjo formulo in pritisnite Vnesite. Nato povlecite ročico samodejnega izpolnjevanja navzdol do celice D9.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. Izberite celico E2, vnesite spodnjo formulo in pritisnite Vnesite. Nato povlecite ročico samodejnega izpolnjevanja navzdol do E9, da uporabite isto formulo.
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. Zdaj morate ustvariti obseg imen. Kliknite prosim Formula > Določite ime.
  10. v Novo ime pogovorno okno, vnesite Spustni seznamIme polje, vnesite spodnjo formulo v Se nanaša na in nato kliknite OK gumb.
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. Zdaj s klikom vklopite način oblikovanja Razvojni > Način oblikovanja. Nato dvokliknite kombinirano polje, da odprete Microsoft Visual Basic za aplikacije okno.
  12. Kopirajte in prilepite spodnjo kodo VBA v urejevalnik kode.
    Koda VBA: omogoči iskanje po spustnem seznamu
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej, ko je znak vnesen v kombinirano polje, bo opravil mehko iskanje in nato na seznamu navedel ustrezne vrednosti.

Opombe: Ta delovni zvezek morate shraniti kot datoteko delovnega zvezka z omogočenimi makri v Excelu, da ohranite kodo VBA za nadaljnjo uporabo.

Najboljša orodja za pisarniško produktivnost

Kutools za Excel - vam pomaga izstopati iz množice

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike  |  Izbriši prazne vrstice  |  Združite stolpce ali celice brez izgube podatkov  |  Krog brez formule ...
Super VLookup: Več meril  |  Več vrednosti  |  Na več listih  |  Nejasno iskanje...
Adv. Spustni seznam: Preprost spustni seznam  |  Odvisni spustni seznam  |  Večkrat izberite spustni seznam...
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  Primerjaj stolpce z Izberite Enake in različne celice ...
Predstavljene funkcije: Mrežni fokus  |  Pogled oblikovanja  |  Velika vrstica formule  |  Upravitelj delovnih zvezkov in listov | Knjižnica virov (Samodejno besedilo)  |  Izbirnik datuma  |  Združite delovne liste  |  Šifriranje/dešifriranje celic  |  Pošljite e-pošto po seznamu  |  Super filter  |  Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake ...)  |  50 + Graf Vrste (Gantt Chart ...)  |  40+ Praktično Formule (Izračunajte starost glede na rojstni dan ...)  |  19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti ...)  |  12 Pretvorba Orodja (Številke v besede, Pretvorba valut ...)  |  7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeli Excelove celice ...)  |  ... in več

Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...


Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)

  • Eno sekundo za preklop med desetinami odprtih dokumentov!
  • Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
  • Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
  • Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.