Ustvarite dinamično odvisen spustni seznam v Excelu (korak za korakom)
V tej vadnici bomo korak za korakom predstavili, kako ustvariti odvisen spustni seznam, ki prikazuje izbire glede na vrednosti, izbrane na prvem spustnem seznamu. Z drugimi besedami, naredili bomo seznam za preverjanje veljavnosti podatkov Excel na podlagi vrednosti drugega seznama.
Naredite dinamično odvisen spustni seznam
10s, da s priročnim orodjem naredite odvisen spustni seznam
Ustvarite dinamično odvisen spustni seznam v Excelu 2021 ali Excelu 365
Nekaj vprašanj, ki jih lahko postavite o tej vadnici
Brezplačno prenesite vzorčno datoteko
Video: ustvarite spustni seznam, odvisen od Excela
Naredite dinamično odvisen spustni seznam
1. korak: Vnesite vnose za spustne sezname
1. Najprej vnesite vnose, za katere želite, da se prikažejo na spustnih seznamih, vsak seznam v stolpcu posebej.
Opaziti da bodo postavke v prvem stolpcu (Izdelek) kasneje kot Excelova imena za odvisne sezname. Na primer, tukaj bosta sadje in zelenjava imeni za stolpca B2:B5 in C2:C6 ločeno.
Oglejte si posnetek zaslona:
2. Nato ustvarite tabele za vsak seznam podatkov.
Izberite obseg stolpcev A1:A3, kliknite Vstavi > Tabela, nato v pogovornem oknu Ustvari tabelo označite Moja tabela ima glave potrditveno polje. Kliknite OK.
Nato ponovite ta korak, da ustvarite tabele za druga dva seznama.
Vse tabele in sklicevanje na obsege si lahko ogledate v upravitelju imen (pritisnite Ctrl + F3 da ga odprete).
2. korak: Ustvarite imena obsegov
V tem koraku morate ustvariti imena za glavni seznam in vsak podrejen seznam.
1. Izberite elemente, ki se pojavijo na glavnem seznamu (A2: A3).
2. Nato pojdite na Ime polje ki poleg Vrstica formule.
3. Vnesite ime, tukaj ga poimenujte kot Izdelek.
4. Pritisnite Vnesite ključ za dokončanje.
Nato ponovite zgornje korake, da ločeno ustvarite imena za vsak odvisen seznam.
Tukaj poimenuje drugi stolpec (B2:B5) kot sadje, tretji stolpec (C2:C6) pa kot zelenjavo.
Vsa imena obsegov si lahko ogledate v upravitelju imen (pritisnite Ctrl + F3 da ga odprete).
3. korak: dodajte glavni spustni seznam
Nato dodajte glavni spustni seznam (izdelek), ki je običajni spustni seznam za preverjanje veljavnosti podatkov, ne odvisen spustni seznam.
1. Najprej ustvarite tabelo.
Izberite celico (E1) in vnesite glavo prvega stolpca (Izdelek) in se premaknite v naslednjo celico stolpca (F1), vnesite glavo drugega stolpca (Postavka). V to tabelo boste dodali spustni seznam.
Nato izberite ti dve glavi (E1 in F1), kliknite Vstavi zavihek in izberite Tabela v skupini Tabele.
V pogovornem oknu Ustvari tabelo označite Moja tabela ima glave polje in kliknite OK.
2. Izberite celico E2 v katerega želite vstaviti glavni spustni seznam, kliknite datum in pojdite na Podatkovna orodja skupino, ki jo želite klikniti Preverjanje podatkov > Preverjanje podatkov.
3. V pogovornem oknu Preverjanje podatkov
- Izberite Seznam v Dovoli oddelek,
- Vnesite spodnjo formulo vir bar, izdelek je ime glavnega seznama,
- klik OK.
=Product
Vidite lahko, da je bil ustvarjen glavni spustni seznam.
4. korak: dodajte odvisni spustni seznam
1. Izberite celico F2 kateremu želite dodati odvisni spustni seznam, kliknite datum in pojdite na skupino Podatkovna orodja, da kliknete Preverjanje podatkov > Preverjanje podatkov.
2. V pogovornem oknu Preverjanje podatkov
- Izberite Seznam v Dovoli oddelek,
- Vnesite spodnjo formulo vir E2 je celica, ki vsebuje glavni spustni seznam.
- klik OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Če je E2 prazen (ne izberete nobenega elementa na glavnem spustnem seznamu), boste videli pojavno sporočilo, kot je spodaj, kliknite Da za nadaljevanje.
Sedaj je narejen odvisni spustni seznam.
5. korak: preizkusite odvisni spustni seznam.
1. Izberite sadje na glavnem spustnem seznamu (E2), nato pojdite na odvisni spustni seznam (F2), da kliknete ikono puščice, preverite, ali so sadni predmeti na seznamu, nato pa izberite en element iz odvisnega spustnega seznama.
2. Pritisnite Tab tipko za začetek nove vrstice v tabeli za vnos podatkov izberite Zelenjava, in se premaknite v naslednjo celico na desni, preverite, ali so zelenjavni elementi na seznamu, nato izberite en element iz odvisnega spustnega seznama.
- Če na glavnem spustnem seznamu (stolpec Izdelek) ni izbran noben artikel, odvisni spustni seznam (stolpec Artikel) ne bo deloval.
- Če želite ponastaviti ali počistiti vsebino odvisnega spustnega seznama po izbiri spremenjeno, pojdite na ta članek Kako počistiti odvisno celico spustnega seznama po izbiri spremenjene v Excelu?, vam v pomoč predstavlja kodo VBA.
- Če želite ustvariti 3-nivojski spustni seznam, ta članek Kako ustvariti spustni seznam na več ravneh v Excelu? vam bo pomagal.
10s, da s priročnim orodjem naredite odvisen spustni seznam
Kutools za Excel ponuja zmogljivo orodje za lažji in hitrejši odvisen spustni seznam, poglejmo:
Preden sledite spodnjim korakom, prosim kliknite za prenos Kutools for Excel za 30-dnevno brezplačno preskusno različico najprej.
1. korak: Vnesite vnose za spustni seznam
Najprej razporedite svoje podatke, kot je prikazano na spodnjem posnetku zaslona:
2. korak: Uporaba orodja Kutools
1. Izberite podatke, ki ste jih ustvarili, kliknite Kutools in kliknite Spustni seznam za prikaz podmenija kliknite Dinamični spustni seznam.
2. Na spustnem seznamu Odvisni
- Prijava Način B ki ustreza vašemu podatkovnemu načinu,
- Izberite izhodni razpon, mora biti stolpec izhodnega obsega enak stolpcu obsega podatkov,
- klik Ok.
Zdaj je ustvarjen odvisni spustni seznam.
- Način B podpira ustvarjanje tretje ali več ravni spustnega seznama:
- Če so vaši podatki urejeni, kot prikazuje spodnji posnetek zaslona, morate uporabiti način A, način A podpira samo ustvarjanje dvonivojskega odvisnega spustnega seznama.
- Več podrobnosti o uporabi Kutools za ustvarjanje odvisnega spustnega seznama obiščite to tutorski .
Ustvarite dinamično odvisen spustni seznam v Excelu 2021 ali Excelu 365
Če uporabljate Excel 2021 ali Excel 365, lahko na drug način hitro ustvarite dinamično odvisen spustni seznam z uporabo novih funkcij UNIQUE in FILTER.
Če predpostavimo, da so vaši izvorni podatki urejeni, kot je prikazan na posnetku zaslona, sledite spodnjim korakom, da ustvarite dinamični spustni seznam.
1. korak: Uporaba formule za pridobivanje elementov za glavni spustni seznam
Izberite celico, na primer celico G3, in s funkcijama UNIQUE in FILTER izvlecite edinstvene vrednosti iz Izdelek seznam, ki bo vir glavnega spustnega seznama, in pritisnite Vnesite ključ.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
2. korak: Ustvarite glavni spustni seznam
1. Izberite celico, ki jo želite postaviti na glavni spustni seznam, na primer celica D3, Kliknite datum in pojdite na Podatkovna orodja skupino, ki jo želite klikniti Preverjanje podatkov > Preverjanje podatkov.
2. V pogovornem oknu Preverjanje podatkov
- Izberite Seznam v Dovoli oddelek,
- Vnesite spodnjo formulo vir bar,
- klik OK.
=$G$3#
Zdaj je ustvarjen glavni spustni seznam.
3. korak: Uporaba formule za pridobivanje elementov za odvisni spustni seznam
Izberite celico, na primer celico H3, s funkcijo FILTER za filtriranje elementov glede na vrednost v celici D3 (izbrani element na glavnem spustnem seznamu), pritisnite Vnesite ključ.
=FILTER(B3:B20, A3:A20=D3)
4. korak: Ustvarite odvisen spustni seznam
1. Izberite celico, ki bo postavila odvisni spustni seznam, na primer celica E3, Kliknite datum in pojdite na Podatkovna orodja skupino, ki jo želite klikniti Preverjanje podatkov > Preverjanje podatkov.
2. V pogovornem oknu Preverjanje podatkov
- Izberite Seznam v Dovoli oddelek,
- Vnesite spodnjo formulo vir bar,
- klik OK.
=$H$3#
Zdaj je odvisni spustni seznam uspešno ustvarjen.
Ko dodate nove elemente ali naredite nekaj sprememb v A3:A20, se spustni seznam samodejno posodobi.
Razvrsti spustni seznam po abecedi
Če želite elemente na spustnem seznamu razporediti po abecedi, lahko uporabite spodnjo formulo za pripravljalno tabelo.Za glavni spustni meni (formula v celici G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
Za odvisni spustni meni (formula v celici H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
Zdaj sta oba spustna seznama razvrščena po abecedi od A do Ž.
Če želite biti razvrščeni po abecedi od Ž do A, uporabite spodnjo formulo:
Za glavni spustni meni (formula v celici G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
Za odvisni spustni meni (formula v celici H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
Nekaj vprašanj, ki si jih lahko zastavite:
1. Zakaj vstaviti tabelo za vsak seznam podatkov?
Vstavljanje tabele za seznam podatkov vam bo pomagalo samodejno posodobiti spustni seznam na podlagi sprememb na seznamu podatkov. Če na primer dodate »Drugo« na prvi podatkovni seznam, bo glavni spustni seznam samodejno dodan z »Drugi«.
2. Zakaj uporabljati tabelo za postavitev spustnih seznamov?
Ko pritisnete tipko Tab, da dodate novo vrstico v tabelo, bodo spustni seznami samodejno dodani tudi v novo vrstico.
3. Kako deluje funkcija INDIRECT?
INDIRECT funkcija se uporablja za pretvorbo besedilnega niza v veljavno referenco.
4. Kako deluje formula INDIRECT(SUBSTITUTE(E2&F2," ",""))?
Prvič, NAMESTITEV funkcija nadomesti besedilo z drugim besedilom. Tukaj se je uporabljalo za odstranjevanje presledkov iz kombiniranih imen (E2 in F2). Potem INDIRECT funkcija pretvori besedilni niz (združeno vsebino z E2 in F2) v veljavno referenco.
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!