Za pridobitev zadnje vrednosti na dinamičnem seznamu bomo uporabili možnost Preverjanje podatkov skupaj s funkcijama OFFSET in COUNTA v programu Microsoft Excel 2010/2013.
COUNTA: Vrne število celic, ki vsebujejo vrednosti.
Sintaksa funkcije »COUNTA«: = COUNTA (vrednost1, vrednost2, vrednost3….)
Primer: V območju A1: A5 celice A2, A3 in A5 vsebujejo vrednosti, celice A1 in A4 pa so prazne. Izberite celico A6 in napišite formulo-
= COUNTA (A1: A5) funkcija se bo vrnila 3
OFFSET: Vrne sklic na obseg, ki je premaknjen za številne vrstice in stolpce iz drugega obsega ali celice.
Sintaksa funkcije OFFSET: = OFFSET (sklic, vrstice, stolpci, višina, širina)
Referenca:- To je celica ali obseg, iz katerega želite izravnati.
Vrstice in stolpci za premikanje: - Koliko vrstic želite premakniti izhodiščno točko in obe sta lahko pozitivni, negativni ali nič.
Višina in širina: - To je velikost območja, ki ga želite vrniti. To polje je neobvezno.
Vzemimo primer za razumevanje funkcije Offset v Excelu.
Imamo podatke v razponu A1: D10. Stolpec A vsebuje kodo izdelka, stolpec B vsebuje količino, stolpec C vsebuje stroške izdelka, stolpec D pa skupne stroške. V celico E2 moramo vrniti vrednost celice C5.
Sledite spodnjim korakom.
- Izberite celico E2 in napišite formulo.
- = OFFSET (A1,4,2,1,1)in na tipkovnici pritisnite Enter.
- Funkcija bo vrnila vrednost celice C5.
V tem primeru moramo pridobiti vrednost iz celice C5 v E2. Naša referenčna celica je prva celica v obsegu, ki je A1 in C5 je 4 vrstice spodaj in 2 stolpca desno od A1. Zato je formula = OFFSET (A1,4,2,1,1) ali = OFFSET (A1,4,2) (ker 1,1 ni obvezna).
Zdaj pa vzemimo primer za pridobivanje zadnje vrednosti na dinamičnem seznamu.
V razponu imamo imena držav. Če dodamo več držav na ta seznam, bi moral biti samodejno na voljo na spustnem seznamu.
Za pripravo dinamičnega seznama moramo ustvariti formulo, ki bo pridobila zadnjo vrednost v stolpcu in se samodejno posodobila, ko bo dodana nova številka.
Sledite spodnjim korakom:-
- Izberite celico B2.
- Pojdite na zavihek Podatki, v skupini Orodja za podatke izberite Preverjanje podatkov.
- Prikaže se pogovorno okno »Preverjanje podatkov«. Na zavihku »Nastavitve« na spustnem seznamu Dovoli izberite »Po meri«.
- Polje s formulo se aktivira.
- V to polje vnesite formulo.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Kliknite V redu.
- Na tej stopnji je zadnja posodobljena celica A11.
- Če želite preveriti, ali preverjanje podatkov deluje pravilno, dodajte ime mesta v celico A12.
Takoj, ko dodate vnos v A12, bo ta dodan na spustni seznam.
Tako lahko ustvarite dinamičen seznam in vanj samodejno vnesete nove vnose v programih Microsoft Excel 2010 in 2013.