Věděli jste, že v Excelu můžete vytvářet funkční QR kódy, zobrazovat vývoj ceny akcií, prognózy nebo tvořit grafy, které nejsou mezi standardními grafy v Excelu? Nebo třeba počítat a vyhledávat v Excelu bez použití jediné excelové funkce? V dnešním videu si ukážeme pět věcí, které jste možná nevěděli, že jsou v Excelu možné.
Excelový soubor ke stažení:
QR kódy
Předplatitelé Microsoft 365 mohou velmi jednoduše vytvořit v Excelu QR kódy pomocí funkce OBRÁZEK neboli funkce IMAGE. Tato funkce dovoluje do buňky vkládat obrázek podle zdroje, který ve funkci určíme. Nicméně můžeme pomocí této funkce vytvářet i čárové nebo QR kódy. V tabulce máme několik kódů. Z těchto kódů chceme vytvořit QR kódy. Použijeme funkci OBRÁZEK. Napíšeme funkci a pro vytvoření QR kódu potřebujeme vyplnit pouze zdroj. Do zdroje vložíme odkaz na webovou stránku barcode (https://barcodeapi.org/api/qr-code/). Tento odkaz vrátí do buňky obrázek QR kódu. Jakmile do funkce vložíme odkaz, tak na nás vyskočí upozornění, že vkládáme aktivní obsah, což potvrdíme. Zdroj funkce musí být v uvozovkách, takže před a po webovou adresu napíšeme uvozovky. A následně to spojíme pomocí znaku ampersandu s buňkou kódu. A nakonec ukončíme funkci. Funkci potvrdíme a do buňky se vložil čárový kód.
A co kdybychom chtěli do QR kódu schovat třeba odkaz na webovou stránku? I to můžeme udělat. V buňce máme odkaz na webovou stránku. Použijeme stejný postup. A máme vytvořený QR kód s odkazem na webovou stránku.
Čárové kódy fungují nejen pro skenery, ale funkčnost můžete ověřit i telefonem. Namiřte foťák v telefonu na kód a kód se vám načte a přesměruje vás na určený web.
Python grafy
Pokud jste předplatiteli služby Microsoft 365, tak můžete v Excelu vytvářet grafy, které nejsou ve standardní nabídce Excelu. Řeč je třeba o krabicovém a houslovém grafu nebo třeba histogramu. Stačí vám k tomu pouze malá znalost kódu Pythonu. A pokud se nechcete učit Python, tak vám s grafy velmi lehce pomůže jakákoliv AI, ať už ChatGPT, Claude nebo třeba COPILOT.
V příkladu máme fiktivní statistiku bytů o různých dispozicích v městských obvodech v Praze. Kromě ceny máme v tabulce dopočítanou i průměrnou cenu za metr čtvereční bytu. Řekněme, že chceme tato data zobrazit v krabicovém grafu, ale tento typ grafu není v Excelu podporovaný. To ale nevadí. S minimální znalostí Python si tento graf v Excelu zobrazíte.
První, co musíme udělat, je načíst data do datového rámce v Python neboli v angličtině do data framu. To uděláme tak, že do buňky napíšeme =PY a potvrdíme tabulátorem. Tím se přepneme do zobrazení python funkce, což je jiné zobrazení, než jaké známe od ostatních funkcí v Excelu. A v tomto zobrazení můžeme začít psát Python kód. Abychom tabulku načetli do datového rámce, tak musíme napsat rovná se a xl a v závorkách označit tabulku, ale musíme ji označit i se záhlavím, takže na ni klikneme dvakrát. A aby se nám s datovým rámcem lépe pracovalo, tak mu dáme název df, což v angličtině znamená data frame. Vy ale můžete použít jakýkoliv jiný název. Každá python funkce se potvrzuje stisknutím CTRL a ENTER. Tím se do buňky vloží datový rámec, se kterým teď můžeme pracovat v jiných python funkcích.
Chceme tvořit krabicový graf, takže se opět v jiné buňce přepneme do zobrazení Python. Pro vytvoření krabicového grafu stačí použít funkci sns.boxplot. Sns v tomto případě znamená znamená seaborn knihovnu, pomocí které můžeme zobrazit krabicový graf. A boxplot není nic jiného než anglický název pro krabicový graf.
V této funkci se nejprve odkážeme na datový rámec. Tomu jsme dali zkratku df. Následně musíme určit, které sloupce chceme zobrazit na ose x a ose y.
Funkci python potvrdíme stisknutím kláves CTRL a ENTER. Po chvilce se v buňce zobrazí obrázek, který můžeme zvětšit a vložit do sešitu. A máme krabicový graf z dat pomocí jednoduché Python funkce. Graf se vloží ve standardním barevném provedení, pokud chceme u grafu jinou barvu, tak se můžeme vrátit do funkce a vložit hex kód barvy.
A co kdybychom chtěli data zobrazit v histogramu? Rovněž typ grafu, který se standardně v Excelu nevyskytuje. Opět použijeme Python. Tentokrát použijeme ale knihovnu Matplotlib.
A co třeba houslový graf?
Grafy v Python jsou skvělé pro nejrůznější analýzy, a s trochou kódu můžete vytvářet grafy, které nejsou typické pro práci v Excelu, ale zobrazí vaše data v úplně jiném kontextu. Můžete vytvořit třeba následující graf, který zobrazuje vedle sebe průměrné ceny za byty podle dispozice a městských obvodů.
Každý graf lze formátovat pomocí Python kódu a funkcí, což je opět něco s čím vám může pomoci AI, třeba ChatGPT, Claude nebo přímo COPILOT v Excelu.
STOCKHISTORY a datové typy
Věděli jste, že si v Excelu můžete zobrazit vývoj akcií nebo informace o akciích dle vašeho přání? Stačí použít funkci datové typy nebo funkci STOCKHISTORY, které jsou dostupné rovněž pro předplatitele Microsoft 365. Začneme tím, že chceme získat informace třeba o akcii Microsoft. Do buňky napíšeme Microsoft a nejprve změníme buňku na datový typ akcie. Z obyčejného slova se stal datový typ, se kterým teď můžeme pracovat a zobrazovat o něm informace.
Můžeme si třeba nechat zobrazit adresu centrály.
Nebo si můžeme nechat zobrazit tržní kapitalizaci.
A nebo třeba ukazatel P/E.
A co když nás zajímá cena akcie? Můžeme buď použít datový typ, nebo funkci STOCKHISTORY. Ve funkci STOCKHISTORY označíme akcii a stanovíme den, pro který chceme cenu akcie zobrazit. A dále si určíme, v jaké podobě se má výsledek vrátit. Můžeme si vybrat, zda chceme vrátit pouze hodnotu nebo hodnotu včetně záhlaví a jaké všechny informace chceme vrátit.
A co kdybychom chtěli vidět vývoj ceny akcie? V takovém případě vyplníme kromě počátečního datumu i koncové datum.
Prognózy
Věděli jste, že v Excelu můžete vytvářet i prognózy z vašich dat? A stačí vám k tomu verze Excelu alespoň 2016. Prognóza znamená, že se na základě historických dat snažíme odhadnout, jak se budou hodnoty vyvíjet v budoucnu. Typicky můžeme prognózovat například tržby firmy, návštěvnost webu, počet objednávek, spotřebu energií, ceny produktů nebo třeba vývoj ceny akcie.
V našem příkladu budeme pracovat s datovým setem měsíčních tržeb firmy za posledních pět let. Máme tady jednoduchou tabulku se dvěma sloupci. První sloupec obsahuje datum. Druhý sloupec obsahuje tržby za daný měsíc. V Excelu máme pro prognózy několik možností. Nejjednodušší možností je ale použít nástroj Prognóza, anglicky Forecast Sheet. Tento nástroj najdeme na kartě Data.
Stačí kliknout do tabulky a na kartě Data vybrat Prognóza.
Vyskočí na nás okno, kde vidíme náhled na graf s prognózou. V základu se prognóza zobrazuje ve spojnicovém grafu, ale můžeme si vybrat i sloupcový graf. Jediná věc, kterou teoreticky musíte nastavit, je koncové datum, tedy na jak dlouho dopředu chcete dělat prognózu. Zde je potřeba říct, že čím delší je horizont pro prognózu, tím větší je šance, že prognóza bude nepřesná. Takže zvolíme rozumný horizont 6 měsíců. V ostatních možnostech máte další možnosti, jak prognózy nastavit. Nastavení jednotlivých částí je nad rámec tohoto videa, ale pokud by vás zajímalo video pouze o prognózách, tak mi dejte vědět v popisku tohoto videa.
Potvrdíme vytvoření a Excel automaticky vytvoří nový list s grafem a dopočítanými budoucími hodnotami. Toto je velmi rychlý způsob, jak vytvořit prognózu bez psaní vzorců. Excel se podívá na historická data, vytvoří odhad budoucího vývoje a zároveň zobrazí i takzvaný interval spolehlivosti. Interval spolehlivosti je oblast kolem prognózy, která ukazuje možné rozpětí budoucích hodnot. Jinými slovy, Excel neříká: přesně tolik budou tržby. Excel spíše říká: pokud bude minulý vývoj pokračovat podobně, budoucí hodnota se pravděpodobně může pohybovat někde v tomto rozmezí.
Modrá čára v grafu jsou historické údaje, které vycházejí z našich dat. Z těchto dat Excel hledá trendy, sezónnost a opakující se vzorce. Oranžové čáry jsou prognózy. Excel zde dopočítává pravděpodobný budoucí vývoj na základě minulých hodnot. Silná oranžová čára je hlavní prognóza. Jedná se o nejpravděpodobnější scénář vývoje. Horní a dolní oranžová čára jsou intervaly spolehlivosti. Což jsou hodnoty, mezi kterými se pravděpodobný scénář bude nejspíše pohybovat.
Důležité ale je říct jednu věc. Prognóza není věštění budoucnosti. Excel neví, co se stane příští měsíc nebo za 6 měsíců. Excel pouze vezme historická data, najde v nich určitý vzorec, trend nebo sezónnost a na základě toho dopočítá pravděpodobný budoucí vývoj. Proto vždy platí, že kvalita prognózy závisí na kvalitě vstupních dat. Pokud máme špatná, neúplná nebo chaotická data, bude špatná i samotná prognóza.
Power Query
O Power Query většina z vás už slyšela. Alespoň doufám. Protože pokud ne, tak přicházíte o nejlepšího pomocníka v Excelu, kterého si můžete přát. Power Query je dostupné ve všech verzích Excelu od verze 2016, takže ho opravdu může používat doslova každý. Otázkou zůstává, jak efektivně ho používáte.
V tomto příkladu jsme napojeni pomocí Power Query na složku, kam si ukládáme zdrojové soubory. V našem příkladu jsou prodejní data od tří zákazníků. V tabulkách máme produkty uvedené pod kódovým označením a máme zde uvedené počty prodaných kusů.
Informace o zařazení produktů nebo cenách máme ale v jiné tabulce. Naším úkolem je spočítat, jaké byly tržby v jednotlivých měsících a jaké byly tržby pro jednotlivé kategorie produktů. Když bychom na tyto otázky chtěli odpověď v Excelu, znamenalo by to spoustu kopírování, vyhledávání a použití několika funkcí. Zkusíme to jinak.
Pomocí Power Query jsme sloučili všechny zdrojové soubory ze složky do jedné tabulky. Zároveň jsme nahráli tabulku s produkty, kde máme uvedené dodatečné informace o produktech.
Nejprve upravíme tabulku s Daty a to sloupec, kde máme zákazníky. Vyextrahujeme název zákazníka. Takže označíme sloupec Name a na kartě Transformace vybereme Extrahovat a Extrahovat text mezi oddělovači. Počáteční oddělovač je podtržítko a koncový oddělovač je podtržítko a rok.
Následně z názvu zákazníka odstraníme podtržítko tím, že ho nahradíme mezerou. Takže označíme sloupec Name a na kartě Transformace vybereme Nahradit hodnoty. Nakonec sloupec přejmenujeme na Zákazníka.
Následně musíme do tabulky Data dohledat ceny produktů. To uděláme tak, že označíme tabulku Data a na kartě Domů vybereme Sloučit dotazy. Chceme propojit tabulku Data a tabulku Produkty a o přes sloupec Kód produktu.
Z dohledaného sloupce rozbalíme pomocí dvojité šipky v záhlaví kategorii produktu a cenu.
Následně musíme dopočítat tržbu, což uděláme tak, že mezi sebou vynásobíme sloupce Počet a Tržba. Takže označíme tyto dva sloupce a na kartě Přidání sloupce vybereme Standardní výpočty a Součin.
Teď máme vše potřebné k tomu, abychom odpověděli na otázky. A jelikož chceme zachovat i původní data, tak nejprve vytvoříme odkaz na zdrojová Data a tento nový dotaz pojmenujeme jako Report 1. Zde ještě vyextrahujeme do nového sloupce ze sloupce s datumy název měsíce.
Teď již stačí seskupit data podle sloupce Název měsíce. Nový sloupec pojmenujeme jako Tržba celkem a chceme v něm spočítat součet ze sloupce Tržba.
Po potvrzení se vrátí sloučená tabulka. A my máme odpověď na první otázku.
Pro odpověď na druhou otázku vytvoříme druhý odkaz ze zdrojových Dat a pojmenujeme ho jako Report 2. Zde potřebujeme seskupit tržby podle sloupce Kategorie produktu.
Po potvrzení se tabulka seskupí podle kategorie produktů a celkových tržeb. Tabulku můžeme i seřadit podle výše tržeb a to přes filtr v záhlaví, kde vybereme Seřadit sestupně.
Když jsme s reporty hotovi, tak tabulky nahrajeme zpět do Excelu přes kartu Domů, Zavřít a Načíst.
