Rozšířené filtrování

Databází (seznamem) Excelu budeme rozumět tabulku s názvy sloupců. Filtrování databáze je základní databázová technika vyhledávání záznamů (řádků tabulky) na základě specifikovaných podmínek (filtračních kritérií), kladených na pole databáze (sloupce tabulky). Souhrn těchto podmínek nazýváme filtrem. O záznamu, který naše kritéria splnil, říkáme, že prošel filtrem.

Rozšířené filtrování se skrývá pod tlačítkem Upřesnit na kartě Data, panel Seřadit a filtrovat. Má mnohem více možností, než Automatický filtr, dostupný tlačítkem Filtr téže karty. Platí však obecný zákon filozofie (nejen) Excelu: čím dokonalejší nástroj, tím profesionálnější obsluhy je třeba. Uživatel již nevyklepává podmínky filtrování myší, jako v případě Automatického filtru, ale musí sestrojit tabulku kritérií a do ní filtrační kriteria zakódovat.

Kriteriální tabulka

• Kritéria mohou být tvořena konstantami, nebo platnými vzorci Excelu.
• Kritérium ve formě konstanty píšeme v tabulce vždy do sloupce stejně nazvaného jako pole v databázi, na které se kritérium vztahuje. Formulujeme jej, podobně jako u funkce CountIf(), ve formě
• textové konstanty s event. zástupnými znaky (znak * za textem je implicitně uvažován, netřeba jej psát)
• textové konstanty ve formě nerovnice bez levé strany
• číselné konstanty.
• Podmínky spojené logickou spojkou and píšeme na stejný řádek kriteriální tabulky.
• Podmínky spojené logickou spojkou or píšeme do různých řádků kriteriální tabulky.

Na následujícím obrázku je dialogové okno rozšířeného filtru. Textová okna Oblast seznamu, Oblast kriteríí a Kopírovat do obsahují po řadě adresu databáze, adresu kriteriální tabulky a adresu výstupní oblasti. Filtrujeme nápoje v intervalu cen <10;40) a koření v cenovém intervalu <10;20). Kriteriální tabulka je podbarvena žlutě a vyfiltrované záznamy světle modře.

Adresu databáze v textovém okně Oblast seznamu získáme automaticky, pokud před filtrováním umístíme aktivní buňku do této databáze.

Adresu kriteriální tabulky obvykle zadáváme do příslušného okna tahem myší.

Okno Kopírovat do je přístupné pokud aktivujeme akci Kopírovat jinam. V opačném případě jsou filtrováním skrývány záznamy zdrojové databáze, které neprošly filtrem, stejně jako u Automatického filtru. Uvedeme-li v okně Kopírovat do adresu prázdné buňky, je chápána jako levý horní roh výstupní oblasti a vypíšou se všechna pole vyfiltrovaných záznamů. Připravíme-li si před filtrováním řádek názvů polí databáze a zadáme-li do okna Kopírovat do jeho adresu, pak se vypíšou jen tato pole vyfiltrovaných záznamů. Tak je to provedeno na následujícím obrázku.

Vzorce Excelu v kriteriální tabulce.

Kritéria tvořená obecnými vzorci Excelu nazýváme kritéria výpočtová. Takovéto kritérium umístíme do sloupce, jehož název se nesmí shodovat s žádným názvem pole filtrované databáze. Ze vzorce kritéria se odkazujeme

• relativní adresou na první datovou buňku pole databáze, v němž testujeme podmínku. Takto "Excel pozná", že má kritérium testovat v tomto poli a relativní adresu v poli si "interně mění" přes všechny záznamy databáze.
• absolutní adresou na další buňky s hodnotami používanými v testu (=F41>=PRŮMĚR($F$41:$F$66).
• Kritéria tohoto typu formulujeme, chceme-li ve vzorci použít obecný výraz. Může např. obsahovat jakékoli funkce Excelu.

Na následujícím obrázku je výpočtové kriterium použito k vyfiltrování záznamů s korunovou položkou větší nebo rovnou průměru. Kdybychom si průměr spočítali předem např. do buňky P1, použili bychom v buňce N42 vzorec =F41≥$P$1.

Kritéria s konstantami a kritéria výpočtová se mohou v kriteriální tabulce vyskytovat zároveň. Následující obrázek ukazuje příklad, kdy z databáze filtrujeme čtyři kategorie výrobků, počet výrobků je v intervalu <20,50> a náklady jsou menší, než průměrné. V prvních třech sloupcích kriteriální tabulky jsou kriteria s konstantami, v posledním je kriterium výpočtové.