Hva er Excel-løser?

2024 Forfatter: Abigail Brown | [email protected]. Sist endret: 2024-01-07 19:05

Excel Solver-tillegget utfører matematisk optimalisering. Dette brukes vanligvis for å tilpasse komplekse modeller til data eller finne iterative løsninger på problemer. For eksempel vil du kanskje passe en kurve gjennom noen datapunkter ved å bruke en ligning. Løser kan finne konstantene i ligningen som gir best tilpasning til dataene. En annen applikasjon er der det er vanskelig å omorganisere en modell for å gjøre den nødvendige utgangen til gjenstand for en ligning.

Hvor er Solver i Excel?

Solver-tillegget er inkludert i Excel, men det er ikke alltid lastet inn som en del av en standardinstallasjon. For å sjekke om den er lastet, velg DATA-fanen og se etter Solver-ikonet i Analysis-delen.

Hvis du ikke finner Solver under DATA-fanen, må du laste inn tillegget:

1. Velg fanen FILE og velg deretter Options.

   

2. I Options dialogboksen velg Add-Ins fra fanene på venstre side.

   

3. Velg Excel-tillegg nederst i vinduet fra rullegardinmenyen Manage og velg Go…

   

4. Merk av i avmerkingsboksen ved siden av Solver Add-in og velg OK.

   

5. Kommandoen Solver skal nå vises på DATA-fanen. Du er klar til å bruke Solver.

   

Using Solver i Excel

La oss starte med et enkelt eksempel for å forstå hva løseren gjør. Tenk deg at vi vil vite hvilken radius som vil gi en sirkel med et areal på 50 kvadratenheter. Vi kjenner ligningen for arealet av en sirkel (A=pi r²). Vi kan selvfølgelig omorganisere denne ligningen for å gi radiusen som kreves for et gitt område, men la oss for eksempel late som om vi ikke vet hvordan vi gjør det.

Lag et regneark med radius i B1 og beregn arealet i B2 ved å bruke ligningen =pi()B1^2.

Vi kunne justere verdien manuelt i B1 til B2 viser en verdi som er nær nok 50. Avhengig av hvor nøyaktig vi må være, kan dette være en praktisk tilnærming. Men hvis vi trenger å være veldig nøyaktige, vil det ta lang tid å gjøre de nødvendige justeringene. Egentlig er dette i hovedsak hva Solver gjør. Den gjør justeringer av verdier i visse celler, og kontrollerer verdien i en målcelle:

1. Velg DATA-fanen og Solver for å laste inn Solver Parameters-dialogboksen

2. Sett mål celle til å være området, B2. Dette er verdien som vil bli sjekket, og justerer andre celler til denne når riktig verdi.

   

3. Velg knappen for Verdi av: og angi en verdi på 50. Dette er verdien som B2 skal oppnå.

   

4. I boksen med tittelen Ved å endre variable celler: skriv inn cellen som inneholder radius, B1.

   

5. La de andre alternativene være som standard, og velg Solve. Optimaliseringen utføres, verdien av B1 justeres til B2 er 50 og Solver Results-dialogen vises.

   

6. Velg OK for å beholde løsningen.

   

Dette enkle eksemplet viste hvordan løseren fungerer. I dette tilfellet kunne vi lettere fått løsningen på andre måter. Deretter skal vi se på noen eksempler der Solver gir løsninger som ville være vanskelig å finne på noen annen måte.

Tilpasse en kompleks modell ved hjelp av Excel Solver-tillegget

Excel har en innebygd funksjon for å utføre lineær regresjon, og tilpasse en rett linje gjennom et sett med data. Mange vanlige ikke-lineære funksjoner kan lineariseres, noe som betyr at lineær regresjon kan brukes til å tilpasse funksjoner som eksponentialer. For mer komplekse funksjoner kan Solver brukes til å utføre en "minste kvadraters minimering". I dette eksemplet vil vi vurdere å tilpasse en ligning med formen ax^b+cx^d til dataene vist nedenfor.

Dette innebærer følgende trinn:

1. Arranger datasettet med x-verdiene i kolonne A og y-verdiene i kolonne B.
2. Opprett de 4 koeffisientverdiene (a, b, c og d) et sted på regnearket, disse kan gis vilkårlige startverdier.

3. Lag en kolonne med tilpassede Y-verdier ved å bruke en ligning med formen ax^b+cx^d som refererer til koeffisientene opprettet i trinn 2 og x-verdiene i kolonne A. Merk at for å kopiere formelen ned kolonnen må referansene til koeffisientene være absolutte mens referansene til x-verdier må være relative.

   

4. Selv om det ikke er avgjørende, kan du få en visuell indikasjon på hvor god likningen passer ved å plotte begge y-kolonnene mot x-verdiene på et enkelt XY-spredningsdiagram. Det er fornuftig å bruke markører for de opprinnelige datapunktene, siden disse er diskrete verdier med støy, og å bruke en linje for den tilpassede ligningen.

   

5. Deretter trenger vi en måte å kvantifisere forskjellen mellom dataene og den tilpassede ligningen vår. Standardmåten å gjøre dette på er å beregne summen av kvadrerte forskjeller. I en tredje kolonne, for hver rad, trekkes den opprinnelige dataverdien for Y fra den tilpassede ligningsverdien, og resultatet kvadreres. Så, i D2, er verdien gitt av =(C2-B2)^2 Summen av alle disse kvadratiske verdiene beregnes deretter. Siden verdiene er kvadratisk kan de bare være positive.

   

6. Du er nå klar til å utføre optimaliseringen ved hjelp av Solver. Det er fire koeffisienter som må justeres (a, b, c og d). Du har også en enkelt objektiv verdi å minimere, summen av kvadrerte forskjeller. Start løseren, som ovenfor, og still inn løsningsparameterne til å referere til disse verdiene, som vist nedenfor.

   

7. Fjern avmerkingen for å Make Unconstrained Variables Non-negative, dette ville tvinge alle koeffisienter til å ta positive verdier.

   

8. Velg Solve og se gjennom resultatene. Diagrammet vil oppdateres og gir en god indikasjon på god passform. Hvis løseren ikke passer godt ved første forsøk, kan du prøve å kjøre den på nytt. Hvis passformen har blitt bedre, prøv å løse fra gjeldende verdier. Ellers kan du prøve å forbedre tilpasningen manuelt før du løser det.

   

9. Når en god passform er oppnådd, kan du gå ut av løseren.

Løse en modell iterativt

Noen ganger er det en relativt enkel ligning som gir en utgang i form av noen input. Men når vi prøver å snu problemet er det ikke mulig å finne en enkel løsning. For eksempel er kraften som forbrukes av et kjøretøy omtrentlig gitt av P=av + bv^3 der v er hastigheten, a er en koeffisient for rullemotstanden og b er en koeffisient for aerodynamisk luftmotstand. Selv om dette er en ganske enkel ligning, er det ikke lett å omorganisere for å gi en ligning for hastigheten kjøretøyet vil nå for en gitt kraftinngang. Vi kan imidlertid bruke Solver til å iterativt finne denne hastigheten. Finn for eksempel hastigheten oppnådd med en effektinngang på 740 W.

1. Sett opp et enkelt regneark med hastigheten, koeffisientene a og b, og potensen beregnet fra dem.

   

2. Start Solver og angi kraften, B5, som målet. Angi en objektiv verdi på 740 og velg hastigheten, B2, som variabelcellene som skal endres. Velg solve for å starte løsningen.

   

3. Løseren justerer verdien av hastigheten til kraften er veldig nær 740, og gir den hastigheten vi krever.

   

4. Å løse modeller på denne måten kan ofte være raskere og mindre utsatt for feil enn å invertere komplekse modeller.

Det kan være ganske vanskelig å forstå de forskjellige alternativene som er tilgjengelige i løseren. Hvis du har problemer med å få en fornuftig løsning, er det ofte nyttig å bruke grensebetingelser for de utskiftbare cellene. Dette er grenseverdier som de ikke bør justeres utover. For eksempel, i forrige eksempel, skal hastigheten ikke være mindre enn null, og det vil også være mulig å sette en øvre grense. Dette ville være en hastighet du er ganske sikker på at kjøretøyet ikke kan gå raskere enn. Hvis du er i stand til å sette grenser for de utskiftbare variable cellene, får det også andre mer avanserte alternativer til å fungere bedre, for eksempel multistart. Dette vil kjøre en rekke forskjellige løsninger, og starter med forskjellige startverdier for variabler.

Å velge løsningsmetode kan også være vanskelig. Simplex LP er kun egnet for lineære modeller, hvis problemet ikke er lineært vil det mislykkes med en melding om at denne betingelsen ikke ble oppfylt. De to andre metodene er begge egnet for ikke-lineære metoder. GRG ikke-lineær er den raskeste, men løsningen kan være svært avhengig av startforholdene. Den har den fleksibiliteten at den ikke krever at variabler har satt grenser. Den evolusjonære løseren er ofte den mest pålitelige, men den krever at alle variabler har både øvre og nedre grenser, noe som kan være vanskelig å regne ut på forhånd.

Excel Solver-tillegget er et veldig kraftig verktøy som kan brukes på mange praktiske problemer. For å få full tilgang til kraften til Excel, prøv å kombinere Solver med Excel-makroer.