V dnešním prvním díle seriálu Excel k pohovoru máme zadání s několika úkoly, které máme vyřešit. Jedná se o ucelený příklad, který je určen spíše začátečníkům a mírně pokročilým v Excelu. Ti z vás, kteří si chtějí práci v Excelu procvičit, ať už k pohovoru nebo jen tak, si mohou cvičný excelový soubor stáhnout a zkusit si řešení nejprve sami. Následně se podívejte na toto video, kde vám ukáži, jak jednotlivé příklady vyřešit.
Excelový soubor ke stažení
Excel k pohovoru I. | Procvičte si Excel nejen k pohovoru
Na pohovoru byl před vámi otevřený následující excelový soubor, kde vpravo máme vyznačené zadání s úkoly a chce se po nás, abychom pracovali s touto tabulkou. V zadání máte možnost si zaškrtnout úkoly, které jsme vyřešili, tím že zaškrtneme pole Splněno. Tím se zakřížkuje pole a úkol zešedne a přeškrtne se, dole pod tabulkou se vám počítá celkový počet získaných bodů. Každý úkol je za jeden bod. Maximální počet bodů je tedy 20.
A rovnou se vrhneme na řešení prvního úkolu.
1. Přejmenujte list
Prvním úkolem je přejmenovat list, na kterém se nachází tabulka ze současného všeobecného názvu List1 na název Prodejní data. List v excelovém souboru přejmenujeme tak, že na list klikneme pravým tlačítkem myši a vybereme možnost Přejmenovat. Tím se název listu označí a my ho teď můžeme přejmenovat. Nebo na list dvojitě poklepáme levým tlačítkem myši, což opět označí současný název a dovolí nám list přejmenovat. Je jedno, kterou variantu zvolíte. Smažeme tedy název List1 a přejmenujeme list na Prodejní data.
2. Tmavě modrou barvou zvýrazněte záhlaví tabulky a změňte barvu písma
Druhým úkolem je zvýraznit záhlaví tabulky tmavě modrou barvou, neboli máme změnit barevnou výplň záhlaví. Označíme tedy buňky záhlaví a klikneme na záhlaví pravým tlačítkem myši a vybereme Formát buněk a zde klikneme na Výplň a vybereme příslušnou barvu. Zadání říká, abychom zvolili tmavě modrou barvu, takže nějakou vybereme a potvrdíme.
V dalším kroku máme ještě změnit barvu písma v záhlaví na bílo. Opět tedy označíme celé záhlaví a opět klikneme na záhlaví pravým tlačítkem myši a vybereme Formát buněk a na vybereme záložku písmo. Zde si pod záložkou barva můžeme zvolit barvu písma.
3. Zformátujte sloupec cena na měnu bez desetinných míst
V dalším kroku máme zformátovat čísla ve sloupci měna na českou měnu bez desetinných míst. Označíme tedy číselné hodnoty ve sloupci a klikneme na ně pravým tlačítkem myši a vybereme Formát buněk a Měna. Zde můžeme nastavit počet desetinných míst. Zadání říká bez desetinných míst, takže nastavíme nula. Potvrdíme a úkol je hotový.
4. Zformátujte sloupec počet na číslo s jedním desetinným místem
Podobný úkol máme i v dalším kroku. Tentokrát máme změnit formát čísla ve sloupci Počet kusů na číslo s jedním desetinným místem. Znovu tedy označíme číselné hodnoty ve sloupci a klikneme pravým tlačítkem myši a vybereme Formát buněk. Zde opět vybereme záložku Číslo, kde máme možnost nastavit počet desetinných míst. Nastavíme jedno desetinné místo a kdyby zadání říkalo, abychom oddělili ještě mezerou tisíce, tak bychom zaškrtli pole Oddělovat 1000. My máme ale v příkladu pouze nízká čísla, takže to dělat nemusíme. Výběr potvrdíme a úkol je hotový.
5. U všech sloupců v tabulce nastavte stejnou šířkou 16 px
Dalším úkolem je nastavit všechny sloupce v tabulce na stejnou šířku, a to 16 pixelů. Abychom nastavili šířku pro všechny sloupce najednou, tak nejprve všechny sloupce označíme. Nestačí ovšem označit pouze sloupce v tabulce, nebo tabulku. Pro nastavení šířky sloupců musíme označit přímo celé sloupce. Klikneme tedy na sloupec A, stiskneme levé tlačítko myši a táhneme myší doprava až označíme všechny potřebné sloupce. Když máme sloupce označené tak na sloupce klikneme pravým tlačítkem myši a vybereme Šířka sloupce. Zde nastavíme požadovanou šířku sloupce, tedy 16. Potvrdíme a sloupce se srovnaly na stejnou šířku.
6. Vložte do tabulky jednoduchý filtr
V kroku číslo šest máme do záhlaví tabulky vložit jednoduchý filtr. Máme několik možností, jak filtr do tabulky vložit. Buď můžeme označit buňky v záhlaví a na kartě Domů vybrat možnost Seřadit a filtrovat. Když tuto ikonu rozklikneme, tak zde máme na výběr Filtr. Klikneme na filtr a tím se do záhlaví tabulky vloží jednoduchý filtr. Filtr v tabulce poznáme podle těchto ikon šipek, které značí, že na záhlaví můžeme kliknout a ve filtru vybrat, co chceme vyfiltrovat. Druhou možností, jak do tabulky rychle vložit jednoduchý filtr je, kliknout do tabulky a zmáčknout klávesovou kombinaci CTRL+SHIFT a písmeno L. Tím se do záhlaví tabulky rovněž vloží jednoduchý filtr.
7. Do buňky C1 vložte vzorec, který spočítá celkový počet kusů, výpočet musí reagovat na filtr v tabulce
V dalším kroku máme do buňky C1, tedy nad sloupec s počtem kusů vložit výpočet, který sečte počet prodaných kusů, nicméně výpočet musí reagovat na automatický filtr v tabulce. Co se tímto zadáním myslí, je to, že když ve filtru uplatníme jakýkoliv filtr a vyfiltrujeme tabulku, tak se výpočet součtu v buňce C1 musí přepočítat podle aktivního filtru v tabulce. Zadání nás tedy zkouší, zda známe rozdíl mezi funkcí SUMA a funkcí SUBTOTAL. Pokud bychom totiž použili pro výpočet jednoduchou funkci SUMA, tak výpočet nebude reagovat na filtr v tabulce. Musíme tedy použít funkci SUBTOTAL, ve které použijeme funkci SUMA, jelikož funkce SUBTOTAL reaguje na skryté a filtrované řádky.
Do buňky C1 tedy napíšeme funkci SUBTOTAL, kde jako funkci vybereme číslo devět, tedy SUMA, následuje oblast buněk, což jsou buňky, které chceme sečíst. Označíme tedy hodnoty ve sloupci a ukončíme závorku u funkce a funkci potvrdíme. Funkce vrátila celkový součet 46.
8. Do buňky D1 vložte vzorec, který spočítá průměrnou cenu, výpočet musí reagovat na filtr v tabulce
Velmi podobné zadání je i u následujícího úkolu, kde máme do buňky D1 vložit výpočet, kde spočítáme průměrnou cenu produktů. Výpočet ovšem opět musí reagovat na filtr v tabulce. Zadání úkolu je tedy rovněž zkouškou toho, zda znáte rozdíl mezi funkcí PRŮMĚR a funkcí PRŮMĚR ve funkci SUBTOTAL. Stejně jako funkce SUMA nereaguje na filtr v tabulce, tak na něj nereaguje ani funkce PRŮMĚR.
Opět tedy k vyřešení úkolu využijeme funkci SUBTOTAL. Napíšeme funkci SUBTOTAL, kde tentokrát vybereme funkci 1, tedy průměr. V oblasti buněk vybereme hodnoty ve sloupci, ukončíme závorku a funkci potvrdíme. Když máme průměr spočítaný, tak ho rovnou zformátujeme jako měnu bez desetinných míst, stejně jako je to u sloupce hodnot ve sloupci. Označíme buňku a na kartě Domů vybereme Číslo a zde si pomůžeme rychlým výběrem měny a tlačítkem Odebrat desetinná místa zrušíme desetinná místa.
9. Spočítejte celkovou tržbu
V tabulce máme připravený sloupec s názvem Tržba celkem, kde máme v dalším kroku spočítat celkovou tržbu pro jednotlivé produkty. Výpočet je jednoduchý. Stačí kdy v první buňce pro první produkt vynásobíme počet kusů a cenu produktu, čímž dostaneme celkové tržby. Výpočet z první buňky stáhneme dolů pro ostatní řádky. V tomto případě nemusíme buňky nijak fixovat, jelikož se výpočet posouvá spolu s buňkami. Takže stačí, když vzorec jednoduše stáhneme dolů pro všechny buňky.
10. Ve sloupci cena označte zeleně buňky, které jsou vyšší než průměrná cena sloupce (neoznačujte barvou manuálně)
V dalším kroku máme zeleně označit buňky, ve kterých je cena produktů vyšší než průměrná celková cena všech produktů. Průměrnou cenu produktů jsme vypočítali v kroku 8 v buňce D1, takže teď musíme jen přijít na způsob, jak označit buňky na zeleno, jestliže zadání zní, že to nemáme dělat manuálně. Nejvhodnějším řešením pro tento úkol bude podmíněné formátování. Označíme ceny ve sloupci a na kartě Domů vybereme ikonu Podmíněné formátování. Zde vybereme Pravidla zvýraznění buněk a možnost Větší než.
Máme na zeleno obarvit buňky, které jsou vyšší než průměrná cena. Takže zde v buňce vybereme buňku, kde máme spočítanou průměrnou cenu produktů. Vedle v poli vybereme formát. Máme zde předvolený zelený formát buňky, což pro splnění úkolu bude stačit. Výběr potvrdíme a buňky s cenou, která je vyšší než průměrná cena se označily na zeleno. Jelikož jsme podmíněné formátování navázali přímo na buňku s výpočtem průměrné ceny, tak i toto podmíněné formátování bude reagovat na změny ve filtru tabulky.
11. Spočítejte cenu bez DPH
V dalším kroku máme spočítat ve sloupci F cenu produktů bez DPH. Výše sazby DPH je uvedená v buňce F1, tedy 21%. K tomuto výpočtu žádnou speciální funkci nepotřebujeme, takže začneme s výpočtem. Cenu bez DPH získáme tak, že označíme cenu včetně DPH, kterou máme v první buňce a tuto hodnotu vydělíme sazbou DPH, kterou máme v buňce F1, ke které přičteme jedničku. Celý tento jmenovatel uvedeme do závorky. Tím ale výpočet nekončí. Jelikož chceme tento výpočet stáhnout pro ostatní buňky dolů, tak si musíme dát pozor na správnou fixaci buněk. Buňku ceny včetně DPH fixovat nemusíme, jelikož ta se bude posouvat spolu s tím, jak vzorec potáhneme dolů. Musíme ale zafixovat buňku se sazbou DPH, tedy buňku F1, a to jak pro sloupce, tak řádky pomocí jednoho stisknutí klávesy F4. Teď máme teprve výpočet správně, potvrdíme ho a stáhneme ho pro ostatní buňky dolů.
12. Ve sloupci Statut ověřte následující podmínku (příklad vyřešte pomocí funkcí):
- Pokud je počet prodaných kusů ve sloupci Počet větší nebo roven 5, tak na řádek napište „Splněno“
- Pokud je počet prodaných kusů menší, tak na řádek napište „Nesplněno“
Toto zadání má prověřit, zda žadatel umí pracovat s jednoduchou podmínkovou funkcí KDYŽ. Zadání po nás chce, abychom na řádky, kde je Počet prodaných kusů větší nebo roven číslu 5 uvedli slovo Splněno a v ostatních případech uvedli slovo Nesplněno. Součástí zadání je i to, že tento úkol máme vyřešit pomocí funkce. Takže nemůžeme pouze vizuálně kontrolovat hodnoty ve sloupci Počet kusů a manuálně do buněk tato slova napsat. Jedná se o klasický příklad na funkci KDYŽ.
Do první buňky (G3) napíšeme funkci KDYŽ, otevřeme závorku a prvním parametrem funkce KDYŽ je podmínka. Zde ověříme, zda je počet kusů větší nebo roven číslu 5. Napíšeme středník a následuje parametr ANO, tedy co se má stát, pokud podmínka splněná bude. Pokud bude počet kusů větší nebo roven 5, tak chceme vrátit slovo Splněno, jelikož se ale jedná o text, tak ho musíme uvést do uvozovek. Napíšeme středník a posledním parametrem je NE, tedy co se má stát, pokud podmínka splněná nebude. V takovém případě chceme vrátit slovo Nesplněno a opět nesmíme zapomenout na uvozovky. Tím je celá funkce hotová, takže ukončíme závorku. Musíme fixovat buňky, když víme, že budeme funkci stahovat dolů? Nemusíme. Takže můžeme funkci potvrdit a stáhnout ji pro všechny buňky dolů.
13. Slovo neslněno označte modře a slovo splněno žlutě
V dalším kroku máme znovu využít podmíněného formátování, jelikož máme buňky, které obsahují slovo Nesplněno obarvit na modro a buňky se slovem Splněno obarvit na žluto. Začneme tedy tak, že označíme všechny hodnoty ve sloupci Stav a na kartě Domů vybereme Podmíněné formátování a vybereme Pravidla zvýraznění buněk. Zde vybereme Rovná se.
Chceme nejprve formátovat buňky, které v sobě mají slovo Splněno, napíšeme tedy slovo Splněno a vedle vybereme formát. Tentokrát chceme buňky obarvit žlutě, takže rozklikneme seznam a vybereme Vlastní formát a zde vybereme Výplň a zvolíme si nějakou žlutou barvu. Potvrdíme výběr a buňky se obarvily.
Teď znovu označíme všechny buňky a znovu vybereme Podmíněné formátování a Pravidla zvýraznění buněk a Rovná se. Formátujeme slovo Nesplněno, takže napíšeme Nesplněno a v seznamu vybereme Vlastní formát > Výplň a zde vybereme modrou barvu. Potvrdíme a úkol je hotový.
14. Ve sloupci Marže ověřte následující podmínky (příklad vyřešte pomocí funkcí):
- Pokud je celková tržba vč. DPH mezi 400 a 600, tak tržby násobit 5%
- Pokud je tržba vyšší než 600, tak násobit 10%
- Pokud je tržba nižší tak násobit 2%
Dalším úkolem je spočítat celkovou výši marže z tržeb. Toto zadání testuje dvě věci, první je, zda umíte použít složenou podmínkovou funkci KDYŽ. V tomto příkladu musíme použít nejen složenou podmínkovou funkci KDYŽ, ale musíme ji zkombinovat i s funkcí A. Druhá věc je ta, že nestačí pouze na řádky přiřadit správnou výši marže v procentech, ale máme rovnou spočítat marže z tržeb, tedy výše marže krát tržby.
Začneme postupně. Do první buňky napíšeme první funkci KDYŽ, otevřeme závorku a začneme první podmínkou, tedy pokud je tržba celkem menší nebo rovna hodnotě 400, toto je celá první podmínka. Následuje středník a parametr, co se má stát, pokud tato podmínka bude splněná. V takovém případě chceme tržby vynásobit 2%. Jelikož musíme ověřit i další kritéria, tak místo parametru NE musíme napsat druhou funkci KDYŽ, kde ověříme druhou podmínku. Druhá podmínka je, že pokud jsou tržby mezi 400 až 600 Kč. Právě zde musíme použít ještě funkci A. Jelikož tržby mají být mezi 400 a 600 Kč a obě tyto podmínky musí platit zároveň. Napíšeme tedy funkci A, otevřeme závorku a ověříme, že tržby jsou vyšší než hodnota 400, středník a druhá podmínka, že tržby jsou nižší než 600. Ukončíme závorku u funkce A. A co se má stát, pokud bude splněná tato druhá podmínka. V takovém případě chceme tržby vynásobit 5%. Napíšeme středník a zbývá poslední možnost, a to že tržby budou vyšší než 600 Kč. Takže můžeme napsat, že v takovém případě se mají tržby vynásobit 10%. Ukončíme dvě závorky pro dvě funkce KDYŽ a musíme nějaké buňky fixovat? Nemusíme. Takže funkci můžeme potvrdit a stáhnout ji pro ostatní řádky dolů. Hotovo.
15. Do buňky B15 najděte nejnižší hodnotu tržeb, musí reagovat na filtr v tabulce
Dalším úkolem je do buňky B15 najít nejnižší hodnotu z celkových tržeb, a to a pomocí funkcí. K tomuto účelu poslouží nejlépe funkce MIN, která z hodnot určí nejnižší hodnotu. Do buňky B15 napíšeme funkci MIN, kde stačí pouze označit hodnoty. Označíme tedy sloupec s celkovými tržbami a potvrdíme. Funkce MIN vrátila číslo 100, což je nejnižší tržba v tabulce.
16. Do buňky B16 najděte nejvyšší hodnotu tržeb, musí reagovat na filtr v tabulce
Podobný úkol je i další, kde máme tentokrát najít nejvyšší tržbu v tabulce. Opakem funkce MIN je funkce MAX, která najde ze zadaných hodnot nejvyšší hodnotu. Takže napíšeme funkci MAX, kde opět pouze označíme hodnoty, ze kterých chceme určit nejvyšší hodnotu. Funkci potvrdíme a funkce MAX vrátila hodnotu 1500 Kč, což je vizuální kontrolou nejvyšší hodnota v tabulce.
17. Přepočítejte celkové tržby kurzem eura
V dalším kroku máme přepočítat celkové tržby kurzem EUR, který máme uvedený v buňce I1. To není nic složitého a stačí označit buňku s celkovými tržbami a vydělit ji buňkou s kurzem. Nesmíme ale zapomenout plně zafixovat buňku I1, a to jak sloupce, tak řádky. Tedy označit buňku I1 a jednou zmáčknout klávesu F4. Vzorec potvrdíme a stáhneme ho pro ostatní řádky dolů. I přesto, že to zadání neříká, tak se nám tržby teď zobrazují v korunách, správné tedy je, změnit formát buněk na eura. Označíme hodnoty a na pásu karet vybereme formát měny a eura. Desetinná místa klidně můžeme zanechat.
18. Z datumů a tržeb vč. DPH vytvořte sloupcový graf a zformátujte ho podle obrázku
Dalším úkolem je vytvořit graf, který vidíme na obrázku. Máme se rovněž pokusit ho naformátovat tak, aby se co nejvíce podobal grafu na obrázku.
Začneme tedy tím, že vložíme graf. Z obrázku vidíme, že na ose x jsou datumy a jako hodnoty jsou v grafu celkové tržby. Abychom takový graf vložili, tak musíme nejprve označit celý sloupec s datumy, podržet klávesu CTRL a označit celý sloupec s tržbami. Teď můžeme pustit klávesu CTRL a na kartě Vložení najít Grafy a vložit jednoduchý sloupcový graf. Graf si dáme vedle obrázku a pokusíme se ho naformátovat tak, aby se obrázku co nejvíce podobal.
Na obrázku nemáme pomocné vodicí čáry, tak je smažeme. Klikneme na čáry a stačí zmáčknout klávesu Delete.
Název grafu je Celková tržba produktů. Takže dvojitě poklepáme na název grafu a název přepíšeme na Celková tržba produktů. Dále vidíme, že máme na obrázku popsanou osu y. Klikneme tedy na graf a na kartě Návrh grafu vybereme Přidat prvek grafu a vybereme Názvy os > Hlavní svislá. Tím se přidal název osy do grafu a teď ho stačí označit a správně přejmenovat na Tržba celkem.
Dále vidíme, že na obrázku má graf oranžovo hnědou barvu, takže klikneme na sloupce v grafu > musíme si dát pozor, že klikneme pouze jednou, abychom vybrali všechny sloupce najednou a klikneme na ně pravým tlačítkem myši a vybereme Výplň a zkusíme vybrat podobnou barvu.
Na obrázku máme rovněž sloupce daleko silnější. Označíme tedy znovu všechny sloupce v grafu a klikneme pravým tlačítkem myši a vybereme Formát datové řady a zde změníme Šířku mezery. Neznáme přesnou hodnotu, takže zkusíme sloupce rozšířit, aby se co nejvíce podobaly obrázku.
Poslední, co zbývá je do grafu vložit popisky hodnot. Opět klikneme na sloupce a vybereme Přidat popisky dat. Hotovo.
19. Seřaďte tabulku od nejnižších po nejvyšší tržby
V předposledním kroku máme tabulku seřadit dle výše tržeb od nejnižších tržeb po nejvyšší. Jelikož máme v tabulce filtr, tak klikneme na záhlaví celkových tržeb a rozklikneme filtr, zde máme možnost Seřadit od od nejmenšího k největšímu. Potvrdíme a tabulka je seřazená.
20. Vedle tabulky vyhledejte cenu k produktu
Poslední úkol testuje, zda umíme použít základní vyhledávací funkce. Chce se po nás abychom k produktu, který je v buňce A19 vyhledali marži z tabulky. Není zde určeno jakou vyhledávací funkci máme použít, takže si můžeme vybrat. Použijeme nejoblíbenější vyhledávací funkci SVYHLEDAT, anglicky VLOOKUP. Napíšeme funkci SVYHLEDAT, kde jako co hledáme označíme hledaný produkt, tedy produkt v buňce A19. Následuje tabulka. Zde je trochu chyták, jelikož nemůžeme označit celou tabulku, ale označená tabulka musí začínat sloupcem s produkty, jinak by funkce SVYHLEDAT nefungovala. Následuje sloupec, tedy v kolikátém sloupci má funkce SVYHLEDAT hledat. Zase trochu chyták, jelikož sloupec marže je celkově osmým sloupcem v tabulce, ale my to musíme počítat podle označené tabulky, takže ve skutečnosti je sedmým sloupcem. A hledáme přesnou shodu, takže nula. Potvrdíme a zkontrolujeme, že funkce přiřadila správnou hodnotu.
Gratuluji dokončili jste všech 20 úkolů 
Dejte mi vědět v komentáři, kolik bodů jste získali a zdá vám tento test přišel jednoduchý nebo složitý
3 komentáře
Dobrý den. Myslím že v zadání je chyba, šířka sloupce by mela být 12 a ne 12px.
V bodu 3 a 4 jsou stejné obrázky?
Bez nápovědy jsem dosáhl 15 bodů,
Viděl jsem i těžší, sloužily k rozřazení uchazečů.
Vzhledem tomu, že jsem učitelka na střední škole a učím cizí jazyk, tak bych nedopadla moc dobře. Používám pro svou práci převážně textové editory. Nicméně podle vašeho návodu, jsem to zvládla za plný počet. Takže jsem spokojená. U pohovoru ale nevím jak dopadnu. 🙂