TOP
Liste derulante legate
Descriere
În Excel puteți rapid și ușor creați o listă derulantă , dar ați încercat vreodată să creați o listă derulantă dependentă? Există mai multe moduri de a crea o astfel de listă, așa că haideți să le aruncăm o privire.
Metoda 1. Funcția INDIRECT
Această tehnică se bazează pe aplicarea funcției INDIRECT, care poate face un lucru simplu - convertirea conținutului oricărei celule specificate într-o adresă de interval pe care Excel o înțelege. Adică, dacă celula conține textul „ А1 ", apoi funcția va da o referință la celulă ca rezultat А1 . Dacă celula conține cuvântul „ Auto ", apoi funcția va scoate o referință la intervalul numit cu numele Auto etc.
Luați, de exemplu, această listă de modele de mașini Toyota, Ford și Nissan :
Selectați întreaga listă de modele Toyota (din celulă А2 și până la sfârșitul listei) și dați un nume acestui interval Toyota în meniu Inserare - Nume - Atribuire (Insert - Name - Define) . Apoi vom repeta același lucru cu listele Ford și Nissan , specificând în mod corespunzător numele intervalelor Ford și Nissan .
Când atribuiți nume, rețineți că numele intervalelor din Excel nu trebuie să conțină spații, semne de punctuație și trebuie să înceapă cu o literă. Prin urmare, dacă a existat un decalaj la una dintre mărcile de mașini (de exemplu Ssang Yong), atunci ar trebui înlocuit în celulă și în numele intervalului cu un caracter de subliniere (de ex Ssang_Yong).
Acum să creăm prima listă derulantă pentru alegerea unei mărci de mașini. Selectați o celulă goală și deschideți meniul Date - Verificare (Data - Validation) , apoi din lista derulantă Tip de date selecteaza o optiune Listă iar în câmp Sursă - evidențiați celulele cu nume de marcă (celule galbene în exemplul nostru). După ce faceți clic pe ОК prima listă derulantă este gata:
Acum să creăm o a doua listă derulantă, care va afișa modelele mărcii selectate în prima listă. La fel ca în cazul precedent, selectați o celulă goală și deschideți meniul Date - Verificare - mai departe Listă . În câmp Sursă va trebui să introduceți următoarea formulă:
=INDIRECT(F3)
Unde:
- F3 - adresa celulei cu prima listă derulantă - înlocuiți-o cu a dvs.
Toate. După ce faceți clic pe Bine conținutul celei de-a doua liste va fi selectat după numele intervalului selectat în prima listă.
Dezavantajele acestei metode:
- Intervalele dinamice specificate de formulele de tip OFFSET nu pot acționa ca intervale secundare (dependente). Le puteți folosi pentru lista primară (independentă), dar lista secundară trebuie definită rigid, fără formule.
- Numele intervalului secundar trebuie să se potrivească cu elementele principale din lista verticală. Adică, dacă conține text cu lacune, acestea vor trebui înlocuite cu subliniere etc.
- Trebuie să creați manual multe intervale denumite.
Metoda 2. Lista de potriviri (OFFSET și MATCH)
Această metodă necesită o listă sortată de potriviri marca-model de următorul tip:
Pentru a crea o listă derulantă principală de mărci, puteți utiliza metoda obișnuită descrisă mai sus, adică:
- Dați numele intervalului D1:D3 (de ex Mărci )
- Selectați pe filă Date (Data) echipă Verificarea datelor (Data validation)
- Selectați o opțiune de verificare din lista verticală Listă (List) și specificați ca calitate Surse (Source) = Mărci sau pur și simplu selectați celulele D1:D3 (dacă sunt pe aceeași foaie cu lista).
Dar pentru o listă dependentă de modele, va trebui să creați o gamă numită cu funcția OFFSET, care se va referi în mod dinamic numai la celulele modelelor unei anumite mărci. Pentru aceasta:
- Apăsați Ctrl + F3 sau utilizați butonul Manager de nume (Name manager) pe filă Formule (Formulas) . În versiunile dinainte de 2003, aceasta era o comandă de meniu Inserare - Nume - Atribuire (Insert - Name - Define)
- Creați o nouă gamă de nume cu orice nume (de ex Modele ) și în câmp Legătură (Reference) în partea de jos a ferestrei, introduceți manual următoarea formulă:
=OFFSET( 1 USD ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )
Referințele trebuie să fie absolute (cu semne $ ). După apăsarea Enter, numele foilor vor fi adăugate automat la formulă.
Funcția OFFSET este capabilă să emită o referință la un interval de dimensiunea dorită, deplasată față de celula inițială cu numărul dat de rânduri și coloane. Într-o versiune mai ușor de înțeles, sintaxa acestei funcții este următoarea:
=OFFSET( start_cell ; shift_down ; shift_dreapta ; range_size_in_rows ; range_size_in_columns )
Asa de:
- start_cell - luăm prima celulă din lista noastră, adică A1 ;
- shift_down - numărăm funcția MATCH, care, pur și simplu, scoate la ieșire numărul de serie al celulei cu marca selectată (G7) în intervalul dat (coloane ȘI );
- shift_dreapta =1 , așa că vrem să ne referim la modelele din coloana alăturată ( ÎN );
- range_size_in_rows - calculăm folosind funcția COUNTIF, care poate număra numărul de apariții din lista (coloana A) a valorilor de care avem nevoie - mărci de mașini ( G7 );
- range_size_in_columns =1 , deci avem nevoie de o coloană cu modele.
Rezultatul ar trebui să fie cam așa:
Rămâne să adăugați în celulă o listă derulantă bazată pe formula creată G8 . Pentru aceasta:
- Selectați o celulă G8
- Selectați pe filă Date (Data) echipă Verificarea datelor (Data validation) sau în meniu Date - Verificare (Data - Validation)
- Selectați opțiunea de verificare din lista verticală Listă (List) si intra ca Surse (Source) semnul este egal cu numele gamei noastre, i.e = Modele .
Articole pe tema: