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. Medzi jeho pokročilé funkcie patrí doplnok Riešiteľ (Solver), ktorý transformuje Excel na skutočný optimalizačný nástroj schopný nájsť riešenia obchodných, finančných a logistických problémov, okrem iného.
Čo je Riešiteľ v Exceli?
Riešiteľ je vstavaný doplnok pre Microsoft Excel, ktorého účelom je riešiť optimalizačné problémy, t. j. určiť maximálnu alebo minimálnu hodnotu cieľového vzorca pri dodržaní obmedzení. Riešiteľ je súčasťou analytického balíka Excel a je obzvlášť užitočný pre tzv. lineárne programovanie. Riešiteľ rieši problémy nelineárnej optimalizácie a situácie, v ktorých sa genetické algoritmy používajú na nájdenie riešení v zložitých scenároch.
Kde nájdete a ako aktivujete Riešiteľa
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. Ak príkaz Riešiteľ alebo skupina Analýza nie je k dispozícii, je potrebné aktivovať doplnok Riešiteľ.
Hlavné prvky a koncepty v Riešiteľovi
- Cieľová bunka: Toto je bunka v pracovnom hárku, kde sa vypočíta výsledok, ktorý sa má optimalizovať (maximalizovať, minimalizovať alebo nastaviť na konkrétnu hodnotu).
- Menné (rozhodovacie) bunky: Tieto bunky Riešiteľ automaticky upraví - v praxi Riešiteľ umožňuje automaticky upravovať hodnoty buniek nazývaných „rozhodovacie premenné“.
- Obmedzenia: Môžete zahrnúť toľko obmedzení, koľko si váš problém vyžaduje; vyberte vzťah ( <=, =, >=, int, bin alebo dif ), ktorý chcete použiť medzi odkazovanou bunkou a obmedzením.
Na čo sa Riešiteľ používa - príklady aplikácií
V mnohých podnikoch alebo organizáciách často vznikajú situácie, kedy je potrebné robiť optimálne rozhodnutia za určitých podmienok (rozpočty, časové harmonogramy, materiály). Medzi aplikácie patria:
- Rozdelenie reklamného rozpočtu - predstavte si, že spravujete marketingový rozpočet spoločnosti a chcete maximalizovať vplyv troch rôznych kampaní na predaj, ale máte obmedzený celkový rozpočet a každá kampaň má inú návratnosť investícií.
- Plánovanie zdrojov v oddelení - predpokladajme, že máte rozbehnutých niekoľko projektov a tím má obmedzený čas. Potrebujete rozdeliť hodiny medzi projekty a zabezpečiť, aby každý napredoval, pričom rešpektujete kapacitu a priority tímu.
- Optimálny mix produktov pre maximalizáciu zisku - jedným z klasických príkladov Riešiteľa je problém optimálneho produktového mixu, najmä vo výrobnom priemysle.
- Analýza hypotéz a ďalšie obchodné, finančné a logistické problémy, kde je potrebné automatizovať rozhodovanie.
Krok za krokom: nastavenie Riešiteľa na príklade optimalizácie výroby
V príklade demonštrujeme použitie a nastavenie Riešiteľa pri optimalizácii výroby s dosiahnutím maximálneho zisku. V nasledujúcich krokoch zistíte, čo treba pripraviť a ako spustiť optimalizáciu.
Prečítajte si tiež: problémy s doplnkom Riešiteľ a ich riešenie
Krok 1 - definujte známe údaje a cieľ
Poznámе 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. čo chceme optimalizovať, t.j. čo je cieľom - našim cieľom je maximalizácia zisku.
Krok 2 - vytvorte model v hárku
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. 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.
Krok 3 - zadajte cieľovú bunku
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. 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.
Krok 4 - vyberte menené bunky
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. Variabilné bunky musia priamo alebo nepriamo súvisieť s cieľovou bunkou.
Krok 5 - pridajte obmedzenia
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. Vyberte vzťah ( <=, =, >=, int, bin alebo dif ), ktorý chcete použiť medzi odkazovanou bunkou a obmedzením. Ak vyberiete možnosť int, v poli Obmedzenie sa zobrazí celé číslo. Ak vyberiete možnosť BIN, v poli Obmedzenie sa zobrazí binárne obmedzenie.
Prečítajte si tiež: Sprievodca kombináciou vitamínov
Krok 6 - spustite riešenie a vyhodnoťte výsledok
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. 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. Proces riešenia môžete prerušiť stlačením klávesu Esc.
Praktický výsledok príkladu výroby
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. 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.
Uloženie modelu a zostavy z Riešiteľa
Ak chcete po tom, ako Riešiteľ nájde riešenie, vytvoriť zostavu založenú na tomto riešení, v poli Zostavy vyberte typ zostavy a potom vyberte tlačidlo OK. Zostava sa v zošite vytvorí v novom hárku. Pri ukladaní modelu zadajte odkaz pre prvú bunku zvislého rozsahu prázdnych buniek, do ktorej chcete model problému umiestniť. Posledné výbery v dialógovom okne Parametre doplnku Riešiteľ s hárkom môžete uložiť formou uloženia zošita. Každý hárok v zošite môže disponovať vlastnými výbermi Riešiteľa a všetky z nich sa uložia.
Obmedzenia a typy modelov
Riešiteľ dokáže pracovať až s 200 rozhodovacími premennými a viacerými obmedzeniami. Správne využitie lineárnych modelov vám umožní okamžite dosiahnuť optimálne výsledky, a to aj s mnohými premennými. Nelineárne modely: Ak akýkoľvek vzorec alebo obmedzenie zahŕňa mocniny, pomery, násobenie premenných atď., problém už nie je lineárny. Riešiteľ je nevyhnutný pre pokročilých používateľov Excelu, analytikov, inžinierov, finančných profesionálov, technikov a obchodných profesionálov.
Ako priblížiť a oddialiť pohľad v Exceli
Často kladené otázky
- Môžem si nainštalovať Solver na Mac alebo funguje iba na Windows? - Doplnok Riešiteľ je k dispozícii vo väčšine verzií Excelu vrátane Mac, avšak dostupnosť funkcií sa môže líšiť podľa verzie Excelu a operačného systému.
- Dajú sa modely uložiť a znova použiť v Riešiteľovi? - Áno, posledné výbery v dialógovom okne Parametre doplnku Riešiteľ s hárkom môžete uložiť formou uloženia zošita a každý hárok si ukladá vlastné nastavenia.
- Je Riešiteľ zadarmo? - Riešiteľ je súčasťou Excelu, takže ju môžete používať v rámci licencie Excelu bez ďalších poplatkov.
Tipy pre efektívnu prácu s Riešiteľom
- Overte, že existuje vzťah medzi menenými bunkami a cieľovou bunkou - variabilné bunky musia priamo alebo nepriamo súvisieť s cieľovou bunkou.
- Začnite so zrozumiteľným modelom a postupne pridávajte obmedzenia - v prípade, že Riešiteľ nenašiel riešenie, zvážte uvoľnenie alebo úpravu obmedzení.
- Používajte lineárne modely, kde je to možné - lineárne problémy sú riešené rýchlejšie a spoľahlivejšie.
- Využite funkcie Excelu v modeli: SUMPRODUCT pre výpočet spotreby komponentov, logické funkcie či ďalšie matematické funkcie pre spoľahlivé vzorce.
Kde sa naučiť viac
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. Preskúmanie riešení, ktoré ponúka Riešiteľ v Exceli, môže priniesť zmenu v mnohých oblastiach, od organizácie osobných financií až po riadenie veľkých obchodných projektov.
Prečítajte si tiež: Kanabidiol a zdravie
| Časť nastavenia | Príklad v článku |
|---|---|
| Cieľová bunka | Bunka so vzorcom vypočítavajúcim zisk (napr. F7) |
| Menné bunky | E10:G10 - počty vyrábaných kusov |
| Obmedzenia | Spotreba komponentov ≤ zásoba, kusy ≥ 0, kusy = integer |
| Algoritmy | Lineárny simplex, nelineárny a genetické algoritmy |
Integráciou správnych funkcií, starostlivým definovaním problému a použitím výkonných optimalizačných algoritmov máte k dispozícii nástroj na automatizáciu rozhodnutí, skrátenie času a inteligentnú maximalizáciu výsledkov v rámci jednoduchej tabuľky.