Počet položiek dostupných na filtrovanie je obmedzený. Excel nemôže filtrovať stĺpce, v ktorých počet položiek presahuje 999 (nie počet riadkov).
Ak chcete filtrovať, ak existuje viac ako 999 položiek, použite rozšírený filter.
Na vytvorenie rozšíreného filtra použijeme v programe Microsoft Excel funkcie „OFFSET“ a „COUNTA“.
COUNTA: Vráti počet buniek, ktoré obsahujú hodnoty.
Syntax funkcie „COUNTA“: = COUNTA (hodnota1, hodnota2, hodnota3 ….)
Príklad: V rozsahu A1: A5 bunky A2, A3 a A5 obsahujú hodnoty a bunky A1 a A4 sú prázdne. Vyberte bunku A6 a napíšte vzorec-
= COUNTA (A1: A5) funkcia sa vráti 3
OFFSET: Vráti odkaz na rozsah, ktorý je posunutý počtom riadkov a stĺpcov z iného rozsahu alebo bunky.
Syntax funkcie OFFSET: = OFFSET (referencia, riadky, stĺpce, výška, šírka)
Referencia:- Toto je bunka alebo rozsah, z ktorého chcete kompenzovať.
Riadky a stĺpce na presun: - Počet riadkov, ktoré chcete presunúť z počiatočného bodu, a oba môžu byť kladné, záporné alebo nulové.
Výška a šírka: - Toto je veľkosť rozsahu, ktorý chcete vrátiť. Toto je voliteľné pole.
Ukážme si to na porozumení funkcie Offset v Exceli.
Máme údaje v rozsahu A1: D10. Stĺpec A obsahuje kód produktu, stĺpec B obsahuje množstvo, stĺpec C obsahuje náklady na výrobok a stĺpec D obsahuje celkové náklady. Musíme vrátiť hodnotu bunky C5 v bunke E2.
Aby sme dosiahli požadovaný výsledok, musíme postupovať podľa nižšie uvedených krokov.
- Vyberte bunku E2 a napíšte vzorec.
- = OFFSET (A1,4,2,1,1)a stlačte kláves Enter na klávesnici.
- Funkcia vráti hodnotu bunky C5.
V tomto prípade potrebujeme získať hodnotu z bunky C5 až E2. Naša referenčná bunka je prvou bunkou v rozsahu, ktorý je A1 a C5, je o 4 riadky nižšie a 2 stĺpce napravo od A1. Vzorec je teda = OFFSET (A1,4,2,1,1) alebo = OFFSET (A1,4,2) (pretože 1,1 je voliteľné).
Teraz si vezmime príklad na získanie poslednej hodnoty v dynamickom zozname.
Názvy krajín máme v rozsahu. Ak teraz do tohto zoznamu pridáme ďalšie krajiny, malo by byť v rozbaľovacom zozname k dispozícii automaticky.
Pri príprave rozšíreného filtra postupujte podľa nižšie uvedených krokov:-
- Vyberte bunku B2.
- Prejdite na kartu Údaje a zo skupiny Dátové nástroje vyberte položku Overenie údajov.
- Zobrazí sa dialógové okno „Overenie údajov“. Na karte „Nastavenia“ vyberte v rozbaľovacom zozname Povoliť „Vlastné“.
- Aktivuje sa pole vzorcov.
- Do tohto poľa napíšte vzorec.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Kliknite na OK.
- V tejto fáze je poslednou aktualizovanou bunkou A11.
- Ak chcete skontrolovať, či validácia údajov funguje správne, zadajte do bunky A12 názov mesta.
Hneď ako do A12 pridáte záznam, bude pridaný do rozbaľovacieho zoznamu.
Toto je spôsob, akým môžeme do programu Microsoft Excel pridať viac než 999 položiek.