Dodatak Excel Solver izvodi matematičku optimizaciju. To se obično koristi za prilagođavanje složenih modela podacima ili pronalaženje iterativnih rješenja problema. Na primjer, možda biste željeli uklopiti krivulju kroz neke podatkovne točke pomoću jednadžbe. Rješivač može pronaći konstante u jednadžbi koje najbolje odgovaraju podacima. Druga je primjena gdje je teško preurediti model kako bi traženi izlaz bio predmet jednadžbe.
Gdje je Solver u Excelu?
Dodatak Solver uključen je u Excel, ali se ne učitava uvijek kao dio zadane instalacije. Kako biste provjerili je li učitan, odaberite karticu DATA i potražite ikonu Solver u odjeljku Analysis.
Ako ne možete pronaći Solver pod karticom PODACI, tada ćete morati učitati dodatak:
-
Odaberite karticu FILE i zatim odaberite Opcije.
Image -
U dijaloškom okviru Options odaberite Add-Ins s kartica s lijeve strane.
Image -
Na dnu prozora odaberite Excel Add-ins iz Manage padajućeg izbornika i odaberite Go…
Image -
Označite potvrdni okvir pored Solver Add-in i odaberite OK.
Image -
Naredba Solver sada bi se trebala pojaviti na kartici DATA. Spremni ste za korištenje Solvera.
Image
Korištenje Solvera u Excelu
Počnimo s jednostavnim primjerom da bismo razumjeli što Solver radi. Zamislimo da želimo znati koji polumjer će dati krug s površinom od 50 kvadratnih jedinica. Znamo jednadžbu za površinu kruga (A=pi r2). Mogli bismo, naravno, preurediti ovu jednadžbu da damo radijus potreban za određeno područje, ali radi primjera pretvarajmo se da ne znamo kako to učiniti.
Kreirajte proračunsku tablicu s radijusom u B1 i izračunajte površinu u B2 koristeći jednadžbu =pi()B1^2.
Možemo ručno prilagoditi vrijednost u B1 dok B2 ne pokaže vrijednost koja je dovoljno blizu 50. Ovisno o tome koliko smo točni treba biti, ovo bi mogao biti praktičan pristup. Međutim, ako trebamo biti vrlo precizni, trebat će dosta vremena da se izvrše potrebne prilagodbe. Zapravo, to je u biti ono što Solver radi. Prilagođava vrijednosti u određenim ćelijama i provjerava vrijednost u ciljnoj ćeliji:
- Odaberite DATA karticu i Solver, za učitavanje Solver Parameters dijaloškog okvira
-
Postavi ćeliju Cilj da bude Područje, B2. Ovo je vrijednost koja će se provjeravati, prilagođavajući druge ćelije dok ova ne postigne točnu vrijednost.
Image -
Odaberite gumb za Vrijednost od: i postavite vrijednost od 50. Ovo je vrijednost koju B2 treba postići.
Image -
U okvir naslovljen Promjenom varijabilnih ćelija: unesite ćeliju koja sadrži radijus, B1.
Image -
Ostavite ostale opcije prema zadanim postavkama i odaberite Riješi. Optimizacija se provodi, vrijednost B1 se prilagođava dok B2 ne bude 50 i Solver Results dijalog se prikazuje.
Image -
Odaberite OK da biste zadržali rješenje.
Image
Ovaj jednostavan primjer pokazao je kako rješavač radi. U ovom slučaju, mogli smo lakše doći do rješenja na druge načine. Zatim ćemo pogledati neke primjere u kojima Solver daje rješenja koja bi bilo teško pronaći na bilo koji drugi način.
Uklapanje složenog modela pomoću dodatka Excel Solver
Excel ima ugrađenu funkciju za izvođenje linearne regresije, uklapajući ravnu liniju kroz skup podataka. Mnoge uobičajene nelinearne funkcije mogu se linearizirati što znači da se linearna regresija može koristiti za uklapanje funkcija kao što su eksponencijalne. Za složenije funkcije Solver se može koristiti za izvođenje 'minimizacije najmanjih kvadrata'. U ovom primjeru razmotrit ćemo prilagođavanje jednadžbe oblika ax^b+cx^d podacima prikazanim u nastavku.
Ovo uključuje sljedeće korake:
- Rasporedite skup podataka s vrijednostima x u stupcu A i vrijednostima y u stupcu B.
- Stvorite 4 vrijednosti koeficijenta (a, b, c i d) negdje u proračunskoj tablici, njima se mogu dati proizvoljne početne vrijednosti.
-
Stvorite stupac prilagođenih Y vrijednosti, koristeći jednadžbu oblika ax^b+cx^d koja upućuje na koeficijente stvorene u koraku 2 i x vrijednosti u stupcu A. Imajte na umu da kako biste kopirali formulu stupcu, reference na koeficijente moraju biti apsolutne, dok reference na x vrijednosti moraju biti relativne.
Image -
Iako nije bitno, možete dobiti vizualni pokazatelj koliko se jednadžba dobro uklapa iscrtavanjem oba y stupca u odnosu na x vrijednosti na jednom XY raspršenom grafikonu. Ima smisla koristiti oznake za izvorne podatkovne točke, budući da su to diskretne vrijednosti s šumom, i koristiti liniju za prilagođenu jednadžbu.
Image -
Dalje, trebamo način kvantificiranja razlike između podataka i naše prilagođene jednadžbe. Standardni način za to je izračunavanje zbroja kvadrata razlika. U trećem stupcu, za svaki redak, izvorna vrijednost podataka za Y oduzima se od vrijednosti prilagođene jednadžbe, a rezultat se kvadrira. Dakle, u D2, vrijednost je dana sa =(C2-B2)^2 Zbroj svih ovih kvadratnih vrijednosti se zatim izračunava. Budući da su vrijednosti na kvadrat one mogu biti samo pozitivne.
Image -
Sada ste spremni za izvođenje optimizacije pomoću Solvera. Četiri su koeficijenta koja je potrebno prilagoditi (a, b, c i d). Također imate jednu objektivnu vrijednost koju treba minimizirati, zbroj kvadrata razlika. Pokrenite Solver, kao gore, i postavite parametre Solvera da referenciraju ove vrijednosti, kao što je prikazano u nastavku.
Image -
Odznačite opciju Neograničene varijable učinite nenegativnim, ovo bi prisililo sve koeficijente da poprime pozitivne vrijednosti.
Image -
Odaberite Riješi i pregledajte rezultate. Grafikon će se ažurirati dajući dobar pokazatelj dobrog pristajanja. Ako alat za rješavanje problema ne odgovara dobro u prvom pokušaju, možete ga pokušati ponovno pokrenuti. Ako se prilagodba poboljšala, pokušajte riješiti iz trenutnih vrijednosti. U suprotnom, možete pokušati ručno poboljšati prilagodbu prije rješavanja.
Image - Kada se postigne dobro uklapanje, možete izaći iz rješavača.
Iterativno rješavanje modela
Ponekad postoji relativno jednostavna jednadžba koja daje izlaz u smislu nekog ulaza. Međutim, kada pokušavamo invertirati problem nije moguće pronaći jednostavno rješenje. Na primjer, snaga koju vozilo troši je približno dana P=av + bv^3 gdje je v brzina, a je koeficijent za otpor kotrljanja, a b je koeficijent za aerodinamički otpor. Iako je ovo prilično jednostavna jednadžba, nije ju lako preurediti da bi se dobila jednadžba brzine koju će vozilo postići za određenu ulaznu snagu. Međutim, možemo koristiti Solver za iterativno pronalaženje ove brzine. Na primjer, pronađite brzinu postignutu s ulaznom snagom od 740 W.
-
Postavite jednostavnu proračunsku tablicu s brzinom, koeficijentima a i b i snagom izračunatom iz njih.
Image -
Pokrenite Solver i unesite moć, B5, kao cilj. Postavite ciljnu vrijednost 740 i odaberite brzinu, B2, kao ćelije varijable za promjenu. Odaberite solve za početak rješenja.
Image -
Rješivač prilagođava vrijednost brzine sve dok snaga ne bude vrlo blizu 740, osiguravajući brzinu koju zahtijevamo.
Image - Rješavanje modela na ovaj način često može biti brže i manje sklono pogreškama od invertiranja složenih modela.
Razumijevanje različitih opcija dostupnih u rješavaču može biti prilično teško. Ako imate poteškoća s dobivanjem razumnog rješenja, često je korisno primijeniti rubne uvjete na promjenjive ćelije. To su granične vrijednosti iznad kojih se ne smiju prilagođavati. Na primjer, u prethodnom primjeru, brzina ne bi trebala biti manja od nule, a također bi bilo moguće postaviti gornju granicu. To bi bila brzina za koju ste prilično sigurni da vozilo ne može ići brže od. Ako možete postaviti granice za ćelije s promjenjivim varijablama, to također čini bolje funkcioniranje drugih naprednijih opcija, kao što je multistart. Ovo će pokrenuti niz različitih rješenja, počevši od različitih početnih vrijednosti za varijable.
Odabir metode rješavanja također može biti težak. Simplex LP prikladan je samo za linearne modele, ako problem nije linearan, neće uspjeti uz poruku da ovaj uvjet nije ispunjen. Obje druge dvije metode prikladne su za nelinearne metode. GRG Nonlinear je najbrži, ali njegovo rješenje može uvelike ovisiti o početnim početnim uvjetima. Ima fleksibilnost jer ne zahtijeva postavljanje granica varijabli. Evolucijski alat za rješavanje često je najpouzdaniji, ali zahtijeva da sve varijable imaju i gornju i donju granicu, što može biti teško odrediti unaprijed.
Dodatak Excel Solver vrlo je moćan alat koji se može primijeniti na mnoge praktične probleme. Da biste u potpunosti pristupili snazi Excela, pokušajte kombinirati Solver s Excel makronaredbama.
