Kaj je Excel Solver?

2024 Avtor: Abigail Brown | [email protected]. Nazadnje spremenjeno: 2024-01-07 19:06

Dodatek Excel Solver izvaja matematično optimizacijo. To se običajno uporablja za prilagajanje zapletenih modelov podatkom ali iskanje iterativnih rešitev za težave. Na primer, morda boste želeli prilagoditi krivuljo skozi nekatere podatkovne točke z uporabo enačbe. Reševalec lahko najde konstante v enačbi, ki se najbolje prilegajo podatkom. Druga aplikacija je, kjer je težko preurediti model, da bi zahtevani rezultat postal predmet enačbe.

Kje je Solver v Excelu?

Dodatek Solver je vključen v Excel, vendar ni vedno naložen kot del privzete namestitve. Če želite preveriti, ali je naložen, izberite zavihek DATA in poiščite ikono Solver v razdelku Analysis.

Če pod zavihkom PODATKI ne najdete Reševalca, boste morali naložiti dodatek:

1. Izberite zavihek DATOTEKA in nato izberite Možnosti.

   

2. V pogovornem oknu Možnosti izberite Add-Ins na zavihkih na levi strani.

   

3. Na dnu okna izberite Excel Add-ins iz spustnega menija Manage in izberite Go…

   

4. Potrdite potrditveno polje poleg Dodatek za reševanje in izberite OK.

   

5. Ukaz Solver bi se zdaj moral prikazati na zavihku DATA. Pripravljeni ste na uporabo Reševalca.

   

Uporaba reševalnika v Excelu

Začnimo s preprostim primerom, da razumemo, kaj počne Reševalec. Predstavljajte si, da želimo vedeti, kakšen polmer bo dal krog s površino 50 kvadratnih enot. Poznamo enačbo za ploščino kroga (A=pi r²). To enačbo bi seveda lahko preuredili tako, da bi dali polmer, potreben za določeno območje, vendar se za primer pretvarjajmo, da ne vemo, kako to narediti.

Ustvarite preglednico s polmerom v B1 in izračunajte ploščino v B2 z uporabo enačbe =pi()B1^2.

Lahko ročno prilagodimo vrednost v B1, dokler B2 ne pokaže vrednosti, ki je dovolj blizu 50. Odvisno od tega, kako natančni smo je to morda praktičen pristop. Vendar, če moramo biti zelo natančni, bo potrebno dolgo časa, da izvedemo zahtevane prilagoditve. Pravzaprav to v bistvu počne Solver. Prilagodi vrednosti v določenih celicah in preveri vrednost v ciljni celici:

1. Izberite DATA zavihek in Solver, da naložite Solver Parameters pogovorno okno

2. Nastavi ciljno celico za območje, B2. To je vrednost, ki bo preverjena in prilagaja druge celice, dokler ta ne doseže pravilne vrednosti.

   

3. Izberite gumb za Vrednost: in nastavite vrednost 50. To je vrednost, ki naj bi jo B2 dosegel.

   

4. V polje z naslovom S spreminjanjem spremenljivih celic: vnesite celico, ki vsebuje polmer, B1.

   

5. Pustite druge možnosti, kot so privzete, in izberite Reši. Optimizacija se izvede, vrednost B1 se prilagodi, dokler B2 ni 50 in prikaže se pogovorno okno Solver Results.

   

6. Izberite OK, da obdržite rešitev.

   

Ta preprost primer je pokazal, kako deluje reševalec. V tem primeru bi lahko lažje prišli do rešitve na druge načine. Nato si bomo ogledali nekaj primerov, kjer Solver ponuja rešitve, ki bi jih bilo težko najti na kakršen koli drug način.

Prilagajanje zapletenega modela z uporabo dodatka Excel Solver

Excel ima vgrajeno funkcijo za izvajanje linearne regresije, ki prilega ravni črti skozi nabor podatkov. Številne običajne nelinearne funkcije je mogoče linearizirati, kar pomeni, da se lahko uporabi linearna regresija za prilagajanje funkcijam, kot so eksponente. Za bolj zapletene funkcije je mogoče uporabiti Reševalec za izvedbo "minimizacije najmanjših kvadratov". V tem primeru bomo razmislili o prilagajanju enačbe v obliki ax^b+cx^d spodaj prikazanim podatkom.

To vključuje naslednje korake:

1. Razporedite nabor podatkov z vrednostmi x v stolpcu A in vrednostmi y v stolpcu B.
2. Ustvarite 4 vrednosti koeficientov (a, b, c in d) nekje v preglednici, lahko jim dodelite poljubne začetne vrednosti.

3. Ustvarite stolpec prilagojenih vrednosti Y z uporabo enačbe oblike ax^b+cx^d, ki se sklicuje na koeficiente, ustvarjene v koraku 2, in vrednosti x v stolpcu A. Upoštevajte, da če želite kopirati formulo navzdol stolpcu morajo biti sklicevanja na koeficiente absolutna, sklicevanja na vrednosti x pa relativna.

   

4. Čeprav ni nujno, lahko dobite vizualni pokazatelj, kako dobro se enačba prilega, tako da narišete oba stolpca y proti vrednosti x na enem samem razpršenem grafikonu XY. Smiselno je uporabiti označevalce za prvotne podatkovne točke, ker so to diskretne vrednosti s šumom, in uporabiti črto za prirejeno enačbo.

   

5. Nato potrebujemo način kvantificiranja razlike med podatki in našo prilagojeno enačbo. Standardni način za to je izračun vsote kvadratov razlik. V tretjem stolpcu se za vsako vrstico prvotna vrednost podatkov za Y odšteje od vrednosti prilagojene enačbe, rezultat pa se kvadrira. Torej je v D2 vrednost podana z =(C2-B2)^2 Nato se izračuna vsota vseh teh kvadratov vrednosti. Ker so vrednosti na kvadrat, so lahko le pozitivne.

   

6. Zdaj ste pripravljeni za izvedbo optimizacije z uporabo Solverja. Obstajajo štirje koeficienti, ki jih je treba prilagoditi (a, b, c in d). Imate tudi eno ciljno vrednost, ki jo morate zmanjšati, vsoto kvadratov razlik. Zaženite reševalec, kot zgoraj, in nastavite parametre reševalnika, da se bodo sklicevali na te vrednosti, kot je prikazano spodaj.

   

7. Počistite možnost Neomejene spremenljivke naredite nenegativne, s tem bi vsi koeficienti zavzeli pozitivne vrednosti.

   

8. Izberite Solve in preglejte rezultate. Grafikon se bo posodobil in bo dobro pokazal ustreznost. Če se reševalec v prvem poskusu ne prilega dobro, ga lahko poskusite znova zagnati. Če se je prileganje izboljšalo, poskusite razrešiti iz trenutnih vrednosti. V nasprotnem primeru lahko poskusite ročno izboljšati prileganje, preden razrešite.

   

9. Ko je doseženo dobro prileganje, lahko zapustite reševalec.

Iterativno reševanje modela

Včasih obstaja razmeroma preprosta enačba, ki daje izhod v smislu nekega vhoda. Ko pa poskušamo problem obrniti, ni mogoče najti preproste rešitve. Na primer, moč, ki jo porabi vozilo, je približno podana z P=av + bv^3, kjer je v hitrost, a koeficient kotalnega upora in b koeficient za aerodinamičnega upora. Čeprav je to dokaj preprosta enačba, je ni enostavno preurediti, da bi dobili enačbo hitrosti, ki jo bo vozilo doseglo za dano vhodno moč. Lahko pa uporabimo Solver za iterativno iskanje te hitrosti. Na primer, poiščite hitrost, doseženo z vhodno močjo 740 W.

1. Nastavite preprosto preglednico s hitrostjo, koeficientoma a in b ter iz njih izračunano močjo.

   

2. Zaženi Reševalec in kot cilj vnesi moč, B5. Nastavite ciljno vrednost 740 in izberite hitrost, B2, kot spremenljive celice, ki jih želite spremeniti. Za začetek rešitve izberite solve.

   

3. Reševalec prilagodi vrednost hitrosti, dokler se moč ne približa 740, kar zagotavlja zahtevano hitrost.

   

4. Reševanje modelov na ta način je pogosto lahko hitrejše in manj nagnjeno k napakam kot obračanje kompleksnih modelov.

Razumevanje različnih možnosti, ki so na voljo v reševalniku, je lahko precej težko. Če imate težave pri pridobivanju razumne rešitve, je pogosto koristno uporabiti robne pogoje za spremenljive celice. To so mejne vrednosti, preko katerih se ne smejo prilagajati. Na primer, v prejšnjem primeru hitrost ne sme biti manjša od nič in prav tako bi bilo mogoče nastaviti zgornjo mejo. To bi bila hitrost, za katero ste skoraj prepričani, da vozilo ne more iti hitreje. Če lahko nastavite meje za celice s spremenljivo spremenljivko, potem bolje delujejo tudi druge naprednejše možnosti, na primer večkratni zagon. To bo zagnalo več različnih rešitev, začenši z različnimi začetnimi vrednostmi za spremenljivke.

Izbira metode reševanja je lahko tudi težavna. Simplex LP je primeren samo za linearne modele; če težava ni linearna, bo neuspešna s sporočilom, da ta pogoj ni bil izpolnjen. Drugi dve metodi sta primerni za nelinearne metode. GRG Nonlinear je najhitrejši, vendar je njegova rešitev lahko močno odvisna od začetnih začetnih pogojev. Ima prilagodljivost, saj ne zahteva, da imajo spremenljivke nastavljene meje. Evolucijski reševalec je pogosto najbolj zanesljiv, vendar zahteva, da imajo vse spremenljivke zgornjo in spodnjo mejo, kar je morda težko določiti vnaprej.

Dodatek Excel Solver je zelo zmogljivo orodje, ki ga je mogoče uporabiti za številne praktične težave. Za popoln dostop do moči Excela poskusite združiti Solver z Excelovimi makri.