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 .

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 )

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:

• O listă derulantă într-o celulă
• Un indiciu la completarea datelor într-o celulă