V dnešním videu máte možnost vyzkoušet si vaše excelové dovednosti na praktickém příkladu. V příkladu dostanete tabulku s databází zaměstnanců a během testu máte vyřešit několik úkolů, se kterými se v praxi setkáte denně. Vaším úkolem bude třeba spočítat, kolik zaměstnanců z firmy odešlo v určitém roce, kolik vyplatíme na ročních bonusech při splnění několika různých podmínek nebo třeba dohledat obraty k jednotlivým zaměstnancům. Na příkladu si můžete vyzkoušet využití nejběžnějších excelových funkcí i postupů, které pro řešení každodenních úkolů potřebujete.
V podobném stylu máme i několik následujících videí – Excel k pohovoru I., Excel k pohovoru II. – procvičte si kontingenční tabulky, Excel k pohovoru III. – pro začátečníky, Excel k pohovoru IV. – pro mírně pokročilé.
Excelový soubor ke stažení:
Ve cvičeném souboru máme zdrojovou tabulku se seznamem zaměstnanců. U každého zaměstnance máte uvedený identifikační kód, datum narození, pozici, oddělení, na kterém pracuje, mzdu a den nástupu případně odchodu ze zaměstnání. Naším úkolem je odpověď na otázky, které jsou uvedené na listu Přehled.
K řešení úkolů můžeme použít pomocné výpočty i pomocné sloupce.
Správnou odpověď na otázku napište do orámované buňky. Pokud jste příklad zdárně vyřešili zaškrtněte políčko vede buňky a tím se vám načte celkové skóre. Maximální počet bodů je 21, jelikož některé těžší úkoly jsou z více bodů. Dejte nám po dokončení testu rovněž v komentářích pod videem vědět, jak jste v testu dopadli.
Než se vrhneme na řešení příkladů, tak se nejprve podíváme na zdrojová data. Pro řešení příkladů bude důležité to, že v tabulce máme jak zaměstnance, kteří u nás v současné době pracují, tak zde máme uvedené zaměstnance, kteří od nás již odešli. To bude pro některé výpočty zásadní. Tyto neaktivní zaměstnance poznáme podle toho, že ve sloupci Den odchodu mají vyplněné datum.
Když se podíváme na rozsah celé zdrojové tabulky, tak zjistíme, že tabulka má skoro 1 000 řádků. Aby se nám s daty lépe pracovalo, tak můžeme zdrojovou tabulku změnit na excelovou tabulku. Rozdíl poznáme u vyplňování funkcí, jelikož nebudeme muset označovat manuálně celé sloupce v tabulce, ale u excelové tabulky bude stačit kliknout na záhlaví sloupce, což nám dovolí být u řešení příkladů efektivnější a rychlejší. Tento krok je samozřejmě dobrovolný. Změníme data na excelovou tabulku. A tabulku pojmenujeme jako Data.
Teď když jsme se seznámili se zdrojovými daty, tak se můžeme vrhnout na řešení příkladů.
Kolik zaměstnanců až doposud pracovalo ve firmě?
První otázka se nás ptá, kolik jsme měli od počátku celkem zaměstnanců? To znamená, že máme v podstatě spočítat počet řádků ve zdrojové tabulce, jelikož co řádek, to jeden zaměstnanec. A zajímají nás i ti, co už u nás nepracují. Takže funkce POČET2 neboli funkce COUNTA, kde označíme jakýkoliv sloupec ve zdrojové tabulce. Tím, že pracujeme s excelovou tabulkou, tak stačí označit pouze záhlaví sloupce a do funkce se vloží celý sloupec dat. Funkci potvrdíme a vrátí se počet zaměstnanců.
Kolik máme v současné době zaměstnanců ve firmě?
Další otázka se nás ptá na aktuální počet zaměstnanců. Otázka se od minulé liší tím, že se ptá na současný počet aktivních zaměstnanců, takže musíme odečíst ty, kteří už u nás nepracují. To jsou ti zaměstnanci, u kterých je vyplněné datum odchodu ve zdrojové tabulce. V tomto případě by bylo tedy nejužitečnější vytvořit ve zdrojové tabulce pomocný sloupec, kde bychom rozlišili aktivní a neaktivní zaměstnance. Takže ve zdrojové tabulce vytvoříme nový sloupec, který nazveme třeba stav. A zde použijeme podmínku KDYŽ. Ve funkci KDYŽ ověříme podmínku, že pokud je vyplněné datum, což ověříme jednoduše pomocí funkce JE.ČÍSLO neboli funkce ISNUMBER. Datum je z pohledu Excelu pouze číslo, takže pokud bude datum vyplněné, tak bude podmínka splněná. A pokud bude podmínka splněná, tak chceme vrátit slovo Aktivní a pokud nebude splněná, tak slovo Neaktivní. Samozřejmě vy si na tomto místě můžete doplnit cokoliv. Klidně byste aktivní zaměstnance mohli označit jedničkou a neaktivní zaměstnance nulou. Ukončíme podmínku a potvrdíme.
Teď když máme pomocný sloupec, tak můžeme na listu Přehled použít funkci COUNTIF, která počítá s podmínkou. Takže funkce COUNTIF, kde jako oblast označíme pomocný sloupec status, a kritérium bude slovo „Aktivní“ v uvozovkách. Potvrdíme funkci a vrátí se počet aktivních zaměstnanců.
Kolik zaměstnanců odešlo z firmy v roce 2021?
Následně máme odpovědět na otázku, kolik zaměstnanců odešlo v roce 2021. Zde máme na výběr, jak budeme příklad řešit. Jedna možnost je, že použijeme pomocný sloupec ve zdrojové tabulce, ve kterém vyselektujeme rok z datumu odchodu zaměstnance a následně spočítáme, kolikrát se rok 2021 objeví ve sloupci. Začneme tímto řešením. To znamená, že do zdrojové tabulky přidám nový sloupec, který nazveme rok. V tomto sloupci použijeme funkci ROK neboli funkci YEAR, ve které označíme datum odchodu. Potvrdíme funkci a na řádcích, kde máme datum se vrátil rok a na ostatních řádcích se vrátila chyba. Abychom zde neměli tyto chyby, tak to zabalíme do IFERROR.
A teď stačí na tento pomocný sloupec ROK použít funkci COUNTIF, kde jako kritérium bude rok 2021.
Jaké jsou celkové měsíční náklady na aktivní zaměstnance?
Další otázka se ptá, jaké jsou měsíční mzdové náklady na aktivní zaměstnance. To je jednoduché, použijeme funkci SUMIF, jelikož opět musíme použít podmínku, že se jedná o aktivního zaměstnance. Takže použijeme náš pomocný sloupec stav. Ve funkci SUMIF nejprve označíme oblast, což je sloupec stav, následuje kritérium, což je slovo Aktivní a jako oblast součtu označíme sloupec s platy.
Označení neaktivních zaměstnanců ve zdrojové tabulce
V dalším kroku máme ve zdrojové tabulce obarvit všechny neaktivní zaměstnance oranžově. A máme obarvit celý řádek. Můžeme opět použít pomocný sloupec Stav. Přepneme se do zdrojové tabulky a jelikož chceme obarvovat celé řádky zdrojové tabulky, tak ji celou označíme, nebudeme ale označovat pomocné sloupce. Na kartě data vybereme podmíněný formát, nové pravidlo a formátovat pomocí vzorce. Pravidlo je, že se buňka ve sloupci stav, která bude zafixovaná pro sloupec bude rovnat slovu „Neaktivní“. Vybereme formát a potvrdíme.
Jaký celkový bonus vyplatíme na základě podmínek
Jako další úkol máme dopočítat celkový vyplacený bonus pro zaměstnance. Zadání říká, že bonus náleží zaměstnanci, jehož obrat byl vyšší než 850 000 Kč. Obraty pro jednotlivé zaměstnance máme uvedené na listu Obraty. A pokud zaměstnanec udělal obrat nad 850 000 Kč, tak mu z jeho obratu náleží bonus 5 %. Máme dvě možnosti, jak příklad vyřešit. Buď dohledáme obrat do zdrojové tabulky a nebo do obratové tabulky dohledáme, zda se jedná o aktivního zaměstnance. Jelikož chybou by bylo opět jen dopočítat bonus, jelikož z obratové tabulky opět nevíme, zda se jedná o aktivního zaměstnance. Zkusíme dohledat obrat do zdrojové tabulky.
To ale není vše. Všimli jste si háčku? Ve zdrojové tabulce máme zaměstnance uvedené jako jméno a příjmení, kdežto v obratové tabulce máme nejprve příjmení a až pak jméno. Takže nejprve musíme vyřešit toto než se pustíme do hledání obratů.
Jak přehodit jméno a příjemní bude záležet na verzi Excelu, kterou používáte. Buď můžete použít nástroj Text do sloupce, kde nejprve oddělíte jméno a příjmení do samostatných sloupců a následně je spojíte. Nebo pokud používáte verze Excelu po 2016, tak můžete použít dynamické doplnění. V takovém případě by pouze stačilo napsat pár příkladů a potvrdit dynamické doplnění. Ani jedna z těchto předchozích verzí nebyla ale založená na funkci, takže by tato rozdělení nereagovala na změny ve zdroji. Pokud používáte Office 365, tak můžete použít funkce TEXTPŘED neboli TEXTBEFORE a TEXTZA neboli TEXTAFTER. A nebo můžete použít klasické funkce ZLEVA, ZPRAVA a DÉLKA.
Vytvoříme první pomocný sloupec Příjmení, ve kterém oddělíme příjmení od jména. K tomu využijeme funkci ZLEVA, anglicky funkci LEFT. Abychom ale mohli slova oddělit, tak musíme nejprve určit, kolikátým znakem ve slově je mezera, pomocí které budeme slova oddělovat. To určíme pomocí funkce HLEDAT, anglicky funkce SERACH. Kde budeme hledat mezeru v textovém řetězci. Funkce HLEDAT vrátí pořadové místo mezery v buňce a toto číslo následně použijeme ve funkci ZLEVA pro oddělení příjmení.
Ve druhém pomocném sloupci oddělíme jméno. Opět musíme nejprve určit pozici mezery pomocí funkce HLEDAT. Tuto pozici musíme odečíst od celkové délky textového řetězce v buňce, abychom dostali počet znaků jména, které máme oddělit. Délku textového řetězce určíme pomocí funkce DÉLKA, anglicky funkce LEN. A jméno následně oddělíme pomocí funkce ZPRAVA, anglicky funkce RIGHT.
Nakonec jméno a příjmení ve třetím pomocném sloupci spojíme dohromady v pořadí, jako to máme ve zdrojové tabulce.
Teď můžeme konečně dohledat obrat do zdrojové tabulky. V tomto případě nemůžeme kvůli struktuře dat použít funkci SVYHLEDAT, jelikož sloupec se jménem a příjmením v pomocné tabulce je posledním sloupcem tabulky. Takže pro dohledání obratu použijeme funkce INDEX & POZVYHLEDAT.
Teď když máme ke každému zaměstnanci dohledané obraty, tak můžeme konečně ověřit podmínku, kdo splnil podmínky pro vyplacení bonusu. Využijeme dalšího pomocného sloupce ve zdrojové tabulce, kde použijeme funkci KDYŽ. Ve funkci KDYŽ ale musíme ověřit dvě podmínky, které platí zároveň. První podmínka je, že se jedná o aktivního zaměstnance, a druhá podmínka je, že je jeho obrat vyšší než 850 000 Kč. Takže použijeme funkci A, kde ověříme tyto dvě podmínky. A pokud budou podmínky splněné, tak chceme obrat násobit 5%. A pokud podmínky splněné nebudou, tak chceme vrátit nulu.
Teď stačí sečíst hodnoty v tomto pomocném sloupci podmínka. A máme celkový bonus dle obratu, který zaměstnancům vyplatíme.
Který zaměstnanec bere nejvyšší mzdu?
Další otázka zní, který současný zaměstnanec pobírá nejvyšší mzdu a na jakém oddělení tento zaměstnanec pracuje. Zde je drobný chyták. Když totiž vyfiltrujeme tabulku pro nejvyšší mzdu, tak zjistíme, že nejvyšší mzdu pobíral zaměstnanec, který už u nás nepracuje, jelikož u něho máme vyplněné i datum odchodu, takže pokud bychom pouze vyhledali maximální hodnotu tohoto sloupce, tak by se nám dohledat zaměstnanec, který již není aktivní. A otázka se ptá na současného zaměstnance. To znamená, že musíme pro vyhledání mzdy a zaměstnance použít podmínku. Pokud používáte Excely po 2019, tak můžete použít funkci MAXIFS, ve které ověříte podmínku, že se jedná o aktivního zaměstnance.
Funkce MAXIFS dohledá nejvyšší mzdu pro stále aktivního zaměstnance. K této nejvyšší mzdě teď dohledáme jméno zaměstnance přes funkce INDEX & POZVYHLEDAT. Ve funkci INDEX označíme sloupec se jmény zaměstnanců ve zdrojové tabulce, a ve funkci POZVYHLEDAT bude jako parametr co funkce MAXIFS, a tuto nejvyšší hodnotu hledáme ve sloupci Mzda.
Na jakém oddělení pracuje zaměstnanec s nejvyšší mzdou?
V další otázce stačí pouze dohledat, na jakém oddělení pracuje tento zaměstnanec s nejvyšší mzdou. A zde si můžeme vybrat jakou vyhledávací funkci použijeme. Třeba SVYHLEDAT.
Jaký je podíl mužů a žen ve firmě?
Další otázka se ptá, jaký podíl mužů a žen máme ve firmě včetně zaměstnanců, kteří u nás již nepracují. Zajímá nás tedy poměr pohlaví od počátku bez ohledu na status zaměstnance. V závorce máme uvedenou nápovědu, že pohlaví zaměstnance poznáme podle prvního písmena kódu zaměstnance. Abychom tedy mohli spočítat podíly, tak musíme nejprve oddělit první písmeno z kódu a následně tato písmena spočítat. První písmeno kódu oddělíme pomocí funkce ZLEVA, anglicky funkce LEFT. Ve funkci ZLEVA pouze označíme kód zaměstnance a chceme oddělit první znak.
Když máme písmena oddělená, tak je stačí spočítat pomocí funkce COUNTIF. A jelikož se ptáme na podíl, tak funkci COUNTIF vydělíme celkovým počtem zaměstnanců.
Pro výpočet podílu mužů můžeme buď použít stejný postup a nebo jednoduše od podílu žen odečteme jedničku a získáme podíl mužů.
Kolik vyplatíme na ročních bonusech při splnění podmínek?
Další otázka se nás ptá, kolik celkem vyplatíme na ročních bonusech při podmínkách, které zde máme uvedené. Tedy, že roční bonus náleží aktivnímu zaměstnanci, který ve firmě pracuje déle než 5 let a vygeneroval obrat vyšší než milion korun a nebo zaměstnanci, kterému je více než 55 let věku. Bonus je tedy založený na mixu dvou logických podmínek A a NEBO. Kromě toho, abychom podmínku mohli ověřit, tak musíme dopočítat věk zaměstnance a délku let, které u nás zaměstnanec pracuje. To uděláme ze sloupců datum narození a datum nástupu do zaměstnání. Pokud zaměstnanec podmínky splní, tak mu náleží bonus 100 000 Kč.
Nejprve ve zdrojové tabulce vytvoříme pomocný sloupec Věk, kde spočítáme aktuální věk zaměstnance. To uděláme tak, že z dnešního datumu vyselektujeme rok pomocí funkce ROK/YEAR a od toho odečteme rok narození zaměstnance.
Dále musíme spočítat, jak dlouho u nás zaměstnanec pracuje. To uděláme tak, že od dnešního datumu odečteme datum nástupu do zaměstnaní. To nám vrátí počet dnů, po které u nás zaměstnanec pracuje. My ale potřebujeme odpověď v letech, takže to celé vydělíme číslem 365.
Následně můžeme spočítat, kdo má nárok na roční bonus, a to tak, že v dalším pomocném sloupci ověříme podmínky. Nejprve ověříme podmínky, které musí platit zároveň. Takže funkce A, kde ověříme podmínky, že se jedná o aktivního zaměstnance a že u nás pracuje déle než 5 let. Máme ale ještě další podmínku, že bonus může být rovněž vyplacen pokud je zaměstnanci více než 55 let. Takže funkci A zabalíme do funkce NEBO, kde prvním parametrem ve funkci NEBO je funkce A a druhá podmínka je, že je věk zaměstnance více než 55 let. Tato funkce NEBO je základem podmínky ve funkci KDYŽ. A pokud budou podmínky splněné, tak chceme vrátit bonus ve výši 100 000 Kč, v opačném případě chceme vrátit nula.
Na listu přehled teď stačí tento pomocný sloupec opět sečíst.
Další úkol má několik kroků. Nejprve máme vytvořit rozbalovací seznam se seznamem oddělení. A následně pro vybrané oddělení dopočítat údaje níže.
Začneme výpočtem, kolik zaměstnanců pracuje na vybraném oddělení. K tomu použijeme funkci COUNTIFS, kde ověříme dvě podmínky, že se jedná o aktivního zaměstnance a že pracuje na vybraném oddělení.
Dále máme zjistit, jaká je na vybraném oddělení průměrná mzda. To spočítáme pomocí funkce AVERAGEIFS. V této funkci opět ověříme podmínky, že se jedná o aktivního zaměstnance a že zaměstnanec pracuje na vybraném oddělení.
Stejný postup použijeme i pro výpočet průměrné délky zaměstnaní.
Funkci AVERAGEIFS využijeme i pro odpověď na poslední otázku.
Hotovo.
Gratuluji k úspěšnému dokončení testu. Kolik bodů jste získali?
Jedna odpověď
Dobrý den,
jak ve Zdrojové tabulce, tak v tabulce s Obraty se nacházejí duplicitní jména zaměstnanců. Při doplňování obratů do zdrojové tabulky se tedy vždy dohledá pouze první hodnota, a tedy se u duplicitních položek dopočítá stejný bonus.
Nicméně, to je drobnost . . . chtěl bych Vám moc poděkovat za to, co pro Excel a nás excelové nadšence děláte . . . je to skvělé!!! Díky 🙂