Excela

Cum se fac liste drop-down dependente în Excel

How Make Dependent Dropdown Lists Excel

Vezi si: Prezentare generală a validării datelor | Formule de validare a datelor

Una dintre cele mai utile caracteristici ale validării datelor este posibilitatea de a crea o listă verticală care permite utilizatorilor să selecteze o valoare dintr-o listă predefinită. Listele derulante permit utilizatorilor să introducă cu ușurință doar datele care îndeplinesc cerințele dvs.





Exemplu de listă derulantă creată cu validarea datelor

Listele derulante sunt ușor de creat și de utilizat. Dar, odată ce începeți să utilizați meniurile derulante în foile dvs. de calcul, veți întâmpina inevitabil o provocare: cum puteți face ca valorile dintr-o listă derulantă să depindă de valorile din alta? Cu alte cuvinte, cum puteți face o listă derulantă dinamică?





cum se introduce un link în Excel

Aici sunt cateva exemple:

  • o listă de orașe care depinde de țara selectată
  • o listă de arome care depinde de tipul de înghețată
  • o listă de modele care depinde de producător
  • o listă de alimente care depinde de categorie

Acest tip de liste sunt numite derulante dependente , deoarece lista depinde de o altă valoare. Acestea sunt create cu validarea datelor, utilizând o formulă personalizată bazată pe Funcția INDIRECTĂ și intervale denumite . Acest lucru poate părea complicat, dar este de fapt foarte simplu și un exemplu excelent al modului în care INDIRECT poate fi utilizat.



Citiți mai departe pentru a vedea cum să creați liste derulante dependente în Excel.

Exemplu drop-down dependent

În exemplul prezentat mai jos, coloana B oferă un meniu derulant pentru categoria de alimente, iar coloana C oferă opțiuni în categoria aleasă. Dacă utilizatorul selectează „Fructe”, vede o listă de fructe, dacă selectează „Nuci”, vede o listă de nuci, iar dacă selectează „Legume”, vede o listă de legume.

listă derulantă regulată cu interval orizontal

listă verticală dependentă cu formulă personalizată și INDIRECT

Validarea datelor din coloana B utilizează această formulă personalizată:

 
=category

Și validarea datelor din coloana C folosește această formulă simplă:

 
= INDIRECT (B5)

Unde foaia de lucru conține următoarele intervale denumite:

categorie = E4: G4
vegetal = F5: F10
piuliță = G5: G9
fruct = E5: E11

Cum funcționează acest lucru

Cheia acestei tehnici este intervale denumite + funcția INDIRECTĂ. INDIRECT acceptă valorile textului și încearcă să le evalueze ca referințe de celulă. De exemplu, INDIRECT va lua textul „A1” și îl va transforma într-o referință reală:

 
= INDIRECT ('A1') =A1

În mod similar, INDIRECT va converti textul „A1: A10” în intervalul A1: A10 din cadrul unei alte funcții:

 
= SUM ( INDIRECT ('A1:A10') = SUM (A1:A10)

La prima vedere, s-ar putea să vi se pară enervantă sau chiar lipsită de sens. De ce să complicăm o formulă simplă drăguță cu INDIRECT?

Fii sigur, există o metodă pentru nebunie :)

Frumusețea INDIRECT este că vă permite să utilizați text exact ca o referință de celulă . Aceasta oferă două avantaje cheie:

  1. Puteți asambla o referință text într-o formulă, care este la îndemână anumite tipuri de referințe dinamice .
  2. Puteți prelua valorile textului pe o foaie de lucru și le puteți folosi ca referință de celulă într-o formulă.

În exemplul de pe această pagină, combinăm ultima idee cu intervale denumite pentru a crea liste derulante dependente. INDIRECT mapează textul la un interval numit, care este apoi rezolvat la o referință validă. Deci, în acest exemplu, preluăm valorile textului din coloana B și folosim INDIRECT pentru a le converti în referințe de celule prin potrivirea unor intervale denumite existente, astfel:

nu semnează egal în excel
 
= INDIRECT (B5) = INDIRECT ('nut') =G5:G9

B5 se rezolvă la textul „piuliță” care se rezolvă la intervalul G5: G9.

Cum să configurați liste derulante dependente

Această secțiune descrie modul de configurare a listelor derulante dependente afișate în exemplu.

1. Creați listele de care aveți nevoie. În exemplu, creați o listă de fructe, nuci și legume într-o foaie de lucru.

creați listele necesare pentru validarea datelor

2. Creați intervale denumite pentru fiecare listă: categorie = E4: G4, legume = F5: F10, nuci = G5: G9 și fructe = E5: E11.

creați intervalele numite necesare pentru validarea datelor

Important: valorile din E4, F4 și G4 trebuie să se potrivească cu ultimele trei game numite de mai sus (legume, nuci și fructe). Cu alte cuvinte, trebuie să vă asigurați că intervalele numite pe care le-ați creat sunt denumite pentru a se potrivi cu valorile care vor apărea în lista verticală Categorie.

Notă: dacă intervalele denumite sunt noi pentru dvs., vezi această pagină .

3. Creați și testați o regulă de validare a datelor pentru a furniza o listă derulantă pentru Categorie folosind următoarea formulă personalizată:

 
=category

regula de validare a datelor pentru categorie

Notă: pentru a fi clar, intervalul denumit „categorie” este utilizat doar pentru lizibilitate și comoditate - utilizarea unui interval denumit aici nu este necesară. De asemenea, rețineți că validarea datelor cu o listă funcționează bine atât cu intervalele orizontale, cât și cu cele verticale - ambele vor fi prezentate ca un meniu vertical vertical.

4. Creați o regulă de validare a datelor pentru lista verticală dependentă cu o formulă personalizată bazată pe funcția INDIRECT:

 
= INDIRECT (B5)

regula de validare a datelor pentru meniul derulant

În această formulă, INDIRECT evaluează pur și simplu valorile din coloana B ca referințe, care le leagă de intervalele denumite definite anterior.

schimbați numărul negativ în pozitiv în Excel

5. Testați listele derulante pentru a vă asigura că răspund dinamic la valorile din coloana B.

testarea validării datelor pentru lista alimentelor

Notă: abordarea pe care o adoptăm aici nu ține cont de majuscule și minuscule. Intervalul numit se numește „piuliță”, iar valoarea din B6 este „Piuliță”, dar funcția INDIRECTĂ se rezolvă corect la intervalul numit, chiar dacă diferența dintre majuscule și minuscule.

Tratarea spațiilor

Intervalele denumite nu permit spații, așa că convenția obișnuită este să folosiți în schimb caractere de subliniere. De exemplu, dacă doriți să creați un interval numit pentru inghetata , ai folosi inghetata. Acest lucru funcționează bine, dar listele drop-down dependente se vor sparge dacă încearcă să mapeze „înghețată” cu „ice_cream”. Pentru a remedia această problemă, puteți utiliza o formulă personalizată mai robustă pentru validarea datelor:

 
 = INDIRECT ( SUBSTITUTE (A1,' ','_'))

Această formulă folosește încă INDIRECT pentru a lega valoarea textului din A1 la un interval denumit, dar înainte de a se executa INDIRECT, funcția SUBSTITUTE înlocuiește toate spațiile cu punctele de subliniere. Dacă textul nu conține spații, SUBSTITUTE nu are efect.

Dosar de exersare

Fișierul de exemplu este atașat mai jos - verificați-l pentru a vedea cum funcționează. Dacă doriți să învățați singură tehnica, vă recomand să creați un fișier propriu. Cel mai bun mod de a învăța este prin a face.

Autor Dave Bruns Atașamente Fişier dropdown dependent.xlsx


^