Excel-Löser: Optimiere komplexe Entscheidungen mit mehreren Variablen

Mit der Was-wäre-wenn-Analyse kannst du verschiedene Szenarien ausprobieren, um zu sehen, wie sie sich auf deine Ergebnisse auswirken. Es gibt dir einen sicheren Ort, um mit Sachen wie Kosten oder Kapazitäten rumzuexperimentieren und die beste Lösung zu finden. Dieser Ansatz ist super wichtig für alle, die Ressourcen verwalten, Zeitpläne erstellen oder strategische Geschäftsentscheidungen treffen.

Manuelles Ausprobieren dauert ewig und oft findet man nicht die beste Lösung. Der Solver sorgt in Sekundenschnelle für mathematische Optimierung. Das ist der Unterschied zwischen dem Raten bei der Ressourcenzuteilung und dem Wissen, dass man die bestmögliche Entscheidung trifft.

Dieses Tutorial zeigt dir, wie du Excel Solver benutzt, die coole Funktion für solche Optimierungsaufgaben. Ich zeige dir anhand von Beispielen aus der Praxis, wie du deine Tabelle einrichtest, die Analyse durchführst und die Ergebnisse interpretierst.

Excel Solver verstehen: Grundlegende Konzepte

Bevor ich dir zeige, wie man Solver benutzt, möchte ich dir die drei Bausteine jedes Optimierungsproblems erklären. Das sind nicht nur Schlagworte, sondern genau das, was Solver so gut macht.

Zielzelle, Entscheidungsvariablen und Einschränkungen

Stell dir die objektive Zell e als dein Ziel vor . Das ist die Formel, die du maximieren oder minimieren willst, wie zum Beispiel den Gesamtgewinn oder die Gesamtkosten. Das muss eine Formel sein, nicht nur eine Zahl, weil Solver sehen muss, wie sie sich ändert, wenn du Sachen anpasst.

Entscheidungsvariablen sind das, was du herausfinden willst. Wie viele Stühle sollen wir machen? Wie viel sollten wir für jeden Marketingkanal ausgeben? Ich fang immer bei Null an, damit man nach dem Ausführen von Solver leicht sehen kann, was sich geändert hat.

Einschränkungen sind deine Grenzen und Regeln. Du kannst nicht mehr ausgeben, als du dir vorgenommen hast. Du kannst keine negativen Mengen produzieren. Manche Projekte brauchen eine Mindestinvestition. Diese Einschränkungen machen dein Matheproblem zu etwas Realistischem, das die tatsächlichen geschäftlichen Grenzen widerspiegelt.

So geht's: Der Solver passt deine Entscheidungsvariablen an, um dein Ziel zu optimieren, und sorgt gleichzeitig dafür, dass alle Einschränkungen erfüllt bleiben. Wenn du dich mit Operations Research beschäftigt hast, kommt dir das bestimmt bekannt vor. Wenn nicht, sieh es einfach als cleveres Ausprobieren mit Garantien an.

Vergleich mit der Zielwertsuche

Ich werde oft danach gefragt. Die Zielwertsuche ist einfacher. Es verändert eine Zelle, um ein bestimmtes Ziel zu treffen. Benutz es, wenn du die Antwort schon kennst und nur noch die Eingabe finden musst, die dich dahin bringt.

Solver ist anders. Es findet die beste Lösung, wenn du mehrere Entscheidungen treffen musst und bestimmte Einschränkungen beachten solltest. IchSolver wird benutzt, wenn, wenn du etwas maximieren oder minimieren musst, während du mit mehreren Variablen jonglierst.

Meine Faustregel: Wenn du genau sagen kannst, welche Zahl du willst, dann nimm die Zielwertsuche. Wenn du dich fragst, wie du das am besten machst, dann nimm Solver.

Laden und Aktivieren des Solver-Add-Ins

Solver ist in Excel enthalten, muss aber erst aktiviert werden. Keine Sorge, es dauert weniger als eine Minute.

Installationsschritte für Windows und Mac

Für Windows:

1. Datei > Optionen > Add-Ins
2. Wähle „Excel-Add-Ins “ aus dem Dropdown-Menü „Verwalten“ und klick auf „Los“.
3. Check Solver-Add-In, klick auf OK
4. Geh zu den Registerkarte „Daten“ und such den „Solver“ unter Gruppe „Analysieren“ .

Für Mac:

1. Werkzeuge > Excel Add-Ins
2. Check Solver-Add-In, klick auf OK

Wenn du Excel Online benutzt, hab ich leider schlechte Nachrichten. Es unterstützt Solver nicht. Du brauchst die Desktop-Version (2016 oder neuer).

Konfiguration

Sobald es aktiviert ist, kannst du über die Registerkarte „Daten“ > Schaltfläche „Löser“ auf den Löser zugreifen. Die Standardeinstellungen funktionieren bei den meisten Problemen super, also mach dir darüber nicht zu viele Gedanken.

Wenn du Probleme hast, sind zwei Einstellungen wichtig. Die Präzisions -Einstellung bestimmt, wie streng Solver die Einschränkungen einhält. Ich ziehe das bei Finanzmodellen fest, wo jeder Cent zählt. Die Konvergenz e sagt dem Solver, wann er aufhören soll, die Lösung zu verbessern. Ich senke das, wenn Solver zu früh mit einer nicht ganz optimalen Antwort aufhört.

Du findest beides im Dialogfeld „Solver-Parameter“ > Schaltfläche „Optionen“, aber ehrlich gesagt, verändere ich die Einstellungen dort echt selten.

Dein Problem beschreiben: Modellformulierung in Excel

Eine übersichtliche Tabelle macht die Solver-Einstellungen echt einfach.

Lass mich dir ein Beispiel geben: ein Möbelhersteller, der Stühle und Tische macht. Stühle bringen 60 Dollar Gewinn (benötigt werden 4 Arbeitsstunden und 2 Holzeinheiten). Tabellen bringen 75 Dollar Gewinn (benötigt werden 6 Arbeitsstunden und 2 Holzeinheiten). Wir haben 240 Arbeitsstunden und 100 Holzeinheiten zur Verfügung. Wie viele von jedem sollten wir machen?

Problemdefinition und Tabellenkalkulation einrichten

Ich mache jedes Solver-Modell auf die gleiche Weise. Immer vier Abschnitte:

1. Eingabedaten (Produktinfos, Ressourcenbeschränkungen)
2. Entscheidungsvariablen (zu produzierende Mengen)
3. Zielfunktion (Gesamtgewinn)
4. Ressourcenbeschränkungen berechnen (verwendete vs. verfügbare Ressourcen)

Vollständiges Solver-Modell mit allen vier Abschnitten. Bild vom Autor.

Dieses Layout macht alles klar. Jeder, der sich dein Modell anschaut, kann sofort erkennen, was du optimierst und mit welchen Einschränkungen du arbeitest.

Jetzt mache ich immer sogenannte benannte Bereiche für wichtige Zellen. Anstatt „ =B8*B3+B9*B4 “ zu schreiben, kann ich „ =SUMPRODUCT(QuantityToProduce,ProfitPerUnit) “ schreiben . Sechs Monate später ist das viel einfacher zu verstehen, wenn du vergessen hast, was diese Zellbezüge bedeuten.

Benannte Bereiche machen Formeln übersichtlicher. Bild vom Autor.

So machst du das: Markier die Zellen, klick auf das Namensfeld, tipp einen Namen ein und drück die Eingabetaste. Dauert nur zwei Sekunden und erspart dir später Kopfzerbrechen.

Entscheidungsvariablen und Zielfunktion

Ich setze die Entscheidungsvariablen auf Null. Das hilft beim Vergleichen. Manche Leute fangen gerne mit geschätzten Werten an, was okay ist, aber für mich klappt Null am besten.

Die Zielfunktion muss eine Formel sein. Für unser Möbelbeispiel nehme ich:

=SUMPRODUCT(B8:B9,B3:B4)

Das berechnet: (Stühle × 60 $) + (Tabellen × 75 $)

Einfache Multiplikation und Addition. Das ist alles, was du für die meisten geschäftlichen Probleme brauchst.

Einschränkungen

Einschränkungen sind, wo du echte Geschäftsbeschränkungen in Mathe umsetzt. Unser Möbelhersteller hat zwei Einschränkungen bei den Ressourcen:

• Arbeit: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Holz: =SUMPRODUCT(B8:B9,D3:D4) <= 100

Und hier ist was, das ich auf die harte Tour gelernt habe: Immer Nicht-Negativitätsbeschränkungen hinzufügen (B8:B9 >= 0). Ohne sie könnte Solver vorschlagen, negative Stühle zu machen, was in der echten Welt natürlich nicht klappt.

Ich füge auch eine Statusspalte mit „ =IF() “-Formeln hinzu, wie zum Beispiel „ “ =IF(B16<=C16,"OK","EXCEEDED") . So kriege ich sofort ein visuelles Feedback, noch bevor ich den Solver starte. Grüne Lichter bedeuten „Los“, rote bedeuten, dass mit deiner Einrichtung was nicht stimmt.

Schritt für Schritt: Wie man den Solver in Excel benutzt

Okay, das Modell ist fertig. Jetzt zeige ich dir, wie du Solver tatsächlich ausführst. Dieser Schritt dauert vielleicht 30 Sekunden, wenn du weißt, was du machst.

Benutzung des Solver-Parameter-Dialogs

Öffne den Solver, indem du auf die Registerkarte „Daten“ klickst und dann auf die Schaltfläche „Solver“ drückst.

Lösungsdialog vor der Konfiguration. Bild vom Autor.

Hier ist, was du ausfüllst:

Ziel festlegen: Klick auf dieses Feld und dann auf die Zelle„ “ B12 (unsere Zelle für den Gesamtgewinn).

To: Wähle„ “ (Maximale Gewinnoptimierung ) aus,weil wir den Gewinn maximieren wollen.

Durch Ändern variabler Zellen: Wähle„ “ B8:B9 aus (die Mengen, die wir festlegen).

Unter Berücksichtigung der Einschränkungen: Klick auf„ “ Füge für jede Einschränkung Folgendeshinzu :

• Arbeit: $B$16 <= $C$16

• Holz: $B$17 <= $C$17

• Nicht-Negativität: $B$8:$B$9 >= 0

Wähle eine Lösungsmethode aus: Wähle„ “ Simplex LP. Die anderen Methoden erkläre ich später, aber für lineare Probleme wie dieses ist Simplex LP genau das Richtige.

Löser ist eingerichtet und kann loslegen. Bild vom Autor.

Lösen des Modells

Bevor du auf „Lösen“ klickst, schau dir kurz deine aktuellen Werte an. Alles sollte auf Null stehen.

Anfangszustand mit überall Nullen. Bild vom Autor.

Klick jetzt auf „ “lösen. Bei einfachen Problemen wie unseren dauert das ein paar Sekunden.

Geschafft! Der Solver hat die beste Lösung gefunden. Bild vom Autor.

Wenn das Dialogfeld „Solver-Ergebnisse“ aufpoppt, sollte da stehen: „Solver hat eine Lösung gefunden.“ Klicke unter „Berichte“ die Kästchen für„ -Antwort“ und „Sensitivitäts “ an. Die liefern dir detaillierte Analysen, die wir uns später anschauen werden. Dann klick auf OK.

Die beste Lösung: 30 Stühle und 20 Tabellen. Bild vom Autor.

Und das war's auch schon. Die Lösung zeigt, dass wir 30 Stühle und 20 Tabellen machen sollten, um 3.300 Dollar Gewinn zu machen. Beachte, dass beide Einschränkungen „OK“ anzeigen, was bedeutet, dass wir unsere Ressourcen effizient nutzen, ohne die Grenzen zu überschreiten.

Zusammenfassung des Arbeitsablaufs

Hier ist der Ablauf, den ich jedes Mal mache:

1. Erstelle das Modell (Eingaben, Variablen, Ziel, Einschränkungen).
2. Solver einrichten (festlegen, was optimiert und was angepasst werden soll)
3. Klick auf „Lösen“ und warte
4. Schau mal, ob die Lösung geschäftlich Sinn macht.
5. Schau dir die Berichte an, um mehr zu erfahren.

Dieser letzte Schritt ist wichtig. Nur weil Solver eine Antwort gefunden hat, heißt das nicht, dass es die richtige Antwort für dein Unternehmen ist. Überprüfe die Ergebnisse immer auf ihre Plausibilität.

Lösungsmethoden, Parameter und erweiterte Optionen

Solver bietet dir drei verschiedene Algorithmen. Die richtige Wahl macht einen großen Unterschied in Sachen Geschwindigkeit und Genauigkeit.

Überblick über Lösungsmethoden

Drei Lösungsmethoden für verschiedene Problemtypen. Bild vom Autor.

Simplex LP

Simplex LP benutze ich in 80 % der Fälle. Es geht um lineare Probleme, bei denen es nur um Addition und Multiplikation geht. Produktmix? Wie sieht's mit der Budgetverteilung aus? Ressourcenplanung? Simplex LP. Es geht schnell und du bekommst garantiert die beste Antwort.

GRG Nichtlinear

GRG Nonlinear ist genau das Richtige, wenn deine Formeln Kurven, Quadrate oder Exponentialfunktionen haben. Stell dir Preismodelle vor, bei denen die Nachfrage mit steigendem Preis sinkt, aber nicht linear. Oder Portfoliooptimierung mit Varianzberechnungen. Es findet lokale Optima, die vielleicht nicht die absolut besten sind, aber meistens gut genug.

Evolutionär

Evolutionär ist die Brute-Force-Option. Benutz das, wenn du ganzzahlige Einschränkungen (nur ganze Einheiten möglich), binäre Entscheidungen (ja oder nein) oder „ =IF() “-Anweisungen in deinen Formeln hast. Es ist langsamer, weil es im Grunde genommen eine Reihe von zufälligen Lösungen ausprobiert und die guten davon behält. Das „” funktioniert, wenn die anderen Methoden nicht klappen.

Meine Entscheidungsregel ist einfach: Fang mit Simplex LP an. Wenn deine Formeln Exponenten oder Produkte von Variablen haben, wechsel einfach zu GRG Nonlinear. Wenn du ganze Zahlen oder binäre Variablen brauchst, dann nimm Evolutionary.

Lösungsoptionen und Einstellungen

Klick im Solver-Dialogfeld auf„Optionen“ und dann auf „ “, um die erweiterten Einstellungen zu sehen. Ich beschäftige mich selten damit, aber hier ist, was wichtig ist:

Begrenze die Laufzeit von Solver mit der Option „Max Time“ (Maximale Zeit). Die Standardeinstellung ist 100 Sekunden, was für die meisten Probleme völlig ausreicht. Ich mach das bei richtig großen Modellen, die mehr Zeit zum Nachdenken brauchen.

Begrenze die Anzahl der Versuche, die Solver mit Iterationenmacht, auf . Wenn du an diese Grenze stößt, ohne eine Lösung zu finden, versuch's einfach noch mal.

Lass uns mal schauen, wie genau Solver die Einschränkungen mit der Option„Precision“handhabt. . Ich ziehe das bei Finanzmodellen fest, wo jeder Cent zählt, aber sonst lass ich es einfach so.

Erstellung von Berichten und Sensitivitätsanalyse

Weißt du noch, als ich dir gesagt habe, du sollst diese Berichtskästen überprüfen? Hier ist, was du bekommst.

Der Antwortbericht zeigt, wie das Problem gelöst wurde. Bild vom Autor.

Der Antwortbericht ist deine schriftliche Nachverfolgung. Es zeigt, was sich geändert hat: Die Entscheidungsvariablen gingen von 0 auf 30 Stühle und 20 Tabellen, der Gewinn stieg von 0 auf 3.300 Dollar. Es zeigt dir auch, welche Einschränkungen verbindlich sind (vollständig genutzt) und welche nicht verbindlich sind (Spielraum haben). Bei uns sind sowohl die Arbeitskräfte als auch das Holz knapp, was heißt, dass wir jede verfügbare Kapazität nutzen.

Wie stark können sich die Eingaben ändern, bevor sich die Lösung ändert? Bild vom Autor.

Der Sensitivitätsbericht ist echt spannend. Die Spalten„Zulässige Erhöhung/Verringerung“ ( ) in der Tabelle„ “ zeigen, wie stark sich deine Eingaben ändern können, bevor du eine andere Lösung brauchst. Große Bereiche bedeuten, dass deine Antwort zuverlässig ist. Kleine Bereiche bedeuten, dass schon kleine Änderungen alles auf den Kopf stellen können.

Schattenpreise zeigen den Wert von Ressourcen. Bild vom Autor.

Aber das eigentliche Gold ist der Schattenpreis. Das sagt dir, was eine weitere Einheit einer Ressource wert ist. Wenn die Arbeit einen Schattenpreis von 7,5 Dollar hat, bringt eine zusätzliche Arbeitsstunde 7,5 Dollar mehr Gewinn. Also, wenn du Zeitarbeitskräfte für 5 Dollar pro Stunde einstellen kannst, dann mach das. Bei 10 Dollar pro Stunde, nein danke.

Wichtigkeit der Sensitivitätsanalyse

Ich nutze diese Berichte, um drei Fragen zu beantworten. In welche Ressourcen sollte ich investieren? Ist meine Lösung anfällig? Was ist, wenn meine Prognosen falsch sind? Die Berichte geben klare Antworten auf alle drei Fragen.

Zuerst schaue ich mir die Schattenpreise an, um zu entscheiden ,in welche Ressourcen ich investieren sollte. Ein hoher Schattenpreis heißt einen hohen Wert.

Als Nächstes schaue ich mir die zulässigen Bereiche an, um zu sehen, ob meine Lösung anfällig ist. Wenn sich der Gewinn nur um 1 Dollar ändern kann, bevor sich die Lösung ändert, bist du auf dünnem Eis. Wenn es sich um 50 Dollar ändern kann, ist alles gut.

Zum Schluss frag ich mich : Was ist, wenn meine Prognosen falsch sind? Die zulässigen Bereiche zeigen dir, wie weit du dich davon entfernen kannst, bevor du neu rechnen musst.

Mein Tipp: Schau dir immer die Schattenpreise an, bevor du ein Budget genehmigst. Wenn eine Ressource einen Schattenpreis von Null hat, heißt das, dass du über freie Kapazitäten verfügst. Mehr zu investieren ist reine Verschwendung. Investier dein Geld da, wo die Schattenpreise am höchsten sind.

Fehlerbehebung und Problemlösung

Die Fehlermeldungen von Solver klingen beängstigend, sind aber meistens leicht zu beheben. Hier sind die, die ich am häufigsten sehe.

Häufige Fehler und Meldungen

Der Solver hat keine machbare Lösung gefunden.

Das heißt, deine Einschränkungen stehen im Widerspruch zueinander.

 Keine Lösung erfüllt alle Anforderungen. Bild vom Autor.

Das passiert meistens, wenn du was Unmögliches verlangst, wie zum Beispiel ein Gewinnziel erreichen zu wollen, das mit den vorhandenen Ressourcen einfach nicht machbar ist. Die Lösung besteht darin, die Einschränkungen nacheinander zu entfernen, bis Solver funktioniert. So findet man genau heraus, welche Einschränkung den Konflikt verursacht.

Die Zellwerte laufen nicht zusammen.

Das heißt, dein Ziel kann immer weiter wachsen. Du hast irgendwo eine Obergrenze vergessen. Echte Unternehmen haben immer Grenzen, füge sie hinzu.

Der Solver kann die aktuelle Lösung nicht verbessern.

Das passiert meistens bei nichtlinearen Problemen. Probier mal andere Startwerte aus oder wechsel zur evolutionären Methode. Manchmal reicht ein lokales Optimum schon aus.

Fehler bei der Modellformulierung

Zirkuläre Referenzen

Das passiert, wenn Zelle A von Zelle B abhängt, die wiederum von Zelle A abhängt. Excel macht dich darauf aufmerksam. Geh auf die Registerkarte „Formeln“ > „Vorgänger verfolgen“, um die Schleife zu finden und sie zu beenden.

Ganzzahlige Einschränkungen

Hinzufügen von ganzzahligen Einschränkungen für ganze Zahlen. Bild vom Autor.

Einschränkungen und Alternativen für große Probleme

Excel Solver ist super, aber es gibt ein paar Einschränkungen, die du kennen solltest.

Die Einschränkungen von Solver verstehen

Der Standard-Löser schafft maximal 200 Entscheidungsvariablen und 100 Einschränkungen. Das klingt nach viel, bis du versuchst, 200 Mitarbeiter auf 50 Zeitfenster zu verteilen. Das sind 10.000 Variablen, viel mehr, als Solver verarbeiten kann.

Ich nutze Solver für monatliche Produktionspläne, vierteljährliche Budgets und die Projektauswahl. Es ist super für taktische Entscheidungen. Aber es ist nicht für große Probleme wie Lieferkettennetzwerke mit vielen Standorten gemacht.

Alternativen für große Probleme

OpenSolver

OpenSolver ist kostenlos und hebt die Beschränkungen vonauf. Es kann so viele Variablen verarbeiten, wie dein Computer-Speicher hergibt, nutzt die gleiche Oberfläche wie Excel Solver und du kannst es von OpenSolver.org runterladen. Ich empfehle es, wenn du über den Standard-Solver hinaus bist.

Frontline Solver Premium

Frontline Solver kostet zwar was (3.000 bis 1.500 Dollar pro Jahr), unterstützt aber Tausende von Variablen mit besseren Algorithmen. Das würde ich nehmen, wenn ich das beruflich machen würde.

Einzelplatzsoftware

Tools wie CPLEX oder Gurobi können Millionen von Variablen verarbeiten, aber man braucht Programmierkenntnisse und muss echt Geld investieren. Das ist was für Unternehmen.

Ich denke, für die meisten Business-Analysten deckt Excel Solver 95 % der benötigten Funktionen ab. Wenn du an seine Grenzen stößt, ist das eigentlich ein gutes Zeichen: Du machst anspruchsvolle Arbeit, die bessere Tools rechtfertigt.

Fazit

Wir haben echt viel durchgenommen, von den Grundeinstellungen bis hin zur Interpretation von Schattenpreisen. 

Jetzt musst du nicht mehr in einem Besprechungsraum über Meinungen diskutieren, sondern kannst die Zahlen einfach auf den Bildschirm bringen. Hier ist der mathematisch beste Plan. Das ändert die Situation. Hör auf zu raten und fang an zu beweisen.

Probier's doch mal bei deinem nächsten kniffligen Problem aus, bei dem Terminkonflikt oder Budgetengpass, den du bisher gemieden hast. Vielleicht findest du die perfekte Antwort ja schon die ganze Zeit in deiner Tabelle.

Und wenn du noch mehr Techniken lernen willst, um deinen Chef zu beeindrucken, mach doch an unserem  Kurs „Datenanalyse in Excel“ , um deine Kenntnisse weiter auszubauen.