Broj stavki dostupnih za filtriranje je ograničen. Excel ne može filtrirati stupce u kojima broj stavki prelazi 999 (ne broj redaka).
Za filtriranje ako ima više od 999 stavki, upotrijebite napredni filtar.
Za izradu naprednog filtra koristit ćemo funkcije “OFFSET” i “COUNTA” u programu Microsoft Excel.
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: - Broj redaka koje želite premjestiti s početne točke, a oba ova 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.
Da bismo postigli željeni rezultat, moramo slijediti 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 do 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 ovom popisu dodamo još zemalja, on bi trebao biti automatski dostupan na padajućem popisu.
Za pripremu naprednog filtra 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žemo dodati više unosa od 999 stavki u Microsoft Excel.