V dnešním videu si představíme praktické využití funkcí UNIQUE, SORT, SORTBY, FILTER, XLOOKUP, XMATCH, SEQUENCE a RANDARRAY. Tedy nových funkcí v Excelu, které pro uživatele Excelu 2021 a Office 365 nejsou až tak nové. Některé z těchto funkcí byly veřejnosti poprvé představeny už na konci roku 2018, následně se staly součástí Excelu 2021 a Office 365.
Dnešní video navazuje na přecházející videa 20 excelových funkcí, které musí znát každý uživatel Excelu a 5 excelových funkcí, které z vás udělají profíka.
Nové jsou zejména pro ty, co ještě stále nepřešli na nejnovější verze Excelu, kde by tyto skvělé nové funkce mohli využívat. Pojďme si ukázat, v čem vám tyto nové funkce usnadní život.
Excelový soubor ke stažení
Excelové funkce, které vám změní život
To, co je na těchto nových funkcích zvláštní je to, že tyto funkce, stejně jako většina nových funkcí v Excelu Office 365, pracují s jiným výpočtovým systémem. Jedná se ve své podstatě o maticové funkce, které se ve starších verzích Excelu musely potvrzovat kombinací kláves CTRL+SHIFT a ENTER. Nové verze Excelu již toto omezení nemají a vzorce se tzv. rozlévají do buněk po potvrzení klávesou ENTER. Tyto funkce poznáte podle toho, že funkce tzv. žije pouze v první buňce vzorce a nikde jinde a do ostatních buněk je pouze rozlitá. Stejně tak tyto funkce poznáte podle toho, že když na funkci kliknete, tak se oblast funkce znázorní modrým orámováním.
Tyto nové excelové funkce vám neskutečně ulehčí práci v Excelu. Pro ukázku toho, jak jsou nové funkce skvělé, se podívejte na následující srovnání. Máme zde tabulku (A3:D12), kterou bychom potřebovali vyfiltrovat pouze pro Produkt A a ještě bychom výslednou tabulku potřebovali dynamicky seřadit dle výše tržeb.
Bez nových funkcí v Excelu, to znamená Excely, které jsou starší než 2019, bychom museli nejprve vytvořit tabulku s filtrem pro produkt A (G3:K6). Funkce pro filtrování je uvedená v buňce G9. Následně bychom museli vytvořit druhou tabulku (M3:Q6), kde bychom nově vytvořenou tabulku seřadili pomocí funkcí, tak aby řazení bylo dynamické. K seřazení bychom museli použít kombinace funkcí RANK, INDEX & POZVYHLEDAT.
S novými excelovými funkcemi vám stačí použít dvě funkce, a to funkci SORT a FILTER, tak jak vidíte v tomto příkladu (H13:K16).
Pojďme si ale nové excelové funkce představit na praktických příkladech.
Funkce UNIQUE
Funkce UNIQUE je funkce, která vrátí ze seznamu hodnot unikátní hodnoty. To znamená, že vrátí seznam jedinečných hodnot. Tato funkce tak nahrazuje velmi komplikované kombinace funkcí, které jsme museli tvořit, když jsme ve starších verzích Excelu chtěli dostat ze seznamu hodnot jedinečné záznamy bez duplicit.
V příkladu máme seznam produktů, ve kterém se vyskytují duplicitní záznamy. Z tohoto seznamu chceme dostat seznam jedinečných produktů. Vedle tabulky napíšeme funkci UNIQUE, kde jako parametr označíme celý seznam hodnot.
= UNIQUE (pole; [sloupce]; [právě jednou])
Funkci potvrdíme a na jedno kliknutí se vrátil seznam jedinečných produktů. Funkce je samozřejmě dynamická, takže jakmile v seznamu změníme hodnoty, například přidáme produkt, tak se celá funkce přepočítá a vrátí aktuální seznam jedinečných záznamů.
Funkce SORT
Další skvělou funkcí je funkce SORT, což v angličtině znamená seřadit. Jak název napovídá, tak se jedná o excelovou funkci, která vám dovolí seřadit hodnoty. Funkce SORT má jeden povinný parametr, kterým je pole, tedy pole hodnot. Další parametry jsou nepovinné.
= SORT (pole; [index řazení]; [pořadí řazení]; [podle sloupce])
V následujícím příkladu máme seznam číselných hodnot, které bychom chtěli seřadit. Vedle tabulky tedy napíšeme funkci SORT, kde jako pole označíme celý seznam hodnot (A4:A14). Pokud ostatní nepovinné parametry budeme ignorovat a funkci potvrdíme, tak se v základu čísla seřadí od nejmenší hodnoty po nejvyšší.
V dalším příkladu máme tabulku, kde máme datum, produkt, počet prodaných produktů a celkové tržby. Rádi bychom celou tabulku vedle seřadili dle výše tržeb. Vedle zdrojové tabulky máme připravenou tabulku, do které do prvního pole (F17) napíšeme funkci SORT, kde jako pole označíme celou tabulku (A17:D25). Pokud opět nic jiného nevyplníme a funkci potvrdíme, tak funkce SORT vrátí celou tabulku seřazenou dle prvního sloupce, což je datum. My jsme ale tabulku chtěli seřadit dle tržeb, takže se k funkci vrátíme a podíváme se na nepovinné parametry.
Prvním nepovinným parametrem je index řazení. Zde můžete specifikovat podle jakého řádku nebo sloupce dojde k seřazení označeného pole. Tabulku chceme seřadit dle tržeb, takže jako index řazení napíšeme čtyřku, jelikož tržby jsou čtvrtým sloupcem v tabulce. Funkci potvrdíme a tabulka se seřadila dle výše tržeb, a to od nejnižších tržeb o nejvyšší. Pokud bychom chtěli obrácené řazení, tedy od nejvyšších tržeb po nejnižší, tak musíme vyplnit další nepovinný parametr. Ještě jednou se tedy k funkcí vrátíme.
Dalším nepovinným parametrem je Pořadí řazení. Zde si můžeme vybrat, zda chceme hodnoty seřadit vzestupně nebo sestupně. Pokud tento parametr nevyplníme, tak v základu se hodnoty řadí vždy vzestupně. Pokud vyplníme -1, tak se tabulka seřadí dle tržeb sestupně, tedy od nejvyšších tržeb po nejnižší.
Posledním nepovinným parametrem je Podle sloupce. Na výběr máme seřadit dle řádků nebo sloupců. Jelikož jsou naše data v řádcích, tak bychom měli vybrat nepravda, což je rovněž základní nastavení funkce, takže pokud máte tabulku strukturovanou vertikálně, tedy do řádků, tak tento parametr nemusíme vyplňovat. Pokud bude vaše tabulka strukturovaná horizontálně, tak zde můžete vybrat seřadit dle sloupců.
Funkce SORTBY
Pokud byste potřebovali seřadit tabulku podle více sloupců, tak se vám bude hodit funkce SORTBY, což v angličtině znamená seřadit podle. Prvním povinným parametrem funkce SORTBY je pole, což je oblast, kterou chceme seřadit.
= SORTBY (pole; podle pole1; [pořadí řazení1]; [podle pole2]; [pořadí řazení2]; …)
I v tomto případě tedy označíme celou zdrojovou tabulku (A31:D39). Druhým povinným parametrem je podle pole1, tedy podle kterého pole chceme tabulku seřadit. Řekněme, že chceme tabulku seřadit opět podle tržeb (D31:D39). Na rozdíl od funkce SORT, zde nepíšeme pořadové číslo sloupce, dle kterého chceme řadit, ale musíme označit celý sloupec hodnot. Následuje nepovinný parametr pořadí řazení, kde je v základu opět nastaveno vzestupné řazení. Funkci potvrdíme. Tabulka se seřadila dle velikosti tržeb.
Jenže teď by se nám hodilo, kdyby byla tabulka seřazená nejen dle tržeb, ale i dle datumů. Vrátíme se tedy ke vzorci a budeme pokračovat dalším parametrem, což je opět podle pole2. Označíme tedy druhý sloupec, dle kterého chceme hodnoty seřadit, tedy sloupec s datumy (A31:A39). Můžeme opět zvolit, zda chceme i toto pole seřadit vzestupně nebo sestupně. Řekněme, že vybereme tentokrát sestupně. Funkci potvrdíme. Teď je tabulka stále seřazená dle velikosti tržeb, ale u duplicitních tržeb se tržby navíc seřadily dle datumů, a to sestupně.
Funkce FILTER
FILTER funkce dělá přesně to, co napovídá její název. Filtruje tabulku nebo hodnoty dle zadaných kritérií. V následujícím příkladu máme tabulku s několika produkty (A3:D12). Vedle do separátní tabulky bychom chtěli vyselektovat pouze záznamy pro Produkt A.
= FILTER (pole; zahrnuje; [pokud prázdné])
Funkce FILTER má tři parametry, přičemž povinné jsou první dva. Prvním povinným parametrem je pole, což je tabulka nebo sloupce, které chceme filtrovat. My chceme filtrovat celou tabulku, takže označíme celou tabulku (A4:D12). Druhým povinným parametrem je zahrnuje, což je logický filtr tabulky. My chceme vyfiltrovat tabulku pro Produkt A, takže označíme sloupec s produkty a napíšeme rovná se a v uvozovkách Produkt A (B4:B12=”Produkt A”).
Poslední nepovinný parametr je pokud prázdné, kde můžete specifikovat, co se má vrátit, pokud zadaný filtr nebude nalezený.
Pro teď tento parametr vynecháme. Funkci potvrdíme a funkce FILTER vrátila tabulku pouze pro produkt A.
Zkusíme napojit funkci FILTER na rozbalovací seznam. Vedle tabulek použijeme funkci UNIQUE, kde označíme produkty ze zdrojové tabulky (B4:B12). Tento seznam teď použijeme jako zdroj pro rozbalovací seznam. Nad tabulku klikneme do některého z polí (F1), na kartě Data vybereme Ověření dat a v rozbalovacím seznamu najdeme seznam. Jako zdroj označíme pomocný sloupec produktů, který jsme vytvořili pomocí funkce UNIQUE (L2:L5). Potvrdíme a nad tabulkou máme rozbalovací seznam.
Vrátíme se k funkci a napojíme funkci FILTER na rozbalovací seznam tím, že z funkce odstraníme natvrdo napsaný Produkt A a místo toho označíme buňku s rozbalovacím seznamem (B4:B12=F1).
Vrátíme se ještě jednou k funkci a vyplníme i poslední nepovinný parametr pokud prázdné. Vyplníme zde třeba Nenalezeno v uvozovkách. Pokud bychom se teď vrátili k seznamu, smazali ho a přepsali ho například na produkt Z, který ve zdrojové tabulce nemáme, a aktualizovali výběr v rozbalovacím seznamu, tak funkce FILTER vrátí slovo nenalezeno, jelikož žádný produkt Z ve zdrojové tabulce není.
Funkce XLOOKUP
Další skvělou novou funkcí je vyhledávací funkce XLOOKUP. Jedná se v podstatě o vylepšenou funkci SVYHLEDAT neboli anglicky VLOOKUP. Funkce XLOOKUP mimo jiné odstranila hlavní nevýhodu funkce SVYHLEDAT, což je její neschopnost vyhledávat směrem doleva od hledaných hodnot.
V následujícím příkladu máme zdrojovou tabulku s produkty a vedle do tabulky chceme vyhledat údaje k vybraným produktům. Všimněte si, že sloupec s produkty máme mezi počtem a tržbami, vyhledávat chceme nicméně i počet, který je od produktového sloupce nalevo. Klasická funkce SVYHLEDAT by v takovém případě nefungovala. Použijeme tedy funkci XLOOKUP.
= XLOOKUP (co; prohledat; vrátit; [pokud_nenalezeno]; [režim_shody]; [režim_vyhledávání])
Funkce XLOOKUP má tři povinné parametry, které jsou podobné všem ostatním vyhledávacím funkcím.
Prvním parametrem funkce je, co hledáme. Označíme tedy první hledaný produkt (G4). Druhý parametr je prohledat, tedy sloupec, kde hledanou hodnotu hledáme (C4:C18). Na rozdíl od funkce SVYHLEDAT se zde neoznačuje celá tabulka, ale pouze sloupec, kde hledanou hodnotu hledáme. Označíme tedy sloupec s produkty.
Posledním povinným parametrem je vrátit, tedy sloupec, ze kterého chceme vrátit hodnoty (B4:B18). Chceme nejprve vrátit počet prodaných produktů, takže označíme sloupec s počty. Funkci potvrdíme a funkce XLOOKUP vrátila správný počet k hledaným produktům, a to i přesto, že se sloupec s počtem kusů vyskytuje nalevo od sloupce s produkty.
Chceme vyhledat i tržby, takže funkci přetáhneme doprava, pokud jsme správně zafixovali buňky a funkci upravíme. Jako co hledáme označíme opět hledaný produkt (G4), prohledat je sloupec s produkty (C4:C18) a co chceme vrátit? Chceme vrátit sloupec s tržbami (D4:D18). Funkci potvrdíme a máme správně doplněné i tržby.
Vrátíme se k funkci a podíváme se ještě na nepovinné parametry. Prvním nepovinným parametrem je pokud nenalezeno. Zde můžeme specifikovat, co má funkce XLOOKUP vrátit, pokud hledaná hodnota nebude nalezena. U funkce SVYHLEDAT jsme tuto situaci většinou museli ošetřit zabalením funkce SVYHLEDAT do funkce IFERROR. Cvičně zde vyplníme, Nenalezeno v uvozovkách.
Přepneme se do dalšího parametru Režimu shody. To je velmi podobné shodě ve funkci POZVYHLEDAT, akorát s tím rozdílem, že si zde můžeme vybrat, zda chceme vybrat přibližnou shodu k nejmenší nebo nejvyšší položce. My hledáme přesnou shodu, takže vyplníme nulu, což je mimochodem základní nastavení funkce.
Posledním parametrem funkce je Režim vyhledávání. Režim vyhledávání využijete, pokud máte ve zdrojové tabulce duplicity. Pokud vyplníme 1, tak funkce začne vyhledávat od počátku tabulky, pokud vyplníte -1, tak začne vyhledávat od konce. Zkusíme vyplnit -1 a funkci potvrdíme.
Jelikož nemáme ve zdroji duplicity, tak se tabulka nezměnila, pokud ale produkt 14 změníme na produkt 3, tak tím, že jsme vyplnili v parametru Režim vyhledávání -1, tedy vyhledávat od konce, tak se vyhledaná hodnota u produktu 3 změnila. A je to proto, že funkce přiřadila hodnotu od produktu 3, ze spodu tabulky, který jsme změnili.
Zkusíme ještě vyhledat produkt, který ve zdrojové tabulce nemáme, třeba Produkt 25. A funkce vrátila slovo Nenalezeno, přesně tak, jak jsme nastavili v parametru pokud nenalezeno.
Funkce XMATCH
Funkce XMATCH je vylepšenou funkcí MATCH, neboli v českém Excelu funkce POZVYHLEDAT. Stejně jako funkce POZVYHLEDAT, tak i funkce XMATCH vyhledá relativní pozici hledané položky v rámci série hodnot. Výsledkem funkce je tak určení polohy, většinou pořadového čísla řádku nebo sloupce, na kterém se hledaná položka nachází. Základní použití si ukážeme na následující tabulce, kde potřebuje ze zdrojové tabulky vyhledat následující položku.
= XMATCH (hodnota_vyhledávání; pole_vyhledávání; [režim shody]; [režim vyhledávání])
Do buňky napíšeme funkci XMATCH, kde jsou povinné parametry stejné jako u funkce POZVYHLEDAT. Prvním parametrem funkce XMATCH je hledaná položka (G4), následuje oblast, ve které hledanou položku hledáme. Tedy sloupec s produkty (C4:C18).
Třetím parametrem je Režim shody, kde je oproti funkci POZVYHLEDAT několik rozdílů. Prvním rozdílem je to, že základní nastavení funkce je přesná shoda. Pro vyhledání přesné shody tedy nemusíme tento parametr vyplňovat a funkci můžeme potvrdit. Funkce XMATCH přiřadila k hledané položce pořadové číslo řádku, na kterém se ve zdrojové tabulce nachází. To většinou ale není to, co hledáme. Takže teď můžeme funkci XMATCH klasicky zabalit do funkce INDEX pro přirazené hledané hodnoty. Před funkci XMATCH napíšeme funkci INDEX, kde označíme hledané hodnoty (D4:D18), napíšeme středník a parametr řádky je celá funkce XMATCH. Potvrdíme a kombinace funkcí správně přiřadili hledanou hodnotu.
V tomto základním použití se funkce XMATCH v podstatě neliší od funkce POZVYHLEDAT, jediný rozdíl je v tom, že u funkce XMATCH nemusíme vyplňovat režim shody, jelikož defaultně je nastavený režim přesné shody.
Na dalším příkladu se podíváme na využití nových a upravených nepovinných parametrů. V následujícím příkladu máme tabulku tržeb po produktech, naším úkolem je najít tržby posledního měsíce. Nicméně jak si můžeme všimnout, tak zde nemáme celý název produktu, ale jen začátek kódu. Tržby jsou ve zdrojové tabulce seřazené dle měsíců, takže víme, že nejnovější měsíce jsou na konci tabulky. Zkusíme k části kódu vyhledat tržby z posledního měsíce. Rovnou to spojíme s funkcí INDEX. Napíšeme funkci INDEX, kde označíme pole, kde se nacházejí odpovědi, tedy sloupec s tržbami (C22:C36).
Následuje funkce XMATCH, kde musíme označit kód produktu. Jelikož ale nemáme celý kód, tak zde budeme muset použít wildcards neboli zástupné znaky.
Jako co hledáme tedy označíme část kódu, ampersand a hvězdičku v uvozovkách (G22&”*”). Následuje oblast, kde tento produkt hledáme, tedy sloupec s produkt (A22:A36). Přepneme se do režimu shody. Oproti funkci POZVYHLEDAT je zde o možnosti navíc, v tomto příkladu se nám bude hodit režim shody – Shoda pomocí zástupného znaku. Vyplníme tedy dvojku a přepneme se do posledního parametru Režim vyhledávání, kde vyplníme -1, jelikož chceme, aby funkce XMATCH začala vyhledávat od konce tabulky. Jelikož chceme přiřadit tržby z nejnovějšího měsíce, který je na konci tabulky. Ukončíme závorky a funkce potvrdíme. Kombinace funkcí INDEX a XMATCH správně přiřadila dle částečné shody textu tržbu z posledního měsíce v tabulce.
Funkce SEQUENCE
Velmi užitečnou funkcí je i funkce SEQUENCE. Tato funkce vrátí sekvence čísel nebo datumů, tak jak je zadáme. Řekněme, že chceme do Excelu dostat čísla od 1 do 10. Normálně bychom napsali prvních pár čísel a následně čísla stáhly dolů, což by vytvořilo sekvenci pořadových čísel. Nicméně kdybychom teď chtěli sekvenci pouze od 1 do pěti nebo od 1 do 15, tak bychom museli čísla mazat nebo přidávat.
Zkusíme to pomocí funkce SEQUENCE.
= SEQUENCE (řádky; [sloupce]; [začátek]; [krok])
Ve funkci SEQUENCE je v podstatě pouze jeden povinný parametr řádky. Tedy kolik řádků chceme vrátit. Vyplníme nejprve číslo 10 a potvrdíme. Funkce SEQUENCE vrátila čísla od 1 do 10.
Pokud bychom chtěli čísla vložit horizontálně, tak jako parametr řádky vyplníme jedničku a v nepovinném parametru sloupce vyplníme číslo 10. Funkci potvrdíme a do sešitu se vložila čísla od 1 do 10 v jednom řádku.
Co kdybychom ale do prvních dvou parametrů napsali 10 a 10? Co myslíte, že se vloží? Funkci potvrdíme. Vložila se v podstatě tabulka čísel od 1 do 100, a to v deseti řádcích a 10 sloupcích.
Funkce SEQUENCE má mnohačetná využití. Ostatní nepovinné parametry můžete využít, pokud chcete vytvářet trochu dynamičtější řady. Teď chceme vložit do jednoho sloupce například násobky čísla 3. Napíšeme funkci SEQUENCE, kde jako parametr řádky vyplníme třeba 10, sloupec nepotřebujeme, takže ho přeskočíme, jako začátek napíšeme trojku a krok je o velikosti 3, jelikož chceme násobky tří. Potvrdíme a máme zde řadu násobků čísla tři na celkem 10 řádcích.
A kde funkci SEQUENCE využijete v praxi? Na Akademii Excelu jsme třeba díky funkci SEQUENCE vytvořili plně dynamickou hypoteční kalkulačku.
Funkce RANDARRAY
Funkce RANDARRAY generuje náhodná čísla do dané oblasti dle zvolených kritérií. Je to něco podobného jako funkce RANDBETWEEN, která je v Excelu od roku 2010, která generuje náhodná čísla v limitech, které ji zadáte.
= RANDARRAY ([řádky]; [sloupce]; [min]; [max]; [celé číslo])
Funkce RANDARRAY má jako první parametr řádky, zkusíme vyplnit třeba číslo 5. Funkci potvrdíme a funkce vrátila 5 náhodných čísel mezi 0 a 1.
Vrátíme se k funkci a vyplníme další parametr sloupce, i zde vyplníme třeba číslo 5. Teď funkce vrátila 5 sloupců a 5 řádků s náhodně generovanými čísly od 0 do 1.
Vrátíme se k funkci a doplníme další nepovinné parametry. Následují parametry min a max. Zde si můžete vybrat mezi jakými limity chceme, aby se náhodná čísla generovala. Pokud jako min vyplníme třeba 50 a jako max 100, tak se po potvrzení funkce vygenerují čísla mezi těmito limity.
Funkce v základu generuje náhodná čísla jako čísla s desetinnými místy. Pokud chcete generovat pouze celá čísla, tak se k funkci vrátíme a vyplníte i poslední nepovinný parametr. Pro celá čísla vyplníme jedničku. Funkci potvrdíme a máme náhodně generovaná celá čísla.
