Zahlen analysieren und optimieren: Der Solver in Microsoft Excel

Das Lösen komplexer Rechenprobleme kostet in der Regel Zeit und manchmal Nerven. Wer es sich einfacher machen möchte, kann auf den Solver von Microsoft Excel zurückgreifen. Er ist der große Bruder der Zielwertsuche und ermöglicht es, auch umfangreiche Optimierungsaufgaben zu lösen. Im Gegensatz zur Zielwertsuche kann er mehrerer Variablen gleichzeitig verändern, bis das gewünschte Ergebnis gefunden ist. Dabei berücksichtigt er zusätzliche Nebenbedingungen. Anhand eines Beispiels erklären Tanja Kuhn und Kathrin Rykart in ihrem Artikel, wie Sie den Solver für Optimierungen einsetzen können und wie Sie Schnelligkeit und Genauigkeit beeinflussen.

Zahlen analysieren und optimieren: Der Solver in Microsoft Excel

Das Lösen komplexer Rechenprobleme kostet in der Regel Zeit und manchmal Nerven. Wer es sich einfacher machen möchte, kann auf den Solver von Microsoft Excel zurückgreifen. Er ist der große Bruder der Zielwertsuche und ermöglicht es, auch umfangreiche Optimierungsaufgaben zu lösen. Im Gegensatz zur Zielwertsuche kann er mehrerer Variablen gleichzeitig verändern, bis das gewünschte Ergebnis gefunden ist. Dabei berücksichtigt er zusätzliche Nebenbedingungen. Anhand eines Beispiels erklären Tanja Kuhn und Kathrin Rykart in ihrem Artikel, wie Sie den Solver für Optimierungen einsetzen können und wie Sie Schnelligkeit und Genauigkeit beeinflussen.

Das Lösen komplexer Rechenprobleme kostet in der Regel Zeit und manchmal Nerven. Wer es sich einfacher machen möchte, kann auf den Solver von Excel zurückgreifen. Er ist der große Bruder der Zielwertsuche und ermöglicht es, auch umfangreiche Optimierungsaufgaben zu lösen. Im Gegensatz zur Zielwertsuche kann er mehrerer Variablen gleichzeitig verändern, bis das gewünschte Ergebnis gefunden ist. Dabei berücksichtigt er zusätzliche Nebenbedingungen. Im Folgenden erfahren Sie, wie Sie den Solver in Excel aktivieren und für Optimierungen einsetzen können.

Solver aktivieren

Der Solver ist ein Add-In von Excel und wird bei vollständiger Installation automatisch installiert (siehe Menüpunkt Extras / Solver). Falls dieser Menüpunkt bei Ihnen nicht erscheint, gehen Sie folgendermaßen vor:

  • Rufen Sie den Menüpunkt Extras / Add-Ins auf.
  • Aktivieren Sie im Dialogfeld "Add-Ins" in der Liste "Verfügbare Add-Ins" das Kontrollfeld "Solver".
  • Schließen Sie das Dialogfenster "Add-Ins" über die Schaltfläche "OK".

Der Solver ist nun aktiviert. Die konkrete Anwendung des Solvers lässt sich am Besten anhand eines einfachen Beispiels zeigen.

Beispiel: Streckenverlauf optimieren

Die kleine Gemeinde Musterdorf liegt am Rande eines Waldes. Der Wald ist ein beliebtes Naherholungsgebiet mit einer großen Waldlichtung. Auf dieser Waldlichtung befinden sich ein Spielplatz, ein Waldhaus und eine Feuerstelle zum Grillen. Der Gemeinderat hat beschlossen, das Waldhaus neu einzurichten und eine Stromleitung dorthin verlegen zu lassen, um das Haus für private, Gemeinde- und Vereinsanlässe vermieten zu können.

Bild 1: Skizze der Ortschaft und des Waldhauses.

Zur Waldlichtung gelangt man über eine Straße (1,5 km) und einen Waldweg (0,5 km), wie Bild 1 zeigt. Das Problem besteht nun darin, die Strecke für die Kabelverlegung so zu wählen, dass die Gesamtkosten möglichst niedrig gehalten werden. Aus früheren Projekten weiß der Gemeinderat, dass die Kabelverlegung entlang der Straße 300 Euro pro Meter und durch den Wald wegen der erschwerten Grabungsarbeiten 450 Euro pro Meter kostet. Aber welche Strecke erfüllt dieses Kriterium? Auf den ersten Blick bieten sich zwei mögliche Streckenverläufe an:

Variante 1: Die kürzeste Gesamtstrecke

Eine Möglichkeit ist, die kürzeste Strecke zu wählen und das Kabel ausschließlich durch den Wald zu verlegen. Errechnet man die benötigte Kabellänge für diese Strecke und multipliziert sie mit den Kosten für die Verlegung durch den Wald, ergeben sich Gesamtkosten in Höhe von 711.513 Euro (Berechnung siehe Tabelle 1).

Variante 2: Die kürzeste Waldstrecke

Die Kabelverlegung entlang der Straße ist pro Meter billiger, als die Verlegung im Wald. Deshalb liegt der Gedanke nahe, möglichst wenig Kabelmeter durch den Wald verlaufen zu lassen, um so Kosten einzusparen. Das Kabel würde in diesem Fall 1.500 Meter an der Straße entlang verlaufen und 500 Meter durch den Wald. Obwohl diese Alternative diejenige mit der längsten Gesamtstrecke ist, liegen die Kosten niedriger als bei der Variante 1: Sie belaufen sich auf 675.000 Euro (Tabelle 1).

Tabelle 1: Kostenberechnung für die Varianten 1 und 2.

Beide Lösungen - die längste und die kürzeste Gesamtstrecke - sind Extreme, und die Mitglieder des Gemeinderats fragen sich, ob es nicht eine noch günstigere Lösung zwischen diesen Extremen gibt. Bei dieser dritten Variante müsste das Kabel teils entlang der Straße und teils diagonal durch den Wald verlaufen (Bild 2).

Bild 2: Ansatz zur Optimierung des Streckenverlaufs: Das Kabel läuft sowohl entlang der Straße als auch durch den Wald.

Die Gesamtkosten für die Kabelverlegung ergeben sich, wenn man die Streckenabschnitte S1 und S2 mit den jeweiligen Kosten pro Meter multipliziert; für das optimale Ergebnis müssen diese Kosten minimiert werden. Mit Hilfe des Solvers können Sie die optimale Lösung berechnen.

Optimierung mit dem Solver

Um das Optimierungsproblem in unserem Beispiel zu lösen, geben Sie entsprechend Bild 3 Text, Werte und Formeln in eine Excel-Tabelle ein.

Bild 3: Aufstellung für die Solver-Optimierung.

Tabelle 2: Formeln und ihre Entsprechungen.
Zelle Formel Entsprechung
B6 =B13-B5 S1=Gesamtlänge der Straße abzüglich X
B7 =WURZEL(B5^2+B14^2) S2 (berechnet mit dem Satz des Pythagoras)
B9 =B6*B3 Kosten der Kabelverlegung entlang der Straße
B10 =B7*B2 Kosten der Kabelverlegung durch den Wald
B11 =SUMME(B9:B10) Gesamtkosten der Kabelverlegung

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 1
Kommentare 1

Alle Kommentare

Dieter
Gennburg
Gefällt mir, kann man sofort anwenden zur Splittung externer und interner Dienstleistungen, statt Wald- und Straßenverlegekosten. Dabei werden die beiden Ausgangsstrecken als maximale Stunden-Limits deklariert.
Alle anzeigen