Vad är Excel Solver?

2024 Författare: Abigail Brown | [email protected]. Senast ändrad: 2024-01-07 19:06

Excel Solver-tillägget utför matematisk optimering. Detta används vanligtvis för att anpassa komplexa modeller till data eller hitta iterativa lösningar på problem. Till exempel kanske du vill passa en kurva genom vissa datapunkter med hjälp av en ekvation. Solver kan hitta de konstanter i ekvationen som ger bäst passform till data. En annan applikation är där det är svårt att ordna om en modell för att göra den önskade utdata till föremål för en ekvation.

Var är Solver i Excel?

Solver-tillägget ingår i Excel men det laddas inte alltid som en del av en standardinstallation. För att kontrollera om den är laddad, välj fliken DATA och leta efter Solver-ikonen i avsnittet Analysis.

Om du inte kan hitta Solver under fliken DATA måste du ladda tillägget:

1. Välj fliken FILE och välj sedan Options.

   

2. I dialogrutan Options välj Add-Ins från flikarna på vänster sida.

   

3. Längst ned i fönstret, välj Excel-tillägg från rullgardinsmenyn Manage och välj Go…

   

4. Markera kryssrutan bredvid Solver Add-in och välj OK.

   

5. Kommandot Solver bör nu visas på fliken DATA. Du är redo att använda Solver.

   

Använda Solver i Excel

Låt oss börja med ett enkelt exempel för att förstå vad Solver gör. Föreställ dig att vi vill veta vilken radie som ger en cirkel med arean 50 kvadratenheter. Vi känner till ekvationen för arean av en cirkel (A=pi r²). Vi skulle naturligtvis kunna arrangera om den här ekvationen för att ge den radie som krävs för ett givet område, men låt oss för exempel låtsas att vi inte vet hur man gör det.

Skapa ett kalkylblad med radien i B1 och beräkna arean i B2 med hjälp av ekvationen =pi()B1^2.

Vi kunde justera värdet manuellt i B1 tills B2 visar ett värde som är tillräckligt nära 50. Beroende på hur exakt vi behöver vara, kan detta vara ett praktiskt tillvägagångssätt. Men om vi behöver vara mycket exakta kommer det att ta lång tid att göra de justeringar som krävs. Egentligen är detta i huvudsak vad Solver gör. Den gör justeringar av värden i vissa celler och kontrollerar värdet i en målcell:

1. Välj DATA fliken och Solver för att ladda Solver Parametrar dialogrutan

2. Ange mål cell för att vara området, B2. Detta är värdet som kommer att kontrolleras, och andra celler justeras tills denna når rätt värde.

   

3. Välj knappen för Värde av: och ställ in värdet 50. Detta är värdet som B2 ska uppnå.

   

4. I rutan med titeln Genom att ändra variabelceller: ange cellen som innehåller radien, B1.

   

5. Lämna de andra alternativen som de är som standard och välj Solve. Optimeringen utförs, värdet på B1 justeras tills B2 är 50 och dialogen Solver Results visas.

   

6. Välj OK för att behålla lösningen.

   

Det här enkla exemplet visade hur lösaren fungerar. I det här fallet hade vi lättare kunnat få lösningen på andra sätt. Därefter ska vi titta på några exempel där Solver ger lösningar som skulle vara svåra att hitta på något annat sätt.

Anpassa en komplex modell med Excel Solver-tillägget

Excel har en inbyggd funktion för att utföra linjär regression, genom att passa en rak linje genom en uppsättning data. Många vanliga icke-linjära funktioner kan linjäriseras vilket innebär att linjär regression kan användas för att passa funktioner som exponentialer. För mer komplexa funktioner kan Solver användas för att utföra en "minsta kvadraters minimering". I det här exemplet kommer vi att överväga att anpassa en ekvation med formen ax^b+cx^d till data som visas nedan.

Detta innefattar följande steg:

1. Ordna datamängden med x-värdena i kolumn A och y-värdena i kolumn B.
2. Skapa de 4 koefficientvärdena (a, b, c och d) någonstans på kalkylarket, dessa kan ges godtyckliga startvärden.

3. Skapa en kolumn med anpassade Y-värden med hjälp av en ekvation av formen ax^b+cx^d som refererar till koefficienterna som skapades i steg 2 och x-värdena i kolumn A. Observera att för att kopiera formeln nedåt kolumnen måste referenserna till koefficienterna vara absoluta medan referenserna till x-värden måste vara relativa.

   

4. Även om det inte är nödvändigt, kan du få en visuell indikation på hur bra ekvationen passar genom att plotta båda y-kolumnerna mot x-värdena på ett enda XY-spridningsdiagram. Det är vettigt att använda markörer för de ursprungliga datapunkterna, eftersom dessa är diskreta värden med brus, och att använda en linje för den anpassade ekvationen.

   

5. Nästa, vi behöver ett sätt att kvantifiera skillnaden mellan data och vår anpassade ekvation. Det vanliga sättet att göra detta är att beräkna summan av de kvadratiska skillnaderna. I en tredje kolumn, för varje rad, subtraheras det ursprungliga datavärdet för Y från det anpassade ekvationsvärdet, och resultatet kvadreras. Så, i D2, ges värdet av =(C2-B2)^2 Summan av alla dessa kvadratiska värden beräknas sedan. Eftersom värdena är kvadratiska kan de bara vara positiva.

   

6. Du är nu redo att utföra optimeringen med Solver. Det finns fyra koefficienter som behöver justeras (a, b, c och d). Du har också ett enda objektivt värde att minimera, summan av de kvadratiska skillnaderna. Starta lösaren enligt ovan och ställ in lösarens parametrar så att de refererar till dessa värden, som visas nedan.

   

7. Avmarkera alternativet för att Make Unconstrained Variables Non-negative, detta skulle tvinga alla koefficienter att ta positiva värden.

   

8. Välj Solve och granska resultaten. Diagrammet kommer att uppdateras och ger en god indikation på hur bra passformen är. Om lösaren inte ger en bra passform vid första försöket kan du försöka köra den igen. Om passformen har förbättrats, försök att lösa från de nuvarande värdena. Annars kan du försöka förbättra passformen manuellt innan du löser det.

   

9. När en bra passform har erhållits kan du avsluta lösaren.

Lösa en modell iterativt

Ibland finns det en relativt enkel ekvation som ger en utdata i form av viss input. Men när vi försöker invertera problemet är det inte möjligt att hitta en enkel lösning. Till exempel, den effekt som ett fordon förbrukar ges ungefärligen av P=av + bv^3 där v är hastigheten, a är en koefficient för rullmotståndet och b är en koefficient för aerodynamiskt motstånd. Även om detta är en ganska enkel ekvation, är det inte lätt att arrangera om för att ge en ekvation av hastigheten som fordonet kommer att nå för en given effekt. Vi kan dock använda Solver för att iterativt hitta denna hastighet. Hitta till exempel hastigheten som uppnås med en ineffekt på 740 W.

1. Sätt upp ett enkelt kalkylblad med hastigheten, koefficienterna a och b och effekten beräknad utifrån dem.

   

2. Starta Solver och ange kraften, B5, som målet. Ställ in ett objektivvärde på 740 och välj hastigheten, B2, som de variabelceller som ska ändras. Välj solve för att starta lösningen.

   

3. Lösaren justerar värdet på hastigheten tills effekten är mycket nära 740, vilket ger den hastighet vi kräver.

   

4. Att lösa modeller på detta sätt kan ofta vara snabbare och mindre felbenägen än att invertera komplexa modeller.

Det kan vara ganska svårt att förstå de olika alternativen som finns i lösaren. Om du har svårt att få en vettig lösning är det ofta användbart att tillämpa gränsvillkor för de föränderliga cellerna. Dessa är gränsvärden utöver vilka de inte bör justeras. Till exempel, i föregående exempel, bör hastigheten inte vara mindre än noll och det skulle också vara möjligt att sätta en övre gräns. Detta skulle vara en hastighet som du är ganska säker på att fordonet inte kan köra snabbare än. Om du kan sätta gränser för de föränderliga variabla cellerna, gör det också att andra mer avancerade alternativ fungerar bättre, som multistart. Detta kommer att köra ett antal olika lösningar, med början på olika initiala värden för variabler.

Att välja lösningsmetod kan också vara svårt. Simplex LP är endast lämplig för linjära modeller, om problemet inte är linjärt kommer det att misslyckas med ett meddelande om att detta villkor inte uppfylldes. De andra två metoderna är båda lämpade för icke-linjära metoder. GRG Nonlinear är den snabbaste men dess lösning kan vara mycket beroende av de initiala startförhållandena. Den har den flexibiliteten att den inte kräver att variabler har gränser. Den evolutionära lösaren är ofta den mest tillförlitliga men den kräver att alla variabler har både övre och nedre gränser, vilket kan vara svårt att räkna ut i förväg.

Excel Solver-tillägget är ett mycket kraftfullt verktyg som kan användas på många praktiska problem. För att få full tillgång till kraften i Excel, prova att kombinera Solver med Excel-makron.