Die Lösungsmethoden des Excel-Solver

Der Solver ist ein Add-In-Programm für Microsoft Excel. Du kannst ihn über das Menü Daten / Analyse aufrufen.

Sollte der Solver nicht verfügbar sein, musst Du ihn zunächst laden, wie, erfährst Du über diesen Link.

Laden des Solver-Add-Ins – Excel (microsoft.com)

Wichtig ist, dass sich die Berechnungen des Solver auf nur eine Zielzelle beziehen. Diese muss eine Formel enthalten, die mit Vorgängerwerten ein gewünschtes Ergebnis errechnet.

Dies geschieht, indem ein exakter Wert oder ein Minimum oder ein Maximum als Ziel definiert wird. Der Solver arbeitet mit einer Gruppe von Zellen, die Du als „veränderbare Zellen“ bestimmen musst.

Die Berechnungen können Nebenbedingungen oder Einschränkungen unterliegen, die ebenfalls vorher festzulegen sind.

Letztlich passt der Solver darauf aufbauend die Werte in den veränderbaren Zellen so an, dass das Ziel erreicht und die Nebenbedingungen eingehalten werden.

Mit welchen Methoden der Solver im Einzelnen arbeitet, zeigt dieser Blogbeitrag.

1. Definieren und Lösen eines linearen Problems mit „Simplex-LP“

Bevor Du den Solver aufrufst, benötigst Du eine Kalkulationstabelle.

Angenommen, es geht um ein Produktionsunternehmen, dass drei Produkte fertigt. Nachfolgend sind die geplanten Mengen, variable und fixe Kosten sowie die Einzelpreise im Verkauf dargestellt. In Zelle I7 wird letztlich ein Gewinn ausgewiesen.

Zelle I7 soll die Zielzelle für den Solver werden. Du strebst, natürlich, einen maximalen Gewinn an.

Veränderbar sind die Mengenangaben in C4:C6. Für Produkt C besteht aufgrund eines Vertrages allerdings die Pflicht, mindestens 1800 Stück zu produzieren. Weiterhin können die Produkte A und B nicht unbegrenzt produziert werden. Für Produkt A liegt die Kapazitätsgrenze bei 2800 Stück, für Produkt B bei 4000 Stück. Diese Einschränkungen musst Du daher als Nebenbedingungen definieren.

Aktiviere Zelle I7 und rufe nun den Solver auf.

Trage unter „Ziel festlegen“ „$I$7“ ein.

Klicke „Max.“ an.

Trage als „Veränderbare Zellen“ nun „$C$4:$C$6“ ein.

Jetzt sind noch die Nebenbedingungen zu definieren. Klicke auf „Hinzufügen“.

Trage hier den Zellbezug (Menge von Produkt C), >= und als Wert ein. Klicke OK.

Für die Zellen C4 und C5 fügst Du die Bedingungen auf die gleiche Weise hinzu.

Bleibt noch, die Lösungsmethode zu bestimmen. Da es sich um ein lineares Problem handelt (der Gewinn je Produkt und damit der Gesamtgewinn sind nur abhängig von der Menge), wählst Du „Simplex-LP“.

Nun hast Du alle Eingaben vorgenommen und kannst auf „Lösen“ klicken.

Aber, es gibt noch ein paar Optionen (Schaltfläche „Optionen“), die es Dir ermöglichen, noch weitere Argumente vor der Lösung der Aufgabe anzugeben. Erläuterungen zu den Optionen findest Du auf einer Site von docs.microsoft.com. [2]

Für das dargestellte Problem wurden keine optionalen Einstellungen vorgenommen.

Der Solver hat evtl. eine mögliche Lösung gefunden und zeigt es Dir an.

Akzeptiere die Lösung oder lass die ursprünglichen Werte wiederherstellen. Klicke OK.

Hast Du die Lösung akzeptiert, sieht die Tabelle nun so aus:

Die Mengen von A und B wurden auf die Kapazitätsgrenze angehoben, die von C bleibt auf dem Vertragswert. Der Gewinn hat sich durch die Optimierung von 42.300 auf 53.820 erhöht.

2. Definieren und Lösen eines nichtlinearen Problems mit „GRG-Nichtlinear“

Im Beispiel soll es um die Herstellung eines Zylinders gehen, dass an einer Seite offen, an der anderen Seite geschlossen ist. Bei einem gewünschten Volumen von 800 geht es darum, den Radius und die Höhe so zu optimieren, dass für den Zylindermantel so wenig Material verwendet werden muss wie möglich.

Dazu benötigst Du wieder eine Kalkulationstabelle, die etwa so aussehen kann:

Dargestellt sind die Kennziffern für einen Zylinder, Radius und Höhe enthalten noch keinen Wert, die Kennziffern stehen deshalb auf 0. Die Mantelfläche soll nun für den Zylinder berechnet werden, der eine veränderbare Höhe >=10 und einen veränderbaren Radius >=5 hat.

Die Zielzelle für den Solver ist E13. Hier wird ein Minimum angestrebt.

Veränderbar sind der Radius in Zelle E3 und die Höhe in Zelle E5.

Die Nebenbedingungen besagen, dass die Höhe in E5 >=10 und der Radius >=5 sein soll. Das Volumen soll maximal 800 betragen.

Eine Veränderung von Radius und Höhe bewirken sowohl eine Änderung des Volumens als auch eine Änderung der Mantelfläche.

Da es sich um ein nichtlineares Problem handelt (bedingt durch die Kreisformeln), wählst Du „GRG-Nichtlinear“ (GRG steht für “Generalized Reduced Gradient“).

Auch hier gibt noch ein paar Optionen (Schaltfläche „Optionen“), die es Dir ermöglichen, noch weitere Argumente vor der Lösung der Aufgabe anzugeben. Erläuterungen zu den Optionen findest Du auf einer Site von docs.microsoft.com. [2]

Auch für dieses Problem wurden keine optionalen Einstellungen vorgenommen.

Klicke nun auf „Lösen“.

Der Solver findet in diesem Beispiel eine Lösung. Wenn Du sie akzeptierst, sieht Deine Tabelle nun so aus:

Der Radius hat sich auf 5,044, die Höhe auf 10,011 eingestellt. Das Volumen beträgt genau 800. Die Nebenbedingungen wurden damit erfüllt.

Die Mantelfläche ist letztlich das Resultat der Formeln und steht bei den gegebenen Nebenbedingungen auf einem Minimum.

3. Definieren und Lösen eines naturanalogen Problems mit dem „Evolutionärer Algorithmus (EA)“

Was sind evolutionäre Algorithmen? Bei Wikipedia ist dazu zu lesen:

„Evolutionäre Algorithmen (EA) sind eine Klasse von stochastischen, metaheuristischen Optimierungsverfahren, deren Funktionsweise von der Evolution natürlicher Lebewesen inspiriert ist.

In Anlehnung an die Natur werden Lösungskandidaten für ein bestimmtes Problem künstlich evolviert, EA sind also naturanaloge Optimierungsverfahren. Die Zuordnung zu den stochastischen und metaheuristischen Algorithmen bedeutet vor allem, dass EA meist nicht die beste Lösung für ein Problem finden, aber bei Erfolg eine hinreichend gute, was in der Praxis vor allem bei NP-vollständigen Problemen bereits wünschenswert ist.“ [1]

Die Anwendung dieser Lösungsmethode wurde Dir erst kürzlich im Beitrag „Das Rundreiseproblem mit dem Excel-Solver lösen“ vorgestellt. Es ging dabei um das „Travelling Salesman-Problem“, in Deutsch kurz das Rundreiseproblem genannt. Die Aufgabe war, die kürzeste Route für 10 zu besuchende Orte zu finden. Ausgangs- und Zielort waren identisch und es durfte jeder Ort während der Rundreise nur einmal aufgesucht werden.

Ausgangspunkt war die nachfolgend dargestellte Strecke mit insgesamt 205,67 km.

Die Einstellungen im Solver waren:

Zielzelle                      D28 = Min.

Veränderbar               C17:C26

Nebenbedingungen    C17:C26=AllDifferent

                                    C17:C26=Ganzzahlig

Lösungsmethode        „EA (Evolutionärer Algorithmus)“

Hier gibt es ebenfalls ein paar Optionen (Schaltfläche „Optionen“), die es Dir ermöglichen, noch weitere Argumente vor der Lösung der Aufgabe anzugeben. Erläuterungen zu den Optionen findest Du auf einer Site von docs.microsoft.com. [2]

Für dieses Problem wurden die vorgeschlagenen optionalen Einstellungen behalten.

Nach dem Klicken auf „Lösen“ war dies das optimale Ergebnis:

Die Fahrstrecke hatte sich damit um fast 83 km verringert und damit die eingangs aus Wikipedia zitierte Aussage

„dass EA meist nicht die beste Lösung für ein Problem finden, aber bei Erfolg eine hinreichend gute, was in der Praxis vor allem bei NP-vollständigen Problemen bereits wünschenswert ist.“

bestätigt.

4. Fazit

Mit dem Solver lassen sich eine Reihe von Optimierungsaufgaben lösen. In diesem Beitrag konnten nur ein paar Beispiele vorgestellt werden.

Im Blog findest Du weitere Artikel zur Nutzung des Solver, die Dich vielleicht interessieren könnten:

„So findest du mit dem Solver eine optimale Lösung“ (Transportoptimierung)

„Mit dem Solver die Transportleistung optimieren“ (Transportoptimierung)

„Optimale Programmplanung mit dem Solver in Excel“ (Produktionsprogrammoptimierung)

„Den kürzesten Weg mit dem Excel-Solver finden“ (Fahrstreckenoptimierung)

„Das Rundreiseproblem mit dem Excel-Solver lösen“ (Fahrstreckenoptimierung)

Welche Erfahrungen hast Du mit dem Solver gemacht? Nutzt Du ihn überhaupt?

Ein Kommentar von Dir wäre gut.

Quellen:

[1] Wikipedia

[2] Microsoft