Crearea unei liste drop-down dependente în Excel (Tutorial pas cu pas)

Vizionați video - Crearea unei liste drop-down dependente în Excel

O listă verticală Excel este o caracteristică utilă atunci când creați formulare de introducere a datelor sau tablouri de bord Excel.

Afișează o listă de articole ca o listă verticală într-o celulă, iar utilizatorul poate face o selecție din lista verticală. Acest lucru ar putea fi util atunci când aveți o listă de nume, produse sau regiuni pe care adesea trebuie să le introduceți într-un set de celule.

Mai jos este un exemplu de listă verticală Excel:

În exemplul de mai sus, am folosit elementele din A2: A6 pentru a crea un meniu derulant în C3.

Citit: Iată un ghid detaliat despre cum să creați o listă drop-down Excel.

Uneori, totuși, poate doriți să utilizați mai multe liste drop-down în Excel, astfel încât articolele disponibile într-o a doua listă drop-down să depindă de selecția făcută în prima listă drop-down.

Acestea sunt numite liste drop-down dependente în Excel.

Mai jos este un exemplu de ceea ce vreau să spun prin o listă verticală dependentă în Excel:

Puteți vedea că opțiunile din Drop Down 2 depind de selecția făcută în Drop Down 1. Dacă selectez „Fructe” în Drop Down 1, mi se afișează numele fructelor, dar dacă selectez Legume în Drop Down 1, atunci eu Sunt afișate numele legumelor în Drop Down 2.

Aceasta se numește o listă verticală condiționată sau dependentă în Excel.

Crearea unei liste drop-down dependente în Excel

Iată pașii pentru a crea o listă verticală dependentă în Excel:

• Selectați celula în care doriți prima listă derulantă (principală).
• Accesați Date -> Validare date. Aceasta va deschide caseta de dialog pentru validarea datelor.
• În caseta de dialog pentru validarea datelor, în fila Setări, selectați Listă.
• În câmpul Sursă, specificați intervalul care conține elementele care urmează să fie afișate în prima listă derulantă.
• Faceți clic pe OK. Aceasta va crea Drop Down 1.
• Selectați întregul set de date (A1: B6 în acest exemplu).
• Accesați Formule -> Nume definite -> Creați din selecție (sau puteți utiliza comanda rapidă de la tastatură Control + Shift + F3).
• În caseta de dialog „Creați un nume din selecție”, bifați opțiunea Rând superior și debifați toate celelalte. Procedând astfel, se creează 2 intervale de nume („Fructe” și „Legume”). Gama cu fructe denumită se referă la toate fructele din listă și Gama cu legume denumită se referă la toate legumele din listă.
• Faceți clic pe OK.
• Selectați celula în care doriți lista drop-down Dependent / Conditional (E3 în acest exemplu).
• Accesați Date -> Validare date.
• În caseta de dialog Validare date, din fila de setări, asigurați-vă că Lista este selectată.
• În câmpul Sursă, introduceți formula = INDIRECT (D3). Aici, D3 este celula care conține meniul derulant principal.
• Faceți clic pe OK.

Acum, când faceți selecția în meniul drop down 1, opțiunile listate în lista drop down 2 se vor actualiza automat.

Descărcați fișierul de exemplu

Cum funcționează asta? - Lista derulantă condițională (în celula E3) se referă la = INDIRECT (D3). Aceasta înseamnă că, atunci când selectați „Fructe” în celula D3, lista derulantă din E3 se referă la intervalul numit „Fructe” (prin funcția INDIRECT) și, prin urmare, listează toate articolele din acea categorie.

Notă importantă: Dacă categoria principală este mai mult de un cuvânt (de exemplu, „Fructe sezoniere” în loc de „Fructe”), atunci trebuie să utilizați formula = INDIRECT (SUBSTITUT (D3, ”„, ”_”)), în loc de funcție simplă INDIRECT prezentată mai sus.

• Motivul pentru aceasta este că Excel nu permite spații în intervale denumite. Deci, atunci când creați un interval numit folosind mai mult de un cuvânt, Excel introduce automat o subliniere între cuvinte. De exemplu, atunci când creați un interval numit cu „Fructe sezoniere”, acesta va fi numit Sezon_Fructe în backend. Utilizarea funcției SUBSTITUTE în cadrul funcției INDIRECT asigură spațiile sunt convertite în subliniere.

Resetați / Ștergeți automat conținutul listei derulante dependente

Când ați făcut selecția și apoi modificați meniul derulant părinte, lista verticală dependentă nu s-ar schimba și, prin urmare, ar fi o intrare greșită.

De exemplu, dacă selectați „Fructe” ca categorie și apoi selectați Apple ca element și apoi reveniți și schimbați categoria în „Legume”, meniul derulant dependent va continua să afișeze Apple ca element.

Puteți utiliza VBA pentru a vă asigura că conținutul listei drop-down dependente se resetează ori de câte ori se modifică lista drop-down principală.

Iată codul VBA pentru a șterge conținutul unei liste derulante dependente:

Private Sub Worksheet_Change (ByVal Target As Range) On Error Reîncepeți în continuare dacă Target.Column = 4 Apoi If Target.Validation.Type = 3 Apoi Application.EnableEvents = False Target.Offset (0, 1). ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

Creditul pentru acest cod revine acestui tutorial de Debra privind ștergerea listelor drop-down dependente în Excel atunci când selecția este modificată.

Iată cum puteți face acest cod să funcționeze:

• Copiați codul VBA.
• În registrul de lucru Excel în care aveți lista verticală dependentă, accesați fila Dezvoltator și, în grupul „Cod”, faceți clic pe Visual Basic (puteți utiliza și comanda rapidă de la tastatură - ALT + F11).
• În fereastra Editorului VB, în stânga în exploratorul de proiecte, veți vedea toate numele foilor de lucru. Faceți dublu clic pe cel care are lista derulantă.
• Lipiți codul în fereastra de cod din dreapta.
• Închideți Editorul VB.

Acum, ori de câte ori schimbați lista derulantă principală, codul VBA va fi declanșat și va șterge conținutul listei derulante dependente (așa cum se arată mai jos).

Dacă nu sunteți un fan al VBA, puteți utiliza, de asemenea, un simplu truc de formatare condiționată care va evidenția celula ori de câte ori există o nepotrivire. Acest lucru vă poate ajuta să vedeți și să corectați vizual nepotrivirea (așa cum se arată mai jos).

Iată pașii t0 evidențiază nepotrivirile din listele derulante dependente:

• Selectați celula care are listele derulante dependente.
• Accesați Pagina principală -> Formatare condiționată -> Regulă nouă.
• În caseta de dialog Nouă regulă de formatare, selectați „Utilizați o formulă pentru a determina ce celule să formatați”.
• În câmpul formulă, introduceți următoarea formulă: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6 ,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Setați formatul.
• Faceți clic pe OK.

Formula folosește funcția VLOOKUP pentru a verifica dacă elementul din lista verticală dependentă este sau nu din categoria principală. Dacă nu este, formula returnează o eroare. Aceasta este utilizată de funcția ISERROR pentru a returna TRUE, care spune formatarea condiționată pentru a evidenția celula.

Este posibil să vă placă și următoarele tutoriale Excel:

• Extrageți datele pe baza unei selecții din lista derulantă.
• Crearea unei liste drop-down cu sugestii de căutare.
• Selectați mai multe articole dintr-o listă derulantă.
• Creați mai multe liste drop-down fără repetare.
• Economisiți timp cu formularele de introducere a datelor în Excel.