V dnešním videu si představíme čtyři nové excelové funkce, které jsou dostupné ve verzích Excelu Microsoft 365. Slučování tabulek, odstraňování nechtěných hodnot nebo výběr pouze potřebných sloupců? S novými dynamickými funkcemi nic z toho není problém. K funkcím jako UNIQUE, FILTER nebo SORT, o kterých máme na Akademii Excelu rovněž videa si tak dnes přidáme další čtyři užitečné funkce jako SROVNAT.SVISLE, ZVOLIT.SLOUPCE, funkce VZÍT nebo funkce ZAHODIT.
Excelový soubor ke stažení
SROVNAT.SVISLE / VSTACK
Funkce SROVNAT.SVISLE, anglicky funkce VSTACK, je jednou z nejužitečnějších funkcí, kterou Microsoft v Excelu spolu s funkcí FILTER, kdy představil. Tato funkce dovoluje sloučit jednotlivé oblasti do jedné, a to pod sebe. To znamená, že se skvěle hodí pro slučování tabulek.
V příkladu máme tři tabulky, ve kterých máme informace o datumu, produktu, počtu prodaných kusů a tržbě. Tyto tři tabulky potřebujeme kvůli dalším výpočtům sloučit do jedné tabulky. K tomu můžeme použít novou dynamickou funkci SROVNAT.SVISLE.
Klikneme do první buňky nové tabulky, a napíšeme funkci SROVNAT.SVISLE. Ve funkci SROVNAT.SVISLE se označují pouze oblasti, které chceme slučovat. Takže nejprve označíme první tabulku, napíšeme středník a následuje druhá oblast, tedy druhá tabulka. Středník a třetí tabulka. Když máme všechna data označená, tak funkci ukončíme a potvrdíme. Funkce SROVNAT.SVISLE sloučila všechny tabulky dohromady.
Stejně jako ostatní dynamické funkce, ani funkce SROVNAT.SVISLE nepřenáší původní formát buněk, takže musíme přenést formát. Můžeme označit celý první řádek a kliknout na ikonu kopírování formátu a formát přenést na všechny buňky ve sloučené tabulce.
Aby byla tabulka dokonalá, tak bychom ji ještě mohli seřadit podle datumu, tak, aby nejstarší datum bylo nahoře. Funkci SROVNAT.SVISLE zabalíme do funkce SORT, kde polem je funkce SROVNAT.SVISLE, index řazení bude jednička, jelikož chceme tabulku seřadit podle datumu, což je první sloupec sloučené tabulky a tabulku chceme seřadit vzestupně, takže jednička. Funkci ukončíme a potvrdíme a sloučená tabulka se seřadila dle sloupce s datumem.
Funkce je samozřejmě plně dynamická, takže pokud něco změníme ve zdrojových tabulkách, tak se jakákoliv změna okamžitě propíše do sloučené tabulky.
ZVOLIT.SLOUPCE / CHOOSECOL
Další funkce, kterou si představíme je funkce ZVOLIT.SLOUPCE, anglicky funkce CHOOSECOL. Funkce ZVOLIT.SLOUPCE dovoluje vybrat sloupce z datové sady, se kterými chceme pracovat. V příkladu máme zdrojovou tabulku, která má celkem čtyři sloupce. Ve výsledné tabulce chceme ovšem zobrazit pouze dva sloupce, sloupec s datumem a tržbou. To znamená, že nepotřebujeme sloupce s produktem a počtem prodaných kusů.
Použijeme funkci ZVOLIT.SLOUPCE. Do první buňky napíšeme funkci ZVOLIT.SLOUPCE, ve které se nejprve označuje zdrojová datová sada, což je celá zdrojová tabulka. A následně se vyplňují pořadová čísla sloupců, ze kterých chceme složit tabulku. V tabulce jsme chtěli mít datum a tržbu, takže napíšeme jedničku, jako první sloupec s datumem a následuje čtyřka, jelikož tržba je čtvrtým sloupcem zdrojové tabulky. To je celé. Funkci ukončíme a potvrdíme a funkce ZVOLIT.SLOUPCE doručila tabulku o dvou sloupcích.
Stejně jako ostatní dynamické funkce nepřenáší ani tato funkce formát, takže musíme formát upravit. Označíme sloupec s datumem a uplatníme na něho formát datumu. Rovnou změníme i formát u sloupce s tržbami a změníme formát na českou měnu bez desetinných míst.
Výhodou použití této funkce je, že je opět dynamická. Pokud tedy změníme jakákoliv data ve zdrojové tabulce, tak se změna okamžitě propíše i do nové tabulky, takže máte vždy jistotu, že pracujete s aktuálními daty.
VZÍT / TAKE
V Microsoft 365 je nově přidaná rovněž funkce VZÍT, anglicky funkce TAKE. Jak její název napovídá, tak funkce TAKE dovoluje z datové sady vzít vybrané řádky nebo sloupce. Základní použití funkce si ukážeme na následujícím příkladu. Na listu máme zdrojovou tabulku, ze které bychom potřebovali do vedlejší tabulky vybrat pouze tři horní řádky. Nechceme řádky ale kopírovat, celé řešení chceme dynamické, aby se případná jakákoliv změna propsala i do nové tabulky.
Použijeme funkci VZÍT. Do první buňky napíšeme funkci VZÍT, kde se nejprve označuje oblast dat. To je celá zdrojová tabulka. Následuje parametr řádky. Tedy které řádky chceme vzít do nové tabulky. Chtěli jsme první tři řádky, takže napíšeme trojku. Funkci potvrdíme a funkce VZÍT do nové tabulky doručila první tři řádky ze zdrojové tabulky.
Opět se nepřenesl formát, takže zkopírujeme formát ze zdrojové tabulky a přeneseme ho na novou tabulku.
Stejně tak bychom mohli vzít pouze poslední tři řádky. V takovém případě bychom se k funkci vrátili a před počet řádků bychom napsali mínus. Mínus zajistí, že se vrátí spodní tři řádky ze zdrojové tabulky.
To samé můžeme udělat i se sloupci. Řekněme, že chceme do další tabulky vrátit poslední tři sloupce ze zdrojové tabulky, v tabulce tedy nechceme mít sloupec s datumem. Použijeme funkci VZÍT. Jako první označíme celou zdrojovou tabulku. Následuje počet řádků. Pokud bychom chtěli vrátit celou tabulku se všemi řádky, tak tento parametr přeskočíme. Řekněme, ale že chceme v tabulce pouze prvních pět řádků, takže vyplníme pětku. Poslední parametr jsou sloupce. Chceme vrátit poslední tři sloupce, takže vyplníme číslo mínus tři. Funkci ukončíme a potvrdíme a funkce VZÍT vrátila ze zdrojové tabulky prvních pět řádků a poslední tři sloupce.
Kdybychom chtěli první tři sloupce, tak smažeme mínus před počtem sloupců.
Funkci VZÍT v praxi využijete třeba v následujícím příkladu. Ze zdrojové tabulky chceme do druhé tabulky vrátit tři nejlepší produkty dle tržeb. Funkci VZÍT umí vracet první nebo poslední řádky nebo sloupce ze zdrojových dat. Abychom funkci VZÍT mohli použít, tak musíme nejprve zdrojovou tabulku seřadit. Použijeme tedy funkci SORT a seřadíme zdrojovou tabulku od nejvyšších tržeb po nejnižší. Ve funkci SORT označíme zdrojová data, řadit chceme podle tržeb, což je čtvrtý sloupec a tabulku chceme seřadit sestupně, takže mínus jedna. Teď máme tabulku seřazenou dle tržeb a můžeme pomocí funkce VZÍT nechat pouze první tři řádky, což jsou produkty s nejvyššími tržbami. Takže funkci SORT zabalíme do funkce VZÍT, kde polem je funkce SORT a chceme nechat pouze tři řádky, takže do parametru řádky napíšeme trojku. Funkci ukončíme a potvrdíme a máme vrácené tři produkty s nejvyššími tržbami.
ZAHODIT / DROP
Podobná funkce jako funkce VZÍT je funkce ZAHODIT, anglicky funkce DROP. Zatímco funkce VZÍT nechává řádky a sloupce, tak funkce ZAHODIT dovoluje některé řádky a sloupce zahodit. Tedy odstranit z tabulky.
V příkladu máme opět zdrojovou tabulku se čtyřmi sloupci. Řekněme, že do vedlejší tabulky potřebujeme vrátit tuto tabulku, ale bez posledních tří řádků. Napsali bychom funkci ZAHODIT, kde nejprve označíme celou datovou sadu. Následuje parametr řádky. Jelikož chceme zahodit poslední tři řádky tabulky, tak musíme vyplnit mínus tři. To je celé. Funkci ukončíme a potvrdíme a vrátila se celá zdrojová tabulka bez posledních tří řádků.
Pokud bychom chtěli zahodit první tři řádky, tak bychom napsali počet řádků jako kladné číslo.
A kde se tato funkce bude hodit v praxi? Řekněme, že máme ve zdrojové tabulce tři extrémní hodnoty, které nám kazí celkový průměr. Potřebujeme spočítat průměrnou tržbu v tabulce, ale bez těchto tří extrémních hodnot.
Využijeme tedy kombinaci několika excelových funkcí. Začneme tím, že zdrojovou tabulku seřadíme, a to od nejvyšších tržeb po nejnižší. Takže začneme s funkcí SORT, kde označíme zdrojovou tabulku, řadit chceme podle sloupce tržeb, což je čtvrtý sloupec a chceme tabulku seřadit sestupně, takže mínus jedna. Potvrdíme a máme zdrojovou tabulku seřazenou. Teď máme první tři řádky s extrémními hodnotami.
Můžeme tedy použít funkci ZAHODIT. Funkci SORT zabalíme do funkce ZAHODIT, ve které je polem funkce SORT a chceme zahodit první tři řádky tabulky, takže do parametru řádky napíšeme trojku. Potvrdíme a funkce ZAHODIT odstranila z datové sady první tři řádky s extrémními hodnotami.
Pro výpočet průměru potřebujeme jen sloupec s tržbami, takže se k funkci ZAHODIT vrátíme a zahodíme z tabulky i první tři sloupce, které nepotřebujeme. Funkci potvrdíme a vrátil se pouze sloupec s tržbami bez extrémních hodnot.
Teď to můžeme zabalit do funkce PRŮMĚR. Funkce PRŮMĚR doručí průměr z těchto zbylých hodnot. Potvrdíme a máme průměrnou hodnotu tržeb bez extrémních hodnot.
Jedna odpověď
😊👍