V dnešním videu se podíváme na funkci, která není sice běžně využívána, ale přitom je velmi užitečná. Řeč je o funkci MOD. Úkolem funkce MOD je vrátit zbytek po vydělení čísla dělitelem. Možná si teď říkáte, k čemu vám taková funkce může být a kde byste ji v praxi využili. A právě to si ukážeme v dnešním videu.
Excelový soubor ke stažení:
Funkce MOD
Samotná funkce MOD se využívá jen zřídka, většinou je funkce MOD součástí jiných funkcí, kde funguje jako doplněk nebo pomocná funkce. Funkce MOD má pouze dva parametry. Prvním parametrem je číslo a druhým je dělitel. Řekněme, že chceme zjistit zbytková čísla po vydělení čísel v tabulce. Jako první zde máme zjistit zbytkové číslo pro vydělení pětky číslem dva. Napíšeme funkci MOD, kde jako číslo označíme pětku, jelikož to je číslo, které chceme dělit a dělitel je dvojka. Funkci ukončíme a potvrdíme a funkce MOD vrátila jedničku. Proč? Vysvětlení máme uvedené vedle.
Funkce MOD našla nejbližší násobek čísla 2 k pětce, což je číslo 4 a pět mínus čtyři je jedna. Po vydělení čísla 5 čísel 2 je tedy zbytek jedna. Kdybychom hledali zbytkové číslo u čísla 10, které vydělíme 5, tak napíšeme funkci MOD, kde jako číslo označíme desítku a jako dělitel pětku. A co bude výsledkem? Výsledkem bude nula. Jelikož 10 je plně dělitelné pěti, takže nezbude po vydělení nic. Kdybychom ale 10, vydělili 4, tak funkce MOD vrátí 2, jelikož se od čísla 10 odečte osmička. V dalším příkladu máme číslo 25 vydělit devíti a zbytkové číslo bude sedm, jelikož se od čísla 25 odečte hodnota 18, což je nejbližší násobek devítky od čísla 25. A kdybychom hledali zbytkové číslo u vydělení čísla 150 číslem 26, tak výsledkem bude 20, jelikož se od čísla 150 odečte hodnota 130, což je 26*5.
Teď, když víte jak funkce MOD funguje, tak si pojďme ukázat, kde ji využijete v praxi.
Rozdíl času přes půlnoc
Funkci MOD využijete i ve chvíli, kdy potřebujete spočítat rozdíl v čase, který překračuje půlnoc. Jak jsme si podrobně vysvětlilo ve videu 5 frustrujících problémů s časem v Excelu, tak pro výpočet použijeme funkci MOD, kde od sebe odečteme koncový a počáteční čas, a jako dělitel použijeme jedničku. Funkci ukončíme a potvrdíme a stáhneme ji dolů a máme správně dopočítaný rozdíl v čase, který překračuje půlnoc.
Součet kvartálních hodnot
Funkci MOD můžeme využít i pro kumulativní součet kvartálních dat. V tabulce máme tržby pro každý měsíc. Ve sloupci kvartální tržby chceme sečíst kvartální tržby, a to vždy na třetím řádku, tedy tam kde končí kvartál. A navíc chceme tržby kumulativně sčítat. Takže na březnovém řádku bude kumulativní součet prvních třech měsíců. Na červnovém řádku chceme součet předešlých šesti měsíců atd. Abychom nemuseli postupně sčítat tržby pro každé tři měsíce, tak využijeme funkci MOD.
Začneme tím, že musíme identifikovat měsíc, ve kterém končí kvartál. Což je každý třetí měsíc. Abychom identifikovali každý třetí řádek, tak nejprve vyselektujeme měsíc z datumu a to pomocí funkce MĚSÍC. Pořadové číslo měsíce bude sloužit jako náhrada čísel řádků, podle kterých poznáme každý třetí měsíc. Ve funkci MĚSÍC označíme datum. Funkci ukončíme a stáhneme pro všechny řádky dolů. Funkce MĚSÍC vrátí pořadová čísla měsíců. Tato čísla teď využijeme ve funkci MOD. Funkci MĚSÍC zabalíme do funkce MOD, kde číslem je funkce MĚSÍC, a dělitelem je trojka, tedy každý třetí měsíc. Funkci ukončíme a stáhneme ji dolů. Na řádcích, kde je třetí měsíc je nula. Takže konec kvartálu je, když se funkce MOD rovná nule. Funkci potvrdíme a stáhneme ji dolů. Na každém třetím řádku se vrátila pravda.
A abychom dostali kumulativní součet tržeb, tak tuto funkci musíme vynásobit kumulativní SUMOU. Nejprve celou funkci MOD zabalíme do vlastních závorek a to vynásobíme funkci SUMA. Takže funkce SUMA, kde vytvoříme dynamické rozpětí, takže označíme první buňku, dvojtečka a první buňka a první buňku zafixujeme. Ukončíme funkci a potvrdíme a když funkci stáhneme dolů, tak se na každém třetím řádku vrátí kumulativní součet tržeb. U některých řádků se vrátily zelené trojúhelníky, což Excel dělá, když použijete kumulativní sumu. O chybu se nejedná, takže můžeme buňky označit a ignorovat.
Zvýraznění sudých hodnot
Funkci MOD můžete využít i s podmíněným formátováním. Řekněme, že v tabulce potřebujeme zvýraznit všechny sudé hodnoty. K identifikaci sudých hodnot použijeme funkci MOD. Ukážeme si funkci nejprve na první buňce a pak ji nakopírujeme do podmíněného formátování. Napíšeme funkci MOD, kde jako číslo označíme první číslo v tabulce a jako dělitel bude číslo 2, jelikož hledáme sudou hodnotu. Funkci MOD potvrdíme a jelikož první číslo v tabulce je sudé, tak je výsledkem funkce nula, jelikož po vydělení nezbude žádný zbytek. Takže pravidlo pro podmíněné formátování je, že se funkce MOD bude rovnat nule, v takovém případě bude číslo sudé.
Teď když máme funkci hotovou, tak funkci zkopírujeme, označíme hodnoty a na kartě Domů najdeme podmíněné formátování. Vybereme pravidlo podle vzorce a jako vzorec vložíme funkci MOD. Vybereme formát, ve formátu vybereme výplň a vybereme nějakou světlou výplň. Formát potvrdíme a obarvily se pouze sudé hodnoty. Podmíněné formátování je samozřejmě dynamické, takže pokud změníme jakékoliv číslo v tabulce, tak se obarvení buněk přizpůsobí.
Ověření dat
Funkci MOD můžeme využít i ve chvíli, kdy chceme omezit vložení určitých hodnot do buněk. Řekněme, že potřebujeme, aby se do sloupce vyplnili pouze celá čísla, nikoliv čísla s desetinnými místy. Toto pravidlo chceme ošetřit pomocí ověření dat. Jak bude vypadat funkce, kterou použijeme? Abychom ověřili správnost funkce MOD, tak zkusíme do sloupce napsat dvě čísla, třeba číslo 150 a 150,5. Napíšeme funkci MOD, kde označíme první číslo a jako dělitel napíšeme jedničku. Funkci potvrdíme a stáhneme ji i pro druhé číslo. Jak si můžeme všimnout, tak u celého čísla vrátila funkce MOD s dělitelem jedničku nulu, kdežto u čísla s desetinným místem vrátila desetinné číslo, které zbude. Tím pádem poznáme celé číslo podle toho, že se funkce MOD bude rovnat nule.
Když máme funkci hotovou, tak smažeme cvičné hodnoty, zkopírujeme funkci MOD, označíme sloupec, kde chceme omezit hodnoty a na kartě Data vybereme Ověření dat. Vybereme vlastní pravidlo a funkci vložíme do příkazového pole. Ještě klidně můžeme vyplnit zprávu v případě chybného zadání hodnoty. Třeba Vyplňte celé číslo. Potvrdíme pravidlo a zkusíme do sloupce vyplnit několik hodnot, celých čísel. A teď zkusíme vyplnit desetinné číslo a vyskočí na nás chybové hlášení, které nám oznamuje, že máme vyplnit celé číslo.
Součet sudých řádků
Funkci MOD můžeme použít třeba ve chvíli, kdy potřebujeme sečíst každý sudý nebo lichý řádek. V tabulce máme produkty a tržby a řekněme, že potřebujeme sečíst každý druhý řádek v tabulce. K tomu se perfektně hodí funkce MOD. V prvním sloupci v tabulce máme očíslované řádky, takže víme, které řádky jsou sudé. Začneme tvořit funkci. Nejprve pomocí funkce MOD zjistíme, které řádky v tabulce jsou sudé řádky. Takže napíšeme funkci MOD, kde označíme první číslo řádku a co bude dělitel? Chceme sčítat sudé řádky, takže sudé číslo poznáme podle toho, že je dělitelné dvěma. Takže dělitel je dva. Ukončíme funkci a stáhneme ji dolů. Na sudých řádcích se vrátila nula, jelikož sudý řádek je plně dělitelný číslem dva. Na lichých řádcích se vrátila jednička, jelikož po vydělení dvojkou vždy jednička zůstane.
Funkce MOD bude základem pro funkci SOUČIN.SKALÁRNÍ, anglicky funkci SUMPRODUCT. Ve funkci SOUČIN.SKALÁRNÍ vynásobíme mezi sebou sudé řádky s tržbami a výsledkem bude součet tržeb na sudých řádcích. Proto abychom to ale mohli udělat bychom na sudých řádcích potřebovali nikoliv nulu ale jedničku. Takže funkci MOD předěláme na podmínku. Podmínka bude, zda se funkce MOD rovná nule. Funkci potvrdíme a stáhneme ji dolů a teď máme na sudých řádcích pravdu, což je v Excelu jednička a nepravda je nula. Ve funkci SOUČIN.SKALÁRNÍ ale nemůžeme pracovat s pravdami a nepravdami, takže tyto logické hodnoty musíme převést zpátky na jedničky a nuly a to pomocí dvojitého negativu před funkcí MOD, která musí být v závorce. Potvrdíme a stáhneme dolů a teď máme na sudých řádcích jedničky.
Před funkci MOD napíšeme funkci SOUČIN.SKALÁRNÍ, kde prvním polem je funkce MOD, kterou ale musíme trochu upravit a v poli číslo označíme celý sloupec řádky v tabulce. Dělitel ve funkci zůstane stejný. Ve funkci MOD musíme označit celý sloupec hodnot, jelikož i v druhém poli budeme označovat celý sloupec tržeb a výška obou polí musí být shodná, jinak by funkce SOUČIN.SKALÁRNÍ vrátila chybu. A druhé pole bude sloupec tržeb. Funkce SOUČIN.SKALÁRNÍ mezi sebou pole násobí a následně výsledek sečte. Na sudých řádcích se tedy tržba vynásobí jedničkou a na lichých řádcích nulou. Po sečtení se sečtou pouze tržby na sudých řádcích.
Jedna odpověď
😊