Uvod u dinamičke raspone
Funkcija VLOOKUP često se koristi za pronalaženje informacija pohranjenih unutar tablica u Excelu. Na primjer, ako imamo popis imena i dobi ljudi:
A onda možemo u obližnjoj ćeliji upotrijebiti funkciju VLOOKUP za određivanje Pauline starosti:
Do sada je to prilično standard. No što se događa ako na popis moramo dodati još neka imena? Očigledna bi pomisao bila izmjena raspona u VLOOKUP -u. Međutim, u doista složenom modelu može postojati nekoliko referenci na VLOOKUP. To znači da bismo morali promijeniti svaku referencu - pod pretpostavkom da znamo gdje se nalaze.
Međutim, Excel nudi alternativni način - nazvan DINAMIČKI raspon. Ovo je raspon koji automatski proširuje ažuriranja. Ovo je savršeno ako se vaši popisi uvijek proširuju (npr. Podaci o prodaji mjesec po mjesec).
Za postavljanje dinamičkog raspona moramo imati naziv raspona - pa ćemo nazvati naš AGE_DATA. Pristup postavljanju dinamičkih raspona razlikuje se između programa Excel 2007 i ranijih verzija programa Excel:
U Excelu 2007 kliknite "Definiraj naziv" ispod formula:
U ranijim verzijama programa Excel kliknite "Umetni", a zatim Imena.
U skočni okvir unesite naziv našeg dinamičkog raspona - koji je "UZRASTNI PODACI":
U okvir s oznakom "Odnosi se na" moramo unijeti raspon naših podataka. To će se postići pomoću funkcije OFFSET. Ovo ima 5 argumenata:
= OFFSET (Referenca, Redovi, Cols, Visina, Širina)
- Referenca je adresa gornjeg lijevog kuta našeg raspona - u ovom slučaju ćelije B5
- Redovi su broj redova s gornje lijeve strane za koji želimo da taj raspon bude - što će u ovom slučaju biti 0
- Cols je broj redaka s gornje lijeve strane za koji želimo da taj raspon bude - što će u ovom slučaju biti 0
- Visina raspona - pogledajte dolje za ovo
- Širina raspona - ovo je 2, imamo dva stupca u našem rasponu (ime osoba i njihova dob)
Sada će visina raspona morati varirati ovisno o broju unosa u našoj tablici (koji je trenutno 7).
Naravno da želimo način odbrojavanja redaka u našoj tablici koji se automatski ažuriraju - pa je jedan od načina za to korištenje funkcije COUNTA. Ovo samo broji broj ćelija koje nisu prazne u rasponu. Kako su naša imena u stupcu B, broj unosa u naše podatke je COUNTA (B: B).
Imajte na umu da biste, ako ovo stavite u ćeliju, dobili vrijednost 8 - jer uključuje zaglavlja Names. Međutim, da je to beznačajno.
Dakle, u okvir "Odnosi se na" stavljamo:
= OFFSET ($ B $ 5,0,0, counta (B: B), 2)
I kliknite gumb U redu. Naš dinamički raspon je sada stvoren.
Sada se vratite na formule VLOOKUP i zamijenite raspon $ B: 4: $ C11 imenom našeg novog dinamičkog raspona AGE_DATA tako da imamo:
Do sada se ništa nije promijenilo. Međutim, ako našoj tablici dodamo još nekoliko imena:
A u ćeliji u kojoj smo imali Paula zamijenite je novim imenom, poput Pedra (koje nije bilo na izvornom popisu):
Vidimo da je Excel automatski vratio Pedrovu dob - iako nismo promijenili formule VLOOKUP. Umjesto toga, opseg dinamičkog raspona povećan je uključivanjem dodatnih naziva.
Dinamički rasponi vrlo su korisni kada imamo sve veće količine podataka - osobito kada su potrebne tablice VLOOKUP i PIVOT.
