V dnešním díle ze série videí Excel nejen k pohovoru se podíváme na základní excelové úkoly, se kterými se můžete potkat u pracovního pohovoru. Tak jako v předchozích videích Excel k pohovoru a Kontingenční tabulky k pohovoru, máme i v tomto příkladu 20 úkolů, které musíme vyřešit.
Excelový soubor ke stažení
Excel k pohovoru pro začátečníky
Dnes byl před vámi na pohovoru otevřený tento excelový soubor, kde na jednom listu máte několik tabulek s úkoly, které máte vyřešit. Když úkol vyřešíte, máte možnost si ho zaškrtnout v poli vedle, čímž se vám počítají body. V excelovém sešitu je dvacet úkolů, takže maximální počet bodů, který můžete získat je dvacet.
Tak jako v předchozím videu ze série Excelů k pohovoru, si pro procvičení můžete stáhnout excelový soubor a zkusit si nejprve úkoly vyřešit sami. Následně se podívejte na řešení v tomto videu. Odkaz na stažení excelového sešitu najdete v popisku tohoto videa.
1.Rozdělte sloupec jmen
V prvním úkolu v dnešním testu z Excelu máme sloupec se jmény (A6:A10), která jsou místo mezery spojené znakem procent. Naším úkolem je rozdělit sloupec jmen do dvou sloupců, a to jméno do jednoho sloupce (B) a příjmení do druhého sloupce (C). Máme několik možností, jak úkol vyřešit. Nejjednodušším způsobem je, použít rozdělení textu do sloupců. Označíme sloupec (A6:A10) a na kartě Data najdeme Text do sloupců. Vyskočí na nás okno průvodce rozdělení textu. Zde musíme vybrat oddělovač. Přepneme se pomocí tlačítka Další na další stranu, kde vybereme jaký oddělovač máme v textu. V našem případě je to znak procent, takže v nabídce oddělovače vybereme Jiné a do pole napíšeme znak %. V okně náhledu dat vidíme, jak bude vypadat výsledek. Proklikneme se na Další stranu, zde nás zajímá pouze buňka cíl. Máme zde přednastavenou buňku A6, což je první buňka. Pokud bychom to potvrdili, tak ve sloupci A zůstanou jména a do sloupce B se vloží příjmení. My ovšem jména máme vložit do sloupce B, přesněji první jméno má být vloženo do buňky B6. Klikneme tedy do pole cíl a změníme buňku na B6. Potvrdíme tlačítkem Dokončit. Úkol je hotový.
2. Záporné hodnoty změňte do účetního formátu se závorkou
Jako další máme zobrazit záporné hodnoty ve sloupci v klasickém účetním formátu, tak jak to vidíme na obrázku, tedy záporná čísla v závorce. Tisíce mají být navíc oddělená mezeru. Abychom toho docílili, tak musíme použít vlastní formát buňky. Označíme celý sloupec hodnot a klikneme pravým tlačítkem myši a vybereme Formát buněk, v nabídce vybereme vlastní formát.
Zde musíme nastavit vlastní formát buňky. Můžeme si přitom trochu pomoci předvolenými formáty v nabídce. Tisíce mají být oddělené mezerou, takže vybereme možnost s křížky. V náhledu dat vidíme, jak bude vypadat první číslo ve sloupci. Abychom docílili toho, že záporné hodnoty budou v závorce, tak musíme za tento první formát napsat středník, formát zkopírovat a vložit ho za čárku ještě jednou. První pozice označuje, jak se budou formátovat kladné hodnoty, tato druhá pozice definuje, jak se budou formátovat záporné hodnoty. Formát zabalíme do závorek. V náhledu dat vidíme, že se číslo zabalilo do závorky. Formát potvrdíme a záporné hodnoty ve sloupci se změnili na čísla v závorkách bez mínusového znaménka.
3. Obarvěte řádek s produktem D
Za další máme obarvit celý řádek v tabulce, na které se vyskytuje produkt D. Samozřejmě to nemáme dělat manuálně tak, že najdeme řádek s produktem a obarvíme ho. K obarvení řádky použijeme Podmíněného formátování. Když chceme obarvit celý řádek, tak musíme označit celou tabulku. Na kartě Domů najdeme Podmíněné formátování. Zde vybereme Nové pravidlo a Určit buňky k formátování pomocí vzorce. Pravidlo pro obarvení bude, že se produkt musí rovnat produktu D. Označíme tedy první buňku v tabulce, kde se nacházejí produkty, napíšeme rovná se a „Produkt D“. Nesmíme zapomenout uvést produkt D do uvozovek, jelikož se jedná o textovou hodnotu. Aby se obarvil celý řádek, tak musíme ale upravit ještě fixování první buňky, z plně zafixované buňky musíme fixaci změnit na fixaci sloupce, aby se buňky mohla posouvat v rámci sloupce. Klikneme na formát buňky a na kartě Výplň vybereme nějakou modrou barvu. Formát potvrdíme. A máme obarvený řádek s produktem D. Pokud produkt G změníme v tabulce a druhý produkt D, tak se obarví i tento řádek. Podmíněné formátování tedy funguje, takže je úkol hotový.
4. Kolik je ve sloupci čísel?
Za další máme ze sloupce spočítat, kolik číselných hodnot se ve sloupci vyskytuje. Jak vidíme, tak máme ve sloupci mix textových i číselných hodnot. Pro spočítání číselných hodnot můžeme využít funkci POČET, anglicky funkce COUNT. Napíšeme funkci POČET, kde pouze označíme celý sloupec hodnot. Funkci potvrdíme a vrátilo se číslo pět, což je správně. Ve sloupci máme pět číselných hodnot. Funkce POČET totiž registruje pouze číselné hodnoty, nikoliv textové hodnoty. Textové hodnoty tato funkce přeskakuje.
5. Kolik je ve sloupci celkem hodnot (číselných i textových)
V dalším příkladu ovšem máme spočítat celkový počet všech hodnot ve sloupci, tedy jak číselné, tak textové hodnoty dohromady. K tomu můžeme využít příbuznou funkce POČET, což je funkce POČET2, v anglické verzi Excelu COUNTA. Tato funkce na rozdíl od funkce POČET počítá i textové hodnoty. Napíšeme tedy funkci POČET2, kde opět označíme celý sloupec hodnot a funkci potvrdíme. Vrátila se hodnota 10, ověříme výsledek tím, že označíme celý sloupec hodnot a na spodní liště vidíme, že počet neprázdných řádků je opravdu 10. Takže výsledek máme správně.
6. Kolikrát máme ve sloupci Jablko?
Posledním úkolem v této sekci je spočítat, kolikrát se v tabulce vyskytuje slovo jablko. K tomu využijeme funkci COUNTIF, což je funkce, která počítá s podmínkou. Napíšeme funkci COUNTIF, kde označíme celý sloupec hodnot, napíšeme středník a kritérium je slovo jablko v uvozovkách, jelikož se jedná o textovou hodnotu. Funkci potvrdíme a vrátilo se číslo 2, což je správně.
7. Přepočet ceny na EUR
Dalším úkolem je přepočítat cenu produktů v tabulce na eura. Kurz eura máme uvedený vedle v buňce, takže stačí vydělit cenu u jednotlivých produktů kurzem. Klikneme do první buňky u prvního produktu, napíšeme rovná se a označíme cenu produktu A a vydělíme ji kurzem v buňce. Jelikož máme v plánu stahovat vzorec pro ostatní produkty dolů, tak musíme buňku s kurzem plně zafixovat jedním stisknutím klávesy F4. Vzorec potvrdíme a stáhneme ho dolů pro všechny řádky. Sloupec převzal formátování sloupce s korunami, takže to opravíme tím, že označíme všechny hodnoty a na horní liště na kartě Domů najdeme záložku číslo, kde máme možnost změnit formát čísla. Vybereme v nabídce měna euro a nastavíme dvě desetinná místa.
8. Označte duplicitní částky v CZK
Dalším úkolem je označit duplicitní hodnoty v této tabulce v české koruně. Označíme celý sloupec hodnot s cenami a na kartě Domů najdeme Podmíněné formátování. Zde vybereme Pravidla zvýraznění buněk a dole Duplicitní hodnoty. Chceme obarvit duplicitní hodnoty, takže nastavení necháme a zadání říká abychom hodnoty obarvily na zeleno, takže vybereme zelený formát a potvrdíme.
9. Graf s dvěma osami
Dalším úkolem je vytvořit graf, který vidíme na obrázku. Zdrojem grafu je tato tabulka s celkovými tržbami a počtem prodaných kusů. Na první pohled vidíme, že máme v grafu dvě veličiny, tržby a počet prodaných kusů. Jelikož mají obě veličiny rozdílné hodnoty, tržby jsou v měně a počet kusů jsou pouhá čísla, tak máme v grafu i dvě osy y. Tržby jsou v grafu zobrazené jako spojnicový graf a počet prodaných kusů jako sloupcový graf.
Tento kombinovaný graf vytvoříme tak, že označíme celou tabulku hodnot a na kartě Vložení vybereme jeden z těchto dvou grafů, buď sloupcový nebo spojnicový. Vybereme třeba sloupcový a graf a potvrdíme. Graf si přesuneme k tabulce a obrázku a zvětšíme ho.
Teď máme v grafu viditelné sloupce pro tržbu, sloupce pro kusy nejsou na první pohled viditelné, jelikož se oproti hodnotám tržeb jedná o velmi malá čísla. Klikneme do grafu pravým tlačítkem myši a vybereme možnosti Změnit typ grafu. Zde v nabídce vybereme Kombinovaný graf. Tržbu máme na obrázku jako spojnicový graf, takže u tržby vybereme spojnicový a počet kusů naopak změníme na sloupcový. Počet kusů máme na vedlejší ose, takže zde zaškrtneme pole vedlejší osa. V náhledu vidíme, jak se graf změnil. Potvrdíme.
Základ grafu máme hotový. Zbývá graf zformátovat. Na obrázku nemáme pomocné vodicí čáry, takže je v grafu označíme a smažeme je klávesou DELETE. Název grafu je Vývoj tržeb a prodaných kusů, takže klikneme na název grafu, původní název smažeme a přepíšeme ho na Vývoj tržeb a prodaných kusů. Sloupce mají černou barvu, takže jednou klikneme na libovolný sloupec a vybereme barevnou výplň a černou barvu. Linie tržeb je světle oranžová, takže označíme linii, klikneme pravým tlačítkem myši a vybereme obrys a světle oranžovou. V grafu na obrázku máme ještě popsané osy, takže klikneme na graf a na kartě Návrh grafu vybereme Přidat prvek grafu a zde vybereme Názvy os a nejprve vybereme Hlavní svislá. Znovu klikneme na Přidat prvek grafu, Názvy os a Vedlejší svislá. Teď postupně klikneme na názvy os a přejmenujeme je tak, jak máme na obrázku, tedy Tržby a Počet kusů.
Pro dalších několik úkolů zde máme tabulku, ve které máme produkty, prodané množství a tržby. Zkusíme postupně odpovědět na jednotlivé otázky.
10. Kolik se prodalo kusů produktu A?
První otázkou je, kolik se celkem prodalo produktů A. K tomuto výpočtu můžeme použít funkci SUMIF, která sčítá hodnoty na základě podmínky. Podmínkou v tomto případě je, že produkt se musí rovnat Produktu A. Napíšeme tedy funkci SUMIF, kde jako oblast označíme sloupec s produkty. Následuje kritérium, což je Produkt A, jelikož se jedná o textovou hodnotu, tak ji do vzorce musíme napsat v uvozovkách. Můžete samozřejmě zvolit i postup, že místo toho, že produkt A napíšete slovy do vzorce, tak ho v tabulce označíte. Výsledek bude stejný. A jako poslední označíme oblast součtu, což je sloupec s Množstvím. Funkci potvrdíme a vrátil se součet prodaného množství u produktu A.
11. Sečtěte tržby nad 10 000 Kč
Dále máme sečíst tržby, jejichž výše je ovšem nad 10 000 Kč. I k tomuto výpočtu použijeme funkci SUMIF. Jako oblast tentokrát označíme sloupec s tržbami, jelikož kritérium se týká výše tržeb. Středník a následuje kritérium, což je, že tržby mají být vyšší než 10 000 Kč. Napíšeme tedy znaménko vyšší v uvozovkách, ampersand a hodnotu 10 000, a jako oblast součtu opět označíme sloupec tržeb, jelikož chceme sčítat tržby. Funkci potvrdíme a máme výsledek. Správnost výsledku můžeme ověřit tím, že označíme všechny hodnoty v tabulce, které jsou nad 10 000 Kč a pohledem na spodní lištu ověříme, že máme výsledek správně.
12. Průměrná tržba produktu D
Pokračujeme dále, a máme spočítat průměrnou tržbu pro produkt D. Použijeme tedy funkci AVERAGEIF, což je příbuzná funkce SUMIF a COUNTIF pro počítání s podmínkami. Ve funkci AVERAGEIF nejprve označíme oblast, což je sloupec s produkty, kritérium je Produkt D, opět buď můžeme produkt D do vzorce napsat, nesmíme ale zapomenout na uvozovky a nebo produkt D označíme v tabulce. Jako poslední označíme sloupec, ze kterého chceme průměr počítat, tedy sloupec tržeb. Funkci potvrdíme.
13. Z kolik % se tržby podílejí na celku?
Další otázka zní, z kolika % se tržby produktu C podílejí na celkových tržbách. Základem výpočtu bude spočítat celkové tržby produktu C. Opět použijeme funkci SUMIF, kde nejprve označíme sloupec s produkty. Následuje kritérium neboli podmínka, což je Produkt C, opět označíme produkt C v tabulce. Jako poslední označíme sloupec s tržbami, jelikož chceme spočítat celkové tržby. Funkci potvrdíme. Vrátil se součet tržeb pro produkt C. Teď stačí toto číslo vydělit sumou celkových tržeb abychom dostali podíl na celkových tržbách. Vrátíme se k funkci a funkci SUMIF vydělíme SUMOU celkových tržeb. Funkci potvrdíme. Buňku nemáme ve správném formátu, takže klikneme na buňku pravým tlačítkem myši a vybereme Formát buněk, zde vybereme procenta a nastavíme třeba dvě desetinná místa. Tržby produktu C se na celkových tržbách podílejí z 25 %.
14. Průměrná cena produktu B
Poslední otázkou je, jaká je průměrná cena produktu B. Cenu v tabulce přímo nemáme, ale máme zde celkovou tržbu a počet prodaných kusů, takže cenu získáme tak, že tržbu vydělíme množstvím. Musíme tedy sečíst všechny tržby pro produkt B a vydělit to celkovým množstvím produktu B. Využijeme zde dvě funkci SUMIF. Začneme součtem tržeb. Ve funkci SUMIF označíme sloupec s produkty, následuje produkt, tedy produkt B, jako oblast součtu označíme sloupec s tržbami. Ukončíme závorku, funkci potvrdíme a ověříme, že vrací správný výsledek. Tuto funkci teď musíme vydělit druhou funkcí SUMIF, kde sečteme prodané množství. Tedy děleno a funkce SUMIF, kde opět označíme sloupec s produkty, jako kritérium produkt B a jako oblast součtu tentokrát sloupec s množstvím. Ukončíme závorku, funkci potvrdíme. Průměrná cena produktu B je 586 Kč.
15. Rozbalovací seznam
Posledním úkolem v této sekci je, abychom do modré buňky vložili rozbalovací seznam s jedinečným seznamem produktů z tabulky. K tomu si nejprve musíme vytvořit tento jedinečný seznam. Kdybychom totiž rozbalovacím seznamu označili celý sloupec s produkty, tak by se v rozbalovacím seznamu objevil celý tento seznam, takže by v něm byl každý produkt několikrát. To ale nechceme, chceme aby v něm byl seznam, kde bude každý produkt pouze jednou. Způsobů, jak můžete vytvořit seznam z produktů je několik. Můžete použít excelové funkce, můžete seznam vytvořit ručně, že hodnoty prostě opíšete. My použijeme způsob, kdy vytvoříme seznam pomocí odstranění duplicit. Označíme celý sloupec s produkty, zkopírujeme ho, a vložíme ho někam vedle, kde nebude překážet. Když máme zkopírovaný sloupec ještě označený, tak na kartě Data vybereme Odebrat duplicity. Výběr potvrdíme a zůstal nám zde pouze seznam s jedinečnými záznamy. Teď z tohoto seznamu můžeme vytvořit rozbalovací seznam. Klikneme do modré buňky, a na kartě Data vybereme Ověření dat. V ověření dat vybereme seznam a jako zdroj označíme tento pomocný seznam produktů. Potvrdíme a máme v buňce rozbalovací seznam.
16. Napište pouze čísla
Jako další úkol máme omezit, jaká čísla budeme moci vepsat do modré buňky. Požadavek je, aby se do buňky dala doplnit pouze čísla od 1 do 2. Toto v Excelu vykouzlíme pomocí ověření dat. Klikneme do modré buňky a na kartě Data najdeme Ověření dat. V rozbalovacím seznamu najdeme celá čísla, jako minimum napíšeme jedničku a jako maximum dvacet. Potvrdíme a do buňky teď můžeme napsat pouze čísla od 1 do 20. V případě, že podmínku porušíme, tak nás Excel upozorní a nenechá nás hodnotu mimo povolený rozsah napsat.
17. Vyhledávání
V dalším příkladu máme za úkol do tabulky dohledat tržby pro jednotlivé společnosti. Tabulku s tržbami a společnostmi máme vedle. Klikneme tedy do první buňky a k vyhledávání použijeme třeba nejoblíbenější vyhledávací funkci SVYHLEDAT, anglicky VLOOKUP. Napíšeme funkci SVYHLEDAT, kde nejprve označíme, co hledáme. Hledáme první společnost v tabulce, takže ji označíme. Vzorec máme v plánu stahovat směrem dolů, takže buňku nemusíme fixovat. Následuje středník a tabulka. Označíme celou zdrojovou tabulku, kde máme uvedené tržby. Tyto buňky ale už fixovat musíme, jelikož s tím, jak potáhneme vzorec dolů by se posouvaly s potažením i tyto buňky, což nechceme. Označíme tedy buňky a jednou zmáčkneme klávesu F4, čímž se buňky zafixují jak pro sloupec, tak řádek. Následuje pořadové číslo sloupce, kde jsou naše odpovědi. Tržby jsou druhým sloupcem, takže napíšeme číslo 2 a hledáme přesnou shodu názvů, takže napíšeme nulu nebo vybereme nepravdu. Funkci potvrdíme a stáhneme ji pro ostatní buňky dolů. Pro společnosti, které nemáme v tabulce uvedené tržby se vrátila chybová hláška, u ostatních společností se doplnily tržby.
18. Pro chybné položky napište nulu
Na první úkol navazuje hned druhý, který nám říká, že pro společnosti, pro které nemáme uvedené tržby v tabulce máme vyplnit nulu. Samozřejmě by bylo chybou teď manuálně označit buňky, kde se objevila chybová hláška a vzorec přepsat na nulu. Tím by vám vzorec zmizel z buňky a výpočet by přestal být dynamický. Správný postup je takový, ošetřit výskyt chybových hodnot funkcí. Někdy můžete k tomuto účelu použít podmínkové funkce KDYŽ neboli IF, v tomto případě si vystačíme s funkcí IFERROR, ve které můžeme nastavit, co se má stát, když se v buňce vyskytne chybová hláška. Funkci SVYHLEDAT tedy zabalíme do funkce IFERROR. Klikneme před funkci a dopíšeme před ní IFERROR, otevřeme závorku a parametrem hodnota je celá funkce SVYHLEDAT, překlikneme se na konec funkce SVYHLEDAT, napíšeme středník a jsme v parametru hodnota v případě chyby, kde napíšeme nulu. Ukončíme závorku a funkci potvrdíme. Poklepáním na pravý spodní roh funkci pošleme dolů pro všechny buňky.
19. Přeneste formát
Za další máme přenést formát ze zdrojové tabulky tržeb na vyhledané tržby. To je jednoduché, stačí označit jakoukoliv buňku tržeb ve zdrojové tabulce, na liště vybrat Kopírovat formát a následně označit všechny tržby v tabulce. Tím se přenese formát ze zdrojové tabulky na vyhledané hodnoty.
20. Zkontrolujte, že v tabulce není chyba, případně ji opravte
Posledním úkolem u této tabulky je zkontrolovat, že v ní nemáme chybu. Pokud na nějakou chybu narazíme, máme ji opravit. Jak nejrychleji přijít na to, zda se všechny tržby doplnili správně? Nejrychlejší způsob je ověřit celkové tržby. Nad zdrojovou tabulkou provedeme kontrolní výpočet v podobě SUMY. Napíšeme SUMA a označíme všechny tržby ve zdrojové tabulce. Funkci potvrdíme a celkový součet je 530 tisíc. To samé uděláme i v tabulce, kam jsme tržby dohledávali. Nad tabulkou napíšeme SUMA a označíme celý sloupec tržeb. Funkci potvrdíme a výsledek je 410 tisíc. Takže v tabulce opravdu máme chybu. Některá z tržeb se nedoplnila správně, což je velmi častý případ i v praxi. Chybu máme najít a opravit.
V tomto případě je rozdíl mezi celkovými tržbami 120 tisíc, což je tržba u Coca Coly. V praxi byste pro vyhledání chyby mohli použít excelové funkce, filtrování nebo třeba kontrolu v podobě označení duplicit. V této malé tabulce vidíme, že problém bude nejspíš u společnosti Coca Cola. Ověříme to v tabulce a vidíme, že se opravdu pro tuto společnost nedoplnila tržba. Když se blíže podíváme na název společnosti, tak vidíme, že někdo omylem mezi slovy Coca Cola vyplnil dvě mezery, kdežto ve zdrojové tabulce je název správně s jednou mezerou.
Chybu buď můžete manuálně opravit, tedy že kliknete do názvu Coca Cola a mezeru navíc prostě smažete. Můžete podobné chyby ale ošetřit i pomocí funkcí, a to zejména, pokud si myslíte, že se podobné chyby budou stávat i v budoucnosti. V takovém případě byste upravili vyhledávací vzorec, a to tak, že byste buňku, ve které je společnost, kterou hledáme, tedy buňka s první společností, zabalili do funkce PROČISTIT, anglicky TRIM. Tato funkce odstraňuje z buněk přebytečné mezery, a to jak na začátku, na konci nebo i uprostřed slov. Upravenou funkci potvrdíme a pošleme ji dolů. Teď se tržby doplnily i pro společnost Coca Cola a to i přesto, že jsme v názvu nechali dvě mezery.
Gratuluji. Dokončili jste celý test z Excelu. Pokud jste s některým úkolem měli problém a nevíte, proč jsme ho vyřešili způsobem, jakým jsme ho vyřešili, tak se podívejte na videa na Akademie Excelu. Ke každému úkolu máme detailní video, kde jednotlivé funkce vysvětlujeme. Odkazy na tato videa najdete v popisku tohoto videa. Pokud se vám video líbilo, staňte se členem Akademie Excelu na našem webu, a získejte tak přístup k bonusovým videím z excelové praxe. Členství je zdarma a můžete ho kdykoliv zrušit. A já se zatím budu těšit u dalšího videa.
3 komentáře
Śkoda, že už jsem ve věku kdy vaše video nemůžu využít v praxi (74let).Jinak dávám 1. 👍😂
😀 děkuju, jsem ráda, že to pro vás slouží aspoň k procvičení 🙂
👍