Unul din primele articole scrise pe InvatamExcel.ro a fost despre crearea listelor dependente folosind funcția Data Validation. Listele dependente conțin cel puțin două liste dropdown conectate între ele. Cu ajutorul primei liste condiționăm opțiunile care vor fi afișate în cea de-a două.
Deși este ușor să creezi o listă simplă folosind funcția Data Validation, realizarea listelor dependente multi-nivel a fost întotdeauna dificilă.
Vestea bună este că acest proces a devenit mult mai ușor odată cu introducerea funcțiilor Dynamic Arrays.
Disponibilitate
Modalitatea de a crea liste dependente prezentată în acest articol funcționează numai în Excel 365 și Excel 2021.
Pași pentru a crea liste dependente folosind formule de tip Dynamic Arrays
Exemplul de mai jos demonstrează abordarea generală pentru crearea unei liste dependente în Excel prin utilizarea noilor formule Dynamic Arrays.
Să presupunem că avem un tabel cu departamentele, funcțiile și angajații dintr-o companie.
Scopul este de a realiza într-un alt tabel câte un dropdown list pentru coloanele Departament și Funcție iar Angajatul să fie completat automat în funcție de cele două selecții. De asemenea, lista afișată pentru funcții trebuie să fie dependentă de selecția departamentului.
Pentru a crea listele dependente este necesar să realizăm următorii pași:
=UNIQUE(Tabel_sursa[Departament])Când apăsăm Enter, Excel va afișa primul nume găsit în prima celulă, revărsând celelalte nume în celulele de mai jos. Ca rezultat, vei obține toate valorile unice din coloana Departament.
Următorul pas constă în folosirea funcției Data validation pentru configurarea listei dropdown pentru coloana principală de selecție:
- Selectăm celula care dorim să conțină lista dropdown pentru Departament, în exemplul nostru celula H8.
- În bara de meniu selectăm tab-ul Data și apasăm butonul Data validation.
- În fereastra care se deschide pentru câmpul Allow alegem List.
- Iar pentru câmpul Source introducem referința către intervalul de valori rezultat la pasul anterior. Este indicat să completăm sau selectăm prima celulă a formulei Unique pentru coloana Departament și notăm semnul # la final: =$L$8# .
- Pentru salvarea listei apăsăm Ok.
Acum avem nevoie să filtrăm valorile din coloana Funcție pe baza selecției realizate în primul dropdown.
Pentru acest lucru folosim Filter, o altă formulă Dynamic Arrays.
Pentru coloana Funcție vom folosi următoarea formulă:
=FILTER(Tabel_sursa[Functie],Tabel_sursa[Departament]=H8)
La primul argument, array, selectăm coloana cu funcțiile din tabelul sursă.
Iar criteriul de filtrare, argumentul include, selectăm coloana Departament din tabelul sursă care trebuie să fie egală cu celula în care se selectează departamentul dorit din dropdown.
Ne putem asigura că formula funcționează corect selectând o anumită valoare din dropdown-ul principal și verificăm că rezultatele afișate de filter sunt corecte:
Notă: Nu se poate utiliza funcția FILTER direct în câmpul Source din Data Validation List deoarece acest câmp necesită un interval de celule, iar valorile returnate de Filter sunt sub forma de array.
În acest moment am finalizat configurarea celor două liste dropdown și opțiunile listei secundare depind de selecția realizată în cea primară.
Pentru acest exemplu datele sursă pentru listele dependente sunt salvate într-un Table. Astfel că pe măsură ce se adaugă noi elemente sau se șterg din cele existente, informațiile din cele două liste dropdown se vor actualiza automat.
La final pentru câmpul Angajat vom aplica formula Xlookup cu mai multe criterii de căutare.
Mai jos este formula care face acest lucru:
=XLOOKUP(H8&I8,Tabel_sursa[Departament]&Tabel_sursa[Functie],Tabel_sursa[Angajat])
Te invit să urmărești aplicarea tuturor pașilor de mai sus în următorul video:
Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos:
Concluzie
Capacitatea de a crea o lista dropdown care să permită utilizatorilor să selecteze o valoare dintr-o listă predefinită este una din cele mai utile caracteristici ale funcției Data Validation.
Acestea sunt foarte utile pentru a ghida sau a restricționa un utilizator în ceea ce privește datele pe care acesta le poate completa în anumite celule.
În articolul de azi, am văzut cât de ușor se pot crea listele dropdown dependente folosind formule de tip Dynamic Arrays.
Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de folosirea funcției Data Validation List.
