Ako používať Excel 2019 Riešiteľ

Hoci príkazy Excelu Data Table a Goal Seek fungujú dobre pri jednoduchých problémoch, ktoré si vyžadujú určenie priameho vzťahu medzi vstupmi a výsledkami vo vzorci, pri riešení zložitejších problémov musíte použiť doplnok Riešiteľ. Napríklad použite Riešiteľ na nájdenie najlepšieho riešenia, keď potrebujete zmeniť viacero vstupných hodnôt v modeli Excel a potrebujete na tieto hodnoty a/alebo výstupnú hodnotu zaviesť obmedzenia.

Doplnok Riešiteľ funguje pomocou iteračných metód na nájdenie „najlepšieho“ riešenia vzhľadom na vstupy, požadované riešenie a obmedzenia, ktoré ukladáte. Pri každej iterácii program aplikuje metódu pokus-omyl (založenú na použití lineárnych alebo nelineárnych rovníc a nerovníc), ktorá sa pokúša priblížiť k optimálnemu riešeniu.

Pri používaní doplnku Riešiteľ majte na pamäti, že mnohé problémy, najmä tie zložitejšie, majú veľa riešení. Hoci Riešiteľ vracia optimálne riešenie, vzhľadom na počiatočné hodnoty, premenné, ktoré sa môžu meniť, a obmedzenia, ktoré definujete, toto riešenie často nie je jediné možné a v skutočnosti nemusí byť pre vás tým najlepším riešením. Aby ste si boli istí, že nájdete najlepšie riešenie, možno budete chcieť spustiť Riešiteľ viackrát, pričom pri každom vyriešení problému upravíte počiatočné hodnoty.

Pri nastavovaní problému pre doplnok Riešiteľ v pracovnom hárku programu Excel definujte nasledujúce položky:

• Cieľová bunka: Cieľová bunka v pracovnom hárku, ktorej hodnota sa má maximalizovať, minimalizovať alebo dosiahnuť konkrétnu hodnotu. Upozorňujeme, že táto bunka musí obsahovať vzorec.
• Premenné bunky: meniace sa bunky v pracovnom hárku, ktorých hodnoty sa majú upravovať, kým sa nenájde odpoveď.
• Bunky s obmedzením: Bunky, ktoré obsahujú limity, ktoré uvalíte na meniace sa hodnoty v bunkách premenných a/alebo cieľovej bunke v bunke cieľa.

Po dokončení definovania problému s týmito parametrami a vyriešení problému pomocou doplnku Riešiteľ, program vráti optimálne riešenie úpravou hodnôt vo vašom pracovnom hárku. V tomto bode sa môžete rozhodnúť zachovať zmeny v hárku alebo obnoviť pôvodné hodnoty v hárku. Riešenie môžete tiež uložiť ako scenár, aby ste si ho mohli pozrieť neskôr pred obnovením pôvodných hodnôt.

Doplnok Riešiteľ so Správcom scenárov môžete použiť na pomoc pri nastavovaní problému na vyriešenie alebo na uloženie riešenia, aby ste si ho mohli pozrieť neskôr. Meniace sa bunky, ktoré definujete pre Správcu scenárov, automaticky prevezme a použije Riešiteľ, keď vyberiete tento príkaz, a naopak. Riešiteľovo riešenie problému môžete uložiť aj ako scenár (kliknutím na tlačidlo Uložiť scenár v dialógovom okne Riešiteľ), ktorý si potom môžete pozrieť pomocou Správcu scenárov.

Nastavenie a definovanie problému v Exceli 2019

Prvým krokom pri nastavovaní problému, na ktorom bude Riešiteľ pracovať, je vytvorenie modelu pracovného hárka, pre ktorý definujete cieľovú bunku, bunky premenných a bunky obmedzení.

Majte na pamäti, že Riešiteľ je doplnkový nástroj. To znamená, že predtým, ako ho budete môcť použiť, sa musíte uistiť, že je stále načítaný doplnkový program Riešiteľ, ako naznačuje zobrazenie tlačidla Riešiteľ v skupine Analýza na konci karty Údaje na páse s nástrojmi. Ak toto tlačidlo chýba, môžete načítať Riešiteľ tak, že otvoríte kartu Doplnky v dialógovom okne Možnosti programu Excel (Alt+FTAA) a potom kliknete na tlačidlo Prejsť po uistení sa, že v rozbaľovacej ponuke Spravovať sa zobrazujú doplnky programu Excel. zoznam na ľavej strane. Potom začiarknite políčko Doplnok Riešiteľ v dialógovom okne Doplnky, aby ste ho začiarkli pred kliknutím na tlačidlo OK, aby ste zatvorili dialógové okno a znova načítali doplnok.

Ak chcete definovať a vyriešiť problém s doplnkom Riešiteľ po načítaní doplnku a vytvorení modelu pracovného hárka, postupujte takto:

Kliknite na príkazové tlačidlo Riešiteľ v skupine Analyzovať na konci karty Údaje na páse s nástrojmi.

Excel otvorí dialógové okno Parametre riešiteľa.

Zadanie parametrov, ktoré sa majú použiť na model, v dialógovom okne Parametre riešiteľa.

Kliknite na cieľovú bunku v hárku alebo zadajte jej odkaz na bunku alebo názov rozsahu do textového poľa Nastaviť cieľ.

Ďalej musíte vybrať nastavenie Do. Kliknite na tlačidlo voľby Max, keď chcete, aby bola hodnota cieľovej bunky čo najväčšia. Kliknite na tlačidlo voľby Min, keď chcete, aby bola hodnota cieľovej bunky čo najmenšia. Kliknite na tlačidlo voľby Hodnota a potom zadajte hodnotu do priradeného textového poľa, keď chcete, aby hodnota cieľovej bunky dosiahla konkrétnu hodnotu.

Kliknite na príslušnú možnosť tlačidla v časti Komu dialógového okna. Ak vyberiete tlačidlo možnosti Hodnota, zadajte do príslušného textového poľa hodnotu, ktorá sa má zhodovať.

Ďalej označte premenné bunky – teda tie, ktoré môže Riešiteľ zmeniť, aby dosiahol váš cieľ Rovná sa.

Kliknite na textové pole Zmenou buniek s premennými a potom v hárku vyberte bunky, ktoré chcete zmeniť, alebo zadajte ich odkazy na bunky alebo názov rozsahu do textového poľa.

Pamätajte, že ak chcete vybrať nesusediace bunky v pracovnom hárku, musíte pri klikaní na každú bunku vo výbere podržať kláves Ctrl. Ak chcete, aby Excel vybral meniace sa bunky za vás na základe cieľovej bunky, ktorú ste vybrali, kliknite na tlačidlo Hádaj napravo od tohto textového poľa.

Predtým, ako necháte Riešiteľa upraviť váš model, môžete pridať obmedzenia pre cieľovú bunku alebo ktorúkoľvek z meniacich sa buniek, ktoré určujú jej limity pri úprave hodnôt.

(Voliteľné) Kliknite na tlačidlo Pridať napravo od zoznamu Predmet obmedzenia v dialógovom okne Parametre riešiteľa.

Táto akcia otvorí dialógové okno Pridať obmedzenie. Pri definovaní obmedzenia vyberte bunku, ktorej hodnotu chcete obmedziť, alebo vyberte bunku v hárku alebo zadajte odkaz na bunku do textového poľa Odkaz na bunku. Potom vyberte vzťah (=, <=,>= alebo int pre celé číslo alebo bin pre binárne) z rozbaľovacieho zoznamu napravo a (pokiaľ ste nezvolili int alebo bin ) zadajte príslušnú hodnotu alebo odkaz na bunku do poľa Textové pole obmedzenia.

Ak chcete pokračovať v pridávaní obmedzení pre ďalšie bunky používané Riešiteľom, kliknutím na tlačidlo Pridať pridajte obmedzenie a vymažte textové polia v dialógovom okne Pridať obmedzenie. Potom zopakujte krok 5 a pridajte nové obmedzenie. Po dokončení definovania obmedzení pre cieľovú bunku a zmene hodnôt v modeli kliknutím na tlačidlo OK zatvorte dialógové okno Pridať obmedzenie a vráťte sa do dialógového okna Parametre riešiteľa (v ktorom sú teraz vaše obmedzenia uvedené v zozname Predmet obmedzenia).

(Voliteľné) Ak chcete povoliť záporné hodnoty, keď bunky premenných nepodliehajú obmedzeniam, zrušte začiarknutie políčka Neobmedzené premenné nie sú záporné.
V predvolenom nastavení doplnok Riešiteľ využíva nelineárnu metódu GRG (Generalized Reduced Gradient) pri riešení modelu, ktorého parametre nastavujete, ktorá je známa ako veľmi efektívny spôsob riešenia hladkých nelineárnych problémov. Ak chcete použiť metódu LP Simplex (pre lineárne programovanie podľa algoritmu Simplex) alebo Evolutionary engine na riešenie problémov, ktoré nie sú hladké, musíte postupovať podľa kroku 7.

(Voliteľné) Vyberte LP Simplex alebo Evolutionary z rozbaľovacieho zoznamu Select a Solving Method, ak chcete použiť jednu z týchto metód na riešenie neplynulých problémov.

Kliknite na tlačidlo Riešiť, aby riešiteľ vyriešil problém tak, ako ste ho definovali v dialógovom okne Parametre riešiteľa.

Riešenie problému pomocou Riešiteľa Excelu

Keď kliknete na tlačidlo Riešiť, dialógové okno Parametre riešiteľa zmizne a stavový riadok indikuje, že Riešiteľ nastavuje problém a potom vás informuje o postupe pri riešení problému zobrazením čísla stredného (alebo skúšobného). riešenia, ako sú vyskúšané. Ak chcete prerušiť proces riešenia kedykoľvek predtým, ako Excel vypočíta poslednú iteráciu, stlačte kláves Esc. Excel potom zobrazí dialógové okno Zobraziť skúšobné riešenie, ktoré vás informuje, že proces riešenia bol pozastavený. Ak chcete pokračovať v procese riešenia, kliknite na tlačidlo Pokračovať. Ak chcete proces riešenia prerušiť, kliknite na tlačidlo Stop.

Keď Excel dokončí proces riešenia, zobrazí sa dialógové okno Výsledky riešiteľa. Toto dialógové okno vás informuje, či Riešiteľ dokázal nájsť riešenie vzhľadom na cieľovú bunku, meniace sa bunky a obmedzenia definované pre problém. Ak chcete zachovať zmeny, ktoré riešiteľ vykonal v modeli pracovného hárka, ponechajte tlačidlo voľby Ponechať riešenie riešiteľa vybraté a kliknutím na tlačidlo OK zatvorte dialógové okno Výsledky riešiteľa. Ak chcete vrátiť pôvodné hodnoty do pracovného hárka, kliknite namiesto toho na tlačidlo voľby Obnoviť pôvodné hodnoty. Ak chcete pred obnovením pôvodných hodnôt uložiť zmeny ako scenár, kliknite na tlačidlo Uložiť scenár a priraďte názov aktuálnemu scenáru predtým, ako kliknete na možnosť Obnoviť pôvodné hodnoty a tlačidlo OK.

Dialógové okno Výsledky riešiteľa zobrazujúce, že riešiteľ našiel riešenie problému.

Na rozdiel od použitia príkazu Hľadanie cieľa po kliknutí na tlačidlo voľby Ponechať riešenie riešiteľa v dialógovom okne Výsledky riešiteľa nemôžete použiť príkazové tlačidlo Späť na paneli s nástrojmi Rýchly prístup na obnovenie pôvodných hodnôt v hárku. Ak chcete mať možnosť prepínať medzi zobrazeniami pracovného hárka „pred“ a „po“, musíte zmeny uložiť pomocou tlačidla Uložiť scenár a potom vybrať tlačidlo voľby Obnoviť pôvodné hodnoty. Týmto spôsobom môžete zachovať zobrazenie „pred“ v pôvodnom pracovnom hárku a použiť Správcu scenárov na zobrazenie zobrazenia „po“ vytvorené Riešiteľom.

Zmena možností Riešiteľa programu Excel

Pre väčšinu problémov sú predvolené možnosti používané Riešiteľom dostatočné. V niektorých situáciách však možno budete chcieť zmeniť niektoré možnosti Riešiteľa pred začatím procesu riešenia. Ak chcete zmeniť možnosti riešenia, kliknite na tlačidlo Možnosti v dialógovom okne Parametre riešiteľa. Excel potom otvorí dialógové okno Možnosti s vybratou kartou Všetky metódy, kde môžete vykonať všetky potrebné zmeny.

Úprava možností riešenia v dialógovom okne Možnosti.

Nastavenia možností riešenia Excel 2019

Po zmene možností sa kliknutím na tlačidlo OK vráťte do dialógového okna Parametre riešiteľa; odtiaľto môžete kliknúť na tlačidlo Vyriešiť a začať proces riešenia s novými nastaveniami riešenia, ktoré ste práve zmenili.

Keď použijete predvolenú nelineárnu alebo evolučnú metódu GRG (Generalized Reduced Gradient), môžete nastaviť ďalšie nastavenia Riešiteľa pomocou volieb na kartách GRG Nelineárne a Evolučné v dialógovom okne Možnosti. Tieto možnosti zahŕňajú zmenu nastavení Converge, Population Size a Random Seed pre ktorúkoľvek z týchto konkrétnych metód.

Uloženie a načítanie problému s modelom v Exceli 2019

Bunka cieľa, bunky premenných, bunky obmedzení a možnosti Riešiteľ, ktoré ste naposledy použili, sa uložia ako súčasť pracovného hárka programu Excel, keď kliknete na tlačidlo Uložiť na paneli s nástrojmi Rýchly prístup (Ctrl+S). Keď definujete iné problémy pre rovnaký pracovný hárok, ktorý chcete uložiť, musíte kliknúť na tlačidlo Uložiť model v dialógovom okne Možnosti riešiteľa a uviesť odkaz na bunku alebo názov rozsahu v aktívnom hárku, kde chcete, aby boli parametre problému vložené.

Keď kliknete na tlačidlo Načítať/Uložiť, Excel otvorí dialógové okno Načítať/Uložiť model, ktoré obsahuje textové pole Výber oblasti modelu. Toto textové pole obsahuje odkazy na bunky pre rozsah dostatočne veľký na to, aby obsahoval všetky parametre problému, počnúc aktívnou bunkou. Ak chcete uložiť parametre problému v tomto rozsahu, kliknite na tlačidlo OK. Ak tento rozsah zahŕňa bunky s existujúcimi údajmi, musíte pred kliknutím na tlačidlo OK upraviť odkaz na bunku v tomto textovom poli, aby ste zabránili Excelu nahradiť existujúce údaje.

Po kliknutí na tlačidlo OK Excel skopíruje parametre problému v zadanom rozsahu. Tieto hodnoty sa potom uložia ako súčasť pracovného hárka pri ďalšom ukladaní zošita. Ak chcete znova použiť tieto parametre problému pri riešení problému, jednoducho musíte otvoriť dialógové okno Možnosti riešiteľa, kliknutím na tlačidlo Načítať/Uložiť otvorte dialógové okno Načítať/Uložiť model, kliknite na tlačidlo Načítať a potom vyberte rozsah obsahujúci uložené problémové parametre. Keď kliknete na tlačidlo OK v dialógovom okne Načítať model, Excel načíta parametre z tohto rozsahu buniek do príslušných textových polí v dialógovom okne Parametre riešiteľa. Potom môžete zatvoriť dialógové okno Možnosti riešiteľa kliknutím na tlačidlo OK a problém môžete vyriešiť pomocou týchto parametrov kliknutím na príkazové tlačidlo Vyriešiť.

Nezabudnite, že tlačidlo Reset All môžete použiť vždy, keď chcete vymazať všetky parametre definované pre predchádzajúci problém a vrátiť možnosti Riešiteľa na ich predvolené hodnoty.

Vytváranie zostáv Riešiteľa v Exceli 2019

Pomocou Riešiteľa môžete vytvoriť tri rôzne typy zostáv:

• Správa s odpoveďami: Uvádza cieľovú bunku a meniace sa bunky s ich pôvodnými a konečnými hodnotami spolu s obmedzeniami použitými pri riešení problému.
• Správa o citlivosti: Označuje citlivosť optimálneho riešenia na zmeny vo vzorcoch, ktoré vypočítavajú cieľovú bunku a obmedzenia. Správa zobrazuje meniace sa bunky s ich konečnými hodnotami a zníženým gradientom pre každú bunku. (Znížený gradient meria cieľ na jednotku zvýšenia v meniacej sa bunke.) Ak ste definovali obmedzenia, v správe Sensitivity sú uvedené ich konečné hodnoty a Lagrangeov multiplikátor pre každé obmedzenie. (Lagrangeov multiplikátor meria cieľ na jednotku zvýšenia, ktorý sa zobrazuje na pravej strane rovnice obmedzenia.)
• Správa limitov: Zobrazuje cieľovú bunku a meniace sa bunky s ich hodnotami, dolnými a hornými limitmi a cieľovými výsledkami. Dolná hranica predstavuje najnižšiu hodnotu, ktorú môže mať meniaca sa bunka, pričom sa fixujú hodnoty všetkých ostatných buniek a stále sú splnené obmedzenia. Horná hranica predstavuje najvyššiu hodnotu, ktorá to urobí.

Excel umiestni každú zostavu, ktorú vygenerujete pre problém Riešiteľa, do samostatného pracovného hárka v zošite. Ak chcete vygenerovať jednu (alebo všetky) z týchto správ, vyberte typ správy (Odpoveď, Citlivosť alebo Limity) zo zoznamu Správy v dialógovom okne Výsledky riešiteľa. Ak chcete vybrať viac ako jeden prehľad, stačí kliknúť na názov prehľadu.

Keď kliknutím na tlačidlo OK zatvoríte dialógové okno Výsledky riešiteľa (po výbere medzi možnosťami Zachovať riešenie a Obnoviť pôvodné hodnoty), Excel vygeneruje zostavu (alebo zostavy), ktoré ste vybrali, v novom hárku, ktorý pridá na začiatok zošita. . (Karty hárka prehľadu sú pomenované podľa typu prehľadu, ako v prehľade odpovedí 1, správe o citlivosti 1 a správe o limitoch 1. )