O introducere la gamele dinamice
Funcția VLOOKUP este adesea utilizată pentru a găsi informații stocate în tabele în Excel. De exemplu, dacă avem o listă cu numele și vârstele persoanelor:
Și apoi putem folosi într-o celulă din apropiere funcția VLOOKUP pentru a determina vârsta lui Paul:
Până în prezent, acesta este un standard destul de mare. Dar ce se întâmplă dacă trebuie să mai adăugăm câteva nume la listă? Gândul evident ar fi modificarea intervalului în VLOOKUP. Cu toate acestea, într-un model cu adevărat complex, pot exista mai multe referințe la VLOOKUP. Aceasta înseamnă că va trebui să schimbăm fiecare referință - presupunând că știm unde sunt.
Cu toate acestea, Excel oferă o modalitate alternativă - numită gamă DINAMICĂ. Acesta este un interval care extinde automat o actualizare. Acest lucru este perfect dacă listele dvs. sunt în continuă expansiune (de exemplu, date de vânzări de la lună la lună).
Pentru a configura un interval dinamic, trebuie să avem un nume de interval - așa că îl vom numi pe al nostru AGE_DATA. Abordarea pentru configurarea intervalelor dinamice diferă între Excel 2007 și versiunile anterioare ale Excel:
În Excel 2007, faceți clic pe „Definiți numele” sub formule:
În versiunile anterioare de Excel, faceți clic pe „Inserați” și apoi pe Nume ”.
În caseta pop-up, introduceți numele intervalului nostru dinamic - care este „AGE DATA”:
În caseta numită „Se referă la” trebuie să introducem intervalul de date. Aceasta va fi o realizare utilizată de o funcție OFFSET. Aceasta are 5 argumente:
= OFFSET (Referință, rânduri, coluri, înălțime, lățime)
- Referința este adresa colțului TOP STÂNGA din gama noastră - în acest caz celula B5
- Rows este numărul de rânduri din STÂNGA SUPERIOR pe care dorim să fie acel interval - care va fi 0 în acest caz
- Cols este numărul de rânduri din partea stângă sus pe care dorim ca intervalul să fie - care va fi 0 în acest caz
- Înălțimea gamei - vezi mai jos pentru aceasta
- Lățimea intervalului - acesta este 2 are, avem DOUĂ coloane în gama noastră (numele persoanelor și vârsta lor)
Acum, înălțimea intervalului va trebui să varieze în funcție de numărul de intrări din tabelul nostru (care este în prezent 7).
Desigur, dorim un mod de numărare a rândurilor din tabelul nostru care se actualizează automat - deci o modalitate de a face acest lucru este să folosiți funcția COUNTA. Acest lucru numără doar numărul de celule necompletate dintr-un interval. Deoarece numele noastre sunt în coloana B, numărul de intrări din datele noastre este COUNTA (B: B).
Rețineți că, dacă ați pune acest lucru într-o celulă, ați obține valoarea 8 - deoarece include antetul Nume. Cu toate acestea, este imaterial.
Deci, în caseta „Se referă la” am pus:
= OFFSET ($ B $ 5,0,0, țară (B: B), 2)
Și faceți clic pe butonul OK. Gama noastră dinamică este acum creată.
Acum reveniți la formulele VLOOKUP și înlocuiți intervalul $ B: 4: $ C11 cu numele noului nostru interval dinamic AGE_DATA, astfel încât să avem:
Până acum nu s-a schimbat nimic. Cu toate acestea, dacă adăugăm câteva nume în tabelul nostru:
Și în celula unde l-am avut pe Paul, înlocuiți-l cu un nume nou, cum ar fi Pedro (care nu era pe lista originală):
Și vedem că Excel a revenit automat la vârsta lui Pedro - chiar dacă nu am schimbat formulele VLOOKUP. În schimb, domeniul de aplicare al gamei dinamice a crescut pentru a include nume suplimentare.
Intervalele dinamice sunt foarte utile atunci când avem volume de date în creștere - mai ales când sunt necesare tabele VLOOKUP și PIVOT.
