Kako koristiti Excel 2019 Solver

Iako Excelove naredbe Data Table i Goal Seek rade sasvim dobro za jednostavne probleme koji zahtijevaju određivanje izravnog odnosa između ulaza i rezultata u formuli, morate koristiti dodatak Solver kada se bavite složenijim problemima. Na primjer, koristite Solver da biste pronašli najbolje rješenje kada trebate promijeniti više ulaznih vrijednosti u vašem Excel modelu i trebate nametnuti ograničenja na te vrijednosti i/ili izlaznu vrijednost.

Dodatak Solver radi primjenom iterativnih metoda kako bi se pronašlo “najbolje” rješenje s obzirom na ulazne podatke, željeno rješenje i ograničenja koja namećete. Uz svaku iteraciju, program primjenjuje metodu pokušaja i pogreške (temeljenu na korištenju linearnih ili nelinearnih jednadžbi i nejednadžbi) kojom se pokušava približiti optimalnom rješenju.

Kada koristite dodatak Solver, imajte na umu da mnogi problemi, osobito oni kompliciraniji, imaju mnoga rješenja. Iako Solver vraća optimalno rješenje, s obzirom na početne vrijednosti, varijable koje se mogu promijeniti i ograničenja koja definirate, ovo rješenje često nije jedino moguće i, zapravo, možda nije najbolje rješenje za vas. Da biste bili sigurni da ćete pronaći najbolje rješenje, možda ćete htjeti pokrenuti Solver više puta, prilagođavajući početne vrijednosti svaki put kada riješite problem.

Prilikom postavljanja problema za dodatak Solver u radnom listu programa Excel, definirajte sljedeće stavke:

• Ciljna ćelija: Ciljna ćelija u vašem radnom listu čiju vrijednost treba povećati, minimizirati ili postići da postigne određenu vrijednost. Imajte na umu da ova ćelija mora sadržavati formulu.
• Varijabilne ćelije: ćelije koje se mijenjaju u vašem radnom listu čije vrijednosti treba prilagoditi dok se ne pronađe odgovor.
• Ograničene ćelije: ćelije koje sadrže ograničenja koja namećete promjenama vrijednosti u varijabilnim ćelijama i/ili ciljnoj ćeliji u ciljnoj ćeliji.

Nakon što završite s definiranjem problema s ovim parametrima i programski dodatak Solver riješi problem, program vraća optimalno rješenje mijenjajući vrijednosti u vašem radnom listu. U ovom trenutku možete odabrati zadržati promjene na radnom listu ili vratiti izvorne vrijednosti na radni list. Rješenje možete također spremiti kao scenarij za kasnije pregled prije nego što vratite izvorne vrijednosti.

Dodatak Solver možete koristiti s Upraviteljem scenarija za pomoć pri postavljanju problema za rješavanje ili za spremanje rješenja kako biste ga kasnije mogli vidjeti. Promjenjive ćelije koje definirate za Upravitelj scenarija automatski preuzima i koristi Solver kada odaberete ovu naredbu, i obrnuto. Također, možete spremiti rješenje Rješavača za problem kao scenarij (klikom na gumb Spremi scenarij u dijaloškom okviru Solver) koji zatim možete vidjeti u Upravitelju scenarija.

Postavljanje i definiranje problema u programu Excel 2019

Prvi korak u postavljanju problema na kojem će Solver raditi je stvaranje modela radnog lista za koji ćete definirati ciljnu ćeliju, ćelije varijabli i ćelije ograničenja.

Imajte na umu da je Solver pomoćni programski dodatak. To znači da, prije nego što ga možete koristiti, morate biti sigurni da je program dodatka Solver još uvijek učitan, što pokazuje izgled gumba Solver u grupi Analiza na kraju kartice Podaci na vrpci. Ako ovaj gumb nedostaje, možete učitati Solver tako da otvorite karticu Add-Ins u dijaloškom okviru Excel Options (Alt+FTAA), a zatim kliknete gumb Idi nakon što se uvjerite da su Excel dodaci prikazani u padajućem izborniku Upravljanje okvir s popisom s njegove lijeve strane. Zatim u dijaloškom okviru Add-Ins označite potvrdni okvir Programski dodatak za rješavanje kako biste u njega stavili kvačicu prije nego što kliknete U redu da zatvorite dijaloški okvir i ponovno učitate dodatak.

Da biste definirali i riješili problem s dodatkom Solver nakon što ste učitali dodatak i kreirali svoj model radnog lista, slijedite ove korake:

Kliknite naredbeni gumb Solver u grupi Analiza na kraju kartice Podaci vrpce.

Excel otvara dijaloški okvir Solver Parameters.

Određivanje parametara za primjenu na model u dijaloškom okviru Parameters Solver.

Kliknite ciljnu ćeliju na radnom listu ili unesite njezinu referencu ćelije ili naziv raspona u tekstualni okvir Postavi cilj.

Zatim morate odabrati postavku Za. Kliknite gumb opcije Max kada želite da vrijednost ciljne ćelije bude što veća. Kliknite gumb opcije Min kada želite da vrijednost ciljne ćelije bude što manja. Kliknite gumb opcije Vrijednost i zatim unesite vrijednost u pridruženi tekstni okvir kada želite da vrijednost ciljne ćelije dosegne određenu vrijednost.

Kliknite odgovarajuću opciju gumba opcije u odjeljku Prima dijaloškog okvira. Ako odaberete gumb opcije Value Of, unesite odgovarajuću vrijednost u pridruženi tekstualni okvir.

Zatim odredite promjenjive ćelije — to jest, one koje Solver može promijeniti kako bi postigao vaš cilj Equal To.

Kliknite tekstni okvir Promjenom varijabilnih ćelija, a zatim odaberite ćelije koje želite promijeniti na radnom listu ili unesite njihove reference ćelija ili naziv raspona u tekstni okvir.

Zapamtite da za odabir nesusjednih ćelija na radnom listu morate držati pritisnutu tipku Ctrl dok kliknete svaku ćeliju u odabiru. Da bi Excel odabrao ćelije koje se mijenjaju za vas na temelju ciljane ćelije koju ste odabrali, kliknite gumb Pogodi desno od ovog okvira za tekst.

Prije nego što Solver prilagodi vaš model, možete dodati ograničenja za ciljnu ćeliju ili bilo koju od promjenjivih ćelija koje određuju njezina ograničenja prilikom prilagođavanja vrijednosti.

(Neobavezno) Kliknite gumb Dodaj desno od okvira s popisom Subject to the Constraints u dijaloškom okviru Parameters Solver.

Ova radnja otvara dijaloški okvir Dodaj ograničenje. Kada definirate ograničenje, odaberite ćeliju čiju vrijednost želite ograničiti ili odaberite ćeliju na radnom listu ili unesite referencu njezine ćelije u okvir za tekst Referenca ćelije. Zatim odaberite odnos (=, <=,>=, ili int za cijeli broj ili bin za binarni) iz okvira padajućeg popisa s desne strane i (osim ako niste odabrali int ili bin ) unesite odgovarajuću vrijednost ili referencu ćelije u Tekstni okvir ograničenja.

Za nastavak dodavanja ograničenja za druge ćelije koje koristi Solver, kliknite gumb Dodaj da biste dodali ograničenje i poništite tekstualne okvire u dijaloškom okviru Dodaj ograničenje. Zatim ponovite korak 5 da dodate novo ograničenje. Nakon što završite s definiranjem ograničenja za ciljnu ćeliju i promjenom vrijednosti u modelu, kliknite U redu da zatvorite dijaloški okvir Dodaj ograničenje i vratite se u dijaloški okvir Parametri rješenja (koji sada navodi vaša ograničenja u okviru s popisom Predmet ograničenja).

(Neobavezno) Poništite potvrdni okvir Učini neograničene varijable nenegativnim ako želite dopustiti negativne vrijednosti kada varijabilne ćelije nisu podložne ograničenjima.
Prema zadanim postavkama, Solver Add-in koristi GRG (Generalized Reduced Gradient) nelinearnu metodu u rješavanju modela čije parametre postavljate poznati kao vrlo učinkovit način rješavanja glatkih nelinearnih problema. Da biste koristili LP Simplex metodu (za linearno programiranje slijedeći Simplex algoritam) ili Evolucioni motor za rješavanje problema koji nisu glatki, trebate slijediti 7. korak.

(Izborno) Odaberite LP Simplex ili Evolutionary s padajućeg popisa Odaberi metodu rješavanja da biste koristili bilo koju od ovih metoda za rješavanje problema koji nisu glatki.

Kliknite gumb Riješi da bi Solver riješio problem onako kako ste ga definirali u dijaloškom okviru Parametri rješenja.

Rješavanje problema s Excelovim Solverom

Kada kliknete gumb Riješi, dijaloški okvir Parametri rješavača nestaje, a statusna traka pokazuje da rješavatelj postavlja problem, a zatim vas obavještava o napretku u rješavanju problema prikazujući broj međuprobnih (ili probnih) rješenja kako se isprobavaju. Da biste prekinuli proces rješenja u bilo kojem trenutku prije nego što Excel izračuna posljednju iteraciju, pritisnite tipku Esc. Excel zatim prikazuje dijaloški okvir Prikaži probno rješenje, obavještavajući vas da je proces rješenja pauziran. Za nastavak postupka rješenja kliknite gumb Nastavi. Da biste prekinuli postupak rješenja, kliknite gumb Stop.

Kada Excel završi proces rješenja, pojavljuje se dijaloški okvir Rezultati rješavanja. Ovaj dijaloški okvir obavještava vas je li Solver uspio pronaći rješenje, s obzirom na ciljnu ćeliju, mijenjanje ćelija i ograničenja definirana za problem. Da biste zadržali promjene koje Solver čini u modelu vašeg radnog lista, ostavite gumb opcije Zadrži rješenje rješenja i kliknite U redu da zatvorite dijaloški okvir Rezultati rješavanja. Da biste vratili izvorne vrijednosti na radni list, umjesto toga kliknite gumb opcije Vrati izvorne vrijednosti. Da biste spremili promjene kao scenarij prije nego što vratite izvorne vrijednosti, kliknite gumb Spremi scenarij i dodijelite naziv trenutnom scenariju prije nego što kliknete opciju Vrati izvorne vrijednosti i gumb U redu.

Dijaloški okvir Solver Results koji pokazuje da je Solver pronašao rješenje problema.

Za razliku od korištenja naredbe Traženje cilja, nakon što kliknete gumb opcije Zadrži rješenje rješenja u dijaloškom okviru Rezultati rješavanja, ne možete koristiti gumb naredbe Poništi na alatnoj traci za brzi pristup za vraćanje izvornih vrijednosti na radni list. Ako želite imati mogućnost prebacivanja između prikaza "prije" i "poslije" vašeg radnog lista, morate spremiti promjene pomoću gumba Spremi scenarij, a zatim odabrati gumb opcije Vrati izvorne vrijednosti. Na taj način možete zadržati prikaz "prije" u izvornom radnom listu i koristiti Upravitelj scenarija za prikaz "nakon" prikaza koji je kreirao Solver.

Promjena Excelovih opcija rješavanja

Za većinu problema, zadane opcije koje koristi Solver su prikladne. U nekim situacijama, međutim, možda ćete htjeti promijeniti neke od opcija Solvera prije nego što započnete proces rješenja. Da biste promijenili opcije rješenja, kliknite gumb Opcije u dijaloškom okviru Parametri rješenja. Excel zatim otvara dijaloški okvir Mogućnosti s odabranom karticom Sve metode gdje možete izvršiti sve potrebne promjene.

Promjena opcija rješenja u dijaloškom okviru Mogućnosti.

Postavke opcija rješenja Excel 2019

Nakon promjene opcija, kliknite OK za povratak u dijaloški okvir Parametri rješavanja; odavde možete kliknuti gumb Riješi da biste započeli proces rješenja s novim postavkama rješenja koje ste upravo promijenili.

Kada koristite zadanu GRG (Generalized Reduced Gradient) nelinearnu ili evolucijsku metodu, možete postaviti dodatne postavke Solvera pomoću opcija na karticama GRG Nelinear i Evolutionary dijaloškog okvira Opcije. Ove opcije uključuju promjenu postavki Konvergiranje, Veličina populacije i Nasumično sjeme za bilo koju od ovih metoda.

Spremanje i učitavanje problema modela u Excel 2019

Ciljna ćelija, varijabilne ćelije, ćelije ograničenja i opcije rješenja koje ste nedavno koristili spremaju se kao dio Excel radnog lista kada kliknete gumb Spremi na alatnoj traci za brzi pristup (Ctrl+S). Kada definirate druge probleme za isti radni list koji želite spremiti, morate kliknuti gumb Spremi model u dijaloškom okviru Mogućnosti rješavanja i navesti referencu ćelije ili naziv raspona u aktivnom radnom listu gdje želite da budu parametri problema umetnuta.

Kada kliknete gumb Učitaj/Spremi, Excel otvara dijaloški okvir Učitaj/Spremi model koji sadrži tekstni okvir Odaberi područje modela. Ovaj tekstni okvir sadrži reference ćelija za raspon dovoljno velik da sadrži sve parametre problema, počevši od aktivne ćelije. Da biste spremili parametre problema u ovom rasponu, kliknite U redu. Ako ovaj raspon uključuje ćelije s postojećim podacima, morate izmijeniti referencu ćelije u ovom tekstualnom okviru prije nego što kliknete U redu kako biste spriječili Excel da zamijeni postojeće podatke.

Nakon što kliknete U redu, Excel kopira parametre problema u navedenom rasponu. Te se vrijednosti zatim spremaju kao dio radnog lista sljedeći put kada spremate radnu knjigu. Za ponovno korištenje ovih parametara problema prilikom rješavanja problema, jednostavno trebate otvoriti dijaloški okvir Mogućnosti rješavanja, kliknuti gumb Učitaj/Spremi da biste otvorili dijaloški okvir Učitaj/Spremi model, kliknite gumb Učitaj, a zatim odaberite raspon koji sadrži spremljeni parametri problema. Kada kliknete U redu u dijaloškom okviru Učitavanje modela, Excel učitava parametre iz ovog raspona ćelija u odgovarajuće tekstualne okvire u dijaloškom okviru Parametri rješavača. Zatim možete zatvoriti dijaloški okvir Mogućnosti rješavanja klikom na OK, a problem možete riješiti korištenjem ovih parametara klikom na naredbeni gumb Riješi.

Ne zaboravite da možete koristiti gumb Reset All kad god želite izbrisati sve parametre definirane za prethodni problem i vratiti opcije rješavanja na njihove zadane vrijednosti.

Izrada izvješća Solvera u Excelu 2019

Pomoću Solvera možete izraditi tri različite vrste izvješća:

• Izvješće o odgovorima: navodi ciljnu ćeliju i promjenjive ćelije s njihovim izvornim i konačnim vrijednostima, zajedno s ograničenjima koja se koriste u rješavanju problema.
• Izvješće o osjetljivosti: pokazuje koliko je osjetljivo optimalno rješenje na promjene u formulama koje izračunavaju ciljnu ćeliju i ograničenja. Izvješće prikazuje promjenjive ćelije s njihovim konačnim vrijednostima i smanjeni gradijent za svaku ćeliju. (Smanjeni gradijent mjeri cilj povećanja po jedinici u ćeliji koja se mijenja.) Ako ste definirali ograničenja, izvješće o osjetljivosti navodi ih s njihovim konačnim vrijednostima i Lagrangeovim množiteljem za svako ograničenje. (Lagrangeov množitelj mjeri cilj povećanja po jedinici koji se pojavljuje na desnoj strani jednadžbe ograničenja.)
• Izvješće o granicama: prikazuje ciljnu ćeliju i ćelije koje se mijenjaju s njihovim vrijednostima, donjim i gornjim granicama i ciljanim rezultatima. Donja granica predstavlja najnižu vrijednost koju stanica koja se mijenja može imati dok fiksira vrijednosti svih ostalih ćelija i još uvijek zadovoljava ograničenja. Gornja granica predstavlja najveću vrijednost koja će to učiniti.

Excel smješta svako izvješće koje generirate za problem Solvera u zaseban radni list u radnoj knjizi. Da biste generirali jedno (ili sva) od ovih izvješća, odaberite vrstu izvješća (Odgovor, Osjetljivost ili Ograničenja) iz okvira s popisom Izvješća dijaloškog okvira Rezultati rješavanja. Da biste odabrali više od jednog izvješća, samo kliknite naziv izvješća.

Kada kliknete U redu da biste zatvorili dijaloški okvir Rezultati rješavanja (nakon što odaberete između opcija Zadrži rješenje rješenja i Vrati izvorne vrijednosti), Excel generira izvješće (ili izvješća) koje ste odabrali na novom radnom listu koji dodaje na početak radne knjige . (Kartice lista izvješća imenovane su prema vrsti izvješća, kao u Izvješću o odgovorima 1, Izvješću o osjetljivosti 1 i Izvješću o granicama 1. )