Dodatak Excel Solver vrši matematičku optimizaciju. Ovo se obično koristi za uklapanje složenih modela u podatke ili pronalaženje iterativnih rješenja problema. Na primjer, možda biste željeli uklopiti krivu kroz neke točke podataka, koristeći jednadžbu. Solver može pronaći konstante u jednadžbi koje najbolje odgovaraju podacima. Druga primjena je gdje je teško preurediti model kako bi traženi rezultat bio predmet jednačine.
Gdje je Solver u Excelu?
Dodatak Solver je uključen u Excel, ali se ne učitava uvijek kao dio zadane instalacije. Da provjerite da li je učitan, odaberite karticu DATA i potražite ikonu Solver u odjeljku Analiza.
Ako ne možete pronaći Solver ispod kartice DATA, morat ćete učitati dodatak:
-
Odaberite karticu FILE, a zatim odaberite Opcije.
Image -
U dijaloškom okviru Opcije odaberite Add-Ins sa kartica na lijevoj strani.
Image -
Na dnu prozora izaberite Excel dodaci sa padajućeg menija Manage i izaberite Idi…
Image -
Označite kvadratić pored Solver Add-in i odaberite OK.
Image -
Komanda Solver bi se sada trebala pojaviti na kartici DATA. Spremni ste za korištenje Solvera.
Image
Upotreba Solvera u Excelu
Počnimo s jednostavnim primjerom da shvatimo šta radi Solver. Zamislite da želimo znati koji radijus će dati krug površine 50 kvadratnih jedinica. Znamo jednačinu za površinu kruga (A=pi r2). Mogli bismo, naravno, preurediti ovu jednačinu da damo radijus potreban za datu oblast, ali radi primjera hajde da se pretvaramo da ne znamo kako to učiniti.
Kreirajte tabelu sa radijusom u B1 i izračunajte površinu u B2 koristeći jednadžbu =pi()B1^2.
Mogli bismo ručno podesiti vrijednost u B1 dok B2 ne pokaže vrijednost koja je dovoljno blizu 50. U zavisnosti od toga koliko smo precizni mora biti, ovo bi mogao biti praktičan pristup. Međutim, ako trebamo biti vrlo precizni, potrebno je mnogo vremena da izvršimo potrebna podešavanja. Zapravo, ovo je u suštini ono što Solver radi. On prilagođava vrijednosti u određenim ćelijama i provjerava vrijednost u ciljnoj ćeliji:
- Odaberite DATA karticu i Solver, da učitate Solver Parameters okvir za dijalog
-
Postavi cilj ćelija da bude područje, B2. Ovo je vrijednost koja će se provjeravati, prilagođavajući druge ćelije dok ova ne dostigne tačnu vrijednost.
Image -
Odaberite dugme za Vrijednost od: i postavite vrijednost od 50. Ovo je vrijednost koju bi B2 trebao postići.
Image -
U polju pod nazivom Promjenom varijabilnih ćelija: unesite ćeliju koja sadrži radijus, B1.
Image -
Ostavite ostale opcije kakve jesu prema zadanim postavkama i odaberite Riješi. Optimizacija je izvršena, vrijednost B1 se podešava dok B2 ne bude 50 i prikazuje se dijalog Rezultati rješavanja.
Image -
Odaberite OK da zadržite rješenje.
Image
Ovaj jednostavan primjer je pokazao kako radi rješavač. U ovom slučaju, mogli smo lakše doći do rješenja na druge načine. Zatim ćemo pogledati neke primjere gdje Solver daje rješenja koja bi bilo teško pronaći na bilo koji drugi način.
Ugradnja složenog modela pomoću dodatka Excel Solver
Excel ima ugrađenu funkciju za izvođenje linearne regresije, uklapajući pravu 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 eksponencijali. Za složenije funkcije Solver se može koristiti za izvođenje 'minimizacije najmanjih kvadrata'. U ovom primjeru, razmotrit ćemo uklapanje jednačine oblika ax^b+cx^d na podatke prikazane ispod.
Ovo uključuje sljedeće korake:
- Uredi skup podataka sa x vrijednostima u koloni A i y-vrijednostima u koloni B.
- Kreirajte 4 vrijednosti koeficijenta (a, b, c, i d) negdje u tabeli, njima se mogu dati proizvoljne početne vrijednosti.
-
Kreirajte kolonu uklopljenih Y vrijednosti, koristeći jednadžbu oblika ax^b+cx^d koja upućuje na koeficijente kreirane u koraku 2 i x vrijednosti u koloni A. Imajte na umu da da biste kopirali formulu naniže kolone, reference na koeficijente moraju biti apsolutne, dok reference na x vrijednosti moraju biti relativne.
Image -
Iako nije bitno, možete dobiti vizuelnu indikaciju o tome koliko je jednačina dobra tako što ćete nacrtati oba y kolona naspram x vrijednosti na jednom XY dijagramu raspršivanja. Ima smisla koristiti markere za originalne tačke podataka, pošto su to diskretne vrijednosti sa šumom, i koristiti liniju za prilagođenu jednačinu.
Image -
Dalje, potreban nam je način kvantifikacije razlike između podataka i naše prilagođene jednačine. Standardni način da se to uradi je izračunavanje sume kvadrata razlika. U trećoj koloni, za svaki red, originalna vrijednost podataka za Y oduzima se od vrijednosti prilagođene jednadžbe, a rezultat se kvadrira. Dakle, u D2, vrijednost je data sa =(C2-B2)^2 Zatim se izračunava zbir svih ovih kvadrata vrijednosti. Pošto su vrijednosti na kvadrat, one mogu biti samo pozitivne.
Image -
Sada ste spremni da izvršite optimizaciju koristeći Solver. Postoje četiri koeficijenta koje je potrebno prilagoditi (a, b, c i d). Također imate jednu ciljnu vrijednost koju treba minimizirati, zbir kvadrata razlika. Pokrenite rješavač, kao što je gore navedeno, i postavite parametre rješavača da upućuju na ove vrijednosti, kao što je prikazano ispod.
Image -
Uklonite oznaku sa opcije Negativne varijable bez ograničenja, ovo bi prisililo sve koeficijente da uzimaju pozitivne vrijednosti.
Image -
Odaberite Riješi i pregledajte rezultate. Grafikon će se ažurirati dajući dobar pokazatelj dobrote pristajanja. Ako se rješavač ne dobro uklapa u prvom pokušaju, možete ga pokušati ponovo pokrenuti. Ako se uklapanje poboljšalo, pokušajte riješiti s trenutnih vrijednosti. U suprotnom, možete pokušati ručno poboljšati uklapanje prije rješavanja.
Image - Kada se dobije 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 inputa. Međutim, kada pokušamo da preokrenemo problem, nije moguće pronaći jednostavno rješenje. Na primjer, snaga koju troši vozilo je približno data sa P=av + bv^3 gdje je v brzina, a koeficijent za otpor kotrljanja, a b koeficijent za aerodinamički otpor. Iako je ovo prilično jednostavna jednačina, nije je lako preurediti kako bi se dala jednačina brzine koju će vozilo postići za datu ulaznu snagu. Međutim, možemo koristiti Solver da iterativno pronađemo ovu brzinu. Na primjer, pronađite brzinu postignutu sa ulaznom snagom od 740 W.
-
Postavite jednostavnu tabelu sa 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 promjenjive ćelije koje želite promijeniti. Odaberite riješi da započnete rješenje.
Image -
Rešavač prilagođava vrijednost brzine sve dok snaga ne bude vrlo blizu 740, obezbjeđujući brzinu koja nam je potrebna.
Image - Rješavanje modela na ovaj način često može biti brže i manje sklono greš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 u pronalaženju razumnog rješenja, često je korisno primijeniti granične uvjete na promjenjive ćelije. Ovo su granične vrijednosti preko kojih ih ne treba prilagođavati. Na primjer, u prethodnom primjeru, brzina ne bi trebala biti manja od nule i također bi bilo moguće postaviti gornju granicu. Ovo bi bila brzina za koju ste prilično sigurni da vozilo ne može ići brže. Ako ste u mogućnosti da postavite granice za promjenjive varijabilne ćelije, to također čini da druge naprednije opcije rade bolje, 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 je pogodan samo za linearne modele, ako problem nije linearan, neće uspjeti s porukom da ovaj uvjet nije ispunjen. Druge dvije metode su prikladne za nelinearne metode. GRG Nelinearni je najbrži, ali njegovo rješenje može u velikoj mjeri ovisiti o početnim početnim uvjetima. Ima fleksibilnost da ne zahtijeva varijable za postavljanje granica. Evolucijski rješavač je često najpouzdaniji, ali zahtijeva da sve varijable imaju i gornje i donje granice, što može biti teško razraditi unaprijed.
Dodatak Excel Solver je vrlo moćan alat koji se može primijeniti na mnoge praktične probleme. Da biste u potpunosti pristupili moći Excela, pokušajte kombinirati Solver s Excel makroima.
