Was ist Excel Solver?

2024 Autor: Abigail Brown | [email protected]. Zuletzt bearbeitet: 2023-12-17 06:43

Das Excel Solver-Add-In führt mathematische Optimierungen durch. Dies wird normalerweise verwendet, um komplexe Modelle an Daten anzupassen oder iterative Lösungen für Probleme zu finden. Beispielsweise möchten Sie möglicherweise eine Kurve mithilfe einer Gleichung durch einige Datenpunkte anpassen. Solver kann die Konstanten in der Gleichung finden, die den Daten am besten entsprechen. Eine andere Anwendung ist, wenn es schwierig ist, ein Modell neu anzuordnen, um die erforderliche Ausgabe zum Gegenstand einer Gleichung zu machen.

Wo ist Solver in Excel?

Das Solver-Add-In ist in Excel enth alten, wird aber nicht immer als Teil einer Standardinstallation geladen. Um zu überprüfen, ob es geladen ist, wählen Sie die Registerkarte DATA und suchen Sie nach dem Symbol Solver im Abschnitt Analysis.

Wenn Sie Solver nicht auf der Registerkarte DATEN finden können, müssen Sie das Add-In laden:

1. Wählen Sie die Registerkarte DATEI und wählen Sie dann Optionen.

   

2. Im Dialogfeld Optionen wählen Sie Add-Ins aus den Registerkarten auf der linken Seite aus.

   

3. Wählen Sie unten im Fenster Excel-Add-Ins aus der Dropdown-Liste Verw alten und wählen Sie Los…

   

4. Aktivieren Sie das Kontrollkästchen neben Solver Add-in und wählen Sie OK.

   

5. Der Befehl Solver sollte jetzt auf der Registerkarte DATEN erscheinen. Sie können Solver jetzt verwenden.

   

Solver in Excel verwenden

Beginnen wir mit einem einfachen Beispiel, um zu verstehen, was der Solver macht. Stellen Sie sich vor, wir möchten wissen, welchen Radius ein Kreis mit einer Fläche von 50 Quadrateinheiten ergibt. Wir kennen die Kreisflächengleichung (A=pi r²). Wir könnten diese Gleichung natürlich umstellen, um den für eine gegebene Fläche erforderlichen Radius zu erh alten, aber zum Beispiel nehmen wir an, wir wüssten nicht, wie das geht.

Erstelle eine Tabelle mit dem Radius in B1 und berechne die Fläche in B2 mit der Gleichung =pi()B1^2.

Wir könnten den Wert in B1 manuell anpassen, bis B2 einen Wert anzeigt, der nahe genug an 50 liegt. Je nachdem, wie genau wir sein müssen, könnte dies ein praktischer Ansatz sein. Wenn wir jedoch sehr genau sein müssen, wird es lange dauern, die erforderlichen Anpassungen vorzunehmen. Eigentlich ist dies im Wesentlichen das, was Solver tut. Es nimmt Anpassungen an Werten in bestimmten Zellen vor und überprüft den Wert in einer Zielzelle:

1. Wählen Sie die Registerkarte DATA und Solver, um das Dialogfeld Solver-Parameter zu laden

2. Set Objective cell to be the Area, B2. Dies ist der Wert, der überprüft wird, wobei andere Zellen angepasst werden, bis dieser den richtigen Wert erreicht.

   

3. Wählen Sie die Sch altfläche für Wert von: und stellen Sie einen Wert von 50 ein. Dies ist der Wert, den B2 erreichen soll.

   

4. Im Feld mit dem Titel Durch Ändern variabler Zellen: geben Sie die Zelle ein, die den Radius enthält, B1.

   

5. Lassen Sie die anderen Optionen unverändert und wählen Sie Lösen. Die Optimierung wird durchgeführt, der Wert von B1 wird angepasst, bis B2 50 beträgt, und der Dialog Solver-Ergebnisse wird angezeigt.

   

6. Wählen Sie OK, um die Lösung beizubeh alten.

   

Dieses einfache Beispiel zeigt, wie der Solver funktioniert. In diesem Fall hätten wir die Lösung auf andere Weise leichter finden können. Als nächstes sehen wir uns einige Beispiele an, bei denen Solver Lösungen liefert, die auf andere Weise schwer zu finden wären.

Anpassen eines komplexen Modells mit dem Excel Solver Add-In

Excel verfügt über eine integrierte Funktion zur Durchführung einer linearen Regression, bei der eine gerade Linie durch einen Datensatz passt. Viele gängige nichtlineare Funktionen können linearisiert werden, was bedeutet, dass die lineare Regression verwendet werden kann, um Funktionen wie Exponentialfunktionen anzupassen. Für komplexere Funktionen kann der Solver verwendet werden, um eine „Kleinste-Quadrate-Minimierung“durchzuführen. In diesem Beispiel betrachten wir die Anpassung einer Gleichung der Form ax^b+cx^d an die unten gezeigten Daten.

Dies umfasst die folgenden Schritte:

1. Ordnen Sie den Datensatz mit den x-Werten in Sp alte A und den y-Werten in Sp alte B.
2. Erstellen Sie die 4 Koeffizientenwerte (a, b, c und d) irgendwo in der Tabelle, diesen können beliebige Startwerte gegeben werden.

3. Erstellen Sie eine Sp alte mit angepassten Y-Werten, indem Sie eine Gleichung der Form ax^b+cx^d verwenden, die auf die in Schritt 2 erstellten Koeffizienten und die x-Werte in Sp alte A verweist. Beachten Sie, dass Sie die Formel nach unten kopieren müssen der Sp alte, müssen die Verweise auf die Koeffizienten absolut sein, während die Verweise auf x-Werte relativ sein müssen.

   

4. Obwohl dies nicht unbedingt erforderlich ist, können Sie einen visuellen Hinweis darauf erh alten, wie gut die Gleichung passt, indem Sie beide y-Sp alten gegen die x-Werte in einem einzelnen XY-Streudiagramm darstellen. Es ist sinnvoll, Markierungen für die ursprünglichen Datenpunkte zu verwenden, da es sich um diskrete Werte mit Rauschen handelt, und eine Linie für die angepasste Gleichung zu verwenden.

   

5. Als nächstes brauchen wir eine Möglichkeit, die Differenz zwischen den Daten und unserer angepassten Gleichung zu quantifizieren. Die Standardmethode hierfür ist die Berechnung der Summe der quadrierten Differenzen. In einer dritten Sp alte wird für jede Zeile der ursprüngliche Datenwert für Y vom angepassten Gleichungswert subtrahiert und das Ergebnis quadriert. In D2 ist der Wert also gegeben durch =(C2-B2)^2 Die Summe all dieser quadrierten Werte wird dann berechnet. Da die Werte quadriert sind, können sie nur positiv sein.

   

6. Sie können jetzt die Optimierung mit Solver durchführen. Es gibt vier Koeffizienten, die angepasst werden müssen (a, b, c und d). Sie müssen auch einen einzigen objektiven Wert minimieren, die Summe der quadrierten Differenzen. Starten Sie den Solver wie oben und stellen Sie die Solver-Parameter so ein, dass sie auf diese Werte verweisen, wie unten gezeigt.

   

7. Deaktivieren Sie die Option Unbeschränkte Variablen nicht negativ machen, dies würde alle Koeffizienten dazu zwingen, positive Werte anzunehmen.

   

8. Wählen Sie Lösen und überprüfen Sie die Ergebnisse. Das Diagramm wird aktualisiert und gibt einen guten Hinweis auf die Anpassungsgüte. Wenn der Solver beim ersten Versuch keine gute Anpassung liefert, können Sie versuchen, ihn erneut auszuführen. Wenn sich die Anpassung verbessert hat, versuchen Sie es mit den aktuellen Werten. Andernfalls könnten Sie versuchen, die Anpassung manuell zu verbessern, bevor Sie eine Lösung finden.

   

9. Sobald eine gute Anpassung erreicht wurde, können Sie den Solver verlassen.

Modell iterativ lösen

Manchmal gibt es eine relativ einfache Gleichung, die eine Ausgabe in Bezug auf eine Eingabe ergibt. Wenn wir jedoch versuchen, das Problem umzukehren, ist es nicht möglich, eine einfache Lösung zu finden. Beispielsweise ist die von einem Fahrzeug verbrauchte Leistung ungefähr gegeben durch P=av + bv^3 wobei v die Geschwindigkeit, a ein Koeffizient für den Rollwiderstand und b ein Koeffizient für ist Aerodynamischer Widerstand. Obwohl dies eine recht einfache Gleichung ist, lässt sie sich nicht leicht umstellen, um eine Gleichung der Geschwindigkeit zu erh alten, die das Fahrzeug bei einer gegebenen Leistungsaufnahme erreichen wird. Wir können Solver jedoch verwenden, um diese Geschwindigkeit iterativ zu finden. Finden Sie beispielsweise die Geschwindigkeit heraus, die mit einer Leistungsaufnahme von 740 W erreicht wird.

1. Erstellen Sie eine einfache Tabelle mit der Geschwindigkeit, den Koeffizienten a und b und der daraus berechneten Leistung.

   

2. Starte den Solver und gib die Potenz B5 als Ziel ein. Legen Sie einen Zielwert von 740 fest und wählen Sie die Geschwindigkeit, B2, als zu ändernde Variablenzellen aus. Wählen Sie solve, um die Lösung zu starten.

   

3. Der Solver passt den Wert der Geschwindigkeit an, bis die Potenz sehr nahe bei 740 liegt, was die erforderliche Geschwindigkeit liefert.

   

4. Das Lösen von Modellen auf diese Weise kann oft schneller und weniger fehleranfällig sein als das Invertieren komplexer Modelle.

Die verschiedenen im Solver verfügbaren Optionen zu verstehen, kann ziemlich schwierig sein. Wenn Sie Schwierigkeiten haben, eine vernünftige Lösung zu finden, ist es oft hilfreich, Randbedingungen auf die veränderbaren Zellen anzuwenden. Dies sind Grenzwerte, über die hinaus sie nicht verstellt werden sollten. Beispielsweise sollte im vorherigen Beispiel die Geschwindigkeit nicht kleiner als Null sein und es wäre auch möglich, eine Obergrenze festzulegen. Dies wäre eine Geschwindigkeit, bei der Sie sich ziemlich sicher sind, dass das Fahrzeug nicht schneller fahren kann. Wenn Sie in der Lage sind, Grenzen für die veränderbaren Variablenzellen festzulegen, werden auch andere erweiterte Optionen wie Multistart besser funktionieren. Dies führt zu einer Reihe verschiedener Lösungen, beginnend mit unterschiedlichen Anfangswerten für Variablen.

Die Wahl der Lösungsmethode kann auch schwierig sein. Simplex LP ist nur für lineare Modelle geeignet, wenn das Problem nicht linear ist, schlägt es mit einer Meldung fehl, dass diese Bedingung nicht erfüllt wurde. Die beiden anderen Verfahren sind beide für nichtlineare Verfahren geeignet. GRG Nonlinear ist am schnellsten, aber seine Lösung kann stark von den anfänglichen Startbedingungen abhängen. Es hat die Flexibilität, dass für Variablen keine Grenzen gesetzt werden müssen. Der evolutionäre Löser ist oft der zuverlässigste, aber er erfordert, dass alle Variablen sowohl obere als auch untere Grenzen haben, was im Voraus schwierig zu berechnen sein kann.

Das Excel Solver-Add-In ist ein sehr leistungsfähiges Tool, das auf viele praktische Probleme angewendet werden kann. Um die Leistungsfähigkeit von Excel voll auszuschöpfen, versuchen Sie, Solver mit Excel-Makros zu kombinieren.