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.
- Iskalni spustni seznam v Excelu 365
- Ustvari spustni seznam po možnostih iskanja (za Excel 2019 in novejše)
- Preprosto ustvarite spustni seznam, po katerem je mogoče iskati (za vse različice Excela)
- Ustvarite spustni seznam po možnostih iskanja s kombiniranim poljem in VBA (bolj zapleteno)
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.
- 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.
- Izberite prvo celico D1 v stolpec D in vnesite naslov stolpca, na primer "Rezultati iskanja" v tem primeru.
- Vnesite naslednjo formulo v celico D2 in pritisnite Vnesite.
=FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
- 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
- Izberite celico spustnega seznama (v tem primeru izberem celico A2 lista Sheet2), nato pojdite na izbiro datum > Preverjanje podatkov > Preverjanje podatkov.
- v Preverjanje podatkov pogovorno okno, morate konfigurirati, kot sledi.
- Pod Nastavitve Kliknite jeziček gumb v vir škatla.
- O Preverjanje podatkov pogovorno okno bo preusmerjeno na Sheet1, izberite celico (npr. D2) s formulo iz 1. koraka, dodajte # in kliknite Zapri gumb.
- 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.
- 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 Omogoči iskanje po spustnem seznamu, samodejno pojavno oknofunkcija. Ta funkcija je na voljo v vseh različicah Excela in vam omogoča preprosto iskanje želenega elementa na spustnem seznamu s preprosto nastavitvijo.
po prenos in namestitev Kutools for Exceltako, da izberete Kutools > Spustni seznam > Omogoči iskanje po spustnem seznamu, samodejno pojavno okno da omogočite to funkcijo. V Omogočite iskanje po spustnem seznamu pogovorno okno, morate:
- Izberite obseg, ki vsebuje spustne sezname, ki jih je treba nastaviti kot spustne sezname, po katerih je mogoče iskati.
- klik OK za dokončanje nastavitev.
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 Vnesite da ga dodate v celico.
- 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.
- Če Razvojni zavihek se ne prikaže na traku, lahko omogočite Razvojni zavihek, kot sledi.
- 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:
- 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.
- Po prikazu Razvojni jeziček, kliknite Razvojni > Vstavi > Kombinirana škatla.
- Narišite kombinirano polje na delovnem listu, ga kliknite z desno miškino tipko in nato izberite Nepremičnine v meniju z desnim klikom.
- v Nepremičnine pogovorno okno, morate:
- Izberite False v AutoWordSelect polje;
- Določite celico v Povezana celica polje. V tem primeru vnesemo A12;
- Izberite 2-fmMatchEntryNone v MatchEntry polje;
- tip Spustni seznam v ListFillRange polje;
- Zapri Nepremičnine pogovorno okno. Oglejte si posnetek zaslona:
- Zdaj s klikom izklopite način oblikovanja Razvojni > Način oblikovanja.
- 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:- $ A $ 12 je celica, ki ste jo navedli kot Povezana celica v koraku 4;
- 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.
- 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),"")
- 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)),"")
- Zdaj morate ustvariti obseg imen. Kliknite prosim Formula > Določite ime.
- v Novo ime pogovorno okno, vnesite Spustni seznam v Ime 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)
- Zdaj s klikom vklopite način oblikovanja Razvojni > Način oblikovanja. Nato dvokliknite kombinirano polje, da odprete Microsoft Visual Basic za aplikacije okno.
- 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
- 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.
Sorodni članki:
Samodokončanje pri vnašanju spustnega seznama v Excelu
Če imate spustni seznam za preverjanje veljavnosti podatkov z velikimi vrednostmi, se morate na seznamu pomakniti navzdol, samo da bi našli pravega, ali pa v besedilno polje vnesite celo besedo. Če obstaja način, ki omogoča samodejno dokončanje pri vnosu prve črke na spustnem seznamu, bo vse postalo lažje. Ta vadnica ponuja metodo za reševanje težave.
Ustvari spustni seznam iz drugega delovnega zvezka v Excelu
Na delovnih listih v delovnem zvezku je zelo enostavno ustvariti spustni seznam za preverjanje veljavnosti podatkov. Če pa se podatki seznama, ki jih potrebujete za preverjanje podatkov, nahajajo v drugi delovni knjigi, kaj bi storili? V tej vadnici boste podrobno izvedeli, kako iz drugega delovnega zvezka v Excelu ustvarite spustni seznam.
Ustvarite spustni seznam, ki ga je mogoče iskati v Excelu
Za spustni seznam s številnimi vrednostmi iskanje pravega ni lahko delo. Prej smo uvedli način samodejnega dokončanja spustnega seznama, ko v spustnem polju vnesemo prvo črko. Poleg funkcije samodokončanja lahko po spustnem seznamu omogočite tudi iskanje za povečanje delovne učinkovitosti pri iskanju ustreznih vrednosti na spustnem seznamu. Če želite omogočiti iskanje po spustnem seznamu, poskusite z metodo v tej vadnici.
Samodejno izpolnite druge celice pri izbiri vrednosti v spustnem seznamu Excel
Recimo, da ste ustvarili spustni seznam na podlagi vrednosti v obsegu celic B8: B14. Ko izberete katero koli vrednost na spustnem seznamu, želite, da se ustrezne vrednosti v območju celic C8: C14 samodejno vnesejo v izbrano celico. Za rešitev težave vam bodo storitve v tej vadnici naredile uslugo.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
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.
Kazalo
Ustvarite spustni seznam, ki omogoča iskanje
- Video
- Za Excel 365
- Za Excel 2019 in novejše različice
- Za vse različice Excela (enostavno)
- Za vse različice Excela (kompleksni VBA)
- Povezani članki
- Najboljša orodja za pisarniško produktivnost
- Komentarji