V dnešním videu se podíváme na několik užitečných triků s funkcí SEQUENCE. Funkce SEQUENCE je velmi jednoduchou funkcí, která tvoří číselné řady podle parametrů, které do funkce zadáme. Funkce SEQUENCE je dostupná pro všechny od verze Office 2021 a předplatitele Office 365.
Excelový soubor ke stažení:
Než si ukážeme triky s funkcí SEQUENCE, tak si v krátkosti ukážeme, jak funkce SEQUENCE funguje.
= SEQUENCE (řádky; [sloupce]; [začátek]; [krok])
Funkce SEQUENCE má jeden povinný parametr řádky a další tři nepovinné parametry. Řekněme, že chceme vytvořit řadu čísel od jedničky do desítky. Použijeme funkci SEQUENCE. Ve funkci SEQUENCE nejprve vyplníme parametr řádky, chceme řadu čísel od jedné do deseti, takže 10 řádků. Druhým nepovinným parametrem je sloupec. Čísla chceme vrátit v jednom sloupci, takže jako parametr sloupec vyplníme 1. A následuje další nepovinný parametr začátek, tedy od jakého čísla chceme začít. Chceme řadu čísel od jedničky, takže jako začátek vyplníme jedničku. Posledním nepovinným parametrem je krok. Tedy o kolik se má počáteční číslo zvyšovat. Jelikož chceme vytvořit plynulou řadu čísel, tak krok je rovněž jedna. Ukončíme funkci a potvrdíme a funkce SEQUENCE vrátí číselnou řadu od jedné do deseti.
Kdybychom chtěli číselnou řadu v řádku a nikoliv ve sloupci, tak bychom v parametru řádek vyplnili jedničku a naopak v parametru sloupce bychom vyplnili číslo deset. Funkci potvrdíme a tím se vrátí číselná řada v řádku.
Vrátíme se k funkci a zkusíme do řádku opět vyplnit číslo deset, ale tentokrát v parametru sloupce vyplníme dvojku. Funkci potvrdíme funkce vrátí čísla od jedné do dvaceti ve dvou sloupcích a na deseti řádcích.
Pokud bychom chtěli aby číselná řada začínala například dvojkou a vytvořili jsme na deseti řádcích násobky čísel dva, tak by funkce SEQUENCE vypadala následovně. V parametru řádek by bylo číslo 10, jelikož chceme vrátit deset čísel. Čísla chceme vrátit v jednom sloupci, začátek bude tentokrát číslo dva, jelikož chceme začínat od dvojky a krok bude rovněž číslo dva, jelikož chceme vrátit násobky čísla dva. Funkci potvrdíme a teď máme číselnou řadu od dvojky do dvaceti.
Když jsme si teď vysvětlili, jak funkce SEQUENCE funguje, tak s pojďme ukázat nějaké praktické triky, kdy se vám funkce SEQUENCE bude hodit.
Dynamické počty řádků
V příkladu máme tabulku s produkty. V prvním sloupci chceme produkty očíslovat od jedničky. Nechceme ale číselnou řadu tvořit ručně, jelikož do sloupce s produkty nestále přibývají nové produkty a my chceme, aby kdykoliv nový produkt přidáme do sloupce, aby se okamžitě rozšířila číselná řada. Použijeme funkci SEQUENCE.
Ve funkci SEQUENCE musíme nejprve stanovit parametr řádek. A právě tento parametr musí dynamicky počítat, kolik produktů je vyplněných ve sloupci. Použijeme tedy funkci POČET2 neboli funkci COUNTA, která mimo jiné počítá textové hodnoty. A ve funkci POČET2 označíme více řádků, než kolik máme v současné době vyplněno. Tato funkce spočítá, kolik neprázdných buněk je vyplněno ve sloupci a podle toho vrátí počet řádků do funkce SEQUENCE. Následuje parametr sloupce. Čísla chceme vrátit v jednom sloupci, takže jednička. Číslování začíná od jedničky a krok je rovněž jedna. Ukončíme funkci a potvrdíme a funkce vrátí správně očíslované řádky. Přidáme do sloupce nový produkt a po potvrzení funkce SEQUENCE přidá další pořadové číslo. Přidáme další produkt a opět funkce SEQUENCE správně očísluje řádky. Výhodou je, že pokud rovněž smažeme řádek řekněme uprostřed tabulky, tak se číslování ve funkci SEQUENCE rovněž přizpůsobí.
Řada datumů
Funkci SEQUENCE využijeme i při tvorbě datumových řad. Řekněme, že chceme vytvořit řadu deseti datumů od dneška. A chceme aby funkce vždy ukazovala deset datum ů od aktuálního dnešního dne. Použijeme funkci SEQUENCE, ve které nejprve vyplníme, kolik datumů chceme vrátit. Říkali jsme deset, takže počet řádků je deset. Datumy chceme opět v jednom sloupci, takže v parametr sloupce vyplníme jedničku. Počátek je dnešní datum, a jelikož chceme vždy aktuální dnešní datum, tak použijeme funkci DNES. A v parametru krok vyplníme jedničku. Funkci ukončíme a potvrdíme a funkce vrátí pořadí deseti datumů. A jelikož je základem funkce DNES, tak funkce vrátí vždy deset datumů s aktuálním počátečním dnešním datumem.
A co kdybychom chtěli vytvořit pouze řadu pracovních dnů. V takovém případě použijeme funkci SEQUENCE uvnitř datumové funkce WORKDAY. Najdeme funkci WORKDAY.INTL. V této funkci nejprve stanovíme počáteční datum. Opět použijeme funkci DNES. A řekněme, že chceme vrátit 15 datumů, takže v parametru dny použijeme funkci SEQUENCE, ve které vyplníme pouze 15 řádků. A následuje parametr víkendy, kde si určíme, které dny jsou víkendy v České republice. U nás jsou víkendy sobota a neděle, takže vybereme možnost jedna. Funkci ukončíme a potvrdíme a funkce vrátila sérii 15 datumů od dne, který následuje po dnešním datu. Pokud bychom chtěli vrátit řadu včetně dnešního dne, tak se k funkci musíme vrátit a od funkce DNES odečíst jedničku.
Pomocí této kombinace ale můžeme vrátit i sérii datumů bez státních svátků. V takovém případě bychom si museli někde vedle vytvořit sloupec s datumy, které připadají na státní svátky. Takový malý příklad máme zde. Ve sloupci máme vyplněné datumy letošních velikonoc a květnových svátků. Zkusíme znovu funkci WORKDAY.INTL. Tentokrát natvrdo vyplníme počáteční datum, třeba 15.dubna a nezapomeneme, že datum musí být v uvozovkách. Následuje počet dnů, kde opět vyplníme funkci SEQUENCE s patnácti dny. Víkendy jsou sobota a neděle, takže jednička a v parametru svátky označíme všechny datumy svátků. Ukončíme funkci a potvrdíme a teď máme sérii datumů od 16.4.2025, mezi kterými jsou vynechané nejen víkendy, ale i státní svátky.
Série měsíců
U datumů ještě zůstaneme. Řekněme, že chceme vytvořit dynamickou sérii názvů měsíců, tedy leden, únor, březen atd. Postupně vytvoříme celou funkci. Začneme tím, že stanovíme počáteční lednové datum. Toto datum buď máme někde napsané a nebo ho vytvoříme pomocí funkce DATUM neboli funkce DATE. Napíšeme datum 1.1.2025. Jelikož chceme vrátit každého prvního dalšího měsíce, tak funkci DATUM zabalíme do funkce EDATE, kde začátek bude funkce DATUM a počet měsíců stanoví právě funkce SEQUENCE. Chceme názvy leden až prosinec, což je 12 měsíců, chceme je v jednom sloupci a chceme názvy od ledna, takže začátek nula. Když tyto funkce ukončíme a potvrdíme, tak se vrátí série 12 datumů, vždy prvého v měsíci.
Abychom z těchto datumů dostali názvy měsíců, tak to zabalíme do funkce HODNOTA.NA.TEXT neboli anglicky funkce TEXT. A jako formát stanovíme plný název měsíce, takže 4 písmena „m“ v uvozovkách. Funkci ukončíme a potvrdíme a teď máme dynamický seznam názvů měsíců.
Série textu
Na začátku jsme si říkali, že funkce SEQUENCE vrací série čísel, nicméně tuto funkci lze použít i k vytvoření řady textu. Pokud bychom například na pěti řádcích potřebovali vrátit slovo produkt, tak použijeme funkci OPAKOVAT, neboli REPT funkci, ve které stanovíme slovo, které chceme opakovat a v počtu použijeme funkci SEQUENCE, kde třeba stanovíme, že slovo chceme vrátit celkem pětkrát. Ukončíme funkce a potvrdíme a teď se na pěti řádcích vrátí slovo, které jsme stanovili ve funkci OPAKOVAT.
Stejně tak můžeme pomocí funkce ZNAK neboli funkce CHAR a SEQUENCE vygenerovat písmena abecedy. Napíšeme funkci ZNAK, ve které použijeme funkci SEQUENCE. Písmen abecedy je 26 a jako počátek použijeme číslo 65). Ukončíme funkce a potvrdíme a funkce doručí všechna písmena abecedy.
Souhrnná tabulka s kvartálními součty
V posledním příkladu máme excelovou tabulku, ve které máme měsíce a hodnoty, může se jednat třeba o tržby nebo počty prodaných kusů. V posledním sloupci máme kumulativní součty. Vedle této tabulky potřebujeme vytvořit souhrnnou tabulku pouze s kvartálními součty, takže chceme vidět březen a kumulativní součet, červen a kumulativní součet atd. Vše chceme samozřejmě dynamické, takže když přidáme nový kvartál do tabulky, tak se i tato souhrnná tabulka automaticky rozšíří.
Začneme s funkcí MOD, která bude základem toho, že určíme každý třetí řádek, na kterém se nachází konec kvartálu. A ve funkci MOD použijeme funkci SEQUENCE, ve které pomocí funkce POČET určíme počet řádků v tabulce. Ve funkci MOD určíme jako parametr dělitel 3.
Když tuto funkci potvrdíme, tak se vrátí série čísel, kde na každém třetím řádku je nula. A právě to jsou řádky, kde jsou kvartální data. Takže tento sloupec můžeme použít v logické podmínce ve funkci FILTER. Takže funkce FILTER, kde filtrujeme zdrojovou tabulku a v parametru zahrnuje je logická podmínka funkce MOD, která se rovná nule. Funkci ukončíme a potvrdíme a vrátila se správná data, ale vrátily se všechny tři sloupce, jelikož funkce FILTER vrací vždy všechny sloupce z označené oblasti.
To vyřešíme pomocí nové funkce ZVOLITSLOUPCE neboli funkce CHOOSECOLS. Vrátíme se k funkci FILTER, kde parametr tabulka zabalíme do funkce ZVOLITSLOUPCE, kde si stanovíme, že chceme vrátit odpovědi pouze z prvního a třetího sloupce. Funkci potvrdíme a teď máme vytvořeno souhrnnou tabulku přesně podle zadání. Když do zdrojové excelové tabulky přidáme nová kvartální data, tak se automaticky doplní i naše souhrnná tabulka.
