Excel Solver: optimiziranje rezultata, dodavanje ograničenja i spremanje rješenja kao scenarija

Svoj model Excel Solver postavljate pomoću dijaloškog okvira Parametri rješenja. Koristite okvir Postavi cilj da odredite ciljnu ćeliju, a koristite grupu Za da biste rekli Excel Solveru što želite od ćelije cilja: maksimalnu moguću vrijednost; minimalna moguća vrijednost; ili određenu vrijednost. Konačno, koristite okvir Promjenom varijabilnih ćelija da navedete ćelije koje Solver može koristiti za dodavanje vrijednosti radi optimizacije rezultata.

Optimiziranje rezultata Excel Solvera

Kada Solver pronađe rješenje, možete odabrati Zadrži rješenje rješenja ili Vrati izvorne vrijednosti. Ako odaberete Keep Solver Solution, Excel trajno mijenja radni list. Ne možete poništiti promjene.

S vašim modelom radnog lista koji je spreman za rješavanje, evo koraka koje trebate slijediti kako biste pronašli optimalan rezultat za svoj model pomoću Solvera:

Odaberite Podaci → Rješač.
Excel otvara dijaloški okvir Solver Parameters.

U okvir Postavi cilj unesite adresu ciljne ćelije vašeg modela.
Imajte na umu da ako kliknete ćeliju da biste je unijeli, Solver automatski unosi apsolutnu adresu ćelije (na primjer, $B$14 umjesto B14). Solver radi dobro u svakom slučaju.

U grupi Prima odaberite opciju:

• Max: Vraća najveću moguću vrijednost.
• Min: Vraća minimalnu moguću vrijednost.
• Vrijednost: Unesite broj da biste ciljnu ćeliju postavili na taj broj.

  Za primjer modela odabrana je vrijednost od i 0 unesena u okvir za tekst.

U okvir Promjenom varijabilnih ćelija unesite adrese ćelija koje želite da Solver promijeni dok traži rješenje.
U primjeru, stanice koje se mijenjaju su B4 i C4. Sljedeća slika prikazuje dovršeni dijaloški okvir Solver Parameters.

Dovršeni dijaloški okvir Parametri rješenja.

Kliknite Riješi.
Solver prelazi na posao. Kako Solver radi na problemu, možda ćete vidjeti da se dijaloški okviri Prikaži probno rješenje pojavljuju jednom ili više puta.

U bilo kojem dijaloškom okviru Prikaži probno rješenje koji se pojavi kliknite Nastavi za premještanje stvari.
Kada je optimizacija dovršena, Excel prikazuje dijaloški okvir Rezultati rješavanja.

Dijaloški okvir Rezultati rješavanja i rješenje problema rentabilnosti.

Odaberite opciju Keep Solver Solution.
Ako ne želite prihvatiti rezultat, umjesto toga odaberite opciju Vrati izvorne vrijednosti.

Kliknite OK.

Možete zatražiti od Solvera da prikaže jedno ili više izvješća koja vam daju dodatne informacije o rezultatima. U dijaloškom okviru Rezultati rješavanja koristite popis Izvješća da biste odabrali svako izvješće koje želite pogledati:

• Odgovor: Prikazuje informacije o ciljnoj ćeliji modela, varijabilnim ćelijama i ograničenjima. Za ciljnu ćeliju i varijabilne ćelije, Solver prikazuje izvorne i konačne vrijednosti.
• Osjetljivost: Pokušaji pokazati koliko je rješenje osjetljivo na promjene u formulama modela. Izgled izvješća o osjetljivosti ovisi o vrsti modela koji koristite.
• Ograničenja: Prikazuje ciljnu ćeliju i njezinu vrijednost, kao i varijabilne ćelije i njihove adrese, nazive i vrijednosti.

Excel Solver može koristiti jednu od nekoliko metoda rješavanja. U dijaloškom okviru Parametri rješavača koristite popis Odabir metode rješavanja da biste odabrali jedno od sljedećeg:

• Simplex LP: Koristite ako je model vašeg radnog lista linearan. Najjednostavnijim mogućim riječima, linearni model je onaj u kojem se varijable ne podižu ni na jedan stepen i ne koristi se nijedna od takozvanih transcendentnih funkcija – poput SIN i COS.
• GRG Nelinearni: Koristite ako je model vašeg radnog lista nelinearan i gladak. Općenito govoreći, glatki model je onaj u kojem graf korištene jednadžbe ne pokazuje oštre rubove ili lomove.
• Evolucijski: koristite ako je model vašeg radnog lista nelinearan i negladak.

Moraš li se brinuti zbog nečega od ovoga? Gotovo sigurno ne. Excel Solver prema zadanim postavkama koristi GRG Nelinear, a to bi trebalo funkcionirati za gotovo sve što radite s Solverom.

Dodavanje ograničenja u Excel Solver

Stvarni svijet formulama postavlja ograničenja i uvjete. Tvornica može imati maksimalni kapacitet od 10.000 jedinica dnevno, broj zaposlenika u tvrtki ne može biti negativan broj, a vaši troškovi oglašavanja mogu biti ograničeni na 10 posto ukupnih troškova.

Slično, pretpostavimo da izvodite analizu rentabilnosti za dva proizvoda. Ako pokrenete optimizaciju bez ikakvih ograničenja, Solver bi mogao postići ukupnu dobit od 0 postavljanjem jednog proizvoda na blagi gubitak, a drugog na malu dobit, pri čemu se gubitak i dobit međusobno poništavaju. Zapravo, ako pomno pogledate prethodnu sliku, to je upravo ono što je Solver napravio. Da biste dobili pravo rješenje za rentabilnost, možda biste radije vidjeli obje vrijednosti profita proizvoda kao 0.

Takva ograničenja i uvjeti primjeri su onoga što Solver naziva ograničenjima. Dodavanje ograničenja govori Solveru da pronađe rješenje kako se ti uvjeti ne bi prekršili.

Evo kako pokrenuti Solver s ograničenjima koja su dodana optimizaciji:

Odaberite Podaci → Rješač.
Excel otvara dijaloški okvir Solver Parameters.

Upotrijebite okvir Postavi cilj, grupu Za i okvir Promjenom varijabilnih ćelija da postavite Solver kao što je gore opisano.

Kliknite Dodaj.
Excel prikazuje dijaloški okvir Dodaj ograničenje.

U okvir Referenca ćelije unesite adresu ćelije koju želite ograničiti.
Možete upisati adresu ili odabrati ćeliju na radnom listu.

Na padajućem popisu odaberite operatera kojeg želite koristiti.
Većinu vremena koristite operator usporedbe, kao što je jednako (=) ili veće od (>). Upotrijebite int (integer) operator kada trebate ograničenje, kao što je ukupan broj zaposlenih, da bude cjelobrojna vrijednost umjesto stvarnog broja (to jest, broj s decimalnom komponentom; ne možete imati 10,5 zaposlenika!). Koristite bin (binarni) operator kada imate ograničenje koje mora biti TRUE ili FALSE (ili 1 ili 0).

Ako ste u koraku 5 odabrali operator usporedbe, u okvir Ograničenje unesite vrijednost kojom želite ograničiti ćeliju.
Ova slika prikazuje primjer dovršenog dijaloškog okvira Add Constraint. U primjeru modela, ovo ograničenje govori Solveru da pronađe rješenje tako da je profit proizvoda Daske za napuhavanje (ćelija B12) jednak 0.

Dovršeni dijaloški okvir Dodaj ograničenje.

Da biste naveli više ograničenja, kliknite Dodaj i po potrebi ponovite korake od 4 do 6.

Za primjer, dodajete ograničenje koje traži da profit proizvoda Dog Polisher (ćelija C12) bude 0.

Kliknite OK.

Excel se vraća u dijaloški okvir Parametri rješenja i prikazuje vaša ograničenja u okviru s popisom Predmet ograničenja.

Kliknite Riješi.

U bilo kojem dijaloškom okviru Prikaži probno rješenje koji se pojavi kliknite Nastavi za premještanje stvari.
Slika u nastavku prikazuje primjer rješenja rentabilnosti s dodanim ograničenjima. Primijetite da ne samo da je ćelija ukupne dobiti (B14) postavljena na 0, već su i dvije ćelije dobiti proizvoda (B12 i C12).

Dijaloški okvir Result Results i konačno rješenje problema rentabilnosti.

Odaberite opciju Keep Solver Solution.
Ako ne želite prihvatiti rezultat, umjesto toga odaberite opciju Vrati izvorne vrijednosti.

Kliknite OK.

Možete dodati najviše 100 ograničenja. Također, ako trebate promijeniti ograničenje prije nego počnete rješavati, odaberite ograničenje u okviru s popisom Predmet ograničenja, kliknite Promijeni, a zatim izvršite svoje prilagodbe u dijaloškom okviru Promjena ograničenja koji se pojavi. Ako želite izbrisati ograničenje koje vam više nije potrebno, odaberite ograničenje, a zatim kliknite Izbriši.

Spremite rješenje Excel Solvera kao scenarij

Kad god imate model proračunske tablice koji koristi koherentan skup ulaznih vrijednosti – poznat kao mijenjanje ćelija – imate ono što Excel naziva scenarijem. S Solverom, ove promjenjive ćelije su njegove promjenjive ćelije, tako da rješenje Solvera predstavlja svojevrsni scenarij u Excelu . Međutim, Solver vam ne daje jednostavan način za spremanje i ponovno pokretanje određenog rješenja. Da biste zaobišli ovaj problem, možete spremiti rješenje kao scenarij kojeg kasnije možete pozvati pomoću Excelove značajke Upravitelj scenarija.

Slijedite ove korake da biste rješenje Solvera spremili kao scenarij:

Odaberite Podaci → Rješač.
Excel otvara dijaloški okvir Solver Parameters.

Upotrijebite okvir Postavi cilj, grupu Za, okvir Promjenom varijabilnih ćelija i popis Predmet ograničenja kako biste postavili Solver kao što je gore opisano.

Kliknite Riješi.

Kad god se pojavi dijaloški okvir Prikaži probno rješenje, odaberite Nastavi.
Kada je optimizacija dovršena, Excel prikazuje dijaloški okvir Rezultati rješavanja.

Kliknite Spremi scenarij.
Excel prikazuje dijaloški okvir Spremi scenarij.

U dijaloškom okviru Naziv scenarija upišite naziv scenarija, a zatim kliknite U redu.
Excel vas vraća u dijaloški okvir Rezultati rješavanja.

Odaberite opciju Keep Solver Solution.
Ako ne želite prihvatiti rezultat, umjesto toga odaberite opciju Vrati izvorne vrijednosti.

Kliknite OK.