IVIT - kurzy IT - počitačové školenia

Hľadanie optimálneho riešenia pomocou Riešiteľa (Solver)

Doplnok Riešiteľ (Solver) nájdete na Údaje/ Analýza/ Riešiteľ (Data/ Analyse/ Solver). Ak sa Riešiteľ nenachádza na uvedenom mieste, je potrebné ho spustiť, pretože doplnok Riešiteľ nie je štandardnou súčasťou MS Excel. Postupujte nasledovne:

1. kliknite na kartu Súbor/ Možnosti/ Doplnky (File/ Options/ Add-Ins),
2. vyberte voľbu Spustiť (Go to),
3. v otvorenom okne začiarknite políčko Riešiteľ (Solver) a potvrďte OK.

V príklade demonštrujeme použitie a nastavenie Riešiteľa pri optimalizácii výroby s dosiahnutím maximálneho zisku. Príklad si môžete stiahnuť a precvičiť. Poznáme požiadavky, t.j. množstvo a kombinácie komponentov, ktoré je potrebné na výrobu jednotlivých výrobkov. Známe sú aj kapacity, t.j. koľko komponentov máme na sklade. A poznáme aj jednotkový zisk výrobkov. Známe informácie sú v modeli vyznačené žltou farbou.

Zistiť chceme, koľko kusov jednotlivých výrobkov (svetlozelené bunky) máme vyrobiť, aby sme dosiahli maximálny zisk (tmavozelená bunka).

Je potrebné zostaviť model, v ktorom budeme mať všetky dostupné informácie a vzťahy medzi nimi.

Pri tvorbe modelu je dôležité poznať tri body:

1. čo chceme optimalizovať, t.j. čo chceme zistiť – v našom prípade je to počet výrobkov, ktorý máme vyrobiť,
2. čo je cieľom – našim cieľom je maximalizácia zisku. Cieľom môže byť maximalizácia alebo minimalizácia akejkoľvek výstupnej hodnoty,
3. aké sú obmedzenia – našimi obmedzeniami sú kapacity komponentov. Nemôžeme na výrobu použiť viac komponentov, ako máme na sklade. To znamená, že počet použitých komponentov nemôže byť väčší ako je skutočný počet, ktorý máme k dispozícii.

K obmedzeniam pri nastavení Riešiteľa patrí aj tzv. podmienka nezápornosti, ktorá vraví, že nemôžeme vyrobiť záporný počet výrobkov, a podmienka celých čísel, podľa ktorej musí byť počet vyrobených výrobkov celočíselný.

Na základe týchto troch bodov musíme v modeli mať aj biele bunky, ktoré obsahujú vzorce, t.j. vzťahy medzi známymi premennými a výsledkom, t.j. v našom prípade ziskom. Pomocou vzorcov vypočítame, koľko bolo skutočne použitých kusov komponentov a aký bol celkový zisk zo všetkých vyrobených výrobkov.

Použitá je funkcia SUMPRODUCT, ktorá vektorovo vynásobí vyrobený počet výrobkov a počet komponentov potrebných na ich výrobu. Tieto násobky následne sčíta a tak zistí, koľko kusov komponentu A bolo použitých. Jednoducho povedané, vynásobí počet vyrobených kusov Výrobku 1 (bunka E10) a komponentu A (bunka E11) plus počet vyrobených Výrobkov 2 (bunka F10) krát počet kusov komponentu A (bunka F11) plus počet vyrobených Výrobkov 3 (bunka G10) krát počet kusov komponentu A (bunka G11).

Keď sú všetky dôležité vzťahy zaznamenané, môžeme prejsť k optimalizácii pomocou Riešeiteľa.

Postup pri nastavení Riešiteľa je nasledovný:

1. spustite doplnok pomocou Údaje/ Analýzy/ Riešiteľ (Data/ Analyse/ Solver),

2. zadajte požadovaný cieľ – do okienka Nastaviť cieľ (Set Target Cell) vložte odkaz na bunku, ktorá predstavuje výsledok. Táto bunka musí obsahovať vzorec. V našom prípade je to bunke E19 obsahujúca celkový zisk,
3. nastavte vlastnosť cieľa – vo výbere Do: (Equal to:) zaznačte, či požadujete cieľovú bunku maximálnu (napr. zisk), minimálnu (napr. náklady), alebo rovnú konkrétnemu číslu. V našom prípade zaškrtneme Maximum,
4. okienko Zmenou premenných buniek (By Changing Cells) zahŕňa odkaz na bunky, ktoré chceme zistiť, t.j. v ktorých sa nachádzajú vstupné hodnoty ovplyvňujúce výsledok. Sú to bunky E10:G10, ktoré predstavujú počet vyrobených ks jednotlivých výrobkov. Tieto bunky nesmú obsahovať vzorec,
5. nastavte obmedzenia – Podlieha obmedzeniam (Subject To The Contraints), t.j. zadanie obmedzujúcich podmienok riešenia. Pomocou voľby Pridať (Add) zadáte jednotlivé podmienky: počet použitých komponentov na výrobu nemôže prekročiť počet komponentov, ktoré máme na sklade, počet kusov musí byť väčší nanajvýš rovný nule, počet vyrobených kusov musí byť celočíselný. Každú podmienku zadáte zvlášť do dialógového okna nasledovne. Po vložení podmienky kliknite na Pridať (Add) a po zadaní poslednej podmienky na OK.

Počet použitých komponentov na výrobu nemôže prekročiť počet komponentov:

Počet vyrobených kusov musí byť väčší nanajvýš rovný nule (podmienka nezápornosti):

Počet vyrobených kusov musí byť celočíselný (podmienka celočíselnosti) – ako znamienko vyberte INT:

Konkrétnu podmienku môžete dodatočne zmeniť pomocou voľby Zmeniť (Change), resp. odstrániť cez voľbu Odstrániť (Delete).

Po potvrdení poslednej podmienky, sa vrátite do pôvodného okna, kde sa podmienky zapíšu.

6. pomocou Možnosti (Options) môžete zmeniť parametre riešenia,
7. pomocou Vybrať metódu riešenia (Select a Solving Method) je možné v prípade nelineárnych vzťahov zvoliť Nelineárny algoritmus GRG, ak sú lineárne (ako v našom prípade), tak ponechajte Simplex LP algoritmus,
8. spustite optimalizáciu voľbou Riešiť (Solve), čím sa spustí hľadanie optimálneho riešenia. Po skončení Excel vypíše informáciu o nájdení riešenia. Potvrďte Ok.

V prípade, že doplnok Riešiteľ nenašiel riešenie, je potrebné zvážiť obmedzujúce podmienky. Prípadne skontrolujte vzorce v modeli, či existuje vzťah medzi Menenými bunkami a Nastaveným cieľom. Práve to býva najčastejšou chybou pri príprave modelu.

Optimálnym výsledkom pri daných podmienkach je vyrobiť 63 ks výrobku 1, 162 ks výrobku 2 a 187 ks výrobku 3. Dosiahneme tak maximálny zisk 12 221,22 Eur. Známy je aj skutočný počet použitých komponentov, vďaka čomu jednoducho zistíme, koľko kusov nám ešte ostane.

Ak by ste následne zmenili napr. počet ks na sklade hociktorého komponentu alebo hociktorú inú známu hodnotu (v modeli žlté bunky), je potrebné znovu spustiť Riešiteľa. Netreba všetko zadávať odznovu, pamätá si posledné nastavenia. Stačí len potvrdiť tlačidlom Riešiť (Solve) a Riešiteľ bude hľadať nové optimálne riešenie na základe zmenených podmienok.

Doplnok Riešiteľ (Solver) môže byť použitý aj napr. pri optimalizácii personálneho obsadenia, rozvrhnutie rozvozu tovarov k odberateľom alebo do predajní, pri akýchkoľvek modeloch, kde poznáte výsledok, obmedzenia a potrebujete zistiť vstupné hodnoty.

Ak Vás tento článok zaujal, v počítačovej škole IVIT na kurze pre užívateľov Excel – pre ekonómov Vás naučíme využívať Riešiteľa aj v iných prípadoch, nielen pri optimalizácii výroby. Predstavíme Vám na konkrétnych príkladoch aj ďalšie nástroje podporujúce rozhodovanie, napríklad tabuľku údajov, hľadanie riešenia, použitie vzorcov a funkcií pri viackriteriálnom rozhodovaní a pod.

Autor: Ing. Zuzana Pogranová, PhD - interný lektor Microsoft Office školení - IVIT - Inštitút vzdelávania informačných technológií, s.r.o.