Foile de calcul Excel includ adesea meniuri de celule pentru a simplifica și / sau standardiza introducerea datelor. Aceste meniuri derulante sunt create folosind funcția de validare a datelor pentru a specifica o listă de intrări permise.
Pentru a configura o listă derulantă simplă, selectați celula în care vor fi introduse datele, apoi faceți clic pe Validare dateputernic>(în fila Date), selectați Validare date, alegeți Lista(sub Permite :), apoi introduceți elementele din listă (separate prin virgule) în secțiunea
În acest tip de meniu derulant de bază, lista de intrări permise este specificată în cadrul validării datelor în sine; prin urmare, pentru a face modificări în listă, utilizatorul trebuie să deschidă și să editeze validarea datelor. Totuși, acest lucru poate fi dificil pentru utilizatorii fără experiență sau în cazurile în care lista de alegeri este lungă.
O altă opțiune este să plasați lista într-un numit interval în foaia de calcul, apoi să specificați numele acelui interval (prefațat cu un semn egal) în câmpul Sursa: validarea datelor (așa cum se arată în figura 2).
Această a doua metodă facilitează editarea alegerilor din listă, dar adăugarea sau eliminarea articolelor poate fi problematică. Întrucât intervalul numit (FruitChoices, în exemplul nostru) se referă la un interval fix de celule ($ H $ 3: $ H $ 10 după cum se arată), dacă se adaugă mai multe opțiuni la celulele H11 sau mai jos, acestea nu vor apărea în lista verticală (întrucât acele celule nu fac parte din gama FruitChoices).
De asemenea, dacă, de exemplu, intrările Pears și Strawberries sunt șterse, acestea nu vor mai apărea în lista verticală, ci în schimb, meniul dropdown va include două Opțiunile „goale” din moment ce meniul derulant încă face referire la întregul interval FruitChoices, inclusiv celulele goale H9 și H10.
Din aceste motive, atunci când utilizați un interval numit normal ca sursă de listă pentru un dropdown, intervalul numit el însuși trebuie editat pentru a include mai multe sau mai puține celule, dacă intrările sunt adăugate sau șterse din listă.
O soluție la această problemă este utilizarea unui dinamic>numele intervalului ca sursă pentru alegerile derulante. Un nume de interval dinamic este unul care se extinde automat (sau se contractează) pentru a se potrivi exact cu dimensiunea unui bloc de date pe măsură ce intrările sunt adăugate sau eliminate. Pentru a face acest lucru, utilizați o formulă , în loc de o gamă fixă de adrese de celule, pentru a defini intervalul numit.
Cum să configurați o dinamică Interval în Excel
Un nume normal (static) se referă la un interval specificat de celule ($ H $ 3: $ H $ 10 în exemplul nostru, a se vedea mai jos):
Dar un interval dinamic este definit folosind o formulă (a se vedea mai jos, preluată dintr-o foaie de calcul separată care folosește denumiri dinamice):
4
Înainte de a începe, asigurați-vă că vă descărcați Fișier exemplu Excel (macro-urile de sortare au fost dezactivate).
Să examinăm detaliat această formulă. Opțiunile pentru fructe sunt într-un bloc de celule direct sub o rubrică (FRUCTE). Această rubrică i se atribuie și un nume: FruitsHeading
Întreaga formulă folosită pentru a defini intervalul dinamic pentru Alegerea fructelor este:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingse referă la titlul care este cu un rând deasupra primei intrări din listă. Numărul 20 (folosit de două ori în formulă) este dimensiunea maximă (numărul de rânduri) pentru listă (aceasta poate fi ajustată după dorință).
Rețineți că, în acest exemplu, există doar 8 intrări în listă, dar există și celule goale sub acestea, unde ar putea fi adăugate intrări suplimentare. Numărul 20 se referă la întregul bloc în care pot fi făcute înregistrări, nu la numărul real de intrări.
Acum, vom descompune formula în bucăți (care codează culoarea fiecărei piese), pentru a înțelege cum funcționează. :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
Piesa „cea mai interioară” este OFFSET (FruitsHeading, 1,0,20,1). Aceasta se referă la blocul de 20 de celule (sub celula FruitsHeading) unde pot fi introduse alegerile. Această funcție OFFSET spune practic: Începeți din celula FruitsHeading, coborâți un rând și peste 0 coloane, apoi selectați o zonă care are 20 de rânduri lungime și o coloană lată. Astfel, acest lucru ne oferă blocul cu 20 de rânduri în care sunt introduse alegerile pentru fructe.
Următoarea bucată a formulei este funcția ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Aici, funcția OFFSET (explicată mai sus) a fost înlocuită cu „de mai sus” (pentru a face lucrurile mai ușor de citit). Dar funcția ISBLANK funcționează pe gama de celule de 20 de rânduri pe care o 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 20- intervalul de rând la care face referire funcția OFFSET este gol (gol) sau nu. În acest exemplu, primele 8 valori din set vor fi FALSE, deoarece primele 8 celule nu sunt goale, iar ultimele 12 valori vor fi ADEVĂRATE.
Următoarea piesă a formulei este funcția INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Din nou, „cele de mai sus” se referă la funcțiile ISBLANK și OFFSET descrise mai sus. Funcția INDEX returnează un tablou care conține cele 20 de valori TRUE / FALSE create de funcția ISBLANK.
INDEXeste folosit în mod normal pentru a alege o anumită valoare (sau o gamă de valori) din un bloc de date, specificând un anumit rând și coloană (în cadrul acelui bloc). Dar setarea intrărilor de rând și coloană la zero (așa cum se face aici) face ca INDEX să returneze un tablou care conține întregul bloc de date.
Următoarea bucată a formulei este funcția MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Funcția MATCHreturnează poziția primei valori TRUE, în cadrul tabloului returnat de funcția INDEX. Deoarece primele 8 intrări din listă nu sunt necompletate, primele 8 valori din tablă vor fi FALSE, iar a noua valoare va fi ADEVĂRATĂ (din moment ce cel de-al nouă rând thdin interval este gol).
Deci funcția MATCH va returna valoarea 9. În acest caz, însă, dorim cu adevărat să știm câte intrări sunt în listă, deci formula scade 1 din valoarea MATCH (care dă poziția ultimei intrări). Așadar, în cele din urmă, MATCH (ADEVĂRAT, de mai sus, 0) -1 returnează valoarea 8
Următoarea bucată a formulei este funcția IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Funcția IFERROR returnează o valoare alternativă, dacă prima valoare specificată are ca rezultat o eroare. Această funcție este inclusă, deoarece, dacă întregul bloc de celule (toate cele 20 de rânduri) sunt umplute cu intrări, funcția MATCH va returna o eroare.
Acest lucru se datorează faptului că spunem funcției MATCH să căutăm prima valoare TRUE (în tabloul de valori din funcția ISBLANK), dar dacă NICIUL celulelor nu este gol, atunci întregul tablou va fi umplut cu valori FALSE. Dacă MATCH nu poate găsi valoarea țintă (TRUE) în tabloul pe care îl caută, returnează o eroare.
Deci, dacă întreaga listă este completă (și, prin urmare, MATCH returnează o eroare), funcția IFERROR va în schimb, întoarceți valoarea de 20 (știind că trebuie să existe 20 de intrări în listă).
În sfârșit, OFFSET (FruitsHeading, 1,0, de mai sus, 1)returnează intervalul pe care îl căutăm de fapt: Începeți din celula FruitsHeading, coborâți pe un rând și peste 0 coloane, apoi selectați o zonă care are totuși multe rânduri, cât există intrări în listă (și o coloană lată). Așadar, întreaga formulă împreună va returna intervalul care conține doar intrările reale (până la prima celulă goală).
Utilizarea acestei formule 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 din celula de sus și sunt contigue), iar lista verticală va reflecta întotdeauna lista curentă (vezi figura 6).
Cele fișier de exemplu (liste dinamice) utilizate aici sunt incluse și se pot descărca de pe acest site web. Cu toate acestea, macro-urile nu funcționează, deoarece WordPress nu îi plac cărțile Excel cu macros în ele.
Ca alternativă la specificarea numărului de rânduri din blocul de listă, blocul de listă poate fi atribuit nume propriu de gamă, care poate fi apoi utilizat într-o formulă modificată. În fișierul de exemplu, o a doua listă (Nume) folosește această metodă. Aici, întregului bloc de listă (sub rubrica „NUME”, 40 de rânduri în fișierul de exemplu) i se atribuie numele de rază Numele blocajului. Formula alternativă pentru definirea Listelor de nume este apoi:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
unde NumeBlucînlocuiește OFFSET (FruitsHeading, 1,0,20,1) și
Deci, pentru listele derulante care pot fi editate cu ușurință (inclusiv de alți utilizatori care nu au experiență), încercați să utilizați nume de gamă dinamice! Și rețineți că, deși acest articol s-a concentrat pe listele derulante, numele intervalelor dinamice pot fi utilizate oriunde aveți nevoie pentru a face referire la un interval sau o listă care poate varia ca dimensiune. Bucurați-vă!