Nestačí vám funkce? Napište si své!

Běžný Excel 2010 má přes 400 funkcí. Přesto se můžeme dostat do situace, kdy by se nám hodila funkce, která v Excelu není. Nebo nás nebaví opakovaně zapisovat dlouhý vzorec obsahující více funkcí a chceme si vytvořit funkci, která tuto kombinaci funkcí nahradí.

Příklad

V mém případě chci vytvořit funkci, která spočte obsah obdélníka na základě dvou vstupních buněk. Netvrdím, že je to zrovna vrchol praktičnosti, ale myslím že se na tom dá vytvoření jednoduché funkce dobře ukázat.

Návod

Jdu do editoru maker (karta Vývojář / tlačítko Visual Basic), vytvořím nový modul a zapíšu funkci.
V mém případě vypadá takto:


Function Obsah_obdelnika(Delka, Sirka)
        Obsah_obdelnika = Delka * Sirka
End Function

Vysvětleno:

• Function Obsah_obdelnika(Delka, Sirka)
  Function říká že je to funkce, Obsah_obdelnika je název funkce, Delka a Sirka jsou názvy vstupních hodnot
• Obsah_obdelnika = Delka * Sirka
  Obsah je roven délce krát šířce
• End Function
  Konec zápisu funkce

Editoru funkcí mohu zavřít. Od teď už se s mojí funkcí pracuje jako s jakoukoliv jinou. Jen si musím uvědomit, že tato funkce existuje v zásadě jen v souboru, kde jsem ji vytvořil.

Otestujte se

Uvádím dva odkazy na testy, ve kterých si můžete procvičit práci s Excelem. Je to docela zajímavé a snad, určitým úchylným způsobem, i zábavné :)
Krátký test Microsoftu:
http://office.microsoft.com/en-us/excel-help/practice-RZ101862712.aspx?section=7
Delší test na excel-skills.com:
http://www.excel-skills.com/demos2010/skills_test.asp

Data mining v Excelu

Data mining používáme, když chceme z velkých souborů dat získat užitečné informace.

Chceme-li se stát na data mining experty, potřebujeme vcelku hluboké znalosti z oblasti práce s databázemi, ale také ze statistiky a matematiky.

Pro data mining existuje řada specializovaných nástrojů. Ne každý ale ví, že silný data mining lze provádět i v Excelu - za pomoci specializovaného doplňku.

Co k tomu kromě znalostí potřebujeme:

• Nainstalovat MS SQL server ve verzi, která obsahuje Analysis services - což je i trial verze Evaluation. Výpočty modelů v rámci dataminingu se totiž fakticky neprovádějí v Excelu, ale na SQL Serveru. Z uživatelského pohledu to ale práci nijak neovlivňuje - uživatel se vlastně pohybuje v Excelu
• Nainstalovat doplněk
  http://office.microsoft.com/en-us/excel-help/data-mining-add-ins-HA010342915.aspx

Když doplněk nainstaluji, zobrazí se mi nová karta s doplňkem Data mining. 

Než se odvážně pustím do modelování, musím zadat, který SQL server má být pro výpočty použit - pomocí třetí ikonky zprava.

Data mining v Excelu

Data mining používáme, když chceme z velkých souborů dat získat užitečné informace. 

Chceme-li se stát na data mining experty, potřebujeme vcelku hluboké znalosti z oblasti práce s databázemi, ale také ze statistiky a matematiky.

Pro data mining existuje řada specializovaných nástrojů. Ne každý ale ví, že silný data mining lze provádět i v Excelu - za pomoci specializovaného doplňku.

Co k tomu kromě znalostí potřebujeme:

• Nainstalovat MS SQL server ve verzi, která obsahuje Analysis services - což je i trial verze Evaluation. Výpočty modelů v rámci dataminingu se totiž fakticky neprovádějí v Excelu, ale na SQL Serveru. Z uživatelského pohledu to ale práci nijak neovlivňuje - uživatel se vlastně pohybuje v Excelu
• Nainstalovat doplněk
  http://office.microsoft.com/en-us/excel-help/data-mining-add-ins-HA010342915.aspx

Když doplněk nainstaluji, zobrazí se mi nová karta s doplňkem Data mining. 

Než se odvážně pustím do modelování, musím zadat, který SQL server má být pro výpočty použit - pomocí třetí ikonky zprava.

Vložení znaku do textu

Příklad

Rychlý úkol: Potřebuji do textu na určitou pozici vložit určitý znak. Například potřebuji vložit mezeru mezi třetí a čtvrtý znak.

Návod

Použiji kombinaci funkcí CONCATENATE (ta spojuje texty), ZLEVA (česky LEFT) a funkce ČÁST (anglicky MID).

• =CONCATENATE(ZLEVA(A1;3);" ";ČÁST(A1;4;1000))

Vysvětlení

• CONCATENATE - spojuje části textu oddělené středníkem - tedy začátek, mezeru a zbytek
• ZLEVA - zobrazí určitý počet znaků odleva (3 znaky)
• ČÁST - zobrazí určitý počet znaků doprava od určitého znaku. V mém případě 1000 znaků (fakticky neomezeně znaků) od čtvrtého znaku

Optimalizace investičního portfolia v Excelu

Příklad

V jednom z minulých dílů jsme si popsali, jak funguje Řešitel / Solver.
Pojďme si jej dnes procvičit na praktičtějším příkladu. V tomto článku není vysvětlené všechno - takže kdyby něco nebylo jasné, můžete si projít původní vysvětlení.
Představte si, že máte určitou částku peněz, a tu chcete investovat tak, aby to pro vás bylo co nejvýhodnější. To znamená, že chcete vydělat co nejvíce peněz, na druhou stranu nechcete, aby investování bylo příliš rizikové.
V mém případě tedy chci maximalizovat celkový výnos při zadaném limitu rizikovosti
Mám k dispozici tyto investice, ze kterých si mohu vybrat libovolné množství - třeba i všechny nebo žádnou.

Řekněme, že mám k dispozici jeden milion a že nechci nést celkové riziko větší než 5%.
Dopočítám si do tabulky vážené výnosy a vážené riziko - tedy výnosnost a ztrátu násobenou jejich
pravděpodobností.

Řešení

V tabulce teď jsou žluté buňky, které vyplňuje uživatel, a ostatní buňky, které se počítají samy.
Ve sloupci "Zařazeno v portfoliu" budou jedničky u všech položek, které budou zařazeny do portfolia. Zatím jsou jedničky u všech - ale já chci, aby Řešitel dal nuly k těm, které do portfolia zařazené nebudou.
V horní části tabulky jsou zatím buňky vyplněné tak, jako bych investoval do všeho. To ale nemohu, protože nemám dost peněz. Po skončení řešitele už se do své částky vejdu a současně bude pořád splněná podmínka s minimální výnosností.

Teď mohu spustit Řešitele, a ten bude vypadat takto:

Všimněte si, že:

• Maximalizuji hodnotu buňky B6 - to je buňka, kde je můj celkový výnos
• Měním buňky B9 až B22 - resp. nechám Excel, aby tyto buňky měnil
• První podmínka říká, že to, kolik investuji, nesmí být výše, než to, kolik jsem se rozhodl investovat
• Druhá podmínka říká, že celková míra rizika nesmí být větší než určitá mez
• Třetí podmínka říká, že ve sloupečku "Zařazeno v portfoliu" nesmí být nic jiného než jedničky a nuly

Metodu řešení jsem vybral Evolutionary - je nejuniverzálnější (i když výpočet trvá déle) a nemělo by se stát, že Excel najde špatné řešení (někdy totiž najde lokální, ale ne absolutní maxima výnosové/nákladové funkce).
A teď už dám pouze Řešit a čekám na výsledek...

Vidím, že ve finální tabulce jsou (s jedničkou) vybrané položky, které mám do portfolia zařadit.
Vidím, že z jednoho milionu mohu při limitu rizikovosti 5% vydělat maximálně 87 854 Kč. Mohu libovolně měnit všechny parametry modelu (žluté buňky) a dalším přepočtem počítat nové varianty investice
Ke stažení:
https://www.dropbox.com/s/ofk7qn0lc90fhs9/resitel_optimalizace_portfolia.xlsx

Druhá hádanka - statistická (odhad objemu prodejů)

Úkol

Chcete-li si trochu polámat hlavu v Excelu, zkuste vyřešit následující hádanku.
Firma v několika obdobích investovala určité peníze do propagace. Podle toho, kolik se do propagace investovalo, se pak i prodával různý počet výrobků - čím větší reklama, tím větší prodeje.

Na příští období plánuji investovat 320 tisíc Kč. Jak velké mohu čekat prodeje?

Na obrázku je to spočítané. Pozná někdo, jaký vzorec jsem použil? 

Pro odhad hodnoty jsem použil lineární regresi, jiné vlivy na prodej než investice do reklamy zanedbávám.

Časová náročnost úkolu je cca 1 minuta.
Další hádanky si můžete vyzkoušet tady.

PowerPivot - vizualizace dat kontingenční tabulkou

Obsah příspěvku byl přesunut na web, zabývající se výhradně doplňkem PowerPivot. Pro zobrazení prosím klikněte zde.

Nový kurs Večerní škola Excelu

Chcete využít čtyři březnové čtvrtky k tomu, abyste se opravdu dobře naučili pracovat s Excelem? Pokud ano, je přímo pro Vás nový kurs Večerní škola Excelu.

Je určen pro uživatele, kteří nejsou úplní začátečníci, ale chtějí se v Excelu výrazně zlepšit.

Ceny a více informací o kursu zde.

Zobrazení procent v kontingenční tabulce

Příklad

Někdy v kontingenční tabulce potřebuji mít nejen součty (průměry, počty..) z hodnot, ale také například procentní podíl na celku. Jinými slovy nestačí mi toto:

Ale potřebuji toto:

Řešení

V tabulce dám do pole hodnot stejnou hodnotu dvakrát. Je to proto, že jedna mi bude později zobrazovat hodnoty, druhá procenta. (Samozřejmě kdybych chtěl pouze procenta a nepotřeboval hodnoty, stačí jednou.)

Po přidání hodnot jdu do jednoho ze sloupečků a kliknu v horní liště v kartě možnosti na Zobrazit hodnoty jako... a % z celkového součtu.

A je to.

Krátký návod na PowerPivot - vypočtené pole, KPI

Obsah příspěvku byl přesunut na web, zabývající se výhradně doplňkem PowerPivot. Pro zobrazení prosím klikněte zde.