Formulácia problému
Predpokladajme, že spoločnosť, v ktorej pracujete, má dva sklady, odkiaľ ide tovar do piatich vašich obchodov roztrúsených po celej Moskve.
Každý obchod je schopný predať určité množstvo nám známeho tovaru. Každý zo skladov má obmedzenú kapacitu. Úlohou je racionálne vybrať, z ktorého skladu do akých predajní má byť tovar doručený, aby sa minimalizovali celkové náklady na dopravu.
Pred začatím optimalizácie bude potrebné zostaviť jednoduchú tabuľku na strana v Exceli– náš matematický model popisujúci situáciu:
Rozumie sa, že:
- Sivá tabuľka (B3:G5) popisuje náklady na prepravu jednotky z každého skladu do každého obchodu.
- Fialové bunky (C14:G14) popisujú množstvo tovaru potrebného na predaj v každom obchode.
- Červené bunky (J10:J11) zobrazujú kapacitu každého skladu - maximálne množstvo tovaru, ktoré sklad pojme.
- Žlté (C12:G12) a modré (H10:H11) bunky predstavujú súčty riadkov a stĺpcov pre zelené bunky.
- Celkové náklady na dopravu (E17) sa vypočítajú ako súčet produktov počtu produktov a im zodpovedajúcich nákladov na dopravu.
Naša úloha sa teda obmedzuje na výber optimálnych hodnôt zelených buniek. Navyše, aby celková suma za riadok (modré bunky) neprekročila kapacitu skladu (červené bunky) a zároveň každá predajňa dostala také množstvo tovaru, ktoré potrebuje predať (množstvo za každú predajňu v r. žlté bunky by mali byť čo najbližšie k požiadavkám - fialové bunky).
Riešenie
V matematike sa takéto problémy výberu optimálneho rozdelenia zdrojov formulovali a popisovali už dávno. A, samozrejme, spôsoby ich riešenia sú už dávno vyvinuté. Excel poskytuje používateľovi jeden z nich - pomocou výkonného doplnku Hľadanie riešenia(riešiteľ), dostupné v Exceli 2003 prostredníctvom ponuky servis(nástroje) alebo z karty Údaje v novších verziách Excelu.
Ak je menu servis alebo na karte Údaje váš Excel takýto príkaz nemá - je to v poriadku - to znamená, že doplnok ešte nie je pripojený. Ak ho chcete pripojiť:
- v Exceli 2003 a novších - otvorte ponuku Služba - doplnky(Nástroje – Pridať- Ins) , v zobrazenom okne začiarknite políčko Hľadanie riešenia(Riešiteľ) a stlačte OK. Excel aktivuje vybraný doplnok a v ponuke servis (Nástroje) bude nový príkaz - Hľadanie riešenia (Riešiteľ) .
- v Exceli 2007 a novších - kliknite na tlačidlo Kancelária, potom vyberte možnostiexcel – doplnky – Choď(Možnosti programu Excel - Pridať moduly - Prejsť na) .
Spustíme doplnok. Otvorí sa nasledujúce okno:
V tomto okne musíte nastaviť nasledujúce nastavenia:
Okrem zjavných obmedzení spojených s fyzickými faktormi (kapacita skladov a dopravných prostriedkov, rozpočet a časové limity atď.) je niekedy potrebné pridať obmedzenia „špeciálne pre Excel“. V našom prípade napríklad budeme musieť pridať nasledujúce obmedzenie:
Dodatočne sa objasní, že objem prepravovaného tovaru (zelené bunky) nemôže byť záporný - pre osobu je to samozrejmé, ale pre počítač to musí byť výslovne napísané.
Po nastavení všetkých potrebných parametrov by okno malo vyzerať takto:
Teraz, keď sú zadané údaje pre výpočet, stlačte tlačidlo Bežať(vyriešiť) na spustenie optimalizácie. V závažných prípadoch s množstvom meniacich sa buniek a obmedzení môže hľadanie riešenia trvať dlho, ale naša úloha pre Excel nebude problémom - za pár okamihov získame nasledujúce výsledky:
Venujte pozornosť tomu, ako zaujímavo boli rozložené objemy dodávok medzi predajne, pričom neprekročili kapacitu našich skladov a uspokojili všetky požiadavky na požadovaný počet tovaru pre každú predajňu.
Ak nám nájdené riešenie vyhovuje, môžeme ho uložiť, alebo sa vrátiť k pôvodným hodnotám a skúsiť to znova s inými parametrami. Vybratú kombináciu parametrov môžete uložiť aj ako Scenár. Na žiadosť používateľa môže Excel zostaviť tri typy Správy o riešenom probléme na samostatných listoch: správa o výsledkoch, správa o matematickej stabilite riešenia a správa o limitoch (obmedzeniach) riešenia, vo väčšine prípadov však zaujímajú len špecialistov .
Existujú však situácie, keď Excel nenájde vhodné riešenie. Takýto prípad je možné nasimulovať, ak v našom príklade uvedieme požiadavky predajní v množstve väčšom ako je celková kapacita skladov. Potom sa pri vykonávaní optimalizácie Excel pokúsi priblížiť k riešeniu čo najbližšie a potom zobrazí správu, že riešenie nemožno nájsť. Avšak aj v tomto prípade máme omšu užitočná informácia– najmä môžeme vidieť „slabé prepojenia“ našich obchodných procesov a rozumieť oblastiam na zlepšenie.
Uvažovaný príklad je, samozrejme, relatívne jednoduchý, ale dá sa ľahko škálovať na riešenie oveľa zložitejších nelineárnych problémov. Napríklad:
- Optimalizácia rozdelenia finančných zdrojov podľa výdavkovej položky v podnikateľskom pláne alebo rozpočte projektu. Obmedzeniami v tomto prípade bude výška financovania a načasovanie projektu a cieľom optimalizácie je maximalizácia zisku a minimalizácia nákladov na projekt.
- Optimalizácia plánovania zamestnancov s cieľom minimalizovať fond mzdy podnikov. Obmedzenia v tomto prípade budú želaním každého zamestnanca podľa pracovného plánu a požiadaviek personálnej tabuľky.
- Optimalizácia investičných investícií - potreba správne rozdeliť finančné prostriedky medzi viaceré banky, cenné papiere alebo akcie podnikov s cieľom opäť maximalizovať zisk alebo (ak je to dôležitejšie) minimalizovať riziká.
V každom prípade doplnok Hľadanie riešenia(riešiteľ) je veľmi výkonný a krásny nástroj Excel a zaslúži si vašu pozornosť, pretože môže pomôcť v mnohých zložitých situáciách, ktorým musíte v modernom podnikaní čeliť.
Svet sa mení závratným tempom, či sa nám to páči alebo nie. Túto pravdu poznajú najmä používatelia PC. Po všetkom softvér sa mení a aktualizuje sa s neuveriteľnou frekvenciou. Kancelárske balíky tým našťastie veľmi netrpia, no nájdu sa aj výnimky. Aký je význam balíčkov kancelárske programy? akýkoľvek kancelárska aplikácia je šikovný nástroj určený na prácu s databázami. Počet pomocných prvkov v tomto systéme je čoraz viac.
S pomocou vizualizačných nástrojov, ktoré sa objavili až v nových verziách takýchto programov, sa práca výrazne zjednodušila. Vďaka prítomnosti nového vyhľadávacieho filtra sa práca výrazne zrýchlila. A samotný Microsoft Excel 2010 je rýchlejší. Zdalo by sa však, že nedávno si pracovníci kancelárie osvojili múdrosť práce s balíkom Office 2007. Zrazu sa však uskutočnila prezentácia balíka Office 2010, čo nešťastným používateľom prinieslo ešte viac problémov. Príkladom je „hľadanie riešenia“ v programe Microsoft Excel 2010.
Tento doplnok je nielen užitočný, ale tiež vám umožňuje urobiť prácu s tabuľkovým editorom oveľa produktívnejšou, čím vám umožní rozhodnúť sa veľké množstvo náročné úlohy. Je to relevantné najmä z hľadiska optimalizácie, ktorá je dnes pre mnohé firmy aktuálna. Ale prečo práve Microsoft Excel 2010? Ak hovoríme konkrétne o tejto verzii Excelu, potom prešla významnými zmenami. Napríklad sa opravilo veľké množstvo chýb vo vzorcoch, kvôli ktorým sa v predchádzajúcich verziách programu často vyskytli chyby vo výpočtoch. Najmenšia nesprávna kalkulácia však môže niekedy viesť k dosť nepríjemným následkom.
Použitím pásového rozhrania, ktoré má možnosť zobraziť náhľad grafov a tabuliek pred ich vložením do tabuľky, je pre používateľa jednoduchšie pripravovať zložité profesionálne dokumenty. Tiež zahrnuté v Nová verzia Editor zahrnul také nové typy vzorcov, ktoré môžu byť veľmi užitočné pre ekonómov a účtovníkov. Táto okolnosť podčiarkuje zameranie spoločnosti Microsoft na podnikových používateľov. Vzhľadom na to, že všetky nižšie opísané situácie sú pre nich typické, nie je v tom nič prekvapujúce.
Ak ste doplnok Riešiteľ nepoužili, môžete si ho nainštalovať samostatne. Ako ho nainštalovať. To sa robí celkom jednoducho. Ak používate tabuľkový editor Excel 2003 a starší, na vykonanie tejto akcie musíte prejsť na položku „Nástroje“ a tam vybrať položku „Doplnky“. A kde hľadať „hľadanie riešení“, ak sa bavíme o modernejšej verzii? Ak používate Excel 2007, potom môžete nájsť tlačidlo "hľadať riešenie" na karte "Údaje". Ako s tým pracovať? Možno sa všetky tieto vysvetlenia môžu zdať trochu zdĺhavé, ale tento doplnok funguje celkom logicky. Na to, aby ste to zvládli, nemusíte byť počítačový génius. Aby ste úplne pochopili princíp jeho použitia, zvážte najjednoduchší príklad.
Ako funguje „hľadanie riešenia“ v Exceli 2010?
Príklad: máte za úlohu rozdeľovať bonusy v organizácii. Pre zjednodušenie riešenia predpokladajme, že bonus potrebujete rozdeliť medzi všetkých zamestnancov pobočky. Prémiový rozpočet je 100 000 rubľov. Bonus môžete rozdeliť v pomere k výške odmeny každého zamestnanca. Kde začať s prácou? V prvom rade je potrebné vypracovať tabuľku, zadať do nej všetky potrebné informácie a výrazy vzorcov. Ako výsledok sa bude brať do úvahy celková hodnota sumy bonusu. Je potrebné zvážiť, že cieľová bunka (napríklad C8) je priradená k sekcii, ktorá sa zmení (napríklad E2).
V rozsahu C2-C7 môžu existovať ďalšie vzorce, pomocou ktorých môžete vypočítať výšku prémie pre každého zamestnanca. Potom musíte spustiť doplnok „hľadať riešenie“. Potom sa v okne, ktoré sa otvorí, nastavia požadované hodnoty. Osobitná pozornosť by sa mala venovať skutočnosti, že vzhľad okná sa môžu značne líšiť rôzne verzie kancelársky balík. Takže v tejto situácii sa s tým budete musieť vyrovnať sami. Neexistujú však žiadne zásadné rozdiely, takže štúdia nezaberie veľa času.
Aké možnosti sú v dialógovom okne?
Na uľahčenie práce by ste si mali byť vedomí tých hodnôt, ktoré vo všeobecnosti existujú v určitom prevádzkovom rozsahu. V prvom rade cieľová bunka. Upozorňujeme, že na rozdiel od iných operácií, ktoré môžu používať viacero vstupných polí, tu môže byť prítomné iba jedno. Okrem toho je potrebné mať na pamäti, že môže existovať niekoľko možností optimalizácie. Osobitná pozornosť by sa mala venovať minimálnej a maximálnej možnej výslednej hodnote. Pozor aj na konkrétny výsledok. Ak potrebuješ posledná možnosť, potom musí byť preferovaný výsledok presne špecifikovaný vo vstupnom poli. Malo by sa tiež pamätať na to, že jednotlivé polia aj rozsah môžu pôsobiť ako vymeniteľné bunky. Práve pre rozsah program získa konečnú hodnotu porovnaním s pôvodnými údajmi.
Ako sa pridávajú obmedzenia?
Ak potrebujete do programu pridať nejaké obmedzenia, musíte použiť tlačidlo "Pridať". Tu je dôležité vziať do úvahy nasledujúci bod: pri nastavovaní takýchto hodnôt musíte byť mimoriadne opatrní. Keďže doplnok programu Excel „hľadať riešenie“ sa používa v pomerne zodpovedných operáciách, je dôležité získať čo najsprávnejšie hodnoty. Samotné výsledky budú závisieť od obmedzení. Môžete nastaviť limity pre jednotlivé bunky, ako aj pre celé rozsahy.
Aké varianty vzorcov a znakov možno v tomto prípade použiť? Môžu sa použiť nasledujúce znaky: =, >=,<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».
Kde nájdem túto možnosť v starších verziách programu? Ak používate Excel 2007 alebo novší, k tejto možnosti sa dostanete kliknutím na tlačidlo „Možnosti“. Tu budete môcť vidieť položku „Možnosti riešenia vyhľadávania“.
Hľadanie hotového výsledku
Ak chcete vyhľadať hotové riešenie, musíte kliknúť na tlačidlo "Spustiť". V dôsledku toho sa zobrazí dialógové okno "Výsledky riešenia". Ak vám konečná odpoveď vyhovuje, stačí kliknúť na tlačidlo „OK“. V dôsledku toho sa odpoveď, ktorá sa vám páči, zaznamená do tabuľky. V prípade, že sa prijatá hodnota líši z vášho pohľadu, musíte kliknúť na tlačidlo „Zrušiť“. Tabuľka sa nakoniec vráti na pôvodnú hodnotu. Môžete pokračovať v hľadaní optimálneho riešenia. Ak ste zmenili pôvodné údaje, toto riešenie bude potrebné vykonať znova.
Kde sa dá použiť doplnok „hľadať riešenie“ v Exceli?
Zvážte ďalší príklad - minimalizáciu nákladov. Ako bolo uvedené vyššie, túto funkciu možno použiť na optimalizáciu výrobných procesov. Pozrime sa, ako môžete znížiť náklady spoločnosti zaoberajúcej sa nízkopodlažnou výstavbou. Predpokladajme, že máme samotnú organizáciu a troch dodávateľov, ktorí dodávajú stavebný materiál. Náklady na výstavbu budú zahrnuté do nákladov na zariadenie, preto je v záujme spoločnosti vybrať si dodávateľa, ktorého práca bude stáť menej.
Aké informácie treba zadať do „hľadania riešenia“ v MS Excel? Je potrebné uviesť náklady na stavebné materiály, ich potrebu na stavenisku a náklady na dopravu stavebného materiálu. Musíte vziať do úvahy každý pár "Dodávateľ-kupujúci". Cieľová bunka musí obsahovať súčet všetkých prepravných nákladov. Ak je všetko vykonané správne, funkcia „hľadať riešenie“ poskytne príležitosť na vytvorenie najziskovejšej stratégie, ktorá prinesie najvyšší možný príjem.
Nájsť riešenia v Exceli 2007 nie je štandardný doplnok. Je to nevyhnutné pre zložité výpočty v prítomnosti viac ako jednej neznámej. Nie je teda súčasťou bežnej sady možností programu. Ak je to však potrebné, doplnok ponúka užívateľovi efektívnu prácu, ako aj vysokú produktivitu.
Čo je hľadanie riešení?
Toto je doplnok programu. V bežnej konfigurácii, ktorú vyrába výrobca, sa tento balík neposkytuje. Musí sa stiahnuť a nakonfigurovať samostatne. Používatelia sa bez neho často zaobídu. Okrem toho sa doplnok často označuje ako „Riešiteľ“, keďže vykonáva presné a rýchle výpočty bez ohľadu na to, aká náročná je úloha. V prípade pôvodnej verzie Microsoft Office nie sú žiadne problémy s inštaláciou. Používateľ musí vykonať nasledujúce prechody: Možnosti - Nástroje - Doplnky - Spravovať - Doplnky programu Excel. V dôsledku toho sa na obrazovke zobrazí okno, v ktorom bude tlačidlo prechodu. Ak naň kliknete, zobrazí sa zoznam všetkých doplnkov, ktoré sú používateľovi ponúkané, nainštalovaných aj nenárokovaných. Ďalej musíte nájsť "Hľadať riešenia" a potom vedľa neho nastaviť značku. Nástroj sa stane aktívnym a bude ho možné použiť kedykoľvek.
Na čo je Riešiteľ? Prečo používať Find Solutions v Exceli 2007 a je potrebné ho nainštalovať? Ak má používateľ objektívnu funkciu, ktorá závisí od viacerých parametrov, doplnok vyberie riešenia problému zodpovedajúce zdrojovým údajom. Môžu to byť premenná, neznáma alebo, povedzme, konečná hodnota. Inými slovami, používateľ bude mať počiatočné charakteristiky, ako aj odozvu. Pokiaľ ide o samotný program, vyberie priebeh riešenia, poskytne vzorec. Stojí za zmienku, že pomocou doplnku je možné nájsť nasledovné:
Úspešná distribúcia pracovných zdrojov na dosiahnutie maximálneho zisku pri prevádzke spoločnosti alebo jej samostatného oddelenia, pobočky;
distribúcia investícií za podmienok minimalizovaných rizík;
riešenie problémov, kde je viac ako jedna neznáma;
uloženie a načítanie modelu riešenia je najlepšou možnosťou, ktorú využívajú zamestnanci, ktorí sú nútení neustále meniť svoj počítač alebo notebook;
riešenie viacerých problémov súčasne s rôznymi premennými, neznámymi, vzorcami a integrálmi.
Program teda otvára veľké príležitosti, ale musíte sa naučiť, ako ho správne používať.
Ako funguje riešiteľ? Okrem Riešiteľa má Excel takú funkciu ako výber parametrov. Je potrebné použiť, keď existuje iba jedna neznáma hodnota. Táto funkcia si vyžaduje menej zdrojov, takže výsledok je vydaný rýchlejšie. Hľadanie riešení v Exceli 2007 sa používa pri problémoch s najväčšou zložitosťou, v ktorých je viacero neznámych, často sa pozorujú premenné. Všetko teda možno znázorniť nasledovne: Nájdite neznáme – niekoľko „x“. Za predpokladu, že ide o vzorec alebo funkciu. S obmedzeniami sa v tomto prípade zvyčajne uvádza nerovnosť alebo minimálne / maximálne hodnoty. Okrem toho by ste mali uviesť bunky, s ktorými chcete vykonávať výpočty. Naraz je možné riešiť niekoľko rôznych úloh za predpokladu, že sú programu nastavené príslušné parametre.
Nastavenie možností Riešiteľa Aby Solver v Exceli 2007 fungoval správne, musíte zadať správne parametre. Spravidla sú obmedzené na niekoľko (1-3) charakteristík, avšak pri úlohách s väčšou zložitosťou je potrebné globálne nastavenie. Možnosti v Nájsť riešenia v Office Excel 2007 sú nasledovné:
1. Maximálny čas – počet sekúnd, ktoré používateľ pridelí programu na vyriešenie, závisí od úrovne zložitosti úlohy.
2. Maximálny počet integrácií. V tomto prípade hovoríme o počte pohybov vykonaných programom pri riešení úlohy. Keď sa parameter zvýši, odpoveď nedosiahne.
3. Chyba alebo presnosť, často používaná pri riešení desatinných zlomkov (napríklad do 0,0001).
4. Tolerancia. Používa sa v procese práce s percentami.
5. Nezáporné hodnoty. Používa sa pri riešení funkcie, ktorá má dve správne odpovede (povedzme +/-X).
6. Zobrazenie výsledkov integrácií. Toto nastavenie sa poskytuje vtedy, keď je dôležitý výsledok rozhodnutí aj ich priebeh.
7. Metóda vyhľadávania - výber optimalizačného algoritmu. Spravidla sa používa "Newtonova metóda". Keď sú vybraté všetky nastavenia, musíte kliknúť na tlačidlo uložiť.
Parametre úlohy vo funkcii „Hľadať riešenia“.
Práca tohto doplnku sa vykonáva v súlade so špecifikovanými charakteristikami výpočtu. Najdôležitejšia z nich je metóda. Sú dve možnosti. "Newtonova metóda" je predvolené nastavenie. Pracuje s väčšou pamäťou, ale menším počtom integrácií. Preto je vhodný pre štandardné a jednoduché rovnice. Okrem toho existuje „metóda konjugovaného gradientu“. V tomto prípade sa vyžaduje menej pamäte, ale je potrebných viac integrácií. Ak sa teda použije, je možné riešiť aj tie najzložitejšie rovnice, aplikovať vzorce a funkcie na úpravu mierky.
Vzorec v Exceli
Za zmienku stojí povinný prvok, bez ktorého nemôže fungovať doplnok „Hľadať riešenia“ v Exceli 2007. V tomto prípade hovoríme o vzorcoch. Sú výrazom, ktorý vykonáva špecifický výpočet. Vzorce nemôžu fungovať bez rovnosti. Program teda nie je schopný rozpoznať jeden, ak neexistuje zodpovedajúci znak. Vzorec pozostáva z nasledujúcich zložiek:
1. Funkcia. Štandardný vzorec, v ktorom existuje určitá a špecifická postupnosť akcií, sa nedá zmeniť.
2. Odkaz. Označuje počet buniek, ktoré sa majú vyriešiť. Zároveň môžu byť bunky umiestnené náhodne alebo v určitom poradí.
3. Operátor. Predstavuje symbol, ktorý určuje typ výpočtu (+ - sčítanie, * - násobenie atď.).
4. Konštantný. Je konštantná hodnota, ktorá vždy zostáva rovnaká. Aby ste to dosiahli, nemusíte robiť žiadne výpočty.
Vzorce sa riešia zľava doprava, dôležité je dodržať všetky matematické pravidlá.
Vytvorte vzorec
Vzorce sú rovnice, ktoré sú potrebné na vykonávanie výpočtov programu. Keď nie sú zadané, Excel Solver nebude fungovať. Úlohy sa tiež nebudú riešiť. Pre správnu prácu teda musíte vzorec zadať správne. Výpočet začína rovnosťou. Ak bunka obsahuje "=ROOT(číslo bunky)", použije sa zodpovedajúca funkcia. Keď je hlavný vzorec vytlačený so znakom "=", je potrebné uviesť údaje, s ktorými interaguje. Na vyhľadanie požadovaných informácií použite funkciu vyhľadávania.
Solution Finder je doplnok programu Microsoft Excel, ktorý možno použiť na nájdenie najlepšieho riešenia problému vzhľadom na obmedzenia špecifikované používateľom.
Zvážime hľadanie riešenia v (tento doplnok prešiel niekoľkými zmenami v porovnaní s predchádzajúcou verziou v .
V tomto článku sa pozrieme na:
- vytvorenie optimalizačného modelu na hárku MS EXCEL
- nastavenie Hľadanie riešenia;
- jednoduchý príklad (lineárny model).
Inštalácia Riešiteľa
Tím Hľadanie riešenia je v skupine Analýza tab Údaje.
Ak tým Hľadanie riešenia v skupine Analýza nie je k dispozícii, musíte povoliť doplnok s rovnakým názvom.
Pre to:
- Na karte Súbor vyberte tím možnosti a potom kategória doplnky;
- V teréne Kontrola vyberte hodnotu Excel doplnky a stlačte tlačidlo Skok;
- V teréne Dostupné doplnky začiarknite políčko vedľa Hľadanie riešenia a kliknite na tlačidlo OK.
Poznámka. okno doplnky dostupné aj na tab Vývojár. Ako povoliť túto kartu.
Po stlačení tlačidla Hľadanie riešenia v skupine analýza, otvorí sa jej dialógové okno .
Pri častom používaní Hľadanie riešenia je pohodlnejšie ho spustiť z panela s nástrojmi Rýchly prístup a nie z karty Údaje. Ak chcete umiestniť tlačidlo na panel, kliknite naň pravým tlačidlom myši a vyberte Pridať na panel s nástrojmi Rýchly prístup.
O modeloch
Táto časť je určená pre tých, ktorí sa s konceptom optimalizačného modelu ešte len zoznamujú.
Poradenstvo. Pred použitím Hľadanie riešenia Dôrazne odporúčame, aby ste si preštudovali literatúru o riešení optimalizačných problémov a zostavovaní modelov.
Nižšie je uvedený malý vzdelávací program na túto tému.
nadstavba Hľadanie riešenia pomáha určiť Najlepšia cesta robiť niečo:
- „Niečo“ môže zahŕňať prideľovanie peňazí na investície, nakladanie skladu, dodávanie tovaru alebo akúkoľvek inú podstatnú činnosť, pri ktorej sa vyžaduje optimálne riešenie.
- „Najlepší spôsob“ alebo optimálne riešenie v tomto prípade znamená: maximalizácia zisku, minimalizácia nákladov, dosiahnutie najlepšej kvality atď.
Tu je niekoľko typických príkladov problémov s optimalizáciou:
- Určte, pri ktorom je maximálny príjem z predaja vyrobených výrobkov;
- Určte, pri ktorých by boli celkové náklady na dopravu minimálne;
- Nájdite tak, aby celkové výrobné náklady boli minimálne;
- Určite minimálny čas na dokončenie všetkých aktivít projektu (kritická cesta).
Na formalizáciu úlohy je potrebné vytvoriť model, ktorý by odrážal základné charakteristiky predmetnej oblasti (a nezahŕňal by drobné detaily). Upozorňujeme, že model sa optimalizuje Hľadá sa riešenie iba jeden ukazovateľ(táto optimalizovaná metrika sa nazýva objektívna funkcia).
V MS EXCEL je model súborom vzájomne prepojených vzorcov, ktoré používajú premenné ako argumenty. Typicky môžu tieto premenné nadobúdať iba platné hodnoty s výhradou obmedzení špecifikovaných používateľom.
Hľadanie riešenia vyberie také hodnoty týchto premenných (berúc do úvahy dané obmedzenia), aby cieľová funkcia bola maximálna (minimálna) alebo rovná danej číselnej hodnote.
Poznámka. V najjednoduchšom prípade možno model opísať pomocou jediného vzorca. Niektoré z týchto modelov je možné optimalizovať pomocou . Pred prvým stretnutím Hľadá sa riešenie má zmysel najprv sa podrobne zaoberať súvisiacim nástrojom.
Hlavné rozdiely Výber parametrov od Hľadanie riešenia:
- Výber parametrov funguje len s modelmi s jednou premennou;
- nie je možné v ňom nastaviť obmedzenia pre premenné;
- nie je určené maximum alebo minimum účelovej funkcie, ale jej rovnosť s určitou hodnotou;
- funguje efektívne len v prípade lineárnych modelov, v nelineárnom prípade nájde lokálne optimum (najbližšie k počiatočnej hodnote premennej).
Príprava optimalizačného modelu v MS EXCEL
Hľadanie riešenia optimalizuje hodnotu cieľovej funkcie. Objektívna funkcia je vzorec, ktorý vracia jednu hodnotu v bunke. Výsledok vzorca musí závisieť od premenných modelu (nie nevyhnutne priamo, je to možné cez výsledok výpočtu iných vzorcov).
Modelové obmedzenia môžu byť uložené tak na rozsah variácií samotných premenných, ako aj na výsledky výpočtu iných vzorcov modelu, ktoré závisia od týchto premenných.
Všetky bunky obsahujúce modelové premenné a obmedzenia musia byť umiestnené len na jednom hárku zošita. Zadávanie parametrov v dialógovom okne Hľadanie riešenia možné len z tohto listu.
Na tomto hárku musí byť umiestnená aj účelová funkcia (bunka). Medzivýpočty (vzorce) však možno umiestniť na iné listy.
Poradenstvo. Usporiadajte údaje modelu tak, aby sa na jednom hárku MS EXCEL nachádzal iba jeden model. V opačnom prípade budete musieť na vykonávanie výpočtov neustále ukladať a načítavať nastavenia. Hľadanie riešenia(Pozri nižšie).
Predstavujeme algoritmus na prácu s Hľadá sa riešenie, ktorý odporúčajú samotní vývojári (www.solver.com):
- Definujte bunky s modelovými premennými (rozhodovacie premenné);
- Vytvorte v bunke vzorec, ktorý vypočíta účelovú funkciu vášho modelu (objektívnu funkciu);
- Vytvorte vzorce v bunkách, ktoré vypočítajú hodnoty, ktoré sa majú porovnávať s limitmi (ľavá strana výrazu);
- Pomocou dialógového okna Hľadanie riešenia zadajte odkazy na bunky obsahujúce premenné, na cieľovú funkciu, na vzorce pre obmedzenia a samotné hodnoty obmedzení;
- Bežať Hľadanie riešenia nájsť optimálne riešenie.
Urobme všetky tieto kroky na jednoduchom príklade.
Jednoduchý prípad použitia Hľadanie riešenia
Kontajner je potrebné naložiť tovarom tak, aby hmotnosť kontajnera bola maximálna. Kontajner má objem 32 metrov kubických. Tovar je uložený v škatuliach a prepravkách. Každá krabica s tovarom váži 20 kg, jej objem je 0,15 m3. Debna - 80 kg a 0,5 m3. Je potrebné, aby celkový počet nádob nebol menší ako 110 kusov.
Údaje modelu usporiadame nasledovne (pozri súbor s príkladom).
Premenné modelu (počet každého typu kontajnera) sú zvýraznené zelenou farbou.
Cieľová funkcia (celková hmotnosť všetkých krabíc a prepraviek) je vyznačená červenou farbou.
Modelové obmedzenia: podľa minimálneho počtu nádob (>=110) a podľa celkového objemu (<=32) – синим.
Účelová funkcia sa vypočíta podľa vzorca =SUMPRODUCT(B8:C8,B6:C6) je celková hmotnosť všetkých krabíc a prepraviek naložených do kontajnera.
Podobne vypočítame celkový objem - =SUMPRODUCT(B7:C7,B8:C8). Tento vzorec je potrebný na nastavenie limitu celkového objemu škatúľ a škatúľ (<=32).
Aby sme nastavili obmedzenie modelu, vypočítame celkový počet kontajnerov = SUM (B8: C8) .
Teraz s dialógovým oknom Hľadanie riešenia zadajme odkazy na bunky obsahujúce premenné, cieľovú funkciu, vzorce pre obmedzenia a samotné hodnoty obmedzení (alebo odkazy na zodpovedajúce bunky).
Je jasné, že počet políčok a políčok musí byť celé číslo – to je ďalšie obmedzenie modelu.
Po stlačení tlačidla Nájsť riešenie nájde sa taký počet krabíc a krabíc, pri ktorom je ich celková hmotnosť (objektívna funkcia) maximálna a zároveň sú splnené všetky stanovené obmedzenia.
Zhrnutie
V skutočnosti je hlavným problémom pri riešení optimalizačných problémov pomocou Hľadanie riešenia V žiadnom prípade nejde o jemnosť nastavenia tohto analytického nástroja, ale o správnosť zostavenia modelu, ktorý je adekvátny danej úlohe. Preto sa v ďalších článkoch budeme venovať budovaniu modelov, pretože práve „zakrivený“ model je často dôvodom nemožnosti nájsť riešenie pomocou Hľadanie riešenia.
Často je jednoduchšie pozrieť sa na niekoľko typických úloh, nájsť medzi nimi podobnú a potom tento model prispôsobiť svojej úlohe.
Riešenie klasických optimalizačných problémov pomocou Hľadanie riešenia zvážil .
Riešiteľ nenašiel vhodné riešenie
Táto správa sa zobrazí, keď Hľadanie riešenia nenašli kombinácie premenných hodnôt, ktoré by spĺňali všetky obmedzenia súčasne.
Ak používate Simplexná metóda riešenia lineárnych úloh, potom si môžete byť istí, že riešenie skutočne neexistuje.
Ak používate nelineárnu metódu riešenia problémov, ktorá vždy začína počiatočnými hodnotami premenných, môže to tiež znamenať, že možné riešenie je ďaleko od týchto počiatočných hodnôt. Ak bežíte Hľadanie riešenia s inými počiatočnými hodnotami premenných, možno sa nájde riešenie.
Predstavte si, že pri riešení problému nelineárnou metódou boli bunky s premennými ponechané prázdne (t.j. počiatočné hodnoty sú 0) a Hľadanie riešenia nenašiel riešenie. To neznamená, že v skutočnosti neexistuje žiadne riešenie (hoci môže byť). Teraz, na základe výsledkov určitého odborného posúdenia, zadáme do buniek s premennými inú množinu hodnôt, ktorá je podľa vás blízka optimálnej (požadovanej). V tomto prípade, Hľadanie riešenia dokáže nájsť riešenie (ak skutočne existuje).
Poznámka. O vplyve nelinearity modelu na výsledky výpočtu sa dočítate v poslednej časti článku.
V každom prípade (lineárnom alebo nelineárnom) musíte najskôr analyzovať model pre obmedzenia konzistencie, teda podmienky, ktoré nemožno splniť súčasne. Najčastejšie je to spôsobené nesprávnou voľbou pomeru (napr.<= вместо >=) alebo hraničná hodnota.
Ak je napríklad vo vyššie diskutovanom príklade hodnota maximálneho objemu nastavená na 16 m3 namiesto 32 m3, potom bude toto obmedzenie v rozpore s obmedzením minimálneho počtu miest na sedenie (110), pretože minimálny počet miest zodpovedá objemu rovnajúcemu sa 16,5 m3 (110 * 0,15, kde 0,15 je objem boxu, t. j. najmenšej nádoby). Nastavením limitu maximálneho objemu na 16 m3, Hľadanie riešenia nenájde riešenie.
S limitom 17 m3 Hľadanie riešenia nájde riešenie.
Niektoré nastavenia Hľadanie riešenia
Metóda riešenia
Vyššie uvažovaný model je lineárny, t.j. účelová funkcia (M je celková hmotnosť, ktorá môže byť maximálna) je vyjadrená nasledujúcou rovnicou M=a1*x1+a2*x2, kde x1 a x2 sú modelové premenné (počet krabíc a krabíc), a1 a a2 sú ich váhy. V lineárnom modeli musia byť obmedzenia tiež lineárnymi funkciami premenných. V našom prípade je objemové obmedzenie V=b1*x1+b2*x2 vyjadrené aj ako lineárny vzťah. Je zrejmé, že ďalšie obmedzenie - maximálny počet kontajnerov (n) - je tiež lineárne x1+x2
V tomto prípade je potrebné zvoliť metódu riešenia nelineárneho problému. Príklady nelineárnych závislostí: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, kde x je premenná a V je účelová funkcia.
Tlačidlá Pridať, Upraviť, Vymazať
Tieto tlačidlá vám umožňujú pridávať, upravovať a odstraňovať obmedzenia modelu.
Tlačidlo reštart
Ak chcete odstrániť všetky nastavenia Hľadanie riešenia stlač tlačidlo Resetovať– dialógové okno sa vymaže.
Táto možnosť je užitočná pri použití rôznych možností obmedzenia. Pri ukladaní parametrov modelu (tlačidlo Načítať/Uložiť,ďalej kliknite na tlačidlo Uložiť) navrhuje sa vybrať hornú bunku rozsahu (stĺpca), v ktorej bude umiestnený: odkaz na cieľovú funkciu, odkazy na bunky s premennými, obmedzenia a parametre metód riešenia (prístupné cez tlačidlo možnosti). Pred uložením sa uistite, že tento rozsah neobsahuje údaje o modeli.
Ak chcete načítať uložené parametre, najskôr stlačte tlačidlo Načítať/Uložiť a potom v zobrazenom dialógovom okne tlačidlo Stiahnuť ▼ a potom nastavte rozsah buniek obsahujúci predtým uložené nastavenia (nemôžete zadať iba jednu hornú bunku). Kliknite na tlačidlo OK. Potvrďte resetovanie aktuálnych nastavení úlohy a ich nahradenie novými.
Presnosť
Pri vytváraní modelu má výskumník na začiatku určitý odhad rozsahov variácií cieľovej funkcie a premenných. Vzhľadom na výpočty v MS EXCEL sa odporúča, aby tieto rozsahy variácií boli výrazne vyššie ako presnosť výpočtu (zvyčajne sa nastavuje od 0,001 do 0,000001). Údaje v modeli sú spravidla normalizované tak, aby sa variačné rozsahy cieľovej funkcie a premenných pohybovali v rozmedzí 0,1 – 100 000. Samozrejme, všetko závisí od konkrétneho modelu, ale ak sa vaše premenné zmenia o viac ako 5- 6 rádov, potom by ste možno mali model "zdrsniť" napríklad pomocou logaritmickej operácie.
Excel Add-in „Search for a solution“ je analytický nástroj, ktorý nám umožňuje rýchlo a jednoducho určiť, kedy a aký výsledok za určitých podmienok dostaneme. Možnosti nástroja na vyhľadávanie riešení sú oveľa vyššie, ako môže poskytnúť „výber parametrov“ v Exceli.
Hlavné rozdiely medzi hľadaním riešenia a výberom parametra:
- Výber viacerých možností v Exceli.
- Uloženie podmienok obmedzujúcich zmeny v bunkách, ktoré obsahujú premenné hodnoty.
- Možnosť využitia v prípadoch, kedy môže byť veľa riešení jedného problému.
Príklady a úlohy na hľadanie riešenia v Exceli
Zvážte analytické možnosti doplnku. Napríklad potrebujete našetriť 14 000 dolárov počas 10 rokov. Po dobu 10 rokov chcete každý rok odložiť 1 000 USD na vkladový účet v banke s 5 % ročne. Na obrázku nižšie je tabuľka v Exceli, ktorá prehľadne zobrazuje stav naakumulovaných prostriedkov za jednotlivé roky. Ako vidno, za takýchto podmienok vkladového účtu a akumulačných príspevkov sa cieľ nepodarí dosiahnuť ani po 10 rokoch. Existujú dva spôsoby riešenia tohto problému:- Nájdite si banku, ktorá ponúka vyšší úrok na vklady.
- Zvýšte výšku ročných financovaných príspevkov na bankový účet.
Môžeme zmeniť hodnoty premenných v bunkách B1 a B2, aby sme vybrali potrebné podmienky na akumuláciu požadovaného množstva peňazí.
Doplnok "Hľadať riešenie" - umožňuje súčasne použiť 2 z týchto možností, aby sme rýchlo simulovali najoptimálnejšie podmienky pre dosiahnutie nášho cieľa. Pre to:
Ako vidíte, program mierne zvýšil úrokovú sadzbu a výšku ročných príspevkov.
Obmedzenie parametrov pri hľadaní riešení
Povedzme, že ste prišli do banky s touto tabuľkou, ale banka vám odmietne zvýšiť úrokovú sadzbu. V takýchto prípadoch musíme zistiť, o koľko budeme musieť zvýšiť objem ročných investícií. Musíme nastaviť limit bunky s jednou hodnotou premennej. Pred spustením však zmeňte hodnoty v bunkách premenných na pôvodné: v B1 o 5% a v B2 o -1000 $. A teraz urobíme nasledovné.