Používame Řešitel alebo Solver (Aby domáce úlohy boli záživnejšie :))

 

Určite ste sa aspoň v škole stretli s problémom (matematickou úlohou), ktorý sa veľmi ťažko počítal ručne! Pre mňa osobne je najhoršie hľadať adekvátny počet hodnôt tak, aby som niečo splnil – isté obmedzujúce podmienky.

Prevažne sa jedná o počty, na ktoré musíte ísť formou pokus, omyl, resp. robíte niekoľko iterácií, až kým nesplníte dané podmienky!

Našťastie nám Excel umožňuje použiť tzv. Solver alebo  Řešitel. Nakoľko som si viac-menej istý, že väčšina z vás ho nebude mať k dispozícii na svojom Excel-i, kliknutím na článok Prvé kroky (Options –> Add-in –> Manage: Excel Add-in –> Řešitel) si môžete zistiť ako ho pridať do aplikácie. Vráťme sa k podstate – riešime slovnú úlohu.

Príklad:

Máme tri typy akcií, do ktorých zvažujeme investovať. V každej z nich máme nadefinované: Výnos, Riziko a Poplatky (viď nasledujúca tabuľka).

Našou úlohou je vytvoriť také portfólio (kombinácia akcií), aby sme splnili minimálny výnos, maximálne prípustné riziko a neprešvihli rozpočet na poplatky (viď nasledujúca tabuľka). Taktiež chceme mať čo najmenej akcií, aby sme sa nestrácali pri sledovaní ich vývoja na burze.K dispozícii je opäť Sample file, ktorý si stiahnite a môžeme začať!

Prejdime na záložku Data a v sekcii Analýza vyberieme voľbu Řešitel (Solver). Objaví sa nám okno v ktorom potrebujeme nadefinovať prakticky celú slovnú úlohu.

Nastavit Cíl: je bunka, pomocou ktorej sa má program orientovať. V našom prípade sa jedná ú súčet množstva kúpených akcií: $G$13

Min Max Hodnota – nastaviť ako má program optimalizovať cieľovú bunku (vyberieme Min, lebo chceme čo najmenej akcií)

Na základe změny proměnných buněk: určíme oblasť buniek, ktoré môže program meniť - $G$8:$G$10 (množstvo nakúpených jednotlivých akcií).

Omezujíci podmínky: nadefinujeme požadovaný výnos,riziko a poplatky (cez možnosť Pridať).

Dôležité je taktiež zaškrtnúť možnosť: Nastavit proměnné bez omezujícich podmínek jako nezáporné (nakupujeme, nie predávame akcie). Kompletná štruktúra nadefinovaného Řešitela bude vyzerať takto:

Potvrdíme možnosťou Řešit a Ok (použiť výsledky riešenia na meniace sa bunky) čakáme na výsledok:

Celkovo sme nakúpili necelé tri akcie – skoro 2,3 kusov Akcie typu III. a cca 0,6 kusov akcie typu II.  Pričom sa nám podarilo splniť obmedzujúce podmienky.

V prípade, že by ste chceli mať celočíselné hodnoty (nie 0,605… kusov akcie), môžete si pridať tieto možnosti ako obmedzujúce podmienky. Pripravený Solver sa zmení o obmedzujúce podmienky takto:

Výsledok bude vyzerať nasledovne:

V porovnaní s predchádzajúcim riešením diametrálne odlišné výsledky, avšak predsa splnené obmedzujúce podmienky.

Takto si môžete nadefinovať v podstate akúkoľvek úlohu a prakticky dospieť k elegantnému riešeniu. Nabudúce budeme pokračovať makrami. Pravdepodobne si na túto srandičku urobíme makro, aby sme nemuseli nepretržite klikať na Řešitel-a .