Creați o listă drop-down dependentă dinamică în Excel (pas cu pas)
Aici, în acest tutorial, vom prezenta pas cu pas cum să creați o listă derulantă dependentă care afișează opțiuni în funcție de valorile selectate în prima listă derulantă. Cu alte cuvinte, vom face o listă de validare a datelor Excel pe baza valorii altei liste.
Creați o listă derulantă dinamică dependentă
10s pentru a face o listă derulantă dependentă cu un instrument la îndemână
Creați o listă derulantă dinamică dependentă în Excel 2021 sau Excel 365
Câteva întrebări pe care le puteți pune despre acest tutorial
Descărcați gratuit fișierul eșantion
Video: Creați o listă derulantă dependentă de Excel
Creați o listă derulantă dinamică dependentă
Pasul 1: Introduceți intrările pentru listele drop-down
1. Mai întâi, tastați intrările pe care doriți să apară în listele derulante, fiecare listă într-o coloană separat.
Observa că articolele din prima coloană (Produs) vor fi mai târziu ca nume Excel pentru listele dependente. De exemplu, aici Fructe și Legume vor fi Numele pentru coloana B2:B5 și C2:C6 separat.
Vedeți captura de ecran:
2. Apoi creați tabele pentru fiecare listă de date.
Selectați intervalul de coloană A1:A3, faceți clic Insera > Tabel, apoi în caseta de dialog Creare tabel, bifați Masa mea are antete Caseta de bifat. Clic OK.
Apoi repetați acest pas pentru a crea tabele pentru celelalte două liste.
Puteți vizualiza toate tabelele și referința la intervale în Name Manager (apăsați Ctrl + F3 pentru a-l deschide).
Pasul 2: Creați nume de intervale
În acest pas, trebuie să creați Alte denumiri pentru lista principală și pentru fiecare listă dependentă.
1. Selectați elementele care apar în lista principală (A2: A3).
2. Apoi mergeți la Caseta de nume care pe lângă Bara de formule.
3. Introduceți numele, aici îl denumește ca Produs.
4. presa Intrați cheie pentru a finaliza.
Apoi repetați pașii de mai sus pentru a crea separat Nume pentru fiecare listă dependentă.
Aici denumește a doua coloană (B2:B5) drept Fructe, iar a treia coloană (C2:C6) ca Legume.
Puteți vizualiza toate numele intervalelor în Manager nume (apăsați Ctrl + F3 pentru a-l deschide).
Pasul 3: Adăugați lista principală derulantă
Apoi, adăugați lista derulantă principală (Produs), care este o listă derulantă normală de validare a datelor, nu o listă derulantă dependentă.
1. În primul rând, creați un tabel.
Selectați o celulă (E1), și tastați primul antet de coloană (Produs) și treceți la următoarea celulă a coloanei (F1), tastați antetul al doilea de coloană (Articol). Veți adăuga lista derulantă la acest tabel.
Apoi selectați aceste două anteturi (E1 și F1), faceți clic pe Insera fila, și selectați Tabel în grupul Tabele.
În caseta de dialog Creare tabel, bifați Masa mea are antete caseta și faceți clic pe OK.
2. Selectați celula E2 în care doriți să inserați lista principală derulantă, faceți clic Date filă și accesați Instrumente de date grup pentru a face clic Data validarii > Data validarii.
3. În dialogul de validare a datelor,
- Alege Listă în Permite secțiune,
- Tastați formula de mai jos în Sursă bara, Produsul este numele listei principale,
- Clic OK.
=Product
Puteți vedea că lista derulantă principală a fost creată.
Pasul 4: Adăugați o listă derulantă dependentă
1. Selectați celula F2 la care doriți să adăugați lista derulantă dependentă, faceți clic Date fila și accesați grupul Instrumente de date pentru a face clic Data validarii > Data validarii.
2. În dialogul de validare a datelor,
- Alege Listă în Permite secțiune,
- Introduceți formula de mai jos în Sursă bară, E2 este celula care conține lista principală derulantă.
- Clic OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))
Dacă E2 este gol (nu selectați niciun element din lista principală derulantă), veți vedea un mesaj ca mai jos, faceți clic Da pentru a continua.
Acum a fost făcută lista derulantă dependentă.
Pasul 5: Testați lista drop-down dependentă.
1. Selectați Fruct în lista principală derulantă (E2), apoi accesați lista derulantă dependentă (F2) pentru a face clic pe pictograma săgeată, vedeți dacă fructele sunt în listă, apoi selectați un articol din lista derulantă dependentă.
2. presa Tab pentru a începe un nou rând în tabelul de introducere a datelor, selectați De legume, și treceți la următoarea celulă din dreapta, vedeți dacă articolele de legume sunt în listă, apoi selectați un articol din lista derulantă dependentă.
- Dacă nu există niciun articol selectat în lista derulantă principală (coloana Produs), lista derulantă dependentă (coloana Articol) nu va funcționa.
- Dacă doriți să resetați sau să ștergeți conținutul listei derulante dependente după ce ați selectat modificat, accesați acest articol Cum se șterge celula de listă verticală dependentă după selectarea modificată în Excel?, introduce un cod VBA pentru a vă ajuta.
- Dacă doriți să creați o listă derulantă pe 3 niveluri, acest articol Cum se creează o listă verticală dependentă de niveluri multiple în Excel? te va ajuta.
10s pentru a face o listă derulantă dependentă cu un instrument la îndemână
Kutools pentru Excel oferă un instrument puternic pentru a face o listă derulantă dependentă mai ușoară și mai rapidă, să vedem:
Înainte de a urma pașii de mai jos, vă rugăm faceți clic pentru a descărca Kutools pentru Excel pentru o încercare gratuită de 30 de zile in primul rand.
Pasul 1: Introduceți intrările pentru lista derulantă
În primul rând, aranjați-vă datele așa cum se arată în captura de ecran de mai jos:
Pasul 2: Aplicarea instrumentului Kutools
1. Selectați datele pe care le-ați creat, faceți clic Kutools și faceți clic pe Lista verticală pentru a afișa submeniul, faceți clic Listă derulantă dinamică.
2. În Lista derulantă Dependent
- Verifică Mod B care se potrivește cu modul dvs. de date,
- selectaţi domeniul de ieșire, coloana intervalului de ieșire trebuie să fie egală cu coloana intervalului de date,
- Clic Ok.
Acum a fost creată lista derulantă dependentă.
- Modul B acceptă crearea a treia sau a mai multor liste derulante de niveluri:
- Dacă datele dvs. sunt aranjate așa cum arată captura de ecran de mai jos, trebuie să utilizați Modul A, Modul A acceptă doar pentru a crea o listă derulantă dependentă de 2 niveluri.
- Mai multe detalii despre cum să utilizați Kutools pentru a crea o listă derulantă dependentă, vă rugăm să vizitați acest tutorial .
Creați o listă derulantă dinamică dependentă în Excel 2021 sau Excel 365
Dacă sunteți în Excel 2021 sau Excel 365, există o altă modalitate de a crea rapid o listă derulantă dependentă dinamică folosind funcții noi UNIC și FILTRU.
Presupunând că datele dumneavoastră sursă sunt aranjate așa cum se arată în captura de ecran, urmați pașii de mai jos pentru a crea lista dinamică derulantă.
Pasul 1: Folosind formula pentru a obține articole pentru lista principală derulantă
Selectați o celulă, de exemplu, celula G3, și folosind funcțiile UNIQUE și FILTER pentru a extrage valorile unice din Produs listă care va fi sursa listei derulante principale și apăsați Intrați cheie.
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Pasul 2: Creați lista principală derulantă
1. Selectați o celulă în care doriți să plasați lista principală derulantă, de exemplu, celulă D3, Faceți clic pe Date filă și accesați Instrumente de date grup pentru a face clic Data validarii > Data validarii.
2. În dialogul de validare a datelor,
- Alege Listă în Permite secțiune,
- Tastați formula de mai jos în Sursă bar,
- Clic OK.
=$G$3#
Acum este creată lista principală derulantă.
Pasul 3: Folosind formula pentru a obține articole pentru lista derulantă dependentă
Selectați o celulă, de exemplu, celula H3, folosind funcția FILTER pentru a filtra elementele pe baza valorii din celulă D3 (articolul selectat din lista derulantă principală), apăsați Intrați cheie.
=FILTER(B3:B20, A3:A20=D3)
Pasul 4: Creați lista derulantă dependentă
1. Selectați o celulă care va plasa lista derulantă dependentă, de exemplu, celulă E3, Faceți clic pe Date filă și accesați Instrumente de date grup pentru a face clic Data validarii > Data validarii.
2. În dialogul de validare a datelor,
- Alege Listă în Permite secțiune,
- Tastați formula de mai jos în Sursă bar,
- Clic OK.
=$H$3#
Acum lista derulantă dependentă este creată cu succes.
Când adăugați elemente noi sau faceți unele modificări în A3:A20, lista derulantă va fi actualizată automat.
Sortați lista derulantă în ordine alfabetică
Dacă doriți să aranjați elementele din lista derulantă în ordine alfabetică, puteți utiliza formula de mai jos în tabelul de pregătire.Pentru meniul derulant principal (formula din celula G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
Pentru meniul derulant dependent (formula din celula H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
Acum ambele liste derulante sunt sortate alfabetic de la A la Z.
Pentru a sorta alfabetic de la Z la A, vă rugăm să utilizați formula de mai jos:
Pentru meniul derulant principal (formula din celula G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
Pentru meniul derulant dependent (formula din celula H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
Câteva întrebări pe care le puteți pune:
1. De ce să inserați un tabel pentru fiecare listă de date?
Inserarea unui tabel pentru lista de date vă va ajuta să actualizați automat lista derulantă pe baza modificărilor din lista de date. De exemplu, adăugarea „Alții” în prima listă de date, apoi lista principală derulantă va fi adăugată automat cu „Alții”.
2. De ce să folosiți un tabel pentru a plasa liste derulante?
Când apăsați tasta Tab pentru a adăuga o nouă linie la tabel, listele drop-down vor fi adăugate automat și în noua linie.
3. Cum funcționează funcția INDIRECT?
INDIRECT funcția este utilizată pentru a converti un șir de text într-o referință validă.
4. Cum funcționează formula INDIRECT(SUBSTITUTE(E2&F2," ",""))?
În primul rând, SUBSTITUI funcția înlocuiește textul cu un alt text. Aici era folosit pentru a elimina spațiile din numele combinate (E2 și F2). Apoi INDIRECT funcția convertește șirul de text (conținutul combinat de E2 și F2) într-o referință validă.
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!