„Excel Solver“: rezultatų optimizavimas, apribojimų pridėjimas ir sprendimų išsaugojimas kaip scenarijus

„ Excel Solver“ modelį nustatote naudodami dialogo langą „Solver Parameters“. Naudojate laukelį Nustatyti tikslą, kad nurodytumėte tikslo langelį, o naudodami grupę To nurodykite Excel Solver, ko norite iš tikslo langelio: didžiausią įmanomą reikšmę; mažiausia galima vertė; arba konkrečią vertę. Galiausiai naudokite laukelį Keičiant kintamuosius langelius, kad nurodytumėte langelius, kuriuos Solver gali naudoti, kad įjungtų reikšmes, kad optimizuotų rezultatą.

„Excel Solver“ rezultatų optimizavimas

Kai „Solver“ randa sprendimą, galite pasirinkti „Keep Solver Solution“ arba „Restore Original Values“. Jei pasirinksite Keep Solver Solution, Excel visam laikui pakeis darbalapį. Negalite anuliuoti pakeitimų.

Kai jūsų Solver paruoštas darbalapio modelis yra paruoštas naudoti, toliau pateikiami veiksmai, kurių reikia atlikti, kad rastumėte optimalų savo modelio rezultatą naudodami Solver:

Pasirinkite Duomenys → Sprendimas.
„Excel“ atidaro dialogo langą „Solver Parameters“.

Lauke Nustatyti tikslą įveskite savo modelio objektyvo langelio adresą.
Atminkite, kad jei spustelėsite langelį, kad jį įvestumėte, Solver automatiškai įves absoliutų langelio adresą (pavyzdžiui, $14, o ne B14). Solver veikia gerai bet kuriuo atveju.

Grupėje Kam pasirinkite parinktį:

• Max: grąžina didžiausią įmanomą reikšmę.
• Min: grąžina mažiausią galimą reikšmę.
• Reikšmė: įveskite skaičių, kad objektyvo langelyje būtų nustatytas šis skaičius.

  Pavyzdiniame modelyje buvo pasirinkta reikšmė ir teksto laukelyje įvesta 0 .

Lauke Keičiant kintamuosius langelius įveskite langelių, kuriuos norite pakeisti, kol Solver ieškos sprendimo, adresus.
Pavyzdyje kintančios ląstelės yra B4 ir C4. Toliau pateiktame paveikslėlyje parodytas užpildytas Sprendimo parametrų dialogo langas.

Užbaigtas Sprendimo parametrų dialogo langas.

Spustelėkite Išspręsti.
Solver imasi reikalo. Kadangi Solver dirba su problema, vieną ar daugiau kartų galite pamatyti dialogo langus Rodyti bandomąjį sprendimą.

Bet kuriame rodomame dialogo lange Rodyti bandomąjį sprendimą spustelėkite Tęsti, kad perkeltumėte dalykus.
Kai optimizavimas baigtas, „Excel“ rodo dialogo langą Sprendimo rezultatai.

Dialogo langas Sprendimo rezultatai ir lūžio problemos sprendimas.

Pasirinkite parinktį Keep Solver Solution.
Jei nenorite priimti rezultato, pasirinkite parinktį Atkurti pradines reikšmes.

Spustelėkite Gerai.

Galite paprašyti Solver parodyti vieną ar daugiau ataskaitų, kuriose pateikiama papildomos informacijos apie rezultatus. Dialogo lange Sprendimo rezultatai naudokite sąrašą Ataskaitos, kad pasirinktumėte kiekvieną ataskaitą, kurią norite peržiūrėti:

• Atsakymas: rodoma informacija apie modelio objektyvo langelį, kintamuosius langelius ir apribojimus. Objektyvaus langelio ir kintamųjų langelių Solver rodo pradinę ir galutinę reikšmes.
• Jautrumas: bandoma parodyti, koks jautrus sprendimas yra modelio formulių pokyčiams. Jautrumo ataskaitos išdėstymas priklauso nuo naudojamo modelio tipo.
• Ribos: rodomas tikslinis langelis ir jo reikšmė, taip pat kintamieji langeliai ir jų adresai, pavadinimai ir reikšmės.

„Excel Solver“ gali naudoti vieną iš kelių sprendimo būdų. Dialogo lange Solver Parameters naudokite sąrašą Pasirinkti sprendimo metodą, kad pasirinktumėte vieną iš šių parinkčių:

• Simplex LP: naudokite, jei darbalapio modelis yra tiesinis. Paprasčiausiai tariant, linijinis modelis yra toks, kuriame kintamieji nepakeliami į jokius laipsnius ir nenaudojama nė viena iš vadinamųjų transcendentinių funkcijų, tokių kaip SIN ir COS.
• GRG Netiesinis: naudokite, jei darbalapio modelis yra netiesinis ir sklandus. Apskritai, sklandus modelis yra toks, kuriame naudojamos lygties diagrama nerodo aštrių kraštų ar lūžių.
• Evoliucinis: naudokite, jei darbalapio modelis yra netiesinis ir nelygus.

Ar turite nerimauti dėl kokių nors dalykų? Beveik tikrai ne. „Excel Solver“ pagal numatytuosius nustatymus naudoja GRG Netiesinį, ir tai turėtų veikti beveik viskam, ką darote su Solver.

Apribojimų įtraukimas į „Excel Solver“.

Realus pasaulis nustato formulėms apribojimus ir sąlygas. Didžiausias gamyklos pajėgumas gali būti 10 000 vienetų per dieną, įmonės darbuotojų skaičius negali būti neigiamas, o reklamos išlaidos gali būti apribotos iki 10 procentų visų išlaidų.

Panašiai tarkime, kad atliekate dviejų produktų lūžio analizę. Jei optimizuosite be jokių apribojimų, „Solver“ gali pasiekti 0 bendrą pelną, kai vienam produktui nustatomas nedidelis nuostolis, o kitam - nedidelis pelnas, o nuostoliai ir pelnas vienas kitą panaikina. Tiesą sakant, jei atidžiai pažvelgsite į ankstesnį vaizdą, tai būtent tai padarė Solver. Norėdami gauti tikrą lūžio sprendimą, galbūt norėsite, kad abi produkto pelno vertės būtų 0.

Tokie apribojimai ir sąlygos yra pavyzdžiai to, ką Solver vadina apribojimais. Pridėjus apribojimus, Solver nurodoma rasti sprendimą, kad šios sąlygos nebūtų pažeistos.

Štai kaip paleisti Solver su optimizavimo apribojimais:

Pasirinkite Duomenys → Sprendimas.
„Excel“ atidaro dialogo langą „Solver Parameters“.

Naudokite laukelį Nustatyti tikslą, grupę Kam ir laukelį Keičiant kintamuosius langelius, kad nustatytumėte Solver, kaip aprašyta aukščiau.

Spustelėkite Pridėti.
„Excel“ rodo dialogo langą „Pridėti apribojimą“.

Lauke Ląstelės nuoroda įveskite langelio, kurį norite apriboti, adresą.
Galite įvesti adresą arba pasirinkti langelį darbalapyje.

Išskleidžiamajame sąraše pasirinkite operatorių, kurį norite naudoti.
Dažniausiai naudojate palyginimo operatorių, pvz., lygų (=) arba didesnį nei (>). Naudokite operatorių int (sveikasis skaičius), kai reikia, kad apribojimas, pvz., bendras darbuotojų skaičius, būtų sveikasis skaičius, o ne tikrasis skaičius (ty skaičius su dešimtainiu komponentu; negalite turėti 10,5 darbuotojo!). Naudokite bin (dvejetainį) operatorių, kai turite apribojimą, kuris turi būti TRUE arba FALSE (arba 1 arba 0).

Jei 5 veiksme pasirinkote palyginimo operatorių, lauke Apribojimas įveskite reikšmę, kuria norite apriboti langelį.
Šiame paveikslėlyje parodytas užbaigto Pridėti apribojimo dialogo lango pavyzdys. Pavyzdiniame modelyje šis apribojimas nurodo Solver rasti sprendimą, kad pripučiamos smiginio lentos (ląstelė B12) produkto pelnas būtų lygus 0.

Užbaigtas dialogo langas Add Constraint.

Norėdami nurodyti daugiau apribojimų, spustelėkite Pridėti ir, jei reikia, pakartokite 4–6 veiksmus.

Pavyzdžiui, pridedate apribojimą, kuris reikalauja, kad šunų poliravimo produkto pelnas (ląstelė C12) būtų 0.

Spustelėkite Gerai.

„Excel“ grįžta į dialogo langą „Solver Parameters“ ir sąrašo lauke „Apribojimai“ pateikia apribojimus.

Spustelėkite Išspręsti.

Bet kuriame rodomame dialogo lange Rodyti bandomąjį sprendimą spustelėkite Tęsti, kad perkeltumėte dalykus.
Toliau pateiktame paveikslėlyje parodytas lūžio sprendimo pavyzdys su pridėtais apribojimais. Atkreipkite dėmesį, kad ne tik bendro pelno langelis (B14) nustatytas kaip 0, bet ir du produkto pelno langeliai (B12 ir C12).

Dialogo langas Sprendimo rezultatai ir galutinis lūžio problemos sprendimas.

Pasirinkite parinktį Keep Solver Solution.
Jei nenorite priimti rezultato, pasirinkite parinktį Atkurti pradines reikšmes.

Spustelėkite Gerai.

Galite pridėti daugiausia 100 apribojimų. Be to, jei jums reikia pakeisti apribojimą prieš pradėdami spręsti, pasirinkite apribojimą sąrašo lauke Subject to the Constraints, spustelėkite Keisti ir atlikite koregavimus pasirodžiusiame dialogo lange Keisti apribojimą. Jei norite ištrinti apribojimą, kurio jums nebereikia, pasirinkite apribojimą ir spustelėkite Naikinti.

Išsaugokite „Excel Solver“ sprendimą kaip scenarijų

Kai turite skaičiuoklės modelį, kuriame naudojamas nuoseklus įvesties reikšmių rinkinys, vadinamas besikeičiančiais langeliais , turite tai, ką „Excel“ vadina scenarijumi. Naudojant Solver, šie besikeičiantys langeliai yra jo kintamieji langeliai, todėl Solver sprendimas yra tam tikras scenarijus programoje „Excel“ . Tačiau „Solver“ nesuteikia lengvo būdo išsaugoti ir pakartotinai paleisti konkretų sprendimą. Norėdami išspręsti šią problemą, galite išsaugoti sprendimą kaip scenarijų, kurį vėliau galėsite prisiminti naudodami „Excel“ scenarijų tvarkyklės funkciją.

Atlikite šiuos veiksmus, kad išsaugotumėte Solver sprendimą kaip scenarijų:

Pasirinkite Duomenys → Sprendimas.
„Excel“ atidaro dialogo langą „Solver Parameters“.

Naudokite laukelį Nustatyti tikslą, grupę Kam, laukelį Keičiant kintamuosius langelius ir sąrašą Apribojimų objektas, kad nustatytumėte Solver, kaip aprašyta aukščiau.

Spustelėkite Išspręsti.

Kai tik pasirodys dialogo langas Rodyti bandomąjį sprendimą, pasirinkite Tęsti.
Kai optimizavimas baigtas, „Excel“ rodo dialogo langą Sprendimo rezultatai.

Spustelėkite Išsaugoti scenarijų.
„Excel“ rodo dialogo langą Įrašyti scenarijų.

Dialogo lange Scenario pavadinimas įveskite scenarijaus pavadinimą ir spustelėkite Gerai.
„Excel“ grąžina jus į dialogo langą Sprendimo rezultatai.

Pasirinkite parinktį Keep Solver Solution.
Jei nenorite priimti rezultato, pasirinkite parinktį Atkurti pradines reikšmes.

Spustelėkite Gerai.