Za dohvaćanje posljednje vrijednosti na dinamičkom popisu koristit ćemo opciju Validacija podataka zajedno s funkcijama OFFSET i COUNTA u programu Microsoft Excel 2010/2013.
COUNTA: Vraća broj ćelija koje sadrže vrijednosti.
Sintaksa funkcije “COUNTA”: = COUNTA (vrijednost1, vrijednost2, vrijednost3….)
Primjer: U rasponu A1: A5, ćelije A2, A3 i A5 sadrže vrijednosti, a ćelije A1 i A4 su prazne. Odaberite ćeliju A6 i napišite formulu-
= COUNTA (A1: A5) funkcija će se vratiti 3
OFFSET: Vraća referencu na raspon koji je pomaknut za niz redaka i stupaca iz drugog raspona ili ćelije.
Sintaksa funkcije OFFSET: = OFFSET (referenca, redovi, stupci, visina, širina)
Referenca:- Ovo je ćelija ili raspon iz kojega se želite pomaknuti.
Redovi i stupci za pomicanje: - Koliko redaka želite pomaknuti početnu točku, a oba mogu biti pozitivna, negativna ili nula.
Visina i širina: - Ovo je veličina raspona koji želite vratiti. Ovo je izborno polje.
Uzmimo primjer za razumijevanje funkcije pomaka u Excelu.
Imamo podatke u rasponu A1: D10. Stupac A sadrži šifru proizvoda, stupac B sadrži količinu, stupac C sadrži cijenu proizvoda, a stupac D sadrži ukupne troškove. Moramo vratiti vrijednost ćelije C5 u ćeliji E2.
Slijedite dolje navedene korake.
- Odaberite ćeliju E2 i napišite formulu.
- = OFFSET (A1,4,2,1,1)i pritisnite Enter na tipkovnici.
- Funkcija će vratiti vrijednost ćelije C5.
U ovom primjeru moramo dobiti vrijednost iz ćelije C5 u E2. Naša referentna ćelija prva je ćelija u rasponu koji je A1, a C5 je 4 retka ispod i 2 stupca desno od A1. Dakle, formula je = OFFSET (A1,4,2,1,1) ili = OFFSET (A1,4,2) (budući da je 1,1 izborna).
Uzmimo sada primjer za dohvaćanje posljednje vrijednosti na dinamičkom popisu.
Imamo nazive država u nizu. Sada, ako dodamo još zemalja na ovaj popis, on bi trebao biti automatski dostupan na padajućem popisu.
Za pripremu dinamičkog popisa moramo stvoriti formulu koja će dohvatiti posljednju vrijednost u stupcu i automatski se ažurirati pri dodavanju novog broja.
Slijedite dolje navedene korake:-
- Odaberite ćeliju B2.
- Idite na karticu Podaci, odaberite Provjera podataka iz grupe Podatkovni alati.
- Pojavit će se dijaloški okvir "Validacija podataka". Na kartici "Postavke" odaberite "Prilagođeno" s padajućeg popisa Dopusti.
- Okvir formule će se aktivirati.
- Napišite formulu u ovaj okvir.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Pritisnite OK.
- U ovoj fazi posljednja ažurirana ćelija je A11.
- Da biste provjerili radi li provjera valjanosti podataka, dodajte naziv grada u ćeliju A12.
Čim dodate unos u A12, bit će dodan na padajući popis.
Ovo je način na koji možete stvoriti dinamički popis i automatski u njega unijeti nove unose u programima Microsoft Excel 2010 i 2013.
