Uvod v dinamični razpon
Funkcija VLOOKUP se pogosto uporablja za iskanje informacij, ki so shranjene v tabelah v Excelu. Na primer, če imamo seznam imen in starosti ljudi:
In potem lahko v bližnji celici s funkcijo VLOOKUP določimo Pavlovo starost:
Zaenkrat je to dokaj standard. Kaj pa se zgodi, če moramo na seznam dodati še nekaj imen? Očitna misel bi bila spremeniti obseg v VLOOKUP -u. Vendar pa lahko v res zapletenem modelu obstaja več sklicevanj na VLOOKUP. To pomeni, da bi morali spremeniti vsako referenco - ob predpostavki, da vemo, kje so.
Vendar Excel ponuja alternativni način - imenovan DYNAMIC range. To je obseg, ki samodejno razširi posodobitve. To je popolno, če se vaši seznami vedno širijo (npr. Podatki o prodaji iz meseca v mesec).
Za nastavitev dinamičnega obsega moramo imeti ime obsega - zato bomo svoje poimenovali AGE_DATA. Pristop za nastavitev dinamičnih obsegov se med Excelom 2007 in starejšimi različicami Excela razlikuje:
V Excelu 2007 kliknite »Določi ime« pod formulami:
V starejših različicah Excela kliknite »Vstavi« in nato imena ».
V pojavno okno vnesite ime našega dinamičnega obsega - to je »STAROSTNI PODATKI«:
V polje z oznako "Se nanaša na" moramo vnesti obseg naših podatkov. To bo doseženo s funkcijo OFFSET. To ima 5 argumentov:
= OFFSET (Referenca, Vrstice, Cols, Višina, Širina)
- Referenca je naslov zgornjega levega kota našega obsega - v tem primeru celice B5
- Vrstice so število vrstic na zgornji levi strani, za katere želimo, da je to območje - kar bo v tem primeru 0
- Cols je število vrstic na zgornji levi strani, za katere želimo, da je to območje - kar bo v tem primeru 0
- Višina območja - o tem glejte spodaj
- Širina obsega - to je 2, v našem obsegu imamo dva stolpca (ime oseb in njihova starost)
Zdaj se bo višina obsega morala razlikovati glede na število vnosov v naši tabeli (ki je trenutno 7).
Seveda želimo način štetja vrstic v naši tabeli, ki se samodejno posodobijo - zato je eden od načinov za to uporaba funkcije COUNTA. To samo šteje število praznih celic v obsegu. Ker so naša imena v stolpcu B, je število vnosov v naše podatke COUNTA (B: B).
Upoštevajte, da če bi to dali v celico, bi dobili vrednost 8 - saj vključuje glavo Imena. Vendar pa je to nepomembno.
Tako v polje »Nanaša se na« vnesemo:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
In kliknite gumb V redu. Naš dinamični razpon je zdaj ustvarjen.
Zdaj se vrnite k formulam VLOOKUP in zamenjajte obseg $ B: 4: $ C11 z imenom našega novega dinamičnega obsega AGE_DATA, tako da imamo:
Doslej se ni nič spremenilo. Če pa v svojo mizo dodamo še nekaj imen:
In v celici, kjer smo imeli Pavla, ga nadomestimo z novim imenom, kot je Pedro (tega ni bilo na prvotnem seznamu):
Vidimo, da je Excel samodejno vrnil Pedrovo starost - čeprav nismo spremenili formul VLOOKUP. Namesto tega se je obseg dinamičnega obsega povečal in vključil dodatna imena.
Dinamični obsegi so zelo uporabni, če imamo vedno večje količine podatkov - še posebej, kadar sta potrebni tabeli VLOOKUP in PIVOT.
