Excel pro management, ekonomy a podnikatele

=KDY =K D Ž( Ž((A A1= A = 5;“ =5 ;“do dost stač ačuj ujííc cí“ í“;“ ;“není ne n en v =KDY =K DY YŽ Ž(((A Ž A1 A 1=1 =10 10; 0;“d dos ostta ost ač ču ujjííc cíí“; “;“n nen ní v =PL LA ATBA((16 16%/ % 12;3 %/12;3 %/ 2;36;1 3 6;10 6;1 ;10 10 00 000;6 00 ;60 6 0 00 60 00 00) 0) 0)

=CON = =CO =C CO ONCA ON CAT CA TENA TE ENA NAT TE(A TE E((A A1; 1;B B1 1) = CON =C NCA CATE C AT TE EN E NA N ATE TE((A A1 1;B 1; ;B1 B1) B 1) =PLA =PL =P PLA LA ATB TBA( TBA A(13 13%//12;36;200 13% 12;36 000;-80 000) PLA PL PLATBA ATBA BA.ÚRO A ÚRO ROK( OK((Saz azba azb a;Z a; ;;Z Za;P Pp P perr;So r; ouč__ho od;Bu ud__hod d; p) d;Typ

=PLATBA(13%/11;35;150 BA(13%/11;35; 000;-70

Excel pro management, ekonomy a podnikatele Ing. Marek Laurenčík

Nakladatelství a vydavatelství

R

Vzdìlávání, které baví www.com putermedia.cz 1

Informace o verzi Excelu Verze Excelu 2007 a 2010 jsou pro potřeby příkladů a vzorců užitých v této knize téměř totožné. Uvedené postupy jsou proto popisovány tak, že je lze použít pro obě verze Excelu. Obrazovky jsou vyobrazeny v grafické podobě Klasické zobrazení.

CD ke knize Součástí knihy je CD, obsahující všechny důležité příklady uvedené v knize. Díky tomu si popisovanou problematiku můžete okamžitě vyzkoušet i prakticky.

Vstupní znalost Tato kniha předpokládá základní znalost prostředí Excelu, chápání principu tabulkového kalkulátoru a znalost základních operací s Excelem. Je určena především pro mírně pokročilé a specializuje se zejména na finanční funkce.

3

E xcel

= K DY Ž (A1= = 5; =5; 5;“ ;“d do dost dos ost st ač st aču ačuj čuj č čují ujjící u ící“ ící“ í“;“není ;“ ;;“ne ;“n “ne “n n ení ne n v = K DYŽ(A Y Ž(A Ž(A1 Ž ((A (A1=10 A1 A1=1 1=10 =1 10 0;“dos 0; “do dos do dos ostaču staču ttač ač aču čujjící jíc jí ící ííc c cíí““;“n ;“ ení en v =CO = CON CO O N CATE ONC ON CAT AT ATE TENA TEN TE AT TE(A1; TE T E(A (A1 (A1; A1 A1; A 1;;B1 1 B1) B 1)) 1 =C =CON CON ON N CA CATE C AT ATE A T NA TE TEN AT TE T E((A (A1; (A1 A1;B A1; A1 A 1 1;B B1) B1 1)) 1

pro management, ekonomy a podnikatele p

Obsah Základní výpočty v buňce ..................................................................... 12 JEDNODUCHÉ VÝPOČTY, JEJICH KOPÍROVÁNÍ........................................12 SESTAVENÍ VZORCE............................................................................................12 JEDNODUCHÉ NUMERICKÉ VÝPOČTY........................................................12 MOCNINA A ODMOCNINA ..............................................................................13 ZÁKLADNÍ MATEMATICKÉ OPERACE.........................................................13 KOPÍROVÁNÍ VZORCE........................................................................................ 14 POJMENOVÁNÍ BUŇKY A OBLASTI .............................................................. 14 ADRESACE VE VÝPOČTECH ............................................................................ 16 VÝPOČTY POMOCÍ FUNKCÍ .............................................................................18 VLOŽENÍ FUNKCE ...............................................................................................18 PŘEHLED PRAKTICKÝCH FUNKCÍ ................................................................22 PŘÍKLADY POUŽITÍ VYHLEDÁVACÍCH FUNKCÍ ......................................34 CHYBOVÁ HLÁŠENÍ ............................................................................................34 MATICOVÉ VZORCE............................................................................................36

Finanční funkce .................................................................................... 40 ÚVOD K FUNKCÍM .............................................................................................40 FUNKCE FINANCOVÁNÍ – ÚROKOVÝ POČET ...........................................41 FUNKCE INVESTIČNÍHO ROZHODOVÁNÍ ...............................................47 KOMPLEXNÍ PŘÍKLADY NA FINANČNÍ FUNKCE ....................................51

Grafy ....................................................................................................... 58 ZÁKLADY VYTVÁŘENÍ GRAFŮ ......................................................................58 POUŽITÍ JEDNOTLIVÝCH TYPŮ GRAFŮ...................................................... 61 PRÁCE S VYTVOŘENÝM GRAFEM .................................................................63 SOUČÁSTI GRAFU ...............................................................................................64 DALŠÍ MOŽNOSTI PŘI PRÁCI S GRAFY .......................................................64 SPOJNICE TRENDU ..............................................................................................66 GRAF SE DVĚMA OSAMI ...................................................................................67 VLASTNÍ TYP GRAFU .........................................................................................69

Práce se seznamy .................................................................................. 74 ŘAZENÍ V SEZNAMECH.....................................................................................75 PRÁCE S FILTRY ....................................................................................................79 ROZŠÍŘENÝ FILTR ................................................................................................82 SOUHRNY ...............................................................................................................84

4

Obsah

Kontingenční tabulky a grafy ............................................................... 90 JAK KONTINGENČNÍ TABULKA VYPADÁ A K ČEMU SLOUŽÍ? ...........90 PRÁCE S HOTOVOU KONTINGENČNÍ TABULKOU ..................................91 VYTVOŘENÍ KONTINGENČNÍ TABULKY ....................................................94 ÚPRAVY KONTINGENČNÍ TABULKY ...........................................................97 FORMÁTOVÁNÍ A STYLY KONTINGENČNÍ TABULKY ...........................99 OKNO MOŽNOSTI KONTINGENČNÍ TABULKY ......................................104 KONTINGENČNÍ TABULKA S VÍCE POLI .................................................104 VÝPOČTOVÉ POLE V KONTINGENČNÍ TABULCE .................................105 SOUHRN ................................................................................................................106 ZÍSKÁNÍ ÚDAJŮ Z KONTINGENČNÍ TABULKY.......................................106 KONTINGENČNÍ GRAFY .................................................................................108

Manažerské nástroje ........................................................................... 112 PODMÍNĚNÉ FORMÁTOVÁNÍ .......................................................................112 OCHRANA DAT V TABULKÁCH....................................................................123 OVĚŘOVÁNÍ VSTUPNÍCH DAT......................................................................129 HLEDÁNÍ ZPĚTNÉHO ŘEŠENÍ .......................................................................132 PRŮVODCE PODMÍNĚNÝM SOUČTEM ......................................................134 STATISTICKÉ FUNKCE - PŘEHLED A POUŽITÍ ........................................138 TIPY A TRIKY V PRAXI ..................................................................................... 142 SDÍLENÍ SEŠITŮ .................................................................................................. 147 POKROČILÉ TECHNIKY PŘI VYTVÁŘENÍ GRAFŮ ................................. 148 PROPOJENÍ DAT EXCELU S DALŠÍMI APLIKACEMI ...............................151

Šablony a formuláře ............................................................................ 156 ŠABLONA DOKUMENTU – VYTVOŘENÍ A PŘÍKLAD POUŽITÍ .........156 PRAKTICKÁ UKÁZKA VYTVOŘENÍ ŠABLONY FAKTURY .................. 162 PRÁCE S FORMULÁŘOVÝMI PRVKY .......................................................... 166

Uživatelská práce s makry .................................................................. 176 EDITOR JAZYKA VISUAL BASIC ...................................................................183 TVORBA KOMPLEXNÍHO MAKRA POMOCÍ KÓDU ...............................200 PRÁCE S UŽIVATELSKÝMI FORMULÁŘI ................................................... 211

5

Základní výpočty v buňce • výpočty v tabulce s použitím funkce, přehledy funkcí • propojování oblastí buněk mezi sešity a listy

Finanční funkce • souhrn finančních funkcí a jejich použití v praxi • příklady na procvičení finančních funkcí

Grafy • tvorba grafů (typy grafů, jejich úprava, vhodnost pro daný typ dat) • tipy, triky a další funkce při tvorbě grafů

Práce se seznamy • řazení v seznamech, užití filtrů, vytváření souhrnů a jejich exporty • externí data a jejich analýza v Excelu

Kontingenční tabulky a grafy • popis a praktické použití kontingenční tabulky, formátování a styly • výpočtová pole v kontingenční tabulce, kontingenční graf

Manažerské nástroje • podmíněné formátování, ochrana dat, ověřování vstupních dat • hledání zpětného řešení, podmíněný součet, statistické funkce

Tipy a triky v praxi • datové řady, komentáře v buňce, sdílení souborů a částí tabulek • propojení Excelu s aplikacemi Word a PowerPoint

Šablony a formuláře • vytvoření a použití šablony, praktická ukázka šablony pro fakturu • práce s formulářovými prvky a jejich využití v příkladech

Makra v Excelu • vytváření a úprava záznamového makra, proměnné v makru • spouštění a ovládání maker, komplexní příklad užití makra

1 2 3 4 5 6 7 8 9 7

E xcel



VYSVĚTLIVKY K PRVKŮM POUŽITÝM V KNIZE V knize naleznete několik značek, které mají svůj smysl. Na první pohled tak snadno vizuálně pochopíte, zda je text důležitý, zda se jedná o poznámku nebo na co si máte dávat pozor. Poznámka: Tímto symbolem jsou v textu označeny poznámky. Poznámkou je myšlen text, který není nezbytně nutný pro pochopení vysvětlovaného problému. Má za úkol pouze upřesnit nebo doplnit význam textu, případně odkázat na jiné stránky s podobným tématem. Tip: Tímto symbolem jsou v textu označeny všechny tipy. Tipem je myšlen text, který podává návod na vyzkoušení dalších postupů a ukazuje i jiné možnosti řešení daného problému. Upozornění: Tímto symbolem jsou v textu označena všechna upozornění. Upozornění varuje před záludnými překážkami a úskalími, na něž je nutné dávat pozor. Rovněž upozorňuje na nebezpečí v programech a na časté chyby v postupech.

JAK PRACOVAT S KNIHOU, ROZČLENĚNÍ KNIHY Obsah knihy je rozčleněn do devíti témat, ve kterých je popsána problematika jednotlivých probíraných funkcí tabulkového kalkulátoru Excel. Výklad je doplněn syntaxí zápisu vzorců a příklady k procvičení. Na doprovodném CD naleznete připravené výukové soubory k procvičení, na kterých si můžete vyzkoušet použití objasňovaných funkcí. Jednotlivé příklady jsou přehledně rozčleněny do adresářů, které se vždy konkrétně vážou k jednomu z devíti tematických celků knihy. Příklad odkazu na cvičný soubor na CD nosiči: Soubor na CD: Příklady použití funkce SVYHLEDAT naleznete v adresáři Téma1, v příkladu Funkce0.xls na listu SVYHLEDAT. Zadání příkladu: Prodejce je motivován podle tabulky měsíčního prodeje, která je uložena v oblasti B3:C10. Jaká bude jeho provize v % a v Kč při měsíční tržbě 84 500 Kč a 62 150 Kč? Výsledek je uveden na listu přímo v příkladu. Na CD v adresáři Software dále naleznete aplikaci Microsoft Office Excel Viewer 2003, která slouží k prohlížení a tisku souborů *.xls. Neumožňuje však jejich editaci a ukládání změn. Ilustrační obrázky jsou pořizovány z prostředí Excelu kancelářského balíku Microsoft Office 2003 s nastavenou maskou prostředí Windows v Klasickém zobrazení.

8

Základní výpočty v buňce


1 Základní výpočty v buňce • jednoduché výpočty, jejich kopírování • sestavení vzorce • základní matematické operace • adresace ve výpočtech

11

E xcel




ZAOKR.DOLŮ Tato funkce zaokrouhluje číslo na nejbližší nižší násobek nastavené hodnoty. Zadává se ve tvaru: ZAOKR.DOLŮ(číslo;násobek) Je to obrácená obdoba předchozí funkce ZAOKR.NAHORU.

USEKNOUT Funkce zkrátí číslo odstraněním desetinné nebo zlomkové části čísla na určitý počet desetinných řádů. Zadává se ve tvaru: USEKNOUT(číslo;desetiny). Číslo je číslo, které chcete zkrátit, desetiny udávají počet desetinných míst ve zkráceném čísle (výchozí nastavení pro desetiny je nula).

Funkce USEKNOUT a CELÁ.ČÁST jsou podobné v tom, že obě vracejí celá čísla. Funkce USEKNOUT odstraní zlomkovou část čísla. Funkce CELÁ.ČÁST zaokrouhlí číslo na nejbližší menší celé číslo. Funkce CELÁ.ČÁST a USEKNOUT poskytují různé výsledky pouze v případě záporných čísel: USEKNOUT(-4,3) vrací -4, ale funkce CELÁ.ČÁST(-4,3) vrátí -5, protože -5 je menší číslo.

ČÁST Funkce ČÁST vrátí zadaný počet znaků z textového řetězce od zadané pozice na základě zadaného počtu znaků. Zadává se ve tvaru: ČÁST(text;start;počet_znaků) Text je textový řetězec obsahující znaky, které chcete extrahovat. Start je pozice prvního znaku; pro první znak v řetězci se hodnota argumentu start rovná hodnotě 1 atd. Počet_znaků určuje počet znaků vrácených z textu funkcí ČÁST.

24

Základní výpočty v buňce Základní výpočty v buňce

Pokud je hodnota argumentu start větší než délka řetězce text, vrátí funkce ČÁST „“ (prázdný řetězec). Pokud je hodnota argumentu start menší než délka řetězce text, ale součet hodnot start a počet znaků je větší než délka řetězce, vrátí funkce ČÁST znaky od pozice start do konce textu. Pokud je hodnota argumentu start menší než 1, vrátí funkce ČÁST chybovou hodnotu #HODNOTA!. Je-li hodnota počet_znaků záporná, vrátí funkce ČÁST chybovou hodnotu #HODNOTA!. Je-li hodnota počet_bajtů záporná, vrátí funkce ČÁST chybovou hodnotu #HODNOTA!.

ZLEVA Funkce ZLEVA vrátí první znak nebo znaky v textovém řetězci na základě zvoleného počtu znaků. Zadává se ve tvaru: ZLEVA(text;znaky) Text je textový řetězec, ze kterého se budou vybírat znaky. Znaky – určuje počet znaků vrácených funkcí ZLEVA. Toto číslo musí být větší nebo rovno nule. Je-li argument znaky větší, než je délka textu, funkce vrátí celý text. Jestliže počet není uveden, uvažuje se číslo 1.

25

E xcel




ZPRAVA Obdoba funkce ZLEVA. Funkce ZPRAVA vrátí zadaný počet znaků od konce textového řetězce (zprava). Zadává se ve tvaru: ZPRAVA(text;znaky)

SUMIF Tato funkce sečte buňky, které jsou zadané podmínkou. Zadává se ve tvaru: SUMIF(oblast;kritéria;součet) Oblast je oblast, ve které je testován parametr Kritéria. Kritéria udávají podmínku, která definuje sčítané buňky. Vyjadřuje se číslem, výrazem nebo textem. Podmínka může mít například tvar: 31, „66“, „>100”, “ABC 25 25”. Do posledního parametru Součet se zadává oblast buněk, které jsou sčítány. V případě, že je oblast stejná jako parametr Oblast, nemusí být uváděna.

COUNTIF Sečte počet buněk v oblasti, které odpovídají zadaným kritériím. Zadává se ve tvaru: COUNTIF(oblast;kritérium) Oblast je oblast buněk, ve které mají být spočítány buňky. Kritérium definuje, které buňky se budou počítat. Udává se jako čísla, výrazy nebo text. Například 31, „66“, „>100”, “ABC 25 25”.

26



COUNTBLANK Sečte počet buněk v oblasti, které jsou prázdné. Zadává se ve tvaru: COUNTBLANK(oblast)

POČET Vrátí počet buněk, které obsahují čísla, a počet čísel v seznamu argumentů. Pomocí funkce POČET určíte počet položek v numerickém poli v oblasti nebo v matici obsahující čísla. Tato funkce se zadává ve tvaru: POČET(hodnota1;hodnota2;….) Hodnota1, hodnota2,... může být až 30 argumentů, které mohou obsahovat různé datové typy nebo na ně odkazovat. Počítají se však pouze čísla.

27

Finanční funkce

Výpočty pomocí funkcí

2 Finanční funkce • souhrn finančních funkcí a jejich použití v praxi • příklady na procvičení finančních funkcí

39

E xcel



Finanční funkce Úvod k funkcím Nezbytnou součástí práce ekonoma s programem MS Excel je práce s finančními funkcemi. V základní nabídce MS Excelu je několik set funkcí rozdělených do 10 kategorií. V kategorii finančních funkcí je 53 základních funkcí. Další funkce včetně finančních je možné do základní verze MS Excel doinstalovat v doplňku analytické nástroje. Finanční funkce

Většina základních finančních funkcí se zabývá čtyřmi základními okruhy: A. Financování B. Investiční rozhodování C. Výpočty odpisů D. Funkce pro cenné papíry Příklad: Ke každé funkci je uveden praktický příklad v souboru fi nfunkce.xlsx.

Jak nainstalovat doplněk Klepněte na tlačítko Office a v dolní části zobrazeného okna na tlačítko Možnosti aplikace Excel. Ve stejnojmenném okně zadejte příkaz Doplňky. Zobrazí se seznam Zobrazení a správa doplňků sady Microsoft Office. V jeho dolní části rozbalte nabídku pole Spravovat a klepněte na příkaz Doplňky aplikace Excel. Poté klepněte na tlačítko Přejít. V zobrazené nabídce Doplňky k dispozici zaškrtněte políčko u doplňku, který chcete načíst, a potom klepněte na tlačítko OK. Pokud doplněk, který chcete použít, není v nabídce Doplňky k dispozici uveden, klepněte na tlačítko Procházet a požadovaný doplněk vyhledejte.

40

Finanční funkce Funkce Financování – úrokový počet K nejdůležitějším a nejpoužívanějším finančním funkcím patří funkce určené k financování. Jsou založeny na výpočtu úrokové míry a určeny zejména k výpočtům úvěrů, leasingů, plateb úroků, spoření a hypoték. Jednotlivé funkce si probereme na základních příkladech z praxe.

Základní argumenty finančních funkcí Finanční funkce

Většina základních finančních funkcí má následující argumenty, které je nutné pro správný výpočet správně aplikovat. • Sazba je úroková sazba dané půjčky nebo splátky za určené období. Úrokovou sazbu je třeba vložit vztaženou k počtu období. V případě, že se částky splácejí měsíčně, je nutné sazbu (která je zpravidla udávána ročně) vydělit 12, v případě čtvrtletního splácení 4. • Pper je celkový počet platebních období půjčky nebo investice. • Souč_hod je aktuální nebo celková hodnota budoucích plateb. • Bud_hod je předpokládaná budoucí hodnota, které chcete dosáhnout (pokud není uvedena, je 0). • Splátka pravidelné splátky za dané období (obvykle měsíc). V případě, že se jedná o dluh, je nutné splátku uvádět se záporným znaménkem. • Typ je číslo určující, kdy je platba prováděna (0 – na konci, 1 – na začátku období). Pokud není typ uveden, je standardně nastaven typ 0. U argumentů Souč_hod, Bud_hod, Splátka je nutné rozlišovat, zda se jedná o záporný tok peněz (částky které platíte), nebo kladný tok peněz (částky, které obdržíte nebo vám patří).

1. ÚROKOVÁ.MÍRA (pper;splátka;souč_hod; bud_hod;typ;odhad) Popis: Tato funkce počítá úrokovou sazbu vztaženou k úročenému období anuity (anuita = pravidelná platba po určité období s konstantní úrokovou mírou.). Funkce je počítána pomocí iterací a může mít žádné nebo několik řešení. Jestliže úspěšné výsledky funkce ÚROKOVÁ.MÍRA nekonvergují, vrátí funkce ÚROKOVÁ.MÍRA chybovou hodnotu #NUM!. Argument odhad – jedná se o odhad výsledné úrokové míry. Tento údaj není povinný, pokud chybí, je použito 10 % neboli 0,1. Pokud by funkce nekonvergovala, bude výsledkem chyba #NUM! a je třeba opakovat výpočet s jiným odhadem. Pokud je i po zpřesnění výsledek chybný, existuje několik nebo žádné řešení.

41

E xcel

= K DY Ž (A1= = 5; =5; 5;“ ;“d do dost dos ost st ač st aču ačuj čuj č čují ujjící“ u ící ící cí“ í“;“není ;“ ;;“ne ;“n “ne “n n ení ne n v = K DYŽ(A Y Ž(A Ž(A1 Ž ((A (A1=10 A1 A1=1 1=10 =1 10 0;“do 0; “dos do d dos ostaču os staču ttač ač aču čujjící jíc jí ící ííc c cíí““;“n ;“ ení en v =CO = CON CO O N CATE ONC ON CAT AT ATE TENA TEN TE AT TE(A1; TE T E(A (A1 (A1; A1 A1; A 1;;B1 1 B1) B 1)) 1 =C =CON CON ON N CA CATE C AT ATE A TENA TE TEN AT TE T E((A (A1; (A1 A1;B A1; A1 A 1 1;B B1) B1 1)) 1

pro management, ekonomy a podnikatele p Příklad v souboru fi nfunkce.xlsx na listu úroková míra.

Finanční funkce

Nejtypičtějším a pro mnohé překvapivým příkladem je výpočet úrokové míry u splátkových společností. Standardní nabídkou splátkových společností je příklad s heslem „Jen jednu splátku navíc“ – pořiďte si TV v ceně 10 000 Kč, složte jen 1000 Kč a poté zaplaťte 10 x 1000 Kč“. Mnoho i studovaných zákazníků se domnívá, že si půjčili peníze s desetiprocentním úrokem. Úrok je však 23,56 %. Ve skutečnosti si půjčíte 9 000 Kč a máte vrátit 10 000 Kč během 10 měsíců. Úrokovou míru vypočtete podle následujícího vzorce: =ÚROKOVÁ.MÍRA(10;-1000;9000)*12 = 0,2356 = 23,56% p.a. Při výpočtu nesmíte zapomenout, že úroková míra bude vypočtena jako měsíční, proto je nutné ji vynásobit 12. Příklad výpočtu úrokové míry 2 Jakou leasingovou společnost vybrat při leasingu osobního automobilu s pořizovací cenou 300 000 Kč, pokud je k dispozici následující nabídka? 1) Akontace 80 000 Kč, měsíční splátka 6999 Kč po dobu 36 měsíců a odprodej po 36 měsících za 1000 Kč =ÚROKOVÁ.MÍRA(36;-6999;300000-80000;-1000)*12 = 9,3 % 2) Akontace 70 000 Kč a měsíční splátka 4999 Kč po dobu 48 měsíců se závěrečným odkoupením za 10 000 Kč =ÚROKOVÁ.MÍRA(48;-4999;300000-70000;-1000)*12 = 13,6 % Komentář: Jako současnou hodnotu je nutné zadat rozdíl mezi cenou a akontací, tento rozdíl se pak splácí měsíčními splátkami, budoucí hodnota je cena odkupu. Protože výsledkem funkce je měsíční úroková sazba, roční úrokovou sazbu získáte vynásobením číslem 12. U druhé společnosti je úroková míra vyšší. 2. PLATBA (sazba;pper;souč_hod; bud_hod;typ) Popis: Funkce vypočítá stálou splátku půjčky (anuitu) při stálé výši úroku. Tato funkce se používá nejčastěji při počítání splátek spotřebních a hypotečních úvěrů. Příklad Výše měsíčních splátek v souboru fi nfunkce.xlsx na listu platba.

42

Finanční funkce Prodejce elektroniky nabízí půjčku na techniku ve výši 20 000 Kč s úrokem 16 % a dobou splatnosti 24 měsíců. Jaká je výše měsíční splátky? Splátka =PLATBA(+16%/12;24;20 000) = - 967,73

Příklad Výše měsíčních vkladů u penzijního fondu

Finanční funkce

Funkce pro financování se používají nejen u dluhového financování, ale i u spoření. Můžete tak například zjistit, kolik byste museli spořit měsíčně v případě, že byste chtěli mít v penzijním fondu (po 20 letech spoření s průměrným ročním výnosem 3 % ) naspořenou částku 1 000 000 Kč. =PLATBA(3%/12;20*12;0;1000000) = - 3046 Kč Mnoho uživatelů neví, k jakému účelu používat ve funkci platba a úroková míra parametry současná hodnota a budoucí hodnota. Současná hodnota je aktuální stav peněz, které jsou k dispozici, budoucí hodnota je celkový budoucí součet peněz, kterého má být dosaženo.

Příklad Splácení leasingu Praktickým příkladem je nabídka leasingové společnosti ABCfin, která nabízí leasing s vyšší odkupní cenou (zůstatkem dluhu). Tím společnost dosáhla výrazného snížení splátek leasingu, a leasingové splátky jsou tak přístupné většímu okruhu zákazníků. Výpočet je uveden na následujícím příkladu – jak poklesne splátka leasingu automobilu. Základní údaje: pořizovací cena akontace počet splátek odkupní cena úroková sazba

300 000 Kč 100 000 Kč 36 80 000 Kč 13 % p. a.

Splátka s odkupní cenou: =PLATBA(13%/12;36;200 000;-80 000) = - 4910 Splátka bez odkupní ceny: =PLATBA(13%/12;36;200 000) = - 6739 Komentář: Klient si tak může vybrat, zda bude platit vyšší splátky a po skončení leasingu leasingová společnost automobil na klienta převede nulovou hodnotu, nebo bude platit menší splátky a po skončení leasingu zaplatí vyšší odkupní cenu, např. formou, že automobil prodá za 150 000 Kč, 80 000 Kč splatí a zbylou část 70 000 Kč použije jako akontaci na leasing nového automobilu.

43

E xcel



IRR = MIRA.VYNOSNOSTI (hodnoty; odhad) Popis: Funkce vypočte míru výnosnosti pro sérii peněžních toků představovaných číselnými hodnotami. Tyto peněžní toky si nemusí být rovny jako v případě anuity. K peněžním tokům však musí docházet v pravidelných intervalech, například jednou za měsíc nebo za rok. Míra výnosnosti je úroková míra investice zahrnující výdaje (záporné hodnoty) a příjmy (kladné hodnoty), které jsou rozloženy v pravidelných intervalech. • Aby bylo možné vypočítat míru výnosnosti, musí být mezi čísly určenými argumentem hodnoty alespoň jedno kladné a jedno záporné číslo. Finanční funkce

• Funkce MÍRA.VÝNOSNOSTI řadí peněžní toky podle pořadí hodnot v argumentu. Proto je potřeba při zadávání výdajů a příjmů dbát na správné seřazení dle období. • Hodnoty musí být uvedeny za stejné časové období. Příklad Míra výnosnosti investice v souboru fi nfunkce.xlsx na listu NPV Vypočtěte míru výnosnosti předešlého příkladu. IRR = MÍRA.VÝNOSNOSTI({-30;4;8;10;8;15}) = 12,7 % Jako kontrolu můžete použít předchozí funkce. Pokud bude sazba = IRR, bude NPV rovna nule. NPV = ČISTÁ.SOUČHODNOTA(0,1275;4;8; 10;8;15)- 30 = 0

MOD.MÍRA.VÝNOSNOSTI(hodnoty;finance;investice) Popis: Funkce vypočte modifikovanou vnitřní míru výnosnosti pro sérii pravidelných peněžních toků, přičemž tato funkce na rozdíl od předchozí zohledňuje náklady na investici a úrok získaný z reinvestování hotovosti získané z investice. Argumenty: Finance – úroková sazba, která je placena za využité peněžní toky. Investice – sazba získaná při investici peněžních toků. Příklad Míra výnosnosti investice 2 v souboru fi nfunkce.xlsx na listu NPV Jaká je míra výnosnosti při koupi automobilu za účelem dalšího pronájmu na 5 let? Automobil koupíte celý na úvěr 10 %, CF viz následující tabulka. období CF

50

0 -300

1 50

2 50

3 50

4 50

5 100

Finanční funkce MOD.MÍRA.VÝNOSNOSTI(C25:H25;0,1;0,05) = 1,69 %

XNPV (sazba; hodnoty; data) Popis: Tato funkce vypočte čistou současnou hodnotu neperiodických toků.

XIRR ( hodnoty; data; odhad ) Popis: Tato funkce vypočte vnitřní výnosové procento při neperiodických tocích.

Finanční funkce

Příklad Výpočet vnitřního výnosového procenta investice v souboru fi nfunkce.xlsx na listu XNPV Jaké bude vnitřní výnosové procento, pokud pořídíte investici (např. poskytnete půjčku) 100 000 Kč a výnosy z investice přinesou 120 000 Kč v platbách dle tabulky?

Komplexní příklady na finanční funkce 1) Hypotéka – výpočet plateb, úroků, … V následujícím příkladu v souboru hypoteka.xlsx list hypoteka je uveden komplexní příklad výpočtu splátkového kalendáře 20leté hypotéky. Pro přehlednost byla zvolena roční agregace plateb.

51

E xcel



V prvním sloupci – Roční splátky – je pomocí funkce platba vypočtena anuitní splátka, ve sloupcích úrok a jistina jsou pomocí funkcí platba.úrok a platba.základ vypočteny roční platby úroků a jistiny. Sloupec úrok můžete použít k odhadu roční daňové úspory.

Finanční funkce

Ve sloupcích Úrok kum. a Jistina kum. jsou uvedeny informace o již zaplacených úrocích a jistiny od počátku období. Výpočty jsou provedeny pomocí funkcí CUMIPMT a CUMPRINC.

2) Investice Komplexní příklad Vyhodnocení investic v souboru investice.xlsx na listu investice. Společnost Laser, a.s., zabývající se opracováváním nerezových plechů pomocí laserových zařízení, má možnost koupit nový laserový stroj LASER 2000 za cenu 70 mil. Kč. Rovněž dojde ke změně oběžných aktiv ve výši 30 mil Kč. Životnost zařízení je 7 let, po té dojde k prodeji zařízení za 20 mil.Kč. a úbytku oběžného majetku o 10 mil.Kč. Dle tabulky výnosů a nákladů vypočtěte, zda projekt bude efektivní, pokud společnost požaduje zhodnocení 10 %. Příklad můžete vypočítat buď nepřímo pomocí diskontovaného CF, nebo pomocí funkce čistá současná hodnota. V našem případě: NPV = ČISTÁ.SOUČHODNOTA(C16;C13:I13)-C19 C16 = úroková míra C13:I13 = nediskontované CF C19 = cena pořízení investice = 100 mil. Kč

52

Finanční funkce

Finanční funkce

Funkce pro cenné papíry V případě, že jste doinstalovali doplňky, umožňuje Excel počítat velké množství funkcí z oblasti cenných papírů. Pro informaci uvádíme jen vybrané funkce a jejich syntaxe a základní popis. Emise je datum emise cenného papíru. První_úrok je datum výplaty prvního úroku. Vypořádání je datum vypořádání cenného papíru. Datum vypořádání označuje datum následující po datu emise, kdy je cenný papír prodán klientovi. Úrok je roční kuponová sazba cenného papíru. Nom_hodnota je nominální hodnota cenného papíru. Počet_plateb je počet kuponových plateb ročně. Pro roční platby je počet_plateb = 1, pro pololetní je počet_plateb = 2 a pro čtvrtletní je počet_plateb = 4. Kupon je roční kupónová sazba cenného papíru. Investice je částka investovaná do cenného papíru. Zaruč_cena je zaručená cena cenného papíru při splatnosti. Základna je typ úročení.

53

Grafy

Grafy

3 Grafy • tvorba grafů (typy grafů, jejich úprava, vhodnost pro daný typ dat) • tipy, triky a další funkce při tvorbě grafů

57

E xcel



Grafy Data v tabulkách jsou sice přehledná a přesná, ale přece jen pro prezentaci nejsou v některých případech příliš vhodná a na první pohled z nich nejsou vidět mnohé výsledky. V takové chvíli přichází na řadu grafické znázornění hodnot z tabulky – graf. Graf musí být navržen takovým způsobem, aby ukazoval přesně to, co je zapotřebí ukázat, a nezkresloval data.

Základy vytváření grafů V souboru graf.xlsx na listu celkem je zdrojová tabulka, ve které je celkový prodej střediska za měsíc.

Grafy

Cílem je vytvoření grafu, který názorně zobrazí obrat (cena celkem) v porovnání jednotlivých měsíců.

Výběr zdrojových dat Nejprve je nutné věnovat se tabulce, kde jsou zdrojová data pro graf. Tabulka dat nebývá obvykle pro tvorbu dat nijak speciálně upravena, je to jedna ze standardních tabulek výpočtů. Bude tedy obsahovat více sloupců hodnot, z nichž pouze z některých chcete vytvářet graf. Další sloupce dat (pomocné výpočty, mezivýsledky a podobně) buď můžete skrýt (Domů – Formát – Skrýt a zobrazit), anebo je při označování oblasti buněk pro graf nebudete označovat (metodou výběru nesouvislých oblastí označíte pouze ty sloupce, ze kterých chcete vytvořit graf). Konkrétně v tomto příkladu budou označeny oblasti A1:A13 a C1:C13. Prakticky si to vyzkoušíte při vlastním vytváření grafu.

58

E xcel



Výsečové grafy Výsečový graf (známý „koláč“) je vhodný především k zobrazení poměru jednotlivých položek k celku. Takový graf vykresluje pouze jednu datovou řadu, ostatní označené v tabulce ignoruje. Součet všech zobrazených položek je 100 %. Pro zvýraznění některých položek je můžete z grafu „vysunout“. Výsečový graf také obsahuje podtyp 3D, jediný rozdíl ve srovnání s grafem na obrázku (soubor graf-typy.xlxs na listu výsečový a výsečový 3D) je pouze v zobrazení.

XY bodové grafy Pomocí těchto grafů se znázorňuje průběh funkce y=F(x). Platí pro něj většina vlastností stejně jako pro spojnicové grafy. Je však třeba mít na paměti, že tyto grafy jsou vhodné především k zobrazení vztahu mezi jednotlivými hodnotami. Hodnoty jsou vyneseny na osy a v jejich průsečíku je umístěn datový bod. Tyto body pak můžete pospojovat křivkou nebo nechat samostatně, bez křivky.

Grafy

Pokud potřebujete body pouze proložit, nikoliv spojovat, použijeme spojnici trendu. Postupem se budeme zabývat dále.

Plošný graf Plošný graf je na první pohled velmi podobný grafu spojnicovému. Jediný rozdíl mezi nimi je ten, že prostor pod křivkou je u plošného grafu vyplněný. Můžete se setkat také se stoprocentním plošným grafem, u toho je ale důležité dbát na přítomnost záporných hodnot v tabulce. Pokud se tam vyskytují, graf bude nepřesný. Plošný graf má jednu velkou nevýhodu – jednotlivé řady se mohou překrývat, což může způsobit jeho nepřesnost, když se stane, že jedna plocha překryje druhou. Proto je zapotřebí s tímto počítat a na aplikace, kde by mohlo k překryjí dojít, jej nepoužívat. Na obrázku (soubor Graf-typy.xlsx, list Plošný) je plošný graf-skládaný. U toho se jednotlivé hodnoty k sobě přičítají, tudíž nemůže dojít k překrytí.

Prstencové grafy Prstencový graf je velmi podobný grafu výsečovému – znázorňuje poměr jednotlivých částí k celku. Jediný rozdíl a zároveň výhoda v porovnání s výsečovým grafem je možnost zobrazit více datových řad. Každá taková řada pak bude zobrazena jako samostatný prstenec.

62

Grafy

Grafy

Práce s vytvořeným grafem Práci s grafem procvičte na příkladu. Otevřete si soubor graf.xlsx, kde je na listu celkem tabulka, ze které máte vytvořit graf výše obratu v závislosti na jednotlivých měsících. 1. Označte si jednotlivé „řady“ grafu: dvě nesouvislé oblasti A1:A13 a C1:C13 (pomocí Ctrl). 2. Na kartě Vložení ve skupině Grafy vyberte typ grafu Sloupcový a podtyp Dvojrozměrný sloupcový. 3. Klepněte na název grafu. Na samostatném listu bude vytvořen zvolený graf.

63

Práce se seznamy

4 Práce se seznamy

Práce se seznamy • řazení v seznamech, práce s filtry • vytváření souhrnů a jejich exporty • pokročilá práce se skupinou a přehledy • exporty a importy dat, externí data a jejich analýza v Excelu

73

E xcel



Práce se seznamy Pokud v Excelu pracujete s dlouhými tabulkami (například seznamy zákazníků, pracovníků, zboží, obratů...), můžete jej využít jako program pro jednoduché zpracování databází. Zjednodušeně řečeno, základem většiny databází je tabulka, případně tabulky, které jsou vzájemně provázány. Zmíněné dlouhé tabulky zboží, klientů a jiné jsou vlastně takové malé databáze. Údaje v těchto tabulkách jsou uspořádány do řádků a sloupců. Sloupce jsou v tabulce pole (databáze) a řádky pak záznamy. Každý řádek = jeden záznam (jedno zboží, jeden klient apod.).

Práce se seznamy

V ukázkovém příkladu seznamy.xlsx na listu ukázka je příklad takového seznamu – databáze. Jednotlivé záznamy jsou řazeny po řádcích a pole jsou ve sloupcích (označení výrobku, kategorie výrobku…):

S takovou nebo podobnou strukturou tabulek budeme v této kapitole pracovat. Ukážeme si zde na příkladech všechny možnosti práce se seznamy (řazení, filtry, souhrny…). Uvedená tabulka pro práci se seznamy nesmí mít nikde prázdný sloupec nebo řádek. V opačném případě by většina možností práce se seznamy nefungovala – příslušná funkce nebo vlastnost se totiž vždy provede jen s tou částí tabulky, ve které stojí kurzor, a další části tabulky oddělené prázdným sloupcem nebo řádkem zůstanou stát na původním místě a v původní formě. Tím se integrita tabulky poškodí a patrně bude dále nepoužitelná.

74

Práce se seznamy Program MS Excel nelze samozřejmě zaměňovat s databází (jakou je například MS Access), ve které lze provádět mnohonásobně víc úprav a zásahů jak do struktur tabulek, tak i do forem výstupů. Nicméně jednoduché struktury seznamů lze i v tomto programu velmi efektně a jednoduše upravovat a měnit. Je tak možné velmi rychle vygenerovat potřebnou sestavu zboží, kterou byste ve standardním databázovém programu museli mnohdy složitě a těžkopádně vytvářet.

Řazení v seznamech Záznamy v předmětném seznamu výrobků můžete velmi jednoduše řadit: abecedně, podle velikosti, podle nejvyšších několika hodnot, případně i podle vlastního klíče řazení (např. podle měsíců). A to nejen podle jednoho ze sloupců/polí, ale v případě stejných hodnot pak podle některého z dalších polí (např. při stejné kategorii výrobku dále řadit podle ceny).

Řazení podle jednoho sloupce/pole Jedná se o nejjednodušší způsob řazení. Seřadí zmíněnou tabulku (všechny záznamy) podle jednoho z polí – a to podle toho, ve kterém je označena libovolná buňka. Řadit můžeme vzestupně nebo sestupně (abecedně, případně podle čísel od největšího nebo od nejmenšího). Uvedený příkaz pro řazení můžete spouštět z karty Data ikonami ze skupiny Seřadit a filtrovat. V příkladu seznamy.xlsx na listech ukázka a řazení A si vyzkoušejte, jak seřadit data podle výrobních nákladů od nejmenšího k největšímu: A) Pomocí nástrojů z karty Data: 1. Umístěte kurzor do libovolné buňky tabulky. Práce se seznamy

2. Přejděte na kartu Data a ve skupině Seřadit a filtrovat klepněte na ikonu Seřadit. 3. V zobrazeném dialogovém okně nastavte Seřadit podle – Výrobní náklady. Dále nastavte Řazení podle Hodnoty a Pořadí Od nejmenšího k největšímu.

75

Kontingenční tabulky a grafy

5 Kontingenční tabulky a grafy


• popis a praktické použití kontingenční tabulky • průvodce kontingenční tabulkou • změna struktury kontingenční tabulky, formátování a styly • výpočtová pole v kontingenční tabulce, kontingenční graf

89

E xcel



Kontingenční tabulky a grafy Pokud máte k dispozici mnohasetřádkové tabulky s podrobnými údaji (např. prodeje výrobků), nejsou vždy takové tabulky přehledné pro vyhledání určitých konkrétních hodnot. V těchto tabulkách jsou mnohdy všechny kompletní údaje (nelze tedy v žádném případě říct, že v nich není to, co potřebujete), jenže nejsou na první pohled vidět – pro zjištění daného údaje jsou nepřehledné. Určitě jste již někdy slyšeli pojem kontingenční tabulka (v anglických verzích Pivot Table) jako určité řešení některých analýz a speciálních souhrnných výběrů ve velkých a nepřehledných tabulkách, případně ve vícenásobných oblastech dat.

Jak taková kontingenční tabulka vypadá a k čemu slouží? Kontingenční tabulka je zvláštní tabulka vytvořená na samostatném listu, která využívá zdrojová data z jiného listu, případně z externího zdroje dat (jiné tabulky, databáze, spojení přes ODBC…). Tato data jsou v kontingenční tabulce uspořádána ve vícerozměrný souhrn podle požadavků na druh a styl informací. Kontingenční tabulka je přitom na původní zdroj dat on-line připojená a při jakékoliv změně zdrojových dat vždy ukazuje jejich aktuální stav/změnu. Kontingenčních tabulek můžete mít více (na jednotlivých listech) a všechny budou na sobě vzájemně nezávislé, ale budou závislé na jednom společném zdroji dat, ke kterému jsou on-line připojeny. Kromě kontingenční tabulky lze vytvořit i kontingenční graf, který obdobně jako kontingenční tabulka je provázaný s původním zdrojem dat a zobrazuje graf vybraných závislostí z tohoto zdroje dat. Oproti tabulce může graf poskytnout více možností pro analýzu a souhrn dat, ze kterých mohou být na první pohled patrné určité velikosti a závislosti.


Do kontingenční tabulky nelze vkládat data – slouží pouze k analýze dat z již hotové tabulky. Rovněž pokud cokoliv v kontingenční tabulce změníte, nebude to mít žádný vliv na zdrojová data. V příkladu Ukázka-tabulka.xlsx na listech A až H se můžete seznámit s již hotovými kontingenčními tabulkami. 1. Otevřete si uvedený soubor a podívejte se na zdrojovou tabulku na listu Data, kde je několik stovek záznamů o jednotlivých prodejích, včetně data i jména obchodníka. 2. Na dalších listech jsou již hotové ukázkové kontingenční tabulky, které znázorňují obrat jednotlivých obchodníků podle typu zboží, počty prodaných kusů zboží u každé-

90

Kontingenční tabulky a grafy ho obchodníka a opět obrat podle typů zboží (avšak vytvořený v jiném provedení): • list A: kontingenční tabulka obratů jednotlivých položek podle obchodníků – v základním provedení bez úprav • list B: kontingenční tabulka viz A graficky upravená • list C: kontingenční tabulka počtu obchodů položek podle obchodníků – v základním provedení bez úprav • list D: kontingenční tabulka viz C graficky upravená • list E: kontingenční tabulka obratů jednotlivých položek podle obchodníků – dvojúrovňové zobrazení • list F: kontingenční tabulka obratů jednotlivých obchodníků po měsících • list G: kontingenční tabulka obratů jednotlivých položek po měsících • list H: kontingenční tabulka obratů jednotlivých položek po měsících s možností volby obchodníka (v tomto případě zvolen obchodník Josef)


Práce s hotovou kontingenční tabulkou Na příkladu Ukázka-tabulka.xlsx na listu H si ukážeme základní práci s již hotovou kontingenční tabulkou. Ukázková kontingenční tabulka se skládá ze tří typů dat: • řádky: v tomto případě obsahují data položek zboží, • sloupce: v tomto případě obsahují data obchodů seskupená po měsících, • stránka: (v horní části tabulky) v tomto případě obsahují možnost volby obchodníka.

91

Manažerské nástroje


6 Manažerské nástroje • podmíněné formátování • ochrana dat v tabulkách, ověřování vstupních dat • hledání zpětného řešení, průvodce podmíněným součtem • statistické funkce - jejich základní přehled a použití

111

E xcel




Manažerské nástroje Podmíněné formátování Velké tabulky dat jsou často nepřehledné a obtížně se s nimi pracuje – vyhledávání potřebných dat je zdlouhavé a zabere mnoho času. Excel však nabízí nástroj, který práci s daty usnadňuje. Umí hledané údaje barevně zvýraznit, a urychlit tak jejich vyhledání. Tímto nástrojem je Podmíněné formátování. Pomocí několika podmínek umožňuje definovat, které údaje budou graficky zvýrazněny. Existují tři základní způsoby zvýraznění dat, a to formátování na základě: 1. přímých hodnot v buňkách (chcete-li například, aby všechny hodnoty menší než nula byly zobrazeny červeně), 2. hodnot sousedních buněk (chcete-li například, aby byly zobrazeny červeně názvy firem ve sloupci X, které mají ve sloupci Y hodnotu větší než pět tisíc – výše pohledávek), 3. podmínky nebo vzorce (chcete-li například, aby všechny hodnoty, které mají větší hodnotu než průměr, byly zobrazeny zeleně – hodnoty tržeb větší, než je průměrná tržba). Podmíněné formátování najdete na kartě Domů ve skupině Styly. Klepnutím na ikonu Podmíněné formátování se zobrazí nabídka možností podmíněného formátování.

112



Zde jsou obsaženy možnosti pro podmíněné formátování podle obsahu buněk, dále je zde nabídka nejpoužívanějších pravidel. Další možností je převést hodnoty do podoby datové čáry nebo barevné škály. K použití se nabízejí také sady ikon. Pro vlastní nastavení podmíněného formátování je třeba klepnout na příkaz Nové pravidlo. Příkaz Vymazat pravidla umožňuje odstraňovat nepotřebná nastavení a příkazem Správa pravidel získáte možnost upravovat všechna podmíněná formátování použitá v sešitu.

Formátování na základě přímých hodnot v buňkách Tento typ podmíněného formátování je vhodný především ke zvýraznění jedné hodnoty v tabulce dat. Může se jednat například o zvýraznění údajů, které jsou větší než daná hodnota. Pro procvičení si otevřete soubor Podmíněný formát.xls, kde na listu A jsou v tabulce dlužníků červeně zvýrazněny všechny údaje, které jsou větší než 15 000 Kč. Na listu tabulka je původní nezformátovaná tabulka dlužníků. Máte za úkol nechat v tabulce zobrazit červeně veškeré částky dluhů, které jsou větší než 15 000 Kč. Původní tabulka vypadá následovně: Tabulka obsahuje čtyři sloupce – v prvním je název společnosti, v druhém dlužná částka, ve třetím poslední provedená platba od společnosti a poslední sloupec indikuje, zda je povoleno dané společnosti prodávat zboží. Ke zvýraznění částek vyšších než 15 000 Kč použijte podmíněné: 1. Označte oblast dlužných částek, tedy oblast B2:B7. 2. Vyvolejte funkci Podmíněné formátování (karta Domů – Podmíněné formátování…). 3. V její nabídce vyberte příkaz Zvýraznit pravidla buněk a poté klepněte na příkaz Větší než.

113

Manažerské nástroje Manažerské nástroje

Podmíněné formátování – vizualizace Novinkou v Excelu 2007 je podmíněné formátování formou vizualizace. V tomto případě jde o zobrazení hodnot pomocí barevných škál, datových čar nebo doplnění ikonami u všech buněk v označené oblasti podle nastavených podmínek. Použití vizualizace si můžete procvičit na příkladu podmíněný formát.xlsx na listu E a výsledek zkontrolovat na listu F. Datová čára vyjádří v buňce hodnotu formou barevné datové čáry (pásu). Čím vyšší hodnota, tím delší datová čára. Tento formát nastavíte příkazem Podmíněné formátování – Datové čáry a následnou volbou jednoho z nabízených stylů. Postup (je společný pro všechny tři typy vizualizace): 1. V tabulce vyberte příslušnou oblast hodnot (B2:B7). 2. Na kartě domů klepněte na ikonu Podmíněné formátování. 3. Z její nabídky vyberte příkaz Datové čáry. 4. Vyberte některý z nabízených stylů a klepněte na něj. 5. Zvolený styl se zobrazí ve vybrané oblasti dat.

Kromě připravených nabídek můžete pomocí příkaz Nové pravidlo vytvořit vlastní nastavení stylu podmíněného formátu. Barevná škála je typ podmíněného formátu, který podbarví jednotlivé buňky barvami na základě hodnot v nich obsažených. Nastavíte jej příkazem Podmíněné formátování – Barevné škály. Poté vyberte ze zobrazené nabídky vhodný styl formátu. Také u tohoto formátu lze v případě potřeby vytvořit vlastní variantu stylu prostřednictvím příkazu Nové pravidlo. Třetí novinkou Excelu 2007 pro podmíněné formátování jsou sady ikon. S jejich využitím bude do každé vybrané buňky vložena ikona představující hodnotu v dané buňce. Tento

119

E xcel

= K DY Ž (A1= = 5; =5; 5;“ ;“d do dost dos ost st ač st aču ačuj čuj č čují ujjící“ u ící ící cí“ í“;“není ;“ ;;“ne ;“n “ne “n n ení ne n v = K DYŽ(A Y Ž(A Ž(A1 Ž ((A (A1=10 A1 A1=1 1=10 =1 10 0;“do 0; “dos do d dos ostaču os staču ttač ač aču čujjící jíc jí ící ííc c cíí““;“n ;“ ení en v



=CO = CON CO O N CATE ONC ON CAT AT ATE TENA TEN TE AT TE(A1; TE T E(A (A1 (A1; A1 A1; A 1;;B1 1 B1) B 1)) 1 =C =CON CON ON N CA CATE C AT ATE A TENA TE TEN AT TE T E((A (A1; (A1 A1;B A1; A1 A 1 1;B B1) B1 1)) 1

podmíněný formát nastavíte příkazem Podmíněné formátování – sady ikon. Z nabídky pak vyberte vhodnou sadu pro svou tabulku.

V případě potřeby nového nastavení (barev nebo typu hodnot) použijte příkaz Nové pravidlo a příslušnou kategorii, v případě úpravy stávajícího stylu použijte příkaz Správa pravidel. Tento příkaz vám také u možní zjistit, jaké pravidlo je nastavené pro vybranou oblast buněk.

Komplexní příklady - Podmíněné formátování Příkladem k procvičení je tabulka v souboru Podmíněný formát-příklad.xlsx, která obsahuje seznam žáků a jejich známky z několika předmětů. Další hodnoty, které v tabulce najdete, jsou i průměry každého žáka i předmětu. Pro řešení příkladu je třeba znát zápisy

120

Manažerské nástroje Manažerské nástroje

a parametry některých méně známých funkcích programu Microsoft Excel; všechny ovšem lze najít v dialogu Vložení funkce. Zadání: 1. V tabulce na listu Zadání nastavte podmíněný formát na jména žáků – v případě, že žák má nejméně jednu známku 5, zobrazí se jeho jméno tučně a červeně. 2. Pokud je průměr žáka menší nebo roven známce dvě, označte tento průměr zeleně a tučně. 3. U známek každého předmětu, které jsou větší než průměrná známka daného předmě-

tu, podbarvěte dané buňky světle žlutou barvou. Řešení: 1. Označte oblast A2:A6. 2. Klepněte na ikonu Podmíněné formátování a vyberte Nové pravidlo. 3. Z nabídky vyberte Určit buňky k formátování pomocí vzorce. 4. Do příslušného pole zadejte vzorec: =COUNTIF(B2:E2;5)>0 5. Po klepnutí na tlačítko Formát nastavte formát písma na tučně a červeně. 6. Nastavení potvrďte klepnutím na tlačítko OK. 7. Nyní v tabulce označte oblast F2:F6. 8. Klepneme na ikonu Podmíněné formátování a vyberte Nové pravidlo. 9. Z nabídky zvolte Formátovat pouze buňky obsahující. 10. Do příslušného pole nastavte Hodnota buňky, z nabídky následujícího pole zvolte Je menší než nebo rovno a do posledního pole zapište hodnotu 2. 11. Pomocí tlačítka Formát nastavte tučné zelené písmo.

121

E xcel

= K DY Ž (A1= = 5; =5; 5;“ ;“d do dost dos ost st ač st aču ačuj čuj č čují ujjící“ u ící ící cí“ í“;“není ;“ ;;“ne ;“n “ne “n n ení ne n v = K DYŽ(A Y Ž(A Ž(A1 Ž ((A (A1=10 A1 A1=1 1=10 =1 10 0;“do 0; “dos do d dos ostaču os staču ttač ač aču čujjící jíc jí ící ííc c cíí““;“n ;“ ení en v



=CO = CON CO O N CATE ONC ON CAT AT ATE TENA TEN TE AT TE(A1; TE T E(A (A1 (A1; A1 A1; A 1;;B1 1 B1) B 1)) 1 =C =CON CON ON N CA CATE C AT ATE A TENA TE TEN AT TE T E((A (A1; (A1 A1;B A1; A1 A 1 1;B B1) B1 1)) 1

12. Nastavení potvrďte tlačítkem OK. 13. Označte oblast B2:E6.

14. Klepněte na ikonu Podmíněné formátování a vyberte Zvýraznit pravidla buněk – Větší než (tento postup je rychlejší než zadání prostřednictvím příkazu Nové pravidlo). 15. Do pole Formátovat buňky s hodnotou VĚTŠÍ NEŽ zapište adresu buňky =B$7 (pro zkopírování do vedlejších sloupců.) 16. V sousedním poli nastavte pomocí příkazu Vlastní formát výplň světle žlutou barvou. Výsledná zformátovaná tabulka je pro kontrolu na listu Výsledek.

122

Excel pro management, ekonomy a podnikatele

Recommend Documents