Máte ďalšie otázky, na ktoré ste tu zatiaľ nenašli odpoveď? Kontaktujte nás mailom, či telefonicky, požadované informácie radi doplníme.
Rozhodovanie a optimalizácia v oblasti výroby, investícií, personalistiky a pod. nie je vôbec jednoduché. Excel a jeho nástroje sú efektívnym pomocníkom, ktorý nám uľahčí analýzu rôznych modelov. Jedným z nástrojov je doplnok Riešiteľ (Solver), ktorý je určený na optimalizáciu vstupných údajov pri známych obmedzeniach a výsledku. Pre každú optimalizáciu modelu zadáte iba Váš požadovaný cieľ a obmedzenia.
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:
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:
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ý:
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.
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.