Solver in Excel: Beispiele zur Zielwertsuche mit mehreren Bedingungen

In diesem Tipp erfahren Sie, was es mit dem Excel Add-In Solver auf sich hat und erhalten einige Beispiele, wie Sie ihn in der Praxis nutzen können.

Berechnungen in Excel können manchmal ganz schön mühselig sein. Sollen die Werte dann auch noch optimiert werden (beispielsweise um ein bestimmtes Ziel zu erreichen) verliert man in großen Tabellen auch mal ganz schnell den Überblick. Mit dem Excel Add-In Solver für Windows und Mac steht Ihnen glücklicherweise eine Möglichkeit zur Verfügung, mit dem Sie mathematische Problemstellungen dieser Art ganz einfach lösen können. Das Tool berechnet automatisch den optimalen Wert und trägt ihn in eine vorher definierte Zielzelle ein.

In diesem Tutorial erfahren Sie, was der Solver in Excel genau ist. Zudem erklären wir Ihnen anhand von zwei einfachen Fallbeispielen, wie Sie ihn effizient nutzen können.

Mit dem Excel Add-In Solver können Sie Zielwertsuchen und -berechnungen durchführen.
Damit der Solver eine Gleichung für Sie lösen kann, müssen Sie lediglich Eigenschaften und Grenzwerte definieren.
Der Solver ist für Excel auf mobilen Geräten nicht verfügbar.

Inhalt

1. Solver liefert optimale Lösungen bei komplexen Problemstellungen
2. So installieren Sie das Solver-Add-In
3. Beispiele zum Lösen einer Gleichung mit Solver in Excel
- 3.1. Fallbeispiel 1: Der Beton-Lieferant
- 3.2. Fallbeispiel 2: Der Außendienst-Mitarbeiter

1. Solver liefert optimale Lösungen bei komplexen Problemstellungen

Solver ist ein Add-In für Excel, mit dem Sie Lösungen für Entscheidungsprobleme finden können. Sie können damit „Was-wäre-wenn“-Analysen durchführen und auf diese Weise ermitteln, in welcher Kombination verschiedene Faktoren oder Parameter zum bestmöglichen Ergebnis führen. Dazu sind keine komplizierten mathematischen Berechnungen nötig, Sie bestimmten den kleinsten oder größten Wert einer Zelle, indem Sie andere Zellen verändern.

Im Gegensatz zur Zielwertsuche, bei der Sie nur den Inhalt einer Zelle verändern können, haben Sie beim Solver die Möglichkeit, gleich mehrere Zellen in den Berechnungsvorgang miteinzubeziehen. Überdies ist es auch möglich, mehrere Nebenbedingungen festzulegen, die bei der Berechnung des Optimums berücksichtigt werden sollen.

Die Funktionsweise des Solver basiert auf drei Grundkomponenten:

Die Zielzelle enthält das Ziel der Berechnung (z.B. die Wochenarbeitsstunden für alle Mitarbeiter).
Variable Zellen lassen sich so verändern, dass das gewünschte Ergebnis erreicht wird (z.B. die Anzahl der Wochenarbeitsstunden für alle Mitarbeiter).
Beschränkungen legen fest, bis zu welchen Grenzen oder Einschränkungen Solver die Aufgabe lösen kann (z.B., wenn der Abteilungsleiter nur von Montag bis Donnerstag im Büro ist)

2. So installieren Sie das Solver-Add-In

Damit Sie den Solver in Excel verwenden können, müssen Sie diesen zuerst aktivieren. Starten Sie dazu die Tabellenkalkulation und führen Sie anschließend die folgenden Schritte aus:

Gehen Sie auf „Datei -> Optionen“ und öffnen Sie im Dialogfenster „Excel-Optionen“ links den Eintrag „Add-Ins“.
Wählen Sie im Drop-Down-Menü „Verwalten“ den Eintrag „Excel-Add-Ins“ aus und klicken Sie anschließend auf „Los“.

Solver Add-In in den Excel Optionen aktivieren

Wählen Sie in den Excel-Optionen den Menü-Eintrag "Add-Ins" aus.

Setzen Sie im Dialogfenster „Add-Ins“ das Häkchen vor dem Eintrag „Solver“ und bestätigen Sie mit „OK“.

Setzen Sie das Häkchen vor dem Eintrag "Solver" und bestätigen Sie mit "OK".

Das Solver-Add-In wird nun installiert und lässt sich im Anschluss über die Registerkarte „Daten“ im Bereich „Analyse“ starten.

Der Solver erscheint nun im Menü "Daten" im Bereich "Analyse".

3. Beispiele zum Lösen einer Gleichung mit Solver in Excel

In diesem Abschnitt stellen wir Ihnen einige typische Berechnungsbeispiele für den Excel-Solver vor.

3.1. Fallbeispiel 1: Der Beton-Lieferant

Stellen Sie sich vor, Sie sind der Inhaber von zwei Betonwerken und beliefern derzeit 3 Baustellen mit Beton. Abhängig vom unterschiedlichen Bedarf müssen Sie die Menge entsprechend verteilen. Diese Berechnung kann der Solver für Sie erledigen.

In der nachfolgenden Tabelle sehen Sie die Verteilung von Bedarf und Aufkommen.

Excel Beispieltabelle mit Bedarf und Aufkommen

Die Verteilung von Bedarf und Aufkommen in unserer Beispieltabelle.

In den Zellen E3 bis E5 werden jeweils die Summen aus den Zeilen 3 bis 5 berechnet, die Zellen C6 und D6 enthalten die Summen der Spalten C und D.

Gut zu wissen: Die Werte C2 bis D2 sind in unserem Beispiel händisch eingetragen, können aber bei Bedarf auch durch Bezüge aus anderen Quellen ersetzt werden.

Im Dialogfenster des Solver tragen wir nun die folgenden Parameter ein:

Nun tragen wir folgende Werte in den Solver ein:

Zielzelle: $E$6
Wert: 2684
Veränderbare Zellen: $C$3:$D$5
Nebenbedingungen:
$C$6 = 1179
$$D6 = 1505
$E$3 = 909
$E$4 = 1172
$E$5 = 603

Mit einem Klick auf „Lösen“ setzen Sie den Solver in Gang. Im Anschluss schlägt Ihnen das Tool eine potenzielle Lösung vor, die Sie annehmen oder ablehnen können.

Die Solver-Parameter aus unserem Beispiel.

Wir nehmen den Vorschlag des Solvers an, das Ergebnis sieht dann wie folgt aus:

Bei unserem Ergebnis handelt es sich lediglich um einen Vorschlag und nicht um die einzig mögliche Lösung.

Gut zu wissen: Sollte der Solver keine Lösung finden, müssen Sie das Problem überdenken und möglicherweise einen neuen Ansatz suchen. Setzen Sie dazu als erstes Ihre Tabellen-Werte auf die ursprüngliche Einstellungen zurück.

3.2. Fallbeispiel 2: Der Außendienst-Mitarbeiter

Die Reisekosten des Außendienst-Mitarbeiters Müller betragen aktuell etwa 460 Euro pro Tag.

In den Zellen B3 bis B7 stehen ganze Zahlen, während in der Zelle B8 die Summe-Formel genutzt wird.

In Ihrem Unternehmen ist allerdings strikter Sparkurs angesagt, daher dürfen die täglichen Gesamtkosten ab sofort nur noch maximal 400 Euro betragen. Dabei dürfen die Spesen nicht weniger als 15 Euro betragen. Zusätzlich sollen alle weitere Kosten im selben Verhältnis zueinander stehen, wie vor der Berechnung. Um Letzteres für den Excel-Solver darzustellen, nutzen wir zwei Hilfsfelder:

In der Zelle C3 stellt =B3*0,5 das Verhältnis der Zellen B4 und B5 zur Zelle B3 dar. In Zelle C4 steht hingegen =B4/100*45, um das Verhältnis von B7 zur Zelle B4 bzw. B5 abzubilden.

Nach dem Erstellen der Hilfsfelder können Zielwert und Nebenbedingungen in den Solver eingetragen werden. Dafür tragen wir in diesem Beispiel die folgenden Parameter ein: