Počet položek dostupných pro filtrování je omezený. Excel nemůže filtrovat sloupce, ve kterých počet položek překračuje 999 (nikoli počet řádků).
Chcete -li filtrovat, pokud existuje více než 999 položek, použijte rozšířený filtr.
K vytvoření rozšířeného filtru použijeme v Microsoft Excelu funkce „OFFSET“ a „COUNTA“.
COUNTA: Vrátí počet buněk, které obsahují hodnoty.
Syntaxe funkce „COUNTA“: = COUNTA (hodnota1, hodnota2, hodnota3….)
Příklad: V rozsahu A1: A5 obsahují buňky A2, A3 a A5 hodnoty a buňky A1 a A4 jsou prázdné. Vyberte buňku A6 a napište vzorec-
= COUNTA (A1: A5) funkce se vrátí 3
OFFSET: Vrátí odkaz na rozsah, který je odsazen od řady řádků a sloupců z jiného rozsahu nebo buňky.
Syntaxe funkce OFFSET: = OFFSET (reference, řádky, sloupce, výška, šířka)
Odkaz:- Toto je buňka nebo rozsah, od kterého chcete kompenzovat.
Řádky a sloupce k přesunu: - Počet řádků, které chcete přesunout z počátečního bodu, a oba mohou být kladné, záporné nebo nulové.
Výška a šířka: - Toto je velikost rozsahu, který chcete vrátit. Toto je nepovinné pole.
Uveďme si příklad, abychom porozuměli funkci Offset v Excelu.
Máme data v rozsahu A1: D10. Sloupec A obsahuje kód produktu, sloupec B obsahuje množství, sloupec C obsahuje náklady na produkt a sloupec D obsahuje celkové náklady. Musíme vrátit hodnotu buňky C5 v buňce E2.
Abychom dosáhli požadovaného výsledku, musíme postupovat podle níže uvedených kroků.
- Vyberte buňku E2 a napište vzorec.
- = OFFSET (A1,4,2,1,1)a stiskněte klávesu Enter na klávesnici.
- Funkce vrátí hodnotu buňky C5.
V tomto případě potřebujeme získat hodnotu z buňky C5 až E2. Naše referenční buňka je první buňkou v rozsahu, který je A1 a C5 je o 4 řádky níže a 2 sloupce napravo od A1. Vzorec je tedy = OFFSET (A1,4,2,1,1) nebo = OFFSET (A1,4,2) (protože 1,1 je volitelný).
Nyní si vezmeme příklad k načtení poslední hodnoty v dynamickém seznamu.
Názvy zemí máme v řadě. Pokud do tohoto seznamu přidáme další země, mělo by být v rozevíracím seznamu k dispozici automaticky.
Chcete-li připravit pokročilý filtr, postupujte podle níže uvedených kroků:-
- Vyberte buňku B2.
- Přejděte na kartu Data, ze skupiny Datové nástroje vyberte Ověření dat.
- Zobrazí se dialogové okno „Ověření dat“. Na kartě „Nastavení“ vyberte z rozevíracího seznamu Povolit „Vlastní“.
- Aktivuje se pole vzorců.
- Do tohoto pole napište vzorec.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klikněte na OK.
- V této fázi je poslední aktualizovanou buňkou A11.
- Chcete -li zkontrolovat, zda ověření dat funguje správně, přidejte do buňky A12 název města.
Jakmile přidáte položku do A12, bude přidána do rozevíracího seznamu.
Tímto způsobem můžeme do aplikace Microsoft Excel přidat více položek než 999 položek.