Excelentní kousky

Jak název napovídá, tento díl se bude věnovat seznamům. Pod pojmem seznamy si v tutu chvíli představte oblast dat tvořenou sadou záznamů v řádcích pod sebou. Máte-li například svou domácí hudební sbírku, pak záznamem je konkrétní album s položkami interpret, název alba, hudební žánr a rok vydání umístěnými v jednotlivých sloupcích. O dlouhém seznamu můžeme mluvit také jako o databázi.
Naše seznamy budete jistě chtít třidit, vyhledávat v nich, zjišťovat duplicitní záznamy apod. Držte se prosím následujících rad:

• Seznamy by zpravidla měly mít svou hlavičku, tj. vymezen první řádek na popisky sloupců. Do tohoto řádku se pak umísťují ovládací prvky pro filtrování. Na hlavičky je třeba vždy brát ohled ve funkcích a nástrojích Excelu, stejně jako při exportu dat.
• Díky hlavičce nelze použít čísla řádků za určující pro pořadí záznamu (první záznam neleží v prvním řádku). V případě nutnosti vymezte první sloupec seznamu pro číselník.
• V seznamech zapomeňte na sloučené buňky. Používáte-li je v popiscích sloupců, nepřiřazujte je bezprostředně k prvnímu záznamu. Tip: Vyberte oblast buněk, které chcete překrýt textem v první buňce. Zvolte Formát / Buňky / karta Zarovnání / Vodorovně: na střed výběru. Tento postup ušetří řadu trápení se sloučenými buňkami.
• Dbejte na to, aby položky téhož sloupce měly stejný charakter (číslo, text, pravdivostní hodnota, zápis datumu, ...). Vhodné je například i stanovit maximální délku textové položky.
• Není-li to nezbytně nutné, na list se seznamem neumísťujte již nic jiného. Případné vzorce zpracovávající filtrovaná data umísťujte nad seznam (opět vyvstává potřeba použít první sloupec seznamu jako číselník, neboť dochází k posunu počátku seznamu).
• Prázdné řádky mezi záznamy jsou nepřípustné!
• Položky seznamu by měly bít fixní (neobsahovat vzorce). Vyvarujte se také zalamování řádků (Alt+Enter) a extrémně dlouhých textů.

Z osmdesáti procent seznamy dotváříme tak, že nové záznamy doplňujeme na konec seznamu. Pak často vyvstane potřeba data abecedně seřadit. K tomu postačí umístit kurzor do seznamu a zvolit Data / Seřadit. Pozor! Ikona "AZ" (objevuje se na panelu nástrojů Standardní) je ikonou Seřadit vzestupně. Doporučuji ji zaměnit za ikonu Seřadit (Nástroje / Vlastní / karta Příkazy, kategorie: Data), která má sice stejný vzhled, ale odlišnou funkci (dává na výběr kritérium řazení). Zmíněná ikona Seřadit vzestupně totiž bez vyptávání řadí záznamy podle aktivní buňky výběru, a to vzestupně. Vzniká tak dojem, že jsme spustili něco, nad čím nemáme kontrolu.

Poznámka
Stejně nešťastně (tentýž vzhled ikony, jiná funkce) jako ikona řazení je zpracována i ikona Nový versus Nový.... nebo třeba ikony pro tisk. K čemu Excel má v databázi přes 1000 ikon, když používá ty samé pro rozdílné funkce? Ach jo...

Tip I.
Nezáleží na tom jak se záznamy v Excelu manipulujete. Chcete-li si ověřit, že pohybem dat jste položky záznamů neposunuli (což může například u položek bankovních účtů klientů způsobit pěkný poprask), vyberte pseudonáhodné záznamy a barevně je zvýrazněte. Po manipulaci tyto záznamy zkontrolujte. Barevně zvýrazněné položky samozřejmě nesmí doznat posunu ve svislém směru. Zároveň musí sedět počet záznamů před a po manipulaci.

Tip II.
Nesetříděná data mohou způsobit problémy u vyhledávacích funkcí nebo při statistickém vyhodnocování. Přesto se někdy setkáte s požadavkem uspořádat záznamy po skupinách (pohromadě TV, ledničky, walkmany, ...). Je-li takový seznam vytvořen a nechtěně seřazen abecedně, můžete toho při pozdější práci litovat. Proto je vhodné vytvořit sloupec, který narozdíl od doporučovaného číselníku nebude tvořen rostoucí posloupností (1, 2, ..., n-1, n), ale u každého záznamu bude buď hodnota pořadí nebo hodnota společná pro celou skupinu. Data se pak budou řadit podle tohoto sloupce.
Alternativně lze použít klíč řazení (Data / Seřadit / tlačítko Možnosti). Roletka využívá seznamů z dialogu Nástroje / Možnosti / karta Seznamy. Posloupnost řazení může být v tomto případě postavena jen na základě textových řetězců.

Automatický filtr

Automatický filtr je první volbou, která nám pomůže třidit data. Najdeme ji pod menu Data/ Filtr / Automatický filtr a než ji použijeme, musíme umístit kurzor dovnitř seznamu. Excel vyhledá první řádek oblasti (naše připravená hlavička) a umístí do něj sadu ovládacích prvků (rozbalovacích nabídek). Stejným způsobem v menu volbu deaktivujeme.

Klepnutím na šipku rozbalovacího seznamu v jednom ze sloupců už pak definujeme filtrování dat onoho sloupce. Dodávám, že filtrováním data neztrácíme, pouze ta nechtěná skrýváme.

Vězte, že:

• Volba (vše) deaktivuje filtr pro daný sloupec.
• Volba (prvních 10...) umožňuje filtraci prvních či posledních "x" položek, resp. prvních či posledních "x" procent položek (viz roletky dialogu). Za "první" se přitom berou hodnoty největší, tj. na číselné ose ty nejvíce vpravo. Volba kupodivu ignoruje textové položky, přestože i znaky lze třídit.
• Volbou (vlastní...) definujeme v zobrazeném dialogu jednu či dvě vlastní podmínky. V pravých okénkách přitom nemusíme vybírat pouze ze seznamu, pro textové položky smíme použít i vlastní "šablonky" se zástupnými symboly (* ... cokoli, ? ... jeden znak). Následující ukázka například vyfiltruje všechny záznamy, jejich položka v daném sloupci začíná písmenem "L" a končí "á" (Lijáčková, Lahůdková).

• Výběrem názvu konkrétní položky filtrujeme všechny buňky sloupce obsahující tento název.
• Volby (prázdné) a (neprázdné) filtrují záznamy s nevyplněnými položkami (jen tehdy se také zobrazují).
• Chcete-li resetovat kritéria filtrování pro všechny sloupce naráz, zvolte Data / Filtr / Zobrazit vše.

Poznámka
Trojí kliknutí "tam" a trojí kliknutí "zpět" je zvláště pro experimentování dosti otravné. Do panelu nástrojů Standardní můžete sice lehce přidat ikonu Automatický filtr (trychtýř se znakem rovná se, viz Nástroje / Vlastní / karta Příkazy, kategorie: Data a obrázek výše), ale i zde se nejedná o funkci ekvivalentní volbě z menu. Klepnutí na tuto ikonu způsobí aktivaci filtru s kritériem postaveným na obsahu aktuální buňky. Tu "správnou" ikonu ale v nabídce nenajdete. Jedinou možností je přetažení nabídky Data/ Filtr / Automatický filtr za držení klávesy Ctrl na viditelnější místo. I proto jsem nechal vzniknout speciálně pro tento sešit panelu nástrojů Filtry. Jeho vytvoření po otevření sešitu (a smazaní po uzavření) je zajištěno volně přístupným kódem VBA (viz Alt+F11, objekt ThisWorkbook, procedury Workbook_Open, Workbook_BeforeClose).

Poznámka
Máte-li v Excelu XP na panelu nástrojů vytaženou ikonu "Odstranit řádek" ( viz Nástroje / Vlastní / karta Příkazy, kategorie: Úpravy), stane se, že po aplikaci filtru "(prázdné)" a úmyslu odstranit filtrované záznamy bude tato ikona nepřístupná. V tom případě užijte pravé tlačítko myši a volbu Odstranit řádky.

Dodatek
Excel transformuje každé slůvko PRAVDA a NEPRAVDA, které mu v listu zadáte, na pravdivostní hodnotu, a tak se můžete setkat s tím, že při použití automatického filtru (například při vytváření seznamu funkcí Excelu) najdete tyto slůvka špatně abecedně zařazená (nachází se na konci seznamu). V sešitu najdete ukázku toho, jak z toho ven.

Rozšířený filtr

Rozšířený filtr, jak název napovídá, obsahuje více možností pro filtrování dat. Kritéria se přitom definují přímo na listu a to tím způsobem, že pro daný sloupec vytvoříme na jiném místě listu (zpravidla nad vlastním seznamem s odstupem několika řádků) stejnou hlavičku, pod níž zapíšeme kritérium. Následně umístíme kurzor do seznamu a vyvoláme dialog z menu Data / Filtr / Rozšířený filtr. . Filtrovaná data můžeme buď zpracovat přímo v seznamu, nebo výsledek přenést jinam (volba Kopírovat jinam). Do Oblasti seznamu je také zahrnuta hlavička seznamu. Vyplněný dialog ukazuje následující obrázek.

• Jedna podmínka pro jeden sloupec.
• Více podmínek pro jeden sloupec.
• Kombinace předcházejících.

Přitom platí následující:

• Podmínky zapsané ve stejném řádku jsou navzájem ve vztahu odpovídajímu logické spojce "A (AND)".
• Podmínky zapsané v různých řádcích jsou navzájem ve vztahu odpovídajímu logické spojce "NEBO (OR)".

Z předchozího vyplývá, že pokud potřebujeme dvě podmínky pro jeden sloupec ve vztahu "A (AND)", museli bychom mít dvě hlavičky stejného názvu, pod nimiž budou v jednom řádku zapsány podmínky filtrování. A tak to také funguje.

Tento příklad by bylo možné řešit i automatickým filtrem. Ten by nám ovšem neumožnil zadat více jak dvě podmínky pro jeden sloupec.

Do třetice si ukážeme kombinovaný příklad, kdy má platit:

A) Hodnota záznamu ve sloupci "A" je menší než 1500 nebo
B) záznam ve sloupci "Typ desky" neobsahuje písmeno "V" a současně je hodnota téhož záznamu ve sloupci "A" větší než 1600.

Ukázky několika zápisů obecných podmínek:

Je vidět, že i rozšířené filtrování podporuje zástupné symboly hvězdička a otazník. Pro deaktivaci filtru slouží volba Data / Filtr / Zobrazit vše.

Dopočítávaná kritéria

Podmínku filtrování této metody tvoří vzorec, jehož výsledkem je hodnota PRAVDA či NEPRAVDA. Použití nejlépe osvětlí příklad:

Vzorec viditelný v řádků vzorců náleží buňce D3 a svým charakterem je podobný maticovému vzorci. Odkazuje se na část seznamu spadající pod hlavičku "Body" (alternativně může ukazovat do první buňky tohoto sloupce). V tuto chvíli není podstatné, že vrací chybovou hodnotu #Název, filtr ji zpracuje správně. V dialogu je pak nutné uvažovat i hlavičku kritéria, jež nese libovolný název, ovšem odlišný od všech hlaviček vlastního seznamu. Povšimněte si také komentáře pole "Oblast seznamu:". Metodu dopočítávání je možné kombinovat s předchozími.

Funkce pro filtrovaná data

Funkce "kovaná" na filtrovaná data nese název SUBTOTAL a dokáže vrátit vícero údajů o filtrovaných datech (součet, součin, běžná popisná statistika). Více se o ní dozvíte v nápovědě. Hodit se mohou i tzv. databázové funkce, mezi které patří DMAX, DMIN, DSUMA, DPOČET, DPRŮMĚR, DSOUČIN a další (zahrnuty i funkce statistické), jejichž použití je naznačeno zde:

Souhrny

Každý sběratel má čas od času potřebu počítat a škatulkovat ty své "kousky". K těmto účelům slouží nástroj Excelu, který se skrývá v menu Data / Souhrny. Stačí mít seznam podobného charakteru, jako je níže zobrazený, kurzor ponechat v oblasti seznamu a aplikovat zmíněné Souhrny. Obrázky dokumentují stav "před" a "po".

Excel vytvoří stromovou strukturu dat, jejíž zobrazení je možné ovlivnit globálně změnou zobrazení celé úrovně (zde tlačítka 1, 2, 3), nebo individuálně (tlačítka plus a mínus). Seznam můžete vrátit do původního stavu volbou Data / Souhrny / Odstranit vše.

Unikátními (jedinečnými, originálními) rozumějte takové položky, resp. celé záznamy, které se vyskytují v seznamu jen jednou. Duplicitní položky (záznamy) jsou takové, které se vyskytují v seznamu vícekrát. Těžko ale určíme, co je v případě dvou stejných zápisů originál a co kopie. Dohoda neexistuje, postavit se k problému můžeme dvěma způsoby:

• Originální je ten zápis v listu, který se vyskytuje jako první ve směru zleva doprava a shora dolů ("Z").
• Originální je ten zápis, který se na listu vyskytl dříve ("Kdo dřív přijde, ten dřív mele.").

Ve skutečnosti nám ale nejde ani tak o to rozlišit, co je originál a co kopie, nýbrž chceme eleminovat duplicitní hodnoty, resp. separovat unikátní hodnoty.

Jak na duplikáty

První postup využívá vzorce listu a automatický filtr.

Vzorec zobrazený v řádku vzorců je zkopírován z buňky B2 tažením do buněk níže. Tak vizuálně vytřídíme originály. Dále nastoupí automatický filtr s volbou (neprázdné), který seskupí originální záznamy. Ty poté můžeme kopírováním přenést jinam.

Druhý postup vychází z rozšířeného filtru a jeho podstata tkví ve ... ne neprozradím, pokud jste byli pozorní, pak už princip víte, pokud ne, vraťte se na této stránce zpět a projděte znovu všechny obrázky. Tak :-)

Ukázka je řešením kombinujícím rozšířený filtr s VBA. Kód je opět volně přístupný a makra pro tlačítka najdete pod objektem List1 (originaly II). Použita je i pojmenovaná oblast.

Další obrázek ukazuje přehled vzorců pro práci s duplicitními položkami a s vyjímkou posledního sloupce se jedná o vzorce listu postavené na funkci COUNTIF.

A ještě jedna technika....

Další příklad řeší problematiku od podlahy s pomocí vlastní funkce ORIGINALY. Jedná se o funkci, která vrací matici unikátních hodnot, navíc setříděných. V prvním kroku touto funkcí zjistíme počet unikátních hodnot, v kroku druhém vybereme počet buněk odpovídající počtu unikátů a zadáme maticový vzorec pro výpis těchto položek.

A tím pro dnešek končíme. Příště se budeme věnovat ovládacím prvkům na listu a ukážeme si řadu praktických příkladů.