Preskoči na glavno vsebino

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

spustni seznam, odvisen od dokumenta 1 1 1

Brezplačno prenesite vzorčno datoteko doc vzorec


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:

spustni seznam, odvisen od dokumenta 1 2

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.

spustni seznam, odvisen od dokumenta 1 3

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).

spustni seznam, odvisen od dokumenta 1 4

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.

spustni seznam, odvisen od dokumenta 1 5

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.

spustni seznam, odvisen od dokumenta 1 15

spustni seznam, odvisen od dokumenta 1 6

Vsa imena obsegov si lahko ogledate v upravitelju imen (pritisnite Ctrl + F3 da ga odprete).

spustni seznam, odvisen od dokumenta 1 7

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.

spustni seznam, odvisen od dokumenta 1 8

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.

spustni seznam, odvisen od dokumenta 1 9

3. V pogovornem oknu Preverjanje podatkov

  • Izberite Seznam v Dovoli oddelek,
  • Vnesite spodnjo formulo vir bar, izdelek je ime glavnega seznama,
  • klik OK.
=Product

spustni seznam, odvisen od dokumenta 1 10

Vidite lahko, da je bil ustvarjen glavni spustni seznam.

spustni seznam, odvisen od dokumenta 1 11

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," ","_"))

spustni seznam, odvisen od dokumenta 1 12

Če je E2 prazen (ne izberete nobenega elementa na glavnem spustnem seznamu), boste videli pojavno sporočilo, kot je spodaj, kliknite Da za nadaljevanje.

spustni seznam, odvisen od dokumenta 1 13

Sedaj je narejen odvisni spustni seznam.

spustni seznam, odvisen od dokumenta 1 14

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.

gif 1

Opombe:

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:

kte gif 1

1. korak: Vnesite vnose za spustni seznam

Najprej razporedite svoje podatke, kot je prikazano na spodnjem posnetku zaslona:

doc kutools dinamični spustni seznam 1

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.

doc kutools dinamični spustni seznam 2

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.

doc kutools dinamični spustni seznam 3

Zdaj je ustvarjen odvisni spustni seznam.

doc kutools dinamični spustni seznam 4

nasveti:
  • Način B podpira ustvarjanje tretje ali več ravni spustnega seznama:
    doc kutools dinamični spustni seznam 5 1
  • Č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.
    doc kutools dinamični spustni seznam 6
  • Več podrobnosti o uporabi Kutools za ustvarjanje odvisnega spustnega seznama obiščite to tutorski .

Kutools za Excel

Brezplačna 30-dnevna preizkusna funkcija, kreditna kartica ni potrebna.

Več kot 300 zmogljivih naprednih funkcij in funkcij za Excel.

Ne potrebujete posebnih veščin, s čimer prihranite ure časa vsak dan.

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<>""))
Opombe: Ko so izdelki v A3:A12, v matriko dodamo 8 dodatnih celic, da poskrbimo za morebitne nove vnose. Poleg tega v UNIQUE vdelamo funkcijo FILTER, da izvlečemo edinstvene vrednosti brez presledkov.

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#
Opombe: To se imenuje sklic na obseg razlitja in ta sintaksa se nanaša na celoten obseg, ne glede na to, koliko se razširi ali skrči.

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)
Opombe: Če je na glavnem spustnem seznamu prazno, se bo formula vrnila na ničle.

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#
Opombe: To se imenuje sklic na obseg razlitja in ta sintaksa se nanaša na celoten obseg, ne glede na to, koliko se razširi ali skrči.

Zdaj je odvisni spustni seznam uspešno ustvarjen.

Ko dodate nove elemente ali naredite nekaj sprememb v A3:A20, se spustni seznam samodejno posodobi.

nasveti:

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 Ž.

spustni meni, odvisen od dokumenta 365 8

Č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«.

posodobitev spustnega seznama, odvisna od dokumenta

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

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
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 iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite 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 obsege in stolpce ...
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, Razdeljene celice,...)   |   ... in več

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...

Opis


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations