Crearea listelor dependente - Dependent Data Validation List

In aceasta prima postare despre o functie excel, am ales sa prezint listele dependente create prin Data Validation List. Am descoperit cum se face acest lucru in urma cu 3 ani, iar cand mi s-a cerut un fisier care sa contina ceva de acest gen am zis ca este imposibil. Va puteti imagina surpriza pe care am avut-o cand am descoperit cum se face si bucuria avuta cand am realizat un fisier cu aceasta functie. Atunci mi-am dat seama ca nu folosesc excel-ul nici pe jumatate din cat as putea iar cu fiecare fisier nou creat am incercat sa descopar noi lucruri. In acest moment descoper macro-urile dar despre ele voi scrie alta data.

Exista mai multe metode prin care se pot crea listele dependente insa cea pe care o voi prezenta in continuare mi s-a parut cea mai simpla de folosit. Pentru a intelege mai bine metoda de creare a listelor am ales un exemplu in care trebuie sa alegem un departament si in functie de acest departament sa selectam un angajat din acesta.

Configurarea fisierului

Pentru lista dependenta avem nevoie de doua sheet-uri: unul in care se aleg datele din Data Validation List si unul ajutator din care vin informatiile in liste.

• In acest exemplu am denumit acest sheet ajutator Liste_ajutatoare si am inceput sa scriu departamentele. Dupa ce am terminat de scris lista departamentelor am sortat-o in ordine alfabetica. Aveti grija sa selectati doar celulele pe care doriti sa le sortati ca in exemplul de mai jos.

• Dupa ce am definit lista departamentelor trecem la crearea tabelului cu angajati. Notam pe prima coloana departamentul din care face parte angajatul si pe a doua numele acestuia. Dupa ce am terminat de completat tabelul, il sortam in ordine alfabetica dupa departament (primul nivel) si angajat (al doilea nivel). 

Pentru listele dependente trebuie realizata sortarea doar dupa department dar eu realizez si sortarea pe al doilea nivel dupa angajat pentru a fi mai usor utilizatorului sa selecteze persoana din lista. Ca in cazul de mai sus pentru sortare trebuie sa selectam doar celulele din tabelul de angajati. Daca dupa ce am creat tabelul si l-am sortat mai adaugam alte date nu uitati sa il sortati din nou.

Crearea Dynamic Named Ranges

In continuare definim cu Define Name listele de care avem nevoie in pentru a crea Data Validation List. Deoarece in cadrul acestor tabele pot aparea intrari noi folosim metoda de definire pentru listele dinamice.
 

Pentru lista departamentelor urmam urmatorii pasi:

1. Alegem in bara de meniu Formulas (Office 2007 si 2010) si Define Name. 
2. Denumim lista (campul Name) in exemplul meu am denumit-o Lista_departament
3. In casuta Refers to folosim o formula bazate pe functia Offset care defineste dimensiunea unei liste, bazandu-se pe numarul de randuri dintr-o anumita coloana: =OFFSET(Liste_ajutatoare!$D$2,0,0,COUNTA(Liste_ajutatoare!$D:$D),1)                                    Argumentele folosite in functia offset sunt:
1. Reference cell:  Liste_ajutatoare!$D$2 reprezinta celula de unde incepe lista (fara titlu);
2. Rows to offset: randul de unde sa inceapa lista. In cazul nostru este 0 pentru ca vrem toata lista;
3. Columns to offset: coloana de unde sa inceapa lista. In cazul nostru este 0 pentru ca vrem lista de pe aceeasi coloana;
4. Height: numarul de randuri pe care sa il aibe lista. In cazul nostru folosim functia counta pentru a afla cate randuri scrise are lista noastra.
5. Width: numarul de coloane pe care sa il aibe lista. In cazul nostru vrem o lista cu o singura coloana.
4.  Apasa OK.

Pentru a creea listele dependenta mai aven nevoie sa definim si urmatoarele celule din tabelul de angajati:

1. DepartamentStart este prima celula a coloanei departament in tabelul de angajati din sheet-ul Liste_ajutatoare (cu titlu cu tot);
2. DepartamentColoana este coloana departament din tabelul de angajati.

Crearea listelor dependente prin Data Validation Dropdown List

In sheet-ul in care dorim sa avem aceste liste dependente notam titlurile celor doua coloane. In cazul nostru sheet-ul se numeste DependentList iar titlurile sunt scrie in celulele A3 Departament si B3 Angajat. 

• Urmatorul pas este sa cream Data Validation Dropdown list in coloana Departament. Celulele A4:A17 au Data Validation Dropdown List avand ca sursa Lista_departament definita mai sus. Atunci cand o celula din acest interval este selectata, o listă derulanta a departamentelor este disponibila.

• Acum incepem sa creem lista dependenta prin Data Validation Dropdown List in coloana Angajat dupa urmatorii pasi:

1. In celula A4 alegem un departament din lista disponibila. (Daca aceasta celula este goala cand scriem formula pentru lista de pe coloana Angajat, vom primi un mesaj de eroare);
2. Selecteaza celulele unde se doreste crearea listei dependente. In cazul nostru regiunea B4:B17;
3. Alege din bara de meniu Data, apoi Data Validation;
4. Din lista din casuta Allow alege List ;
5. In casuta Source, scrie urmatoarea formula: =OFFSET(DepartmanetStart,MATCH(A4,DepartamentColoana,0)-1,1, COUNTIF(DepartamentColoana,A4),1)

1. Reference: In cazul nostru, celula de referinta este DepartamentStart, care reprezinta celula A1 din sheet-ul Liste_ajutatoare;
2. Rows reprezinta cate randuri in jos de la celula de referinta trebuie sa aibe lista dorita. Astfel folosim functia match care ne da numarul randului in care gasim prima ora departamentul anterior in coloana DepartamentColoana. Scadem 1 din acest numar pentru ca celula de referinta este pe randul 1 care este titlul tabelului;
3. Columns reprezinta numarul de coloane de unde incepe lista. Astfel scriem 1 pentru ca dorim ca lista sa inceapa la 1 coloana de la celula de referinta.
4. Height pentru a afla cate randuri contine lista folosim functia countif care numara in DepartamentColoana de cate ori se gaseste departamentul ales in celula A4 din sheet-ul DependtList;
5. Width reprezinta numarul de coloane pe care sa il contina lista. In cazul nostru dorim o lista cu 1 coloana, cea de angajati.
6.  Apasa OK.

 Observatii:  Atunci cand scrieti formula pentru lista dependenta sa aveti grija cand alegeti, in cadrul functiei match si countfi, celula unde se selecteaza departamentul sa nu aibe referinta ($A$4). Daca realizati formula cu aceasta celula avand referinta, in toate celulele cu liste dependete va aparea lista in functie de departamentul scris doar in celula A4.

Pentru a intelege mai usor cum se creaza o lista dependenta cu Data Validation List puteti downloada fisierul cu exemplul de la urmatoarea link: DepValList.xlsx.