Excel Solver: optimiziranje rezultatov, dodajanje omejitev in shranjevanje rešitev kot scenarije

Nastavite svoj Excel Reševalec model, s pomočjo pogovorno okno Reševalec Parameters. Če želite določiti ciljno celico, uporabite polje Set Objective, skupino Za pa uporabite, da rešitelju Excel poveste, kaj želite od ciljne celice: največjo možno vrednost; najmanjša možna vrednost; ali določeno vrednost. Končno uporabite polje S spreminjanjem spremenljivih celic, da določite celice, ki jih lahko Reševalec uporabi za vključitev vrednosti za optimizacijo rezultata.

Optimiziranje rezultatov Excel Solverja

Ko Reševalec najde rešitev, lahko izberete Ohrani rešitev Solverja ali Obnovi prvotne vrednosti. Če izberete Keep Solver Solution, Excel trajno spremeni delovni list. Sprememb ne morete razveljaviti.

Ko je model delovnega lista, ki je pripravljen za reševanje, pripravljen za uporabo, sledite spodnjim korakom za iskanje optimalnega rezultata za svoj model z uporabo Solverja:

Izberite Podatki → Reševalec.
Excel odpre pogovorno okno Solver Parameters.

V polje Nastavi cilj vnesite naslov ciljne celice vašega modela.
Upoštevajte, da če kliknete celico, da jo vnesete, Solver samodejno vnese absolutni naslov celice (na primer $B$14 namesto B14). Reševalec tako ali tako dobro deluje.

V skupini Za izberite možnost:

• Max: Vrne največjo možno vrednost.
• Min: Vrne najmanjšo možno vrednost.
• Vrednost: vnesite številko, da nastavite ciljno celico na to številko.

  Za vzorčni model je bila izbrana Value Of in v besedilno polje vnesena 0 .

V polje S spreminjanjem spremenljivih celic vnesite naslove celic, ki jih želite, da Solver spremeni, medtem ko išče rešitev.
V primeru sta spreminjajoči se celici B4 in C4. Naslednja slika prikazuje dokončano pogovorno okno Solver Parameters.

Končano pogovorno okno Parameters Solver.

Kliknite Reši.
Solver se loti posla. Ko Reševalec deluje na težavo, se lahko enkrat ali večkrat prikažejo pogovorna okna Pokaži poskusno rešitev.

V katerem koli pogovornem oknu Pokaži poskusno rešitev, ki se prikaže, kliknite Nadaljuj, da premaknete stvari.
Ko je optimizacija končana, Excel prikaže pogovorno okno Rezultati reševanja.

Pogovorno okno Rezultati reševanja in rešitev problema preloma.

Izberite možnost Keep Solver Solution.
Če ne želite sprejeti rezultata, namesto tega izberite možnost Obnovi prvotne vrednosti.

Kliknite V redu.

Solver lahko zahtevate, da prikaže eno ali več poročil, ki vam dajejo dodatne informacije o rezultatih. V pogovornem oknu Rezultati reševanja uporabite seznam Poročila, da izberete vsako poročilo, ki si ga želite ogledati:

• Odgovor: Prikaže informacije o ciljni celici modela, spremenljivih celicah in omejitvah. Za ciljno celico in spremenljive celice, Solver prikaže izvirne in končne vrednosti.
• Občutljivost: poskusi pokazati, kako občutljiva je rešitev za spremembe v formulah modela. Postavitev poročila o občutljivosti je odvisna od vrste modela, ki ga uporabljate.
• Omejitve: prikaže ciljno celico in njeno vrednost ter celice spremenljivke in njihove naslove, imena in vrednosti.

Excel Solver lahko uporabi eno od več metod reševanja. V pogovornem oknu Parametri reševalca uporabite seznam Izbira metode reševanja, da izberete eno od naslednjih možnosti:

• Simplex LP: uporabite, če je model vašega delovnega lista linearen. Najpreprosteje povedano, linearni model je tisti, v katerem spremenljivke niso povišane na nobene stopnje in se ne uporablja nobena od tako imenovanih transcendentnih funkcij – kot sta SIN in COS.
• GRG Nelinearno: uporabite, če je model vašega delovnega lista nelinearen in gladek. Na splošno je gladek model tisti, pri katerem graf uporabljene enačbe ne kaže ostrih robov ali prelomov.
• Evolucijsko: uporabite, če je model vašega delovnega lista nelinearen in neglajen.

Vas mora kaj od tega skrbeti? Skoraj zagotovo ne. Excel Solver privzeto uporablja GRG Nelinear in to bi moralo delovati za skoraj vse, kar počnete z Solverjem.

Dodajanje omejitev v Excel Solver

Resnični svet formulam postavlja omejitve in pogoje. Tovarna ima lahko največjo zmogljivost 10.000 enot na dan, število zaposlenih v podjetju ne more biti negativno število, vaši stroški oglaševanja pa so lahko omejeni na 10 odstotkov skupnih stroškov.

Podobno predpostavimo, da izvajate analizo preloma za dva izdelka. Če zaženete optimizacijo brez kakršnih koli omejitev, lahko Solver doseže skupni dobiček 0 tako, da en izdelek nastavite na rahlo izgubo, drugega pa na majhen dobiček, pri čemer se izguba in dobiček medsebojno izničita. Pravzaprav, če natančno pogledate prejšnjo sliko, je Solver naredil točno to. Če želite dobiti pravo rešitev za rentabilnost, boste morda raje videli obe vrednosti dobička izdelka kot 0.

Takšne omejitve in pogoji so primeri tega, kar Solver imenuje omejitve. Dodajanje omejitev pove Solverju, naj poišče rešitev, tako da ti pogoji ne bodo kršeni.

Takole zaženete Solver z omejitvami, dodanimi optimizaciji:

Izberite Podatki → Reševalec.
Excel odpre pogovorno okno Solver Parameters.

Uporabite polje Nastavi cilj, skupino Za in polje S spreminjanjem spremenljivih celic, da nastavite reševalca, kot je opisano zgoraj.

Kliknite Dodaj.
Excel prikaže pogovorno okno Dodaj omejitev.

V polje Referenca celice vnesite naslov celice, ki jo želite omejiti.
Vnesete lahko naslov ali izberete celico na delovnem listu.

Na spustnem seznamu izberite operaterja, ki ga želite uporabiti.
Večino časa uporabljate primerjalni operator, kot je enako (=) ali večje od (>). Uporabite operator int (celo število), ko potrebujete omejitev, kot je skupno število zaposlenih, da je celo število namesto realnega števila (to je število z decimalno komponento; ne morete imeti 10,5 zaposlenih!). Uporabite bin (binarni) operator, če imate omejitev, ki mora biti TRUE ali FALSE (ali 1 ali 0).

Če ste v 5. koraku izbrali primerjalni operator, v polje Omejitev vnesite vrednost, s katero želite omejiti celico.
Ta slika prikazuje primer dokončanega pogovornega okna Dodaj omejitev. V vzorčnem modelu ta omejitev pove Solverju, naj poišče rešitev, tako da je dobiček izdelka napihljive pikado (celica B12) enak 0.

Končano pogovorno okno Dodaj omejitev.

Če želite določiti več omejitev, kliknite Dodaj in po potrebi ponovite korake od 4 do 6.

Za primer dodate omejitev, ki zahteva, da dobiček izdelka Dog Polisher (celica C12) znaša 0.

Kliknite V redu.

Excel se vrne v pogovorno okno Parametri reševalca in prikaže vaše omejitve v seznamskem polju Predmet omejitev.

Kliknite Reši.

V katerem koli pogovornem oknu Pokaži poskusno rešitev, ki se prikaže, kliknite Nadaljuj, da premaknete stvari.
Na spodnji sliki je prikazana primer rešitve preloma z dodanimi omejitvami. Upoštevajte, da ni le celica skupnega dobička (B14) nastavljena na 0, ampak sta tudi dve celici dobička izdelka (B12 in C12).

Pogovorno okno Rezultati reševanja in končna rešitev problema preloma.

Izberite možnost Keep Solver Solution.
Če ne želite sprejeti rezultata, namesto tega izberite možnost Obnovi prvotne vrednosti.

Kliknite V redu.

Dodate lahko največ 100 omejitev. Če morate spremeniti omejitev, preden začnete reševati, izberite omejitev v seznamskem polju Predmet omejitve, kliknite Spremeni in nato prilagodite v pogovornem oknu Spremeni omejitev, ki se prikaže. Če želite izbrisati omejitev, ki je ne potrebujete več, jo izberite in kliknite Izbriši.

Shranite rešitev Excel Solver kot scenarij

Kadar koli imate model preglednice, ki uporablja koherenten nabor vhodnih vrednosti – znanih kot spreminjanje celic –, imate to, kar Excel imenuje scenarij. Pri Solverju so te spreminjajoče se celice njegove spremenljive celice, tako da rešitev Solver pomeni nekakšen scenarij v Excelu . Vendar vam Solver ne ponuja enostavnega načina za shranjevanje in ponovni zagon določene rešitve. Če želite rešiti to težavo, lahko rešitev shranite kot scenarij, ki ga lahko pozneje prikličete s funkcijo Excelovega upravitelja scenarijev.

Sledite tem korakom, da shranite rešitev Solver kot scenarij:

Izberite Podatki → Reševalec.
Excel odpre pogovorno okno Solver Parameters.

Za nastavitev reševalca, kot je opisano zgoraj, uporabite polje Nastavi cilj, skupino Za, polje S spremembo spremenljivih celic in predmet na seznam omejitev.

Kliknite Reši.

Kadar koli se prikaže pogovorno okno Pokaži poskusno rešitev, izberite Nadaljuj.
Ko je optimizacija končana, Excel prikaže pogovorno okno Rezultati reševanja.

Kliknite Shrani scenarij.
Excel prikaže pogovorno okno Shrani scenarij.

V pogovornem oknu Ime scenarija vnesite ime scenarija in kliknite V redu.
Excel vas vrne v pogovorno okno Rezultati reševanja.

Izberite možnost Keep Solver Solution.
Če ne želite sprejeti rezultata, namesto tega izberite možnost Obnovi prvotne vrednosti.

Kliknite V redu.