V dnešním videu ze série Excel nejen k pohovoru se podíváme na trochu pokročilejší použití excelových funkcí a jejich kombinací. Navazujeme tak na předchozí videa Excel k pohovoru I., kontingenční tabulky k pohovoru a excelová cvičení k pohovoru pro začátečníky. Dnešní test má prověřit to, zda vás v praktických úkolech, se kterými se můžete setkat v každodenní práci s Excelem, napadne, jaké funkce a v jaké kombinaci použít, pro co nejjednodušší a nejefektivnější řešení. Cílem testu není používat super pokročilé a bláznivé kombinace excelových funkcí, ale naopak umět použít několik málo funkcí v různých situacích.
Excelový soubor ke stažení:
Procvičte si Excel nejen k pohovoru
Na pohovoru byl před vámi otevřený excelový sešit, ve kterém máte odpovědět na následujících několik otázek. V excelovém sešitu máme hlavní zdrojovou tabulku s daty o zaměstnancích. Kromě ní máme v sešitu ještě dvě pomocné tabulky s dodatečnými informacemi o odděleních a městech. K řešení úkolů můžete použít všechny tři tabulky a můžete vytvořit tolik pomocných sloupců a mezi výpočtů, kolik jen chcete. Jediné na čem záleží je, abyste k řešení použili funkce a nikoliv manuální výpočty.
Cílem je naučit se ukázat vám, jak lze některé praktické situace řešit za pomocí správné kombinace excelových funkcí. U některých řešení vám ukáži i více způsobů, abyste viděli, že jedno správné řešení neexistuje a je jen na vás, jakým způsobem, ke správnému výsledku dojdete.
Jaký je současný průměrný věk zaměstnanců
Prvním úkolem je spočítat současný průměrný věk zaměstnanců v tabulce. Jak vidíme, tak ve zdrojové tabulce nemámě věk zaměstnanců přímo uvedený, máme zde však uvedené datum jejich narození. Současný věk musíme dopočítat. Vytvoříme nový sloupec tabulky, který nazveme Věk, kde nejprve vyselektujeme rok narození zaměstnance z datumu narození pomocí funkce ROK, anglicky funkce YEAR. Napíšeme funkci ROK, kde označíme datum narození zaměstnance. Funkci potvrdíme a pošleme ji dolů. Tato funkce vyselektuje rok narození každého zaměstnance. Abychom zjistili současný věk, tak musíme rok narození odečíst od letošního roku. Buď rok narození natvrdo odečteme od současného roku 2024 a nebo použijeme funkci DNES, anglicky funkce TODAY, čímž získáme dnešní datum. A tuto funkci rovněž zabalíme do funkce ROK, aby se z dnešního datumu vyselektoval rok, tedy rok 2024. Výsledkem funkce bude současný věk zaměstnanců. Funkci potvrdíme a pošleme dolů.
Teď stačí v buňce s odpovědí použít funkci PRŮMĚR, kde označíme sloupec s věky zaměstnanců. Potvrdíme a máme současný průměrný věk zaměstnanců.
Určete jméno nejstaršího zaměstnance
V dalším úkolu máme určit jméno nejstaršího zaměstnance. Využijeme pro tento úkol dopočítaný sloupec s věkem. Pro vyhledání jména nejstaršího zaměstnance použijeme vyhledávací kombinaci excelových funkcí INDEX & POZVYHLEDAT, anglicky INDEX & MATCH, všechno ostatní máme k výpočtu k dispozici. Napíšeme funkci INDEX, ve které označujeme pole, kde se nacházejí naše odpovědi, označíme tedy jména zaměstnanců. Následuje středník a funkce POZVYHLEDAT neboli funkce MATCH. Ve funkci POZVYHLEDAT nejprve označujeme, co hledáme. Hledáme maximální hodnotu věku, takže napíšeme funkci MAX, kde označíme sloupec s věkem. Následuje středník a kde tento věk hledáme a hledáme ho ve sloupci s věkem, a hledáme přesnou shodu. Ukončíme závorky u obou funkcí a funkci potvrdíme. Vrátilo se jméno nejstaršího zaměstnance k dnešnímu dni.
Které oddělení má nejméně zaměstnanců
V dalším kroku máme spočítat, které oddělení ve firmě má nejméně zaměstnanců. Abychom tohoto výpočtu byli schopní, tak musíme nejprve určit, na jakém oddělení pracuje, který zaměstnanec. To můžeme určit z ID zaměstnance. Začátek každého ID je určeno písmenem, které určuje oddělení. K jednotlivým písmenům máme oddělení uvedená v pomocné tabulce. Začneme tím, že z ID oddělení oddělíme první písmeno. Vedle pomocného sloupce Věk přidáme nový sloupec, který nazveme třeba kód Oddělení. Zde použijeme funkci ZLEVA, anglicky funkci LEFT, kde označíme jako text ID zaměstnance a počet znaků, které chceme oddělit je 1, jelikož chceme oddělit první písmeno. Funkci potvrdíme a pošleme ji dolů. Teď máme u každého zaměstnance kód oddělení, na kterém pracuje.
Vedle malé pomocné tabulky teď můžeme spočítat, kolik má každé oddělení zaměstnanců. Vytvoříme nový sloupec, který nazveme třeba počet. A v tomto sloupci použijeme funkci COUNTIF, kde nejprve označíme sloupec s písmeny ve zdrojové tabulce, které reprezentují oddělení. Jelikož chceme funkci stahovat směrem dolů, tak nezapomeneme buňky plně zafixovat klávesou F4. A jako kritérium označíme písmeno oddělení v pomocné tabulce. Funkci potvrdíme a pošleme ji dolů. Teď víme, kolik na každém oddělení pracuje lidí.
Konečně tedy můžeme vyhledat oddělení s nejnižším počtem zaměstnanců. Do buňky, kde má být odpověď napíšeme funkci INDEX, kde označíme název oddělení, jelikož to je odpověď, kterou hledáme. Následuje funkce POZVYHLEDAT, neboli funkce MATCH, kde tentokrát hledáme nejnižší číslo, takže použijeme funkci MIN, kde označíme dopočítaný sloupec s počty zaměstnanců a tento nejnižší počet hledáme ve sloupci s počtem zaměstnanců a hledáme přesnou shodu. Ukončíme závorky u obou funkcí a potvrdíme a správná odpověď je, že nejméně lidí pracuje na oddělení Marketingu.
Kolik zaměstnanců žije v Brně?
V dalším úkolu máme spočítat, kolik zaměstnanců žije v Brně. U každého zaměstnance máme v tabulce uvedené PSČ jeho bydliště a zároveň zde máme druhou pomocnou tabulku, kde ke každému PSČ máme přiřazené město. Abychom spočítali, kolik zaměstnanců žije v Brně, tak stačí použít funkci COUNTIF. Klikneme do buňky s odpovědí a ve funkci COUNTIF označíme v oblasti sloupec PSČ ze zdrojové tabulky, a jako kritérium použijeme PSČ Brna z pomocné tabulky. Funkci ukončíme a potvrdíme. V Brně žije celkem 5 zaměstnanců.
Kolik zaměstnanců nežije v Jihlavě?
Další otázka je podobná, máme zodpovědět, kolik zaměstnanců nežije v Jihlavě. Existuje více způsobů, jak vyřešit odpověď na tuto otázku. Můžeme buď spočítat, kolik lidí žije v jednotlivých městech a odečíst Jihlavu a nebo spočítáme, kolik lidí žije v Jihlavě a toto číslo odečteme od celkového počtu zaměstnanců.
Ukážeme si dva způsoby výpočtu.
Nejprve použijeme výpočet s pomocným sloupcem v pomocné tabulce. Do tabulky s Městy vložíme nový sloupec, který nazveme třeba jako počet. V tomto sloupci opět pomocí funkce COUNTIF dopočítáme, kolik lidí žije v jednotlivých městech. Takže funkce COUNTIF, kde jako oblast označíme sloupec s PSČ ve zdrojové tabulce, buňky zafixujeme klávesou F4, a to plně, a jako kritérium označíme první PSČ. Funkci ukončíme a stáhneme ji dolů. Teď víme, kolik zaměstnanců žije v každém městě.
Teď můžeme v buňce s odpovědí použít funkci SUMIF, kde nejprve označíme sloupec s kritérii, což je sloupec s PSČ v pomocné tabulce. Následuje kritérium a kritérium je, že se PSČ nerovná PSČ Jihlavy, jelikož chceme spočítat všechny zaměstnance, kromě Jihlavy. A jako oblast součtu označíme nový pomocný sloupec, který jsme vytvořili pomocí funkce COUNTIF. Funkci ukončíme a potvrdíme a víme, že v Jihlavě nežije 21 zaměstnanců.
Druhou možností je použití funkce COUNTIF bez pomocného sloupce. Do buňky s odpovědí rovnou napíšeme funkci COUNTIF, kde označíme v parametru oblast sloupec s PSČ ze zdrojové tabulky, a jako kritérium označíme PSČ Jihlavy v pomocné tabulce. Pokud funkci potvrdíme, tak pomocí funkce COUNTIF zjistíme, kolik zaměstnanců žije v Jihlavě, tedy 4. To znamená, že ostatní v Jihlavě nežijí, takže od funkce COUNTIF odečteme funkci, která spočítá celkový počet zaměstnanců. Použijeme funkci POČET2, anglicky funkce COUNTA, která počítá neprázdné řádky a je tedy schopná vrátit celkový počet neprázdných řádků, což je v podstatě celkový počet zaměstnanců. Ve funkci POČET2 označíme například sloupec PSČ ve zdrojové tabulce a od ní odečteme naši funkci COUNTIF. Potvrdíme a víme, že v Jihlavě nežije 21 zaměstnanců.
U kolika zaměstnanců není vyplněný rodinný stav?
Teď máme zjistit, u kolika zaměstnanců nemáme v tabulce vyplněný rodinný stav. Chceme tedy spočítat počet prázdných buněk ve sloupci Status. I zde použijeme funkci COUNTIF. V parametru oblast označíme sloupec s rodinným stavem ve zdrojové tabulce a jako kritérium použijeme znak pro prázdnou buňku, tedy dvě uvozovky. Funkci potvrdíme a vrátilo se správně, že rodinný stav nemáme vyplněný u celkem sedmi zaměstnanců.
Kolik procent zaměstnanců je svobodných?
Další otázka zní, kolik procent zaměstnanců je svobodných. Do svobodných máme započítat i zaměstnance, u kterých rodinný stav vyplněný nemáme. Zde existuje několik různých způsobů, jak můžete příklad vyřešit. Můžete použít několik podmínek ve funkci SOUČIN.SKALÁRNÍ neboli ve funkci SUMPRODUCT. Nebo můžete použít COUNTIF v kombinaci se zástupnými znaky atd.
My si ukážeme asi nejjednodušší metodu s pomocným sloupcem. V pomocném sloupci nejprve ověříme, zda je zaměstnanec v manželství. Zde je trochu chyták, jelikož zde musíme ověřit dvě podmínky, zda je muž ženatý a žena vdaná. Vytvoříme tedy pomocný sloupec Stav a ověříme podmínku. Začneme s funkcí KDYŽ, anglicky funkcí IF, kde v podmínce musíme ověřit dvě kritéria, která jsou v logickém vyjádření NEBO. Takže napíšeme funkci NEBO, neboli anglicky funkcí OR, kde první podmínkou bude, že se buňka rovná slovu Ženatý, středník a druhá podmínka je, že se buňka rovná slovu Vdaná. Obě hodnoty musí být uvedené v uvozovkách, jelikož se jedná o textovou hodnotu. A pokud je tato podmínka splněná, tak chceme vrátit nulu a pokud není splněná, tak chceme vrátit jedničku. Funkci ukončíme a stáhneme dolů a teď máme jedničku u nevyplněných stavů a svobodných.
V buňce s výsledkem stačí sečíst pomocí funkce SUMA tento sloupec, což nám dá počet svobodných. A jelikož výsledek měl být v procentech, tak to celé vydělíme funkcí POČET2, neboli funkcí COUNTA, kde spočítáme celkový počet zaměstnanců. Potvrdíme a máme výsledek v procentech.
Kolik zaměstnanců pracuje k dnešnímu dni ve firmě déle než 20 let?
Za další máme spočítat, kolik zaměstnanců pracuje ve firmě déle než 20 let. K tomu nejprve musíme dopočítat, kolik let u nás jednotliví zaměstnanci pracují. Vytvoříme v tabulce další pomocný sloupec, který nazveme roky ve firmě, kde od letošního roku odečteme rok nástupu do zaměstnání. Začneme tedy funkcí ROK, anglicky funkcí YEAR, do které zabalíme funkci DNES, anglicky funkci TODAY. A od této funkce odečteme druhou funkci ROK, ve které označíme datum nástupu zaměstnance do práce. Funkce potvrdíme a pošleme je dolů.
Teď stačí pomocí tohoto sloupce spočítat, kolik zaměstnanců u nás pracuje déle než 20 let. Funkce COUNTIF, kde označíme tento pomocný sloupec a jako kritérium bude znaménko větší v uvozovkách, ampersand a číslo 20. Ukončíme závorku, potvrdíme a víme, že u nás ve firmě pracuje více než 20 let celkem 15 lidí.
Jeden zaměstnanec ve firmě pracuje 30 let, který?
V dalším úkolu máme zjistit, který zaměstnanec u nás pracuje 30 let. K tomu můžeme použít jakoukoliv vyhledávací funkci, třeba INDEX & POZYHLEDAT. Napíšeme funkci INDEX, kde označíme pole, kde se nacházejí naše odpovědi, tedy sloupec se jmény zaměstnanců. Následuje funkce POZVYHLEDAT, anglicky funkce MATCH, kde napíšeme, co hledáme. Hledáme zaměstnance, který u nás pracuje 30 let, takže hledáme číslo 30 a kde ho hledáme. Hledáme ho ve sloupci počet odpracovaných let a hledáme přesnou shodu. Potvrdíme a funkce správně vrátila jméno zaměstnance.
Kolik zaměstnanců je ve věku 35-45 let?
Dále máme zodpovědět otázku, kolik zaměstnanců je mezi lety 35-45 let věku. I zde můžeme použít funkci COUNTIF, ale tentokrát ve formátu COUNTIFS, která dovoluje použít více podmínek. Pomocný sloupec s věkem jsme si dopočítali v předchozích krocích. Takže teď stačí použít funkci COUNTIFS, kde označíme pomocný sloupec s věkem, a první kritérium je, že věk musí být vyšší nebo rovno 35, znaménko vyšší a rovná se v uvozovkách a spojené s číslicí 35 ampersandem. Následuje druhá oblast, což je opět sloupec s věkem a druhé kritérium je naopak znaménko menší a rovná se v uvozovkách, ampersand a číslice 45. Ukončíme závorku a potvrdíme. A máme správný počet zaměstnanců, kteří jsou ve věku 35-45 let.
Kteří 3 zaměstnanci pracují ve firmě nejdéle?
V dalším úkolu máme určit jména tří zaměstnanců, kteří ve firmě pracují nejdéle. Počet let, kteří zaměstnanci ve firmě pracují, jsme si už dopočítali. Hledáme jméno zaměstnance, takže použijeme funkci INDEX, kde označíme sloupec se jmény, sloupec plně zafixujeme klávesou F4, následuje funkce POZVYHLEDAT, kde v parametru co hledáme použijeme funkci LARGE, víme, že ve funkci LARGE označíme sloupec s roky, plně zafixovaný klávesou F4 a jako k označíme jedničku. Následuje parametr kde, což je sloupec s odpracovanými roky, opět plně zafixovaný, a hledáme přesnou shodu. Potvrdíme a máme správný výsledek pro první místo. Jelikož máme buňky správně zafixované, tak funkci stáhneme dolů a máme správně doplněná jména zaměstnanců, kteří ve firmě pracují nejdéle.
Na kterém oddělení pracuje Hana Mladá?
Další otázka zní, na kterém oddělení pracuje Hana Mladá? Opět existuje několik různých způsobů, jak to vyřešit. V předešlých krocích jsme už z kódu zaměstnance vyselektovali kód oddělení, takže teď můžeme ke každému jménu přiřadit správný název oddělení, na kterém pracuje a následně jen vyhledat Hanu Mladou. Vytvoříme tedy další pomocný sloupec s názvem Oddělení, kde použijeme vyhledávací funkci SVYHLEDAT, anglicky funkci VLOOKUP. Hledáme kód oddělení, a hledáme ho v pomocné tabulce, která musí být plně zafixována, odpověď je ve druhém sloupci a hledáme přesnou shodu. Funkci potvrdíme a pošleme dolů.
Teď stačí vyhledat Hanu Mladou. Můžeme pro změnu opět použít funkci SVYHLEDAT. Hledáme sousloví Hana Mladá v uvozovkách a hledáme ho ve zdrojové tabulce. Tady je trochu chyták, jelikož hledaná položka se musí nacházet jako první, takže označíme pouze část tabulky, a to od jména zaměstnance až po oddělení, následuje pořadové číslo sloupce, kde se nachází odpověď, tedy pořadoví číslo oddělení, což je devátý sloupec a následuje přesná shoda. Potvrdíme a víme, že Hana Mladá dělá na oddělení HR.
Hotovo.
Gratuluji. Dokončili jste celý test z Excelu. Doufám, že se vám video líbilo a že jste si procvičili schopnost použít excelové funkce v praktických příkladech. Cílem bylo ukázat, že vám ve skutečnost k efektivní práci v Excelu stačí jen několik málo funkcí, které když se naučíte detailně, tak si s nimi vystačíte na hodně dlouhou dobu a v hodně situacích. Pokud se vám video líbilo, tak se nezapomeňte stát odběratelem kanálu Akademie Excelu zde na Youtube. A já se zatím budu těšit u dalšího videa.
Jedna odpověď
😊👍