Na okraj kontingenčních tabulek - aneb kdo tomu dal tak blbej název?

Tentokrát žádný návod, jen krátké zamyšlení/povzdychnutí.
Docela často se bavím s lidmi, kteří se učí pracovat s excelem. Vím, že řada z nich má obavy z kontingenčních tabulek. Tyto obavy se nám na kursech daří docela rychle rozptýlit, když si ukážeme, že nejde o nic složitého.
Nicméně - už řadu let přemýšlím, jak mohlo někoho napadnout jméno "kontingenční tabulka", které je zároveň:

• Dlouhé
• Cizí
• Nic neříkající
• Neshodující se s anglickou verzí

Kdo v Čechách ví, co znamená slovo "kontingenční" (mimo tabulky)? Půl procenta lidí?
Slovník cizích slov ke slovu "kontingence":
"Ve filozofii náhodnost, nahodilost, opak nutnosti a terminovanosti, v teorii pravděpodobnosti číselný výraz vyjadřující závislost dvou různých znaků".
Třeba ve Francii je ten název také dlouhý (Tableau croisé dynamique), ale alespoň vypovídá o "dynamice" tabulky a jejích schopnostech.
Tímto prosím českou pobočku Microsoftu, aby buď vymyslela nový název, nebo používala anglický PivotTable. Myslím, že to dost zmenší naprosto zbytečný odstup, který si uživatelé k této jinak nesmírně užitečné funkcionalitě udržují.
A příště už zase k věci :)

Kaskádové rozbalovací menu v Excelu

Příklad

Potřebuji použít rozbalovací menu, která na sebe navazují. Tedy např. tak, že si uživatel v jednom rozbalovacím menu vybere ze seznamu zemí Francii, a následně si pak ve druhém může vybírat už jen francouzská města.

Návod

Viz příklad ke stažení.
Pro pochopení je třeba znát:

• Ověření dat (pro vytvoření menu jako takového) 
• Funkce NEPŘÍMÝ.ODKAZ (anglicky INDIRECT)
• Pojmenování oblastí, případně Hromadné pojmenování oblasti

Funguje to tak, že:

• Pojmenuji všechny oblasti, které se mohou v rozbalovacích menu použít. Musím dodržet to, aby se oblast polí pro hodnoty nižší úrovně vždy jmenovala stejně jako položka v číselníku vyšší úrovně. Tedy např. aby když mám jednu z položek první úrovně Francie, tak aby se oblast s francouzskými městy jmenovala také Francie. 
• Menu pro výběr první úrovně nastavím přes ověření dat standardně - odkazem na oblast, ze které se čerpá. 
• Menu pro výběr další úrovně vždy nastavím jako nepřímý odkaz na buňku, kde se vybere příslušná vyšší úroveň.

Z příkladu to určitě bude pochopitelné lépe :)

Kaskádové rozbalovací menu v Excelu

Příklad

Potřebuji použít rozbalovací menu, která na sebe navazují. Tedy např. tak, že si uživatel v jednom rozbalovacím menu vybere ze seznamu zemí Francii, a následně si pak ve druhém může vybírat už jen francouzská města.

Návod

Viz příklad.
https://www.dropbox.com/s/mg47vo2stji5jqo/rozbalovaci_menu.xlsx
Pro pochopení je třeba znát:

• Ověření dat (pro vytvoření menu jako takového) 
• Funkce NEPŘÍMÝ.ODKAZ (anglicky INDIRECT). 
• Pojmenování oblastí 

Funguje to tak, že:
Pojmenuji všechny oblasti, které se mohou v rozbalovacích menu použít. Musím dodržet to, aby se oblast polí pro hodnoty nižší úrovně vždy jmenovala stejně jako položka v číselníku vyšší úrovně. Tedy např. aby když mám jednu z položek první úrovně Francie, tak aby se oblast s francouzskými městy jmenovala také Francie. Menu pro výběr první úrovně nastavím přes ověření dat standardně - odkazem na oblast, ze které se čerpá. Menu pro výběr další úrovně vždy nastavím jako nepřímý odkaz na buňku, kde se vybere příslušná vyšší úroveň.
Z příkladu to určitě bude pochopitelné lépe :)

Funkce PLATBA.ÚROK

Příklad

Potřebuji zjistit, kolik zaplatím ve sté splátce na úrocích při těchto parametrech:

• Výše půjčky: 2 000 000 Kč
• Úrok 9%
• Počet let splácení: 20

Návod

Zápis

=PLATBA.ÚROK(0,09/12;100;20*12;-2000000)

Zdůvodnění

Sazba: 

Použijeme hodnotu úroku, převedenou dělením dvanácti na měsíční úrok. 

Za: 

Zadáme pořadové číslo splátky, u které chci výši úroku znát. 

Pper: 

Celkový počet plateb, které provedeme. Protože splácíme měsíčně, násobíme dvanácti. 

Souč_hod: 

Zadáme současnou hodnotu půjčky. Protože se z našeho pohledu jedná o zápornou částku (dlužíme), zadáme tuto hodnotu záporně.

Bud_Hod: 

 Tuto hodnotu nemusíme vyplňovat, neboť na konci chceme mít půjčku zcela splacenou, čili hodnota bude nula.

Funkce BUDHODNOTA

Příklad

Potřebuji zjistit, kolik našetřím při následujících parametrech

• úrok: 4,00% 
• doba spoření: 15 
• měsíčně uložím: 5000 
• počáteční zůstatek 800 000 Kč

Návod

Pro výpočet použijeme funkci BUDHODNOTA, která nám umožní na základě úrokové sazby, doby spoření, měsíční úložky a počátečního zůstatku určit výslednou částku, kterou našetříme.
Zápis
=BUDHODNOTA(0,04/12;15*12;-5000;-800000)
Zdůvodnění
Sazba:
Použijeme hodnotu úroku, který musíme převést na měsíční úrok - vydělit dvanácti
Pper:
Celkový počet plateb, které provedeme. Protože šetříme měsíčně, musíme násobit dvanácti
Splátka:
Zadáme výši ukládané částky, ve vzorečku ji ovšem upravíme na zápornou hodnotu (jde o spoření a ne úvěr)
Souč_hod:
Zadáme výši současné hodnoty, hodnotu opět převedeme na zápornou (jde o spoření a ne úvěr)
Typ:
Protože budeme ukládat na konci každého měsíce, necháme hodnotu prázdnou

Funkce SOUČHODNOTA

Příklad

Potřebuji zjistit, jak velký úvěr si mohu vzít při následujících parametrech

• splátek ročně: 2 
• výše splátky 25000 
• sazba 3% 
• počet let 25

Návod

Pro výpočet použijeme funkci SOUČHODNOTA, která nám umožní na základě úrokové sazby, doby, měsíční splátky a výsledné částky určit částkou, kterou si můžeme půjčit.

Zápis

=SOUČHODNOTA(0,03/2;25*2;-25000;0)

Zdůvodnění

Sazba:

Použijeme sazbu úroku, dělenou dvěma - protože splácíme dvě splátky ročně.
Pper:
Vyžaduje zadat celkový počet plateb, které provedeme. Vynásobíme tedy počet let a počet splátek ročně.
Splátka:
Zadáme výši splátky, ve vzorečku jí ovšem upravíme na zápornou hodnotu, neboť potřebujeme vyjádřit, že ze současné hodnoty půjčky ubíráme splátky 
Bud_hod:
Zde zadáme výslednou hodnotu, kterou chceme na konci mít. Protože chceme půjčku úplně splatit, zadáme hodnotu 0 
Typ:
Přepokládáme, že splácet budeme na konci každého měsíce, proto necháme hodnotu nevyplněnou

Excel - jak se připravit na přijímací řízení?

Na těchto stránkách nabízím výuku Excelu, často se zaměřením na úspěch v přijímacím řízení do zaměstnání.
Vím, že řadě lidí toto doučování opravdu pomohlo získat dobré místo a mám z toho vždy velkou radost. Rád bych ale tato školení ještě více přizpůsobil požadavkům zaměstnavatelů. Mám určitou představu, co se na takových pohovorech z Excelu zkouší (kontingenční tabulky, řazení a filtrování, makra...), ale nevím, jestli mi něco neuniká. Chci Vás proto požádat o Vaše zkušenosti.
Můžete do diskuse pod tímto příspěvkem napsat, na co z Excelu jste u přijímacího pohovoru narazili?
Předáním těchto informací jistě pomůžete mnohým uchazečům o práci připravit se na pohovor a uspět při něm.
Díky za Vaše zkušenosti.
Chcete-li své znalosti vyzkoušet, klikněte zde.