Procvičení kontingenčních tabulek - první díl

Tento příspěvek je opakovací - kontingenční tabulky jsou popsané zde.

http://www.excelentnitriky.com/2010/04/kontingencni-tabulky-jak-na-ne.html

Chcete-li si procvičit práci s nimi, stáhněte si tento soubor:

https://www.dropbox.com/s/yc1mukoz97u8x78/zadani.xlsx?m

V souboru jsou pracovní výkazy jednotlivých pracovníků firmy. Pokuste se vytvořit následující tabulky:

Součet odpracovaných hodin za jednotlivé zaměstnance - práce o víkendu

Kdo na čem odpracoval kolik hodin - pouze v únoru v pracovní dny

Průměrný počet hodin odpracovaných v jednotlivé dny, rozdělený mezi kluky a holky

Pokud se Vám některá z tabulek nepodařila, zde je řešení.
https://www.dropbox.com/s/8bf7gmzo7se6i0d/hotove.xlsx
Jestli jste úkoly snadno zvládli, pak umíte s kontingenčními tabulkami slušně pracovat.
Pokud něco nejde, pište do diskuse.
Doplnění - pokud máte ještě chuť na další práci s kontingenční tabulkou, klikněte sem:
Procvičení 2

Funkce IFERROR

Příklad

Potřebuji, aby v buňce byla v případě chyby určitá hodnota.

Návod

Použiji funkci IFERROR

Například pokud chci zobrazit varovnou hlášku "Pozor, chyba" na základě buňky A1, vypadá vzorec takto:

=IFERROR(A1;"Pozor, chyba")

• V případě, že chyba nenastane, je výsledkem hodnota původní buňky
• Za chybu se považují hodnoty  #N/A, #HODNOTA!, #REF!, #DIV/0!, #NUM!, #NÁZEV? a #NULL!.
• Funkce IFERROR funguje podobně jako funkce JE.CHYBHODN zkombinovaná s funkcí KDYŽ

Funkce pro ověření datového typu

Excel obsahuje skupinu speciálních funkcí - jejich účelem je určit, jestli je nebo není pravda, že nějaká buňka má určitý datový typ.

Příklad

V buňce A1 jsou povolena jen čísla. V buňce B2 proto chci mít varování v případě, že v buňce A1 je cokoliv jiného než číslo. 

Návod

Použiji kombinaci jedné z těchto funkcí, JE.ČÍSLO, a funkce KDYŽ. Vzorec pak vypadá takto:

=KDYŽ(JE.ČISLO(A1);"Hodnota je OK";"Pozor, chyba, ve sledované buňce není číslo!")

Jde o funkci KDYŽ, do které je vnořená funkce JE.ČÍSLO. 

Funkce KDYŽ má tři parametry. Prvním z nich je funkce JE.ČÍSLO. Ta vrátí pravdu nebo nepravdu. Vrátí-li pravdu, použije funkce když druhý parametr, jinak třetí parametr.

Je-li tedy v buňce A1 jakékoliv číslo, zobrazí se "Hodnota je OK", jinak se zobrazí "Pozor, chyba, ve sledované buňce není číslo!"

Jednotlivé funkce z této skupiny:

• JE.ČÍSLO - vrátí pravdu jen v případě, že je v buňce libovolné číslo. 
• JE.CHYBA - vrátí pravdu jen v případě jakékoliv chybné hodnoty kromě #N/A
• JE.CHYBHODN - vrátí pravdu jen v případě jakékoliv chybné hodnoty
• JE.LOGHODN - vrátí pravdu jen v případě, že jde o logickou hodnotu, přičemž logická hodnota je PRAVDA nebo NEPRAVDA
• JE.NEDEF - vrátí pravdu jen v případě, že hodnota není dostupná - tedy že jde o  #N/A
• JE.NETEXT - vrátí pravdu jen v případě, že hodnota není text
• JE.ODKAZ - vrátí pravdu jen v případě, že v závorce je odkaz na buňku nebo skupinu buněk (např. A1:A20), jinak vrátí nepravdu (např. pokud je v buňce odkaz na text, číslo nebo funkci)
• JE.PRÁZDNÉ - vrátí pravdu jen v případě, že buňka je úplně prázdná
• JE.TEXT - vrátí pravdu jen v případě, že hodnota je text

Jak vytvořit makro v Excelu

Příklad

Potřebuji vytvořit jednoduché makro v Excelu. Toto makro bude dělat jen jednoduchou věc - vymaže buňky A1 až B2 (tedy čtyři buňky). Je jasné že takové makro Vám práci příliš neulehčí - jde nám ale o to, abychom si ukázali obecný postup pro vytvoření maker.
Makro bude spojené s tlačítkem. Výsledek tedy takový, že uživatel klikne na tlačítko a tím smaže všechno, co je v buňkách A1 až B2 napsáno

Co je to makro

Než se dostaneme k našemu úkolu, pojďme si vyjasnit, co to vlastně makro je. Představte si, že v Excelu (nebo ve Wordu nebo v jiné aplikaci) děláte opakovaně nějakou rutinní věc - v našem případě mažete buňky. A protože je zbytečné, abyste to dělali znovu a znovu a ztráceli tím čas, potřebujete, aby to dělal Excel automaticky. Aby to ale dělat mohl, musíte ho to nejdříve "naučit". Chcete tedy Excelu jakoby říci "teď tě ukážu, co chci abys dělal, a pak to uděláš sám pokaždé, když kliknu na tlačítko".
Technicky je makro aplikace napsaná v programovacím jazyce Visual Basic for Applications (VBA).

Návod

A teď už se pustíme do příkladu. Abyste mohli pracovat s makry, musíte mít v Excelu aktivní kartu "Vývojář" - ta ale v základním nastavení aktivní není. Pro její zobrazení jděte na Soubor - Možnosti - Přizpůsobit pás karet a zaškrtněte "Vývojář". Tak, jak je to na obrázku.

V tuto chvíli byste už měli mít k dispozici kartu "Vývojář" tak, jako je to na obrázku.

Teď tedy nahrajeme makro - jinak řečeno provedeme úkony, které chceme pro příště automatizovat.

• Klikneme na tlačítko "Záznam makra" 
• Pokud chceme, tak makru dáme nějaké jméno (mezery nejsou povoleny)
• Klikneme na OK

V tuto chvíli se tlačítko "Záznam makra" změnilo na "Zastavit záznam". To znamená, že Excel se, obrazně řečeno, dívá, co děláme. Zapamatuje si to a příště už to bude dělat sám.

Teď provedeme to, co je obsahem makra - v našem případě označíme myší čtyři příslušné buňky a stiskneme "Delete". Přesně tak, jako bychom buňky promazávali bez makra.

Klikneme na "Zastavit záznam" - a makro je vytvořené.
Ověříme si to tak, že klikneme na tlačítko "Makra" a seznam všech maker (my máme zatím jen jedno) uvidíme. Můžeme je dokonce i spustit tlačítkem "Spustit" - pokud spustíme konkrétně naše makro, promažou se příslušné buňky.
Vyzkoušejte si to. Napište něco do uvedených buněk a spusťte makro. Je-li makro nahrané správně, buňky se promažou.

Naším posledním úkolem je přiřadit makro k tlačítku.

Nejprve tlačítko vložíme tak, jak je to na obrázku:

Tedy kliknutím a pak roztažením po listu. Excel v tuto chvíli sám nabídne přiřazení makra k tlačítku. Stačí vybrat příslušné makro a dát OK. 

A to je vše. Můžete si vyzkoušet, že i tlačítko funguje - pokud napíšete něco do příslušných buněk a pak zmáčknete tlačítko, buňky se promažou.

Pokud v seznamu maker kliknete na Upravit, zobrazí se Vám zápis makra zapsaný v jazyce VBA, nějak takto:

Sub Macro1()

'

' mojemakro

'

'

    Range("A1:B2").Select

    Selection.ClearContents

End Sub

Důležitý jsou třetí a čtvrtý řádek. Pokud umíte anglicky, není těžké je dešifrovat. V řádku tři označujeme oblast A1 až B2, v řádku čtyři vymažeme obsah z vybrané oblasti.

Další články

Chcete-li umět i další postupy s makry, klikněte sem pro další články.

Video

Video bylo vytvořené pro verzi Excel 2010, zatímco návod pro verzi Excel 2007. Ale odlišnosti verzí nejsou v tomto případě skoro žádné, takže by to nemělo vadit.

Funkce anglicky a česky - slovník

Text byl přesunut sem:
http://www.excelentnitriky.com/p/slovnik-funkci-en-cz.html

Čistá současná hodnota

Příklad

Potřebuji vypočítat čistou současnou hodnotu investice. 

Předpokládám ,že:

• Diskontní sazba je 10%
• První rok (období) investice vydělá 2 000 000 Kč
• Druhý rok (období) investice vydělá 3 000 000 Kč

Návod

Zápis funkce je takovýto:

=ČISTÁ.SOUČHODNOTA(10%;2000000;3000000)

a výsledek:

4 297 520,66 Kč

Poznámky

• Prvním parametrem je diskontní sazba, dále jsou vypsané hodnoty za jednotlivá období v pořadí první období, druhé období, třetí období...
• Kdybych chtěl do čisté současné hodnoty přiřadit i záporné hodnoty (jako např. pořizovací cenu investice), zadal bych je se záporným číslem. V případě, že v jednom období jsou kladné i záporné hodnoty, zadám jejich součet.
• Funkce ČISTÁ.SOUČHODNOTA neumožňuje přiřadit platby konkrétním datům - naopak předpokládá, že jsou všechny platby od sebe časově stejně vzdáleny. Pokud přiřadit platby datům chcete, použijte funkci XNPV.
• Anglický výraz pro funkci je "NPV"
• Více o metodice čisté současné hodnoty zde