Število elementov, ki so na voljo za filtriranje, je omejeno. Excel ne more filtrirati stolpcev, v katerih število elementov presega 999 (ne število vrstic).
Če želite filtrirati, če je več kot 999 elementov, uporabite napredni filter.
Za ustvarjanje naprednega filtra bomo v Microsoft Excelu uporabili funkciji »OFFSET« in »COUNTA«.
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: - Število vrstic, ki jih želite premakniti od začetne točke, pri čemer sta lahko obe 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.
Če želimo doseči želeni rezultat, moramo slediti 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 do 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 na ta seznam dodamo več držav, bi moral biti samodejno na voljo na spustnem seznamu.
Za pripravo naprednega filtra 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 v Microsoft Excel dodamo več vnosov kot 999 elementov.