Jak neobelstit ruletu

Asi jste už někdy slyšeli o některé z metod, které údajně umožňují vyhrávat v ruletě. Je jich hodně a nelze se divit - kdyby někdo objevil takovou, která opravdu funguje, mohl by neomezeně zbohatnout.

Asi nejpopulárnější metoda se jmenuje Martingale a je jednoduchá.

• Vsázím na varianty typu sudá/lichá, černá/červená, malá/velká
• Začnu vsazením určité, po každé stejné částky.
• Když vyhraji, vsadím částku znovu, když prohraji, zdvojnásobím sázku. 
• Pokud po prohře vyhraji, dostanu tedy zpět původní prohru. Pokud zase prohraji, zase zdvojnásobím sázku atd.

Je to bezpečné, protože vlastně každou prohru si v příštím kole vynahradím - a dřív nebo později přece vyhraji. Nebo ne?

Bohužel to tak snadné není. Háček je samozřejmě skrytý v nule - která není ani lichá, ani sudá, ani černá, ani červená a nepatří ani mezi malá nebo velká čísla. 

Metodu lze vyvrátit matematicky, např. tady:

http://en.wikipedia.org/wiki/Martingale_(betting_system)

nebo simulací. Pokud se chcete na vlastní oči přesvědčit, že metoda nefunguje, nabízím tento excelovský soubor:

https://www.dropbox.com/s/p3erzm6apwbfjps/ruleta.xlsx

Obsahuje 100 000 simulovaných roztočení rulety. Imaginární hráč sází na sudou nebo lichou podle metody Martingale. Změnami parametrů sázení můžete zkoušet, jak sázení dopadá - po sto tisících hrách bohužel obvykle špatně... :)

Vzorce pro výpočet kvadratických rovnic

Příklad

Potřebuji vyřešit kvadratickou rovnici jako je například tato:
2x^2 + 2x - 312 = 0

Návod

... je ukázané tady:
https://www.dropbox.com/s/ls223k5qbiq4pg7/kvadraticka_rovnice.xlsx

Řešení soustavy rovnic Řešitelem

Příklad

Potřebuji vyřešit soustavu rovnic jako je například tato:
2x - y -8z = 4
-x +y +5 = -4
-2 -1 +2 = 8

Návod

Použiji Řešitel.
(O tom už jsem psal tady: http://www.excelentnitriky.com/2012/11/resitel-solver.html)
Připravím si tabulku s levou stranou rovnic a do sloupce pravé strany, např. takto:

Ve sloupci E spočtu levé strany rovnice pomocí funkce SOUČIN.SKALÁRNÍ.

Každý řádek soustavy takto pronásobím s posledním čtvrtým řádkem - ten je zatím prázdný, ale později v něm získám řešení.
Např. v buňce D1 funkcí =SOUČIN.SKALÁRNÍ(A1:C1;$A$4:$C$4) pronásobím první rádek čtvrtým.
Spustím řešitel a nastavím ho takto:


Levou stranu jedné z rovnic (je jedno kterou, já jsem si vybral první) optimalizuji na hodnotu z pravé strany. U ostatních to zařídím podmínkou. Další podmínkou zařídím že se i u dalších rovnic rovnají levé strany (skalární součiny) s pravými stranami.

A pak už jen nechám řešit. Možná bude třeba ve volbě Možnosti trochu zvýšit citlivost - aby Excel opravdu dopočítal celá čísla.
Výsledek je takovýto:

Zjistil jsem, že x = -6, y = 0 a z = -2.
Příklad je tady:
https://www.dropbox.com/s/mipl2r0zw952w9m/soustava%20rovnic.xlsx

Funkce RANK

Příklad

Potřebuji zjistit, že hodnota určitého čísla je ve skupině čísel n-tá nejmenší nebo největší.
Například v této tabulce potřebuji stanovit pořadí závodníků podle času.

Návod

Použiji funkci RANK (stejné jméno v češtině i v angličtině).
Zápis funkce v buňce C2:
=RANK(B2;$B$2:$B$9;1)

• B2
  Protože stanovuji pořadí času, který je uvedený v buňce B2
• $B$2:$B$9
  Protože oblast, ve které jsou všechny časy, je zde - a absolutní odkazy použiji proto, abych mohl odkaz bezpečně roztáhnout
• 1
  Protože pořadí se stanovuje od zákazníka s nejkratším časem, který má jedničku. Kdybych chtěl řadit naopak (jedničku by měl ten s nejdelším časem), použil bych nulu.

Funkce SUMIF

Příklad

Potřebuji posčítat hodnoty podle nějakého parametru.
Např. v této tabulce chci sečíst tržby za jednotlivé pobočky do B21 až B23.

Návod

Použiji funkci SUMIF. Do buňky B21 napíšu funkci:
=SUMIF($A$2:$B$18;A21;$B$2:$B$18)
Co znamenají jednotlivé parametry funkce:

• $A$2:$B$18 říká, ve kterých buňkách jsou hodnoty, podle kterých určuji jestli do součtu položku zahrnu nebo ne. V mém případě jsou to názvy jednotlivých poboček. Protože se odkaz při roztahování vzorce nemění, je zafixovaný absolutním odkazem.
• A21 říká, že to, co chci posčítat v konkrétní buňce, se bude určovat podle buňky A1 - tedy v prvním řádku je to Brno. 
• $B$2:$B$18 říká, ze které buňky se mají brát hodnoty pro sčítání. V mém případě jsou to tržby. Protože se odkaz při roztahování vzorce nemění, je zafixovaný absolutním odkazem.

Takto vypadá výsledek:


Poznámky:

• Druhým parametrem nemusí být odkaz na buňku. Může jím být text (v mém případě např. "Brno") nebo porovnávací znaménko (<,>...). 
• O porovnávání číselných hodnot je tento příspěvek:
  http://www.excelentnitriky.com/2012/11/funkce-sumif-trable-s-datumy.html

Funkce SUMIF

Příklad

Potřebuji posčítat hodnoty podle nějakého parametru.
Např. v této tabulce chci sečíst tržby za jednotlivé pobočky do B21 až B23.

Návod

Použiji funkci SUMIF. Do buňky B21 napíšu funkci:
=SUMIF($A$2:$B$18;A21;$B$2:$B$18)
Co znamenají jednotlivé parametry funkce:

• $A$2:$B$18 říká, ve kterých buňkách jsou hodnoty, podle kterých určuji jestli do součtu položku zahrnu nebo ne. V mém případě jsou to názvy jednotlivých poboček. Protože se odkaz při roztahování vzorce nemění, je zafixovaný absolutním odkazem.
• A21 říká, že to, co chci posčítat v konkrétní buňce, se bude určovat podle buňky A1 - tedy v prvním řádku je to Brno. 
• $B$2:$B$18 říká, ze které buňky se mají brát hodnoty pro sčítání. V mém případě jsou to tržby. Protože se odkaz při roztahování vzorce nemění, je zafixovaný absolutním odkazem.

Takto vypadá výsledek:


Poznámky:

• Druhým parametrem nemusí být odkaz na buňku. Může jím být text (v mém případě např. "Brno") nebo porovnávací znaménko (<,>...). 
• O porovnávání číselných hodnot je tento příspěvek:
  http://www.excelentnitriky.com/2012/11/funkce-sumif-trable-s-datumy.html

Řešitel / Solver

V tomto příspěvku je popsáno použití Řešitele. Je vysvětlené na jednoduchém příkladě, nicméně tuto funkcionalitu je možné používat i pro velmi sofistikované úlohy.
Uvedený příklad je inspirován jakýmisi skripty pro Operační výzkum, ale už nevím kterými :)

Příklad

Potřebuji vypočítat tento příklad:

• Firma vyrábí hračky – autíčka a vláčky. K výrobě potřebuje pouze dřevo a plastová kolečka. 
• Na jedno autíčko spotřebuje 4 kolečka a 0,8 metru dřeva. 
• Na jeden vláček spotřebuje 6 koleček a 0,4 metru dřeva. 
• Na jednom vláčku utrží 180 Kč a na jednom autíčku 120 Kč. 
• Pro příští týden má k dispozici 1000 koleček a 200 metrů dřevěného materiálu. 

Co má firma vyrábět, aby maximalizovala tržby?

Návod

Použiji funkcionalitu (nechci používat slovo funkce, protože z hlediska logiky Excelu to není funkce) Řešitel, v anglických verzích Solver.
Než s ním budu pracovat, potřebuji si jej zprovoznit (pokud jsem ho ještě nepoužíval) a připravit si tabulku, se kterou budu pracovat.

Zprovoznění Řešitele

Popisuji Excel 2007, ale v dalších verzích je to plus minus podobné.
Kliknu na tlačítko Office vlevo nahoře
Kliknu Možnosti aplikace Excel
Doplňky
V seznamu vyberu Řešitel











Kliknu na Přejít...
V navazujícím dialogu vyberu Řešitel
Případně potvrdím instalaci a nechám ji proběhnout
Že je vše OK poznám podle toho, že ve volbě Data mě vpravo přibude karta Řešitel.

Řešení úlohy

Připravím si takovouto tabulku:

• Ve sloupci A mam vypsané suroviny, jichž mám omezené množství. Jejich množství nakonec omezí počet výrobků, které mohu vyprodukovat.
• Ve sloupečcích B a C jsou pak jejich množství, které chci použít na jednotlivé výrobky.
• Do sloupce E napíšu, kolik maximálně mohu těchto surovin použít.
• Logicky bude například platit, že počet koleček použitých na vláčky krát počet vláčků plus počet koleček použitých na autíčka krát počet autíček musí být nakonec menší než počet koleček, které mám k dispozici. To samé s dřevem. Mohl bych po jednotlivých buňkách násobit, šikovnější je ale použit funkci SOUČIN.SKALÁRNÍ / SUMPRODUCT.
• V pátém řádku mám napsáno, kolik utržím za jednotlivé produkty, v D4 je pak součet - a právě tuto buňku, celkové tržby, chci maximalizovat.
• Hodnoty v řádku 5, stejně jako hodnoty ve sloupečku D, chci zjistit - dozvím se, kolik čeho mám vyrábět.

Použití Řešitele

• Otevřu řešitele a takto ho nakonfiguruji:

• Nastavit buňku
  Vyberu D4. V této buňce mám hodnotu, u které chci dosáhnout co největší hodnotu - jsou v ní celkové tržby.
• Rovno:
  Vyberu maximalizovat - jde o tržby. Alternativně je možné minimalizovat i cílovat na určitou hodnotu.
• Měněné buňky:
  Vyberu B5 a C5. Excel bude tyto buňky tak dlouho měnit, dokud nedosáhne nejvyšších možných tržeb.
  Omezující podmínky
  Pomocí "Přidat" nastavím omezení, která se při optimalizaci nesmí překročit.
  B5 a C5 musí být celá čisla - protože chci vyrábět hračky celé
  B5 a C5 musí být kladné - protože nemohu vyrábět záporná množství výrobků
  Buńky ve sloupečku D musí být menší než odpovídající buňky ve sloupečku E - protože materiál spotřebovaný celkem musí být menší než ten, co mám k dispozici
• Kliknu na Řešit a Excel spočítá optimální kombinaci vyrobených hraček. 
• V našem případě asi doplní do buněk B5 a C5 hodnoty 115 a 77 - největší tržby tedy budu mít při výrobě 115 vláčků a 77 autíček.
• Do sloupečku D dostanu počty spotřebovaného materiálu a skutečné tržby, kterých dosáhnu.

A to je všechno. Hotový příklad je tady:
https://www.dropbox.com/s/y2l5v3xw99idcwd/resitel.xls