Analýza dat v Microsoft Excelu

Series of Economics Textbooks Faculty of Economics, VSB – Technical University of Ostrava www.ekf.vsb.cz/soet [email protected] ZDE ZAČÍNÁME POČÍTAT ČÍSLA STRÁNEK, ŘÍMSKÝM AŽ PO KONEC OBSAHU BUDE ZDE UMÍSTĚNO LOGO UNIVERZITY A ŘADY, ZBYTEK prázdný PRÁZDNÁ STRÁNKA Series of Economics Textbooks Faculty of Economics, VSB – Technical University of Ostrava Vítězslav Novák ANALÝZA DAT V MICROSOFT EXCELU Ostrava, 2023 Vítězslav Novák Department of Applied Informatics Faculty of Economics VSB – Technical University of Ostrava Sokolská třída 33 702 00 Ostrava, CZ [email protected] Reviews Adéla Kondé, VSB – Technical University of Ostrava Martina Litschmannová, VSB – Technical University of Ostrava IORMA (odkaz na grant) The text should be quoted as follows: Novák, V. (2023). Analýza dat v Microsoft Excelu, SOET, vol. 35. Ostrava: VSB-TUO. © VŠB – Technická univerzita Ostrava 2023 Cover design by Editorial Centre, VSB – Technical University of Ostrava ISBN 978-80-248-4695-8 (on-line) DOI 10.31490/9788024846958 Předmluva Kniha Analýza dat v Microsoft Excelu není knihou pojednávající o základech Excelu, ani se nezabývá všemi nástroji, které Excel obsahuje. Tato kniha se zaměřuje na jeden konkrétní typ úloh, které je možné v Excelu řešit, a to agregační výpočty nad tabulkami typu seznam. Ostatní typy úloh řešitelné v Excelu proto nebudou v knize zmíněny. Předpokládá se tedy, že čtenář již Excel ovládá na běžné úrovni, není mu cizí používání vzorců a funkcí pro výpočty, formátování dat, tvorby grafů atd. Text je vytvářen pro posluchače předmětu Analýza dat v Microsoft Excelu, ale posloužit může každému, koho zajímá využití Excelu pro souhrnné agregační výpočty nad rozsáhlými daty pocházejícími z různých zdrojů, nejen z Excelu. Kniha je rozdělena do několika kapitol. Čtenář je nejdříve seznámen s tím, co je tabulka typu seznam, jaké má vlastnosti a poté se základními operacemi, které lze s takovou tabulkou provádět. V dalších kapitolách se již začnou provádět agregační výpočty, a to pomocí vzorců, včetně vzorců maticových, a funkcí jak s využitím běžné adresace, tak s použitím definovaných názvů. Zmíněny budou také výhody použití strukturovaných tabulek. Jádrem knihy je kapitola popisující použití kontingenčních tabulek a grafů. Pojmem kontingenční tabulky je zde myšlen nástroj Kontingenční tabulky v MS Excel, nikoliv kontingenční tabulky z hlediska statistického názvosloví (tabulky sdružených četností). S kontingenčními tabulkami bude čtenář seznámen nad jednotabulkovými zdroji dat, ale vysvětleno bude také použití vícetabulkových datových modelů v prostředí doplňku Power Pivot i s úvodem do jazyka Data Analysis Expressions (DAX). Pro tuto část publikace je vhodná znalost základních pojmů spojených s relačními databázemi. Závěrečnou kapitolou pak bude ukázka možností unifikovaného importu dat do Excelu pomocí nástroje Power Query. Ukázány budou nejen možnosti importu z několika různých zdrojů dat, ale hlavně většina transformací dat, které tento nástroj také umožňuje. Nedílnou součástí publikace je také množství sešitů Excelu dostupných na adrese https://drive.google.com/file/d/1MhTa83LN8yrW6JWXbM_mnY7bv5sWOlI7/view obsahujících data příkladů. U většiny z nich je v publikaci uvedeno řešení. Závěrem bych rád všem čtenářům popřál hodně radosti při práci v Excelu a vždy jen pozitivní výsledky při vašich výpočtech, a to nejen v Excelu. Vítězslav Novák, Ostrava, září 2022 Obsah Předmluva ............................................................................................... V Obsah .................................................................................................... VII Podrobný obsah ..................................................................................... IX Seznam vybraných zkratek ............................................................... XIII Kapitola 1 Úvod ....................................................................................... 1 1.1 O čem je a o čem není tato kniha ................................................. 1 1.2 Úvodní příklad k zamyšlení.......................................................... 2 Kapitola 2 Práce se seznamy v Excelu ................................................... 5 2.1 Co je to seznam .............................................................................. 6 2.2 Řazení seznamů ............................................................................. 8 2.3 Filtrování seznamů ...................................................................... 10 2.4 Ověření dat .................................................................................. 14 2.5 Odebrání duplicit ........................................................................ 19 2.6 Souhrn .......................................................................................... 21 2.7 Formátování seznamů ................................................................. 23 2.8 Ukotvení záhlaví seznamu .......................................................... 30 2.9 Řešení příkladu z úvodní kapitoly ............................................. 32 Kapitola 3 Analýza dat pomocí vzorců ................................................ 33 3.1 Základní statistické funkce využitelné pro analýzu dat .......... 33 3.2 Statistické funkce s podmínkou ................................................. 34 3.3 Vzorce s využitím relativního a absolutního adresování ......... 36 3.4 Využití maticových vzorců ......................................................... 44 3.5 Vzorce s využitím definovaných názvů ..................................... 48 3.6 Databázové funkce ...................................................................... 51 VIII Obsah 3.7 Řešení příkladu z úvodní kapitoly ............................................. 52 Kapitola 4 Analýza dat pomocí strukturovaných tabulek ................. 55 4.1 Strukturované tabulky ............................................................... 55 4.2 Řešení příkladu z úvodní kapitoly ............................................. 62 Kapitola 5 Analýza dat pomocí kontingenčních tabulek a grafů ...... 63 5.1 Vytváření kontingenčních tabulek ............................................. 63 5.2 Výpočty v kontingenční tabulce ................................................. 66 5.3 Řazení a filtrování v kontingenční tabulce ............................... 71 5.4 Seskupování hodnot .................................................................... 77 5.5 Formátování kontingenční tabulky ........................................... 80 5.6 Kontingenční grafy ..................................................................... 84 5.7 Řešení příkladu z úvodní kapitoly ............................................. 85 Kapitola 6 Analýza dat pomocí doplňku Power Pivot ....................... 87 6.1 Představení doplňku Power Pivot .............................................. 87 6.2 Data Analysis Expressions (DAX) ............................................. 96 6.3 Řešení příkladu z úvodní kapitoly ........................................... 107 Kapitola 7 Import a transformace dat pomocí Power Query ......... 109 7.1 Použití Power Query ................................................................. 109 7.2 Transformace dat ...................................................................... 112 7.3 Parametry dotazu ...................................................................... 120 7.4 Jazyk M ...................................................................................... 121 7.5 Řešení příkladu z úvodní kapitoly ........................................... 130 Přílohy .................................................................................................. 133 Literatura ............................................................................................. 135 Seznam tabulek .................................................................................... 137 Seznam obrázků .................................................................................. 139 Rejstřík ................................................................................................. 143 Summary .............................................................................................. 145 Podrobný obsah Předmluva ............................................................................................... V Obsah .................................................................................................... VII Podrobný obsah ..................................................................................... IX Seznam vybraných zkratek ............................................................... XIII Kapitola 1 Úvod ....................................................................................... 1 1.1 O čem je a o čem není tato kniha ............................................................ 1 1.2 Úvodní příklad k zamyšlení .................................................................... 2 Kapitola 2 Práce se seznamy v Excelu ................................................... 5 2.1 Co je to seznam ....................................................................................... 6 2.2 Řazení seznamů ...................................................................................... 8 2.2.1 Řazení seznamu podle jednoho sloupce ............................................. 8 2.2.2 Řazení seznamu podle více sloupců ................................................... 8 2.2.3 Řazení seznamu podle vlastního pořadí ............................................. 9 2.3 Filtrování seznamů ................................................................................ 10 2.3.1 Automatický filtr .............................................................................. 10 2.3.2 Rozšířený filtr .................................................................................. 13 2.4 Ověření dat ............................................................................................ 14 2.4.1 Obecné ověření dat .......................................................................... 15 2.4.2 Ověření dat typu seznam .................................................................. 18 2.5 Odebrání duplicit .................................................................................. 19 2.6 Souhrn ................................................................................................... 21 2.7 Formátování seznamů ........................................................................... 23 2.7.1 Formátování čísel ............................................................................. 23 2.7.2 Podmíněné formátování ................................................................... 27 2.8 Ukotvení záhlaví seznamu .................................................................... 30 2.8.1 Ukotvení příček ................................................................................ 30 2.8.2 Opakování řádků záhlaví při tisku ................................................... 30 2.9 Řešení příkladu z úvodní kapitoly ......................................................... 32 Kapitola 3 Analýza dat pomocí vzorců ................................................ 33 3.1 Základní statistické funkce využitelné pro analýzu dat ......................... 33 3.2 Statistické funkce s podmínkou ............................................................ 34 3.3 Vzorce s využitím relativního a absolutního adresování ....................... 36 3.3.1 Rozšířené filtrování s využitím vzorců ............................................ 38 3.3.2 Ověření dat pomocí vzorců .............................................................. 39 2 Kapitola 1 2023 V. Novák směrodatná odchylka. O těchto pokročilejší funkcích ale tato kniha nebude. Vlastně nebude ani o výše uvedených pěti základních funkcích, bude spíše o tom, jak těch pět zmíněných typů výpočtů v Excelu realizovat. A my si to zde ukážeme zejména s použitím: • vzorců s různým způsobem adresování dat, • strukturovaných tabulek, • kontingenčních tabulek a grafů, • doplňku Power Pivot, tedy s využitím datových modelů, ale to už budeme opravdu využívat databázovou terminologii. Veškeré příklady v knize budou demonstrovány v Excelu verze 365 ve stavu, v jakém se tato verze nacházela v září roku 2022. Naprostá většina funkcí a nástrojů by měla fungovat i v Excelech nižších verzí, minimálně však do verze Excel 2013. U příslušných částí bude upozornění na funkce, které v nižší verzi fungují jinak nebo nefungují vůbec. V této knize budou některé části textu zvýrazňovány dvojím způsobem. Pokud bude někde použito tučné písmo, pak jde o něco důležitého, např. o názvy některých nástrojů, tabulek, sloupců nebo přímo některé důležité hodnoty (např. "v tabulce Prodeje ve sloupci Prodejce zvolte hodnotu Pavel"). Použití kapitálky v textu označuje názvy jednotlivých karet pásu karet Excelu, jejich tlačítek nebo dialogových oken (např. "po kliku na tlačítko UPŘESNIT na kartě DATA se otevře dialogové okno ROZŠÍŘENÝ FILTR"). 1.2 Úvodní příklad k zamyšlení Ještě než se pustíte do studia práce se seznamy v dalších kapitolách, zkuste se zamyslet nad následujícím problémem. Mějme tabulku o jediném sloupci s názvem Číslo v záhlaví a devadesáti devíti náhodnými čísly mezi 1 a 9 v dalších řádcích. Počet řádků 99 byl zvolen proto, aby tabulka měla včetně záhlaví 100 řádků a všechny výpočtové vzorce tím pádem měly stejné parametry pro snadnější kontrolu vašeho řešení s řešením v knize. Obecně řečeno ale na počtu řádků nezáleží. Zkuste v Excelu vymyslet co nejvíce způsobů, jak zjistit četnost jednotlivých čísel 1 až 9 ve sloupci Číslo, např. viz Obrázek 1–1. Úvod 3 Analýza dat v Microsoft Excelu Obrázek 1–1 Výpočet četností ze seznamu čísel Zdroj: autor (2022) Pokud nevíte, jak vytvořit v Excelu náhodné číslo v určitém rozsahu, pak vězte, že lze použít funkci RANDBETWEEN. Takže do buňky A2 pod záhlaví Číslo vložte vzorec =RANDBETWEEN(1;9), vyplňte jej do sloupce až po řádek 100 a nezapomeňte pak vzorce zaměnit za hodnoty, aby se vám při každé akci v Excelu náhodná čísla neměnila. Pro tento účel všechna čísla stačí jen zkopírovat a do stejných buněk vložit jako hodnoty, na kartě DOMŮ tlačítko VLOŽIT – VLOŽIT JINAK. Tak co, na kolik způsobů výpočtu četností jste přišli? Pokud po přečtení každé kapitoly budete mít pocit, že tato kapitola ukazuje jeden nebo i více způsobů jak tuto četnost vypočítat, a pomocí nabytých znalostí tento výpočet zvládnete, pak to bude znamenat, že jste danou kapitolu pochopili. A pokud to nezvládnete sami, pak na konci každé kapitoly bude poslední podkapitola věnována právě řešení tohoto příkladu pomocí některého z nástrojů popisovaných v dané kapitole. 5 Kapitola 2 Práce se seznamy v Excelu Celá kniha bude pojednávat o zpracování dat v tabulkách, kterým se v Excelu říká seznamy. Seznamem v Excelu není každá tabulka. Tabulka typu seznam musí splňovat určité podmínky a na ty se v této kapitole podíváme. Celou kapitolou nás bude provázet následující tabulka popisující objemy prodejů několika fiktivních firem, viz Obrázek 2–1. Pro tuto kapitolu ji naleznete ve cvičebnici, tedy v sešitu 2_Seznamy.xlsx. Ale setkat se s ní budete moci také v jiných kapitolách. Obrázek 2–1 Tabulka typu seznam Zdroj: autor (2022) 6 Kapitola 2 2023 V. Novák 2.1 Co je to seznam Tabulka typu seznam v Excelu má následující vlastnosti: • V prvním řádku seznamu musí být názvy polí (sloupců), název pole musí být v jedné buňce, pro podrobnější popis polí použijte raději komentář (karta REVIZE). • Názvy polí by neměly být duplicitní. Nástroje jako strukturované nebo kontingenční tabulky provádějí výpočty vždy nad celými sloupci, místo adres buněk ve vzorcích využívají názvy sloupců, proto je nutné, aby názvy sloupců byly jedinečné. • V dalších řádcích tabulky jsou jednotlivé položky seznamu. • Na jednom listu by měl být pouze jeden seznam, ten může začínat v kterékoliv buňce listu. • V seznamu nesmí být prázdné řádky: Pro práci se seznamem není nutné celý seznam vybírat, stačí jen do něj kliknout a Excel si jej vybere sám jako souvislou oblast dat okolo aktivní buňky, proto v seznamu nesmí být prázdné řádky. • V jednom poli (sloupci) musí být data pouze jednoho datového typu. Pro zajištění jednotného datového typu v celém sloupci je možno použít ověření dat, viz později. • Pomocná data (např. pro rozšířenou filtraci) umístěte nad seznamem. Pokud by totiž pomocná data byla umístěna vedle seznamu, a ne nad ním, v případě filtrování seznamu, kdy Excel skrývá celé řádky listu, jež filtru nevyhovují, by došlo také ke skrytí pomocných dat. • V seznamu nesmí být sloučené buňky. Takže tabulka na Obrázku 2-1 seznamem je, ale tabulka na Obrázku 2-2 seznamem není. Obrázek 2–2 Tabulka Excelu, která není seznamem Zdroj: autor (2022) Tabulka na Obrázku 2-2 seznamem není jednak proto, že záhlaví je tvořeno dvěma řádky, ale hlavně proto, že toto záhlaví neobsahuje názvy sloupců, ale hodnoty, které Práce se seznamy v Excelu 7 Analýza dat v Microsoft Excelu chceme analyzovat. Tyto hodnoty patří do sloupců, a ne do záhlaví. Takovouto tabulku není možné použít jako zdroj dat kontingenční tabulky, která vypočítá např. celkové prodeje za jednotlivé produkty. Aby data této tabulky mohla být analyzována pomocí kontingenční tabulky, musela by být transformována do tabulky typu seznam (viz Obrázek 2–3) pomocí nástroje Power Query (viz poslední kapitola). Obrázek 2–3 Transformovaná tabulka (necelá) z tabulky obrázku 2-2 do seznamu Zdroj: autor (2022) Z takovéto tabulky pak není žádný problém pomocí kontingenční tabulky nebo jinými nástroji spočítat např. celkové prodeje jednotlivých produktů, viz Obrázek 2–4. Obrázek 2–4 Kontingenční tabulka vytvořená na základě seznamu z Obrázku 2-3 Zdroj: autor (2022) 8 Kapitola 2 2023 V. Novák Jak je vidět z předchozího příkladu, tabulky typu seznam jsou v Excelu velice důležité a my se je naučíme nejen zpracovávat pomocí různých nástrojů Excelu, ale naučíme se je také vytvářet transformací z tabulek, které nesplňují vlastnosti pro tabulky typu seznam. V dalších podkapitolách se podíváme na základní nástroje, které umožňují zpracování seznamů, jako je řazení nebo filtrování. 2.2 Řazení seznamů Řazení seznamu znamená změnu pořadí celých řádků seznamu podle hodnot jednoho sloupce, případně několika sloupců. Pro řazení seznamu nevybírejte celý seznam, jen označte libovolnou buňku seznamu. V případě, že by byla vybrána pouze část seznamu, Excel seřadí jen tuto část seznamu, a ne celý seznam, a to většinou nechcete. 2.2.1 Řazení seznamu podle jednoho sloupce Pokud má být seznam seřazen podle jednoho sloupce, klikněte do tohoto sloupce, na kartě DATA ve skupině voleb SEŘADIT A FILTROVAT zvolte SEŘADIT OD NEJMENŠÍHO K NEJVĚTŠÍMU nebo SEŘADIT OD NEJVĚTŠÍHO K NEJMENŠÍMU. Příklad 2–1 – seřaďte řádky tabulky podle libovolného sloupce. Pokud ale vaše tabulka obsahuje pouze textová data, Excel nerozpozná, že tabulka obsahuje záhlaví, a záhlaví zamíchá mezi řádky dat. V tomto případě je nutno pro řazení zvolit způsob pomocí dialogového okna SEŘADIT, viz následující kapitola. 2.2.2 Řazení seznamu podle více sloupců Pokud má být seznam seřazen víceúrovňově podle více sloupců, klikněte kamkoliv do seznamu a na kartě DATA ve skupině voleb SEŘADIT A FILTROVAT zvolte SEŘADIT. Obrázek 2–5 Dialogové okno Seřadit Zdroj: autor (2022) V dialogovém okně SEŘADIT, viz Obrázek 2–5, je možné pomocí tlačítek nové úrovně řazení přidávat nebo stávající zase odstraňovat, případně měnit jejich prioritu. Práce se seznamy v Excelu 9 Analýza dat v Microsoft Excelu Neméně důležité je také zaškrtávátko DATA OBSAHUJÍ ZÁHLAVÍ pro případ, kdy Excel automaticky nedetekuje, že tabulka obsahuje záhlaví. To se stává zejména v případech, kdy tabulka obsahuje pouze data typu text, a proto Excelu záhlaví tabulky s daty splývá. V případě, že jsou sloupce formátovány pomocí barev písma nebo buňky, případně je v něm použito podmíněné formátování ve formě ikon, je i podle těchto formátů možné tabulku seřadit. Jen je nutné v seznamu ŘAZENÍ místo možnosti HODNOTY BUNĚK zvolit jednu z možností Barva buňky, Barva písma nebo Ikona podmíněného formátování a poté vybrat hledanou barvu nebo ikonu. 2.2.3 Řazení seznamu podle vlastního pořadí Výchozí řazení nastavené v dialogovém okně SEŘADIT je vždy vzestupné, tedy od nejmenšího k největšímu. Pomocí roletky POŘADÍ lze snadno změnit na řazení sestupné, tedy od největšího k nejmenšímu. Pokud si ale přejete použít vlastní pořadí, je nutné si vytvořit vlastní seznam (pozor, neplést se seznamy, o kterých v této knize mluvíme) určující pořadí dat a ten pak použít pro řazení tabulky. Vlastní seznamy je možno vytvořit pomocí karty SOUBOR – MOŽNOSTI – UPŘESNIT – v dolní části okna nejděte tlačítko UPRAVIT VLASTNÍ SEZNAMY. Otevře se dialogové okno VLASTNÍ SEZNAMY, viz Obrázek 2–6. V dialogovém okně VLASTNÍ SEZNAMY v seznamu VLASTNÍ SEZNAMY zvolte položku Nový seznam a do pole POLOŽKY SEZNAMU napište tyto položky přesně v tom pořadí, v jakém mají být použity pro řazení tabulky. Nový seznam přidáte mezi ostatní vlastní seznamy tlačítkem PŘIDAT. Odstranit jej je možné zase tlačítkem ODSTRANIT. Pokud již máte seznam položek v požadovaném pořadí připraven v buňkách listu, není nutné je v dialogu VLASTNÍ SEZNAMY zapisovat, ale je možné je hromadně naimportovat do seznamu vlastních seznamů pomocí tlačítka IMPORTOVAT. Obrázek 2–6 Dialogové okno Vlastní seznamy Zdroj: autor (2022) 10 Kapitola 2 2023 V. Novák Jakmile máte vlastní seznam vytvořen, pro seřazení tabulky jej snadno použijete v dialogovém okně SEŘADIT v seznamu POŘADÍ, vyberte VLASTNÍ SEZNAM. Příklad 2–2 – seřaďte řádky tabulky na listu Řazení podle firem v pořadí podle obrázku 2-6. Vytvořený vlastní seznam firem poté zase odstraňte. Pro úplnost je nutno dodat, že vlastní seznamy se nevyužívají jen pro řazení tabulek, ale využívají se zejména pro tvorbu vlastních řad. Stačí do buňky napsat libovolnou položku vlastního seznamu a vyplnit ji vyplňovacím úchytem buňky (pravý dolní roh buňky) do dalších buněk. 2.3 Filtrování seznamů Cílem filtrování je vybrat z řádků seznamu jen ty řádky, které vyhovují zadaným kritériím výběru. Řádky, které těmto kritériím nevyhovují, budou skryty. Excel poskytuje dva způsoby filtrovaní: • Automatický filtr – umožňuje základní způsoby filtrování, se kterými si ale v 99 % případů vystačíte. Častým omezením automatického filtru je např. spojení několika kritérií výběru vztahem logického součtu (NEBO – jedno nebo druhé kritérium). Automatický filtr umožňuje spojení několika kritérií výběru pouze vztahem logického součinu (A – jedno kritérium a zároveň druhé kritérium). • Rozšířený filtr – kromě pokročilých způsobů filtrování (např. využití logického součtu při spojení několika filtrovacích podmínek) umožňuje také vyfiltrovaná data zkopírovat do jiné oblasti, vynechat duplicitní řádky atd. 2.3.1 Automatický filtr Pokud chcete použít automatický filtr, vyberte pouze jednu buňku tabulky, kterou chcete filtrovat, a filtrována bude celá tabulka. Pokud byste označili vice buněk tabulky, pak jen označené buňky budou filtrovány. Filtrování zapnete na kartě DATA tlačítkem FILTR. V záhlaví tabulky se zobrazí filtrovací roletky. U pole, podle kterého chcete filtrovat, klikněte na roletku automatického filtru (viz Obrázek 2–7) a vyberte jednu z následujících možností: • Pokud chcete filtrovat podle jedné nebo několika konkrétních hodnot, vyberte je pomocí zaškrtávátek. • Pokud byste ale chtěli vybrat souvislé rozsahy hodnot, např. všechny hodnoty menší než něco nebo hodnoty v nějakém intervalu, je možné používat speciální filtrovací operátory, které se ale liší podle datového typu sloupce. Číselné sloupce nabízejí FILTRY ČÍSEL, textové sloupce nabízejí FILTRY TEXTU a sloupce s daty nabízejí FILTRY KALENDÁŘNÍCH DAT. • Podobně jako u řazení lze i pro filtrování použít také barvy buňky, písma nebo ikony podmíněného formátování použité ve sloupcích tabulky. Ty jsou dostupné v seznamu FILTROVAT PODLE BARVY. Práce se seznamy v Excelu 11 Analýza dat v Microsoft Excelu Obrázek 2–7 Příklad použití automatického filtru Zdroj: autor (2022) Filtrací se v seznamu zobrazí pouze řádky, které splnily kritéria výběru, ostatní řádky jsou skryty. Ve stavovém řádku Excelu je uveden původní počet řádků a počet vyfiltrovaných řádků. Dalším filtrováním dále omezíte vybrané záznamy. Pole použitá pro filtrování jsou označena v roletce automatického filtru. Filtr zrušíte na kartě DATA tlačítkem VYMAZAT, případně nabídkou VYMAZAT v roletce automatického filtru. Automatický filtr zcela vypnete na kartě DATA opětovným klikem na tlačítko FILTR. Nejen že se vymaže filtr z tabulky, ale zmizí i roletky automatického filtru. Pokud použijete více kritérií na více sloupcích, platí mezi nimi vždy vztah logického součinu (platí "a zároveň"). Pokud požadujete logický součet (platí "a nebo"), musíte použít rozšířený filtr (viz dále). Příklad 2–3 – vyzkoušejte na listu Automatický filtr pomocí automatického filtru tabulku filtrovat podle jednotlivých hodnot i podle intervalu hodnot. Výhodou filtrování je nejen možnost vidět pouze vybrané řádky tabulky, ale také nad těmito vyfiltrovanými řádky něco spočítat. Tato kniha se bude výpočty zabývat v následujících kapitolách. Přesto by na tomto místě jedna funkce již měla být zmíněna. Tou funkcí je funkce SUBTOTAL. Na rozdíl od základních funkcí, jako je SUMA nebo PRŮMĚR, funkce SUBTOTAL počítá s filtrem, takže výpočet provede jen z vyfiltrovaných řádků tabulky. Tuto schopnost žádná jiná funkce nemá. Např. funkce SUMA ve filtrované tabulce provede součet i z hodnot buněk skrytých filtrem. Funkce SUBTOTAL vrátí souhrn dat v seznamu a má následující syntaxi: SUBTOTAL(konstanta funkce;odkaz1;[odkaz2];...) 18 Kapitola 2 2023 V. Novák K odstranění ověření dat z označených buněk slouží v dialogovém okně OVĚŘENÍ DAT tlačítko VYMAZAT VŠE. To ale pouze vyresetuje nastavení dialogového okna do výchozího nastavení, takže vše je ještě nutno potvrdit tlačítkem OK. Pokud byste ale chtěli odstranit ověření dat ze všech buněk listu, musíte označit všechny buňky listu a kliknout na tlačítko OVĚŘENÍ DAT. Pokud je v označené oblasti více různých typů ověření dat, Excel zobrazí místo dialogového okna OVĚŘENÍ DAT hlášení, ve kterém se dotáže, zda chcete opravdu vymazat všechna ověření, viz Obrázek 2–14. Obrázek 2–14 Dotaz Excelu na výmaz více různých typů ověření dat v označené oblasti Zdroj: autor (2022) Pokud toto hlášení potvrdíte, Excel zobrazí vyresetované dialogové okno OVĚŘENÍ DAT, které je nutné také potvrdit. Příklad 2–6 – nastavte ve sloupci Objem na listu Ověření dat ověření dat tak, aby do tohoto sloupce bylo možné zadávat pouze celá kladná čísla. Excel bohužel zpětně neověřuje již zadané hodnoty, ověřuje pouze nově zadávané hodnoty. Jediným způsobem, jak zjistit, jestli v již zadaných ověřovaných datech existují nějaké neplatné hodnoty, je nechat si neplatné hodnoty zakroužkovat. Tato možnost ZAKROUŽKOVAT NEPLATNÁ DAT je schována v dolní polovině tlačítka OVĚŘENÍ DAT a nastavuje se pro celý list, takže není nutné nic označovat. Na stejném místě je schována také možnost VYMAZAT KROUŽKY OVĚŘENÍ. 2.4.2 Ověření dat typu seznam Jedním ze způsobů, jak ověřovat data, je nabídnout seznam možných hodnot s tím, že každá jiná hodnota bude neplatná. Pokud chceme využít tuto možnost, je nutno v dialogovém okně OVĚŘENÍ DAT zvolit v roletce POVOLIT hodnotu Seznam. V buňkách, ve kterých tento způsob ověření dat nastavíte, se objeví roletky s definovaným seznamem hodnot, ze kterých uživatel může vybírat. Hodnoty do seznamu je možné zadat dvěma způsoby. Buď tyto hodnoty zapíšete přímo do pole ZDROJ a oddělíte je středníkem (bez mezer) nebo tyto hodnoty zadáte do buněk nejlépe pod sebe a v poli ZDROJ se na tyto buňky pouze odkážete. Pokud budete pro zdrojové hodnoty seznamů využívat číselníky zapsané v buňkách, je dobré tyto číselníky zadat do speciálních listů, které nakonec skryjete. Příklad 2–7 – ve sloupci Zaplaceno na listu Ověření dat vytvořte ověření dat typu seznam s hodnotami ano, ne v seznamu, viz Obrázek 2–15. Práce se seznamy v Excelu 19 Analýza dat v Microsoft Excelu Obrázek 2–15 Řešení pro Příklad 2–7 Zdroj: autor (2022) 2.5 Odebrání duplicit Když máte dlouhé seznamy dat, často se v jednotlivých sloupcích opakuje omezený seznam hodnot. Rádi byste v takovém sloupci vytvořili ověření dat typu seznam a potřebujete pro toto ověření dat seznam jedinečných hodnot daného sloupce a nechce se vám jej vytvářet ručně. V tuto chvíli se vám bude hodit nástroj Odebrat duplicity. Pokud chcete odebrat duplicity v nějakém sloupci tabulky, případně ve více sloupcích, nemusíte celou tabulku označovat, stačí jen do této tabulky kliknout. Poté zvolte na kartě DATA tlačítko ODEBRAT DUPLICITY. V dialogovém okně ODEBRAT DUPLICITY, viz Obrázek 2–16, vyberte sloupec nebo sloupce, ve kterých chcete odebrat duplicity. Ve vybraném sloupci tedy budou odebrány duplicity, to znamená, že budou ponechány pouze jedinečné hodnoty a ostatní řádky tabulky budou odstraněny a s nimi i hodnoty v ostatních sloupcích, které nebyly vybrány. 20 Kapitola 2 2023 V. Novák Obrázek 2–16 Dialogové okno Odebrat duplicity Zdroj: autor (2022) Pokud v dialogové okně vyberete více sloupců, Excel ponechá pouze jedinečné kombinace hodnot v těchto sloupcích, ostatní řádky odstraní spolu s hodnotami ostatních nevybraných sloupců. Pokud byste chtěli odebrat duplicity pouze z části tabulky, je nutné tuto část nejdříve vybrat a až poté zvolit na kartě DATA tlačítko ODEBRAT DUPLICITY. Může se jednat o váš omyl, protože s částí tabulky se pracuje zřídka, a tak se vás Excel nejdříve dotáže, zda nechcete pracovat s celou tabulkou pomocí dialogového okna UPOZORNĚNÍ NA ODEBRÁNÍ DUPLICIT, viz Obrázek 2–17. Obrázek 2–17 Dialogové okno Upozornění na odebrání duplicity Zdroj: autor (2022) Práce se seznamy v Excelu 21 Analýza dat v Microsoft Excelu Příklad 2–8 – ve sloupci Firma na listu Ověření dat vytvořte ověření dat typu seznam se seznamem jedinečných názvů firem ze sloupce Firma seřazenými podle abecedy, viz Obrázek 2–18. Obrázek 2–18 Řešení pro Příklad 2–8 Zdroj: autor (2022) Řešení pro Příklad 2–8: vytvořte nový list Firmy a zkopírujte na něj celý sloupec Firma ze seznamu na listu Ověření dat, včetně záhlaví. Umístěte do sloupce kurzor a odeberte duplicity pomocí tlačítka ODEBRAT DUPLICITY na kartě DATA. Výsledné hodnoty ještě seřaďte podle abecedy vzestupně pomocí tlačítka SEŘADIT na kartě DATA. Vraťte se zpět na list Ověření dat, vyberte všechny hodnoty ve sloupci Firma (tedy bez záhlaví) a pro tyto hodnoty nastavte ověření dat typu seznam pomocí tlačítka OVĚŘENÍ DAT na kartě DATA. 2.6 Souhrn Nástroj Souhrn je jedním z nástrojů, který umožňuje vytvářet agregační výpočty z tabulek typu seznam, jen žije trochu ve stínu kontingenčních tabulek Excelu, protože ačkoliv tyto nástroje v principu fungují podobně, kontingenční tabulky se používají jednodušeji a jejich možnosti jsou mnohem širší. O kontingenčních tabulkách si povíme v kapitole 5. Mějme známou tabulku, tentokrát na listu Souhrn. Přáli bychom si spočítat celkové objemy za jednotlivé firmy. Nástroj souhrn je schopen při každé změně hodnoty v jednom sloupci spočítat nějaký agregační výpočet z hodnot v jiném sloupci. Z toho vyplývá, že pokud chceme spočítat celkové objemy za jednotlivé firmy, musíme tabulku podle sloupce Firma seřadit. Pak je již možné otevřít dialogové okno SOUHRNY pomocí tlačítka SOUHRN na kartě DATA, viz Obrázek 2–19. 22 Kapitola 2 2023 V. Novák Obrázek 2–19 Dialogové okno Souhrny a výsledek použití nástroje Souhrn Zdroj: autor (2022) Nastavení dialogového okna se podobá psaní normální věty, tedy v našem případě požadujeme U KAŽDÉ ZMĚNY VE SLOUPCI Firma POUŽÍT FUNKCI Součet a tento součet PŘIDAT SOUHRN DO SLOUPCE Objem a pak už jen potvrdit dialogové okno. Pokud byste do existujícího souhrnu chtěli přidat další souhrn nad dalším sloupcem, nezapomeňte zrušit zaškrtnutí NAHRADIT AKTUÁLNÍ SOUHRNY. Jak je vidět z výsledné tabulky, nástroj Souhrn nejen spočítá celkový objem pro každou firmu, ale pomocí tlačítek úrovní sbalení na levé straně tabulky dokáže data zobrazit na různé úrovni detailu s tím, že tlačítko 1 zobrazí pouze celkový souhrn, kdežto nejvyšší číslo zobrazí data v maximálním detailu. Souhrny lze odstranit tlačítkem ODEBRAT VŠE v dialogovém okně SOUHRNY. Příklad 2–9 – na listu Souhrn spočítejte celkové Objemy za jednotlivé Výrobky, viz Obrázek 2–20. Práce se seznamy v Excelu 23 Analýza dat v Microsoft Excelu Obrázek 2–20 Řešení pro Příklad 2–9 Zdroj: autor (2022) Řešení pro Příklad 2–9 – nejdříve je nutné tabulku seřadit podle sloupce Výrobek a poté vytvořit souhrn, viz Obrázek 2–20. 2.7 Formátování seznamů Ačkoliv tato kniha není o základech Excelu, nelze zde nezmínit formátování buněk, zejména pak formátování čísel s využitím vlastních formátů, protože to velmi přispívá k čitelnosti číselných hodnot v seznamech. A znalost formátování čísel pak také později využijeme při formátování hodnot kontingenčních tabulek. Kromě formátování čísel se jinými způsoby formátování, jako je např. ohraničení nebo výplň buněk, v této knize nebudeme zabývat. Jednak proto, že to opravdu patří mezi základy Excelu a na to v této knize není prostor, a jednak proto, že se nám takové použití formátování bude úspěšně dařit nahrazovat styly strukturovaných nebo kontingenčních tabulek, případně dynamickým formátování (viz později). A ačkoliv se může zdát, že např. tlačítko FORMÁTOVAT JAKO TABULKU na kartě DOMŮ může patřit do této kapitoly, když obsahuje slovo "formátovat", patří až do 4. kapitoly věnované strukturovaným tabulkám. 2.7.1 Formátování čísel Mezi základní znalosti Excelu patří také znalost, že číslo po zadání do buňky je zarovnáno na pravou stranu buňky, pokud vleze do šířky sloupce, není nijak formátováno, pokud do šířky sloupce nevleze, Excel se jej snaží naformátovat tak, aby do šířky sloupce vlezlo, nejčastěji použitím tzv. matematického formátu. Pokud je sloupec tak úzký, že do něj číslo nevleze v žádném možném formátu, Excel místo čísla buňku vyplní znaky #. Takže pokud vidíte buňku vyplněnou znaky #, není to žádná chyba, ale je to jen úzký sloupec, který stačí rozšířit. 24 Kapitola 2 2023 V. Novák Další základní znalostí Excelu týkající se čísel by mohlo být, že i data si Excel ukládá jako čísla, a to pořadová od 1.1.1900, kde celá část čísla je datum a jeho desetinná část je čas. Takže pokud do buňky napíšete číslo 1,5, po naformátování na datum a čas vidíte 1.1.1900 12:00 (12:00 je to proto, že jedna je jeden den, takže půl je polovina dne, tedy 12:00). Tuto znalost je nutné mít proto, že některé výsledky operací nad daty jsou číselné, což si Excel nemyslí a tvrdošíjně vám výsledek formátuje jako datum, takže pak je nutno výsledek naformátovat jako číslo ručně. Základní možnosti formátování čísel jsou obsaženy v sekci ČÍSLO na kartě DOMŮ, kde spouštěčem v této sekci je možné otevřít také dialogové okno FORMÁT BUNĚK, viz Obrázek 2–21. Obrázek 2–21 Dialogové okno Formát buněk Zdroj: autor (2022) Znalost jednotlivých druhů formátů čísel patří k základním znalostem Excelu a už z názvů těchto druhů vyplývá, pro jaké typy čísel se používají. My se zaměříme na poslední druh v seznamu druhů číselných formátů, tj. na druh Vlastní. Druh Vlastní se používá v případě, že vám žádný z ostatních druhů číselných formátů nevyhovuje. Obecně vlastní formát může obsahovat až 4 části kódu formátu oddělené středníky, který se zapisuje do pole TYP: <KLADNÉ>;<ZÁPORNÉ>;<NULA>;<TEXT> Práce se seznamy v Excelu 25 Analýza dat v Microsoft Excelu Např.: # ##0,00" kg";[Červená]# ##0,00" kg";"Nic";"Zadán text" Pokud je uvedena pouze jedna část vlastního formátu, použije se na všechny typy čísel, tedy kladná čísla, záporná čísla a nuly. Pokud použijete dvě části vlastního formátu, pak první část se použije na kladná čísla a nuly a druhá část se použije na záporná čísla. Zajímavou alternativou vlastního formátu jsou tři středníky ;;; (tedy vynechat formáty pro všechny typy čísel), to pak znamená, že skrýváte hodnoty všech typů čísel. Vynecháním jedné části formátu lze samozřejmě skrýt jen hodnoty jednoho typu čísel. Podstatné pro pochopení vlastních formátů je pochopit význam jednotlivých znaků kódu vlastního formátu, ze kterých se vlastní formát skládá. Mějme např. následující neformátovaná čísla, která se budeme snažit formátovat použitím vlastních číselných formátů, viz Tabulka 2-2. Tabulka 2-2 Vlastní formát čísla Znak kódu Význam Příklad Aplikace 0 Zobrazí pouze celou část čísla. Zobraz pouze celou část čísla. 0,00 Použití více nul znamená počet zobrazených znaků (zde dvě desetinná místa), pokud je znaků více, bude číslo zaokrouhleno, pokud je znaků méně, budou doplněny nuly. Zobraz povinně dvě desetinná místa. 0 000,00 Mezera mezi tisíci znamená oddělovač tisíců mezerou. Nula zase určuje povinnou číslici, takže čísla menší než tisíc mají doplněny nuly zleva. Zobraz mezeru mezi tisíci s doplňováním nul zleva. # ##0,00 Znak # určuje nepovinnou číslici. Používá se pro oddělení tisíců mezerou bez toho, aby se k číslu doplňovaly nuly zleva. Zobraz mezeru mezi tisíci bez doplňování nul zleva. 26 Kapitola 2 2023 V. Novák [červená] Barva písma se zadává do hranatých závorek. Excel zná názvem jen základní barvy barevných modelů RGB a CMYK, ostatní barvy mají svůj speciální kód (viz Google). Zobraz čísla zaokrouhlená na dvě desetinná čísla, záporná čísla červeně bez znaménka mínus. Znaménko mínus u záporných čísel se doplní znakem mínus před prvním znakem #. "text" Text zapsaný v uvozovkách je zobrazen u čísla. Používá se zejména k zápisu jednotek před nebo za číslem. Pokud v kódu vlastního formátu není znak 0 nebo #, číslo vůbec není zobrazeno. Zobraz čísla zaokrouhlená na dvě desetinná místa s jednotkou ks (mezera je součástí jednotky). @ Zástupný symbol textu v buňce. Zobraz pouze texty. Pro vlastní formátování dat se zase používají zástupné symboly r m d h m s (rok, měsíc, den, hodina, minuta, sekunda) a záleží na počtu těchto znaků, jak bude daná část data nebo času zobrazena. Takže např. "d" zobrazí den data jednou číslicí (příp. dvěma), naopak "dddd" zobrazí den data jako název dne týdne. Vyzkoušejte sami různé kombinace těchto znaků. Příklad 2–10: naformátujte hodnoty ve sloupci Objem na listu Formátování čísel podle vzoru (oddělovač tisíců mezerou, u všech čísel jednotka "kg", dvě místa desetinná pouze u kladných čísel, záporná čísla červeně se znaménkem mínus, nula bude vynechána, text bude zeleně a doplní se k němu "je text"), viz Obrázek 2–22. Práce se seznamy v Excelu 27 Analýza dat v Microsoft Excelu Obrázek 2–22 Řešení pro Příklad 2–10 Zdroj: autor (2022) 2.7.2 Podmíněné formátování Dá se říci, že při vlastním formátování čísel lze použít jakousi jednoduchou podmínku, protože čísla lze formátovat jedním způsobem, pokud jsou kladná, a zase třeba jiným způsobem, pokud jsou čísla záporná. Nebylo zde ale možné použít jiné podmínky než větší nebo menší než nula, nebylo ani možné použít barevné výplně buněk a jiné formáty. Takové pokročilé podmíněné formátování umožňuje jen nástroj Podmíněné formátování. Stejně jako u běžného formátování, pokud chcete některé buňky formátovat podmíněně, musíte je nejdříve označit a poté zvolit na kartě DOMŮ tlačítko PODMÍNĚNÉ FORMÁTOVÁNÍ. Otevře se seznam možných pravidel podmíněného formátování: • PRAVIDLA ZVÝRAZNĚNÍ BUNĚK – podmínka je nejčastěji dána pomocí relačních operátorů, jako jsou větší než nebo menší než, ale jsou zde i jiné možnosti. • PRAVIDLA PRO NEJNIŽŠÍ ČI NEJVYŠŠÍ HODNOTY – formátuje prvních nebo posledních X položek seřazených podle velikost. • DATOVÉ PRUHY – doplní do buňky datový pruh o velikosti, která je dána hodnotou uvedenou v buňce. • BAREVNÉ ŠKÁLY – naformátuje výplň buňky barvou podle velikosti hodnoty uvedené v buňce. 34 Kapitola 3 2023 V. Novák Příklad 3–1 – na listu Základní statistické funkce spočítejte požadované výpočty, viz Obrázek 3–1. Obrázek 3–1 Požadované výpočty příkladu Zdroj: autor (2022) Řešení pro Příklad 3–1 – do buněk zadejte vzorce, viz Obrázek 3–2. Obrázek 3–2 Řešení pro Příklad 3–1 Zdroj: autor (2022) 3.2 Statistické funkce s podmínkou Statistické funkce zmíněné v předchozí kapitole (s výjimkou funkce POČET2) mají také své ekvivalenty pro zadávání podmínky, která má při agregaci platit. Názvy těchto funkcí se ale i v české verzi Excelu zadávají v angličtině. Název funkce s podmínkou je doplněn příponou IF nebo IFS s následujícím významem: • NázevFunkceIF – agregační funkce s jednou podmínkou (např. SUMIF). • NázevFunkceIFS – agregační funkce s jednou nebo více podmínkami (např. SUMIFS). V následující tabulce je uveden přehled základních statistických funkcí a jejich ekvivalentů pro zadávání podmínek, viz Tabulka 3-2. Analýza dat pomocí vzorců 35 Analýza dat v Microsoft Excelu Tabulka 3-2 Základní statistické funkce a jejich ekvivalenty s podmínkami Základní funkce Funkce s jednou podmínkou Funkce s jednou nebo více podmínkami SUMA SUMIF SUMIFS PRŮMĚR AVERAGEIF AVERAGEIFS POČET COUNTIF COUNTIFS MIN neexistuje MINIFS MAX neexistuje MAXIFS Funkce s jednou podmínkou mají následující syntaxi (s drobnou odlišností u funkce COUNTIF): NázevFunkceIF(oblast;kritérium;[součet]) • Oblast – povinný argument. Jedná se o oblast buněk vyhodnocovanou pomocí daného kritéria. • Kritérium – povinný argument. Jde o kritérium vyjádřené číslem, výrazem, odkazem na buňku, textem nebo funkcí, které definuje buňky, které se mají sečíst. Příklady kritérií mohou být např. 32, ">32", B5, "3?", "jablko*", "*~?", nebo DNES(). Textová kritéria nebo kritéria obsahující logické či matematické symboly musí být uzavřena v uvozovkách ("). U číselných kritérií nejsou uvozovky nutné. • Součet – nepovinný argument. Skutečné buňky, které budou sečteny, pokud chcete sečíst buňky neuvedené v argumentu oblast. Funkce s jednou nebo více podmínkami mají syntaxi lehce odlišnou od funkcí s jednou podmínkou (znovu s drobnou odlišností u funkce COUNTIFS): NázevFunkceIFS(oblast;oblast_kritérií1;kritéria1;[oblast_kritérií2 ;kritéria2];...) • Oblast – povinný argument. Oblast buněk k sečtení. • Oblast_kritérií1 – povinný argument. Oblast testovaná pomocí Kritéria1. • Kritéria1 – povinný argument. Kritéria, která určují buňky Oblast_kritérií1 k přidání. • Oblast_kritérií2, kritéria2 – nepovinné argumenty. Další oblasti a jejich přidružená kritéria. Můžete zadat až 127 dvojic oblast/kritéria. 36 Kapitola 3 2023 V. Novák Příklad 3–2 – na listu Statistické funkce s podmínkami spočítejte požadované výpočty, viz Obrázek 3–3. Obrázek 3–3 Požadované výpočty příkladu Zdroj: autor (2022) Řešení pro Příklad 3–2 – do buněk zadejte vzorce, viz Obrázek 3–4. Obrázek 3–4 Řešení pro Příklad 3–2 Zdroj: autor (2022) 3.3 Vzorce s využitím relativního a absolutního adresování Zatím jsme ve vzorcích používali pouze relativní adresy buněk. S tím si ale při řešení reálných problémů určitě nevystačíte, a proto je nutné seznámit se také s použitím absolutních a smíšených adres buněk ve vzorcích. Nejdříve si ukážeme obecné použití relativních a absolutních adres. Adresy ve vzorci mohou být: • relativní – je odkaz přizpůsobující se nové pozici, při kopírování buňky se vzorcem se relativní adresy mění, např. =A1, • absolutní – je odkaz směřující stále na stejné buňky, při kopírování buňky se vzorcem se absolutní adresy nemění, např. =$A$1 (znak $ fixuje číslo řádku i název sloupce), • smíšené – je odkaz přizpůsobující se nové pozici pouze v některém směru při kopírování, např. =$A1 – při kopírování se název sloupce nemění, ale číslo řádku ano; nebo =A$1 – při kopírování se název sloupce mění, ale číslo řádku ne. Znak $ do absolutní nebo smíšené adresy se nejsnáze zadává klávesou F4, a to nejlépe okamžitě po vložení adresy do vzorce. Příklad 3–3 – na listu Adresace spočítejte platby, když platba se spočítá jako (plocha * cena + poplatek + doprava) * (1 - sleva), viz Obrázek 3–5. Analýza dat pomocí vzorců 37 Analýza dat v Microsoft Excelu Obrázek 3–5 Výpočet plateb Zdroj: autor (2022) Řešení pro Příklad 3–3 – do buňky L5 zadejte následující vzorec =(B5*G5+$A$2+L$2)*(1- $Q5) a poté vzorec vyplňte do zbytku tabulky. Znalost relativních a absolutní adres se teď pokusíme aplikovat ve statistických výpočtech. Příklad 3–4 – na listu Stat. výpočty pomocí adresace spočítejte požadované výpočty, viz Obrázek 3–6. Obrázek 3–6 Požadované výpočty příkladu Zdroj: autor (2022) Řešení pro Příklad 3–4 – do buněk prvního řádku tabulky zadejte vzorce a ty poté vyplňte do zbytku sloupců, viz Obrázek 3–7. Obrázek 3–7 Řešení pro Příklad 3–4 Zdroj: autor (2022) 38 Kapitola 3 2023 V. Novák 3.3.1 Rozšířené filtrování s využitím vzorců Ve 2. kapitole jsme si v případě filtrů řekli, že pokud vám pro vyřešení vašeho úkolu nestačí automatický filtr, lze použít filtr rozšířený, jehož možnosti jsou mnohem širší. Avšak i rozšířený filtr bez použití vzorců má své limity a pro případ dosažení těchto limitů je nutné zahrnout do hry zadávání kritérií výběru pomocí vzorců. Představme si následující tabulku uvedenou na listu Rozšířený filtr, viz Obrázek 3– 8 (výsek z tabulky). Obrázek 3–8 Vstupní data řešeného příkladu Zdroj: autor (2022) Úkolem je vybrat pouze ty řádky, kde rozdíl hodnot ve sloupcích Norma a Naměřeno je absolutně menší nebo roven povolené odchylce. Protože podmínkou filtru je výpočet rozdílu hodnot dvou sloupců, nelze použít ani automatický filtr, ani rozšířený filtr bez použití vzorce. Jak se tedy používá rozšířený filtr, kde kritérium výběru je zadáno pomocí vzorce? Podobně jako u použití rozšířeného filtru bez vzorce je nutno použít oblast kritérií. Avšak oblast kritérií v tomto případě neobsahuje žádné záhlaví tabulky, v každém případě ale první řádek oblasti kritérií musí zůstat prázdný. Do druhého řádku se pak zadává kritérium výběru ve formě vzorce. Vzorec musí být logický výraz, jehož výsledkem je buď PRAVDA nebo NEPRAVDA. Výsledek vzorce je pak vypočítán pro každý řádek tabulky, a pokud pro daný řádek je výsledkem vzorce PRAVDA, je řádek filtrem vybrán do výsledného seznamu řádků. Je nutno ale dbát na dvě věci: • Protože je vzorec vypočítáván pro každý řádek vstupního seznamu, posunem v řádcích dochází ke změně relativních adres, což ne vždy je v pořádku, takže je v těchto vzorcích nutné důsledně přemýšlet nad použitím relativních a absolutních adres. • Jako oblast kritérií rozšířeného filtru se použije nejen buňka se vzorcem, ale i jedna buňka nad ním, která musí být prázdná. V našem demonstračním úkolu je tedy vzorec následující: =ABS(B6-C6)<=$C$4 Analýza dat pomocí vzorců 39 Analýza dat v Microsoft Excelu Rozšířený filtr je možné použít na kartě DATA tlačítkem UPŘESNIT a je zadán následujícím způsobem, viz Obrázek 3–9. Obrázek 3–9 Řešení řešeného příkladu Zdroj: autor (2022) Příklad 3–5 – na listu Rozšířený filtr vyfiltrujte v tabulce jen ty řádky, kde hodnota ve sloupci Naměřeno je větší než hodnota ve sloupci Norma. Řešení pro Příklad 3–5 – vzorec kritéria výběru je následující: =C6>B6 3.3.2 Ověření dat pomocí vzorců Mějme stejnou tabulku jako v předchozí kapitole, jen na listu Ověření dat. Chceme ve sloupci Naměřeno povolit pouze takové celočíselné hodnoty, aby rozdíl hodnot ve sloupcích Norma a Naměřeno nebyl absolutně větší než Povolená odchylka. Stejně jako u ověřování dat bez vzorců je nejdříve nutno označit buňky, které chceme ověřovat, v našem případě hodnoty ve sloupci Naměřeno, a pak použijeme tlačítko OVĚŘENÍ DAT na kartě DATA. V našem případě povolujeme pouze celá čísla v určitém rozsahu hodnot, minimum a maximum tohoto rozsahu ale nemůže být dáno absolutními hodnotami, protože se řádek od řádku liší. Proto je MINIMUM a MAXIMUM nutné zadat pomocí vzorce, viz Obrázek 3–10. 40 Kapitola 3 2023 V. Novák Obrázek 3–10 Řešení řešeného příkladu s povolením celého čísla Zdroj: autor (2022) Ověření pomocí vzorce funguje tak, že vzorec zadáváte pro levou horní buňku označeného bloku buněk a nastavení ověření se pak automaticky rozkopíruje do ostatních buněk označeného bloku buněk. Z toho důvodu je zase nutné významně dbát na rozlišování relativních a absolutních adres ve vzorcích. V našem řešení jsme ověření dat zvládli tím způsobem, že jsme povolili celá čísla a pomocí vzorců jsme dynamicky počítali krajní přípustné meze pro ověřované buňky. Pomocí vzorce jste tedy počítali celá čísla. Ověření dat pomocí vzorce lze ale vytvořit i jiným způsobem, a to pomocí vzorce, kde vzorec je logickým výrazem podobným jako v předchozí kapitole. V tom případě je však nutné v roletce POVOLIT zvolit hodnotu Vlastní, viz Obrázek 3–11. V případě, že výsledkem logického výraz je PRAVDA, hodnota je povolena, v případě, že výsledkem logického výrazu je NEPRAVDA, hodnota povolena není. Nepříjemné také je, že při zadávání funkcí do pole VZOREC vám nepomáhá žádný našeptávač podobně jako při zadávání funkce do vzorce v buňce, takže název funkce musíte zapsat správně bez pomoci (jen na velikosti znaků nezáleží). Analýza dat pomocí vzorců 41 Analýza dat v Microsoft Excelu Obrázek 3–11 Řešení řešeného příkladu s povolením Vlastní Zdroj: autor (2022) Příklad 3–6 – na listu Ověření dat zajistěte, aby do sloupce Naměřeno bylo možné zadat pouze hodnoty menší nebo rovny, než jsou ve sloupci Norma. Řešení pro Příklad 3–6 – řešení je možné dvěma způsoby, viz Obrázek 3–12. nebo 42 Kapitola 3 2023 V. Novák Obrázek 3–12 Možná řešení pro Příklad 3–6 Zdroj: autor (2022) 3.3.3 Podmíněné formátování pomocí vzorců Mějme ještě jednou stejnou tabulku jako v předchozí kapitole, jenom teď na listu Podmíněné formátování. Chceme zvýraznit červenou výplní ty celé řádky tabulky, kde rozdíl hodnot ve sloupcích Norma a Naměřeno je absolutně větší než Povolená odchylka. Stejně jako u podmíněného formátování bez použití vzorců je nejdříve nutno označit buňky, které chceme podmíněně formátovat. V našem případě to mají být celé řádky, takže musíme označit celou oblast dat (tedy celou tabulku bez záhlaví) a pak použijeme na kartě DOMŮ tlačítko PODMÍNĚNÉ FORMÁTOVÁNÍ – NOVÉ PRAVIDLO. V dialogovém okně NOVÉ PRAVIDLO FORMÁTOVÁNÍ vyberete typ pravidla Určit buňky k formátování pomocí vzorce. Následně nastavíme formát tlačítkem FORMÁT, v našem případě červenou barvu výplně, a na závěr zapíšeme vzorec do pole FORMÁTOVAT HODNOTY, PRO KTERÉ PLATÍ TENTO VZOREC, viz Obrázek 3–13. Analýza dat pomocí vzorců 43 Analýza dat v Microsoft Excelu Obrázek 3–13 Řešení řešeného příkladu Zdroj: autor (2022) Podobně jako v předchozích kapitolách musí být vzorec logickým výrazem: pokud jeho výsledkem v označené buňce je PRAVDA, pak buňka je naformátována, pokud jeho výsledkem v označené buňce je NEPRAVDA, pak označená buňka nebude naformátována. Zároveň vzorec vkládáte do levé horní buňky označené oblasti s tím, že vzorec se automaticky rozkopíruje do ostatních buněk označené oblasti, takže je znovu nutné dbát na používání relativních a absolutních adres ve vzorci. V našem případě má označená oblast více řádků i sloupců, takže je nutné používat i adresy smíšené. Příklad 3–7 – na listu Podmíněné formátování naformátujte zeleně buňky ve sloupci Naměřeno, kde hodnota ve sloupci Naměřeno je větší než hodnota ve sloupci Norma. Řešení pro Příklad 3–7 – řešení je možné dvěma způsoby, viz Obrázek 3–14. 50 Kapitola 3 2023 V. Novák • Zápisem – funguje podobně jako vkládání funkce do vzorce. Při zápisu názvu Excel nabízí všechny názvy funkcí a názvů začínající na zadaná písmena. Požadovaný název stačí jen vybrat klávesou TAB. • Tlačítko POUŽÍT VE VZORCI na kartě VZORCE obsahuje seznam všech názvů, stačí jen z něj požadovaný název vybrat myší. • Funkční klávesa F3 zobrazí dialogové okno VLOŽIT NÁZEV, ze kterého lze požadovaný název vložit do vzorce. Příklad 3–13 – na listu Definované názvy spočítejte platby s využitím definovaných názvů, když platba se spočítá jako (plocha * cena + poplatek + doprava) * (1 - sleva), viz Obrázek 3–24. Obrázek 3–24 Výpočet plateb Zdroj: autor (2022) Řešení pro Příklad 3–13 – nejdříve si definujte všechny potřebné názvy. Protože budeme vzorec vkládat maticově, označte výstupní oblast buněk, vložte vzorec podobný následujícímu vzorci =(Plocha*Cena+Poplatek+Doprava)*(1-Sleva) a maticově jej potvrďte. Znalost definovaných názvů se teď pokusíme aplikovat ve statistických výpočtech. Příklad 3–14 – na listu Stat. výpočty pomocí def. názvů spočítejte požadované výpočty, viz Obrázek 3–25. Obrázek 3–25 Požadované výpočty příkladu Zdroj: autor (2022) Analýza dat pomocí vzorců 51 Analýza dat v Microsoft Excelu Řešení pro Příklad 3–14 – v rámci urychlení práce si nejdříve pojmenujeme buňky všech sloupců podle záhlaví. Označte tedy celou tabulku a zvolte kartu VZORCE a tlačítko VYTVOŘIT Z VÝBĚRU. V dialogovém okně zaškrtněte pouze HORNÍ ŘÁDEK. Poté nejprve označte výstupní oblast ve sloupci I, tedy buňky I2:I5, zadejte vzorec a poté jej maticově potvrďte, viz Obrázek 3–26. Stejný postup opakujte i pro sloupec J. Obrázek 3–26 Řešení pro Příklad 3–14 Zdroj: autor (2022) 3.6 Databázové funkce V této kapitole jsme zatím prováděli statistické výpočty bez podmínek, případně s jednou nebo několika jednoduchými podmínkami, ty ale musely být pouze ve vztahu logického součinu (a zároveň). Pokud bychom ale chtěli při našich výpočtech pomocí vzorců aplikovat podmínky složitější, je nutné použít tzv. databázové funkce. Databázové funkce, podobně jako rozšířené filtrování, pro zadání podmínky využívají tzv. oblast kritérií, která v prvním řádku obsahuje stejné záhlaví tabulky, jako obsahuje tabulka s daty pro výpočty. V dalších řádcích pak obsahuje samotná kritéria výběru. Kritéria uvedená v jednom řádku platí A ZÁROVEŇ (logický součin), mezi různými řádky kritérií pak platí vztah NEBO (logický součet). Databázové funkce se poznají podle předpony D a v české lokalizaci Excelu jsou jejich názvy česky. Takže např. databázový ekvivalent funkce SUMA je jmenuje DSUMA nebo databázový ekvivalent funkce POČET se jmenuje DPOČET. Obecná syntaxe databázových funkcí vypadá následovně: DNázevFunkce(databáze, pole, kritéria) • Databáze – povinný argument. Oblast buněk, která tvoří seznam nebo databázi. První řádek seznamu obsahuje popisky sloupců. • Pole – povinný argument. Určuje, který sloupec je ve funkci používán. Zadejte popisek sloupce v uvozovkách, například "Stáří" či "Výnos", nebo číslo (bez uvozovek) představující umístění sloupce v seznamu: hodnota 1 představuje první sloupec, hodnota 2 druhý sloupec atd. • Kritéria – povinný argument. Oblast buněk, která obsahuje zadaná kritéria. Pro argument kritéria můžete použít libovolnou oblast, jež zahrnuje nejméně jeden popisek sloupce a nejméně jednu buňku pod popiskem sloupce určující podmínku sloupce. 52 Kapitola 3 2023 V. Novák Příklad 3–15 – na listu Databázové funkce vypočítejte pro zadaná kritéria požadované výpočty s výsledkem, viz Obrázek 3–27. Obrázek 3–27 Požadované výpočty a řešení pro Příklad 3–15 Zdroj: autor (2022) 3.7 Řešení příkladu z úvodní kapitoly Příklad četností jednotlivých čísel z úvodní kapitoly má pomocí vzorců několik podobných řešení. Všechna tato řešení předpokládají, že kromě naší tabulky se sloupcem Číslo máme ještě druhou tabulku, kde ve sloupci Číslo je seznam jedinečných hodnot 1 – 9 a do vedlejšího sloupce Četnost budeme dopočítávat různými způsoby jednotlivé četnosti, např. viz Obrázek 3–28. Obrázek 3–28 Připravená tabulka pro výpočet četností čísel Zdroj: autor (2022) Do první buňky sloupce Četnost pak zadáme patřičný vzorec. Vzorec se ale může lišit podle toho, jaký přístup k řešení zvolíme. Analýza dat pomocí vzorců 53 Analýza dat v Microsoft Excelu Řešení s využitím statistických funkcí s podmínkou a relativního a absolutního pozicování Do první buňky sloupce Četnost vložte následující vzorec a vyplňte jej do zbytku sloupce: =COUNTIF($A$2:$A$100;C2) Protože je vstupní oblast dat rozsáhlá, je vhodné adresy zadat pomocí klávesnice místo myší klikem do buňky A2 a pak stisknout CTRL+SHIFT+šipka dolů. Dbejte na správné použití relativních a absolutních adres. Řešení s využitím statistických funkcí s podmínkou a definovaných názvů Označovat rozsáhlé oblasti buněk do vzorců není nikdy příliš pohodlné, proto je mnohdy výhodné si tyto oblasti nazvat buď názvy definovanými, nebo tabulkovými. Zde použijeme definovaný název (tabulkový název až v další kapitole). Pojmenujte si oblast náhodných čísel, nejlépe pomocí záhlaví, tedy označte náhodná čísla včetně záhlaví a na kartě VZORCE zvolte tlačítko VYTVOŘIT Z VÝBĚRU. V dialogovém okně VYTVOŘIT NÁZVY Z VÝBĚRU ponechte, že chcete vytvořit název z horního řádku. Poté do první buňky sloupce Četnost vložte následující vzorec a vyplňte jej do zbytku sloupce: =COUNTIF(Číslo;C2) Výhodou je, že ani nemusíte řešit absolutní adresy. Zkontrolujte si, že výsledek je stejný jako u předchozího řešení. Řešení s využitím statistických funkcí s podmínkou, definovaných názvů a maticových vzorců Toto řešení bude podobné předchozímu, jen vzorec nebudeme vkládat pouze do první buňky sloupce Četnost, ale do všech buněk sloupce Četnost. Proto si nejdříve označte výstupní oblast sloupce Četnost a zadejte následující vzorec, který je nutno potvrdit maticově (tedy CTRL+SHIFT+ENTER): =COUNTIF(Číslo;C2:C10) Řešení s využitím statistických funkcí bez podmínky, definovaných názvů a maticových vzorců Ne všechny statistické funkce mají variantu s podmínkou, proto je dobré také ovládat způsob, jak vypočítat agregaci s podmínkou pomocí funkce bez podmínky. Pro nás tou základní funkcí tedy bude funkce POČET, kde k zadání podmínky využijeme funkci KDYŽ. Do první buňky sloupce Četnost vložte následující vzorec, který je nutno potvrdit maticově (tedy CTRL+SHIFT+ENTER) a vyplňte jej do zbytku sloupce: =POČET(KDYŽ(Číslo=C2;Číslo)) 55 Kapitola 4 Analýza dat pomocí strukturovaných tabulek Velkou nevýhodou běžných tabulek je, že pokud vypočítáváme nad jejich sloupci nějaké agregační výpočty ať pomocí vzorců, nebo třeba kontingenčních tabulek a zpětně do tabulky na její konec přidáme nějaké další řádky s daty, tato data nejsou zahrnuta do existujících výpočtů. Tento problém a mnoho dalších nám pomáhají řešit strukturované tabulky. V první části kapitoly budou vyjmenovány všechny výhody použití strukturovaných tabulek, ve druhé se pak zaměříme na použití strukturovaných odkazů v agregačních výpočtech. Strukturované tabulky v Excelu najdete pod názvem Tabulky, což je ale název dosti matoucí, protože slovo "tabulka" se v Excelu používá pro jakoukoliv tabulku, nejen tu strukturovanou. Někdy se ale v literatuře setkáte také s názvy Formátované tabulky nebo Chytré tabulky. Příklady k této kapitole si můžete vyzkoušet na tabulkách v sešitu 4_StrukturovaneTabulky.xlsx. 4.1 Strukturované tabulky Strukturované tabulky jsou tabulky, které se svými principy používání podobají tabulkám relačních databází. Ve výpočtech se místo adres buněk používají strukturované odkazy, tedy odkazy pomocí tabulkových názvů sloupců, které Excel vytvoří automaticky. Mezi strukturovanými tabulkami lze dokonce vytvářet relace pomocí primárních a cizích klíčů stejně jako v relačních databázích, čímž v Excelu vytvoříme tzv. datový model (viz později v kapitole 6). Strukturovanou tabulku lze vytvořit dvěma způsoby, vždy ale nejdříve umístěte kurzor do nachystané obyčejné tabulky, ze které se má strukturovaná tabulka vytvořit: • Karta DOMŮ – FORMÁTOVAT JAKO TABULKU – při vytváření je nutné zvolit styl tabulky, což je ale často zbytečný krok, proto je výhodnější použít následující možnost. 56 Kapitola 4 2023 V. Novák • Karta VLOŽENÍ – TABULKA – nová strukturovaná tabulka je formátována výchozím stylem, ten lze ale kdykoliv změnit. V obou případech je nutno v dialogovém okně VYTVOŘIT TABULKU zkontrolovat oblast dat, příp. zkontrolovat, zda tabulka obsahuje záhlaví, viz Obrázek 4–1. Pokud oblast dat obsahuje jen textová data, Excel automaticky nerozpozná, že tabulka obsahuje záhlaví a je nutné příslušné zaškrtávátko zaškrtnout ručně. Obrázek 4–1 Dialogové okno Vytvořit tabulku Zdroj: autor (2022) Při vytvoření strukturované tabulky je automaticky vytvořen tabulkový název s výchozí hodnotou TabulkaX, kde X je pořadové číslo tabulky a jeho součástí jsou automaticky pojmenované všechny sloupce podle záhlaví tabulky. V případě používání více tabulek mohou být výchozí názvy tabulek matoucí, proto je dobré po vytvoření každou tabulku ještě přejmenovat. Pojmenování tabulky provedete na kontextové kartě NÁVRH TABULKY v poli NÁZEV TABULKY. Strukturovanou tabulku lze převést zpět na obyčejnou tabulku pomocí kontextové karty NÁVRH TABULKY tlačítkem PŘEVÉST NA OBLAST. Definovaný formát si ale tabulka ponechá. Příklad 4–1 – z tabulky na listu Malá tabulka vytvořte strukturovanou tabulku a pojmenujte ji Firmy. Řešení pro Příklad 4–1 – klikněte do tabulky a na kartě VLOŽENÍ zvolte TABULKA. Na kontextové kartě návrh změňte NÁZEV TABULKY. V dalších podkapitolách se podíváme na jednotlivé výhody strukturovaných tabulek. Pokud bychom ale měli zmínit i některé nevýhody, pak je to zejména nemožnost využívat maticové vzorce nad daty strukturovaných tabulek. 4.1.1 Automatický formát tabulky Pokud pracujete ve strukturované tabulce, máte k dispozici kontextovou kartu NÁVRH TABULKY s dalšími funkcemi. Není nutno celou tabulku označovat, stačí do ní jen kliknout. Na kontextové kartě NÁVRH TABULKY nejvíce prostoru zabírá galerie STYLY TABULKY. Tato galerie obsahuje množství přednastavených stylů, jejichž volbou se celá Analýza dat pomocí strukturovaných tabulek 57 Analýza dat v Microsoft Excelu strukturovaná tabulka přeformátuje. Styly lze dále donastavit pomocí zaškrtávátek MOŽNOSTÍ STYLŮ TABULKY. Je zde ale schována také možnost vytvořit si nový styl pomocí nabídky NOVÝ STYL TABULKY, která otevře dialogové okno NOVÝ STYL TABULKY, viz Obrázek 4–2. Obrázek 4–2 Dialogové okno Nový styl tabulky Zdroj: autor (2022) Abychom ale vytvořili plnohodnotný styl strukturované tabulky, je nutno naformátovat mnoho jejích prvků. Proto je mnohdy vhodnější nový styl nevytvářet zcela od začátku, ale vytvořit jej duplikací existujícího stylu, který se nejvíce blíží našim požadavkům na nový styl. Duplikaci stylu provedete klikem pravým tlačítkem myši na existujícím stylu a volbou DUPLIKOVAT. V dialogovém okně UPRAVIT STYL TABULKY pak kromě názvu pro nový styl doplníte požadované formáty. Nový styl bude v galerii stylů umístěn do horní části zvané VLASTNÍ. Na rozdíl od standardních stylů, které nelze ani měnit ani odstraňovat, vlastní styly měnit i odstraňovat lze. Všechny možnosti najdete v kontextové nabídce daného stylu. Jakmile vlastní styl odstraníte, tabulka bude naformátována opět výchozím standardním stylem. 4.1.2 Automatický filtr a průřezy Po vytvoření strukturované tabulky je v tabulce automaticky zapnut automatický filtr (viz dříve). Pokud ale data nebudete chtít nikdy filtrovat, je možné automatický filtr standardně vypnout na kartě DATA, aniž by to mělo nějaký vliv na další funkčnost strukturovaných tabulek. 58 Kapitola 4 2023 V. Novák Pokud ale data chcete filtrovat ještě jednodušším a vizuálně zajímavějším způsobem, pak strukturované tabulky nabízejí tzv. průřezy, které u obyčejných tabulek používat nelze. Setkáme se s nimi znovu až u tabulek kontingenčních (viz později). Průřez je seznam jedinečných hodnot zvoleného sloupce, kde výběrem těchto hodnot dochází k filtrování tabulky. Průřez strukturované tabulce vložíte pomocí tlačítka VLOŽIT PRŮŘEZ na kontextové kartě NÁVRH TABULKY. V dialogovém okně VLOŽIT PRŮŘEZY je nutno vybrat, pro které sloupce chcete průřezy vytvořit, a po potvrzení dialogu jsou průřezy vytvořeny, viz Obrázek 4–3. Obrázek 4–3 Dialogové okno Vložit průřezy a ukázka průřezu Zdroj: autor (2022) Filtrování tabulky je pak v průřezu prováděno výběrem jednotlivých položek průřezu. Pokud chcete vybrat více než jednu položku průřezu, můžete označit souvislý blok položek pomocí klávesy SHIFT nebo nesouvislý blok položek pomocí klávesy CTRL. Jinou možností, jak provádět vícenásobný výběr položek, je použít tlačítko VÍCENÁSOBNÝ VÝBĚR průřezu. Pokud strukturovanou tabulku filtrujete pomocí několika průřezů, je mezi nimi používán vztah logického součinu. Výběrem všech položek nebo tlačítkem VYMAZAT FILTR průřezu dojde k vymazání filtru. Vymazat filtr je samozřejmě možné také standardně na kartě DATA. Průřez odstraníte jeho označením a prostým smazáním klávesou DEL. Přitom ale nedojde k vymazání použitého filtru. 4.1.3 Použití strukturovaných odkazů ve vzorcích a řádek souhrnů Při výpočtech z dat obyčejných tabulek se ve vzorcích používají odkazy pomocí adres buněk. Při výpočtech z dat tabulek strukturovaných se používají strukturované odkazy, Analýza dat pomocí strukturovaných tabulek 59 Analýza dat v Microsoft Excelu tedy tabulkové názvy zahrnující v sobě také názvy sloupců. Pokud výpočet provádíte přímo ve strukturované tabulce, není nutné název tabulky používat, stačí použít jen název sloupce. Při výpočtech mimo strukturovanou tabulku je název tabulky nutno použít. Příkladem výpočtu ve strukturované tabulce může být agregace hodnot jednoho sloupce umístěná pod daným sloupcem. Takovýto vzorec není nutné zapisovat ručně, ale stačí jen zaškrtnout zaškrtávátko ŘÁDEK SOUHRNŮ na kontextové kartě NÁVRH TABULKY. Příklad 4–2 – na listu Malá tabulka vypočítejte pod sloupcem Objem celkový objem, viz Obrázek 4–4. Obrázek 4–4 Požadovaný výpočet příkladu Zdroj: autor (2022) Řešení pro Příklad 4–2 – umístěte kurzor do strukturované tabulky a na kontextové kartě NÁVRH TABULKY zaškrtněte zaškrtávátko ŘÁDEK SOUHRNŮ. Pod tabulkou bude vytvořen nový řádek, kde si ve vybraném sloupci z roletky vyberete agregační funkci, která se má použít na data daného sloupce. Ve vytvořeném vzorci je vidět použití funkce SUBTOTAL, protože tabulka má zapnutý automatický filtr, ale zejména je zde vidět odkaz pomocí názvu sloupce uzavřeného v hranatých závorkách (strukturovaný odkaz) místo adres buněk. Řádek souhrnů je možné zrušit pouhým zrušením zaškrtnutí zaškrtávátka ŘÁDEK SOUHRNŮ. Pokud chceme provádět výpočet z dat strukturované tabulky mimo ni, musíme ve strukturovaném odkazu použít také název této strukturované tabulky. Následující tabulka ukazuje, na jaké části strukturované tabulky je možné odkazovat a jak, viz Tabulka 4–1. Tabulka 4–1 Nejčastější strukturované odkazy Strukturovaný odkaz Význam =Tabulka nebo = Tabulka[#Data] Odkaz na všechna data tabulky (bez záhlaví) 66 Kapitola 5 2023 V. Novák • Nejčastěji to bude ruční aktualizace pomocí kontextové karty ANALÝZA KONTINGENČNÍ TABULKY tlačítkem AKTUALIZOVAT. • Pokud byste ale chtěli aktualizaci kontingenční tabulky částečně automatizovat, je to možné při otevření sešitu obsahující kontingenční tabulku pomocí kontextové karty ANALÝZA KONTINGENČNÍ TABULKY tlačítkem MOŽNOSTI a dialogovém okně MOŽNOSTI KONTINGENČNÍ TABULKY na kartě DATA zaškrtnete AKTUALIZOVAT DATA PŘI OTEVŘENÍ SOUBORU. V případě, že zdrojem dat kontingenční tabulky je obyčejný seznam Excelu (ne strukturovaná tabulka), větší problém nastává v případě, kdy dojde k přidání nových řádků na konci seznamu. V tomto případě je nutné aktualizovat také oblast zdrojových dat pomocí kontextové karty ANALÝZA KONTINGENČNÍ TABULKY tlačítkem ZMĚNIT ZDROJ DAT. 5.1.3 Odstranění kontingenční tabulky Existující kontingenční tabulku lze odstranit několika způsoby: • Nejdříve kontingenční tabulku celou vyberte nejlépe pomocí klávesové zkratky CTRL+A a poté ji klávesou DELETE odstraňte. • Odstraňte celé sloupce nebo řádky obsahující kontingenční tabulku. • Další možností je odstranit celý list obsahující kontingenční tabulku. Jen si buďte u této operace vědomi, že odstranění listu je nevratná akce. Příklad 5–1 – na listu Seznam vytvořte kontingenční tabulky podle obrázku 5-1 a poté je zase odstraňte. 5.2 Výpočty v kontingenční tabulce Kontingenční tabulky existují proto, abyste v nich mohli počítat agregace hodnot (součty, průměry, počty atd.) ve sloupcích seznamů, tedy výpočty jsou primárním účelem těchto tabulek. Proto se na ně podíváme nejdříve. 5.2.1 Souhrn dat Jak už jsme dříve zjistili, výchozím výpočtem pro pole vložená do sekce HODNOTY je součet pro číselná pole, případně počet pro textová pole. Výpočetní operaci lze ale změnit. V roletce u pole vloženého v sekci HODNOTY k tomu slouží nabídka NASTAVENÍ POLÍ HODNOT. V dialogovém okně NASTAVENÍ POLÍ HODNOT na kartě SOUHRN DAT vyberte jiný typ výpočtu. To lze provést také mnohem rychleji v kontextovém menu kterékoliv buňky s hodnotou v nabídce SOUHRN DAT, viz Obrázek 5–4. Analýza dat pomocí kontingenčních tabulek a grafů 67 Analýza dat v Microsoft Excelu Obrázek 5–4 Dialogové okno Nastavení polí hodnot, karta Souhrn dat a kontextová nabídka buňky se souhrnem Zdroj: autor (2022) Příklad 5–2 – do nové kontingenční tabulky na nový list vypočítejte průměr a počet z hodnot sloupce Počet kusů za jednotlivé Prodejce, viz Obrázek 5–5. Obrázek 5–5 Řešení pro Příklad 5–2 Zdroj: autor (2022) Výše zmíněným způsobem zvolíte základní výpočetní operaci. Výpočet ale nemusí být zobrazen pouze prostou hodnotou, tuto hodnotu je také možné zobrazit jiným způsobem, např. procentem z celku. K tomuto typu výpočtu slouží v dialogovém okně NASTAVENÍ POLÍ HODNOT karta ZOBRAZIT HODNOTY JAKO, případně v kontextovém menu kterékoliv buňky s hodnotou je to nabídka ZOBRAZIT HODNOTY JAKO, viz Obrázek 5–6. 68 Kapitola 5 2023 V. Novák Obrázek 5–6 Dialogové okno Nastavení polí hodnot, karta Zobrazit hodnoty jako Zdroj: autor (2022) V nabídce možných zobrazení se nachází mnoho různých zobrazení, ale nejčastější z nich budou nejspíše následující: • Žádný výpočet – souhrn dat je zobrazen prostou hodnotou bez úprav. • % z celkového součtu – zobrazí hodnotu jako procento z celkového součtu ze všech hodnot. • % ze součtu sloupce – zobrazí hodnotu jako procento ze součtu hodnot pouze za daný sloupec. • % z … – zobrazí hodnotu jako procento dopočítané od jedné konkrétní položky, která má 100 %. • Rozdíl mezi … – zobrazí hodnotu jako rozdíl dopočítaný od jedné konkrétní položky, která tím pádem rozdíl nemá. • Pořadí od nejmenších po největší (od největších po nejmenší) – zobrazí pořadové číslo položky podle velikosti. Určitě si ale vyzkoušejte všechny možnosti, jejich význam je pochopitelný podle popisku nabídky. Kdo ví, kdy se vám některá bude hodit. Příklad 5–3 – do nové kontingenční tabulky na nový list vypočítejte procentuální podíly prodejů (Počet kusů) jednotlivých Prodejců, viz Obrázek 5–7. Analýza dat pomocí kontingenčních tabulek a grafů 69 Analýza dat v Microsoft Excelu Obrázek 5–7 Řešení pro Příklad 5–3 Zdroj: autor (2022) 5.2.2 Počítané pole Další možností, jak v kontingenční tabulce něco vypočítat, je z hodnot stávajících polí vypočítat pole nové. Jen si dejte pozor na to, jak počítané pole funguje. Např. při použití součinu dvou polí A a B není vypočítáno nové pole jako SUMA(A*B), ale jako SUMA(A)*SUMA(B). To tedy znamená, že pokud bychom chtěli v naší tabulce spočítat celkové obraty, není možné to vyřešit pomocí počítaného pole, protože celkový obrat je SUMA(Počet kusů * Cena / ks) a ne SUMA(Počet kusů) * SUMA(Cena / ks). Pokud tedy chcete vypočítat nové pole na základě hodnot pole stávajících, klikněte kamkoliv do kontingenční tabulky. Na kontextové kartě ANALÝZA KONTINGENČNÍ TABULKY ve skupině voleb VÝPOČTY zvolte tlačítko POLE, POLOŽKY A SADY – POČÍTANÉ POLE… V dialogovém okně VLOŽIT POČÍTANÉ POLE definujte NÁZEV pro nové pole a VZOREC, viz Obrázek 5–8. Pokud vzorec vychází z existujících polí, existující pole do vzorce vložíte tlačítkem VLOŽIT POLE nebo dvojklikem. Všimněte si syntaxe zápisu polí do vzorce, názvy polí se zapisují do apostrofů. Je to odlišné od použití sloupců ve strukturovaných tabulkách (viz dříve) nebo ve vzorcích jazyka DAX (viz později). Tlačítkem PŘIDAT pak nové počítané pole přidáte mezi ostatní pole kontingenční tabulky. 70 Kapitola 5 2023 V. Novák Obrázek 5–8 Dialogové okno Vložit počítané pole Zdroj: autor (2022) S počítaným polem se pracuje stejně jako s běžným polem, tedy lze je umísťovat do kterékoliv sekce kontingenční tabulky. Počítané pole z kontingenční tabulky odstraníte tlačítkem ODSTRANIT v dialogovém okně VLOŽIT POČÍTANÉ POLE. Příklad 5–4 – do nové kontingenční tabulky na nový list vypočítejte celkové prodeje (Počet kusů) a 2x větší celkové prodeje Prodejců jako nové počítané pole Počet kusů 200 %, viz Obrázek 5–9. Počítané pole poté zase odstraňte. Obrázek 5–9 Řešení pro Příklad 5–4 Zdroj: autor (2022) Analýza dat pomocí kontingenčních tabulek a grafů 71 Analýza dat v Microsoft Excelu 5.3 Řazení a filtrování v kontingenční tabulce Řazení a filtrování kontingenční tabulek je možné mnoha způsoby. Základním způsobem je využití polí umístěných v sekcích kontingenční tabulky, včetně sekce FILTRY, kterou jsme ještě nepoužili. Další možností filtrování jsou průřezy známé z kapitoly o strukturovaných tabulkách, které ale v tabulkách kontingenčních mají jednu zásadní vlastnost navíc, a to filtrování několika kontingenčních tabulek nebo grafů najednou jedním průřezem, což jiným způsobem udělat nelze. Navíc zde ještě jako možnost filtrování podle sloupců typu datum lze používat časovou osu. V této kapitole si všechny možnosti filtrování ukážeme. 5.3.1 Řazení v kontingenční tabulce Řazení řádků, ale případně také sloupců kontingenční tabulky je jednoduchá věc. Využijete k tomu znalosti získané v předchozích kapitolách. Jak si určitě pamatujete z dřívějších kapitol, řazení tabulky je možné tím způsobem, že kliknete do sloupce, podle kterého má být tabulka seřazena, a poté na kartě DATA zvolíte SEŘADIT OD NEJMENŠÍHO K NEJVĚTŠÍMU nebo naopak. Pokud byste chtěli tabulku seřadit podle polí umístěných v sekcích ŘÁDKY nebo SLOUPCE kontingenční tabulky, můžete k tomu využít také roletky umístěné u POPISKŮ ŘÁDKŮ nebo POPISKŮ SLOUPCŮ kontingenční tabulky. Tlačítko SEŘADIT na kartě DATA ale funguje odlišně, než jak bylo popsáno v dřívějších kapitolách. Toto tlačítko otevírá dialogové okno lišící se podle kontextu, tedy podle toho, zda je v kontingenční tabulce kurzor umístěn v nějaké souhrnné hodnotě nebo v záhlaví řádku nebo sloupce. Pokud je kurzor umístěn v souhrnné hodnotě, pak je otevřeno dialogové okno SEŘADIT PODLE HODNOTY, kde oproti dřívějším možnostem je navíc možnost seřadit tabulku zleva doprava. Pokud je ale kurzor umístěn v záhlaví řádku nebo sloupce, je zobrazeno dialogové okno ŘAZENÍ, kde je možné vybrat si mezi ručním řazením nebo řazením podle některého z polí, viz Obrázek 5–10. Ruční řazení se pak provádí tak, že se označí jedna hodnota v záhlaví řádků nebo sloupců, myší se chytí buňka za její okraj a hodnota se takto přesune na jinou pozici. Zároveň se s ní přesouvá celý řádek, příp. sloupec kontingenční tabulky. Obrázek 5–10 Dialogová okna pro řazení Zdroj: autor (2022) 72 Kapitola 5 2023 V. Novák Příklad 5–5 – na novém listu vytvořte kontingenční tabulku a seřaďte Prodejce podle celkových prodejů (Počet kusů) od největšího k nejmenšímu, viz Obrázek 5–11. Obrázek 5–11 Řešení pro Příklad 5–5 Zdroj: autor (2022) 5.3.2 Filtrování pomocí polí umístěných v sekcích kontingenční tabulky K filtrování kontingenčních tabulek lze využít všechna pole umístěná ve všech sekcích kontingenční tabulky. Postupně si jejich použití ukážeme. Zdánlivě se podobá automatickému filtru zmíněnému dříve, protože se u něj využívají roletky umístěné u jednotlivých polí kontingenční tabulky. Filtrování pomocí polí umístěných v sekci ŘÁDKY nebo SLOUPCE Toto filtrování se nejvíce podobá použití automatického filtru. Využívá se k němu roletek umístěných u POPISKŮ ŘÁDKŮ nebo POPISKU SLOUPCŮ. Po rozevření nabídky u zvoleného popisku se daného pole týkají všechny možnosti s výjimkou možnosti FILTRY HODNOT (viz Filtrování pomocí polí umístěných v sekci HODNOTY). Jak je vidět z nabídky, filtrovat je možné pomocí konkrétních hodnot, stačí jen vybrané hodnoty zaškrtnout. Je ale možné použít také nabídku FILTRY POPISKŮ, kde se nacházejí operátory vhodné pro datový typ daného pole. Pokud jste ale do sekce ŘÁDKY nebo SLOUPCE umístili více polí, je nutno v roletce VYBRAT POLE umístěné na první pozici nejdříve vybrat pole, podle kterého se bude filtrovat nebo řadit. Filtrování pomocí polí umístěných v sekci FILTRY Pokud chceme filtrovat podle pole, které jsme neumístili ani do sekce ŘÁDKY nebo SLOUPCE, je možné toto pole vložit do sekce FILTRY a pomocí něj kontingenční tabulku filtrovat. Je zde také možné zaškrtnout VYBRAT VÍCE POLOŽEK. Není zde ale možné používat žádné operátory, např. relační. Filtrování pomocí polí umístěných v sekci HODNOTY Kontingenční tabulku je také možné filtrovat podle polí umístěných v sekci HODNOTY. Stačí v roletce umístěné u POPISKŮ ŘÁDKŮ nebo POPISKŮ SLOUPCŮ použít nabídku FILTRY HODNOT. Pozor ale na to, že filtrování není prováděno v původních řádcích zdrojové tabulky v daném sloupci, ale je prováděno podle sloupce souhrnu kontingenční tabulky, a to pouze s využitím relačních operátorů. Pokud bychom chtěli Analýza dat pomocí kontingenčních tabulek a grafů 73 Analýza dat v Microsoft Excelu kontingenční tabulku filtrovat podle hodnot uvedených ve zdrojové tabulce v daném sloupci, musíme toto pole umístit do sekce FILTRY. Příklad 5–6 – v kontingenční tabulce z předchozího příkladu vyberte pouze Prodejce s celkovými prodeji (Počet kusů) většími než 13 000, viz Obrázek 5–12. Poté filtr zase zrušte. Obrázek 5–12 Řešení pro Příklad 5–6 Zdroj: autor (2022) 5.3.3 Filtrování pomocí průřezu a časové osy Princip průřezů již známe z kapitoly o strukturovaných tabulkách. Průřez je seznam jedinečných položek některého z polí kontingenční tabulky, jejichž výběrem je kontingenční tabulka filtrována. Podobně funguje také časová osa, kde výběrem časového intervalu v nějakém datovém sloupci filtrujeme kontingenční tabulku pouze s výpočty pro daný časový interval. Průřez (příp. časovou osu) vložíte pomocí tlačítka VLOŽIT PRŮŘEZ (příp. VLOŽIT ČASOVOU OSU) na kontextové kartě ANALÝZA KONTINGENČNÍ TABULKY ve skupině voleb FILTR. V dialogovém okně VLOŽIT PRŮŘEZY (příp. VLOŽIT ČASOVÉ OSY) zvolte pole, podle jehož hodnot se bude filtrovat. Pro časovou osu je ale možné pouze použití polí typu datum, viz Obrázek 5–13. 74 Kapitola 5 2023 V. Novák Obrázek 5–13 Dialogová okna Vložit průřezy a Vložit časové osy Zdroj: autor (2022) Pak je již možné začít filtrovat. Pokud používáte více průřezů nebo časových os, existuje mezi jejich filtry vztah logického součinu, tedy platí a zároveň. Obrázek 5–14 Ukázka průřezu a časové osy Zdroj: autor (2022) V průřezech je možná volba několika položek buď klikáním s využitím kláves SHIFT (souvislý blok položek) nebo CTRL (nesouvislý blok položek) nebo s použitím tlačítka VÍCENÁSOBNÝ VÝBĚR v pravém horním rohu průřezu. Na časové ose je možná volba pouze souvislého časového úseku s využitím klávesy SHIFT, v roletce v pravém horním rohu je ale možná změna časové jednotky, viz Obrázek 5–14. Pro zrušení filtru nastaveného pomocí průřezu nebo časové osy nestačí pouze odstranit průřez nebo časovou osu (klávesa DEL na označeném průřezu nebo časové ose), Analýza dat pomocí kontingenčních tabulek a grafů 75 Analýza dat v Microsoft Excelu protože filtr na kontingenční tabulce stále zůstává zapnutý. Pro zrušení filtru je jej nutné vypnout jakýmkoliv způsobem zmíněným v předchozích kapitolách, včetně tlačítka VYMAZAT FILTR v průřezu nebo časové ose. Příklad 5–7 – kontingenční tabulku z předchozího příkladu zkuste filtrovat pomocí průřezu a časové osy podle obrázku 5-14. Jak již bylo zmíněno v úvodu kapitoly, velkým rozdílem mezi průřezy používanými v tabulkách strukturovaných a v tabulkách kontingenčních je možnost průřezy a časové osy tabulek kontingenčních sdílet mezi několika kontingenčními tabulkami nebo grafy. To znamená, že jeden průřez nebo časová osa může filtrovat několik kontingenčních tabulek a grafů najednou. Tím pádem lze takto vytvořit v Excelu přehledný dashboard. Podmínkou sdílení průřezu nebo časové osy mezi několika kontingenčními tabulkami a grafy je, že tyto kontingenční tabulky a grafy musí mít shodný zdroj dat. Pokud chcete průřez nebo časovou osu sdílet mezi několika kontingenčními tabulkami nebo grafy, vytvořte průřez nebo časovou osu pouze pro jednu kontingenční tabulku nebo graf a poté takto vytvořený průřez nebo časovou osu připojte k dalším kontingenčním tabulkám nebo grafům. Připojení průřezu nebo časové osy k další kontingenční tabulce nebo grafu je možné dvěma způsoby: • Klikněte do další kontingenční tabulky a zvolte tlačítko PŘIPOJENÍ FILTRU na kontextové kartě ANALÝZA KONTINGENČNÍ TABULKY ve skupině voleb FILTR a v dialogovém okně PŘIPOJENÍ FILTRŮ vyberte z nabízených filtrů (průřezy, časové osy) ty požadované, viz Obrázek 5–15. Obrázek 5–15 Dialogové okno Připojení filtrů Zdroj: autor (2022) • Označte průřez nebo časovou osu a na jeho kontextové kartě PRŮŘEZ nebo ČASOVÁ OSA zvolte tlačítko PŘIPOJENÍ SESTAVY. V dialogovém okně PŘIPOJENÍ SESTAVY pak vyberte, která kontingenční tabulka nebo graf má být filtrován vybraným průřezem, viz Obrázek 5–16. 82 Kapitola 5 2023 V. Novák Obrázek 5–24 Dialogové okno Formát buňky pro formátování čísel kontingenční tabulky Zdroj: autor (2022) Pokud byste k formátování čísel chtěli použít kontextovou nabídku buněk, pak zvolte nabídku FORMÁT ČÍSLA (formátuje všechna čísla jednoho pole) a ne FORMÁT BUNĚK (formátuje pouze označené buňky). Příklad 5–13 – na nový list vytvořte kontingenční tabulku, kde celkové prodeje (Počet kusů) naformátujte s oddělovačem tisíců mezerou a jednotkou "ks", viz Obrázek 5–25. Analýza dat pomocí kontingenčních tabulek a grafů 83 Analýza dat v Microsoft Excelu Obrázek 5–25 Řešení pro Příklad 5–13 Zdroj: autor (2022) Řešení pro Příklad 5–13 – na hodnoty použijte následující vlastní formát: # ##0" ks". 5.5.3 Podmíněné formátování kontingenčních tabulek Podobně jako existuje možnost podmíněného formátování v běžných tabulkách (viz 2. kapitola), existuje tato možnost také v kontingenčních tabulkách. I u nich je nutné zvolit kartu DOMŮ a tlačítko PODMÍNĚNÉ FORMÁTOVÁNÍ, tedy výjimečně to není žádná z kontextových karet kontingenční tabulky. V principu je podmíněné formátování kontingenčních tabulek velice podobné podmíněnému formátování běžných tabulek, jeden zásadní rozdíl tady ale je. U běžných tabulek vždy označujete výběr buněk, které mají být podmíněně formátovány. U kontingenčních tabulek je to také možné, a dokonce je to výchozí nastavení, ale podobně jako formátování čísel je výhodnější podmíněně formátovat všechny hodnoty jednoho pole, aniž by bylo nutné je označovat. Není proto možné volit přímo žádné z pravidel z dostupných pěti typů, ale je nutno nabídkou NOVÉ PRAVIDLO otevřít dialogové okno NOVÉ PRAVIDLO FORMÁTOVÁNÍ, které pro potřeby kontingenčních tabulek doznalo drobné úpravy, viz Obrázek 5–26. 84 Kapitola 5 2023 V. Novák Obrázek 5–26 Dialogové okno Nové pravidlo formátování Zdroj: autor (2022) V horní části tohoto dialogového okna je právě možnost výběru, která část kontingenční tabulky má být podmíněně formátována, zda pouze vybraná buňka nebo se má formát rozšířit i na další buňky jednoho typu. Příklad 5–14 – na novém listu vytvořte kontingenční tabulku a naformátujte v ní zeleně celkové prodeje (Počet kusů) za Prodejce a Produkty, které jsou vyšší než 3 000, viz Obrázek 5–27. Obrázek 5–27 Řešení pro Příklad 5–14 Zdroj: autor (2022) 5.6 Kontingenční grafy "Obyčejný" graf Excelu graficky zobrazuje data umístěná v tabulce Excelu, aniž by nad těmito daty prováděl jakékoliv výpočty. Kontingenční graf zobrazuje agregovaná data Analýza dat pomocí kontingenčních tabulek a grafů 85 Analýza dat v Microsoft Excelu podobně jako kontingenční tabulka. Z toho vyplývá, že tvorba kontingenčního grafu se podobá tvorbě kontingenční tabulky, formátování kontingenčního grafu je ale zcela shodné s formátováním obyčejného grafu. Kontingenční graf lze vytvořit dvěma způsoby: • Pokud máte vytvořenu kontingenční tabulku, můžete k ní snadno vytvořit kontingenční graf. Stačí kliknout do kontingenční tabulky a na kontextové kartě ANALÝZA KONTINGENČNÍ TABULKY ve skupině voleb NÁSTROJE kliknout na tlačítko KONTINGENČNÍ GRAF. • Pokud máte data ve formátu seznamu a nemáte ještě kontingenční tabulku, můžete vytvořit přímo kontingenční graf, tedy na kartě VLOŽENÍ ve skupině voleb GRAFY zvolte tlačítko KONTINGENČNÍ GRAF. To obsahuje dvě nabídky: KONTINGENČNÍ GRAF nebo KONTINGENČNÍ TABULKA A KONTINGENČNÍ GRAF. Jak vyplývá z jejich názvu, nabídka KONTINGENČNÍ GRAF vytvoří jen tento graf, ale pouze pokud vstupní data jsou mimo Excel, jinak vytvoří i kontingenční tabulku. Nabídka KONTINGENČNÍ TABULKA A KONTINGENČNÍ GRAF vždy vytváří obojí. Všimněte si, že když je označen kontingenční graf, podokno POLE KONTINGENČNÍHO GRAFU má lehce modifikovány názvy jednotlivých sekcí na rozdíl od podokna POLE KONTINGENČNÍ TABULKY, viz Obrázek 5–28. Obrázek 5–28 Ukázka kontingenčního grafu a jeho sekcí Zdroj: autor (2022) Příklad 5–15 – na nový list vytvořte kontingenční graf, viz Obrázek 5–28. 5.7 Řešení příkladu z úvodní kapitoly Řešení příkladu z úvodní kapitoly pomocí kontingenční tabulky by vás asi mělo napadnout jako první, protože je nejjednodušší. Umístěte kurzor do tabulky náhodných čísel a zvolte na kartě VLOŽENÍ tlačítko KONTINGENČNÍ TABULKA. Kontingenční tabulku nastavte, viz Obrázek 5–29. Ještě je nutné změnit souhrnnou funkci na Počet. Stačí jen kliknout pravým tlačítkem myši do jedné z hodnot a zvolit SOUHRN DAT – POČET. 86 Kapitola 5 2023 V. Novák Obrázek 5–29 Nastavení kontingenční tabulky pro výpočet četnosti čísel Zdroj: autor (2022) 87 Kapitola 6 Analýza dat pomocí doplňku Power Pivot Power Pivot je technologie modelování dat, která nám umožňuje vytvářet datové modely, relace a výpočty pomocí jazyka Data Analysis Expressions (DAX) v prostředí Excelu. Je to doplněk, který je integrální součástí Excelu. V této kapitole se nejdříve podíváme na vytváření datových modelů tvořených několika tabulkami a jejich využití pro tvorbu kontingenčních tabulek. Později ale zjistíme, že i datový model tvořený jedinou tabulkou má svůj smysl, ať pro možnost mít ve zdrojové tabulce více než milion řádků nebo právě pro možnost dodatečných výpočtů (počítané sloupce a míry) pomocí jazyka DAX, který bez doplňku Power Pivot není možné využít. Nakonec zjistíme, že často budeme Power Pivot používat, aniž bychom jej fyzicky otevřeli. Prostě bude pro nás pracovat na pozadí. 6.1 Představení doplňku Power Pivot Doplněk Power Pivot (viz Obrázek 6–1) je integrální součástí Excelu, takže jej není nutné nějakým způsobem instalovat nebo aktivovat. Otevřít jej můžete pomocí karty DATA tlačítkem SPRAVOVAT DATOVÝ MODEL. Pokud nemáte vytvořen datový model, otevře se prázdný Power Pivot. Pokud datový model vytvořen máte, bude vám prostředí Power Pivot připadat podobné Excelu. Také zde jsou listy s tabulkami, je zde možné provádět dodatečné výpočty (zde ve formě počítaných sloupců a měr), zásadním rozdílem oproti Excelu ale je, že data v Power Pivot jsou určena pouze ke čtení, nelze je zde měnit. 88 Kapitola 6 2023 V. Novák Obrázek 6–1 Doplněk Power Pivot Zdroj: autor (2022) Power Pivot vlastně slouží jako datový sklad, ve kterém jsou shromažďována data z jiných zdrojů (nejčastěji relační databáze, ale zdrojem dat může být i sešit Excelu), takže pokud se mají data změnit, je nutné to provést ve zdroji dat a data v Power Pivot pak pouze aktualizovat. My budeme dále jako zdroj dat používat jen sešity Excelu. Jak tedy dostat data z Excelu do Power Pivot (neboli do datového modelu)? Možný je některý z následujících způsobů: • Při tvorbě kontingenční tabulky zaškrtávátkem PŘIDAT TAHLE DATA DO DATOVÉHO MODELU v dialogovém okně VYTVOŘIT KONTINGENČNÍ TABULKU. • Při importu dat zaškrtávátkem PŘIDAT TAHLE DATA DO DATOVÉHO MODELU v dialogovém okně IMPORTOVAT DATA (viz Power Query). • Při tvorbě relace mezi tabulkami pomocí karty DATA – RELACE. Pokud jste v Power Pivot, můžete použít na kartě DOMŮ tlačítko NAČÍST EXTERNÍ DATA Z JINÝCH ZDROJŮ DAT (pokud je zdrojem dat sešit Excelu, musí být zavřený). Většinu z výše zmíněných způsobů si teď ukážeme. Analýza dat pomocí doplňku Power Pivot 89 Analýza dat v Microsoft Excelu 6.1.1 Použití zaškrtávátka Přidat tahle data do datového modelu V první části této kapitoly si ukážeme důvod, proč použít zaškrtávátko PŘIDAT TAHLE DATA DO DATOVÉHO MODELU v dialogovém okně VYTVOŘIT KONTINGENČNÍ TABULKU, i když váš zdroj dat obsahuje jen jednu tabulku. Příklad 6–1 – na nový list sešitu 6_KontingencniTabulky.xlsx vytvořte kontingenční tabulku a spočítejte v ní počet Prodejců, viz Obrázek 6–2. Obrázek 6–2 Řešení pro Příklad 6–1 Zdroj: autor (2022) Řešení pro Příklad 6–1 – Při vytváření kontingenční tabulky zaškrtněte zaškrtávátko PŘIDAT TAHLE DATA DO DATOVÉHO MODELU. Protože jste do sekce HODNOTY kontingenční tabulky vložili pole Prodejce, což je pole typu text, Excel automaticky použije funkci Počet. Ten ale spočítá počet položek ve sloupci, což ale není počet prodejců. Počet prodejců znamená spočítat počet jednoznačných položek. A právě tuto funkci máte dostupnou díky přesunu dat z Excelu do datového modelu Power Pivot. Pro výpočet jednoznačného počtu v dialogovém okně NASTAVENÍ POLÍ HODNOT zvolte funkci Jednoznačný počet. Bez datového modelu jednoznačný počet nelze spočítat. Zaškrtávátko PŘIDAT TAHLE DATA DO DATOVÉHO MODELU je ale dostupné také při importu dat a kromě jiných jej využijete zejména v situaci, kdy tabulka ve zdroji dat obsahuje více než milion řádků, takže do listu sešitu by nebylo možné všechna data naimportovat, ale přitom chcete, aby data byla součástí sešitu. Příklad 6–2 – otevřete prázdný sešit Excelu, naimportujte do něj tabulku z databáze Accessu BigData.accdb a pomocí kontingenční tabulky v něm spočítejte počet jednotlivých čísel 1 až 9 ve sloupci Číslo v tabulce Čísla, viz Obrázek 6–3. Obrázek 6–3 Řešení pro Příklad 6–2 Zdroj: autor (2022) 90 Kapitola 6 2023 V. Novák Řešení pro Příklad 6–2 – Na kartě DATA použijte nabídku NAČÍST DATA – Z DATABÁZE – Z ACCESSOVSKÉ DATABÁZE, zvolte databázi BigData.accdb a tabulku Čísla. Protože má zdrojová tabulka více než milion řádků, není možné data načíst přímo do listu sešitu, proto použijte tlačítko NAČÍST DO …. V dialogovém okně IMPORTOVAT DATA zvolte SESTAVA KONTINGENČNÍ TABULKY a zaškrtněte PŘIDAT TAHLE DATA DO DATOVÉHO MODELU. Pak už můžete vytvořit kontingenční tabulku. Více o importu dat se dozvíte v následující kapitole věnované nástroji Power Query. 6.1.2 Vícetabulkové datové modely V této kapitole budeme pracovat se sešitem 6_Prodeje.xlsx. Velkou výhodu budou mít ti z vás, kdo jsou již obeznámeni s teorií návrhu relačních databází. V relačních databázích je totiž obvyklé, že data databáze nejsou uchovávána v jedné tabulce, ale v několika tabulkách, kde každá tabulka popisuje jeden objekt reálného světa, např. tabulka produktů, prodejců nebo prodejů. Každý řádek tabulky je pak jedním konkrétním objektem reálného světa, tedy jedním konkrétním prodejcem, produktem nebo prodejem. Obrovskou výhodou tohoto přístupu je snadné přidávání nových objektů do databáze, jejich snadná úprava nebo odstranění. Vždy se pracuje jen s jedním řádkem tabulky. Pokud ale chceme takovouto databázi analyzovat komplexně, musíme mezi tabulkami vytvořit tzv. relace, které vyjadřují jejich souvislosti. Pro vytváření relací je nutné znát pojmy primární a cizí klíč, které se zcela běžně používají v relačních databázích. Primární klíč je sloupec tabulky, který jednoznačně identifikuje každý řádek v tabulce, tedy nějaký jedinečný identifikátor. V našem sešitě jsou to sloupce IDProduktu v tabulce DimProdukty, IDProdejce v tabulce DimProdejci a IDProdeje v tabulce FactProdeje. Aby bylo možné vytvořit relaci mezi tabulkami, musí se tento sloupec vyskytovat také v tabulce související, kde již ale hodnoty obvykle obsahují duplicity. Např. aby bylo jasné, který prodej realizoval který prodejce, musí být v tabulce FactProdeje také IDProdejce. Tomuto sloupci, který vyjadřuje souvislost s jinou tabulkou, se říká cizí klíč. Jednou z významných vlastností relací je jejich kardinalita, která vyjadřuje, kolik záznamů v souvisejících tabulkách spolu souvisí. Nejčastější kardinalitou je kardinalita 1:N, která vyjadřuje, že s jedním záznamem tabulky první může souviset nula až několik záznamů tabulky druhé, ale naopak s jedním záznamem tabulky druhé souvisí jen jeden záznam tabulky první. Např. jeden prodejce může mít několik prodejů (čili v tabulce DimProdejci je každá hodnota ve sloupci IDProdejce jen jednou, ale v tabulce FactProdeje se mohou hodnoty ve sloupci IDProdejce každého prodejce vyskytovat mnohokrát), ale jeden konkrétní prodej se týká jen jednoho prodejce. Existují i jiné typy kardinalit, ale my se budeme zabývat pouze kardinalitou 1:N. V datových skladech systémů business intelligence se při použití schématu hvězdy tabulkám na straně 1 říká tabulky dimenzí. Obsahují dimenze, podle kterých chceme fakta zobrazovat, např. dimenze času, tedy za jednotlivé roky nebo čtvrtletí, dimenze produktů, tedy prodeje jednotlivých produktů. Tabulce na straně se N se říká tabulka faktů a ta obsahuje fakta, tedy hodnoty, které chceme agregovat v kontingenčních tabulkách, viz Obrázek 6–4. Podle toho, zda tabulka je tabulkou dimenzí nebo faktů, se pak často Analýza dat pomocí doplňku Power Pivot 91 Analýza dat v Microsoft Excelu používají u názvů tabulek předpony Dim (např. DimProdejci) nebo Fact (např. FactProdeje). Další vysvětlení systémů business intelligence je ale nad rámec této knihy. Obrázek 6–4 Příklad struktury tabulek datového skladu systému business intelligence Zdroj: https://docs.microsoft.com/cs-cz/power-bi/guidance/star-schema (2022) Pokud máte data ve formě tabulek v Excelu a chcete mezi těmito tabulkami vytvářet relace, je nutno vaše tabulky naformátovat jako tabulky strukturované (v našem sešitu je již hotovo). Relace v Excelu se vytvářejí pomocí karty DATA tlačítkem RELACE. Otevře se dialogové okno SPRAVOVAT RELACE, viz Obrázek 6–5. 98 Kapitola 6 2023 V. Novák Obrázek 6–12 Řešení pro Příklad 6–4 Zdroj: autor (2022) Řešení pro Příklad 6–4 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje, přejmenujte sloupec Přidat sloupec na Obrat a poté do řádku vzorců vložte vzorec (názvy tabulek není nutno psát): =FactProdeje[Počet kusů] * FactProdeje[Cena / ks]. Pozor na to, že našeptávač Power Pivot neumí při zápisu vzorců používat znaky s českou diakritikou. Následně v Excelu vytvořte požadovanou kontingenční tabulku s využitím datového modelu sešitu a nového sloupce Obrat. Následně odstraňte jak kontingenční tabulku v Excelu, tak počítaný sloupec v datovém modelu. 6.2.2 Míry Míry neprovádějí výpočty pro každý řádek podobně jako počítaná pole, ale provádějí agregaci hodnot z mnoha řádků (např. SUM), jehož výsledkem je souhrn. Jednoduché míry, jako jsou součty, průměry, minimum, maximum, a počty lze v Excelu nastavit pomocí NASTAVENÍ POLÍ HODNOT – tzv. implicitní míry. Explicitní míry, které si sami vytvoříte v jazyku DAX, se zobrazí v seznamu polí s ikonou fx. U explicitní míry již nelze nastavit agregační funkci v NASTAVENÍ POLÍ HODNOT, agregační funkce je totiž součásti míry. Přemýšlejte, proč např. míra SUM([Počet kusů]) je někdy jedno číslo a někdy několik čísel. Je to tím, že samotná hodnota míry je dána umístěním míry v kontingenční tabulce (tedy je dána průnikem záhlaví řádku a sloupce), případně působením dalších filtrů. Říkáme, že míra je ovlivněna kontextem filtru. V Power Pivot se míra zapisuje do dolní části zvolené tabulky, místo znaku = se ve vzorci na rozdíl od počítaného sloupce používá dvojznak := (dvojtečka rovná se). Míry se na rozdíl od počítaných sloupců počítají až v době vykonání dotazu. Příklad 6–5 – vypočítejte v kontingenční tabulce na novém listu celkové prodeje (Počet kusů) za Prodejce jako explicitní míru s názvem Celkové prodeje, viz Obrázek 6–13. Následně odstraňte jak kontingenční tabulku v Excelu, tak míru v datovém modelu. Analýza dat pomocí doplňku Power Pivot 99 Analýza dat v Microsoft Excelu Obrázek 6–13 Řešení pro Příklad 6–5 Zdroj: autor (2022) Řešení pro Příklad 6–5 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje a do kterékoliv buňky v dolní části listu vložte vzorec: Celkové prodeje := SUM(FactProdeje[Počet kusů]). Následně v Excelu vytvořte požadovanou kontingenční tabulku s využitím datového modelu sešitu a nové míry Celkové prodeje. Následně odstraňte jak kontingenční tabulku, tak míru Celkové prodeje. Příklad 6–6 – vypočítejte v kontingenční tabulce na novém listu celkové Obraty jednotlivých Prodejců, viz Obrázek 6–14. Obrat vypočítejte pomocí míry. Následně odstraňte jak kontingenční tabulku v Excelu, tak míru v datovém modelu. Obrázek 6–14 Řešení pro Příklad 6–6 Zdroj: autor (2022) Řešení pro Příklad 6–6 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje a do kterékoliv buňky v dolní části listu vložte vzorec: Obrat := SUMX(FactProdeje;[Počet kusů]*[Cena / ks]). Funkci SUM nelze použít, protože parametrem funkce SUM nemůže být výraz, pouze jedno pole. Funkce SUMX spočítá součet výrazů (2. argument) v tabulce (1. argument) (viz iterátory později). Následně v Excelu vytvořte požadovanou kontingenční tabulku s využitím datového modelu sešitu a nové míry Obrat. Následně odstraňte jak kontingenční tabulku, tak míru Obrat. 100 Kapitola 6 2023 V. Novák Jak je vidět z příkladů výpočtu celkových obratů, některé výpočty lze řešit jak počítaným polem, tak mírou. Kdy tedy použít počítané pole a kdy míru? Počítané pole vyberte, když: • výsledek výpočtu chcete použít v průřezu, v sekcích ŘÁDKY nebo SLOUPCE kontingenční tabulky (na rozdíl od sekce HODNOTY) nebo jako filtrovací podmínku, • definujete výraz, který je striktně svázán s aktuálním řádkem (např. Množství * Cena), • chcete hodnoty kategorizovat (např. získat věkové kategorie 0-18, 18-25). Míru vyberte, když: • chcete zobrazit výsledek výpočtu v sekci HODNOTY kontingenční tabulky tak, aby reflektoval výběr uživatele v sekcích ŘÁDKY, SLOUPCE a FILTRY. 6.2.3 Funkce jazyka DAX shodné s funkcemi Excelu Jak už jste si asi všimli v předchozí části této kapitoly, mnoho funkcí jazyka DAX je shodných s funkcemi Excelu jen s tím rozdílem, že funkce jazyka DAX jsou uvedeny vždy v angličtině, nikdy nejsou přeloženy do českého jazyka. To znamená, že následující funkce jazyka DAX byste již více méně měli znát. • Agregační funkce – SUM, AVERAGE, MIN, MAX, COUNT (počet číselných hodnot ve sloupci), COUNTA (počet neprázdných hodnot ve sloupci), COUNTBLANK (počet prázdných buněk ve sloupci), COUNTROWS (počet řádků v tabulce, v Excelu není), DISTINCTCOUNT (počet jedinečných hodnot ve sloupci, v Excelu není). • Logické funkce – IF, IFERROR, AND, OR, NOT. • Informační funkce (vracejí TRUE nebo FALSE) – ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISTEXT. • Matematické funkce – ABS, LOG, PI, SQRT, RANDBETWEEN atd. atd. • Textové funkce – CONCATENATE (stejně jako v Excelu lze nahradit operátorem &), FORMAT, LEFT, LEN, LOWER, MID, RIGHT, SEARCH, TRIM, UPPER, VALUE. • Funkce data a času – DATE, DATEVALUE, DAY, EOMONTH, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, YEAR. Příklad 6–7 – vypočítejte nový počítaný sloupec Den týdne a následně v kontingenční tabulce na novém listu spočítejte celkové prodeje (Počet kusů) za jednotlivé Dny týdne, viz Obrázek 6–15. Následně odstraňte jak kontingenční tabulku v Excelu, tak míru v datovém modelu. Analýza dat pomocí doplňku Power Pivot 101 Analýza dat v Microsoft Excelu Obrázek 6–15 Řešení pro Příklad 6–7 Zdroj: autor (2022) Řešení pro Příklad 6–7 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje, přejmenujte sloupec Přidat sloupec na Den týdne a poté do řádku vzorců vložte vzorec: =WEEKDAY([Datum prodeje]; 2) & "-" & FORMAT([Datum prodeje]; "dddd"). Následně v Excelu vytvořte požadovanou kontingenční tabulku s využitím datového modelu sešitu a nového sloupce Den týdne. Následně odstraňte jak kontingenční tabulku, tak počítaný sloupec. 6.2.4 Agregační funkce s příponou X (iterátory) Jako první funkce jazyka DAX, které nejsou v Excelu, si uvedeme iterátory, tedy funkce, jejichž název většinou končí příponou X. Příkladem může být funkce SUMX použitá v Příkladu 6-6. Mezi iterátory patří následující funkce: SUMX, AVERAGEX, COUNTX, COUNTAX, CONCATENATEX, MAXX, MINX, PRODUCTX. Těmto funkcím se říká iterátory proto, že iterují (procházejí řádek po řádku) tabulku (může být i tabulková funkce), přičemž počítají DAX výraz pro každý řádek. Syntaxe funkce je následující: FunkceX(Tabulka; Výraz) • Tabulka – povinný argument. Tabulka, kterou se bude iterovat. • Výraz – povinný argument. DAX výraz, který bude vyhodnocen pro každý iterovaný řádek tabulky a následně agregován podle dané funkce. Další významnou funkcí iterátorem je funkce FILTER, má však odlišnou syntaxi oproti výše zmíněným. Funkce FILTER iteruje tabulkou (což může být i tabulková funkce), pro každý řádek vyhodnocuje podmínku (musí to být logická podmínka) a pokud podmínka je TRUE, zahrne řádek do výsledku. Funkce FILTER tedy vrátí tabulku s naprosto stejnými sloupci jako v původní tabulce, jen obsahuje řádky splňujícími podmínku. Syntaxe funkce FILTER je: FILTER(Tabulka; Podmínka) Pokud má být splněno několik podmínek najednou, lze to vyřešit: 102 Kapitola 6 2023 V. Novák • vnořením funkce: FILTER(FILTER(Tabulka; Podmínka1); Podmínka2) • funkcí AND: FILTER(Tabulka; AND(Podmínka1; Podmínka2)) Příklad 6–8 – vypočítejte v kontingenční tabulce na novém listu Celkové obraty nadprůměrných prodejů jednotlivých Prodejců, ale jen z nadprůměrných prodejů (Počet kusů), viz Obrázek 6–16. Celkový obrat nadprůměrných prodejů vypočítejte pomocí míry. Následně odstraňte jak kontingenční tabulku v Excelu, tak míru v datovém modelu. Obrázek 6–16 Řešení pro Příklad 6–8 Zdroj: autor (2022) Řešení pro Příklad 6–8 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje a do kterékoliv buňky v dolní části listu vložte vzorec: Celkový obrat nadprůměrných prodejů:=SUMX( FILTER( FactProdeje; FactProdeje[Počet kusů]>AVERAGE(FactProdeje[Počet kusů]) ); FactProdeje[Počet kusů]*FactProdeje[Cena / ks] ) Vzorce lze pro přehlednost zalamovat do více řádků pomocí klávesové zkratky SHIFT+ENTER. Následně v Excelu vytvořte požadovanou kontingenční tabulku s využitím datového modelu sešitu a nové míry Celkový obrat nadprůměrných prodejů. Následně odstraňte jak kontingenční tabulku, tak míru Celkový obrat nadprůměrných prodejů. 6.2.5 Funkce ALL a ALLSELECTED Všechny naše míry, které jsme až do této chvíle vytvořili, využívaly kontext filtru, tzn. že míra upravila svůj výpočet podle řádku a sloupce kontingenční tabulky, ve kterých je umístěna, případně podle průřezů, filtrů atd. Můžeme však tvořit také míry, jež vůbec nebudou využívat kontext filtru, ale na každé pozici v kontingenční tabulce vypočítají stejný souhrn. Základní funkcí, která kontext filtru nevyužívá, je funkce ALL. Analýza dat pomocí doplňku Power Pivot 103 Analýza dat v Microsoft Excelu Funkce ALL vrátí jako tabulku vždy všechny její jedinečné řádky tabulky nebo jedinečné řádky vyjmenovaných sloupců tabulky a ignoruje přitom všechny filtry. Syntaxe funkce ALL je: ALL(Tabulka) • Tabulka nesmí být tabulková funkce. ALL(Sloupec1; Sloupec2; …) Příklad 6–9 – vytvořte míru Celkové prodeje, kterou nebude možné ovlivnit žádným filtrem, a zkuste ji použít jako hodnotu v kontingenční tabulce na novém listu Celkové prodeje za jednotlivé Prodejce a čtvrtletí (Datum prodeje), viz Obrázek 6–17. Všimněte si, že míra má stejnou hodnotu ve kterékoliv části kontingenční tabulky. Následně odstraňte jak kontingenční tabulku v Excelu, tak míru v datovém modelu. Obrázek 6–17 Řešení pro Příklad 6–9 Zdroj: autor (2022) Řešení pro Příklad 6–9 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje a do kterékoliv buňky v dolní části listu vložte vzorec: Celkové prodeje:=SUMX( ALL(FactProdeje); FactProdeje[Počet kusů] ) Z příkladu je asi patrné, že taková míra nemá velký praktický význam, což ale neznamená, že funkce ALL nemá význam žádný. Občas totiž potřebujete, aby byly všechny filtry ignorovány, např. kdybychom chtěli vypočítat procentuální podíl celkových prodejů za prodejce a čtvrtletí. Takovýto procentuální podíl je vlastně podíl celkových prodejů za daného prodejce a čtvrtletí a celkových prodejů za všechny prodejce a čtvrtletí. A zde, ve jmenovateli takového výpočtu s úspěchem použijeme funkci ALL. Příklad 6–10 – vytvořte míru Podíl prodejů a zkuste ji použít jako hodnotu v kontingenční tabulce na novém listu Podíl prodejů za jednotlivé Prodejce a čtvrtletí (Datum prodeje), viz Obrázek 6–18. Kontingenční tabulku ani míru neodstraňujte! 104 Kapitola 6 2023 V. Novák Obrázek 6–18 Řešení pro Příklad 6–10 Zdroj: autor (2022) Řešení pro Příklad 6–10 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje a do kterékoliv buňky v dolní části listu vložte vzorec: Podíl prodejů := DIVIDE( SUM(FactProdeje[Počet kusů]); SUMX( ALL(FactProdeje); FactProdeje[Počet kusů] ) ) Zkuste k této kontingenční tabulce doplnit další filtr, např. průřez za jednotlivé produkty, viz Obrázek 6–19. Obrázek 6–19 Řešení předchozího příkladu s využitím průřezu Zdroj: autor (2022) Jak je vidět na obrázku, míra Podíl prodejů přestala fungovat korektně. Je to proto, že funkce ALL ruší všechny filtry, tedy i filtr průřezu. Čitatel podílu je filtrován, ale jmenovatel filtrován není. Proto Celkový součet při použití průřezu není 100 %. Řešením tohoto problému je nahradit funkci ALL funkcí, která by zrušila filtry řádků a sloupců kontingenční tabulky (Prodejce, čtvrtletí), ale ponechala by všechny ostatní filtry, jako jsou třeba průřezy. Touto funkcí je funkce ALLSELECTED. Funkce ALLSELECTED ignoruje filtry řádků a sloupců kontingenční tabulky a ponechá jen filtry pocházející zvenčí kontingenční tabulky. Syntaxe funkce je následující: ALLSELECTED(NázevTabulkyNeboSloupce) Parametrem funkce může být: Analýza dat pomocí doplňku Power Pivot 105 Analýza dat v Microsoft Excelu • Jeden sloupec – vybere hodnoty sloupce vybrané pouze ve vnějším filtru, ignoruje filtr řádků nebo sloupců kontingenční tabulky, pokud je sloupec použit jako řádek nebo sloupec kontingenční tabulky. • Celá tabulka – vykoná ALLSELECTED na všech sloupcích, ignoruje filtr řádků a sloupců kontingenční tabulky, pokud je kterýkoliv sloupec tabulky použit v řádku nebo sloupci kontingenční tabulky. • Parametr nezadán – vykoná ALLSELECTED na všech tabulkách datového modelu, ignoruje všechny filtry řádků a sloupců kontingenční tabulky. Příklad 6–11 – opravte míru Podíl prodejů tak, aby fungovala korektně i s použitím filtrů mimo kontingenční tabulku, např. průřezem, viz Obrázek 6–20. Obrázek 6–20 Řešení pro Příklad 6–11 Zdroj: autor (2022) Řešení pro Příklad 6–11 – v doplňku Power Pivot opravte míru Podíl prodejů následujícím způsobem: Podíl prodejů:=DIVIDE( SUM(FactProdeje[Počet kusů]); SUMX( ALLSELECTED(FactProdeje); FactProdeje[Počet kusů] ) ) 6.2.6 Funkce CALCULATED Jak jsme již zjistili, každý výraz DAX je vykonán v rámci nějakého kontextu. Kontext je "prostředí", v rámci kterého je výraz DAX vyhodnocen. V jazyce DAX jsou kontexty dva: • Kontext filtru – mějme pouze jednu míru, ta ale pro každou buňku kontingenční tabulky vypočítá jiný výsledek. Je to proto, že výsledek je dán kontextem filtru – řádek, sloupec, průřez, filtry atd. Všechny tyto filtry přispívají k definici jediného kontextu filtru, ve kterém DAX vykoná vzorec. • Kontext řádku – používají počítaná pole. Je pouze jeden vzorec, ten ale v každém řádku vypočítá jiný výsledek. Výrazy DAX jsou vždy vykonávány nad oběma kontexty zároveň a výsledek výrazu na nich vždy závisí. Pokud například použijete míru Obrat := SUMX(FactProdeje, [Počet kusů]*[Cena / ks]) jako hodnoty kontingenční tabulky, je v kontextu filtru nejdříve vyhodnocena tabulka (první parametr, aplikují se všechny filtry – řádky, sloupce, průřezy 106 Kapitola 6 2023 V. Novák aj.) a poté je v této filtrované tabulce pro každý řádek (kontext řádku) vyhodnocen výraz (druhý parametr), jehož výsledky za každý řádek jsou na závěr sečteny. Funkce CALCULATE vytvoří nový kontext filtru a v něm pak vykoná definovaný výraz. Syntaxe funkce je následující: CALCULATE(Výraz; Podmínka1; …; PodmínkaN) Podmínky mohou být dvojího typu: • Seznam hodnot ve formě tabulkového výrazu – poskytuje přesný seznam hodnot, které mají být v novém kontextu filtru. • Logická podmínka – např. FactProdeje[Prodejce]="Pavel". Jestliže použijete logickou podmínku, DAX si ji transformuje na seznam hodnot, takže např. výraz počítající celkové prodeje prodejce Pavla: CALCULATE( SUM(FactProdeje[Počet kusů]); FactProdeje[Prodejce]="Pavel" ) bude transformován na výraz: CALCULATE( SUM(Prodeje[Počet kusů]); FILTER( ALL(Prodeje[Prodejce]); Prodeje[Prodejce]="Pavel" ) ) Příklad 6–12 – v kontingenční tabulce na novém listu spočítejte celkové prodeje a průběžný součet prodejů (Počet kusů) za čtvrtletí (Datum prodeje), viz Obrázek 6–21. Obrázek 6–21 Řešení pro Příklad 6–12 Zdroj: autor (2022) Analýza dat pomocí doplňku Power Pivot 107 Analýza dat v Microsoft Excelu Řešení pro Příklad 6–12 – v doplňku Power Pivot v zobrazení dat aktivujte tabulku FactProdeje a do kterékoliv buňky v dolní části listu vložte vzorec: Průběžný součet prodejů:=CALCULATE( SUM(FactProdeje[Počet kusů]); FILTER( ALLSELECTED(FactProdeje); FactProdeje[Datum prodeje]<=MAX(FactProdeje[Datum prodeje]) ) ) Příklad 6–13 – změňte míru Podíl Prodejů vytvořenou v Příkladu 6-11 tak, aby použila funkci CALCULATE. • Verze bez použití funkce CALCULATE Podíl prodejů:=DIVIDE( SUM(FactProdeje[Počet kusů]); SUMX( ALLSELECTED(FactProdeje); FactProdeje[Počet kusů] ) ) Řešení pro Příklad 6–13 – míra Podíl prodejů s použitím funkce CALCULATE. Podíl prodejů:=DIVIDE( SUM(FactProdeje[Počet kusů]); CALCULATE( SUM(FactProdeje[Počet kusů]); ALLSELECTED(FactProdeje) ) ) Ačkoliv jsme si ukázali jen několik málo funkcí jazyka DAX, je z těchto ukázek jasně patrné, že kdo ve svých kontingenčních tabulkách nechce provádět jen základní výpočty, jako jsou součty, počty nebo průměry, bude se muset do možností tohoto jazyka ponořit. 6.3 Řešení příkladu z úvodní kapitoly V našem případě, kdy máme data umístěna v jediné ne moc rozsáhlé tabulce a přejeme si z nich spočítat pouhé četnosti, je asi zbytečné data umísťovat do datového modelu, tedy do Power Pivot, možné to ale je. Stačí jen při vytváření kontingenční tabulky v dialogovém okně KONTINGENČNÍ TABULKA Z TABULKY NEBO OBLASTI zaškrtnout PŘIDAT TATO DATA DO DATOVÉHO MODELU. Dál už se postupuje stejně jako při řešení tohoto příkladu v předchozí kapitole. Použití datového modelu by bylo nutné, kdyby řádků v tabulce bylo více než milión nebo bychom měli více tabulek propojených relacemi. I v případě našeho jednoduchého příkladu lze ale vymyslet částečně odlišné řešení, pokud používáme datový model. Odlišné řešení spočívá v použití explicitní míry pro výpočet četnosti místo použité míry implicitní v předchozí kapitole. 114 Kapitola 7 2023 V. Novák TRANSFORMACE – DATOVÝ TYP – změní datový typ sloupce. Pokud je u změny datového typu nutné určení národního prostředí, pak je nutno použít kontextové menu sloupce – ZMĚNIT TYP – POUŽÍT NÁRODNÍ PROSTŘEDÍ. Příklad 7–8 – změňte datový typ sloupce SalesValue na desetinné číslo. TRANSFORMACE – ROZPOZNAT DATOVÝ TYP – pokusí se změnit datové typy označených sloupců podle hodnot ve sloupci. Příklad 7–9 – odstraňte transformaci Změněný typ a zase ji pro všechny sloupce nastavte. TRANSFORMACE – EXTRAHOVAT – z údajů ve sloupci vybere jen určité znaky. Příklad 7–10 – extrahujte poslední 3 znaky ve sloupci InvoiceNumber a změňte typ sloupce na celé číslo. 7.2.2 Filtrování a řazení řádků Následující transformace umožňují práci s řádky tabulky. AUTOMATICKÝ FILTR – pomocí roletek v záhlaví sloupců lze stejně jako v Excelu vyfiltrovat jen určité řádky tabulky splňující zadaná kritéria. DOMŮ – ZACHOVAT ŘÁDKY – zachová pouze horních nebo dolních x řádků, prázdné řádky nebo řádky, kde v označených sloupcích jsou duplicity nebo chyby. DOMŮ – ODEBRAT ŘÁDKY – odebere horních nebo dolních x řádků, prázdné řádky nebo řádky, kde v označených sloupcích jsou duplicity nebo chyby. DOMŮ – SEŘADIT VZESTUPNĚ (SESTUPNĚ) – lze také pomocí roletek v záhlaví sloupců (stejně jako v Excelu). Pokud má být tabulka seřazena podle více sloupců, je nutno provést řadicí kroky bezprostředně za sebou (nelze řadit několik označených sloupců). Tyto řadicí kroky pak budou sloučeny do jednoho kroku. Pokud by řadicí kroky byly odděleny od sebe jinou transformaci, tabulka bude seřazena podle posledního z nich. TRANSFORMACE – OBRÁTIT ŘÁDKY – otočí pořadí řádků. Příklad 7–11 – vyzkoušejte všechny uvedené transformace podle svého uvážení. 7.2.3 Změny hodnot ve sloupcích Následující transformace umožňují změny hodnot ve sloupcích. TRANSFORMACE – NAHRADIT HODNOTY – nahradí text (příp. chyby) jinou hodnotou. Rozlišuje velikost znaků! Pokud chcete nahrazovat hodnoty prázdným řetězcem, ponechte pole NAHRADIT HODNOTOU prázdné (blank, např. číselné sloupce nemohou být blank, mohou být jen null). Pokud chcete nahrazovat hodnotou null, zadejte do pole NAHRADIT HODNOTOU hodnotu null. Příklad 7–12 – ve sloupci InvoiceNumber nahraďte slovo Invoice za nic, ve sloupci ShipToCountry nahraďte nic za null, změňte datový typ sloupce InvoiceNumber na celé číslo a chyby nahraďte za null. Import a transformace dat pomocí Power Query 115 Analýza dat v Microsoft Excelu TRANSFORMACE – FORMÁT – v textových sloupcích změní velikosti znaků textu, umožňuje čištění textu atd. Příklad 7–13 – ve sloupci SalesPeople zajistěte, aby každé jméno začínalo velkým písmenem a ostatní písmena byla malá. TRANSFORMACE – VYPLNIT – nahradí ve sloupci null hodnoty poslední nenullovou hodnotou. Příklad 7–14 – vyplňte ve sloupci ShipToCountry prázdné buňky ve sloupci posledními neprázdnými hodnotami (nejdříve je nutné zaměnit nic za null) TRANSFORMACE – STANDARDNÍ, VĚDECKÝ, ZAOKROUHLENÍ, INFORMACE – v číselných sloupcích provede vybranou matematickou operaci. Příklad 7–15 – vynásobte hodnoty ve sloupci UnitsSold číslem 1000. TRANSFORMACE – DATUM, ČAS, TRVÁNÍ – ve sloupci typu datum provede vybranou operaci. Příklad 7–16 – vytvořte nový sloupec Month obsahující názvy měsíců sloupce SalesDate (karta PŘIDÁNÍ SLOUPCE – DATUM – MĚSÍC – NÁZEV MĚSÍCE). 7.2.4 Přidání sloupce Následující transformace umožňují přidávání nových sloupců, mnohdy s využitím sloupců stávajících. PŘIDÁNÍ SLOUPCE – INDEXOVANÝ SLOUPEC – přidá nový sloupec s číslem řádku počítaným od 0 nebo 1 nebo od zvoleného čísla. PŘIDÁNÍ SLOUPCE – DUPLIKOVAT SLOUPEC – duplikuje vybraný sloupec. PŘIDÁNÍ SLOUPCE – sekce Z TEXTU, Z ČÍSLA, Z DATA A ČASU – podobné jako u transformací na kartě TRANSFORMACE, jen ke stávajícímu sloupci je vytvořen také nový vypočítaný sloupec. PŘIDÁNÍ SLOUPCE – PODMÍNĚNÝ SLOUPEC – přidá nový sloupec na základě splnění podmínky nad jiným sloupcem. Příklad 7–17 – vytvořte nový sloupec ProductID, kde Apples=1, Oranges=2, Pears=3, Grapes=4 PŘIDÁNÍ SLOUPCE – VLASTNÍ SLOUPEC – vytvoří nový sloupec pomocí výpočtu v jazyce M. DOMŮ – SESKUPIT PODLE – vytvoří tabulku agregací hodnot vybraného sloupce podle seskupovaného sloupce. Příklad 7–18 – vytvořte tabulku se sloupci ProductName a TotalShippingCosts, viz Obrázek 7–5. 116 Kapitola 7 2023 V. Novák Obrázek 7–5 Výsledná tabulka pro Příklad 7–18 Zdroj: autor (2022) 7.2.5 Další transformace V této kapitole si můžete dané transformace zkoušet na datech ze sešitu 7_UnPivot.xlsx. TRANSFORMACE – PŘEVÉST SLOUPCE NA ŘÁDKY – převede dvourozměrnou tabulku na jednorozměrnou. TRANSFORMACE – TRANSPONOVAT – otočí tabulku o 90 stupňů. 7.2.6 Práce s několika dotazy Práci s několika dotazy si můžete zkoušet na tabulkách ze sešitu 7_Kombinovat.xlsx, importujte všechny strukturované tabulky. DOMŮ – KOMBINOVAT – PŘIPOJIT DOTAZY – provede sjednocení dotazů podobně jako příkaz UNION jazyka SQL. Připojované dotazy by měly mít stejnou strukturu (počty a typy sloupců). • PŘIPOJIT DOTAZY – k vybranému dotazu připojí další dotaz nebo dotazy. • PŘIPOJIT DOTAZY JAKO NOVÉ – vytvoří nový dotaz sjednocením několika dotazů. Příklad 7–19 - do nové tabulky Fruits připojte tabulky Apples, Oranges a Pears. DOMŮ – KOMBINOVAT – SLOUČIT DOTAZY – provede spojení dotazů podobně jako JOIN jazyka SQL. • SLOUČIT DOTAZY – k vybranému dotazu sloučí další dotaz. • SLOUČIT DOTAZY JAKO NOVÉ – vytvoří nový dotaz sloučením dvou dotazů. Ve slučovaných tabulkách je nutno vybrat sloupec nebo sloupce, pomocí kterých bude sloučení provedeno. Pro spojení tabulek je nutno vybrat typ spojení, viz Obrázek 7– 6. Import a transformace dat pomocí Power Query 117 Analýza dat v Microsoft Excelu Obrázek 7–6 Typy spojení pro sloučení dotazů Zdroj: autor (2022) Sloučená tabulka se zobrazí v jednom sloupci s odkazem Table. Po sloučení tabulek je nutno si vybrat (tlačítko vpravo v záhlaví sloupce), zda sloučenou tabulku chceme jen rozbalit nebo agregovat. Příklad 7–20 – do nové tabulky ApplesComplete slučte tabulky Apples a ApplesProfit a vyberte jen řádky se shodnými hodnotami ve sloupcích Month a Product. Zkuste měnit typ spojení pomocí nastavení transformace a pozorujte změny ve výsledné tabulce. Závěrem k seznamu možných transformací je nutno dodat, že na pořadí prováděných transformací záleží, jak rychle se M skript provede. Proto pokud chcete, aby se vaše importy prováděly co nejrychleji, dodržujte následující doporučené pořadí transformací: • Načíst data. • Vyfiltrovat. • Odebrat nepotřebné sloupce. • Transformovat. • Přejmenovat sloupce. • Přetypovat sloupce. 7.2.7 Řešené příklady na použití transformací V této kapitole si zopakujete transformace vysvětlené v předchozích kapitolách. Příklad 7–21 – z dat sešitu 7_NeniSeznam.xlsx vytvořte tabulku typu seznam, viz Obrázek 7–7. 118 Kapitola 7 2023 V. Novák Obrázek 7–7 Vzor výsledné tabulky pro Příklad 7–21 Zdroj: autor (2022) Postup práce: • Otevřete sešit. • Pro import použijte data z tabulky pomocí karty DATA tlačítkem Z TABULKY NEBO OBLASTI a zaškrtněte, že TABULKA NEOBSAHUJE ZÁHLAVÍ. • Odstraňte dva poslední kroky postupu, který vytvořil automaticky Power Query, a to ZMĚNĚNÝ TYP a ZÁHLAVÍ SE ZVÝŠENOU ÚROVNÍ. • Protože budeme převádět sloupce na řádky, potřebujeme, aby záhlaví bylo tvořeno pouze jedním řádkem a ne dvěma, proto je nutno tabulku transponovat pomocí karty TRANSFORMACE tlačítkem TRANSPONOVAT. • Z prvního řádku vytvoříme záhlaví pomocí karty TRANSFORMACE tlačítkem POUŽÍT PRVNÍ ŘÁDEK JAKO ZÁHLAVÍ. • Prázdné buňky ve sloupci s prodejci vyplníme, označte tedy 1. sloupec a zvolte kartu TRANSFORMACE tlačítkem VYPLNIT – DOLŮ. • Nyní je sloupce měsíců nutno převést na řádky, označte tedy sloupce 1-12 a zvolte kartu TRANSFORMACE tlačítkem PŘEVÉST SLOUPCE NA ŘÁDKY. • Přejmenujte sloupce podle obrázku. • Změňte datový typ sloupce Měsíc na Celé číslo. Příklad 7–22 – spočítejte celkové obraty v CZK, viz Obrázek 7–8. K dispozici máme dva zdroje dat: • Prodeje v EUR a USD (7_ProdejeKurzy.xlsx) – z dat tohoto sešitu spočítejte celkové obraty v CZK (viz následující obrázek), k přepočtu na CZK použijte kurzy ze stránek ČNB, viz dále. • Kurzy EUR a USD za rok 2016 (https://www.cnb.cz/cs/financni-trhy/devizovytrh/kurzy-devizoveho-trhu/kurzy-devizoveho-trhu/rok.txt?rok=2016) Import a transformace dat pomocí Power Query 119 Analýza dat v Microsoft Excelu Obrázek 7–8 Výsledná kontingenční tabulka pro Příklad 7–22 Zdroj: autor (2022) Postup práce: • Importujte do Power Query obě tabulky (www.cnb.cz – Všechny kurzy – Kurzy devizového trhu – roční historie – 2016). Transformace tabulky Kurzy: • Odstraňte poslední krok ZMĚNIT DATOVÝ TYP. • Převeďte na řádky všechny sloupce kromě sloupce Datum: TRANSFORMACE – PŘEVÉST SLOUPCE NA ŘÁDKY. • Rozdělte sloupec měny na dva sloupce: TRANSFORMACE – ROZDĚLIT SLOUPEC – ODDĚLOVAČEM. • Přejmenujte sloupce na Datum, Nasobek, Mena, Kurz pomocí dvojkliku v záhlaví sloupce. • Změňte datové typy všech sloupců: označte všechny sloupce a zvolte TRANSFORMACE – ROZPOZNAT DATOVÝ TYP. Transformace tabulky Prodeje: • Slučte dotazy přes sloupce Datum a Měna: DOMŮ – SLOUČIT DOTAZY. • Rozbalte kurzy a zobrazte sloupce Nasobek a Kurz. • Přidejte vlastní sloupec Cena / ks (CZK) jako [#"Cena / ks"] * [Kurzy.Kurz] / [Kurzy.Nasobek]: PŘIDÁNÍ SLOUPCE – VLASTNÍ SLOUPEC. • Změňte datový typ sloupce na Desetinné číslo: TRANSFORMACE – DATOVÝ TYP – DESETINNÉ ČÍSLO. • Přidejte vlastní sloupec Obrat (CZK) jako [Počet kusů] * [#"Cena / ks (CZK)"]: PŘIDÁNÍ SLOUPCE – VLASTNÍ SLOUPEC. • Změňte datový typ sloupce na Desetinné číslo: TRANSFORMACE – DATOVÝ TYP – DESETINNÉ ČÍSLO. • Načtěte tabulku Prodeje do Excelu: DOMŮ – ZAVŘÍT A NAČÍST DO… – POUZE VYTVOŘIT PŘIPOJENÍ a pak načtěte jen tabulku Prodeje. 120 Kapitola 7 2023 V. Novák 7.3 Parametry dotazu Parametry dotazů mají podobný význam jako parametry funkcí Excelu. Práce s parametry je možná pomocí karty DOMŮ – SPRAVOVAT PARAMETRY, viz Obrázek 7–9. • NOVÝ PARAMETR – přidá nový parametr. • UPRAVIT PARAMETR – umožňuje změnu hodnoty existujícího parametru. • SPRAVOVAT PARAMETRY – umožňuje změnu nastavení parametrů. Obrázek 7–9 Dialogové okno Spravovat parametry Zdroj: autor (2022) Import a transformace dat pomocí Power Query 121 Analýza dat v Microsoft Excelu Kromě názvu má parametr následující vlastnosti: • POVINNÝ – pokud je povinný, musí být zadána AKTUÁLNÍ HODNOTA. • TYP – datový typ parametru. • NAVRHOVANÉ HODNOTY – jakým způsobem se zadává hodnota parametru: může být zadávána jako libovolná hodnota, výběrem z definovaného seznamu hodnot nebo výběrem ze seznamu hodnot získaného dotazem. Příklad 7–23 - použití parametru s libovolnou hodnotou – změna prodejce v dotazu (7_ZdrojExcel.xlsx) Řešení pro Příklad 7–23: • Vytvořte dotaz na strukturovanou tabulku. • Pomocí DOMŮ – SPRAVOVAT PARAMETRY – NOVÝ PARAMETR vytvořte volitelný parametr Prodejce typu Text s navrhovanou hodnotou Libovolnou. • Přidejte transformaci pro sloupec Prodejce: FILTRY TEXTU – JE ROVNO – PARAMETR – PRODEJCE1, viz Obrázek 7–10. Obrázek 7–10 Zadání parametru jako hodnoty transformace. Zdroj: autor (2022) • Pomocí DOMŮ – SPRAVOVAT PARAMETRY – UPRAVIT PARAMETRY nebo v dotazu parametru zvolte hodnotu parametru (např. Pavel). Power Query ZAVŘETE A NAČTĚTE. • Pro změnu parametru v Excelu je nutno upravit dotaz parametru a poté tabulku aktualizovat. 7.4 Jazyk M Následující část nebude vyčerpávající výklad jazyka M (M jako mashup). Půjde hlavně o to pochopit syntaxi jazyka M, všechny ty závorky, čárky atd. Pozor, jazyk M rozlišuje velikost znaků! 122 Kapitola 7 2023 V. Novák Kód jazyka M je možno vidět a editovat: • V řádku vzorců – karta ZOBRAZENÍ – ŘÁDEK VZORCŮ. V řádku vzorců je vidět jen kód vybraného kroku. Nový krok lze vložit tlačítkem fx (defaultně obsahuje jen odkaz na předchozí krok, takže vlastně nic nemění). • V Rozšířeném editoru – karta DOMŮ nebo ZOBRAZENÍ – ROZŠÍŘENÝ EDITOR, viz Obrázek 7–11. Rozšířený editor vždy zobrazuje kód jazyka M pro celý postup a ne jen pro jeden krok. V Excelu 365 již editor při psaní napovídá. Obrázek 7–11 Rozšířený editor. Zdroj: autor (2022) Jazyk M je funkcionální jazyk podobně jako DAX, vše v kódu jazyka M je tedy volání funkcí. Seznam všech funkcí získáte, pokud do Power Query zadáte vzorec =#shared. let Zdroj = #shared in Zdroj Všechny kroky použitého postupu viditelné v panelu NASTAVENÍ DOTAZU jsou uloženy v jazyce M. Jedná se o příkaz let obsahující dva výrazy: • let – obsahuje jednotlivé kroky postupu oddělené čárkami, každý krok postupu (výraz, expression) uloží svůj výstup (hodnotu, value) do proměnné, která je typicky parametrem pro další krok, Import a transformace dat pomocí Power Query 123 Analýza dat v Microsoft Excelu • in – obsahuje návratovou hodnotu celého dotazu, typicky poslední proměnná výrazu let (ale může to být i jiná proměnná nebo výraz). Při vytváření dotazu M v editoru dotazů postupujte takto: • Vytvořte řadu kroků dotazu, které začínají výrazem let. Každý krok je definován názvem proměnné kroku. Proměnná jazyka M může obsahovat mezery, pokud na začátku názvu proměnné použijete znak # a název uzavřete do uvozovek, např. #"název kroku". • Každý krok dotazu se odvozuje z předchozího kroku odkazem na krok podle názvu jeho proměnné, viz např. následující import dat ze sešitu Excelu. • K výstupu dotazu slouží výraz in. Obecně se poslední krok dotazu používá jako konečný výsledek dotazu. Dotaz v jazyce M se skládá z: • Výrazů – výrazy vzorců, výraz let atd. • Hodnot – jsou vráceny jednotlivými výrazy. Hodnota může být primitivního datového typu (hodnota tvořena jednou částí – číslo, text, null atd.) nebo funkce (hodnota, která při vyvolání s parametry vygeneruje novou hodnotu) nebo strukturovaného datového typu (nejčastější strukturovaná hodnota je tabulka, dotazy nejčastěji vrací tabulku). Pokud vyhodnocením výrazu nemůže vzniknout hodnota, vznikne chyba. • Proměnné – do proměnných se ukládají hodnoty výrazů. Příklad 7–24 – vytvořte prázdný dotaz pomocí DOMŮ – NOVÝ ZDROJ – PRÁZDNÝ DOTAZ a změňte jej na dotaz HelloWorld, který vrátí text "Hello World" převedený na velká písmena. Řešení pro Příklad 7–24: let Zdroj = Text.Upper("Hello World") in Zdroj 130 Kapitola 7 2023 V. Novák in Zdroj{0}[Fruit] //nebo Zdroj{[Sales=1]}[Fruit] Příklad 7–29 – prozkoumejte kód jazyka M importu dat z Excelu (7_ZdrojExcel.xlsx) let Zdroj = Excel.Workbook(File.Contents("C:\ZdrojExcel.xlsx"), null, true), Tabulka = Zdroj{[Item="StrukturovanaTabulka",Kind="Table"]}[Data], #"Změněný typ" = Table.TransformColumnTypes(Tabulka,{{"Prodejce", type text}, {"Druh", type text}, {"Produkt", type text}, {"Počet kusů", Int64.Type}, {"Cena / ks", Int64.Type}, {"Datum prodeje", type date}}) in #"Změněný typ" Excel.Workbook() – funkce vracející tabulky z listů sešitu Excelu jako tabulku. Tabulka = Zdroj{[Item="StrukturovanaTabulka",Kind="Table"]}[Data] – vezme tabulku Zdroj, z ní vezme záznam, kde hodnota ve sloupci Item="StrukturovanaTabulka" a ve sloupci Kind="Table", a z tohoto záznamu vrátí hodnotu sloupce Data, kde je samotná tabulka s daty, a ta se uloží do proměnné Tabulka. Všechny sloupce jsou typu any. Table.TransformColumnTypes() – funkce, která změní typy sloupců a změněnou tabulku vrátí. 7.5 Řešení příkladu z úvodní kapitoly Power Query je nástroj pro import a transformace dat, takže to asi není optimální nástroj pro náš příklad četností čísel, ale kupodivu i pomocí Power Query lze tyto četnosti spočítat. Za tímto účelem je možné využít transformaci s názvem SESKUPIT PODLE. Použijte následující postup: • Načtěte do Power Query data z tabulky pomocí tlačítka Z TABULKY NEBO OBLASTI na kartě DATA. • V Power Query přidejte další transformaci pomocí tlačítka SESKUPIT PODLE na kartě DOMŮ. Dialogové okno nastavte následujícím způsobem, viz obrázek Obrázek 7–12. Import a transformace dat pomocí Power Query 131 Analýza dat v Microsoft Excelu Obrázek 7–12 Nastavení transformace Seskupit podle. Zdroj: autor (2022) • Pro pořádek můžeme ještě transformovanou tabulku seřadit podle sloupce Číslo pomocí tlačítka SEŘADIT VZESTUPNĚ na kartě DOMŮ, není to ale nutné. • Na závěr transformovanou tabulku načteme do Excelu tlačítkem ZAVŘÍT A NAČÍST na kartě DOMŮ. 133 Přílohy Příloha 1 Tabulky k procvičování příkladů ve formě sešitů Excelu dostupných ke stažení na adrese: https://drive.google.com/file/d/1MhTa83LN8yrW6JWXbM_mnY7bv5sW OlI7/view 135 Literatura ALEXANDER, M., KUSLEIKA, D. (2019). Excel 2019 Bible. Indianapolis: Wiley. FERRARI, A., RUSSO, M. (2017). Analyzing data with Microsoft Power BI and Power Pivot for Excel. Redmond: Microsoft Press. FERRARI, A., RUSSO, M. (2015). The definitive guide to DAX: business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI. Redmond: Microsoft Press. LASÁK, P. (2021). Praktické použití funkcí Excelu. Praha: Grada Publishing. LAURENČÍK, M. (2020). Excel 2019: práce s databázemi a kontingenčními tabulkami. Praha: Grada Publishing. Další zdroje: Microsoft (2022). Nápověda a výuka pro Excel. [Online], [cit. 1. 9. 2022]. Dostupné na www: <https://support.microsoft.com/cs-cz/excel>. Microsoft (2022). Funkce Excelu (podle abecedy). [Online], [cit. 1. 9. 2022]. Dostupné na www: <https://support.microsoft.com/cs-cz/office/funkce-excelu-podle-abecedyb3944572-255d-4efb-bb96-c6d90033e188>. Microsoft (2022). Informace o jazyce Data Analysis Expressions (DAX). [Online], [cit. 1. 9. 2022]. Dostupné na www: <https://docs.microsoft.com/cs-cz/dax/>. Microsoft (2022). Jazyk vzorců Power Query M. [Online], [cit. 1. 9. 2022]. Dostupné na www: <https://docs.microsoft.com/cs-cz/powerquery-m/>. 137 Seznam tabulek Tabulka 2-1 Konstanty funkce SUBTOTAL .................................................................. 12 Tabulka 2-2 Vlastní formát čísla ..................................................................................... 25 Tabulka 3-1 Základní statistické funkce ......................................................................... 33 Tabulka 3-2 Základní statistické funkce a jejich ekvivalenty s podmínkami .................. 35 Tabulka 4–1 Nejčastější strukturované odkazy ............................................................... 59 Tabulka 7-1 Primitivní datové typy jazyka M............................................................... 124 139 Seznam obrázků Obrázek 1–1 Výpočet četností ze seznamu čísel ............................................................... 3 Obrázek 2–1 Tabulka typu seznam ................................................................................... 5 Obrázek 2–2 Tabulka Excelu, která není seznamem......................................................... 6 Obrázek 2–3 Transformovaná tabulka (necelá) z tabulky obrázku 2-2 do seznamu ......... 7 Obrázek 2–4 Kontingenční tabulka vytvořená na základě seznamu z Obrázku 2-3 ......... 7 Obrázek 2–5 Dialogové okno Seřadit ............................................................................... 8 Obrázek 2–6 Dialogové okno Vlastní seznamy ................................................................ 9 Obrázek 2–7 Příklad použití automatického filtru .......................................................... 11 Obrázek 2–8 Řešení pro Příklad 2–4 .............................................................................. 13 Obrázek 2–9 Dialogové okno Rozšířený filtr.................................................................. 14 Obrázek 2–10 Řešení pro Příklad 2–5............................................................................. 14 Obrázek 2–11 Dialogové okno Ověření dat, karta Nastavení ......................................... 15 Obrázek 2–12 Dialogové okno Ověření dat, karta Zpráva při zadávání a její praktická realizace .......................................................................................................................... 16 Obrázek 2–13 Dialogové okno Ověření dat, karta Chybové hlášení a její praktická realizace .......................................................................................................................... 17 Obrázek 2–14 Dotaz Excelu na výmaz více různých typů ověření dat v označené oblasti ........................................................................................................................................ 18 Obrázek 2–15 Řešení pro Příklad 2–7............................................................................. 19 Obrázek 2–16 Dialogové okno Odebrat duplicity ........................................................... 20 Obrázek 2–17 Dialogové okno Upozornění na odebrání duplicity ................................. 20 Obrázek 2–18 Řešení pro Příklad 2–8............................................................................. 21 Obrázek 2–19 Dialogové okno Souhrny a výsledek použití nástroje Souhrn ................. 22 Obrázek 2–20 Řešení pro Příklad 2–9............................................................................. 23 Obrázek 2–21 Dialogové okno Formát buněk ................................................................ 24 Obrázek 2–22 Řešení pro Příklad 2–10 ........................................................................... 27 Obrázek 2–23 Dialogové okno Nové pravidlo formátování ........................................... 28 Obrázek 2–24 Dialogové okno Správce pravidel podmíněného formátování ................. 29 Obrázek 2–25 Řešení pro Příklad 2–11 ........................................................................... 29 Obrázek 2–26 Dialogové okno Vzhled stránky, karta List ............................................. 31 Obrázek 2–27 Nastavení souhrnu ................................................................................... 32 Obrázek 3–1 Požadované výpočty příkladu .................................................................... 34 Obrázek 3–2 Řešení pro Příklad 3–1 .............................................................................. 34