Foile de calcul Excel includ adesea dropdown-uri pentru a simplifica și / sau standardiza introducerea datelor. Aceste meniuri sunt create utilizând caracteristica de validare a datelor pentru a specifica o listă de intrări admisibile.
Pentru a configura o listă de dropdown simplu, selectați celula unde vor fi introduse datele, apoi faceți clic pe Validare date (în fila Date ), selectați Validare date, alegeți Listă (sub Allow :) și apoi introduceți elementele listate (separate prin virgule ) în câmpul Sursă : (a se vedea Figura 1).
În acest tip de dropdown de bază, lista intrărilor admisibile este specificată în cadrul validării datelor; prin urmare, pentru a face modificări în listă, utilizatorul trebuie să deschidă și să editeze validarea datelor. Acest lucru poate fi dificil, totuși, pentru utilizatorii neexperimentați sau în cazurile în care lista de opțiuni este lungă.
O altă opțiune este plasarea listei într-un interval numit în foaia de calcul și apoi specificarea numelui domeniului (precedat cu un semn egal) în câmpul Sursă : Validarea datelor (după cum se arată în Figura 2).
Această a doua metodă facilitează editarea opțiunilor din listă, dar adăugarea sau eliminarea elementelor pot fi problematice. Deoarece numita gamă (FruitChoices, în exemplul nostru) se referă la o gamă fixă de celule ($ H $ 3: $ H $ 10 așa cum se arată), dacă se adaugă mai multe opțiuni la celulele H11 sau mai jos, acestea nu vor apărea în meniul drop down (deoarece aceste celule nu fac parte din gama FruitChoices).
De asemenea, dacă, de exemplu, intrările de Pere și Căpșuni sunt șterse, acestea nu vor mai apărea în meniul dropdown, dar în meniul dropdown vor fi incluse două opțiuni "goale", deoarece meniul derulant încă include întregul interval FruitChoices, inclusiv celulele goale H9 și H10.
Din aceste motive, atunci când utilizați o gamă normală numită ca sursă de listă pentru o meniuri, intervalul numit în sine trebuie să fie editat pentru a include mai multe sau mai puține celule dacă sunt adăugate sau șterse intrări din listă.
O soluție la această problemă este utilizarea unui nume de domeniu dinamic ca sursă pentru opțiunile dropdown. Un nume de domeniu dinamic este unul care se extinde (sau contractează) automat pentru a se potrivi exact cu dimensiunea unui bloc de date, deoarece intrările sunt adăugate sau eliminate. Pentru a face acest lucru, utilizați o formulă, mai degrabă decât o gamă fixă de adrese de celule, pentru a defini intervalul numit.
Cum se configurează o gamă dinamică în Excel
Un nume de domeniu normal (static) se referă la un interval de celule specificat ($ H $ 3: $ H $ 10 în exemplul nostru, vezi mai jos):
Dar un interval dinamic este definit folosind o formulă (a se vedea mai jos, luată dintr-o foaie de calcul separată care utilizează nume din raza dinamică):
Înainte de a începe, asigurați-vă că descărcați fișierul exemplu Excel (macrosurile de sortare au fost dezactivate).
Să examinăm această formulă în detaliu. Alegerile pentru fructe se află într-un bloc de celule direct sub o rubrică ( FRUCTE ). În această poziție se atribuie și un nume: Fructe :
Întreaga formulă utilizată pentru a defini intervalul dinamic pentru opțiunile Fructe este:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MECI (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading se referă la poziția care este un rând deasupra primei intrări din listă. Numărul 20 (utilizat de două ori în formula) este dimensiunea maximă (numărul de rânduri) pentru listă (aceasta poate fi ajustată după cum doriți).
Rețineți că în acest exemplu există numai 8 intrări în listă, dar mai jos sunt și celule goale sub acestea, unde pot fi adăugate intrări suplimentare. Numărul 20 se referă la întregul bloc unde pot fi introduse intrări, și nu la numărul efectiv de intrări.
Acum, haideți să descompunem formula în bucăți (codul de culoare pentru fiecare piesă), pentru a înțelege cum funcționează:
= OFFSET (Fructe, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1 )
Cea mai "interioară" piesă este OFFSET (FruitsHeading, 1, 0, 20, 1) . Aceasta se referă la blocul de 20 de celule (sub celula FruitsHeading) în care pot fi introduse opțiuni. Această funcție OFFSET spune în principiu: Începeți din celula FruitsHeading, mergeți în jos cu 1 rând și peste 0 coloane, apoi selectați o zonă cu lungimea de 20 de rânduri și cu o lățime de 1 coloană. Așa că ne dă blocul de 20 de rânduri în care sunt introduse opțiunile Fructe.
Următoarea piesă a formulei este funcția ISBLANK :
= OFFSET (Fructe, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (de mai sus), 0, 0), 0) -1, 20)
Aici, funcția OFFSET (explicată mai sus) a fost înlocuită cu "cele de mai sus" (pentru a face lucrurile mai ușor de citit). Dar funcția ISBLANK funcționează pe domeniul de 20 de rânduri de celule pe care definește funcția OFFSET.
ISBLANK creează apoi un set de 20 de valori TRUE și FALSE, indicând dacă fiecare dintre celulele individuale din intervalul de 20 de linii la care se face referire prin funcția OFFSET este necompletat (gol) sau nu. În acest exemplu, primele 8 valori din set vor fi FALSE, deoarece primele 8 celule nu sunt goale și ultimele 12 valori vor fi TRUE.
Următoarea piesă a formulei este funcția INDEX:
= OFFSET (Fructe, 1, 0, IFERROR (MATCH (TRUE, INDEX (cele de mai sus, 0, 0), 0) -1, 20)
Din nou, "cele de mai sus" se referă la funcțiile ISBLANK și OFFSET descrise mai sus. Funcția INDEX returnează o matrice care conține cele 20 de valori TRUE / FALSE create de funcția ISBLANK.
INDEX este folosit în mod normal pentru a alege o anumită valoare (sau o gamă de valori) dintr-un bloc de date, specificând un anumit rând și o coloană (în cadrul acelui bloc). Dar setarea intrărilor de la rând și coloană la zero (așa cum se face aici) determină ca INDEX să returneze o matrice care conține întregul bloc de date.
Următoarea piesă a formulei este funcția MATCH:
= OFFSET (Fructe, 10, IFERROR ( MATCH (TRUE, mai sus, 0) -1, 20), 1)
Funcția MATCH returnează poziția primei valori TRUE, în cadrul matricei returnate de funcția INDEX. Deoarece primele 8 intrări din listă nu sunt goale, primele 8 valori din matrice vor fi FALSE, iar a noua valoare va fi TRUE (din moment ce al 9-lea rând din interval este gol).
Deci, funcția MATCH va reveni la valoarea de 9 . În acest caz, cu toate acestea, dorim într-adevăr să știm câte intrări sunt în listă, astfel încât formula să scadă 1 din valoarea MATCH (care dă poziția ultimei intrări). În cele din urmă, MATCH (TRUE, mai sus, 0) -1 returnează valoarea de 8 .
Următoarea piesă a formulei este funcția IFERROR:
= OFFSET (Fructe, 1, IFERROR (cele de mai sus, 20), 1)
Funcția IFERROR returnează o valoare alternativă, dacă prima valoare specificată duce la o eroare. Această funcție este inclusă deoarece, dacă întregul bloc de celule (toate cele 20 de rânduri) este umplut cu intrări, funcția MATCH va întoarce o eroare.
Acest lucru se datorează faptului că îi spunem funcției MATCH să caute prima valoare TRUE (în matricea valorilor din funcția ISBLANK), dar dacă NU este una dintre cele două celule, atunci întreaga matrice va fi umplută cu valori FALSE. Dacă MATCH nu poate găsi valoarea țintă (TRUE) în matricea pe care o caută, aceasta returnează o eroare.
Deci, dacă întreaga listă este plină (și, prin urmare, MATCH returnează o eroare), funcția IFERROR va reveni la valoarea de 20 (știind că trebuie să existe 20 de intrări în listă).
În cele din urmă, OFFSET (FruitsHeading, 1.0, cele de mai sus, 1) returnează intervalul pe care îl căutăm: Începeți din celula FruitsHeading, coborâți 1 rând și peste 0 coloane, apoi selectați o zonă care are însă multe rânduri lungi există intrări în listă (și o lățime de coloană). Astfel, întreaga formulă împreună va returna intervalul care conține numai intrările reale (până la prima celulă goală).
Folosind această formulă pentru a defini intervalul care este sursa pentru dropdown înseamnă că puteți edita liber lista (adăugarea sau eliminarea intrărilor, atâta timp cât intrările rămase încep de la celula de sus și sunt contiguoase), iar meniul derulant va reflecta întotdeauna actuala (a se vedea figura 6).
Fișierul exemplu (liste dinamice) care a fost utilizat aici este inclus și poate fi descărcat de pe acest site web. Macrourile nu funcționează, totuși, deoarece WordPress nu-i plac cărțile Excel cu macro-uri în ele.
Ca alternativă la specificarea numărului de rânduri din blocul de listă, blocul de listă poate fi atribuit propriului nume de domeniu, care poate fi apoi utilizat într-o formulă modificată. În fișierul exemplu, oa doua listă (Nume) utilizează această metodă. Aici, întregul bloc de listă (sub denumirea "NAMES", 40 de rânduri din fișierul exemplu) este atribuită numele domeniului NameBlock . Formula alternativă pentru definirea listei de nume este apoi:
= OFFSET (Nume, 1, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NumeBloc ), 0, 0), 0) -1, ROWS (NamesBlock )
unde NamesBlock înlocuiește OFFSET (FruitsHeading, 1, 0, 20, 1) și ROWS (NamesBlock) înlocuiește 20 (numărul de rânduri) din formula anterioară.
Deci, pentru listele derulante care pot fi ușor editate (inclusiv de către alți utilizatori care ar putea fi neexperimentați), încercați să utilizați nume din raza dinamică! Și rețineți că, deși acest articol a fost focalizat pe liste derulante, numele zonelor dinamice pot fi utilizate oriunde este nevoie pentru a face referire la un interval sau o listă care poate varia în funcție de dimensiune. Bucurați-vă!
