Excel–Solver für mehrere Bedingungen

Gestern, am 22.07.2020, habe ich gezeigt, wie man ein einfaches Rückwärtsrechnen mit der Zielwertsuche vornimmt. Heute habe ich mehrere veränderbare Zellen und brauche ein anderes Hilfsmittel: den Solver.

Im Garten sollen die Flächen neu aufgeteilt werden. Zur Verfügung stehen 130 Quadratmeter, einige Werte wie Terrassengröße oder Sitzplatz sind fest vorgegeben. Bei den anderen Werten gibt es Minimal-Größen, die nicht unterschritten werden sollen. So soll es beispielsweise mindestens einen Quadratmeter für Kräuter und mindestens 20 Quadratmeter für Rasen geben.

Eingetragen sind in Spalte B die Wunschflächen, aber das ergibt insgesamt 161 Quadratmeter, also 30 mehr als ich habe. Die Minimalgrößen stehen der Übersichtlichkeit in Spalte C.

29.06.2020-006

Für die  Berechnung benötigen Sie den Solver, der sich – wenn bereits aktiviert – unter “Daten | Analyse” ganz am rechten Rand des Ribbons befindet. Fehlt er da, muss er zuerst eingeschaltet werden mit “Datei | Optionen | Addins”. Vergewissern Sie sich, dass unten im Auswahlfeld “Veralten” die “Excel Addins” angezeigt werden, anderenfalls suchen Sie sie aus der Auswahlliste aus. Klicken Sie dann auf “Los”. Es werden einige Addins angezeigt, unter anderem der Solver. Aktivieren Sie ihn und bestätigen Sie alles mit OK.

Markieren Sie B11 und starten Sie mit “Daten | Solver” den Lösungsweg.

29.06.2020-009

  • Aktivieren Sie bei „Bis” die Option “Wert” und tragen Sie 130 ein
  • Klicken Sie bei “Durch Ändern von Variablenzellen” und markieren Sie die Zellen B2 bis B7; die Zellen von B8 bis B10 sind bei mir die Werte, die nicht verändert werden können.
  • Klicken Sie auf “Hinzufügen” und erstellen Sie die erste Nebenbedingung:
    Zellbezug $B$2 >= 129.06.2020-008
  • Klicken Sie in diesem Dialog auf “Hinzufügen” für die nächste Nebenbedingung. Das ist
    $B$2 >=4
  • Auch hier wieder Hinzufügen anklicken für die nächste Bedingung.
  • Fahren Sie bis zur letzten Bedingung $B$7 >= 20 fort und bestätigen dann mit OK

Der ausgefüllte Solver sieht jetzt so aus:

29.06.2020-010

Klicken Sie auf “Lösen” und lassen Excel die Werte durchrechnen. Sie erhalten eine Lösung – aber mit reichlich krummen Zahlen.

29.06.2020-011

Um das zu vermeiden, müssen Sie den Solver noch anweisen, nur ganzzahlige Werte zu verwenden. Brechen Sie hier ab und rufen den Solver erneut auf.

  • Klicken Sie auf “Hinzufügen”
  • Als Zellbezug geben Sie den Bereich B2 bis B7 an (am einfachsten durch Markieren der Zellen)
  • Wählen Sie als Vergleichsoperator “int” aus, damit wird in das Feld “Nebenbedingung” automatisch das Wort “Ganzzahlig” eingetragen.29.06.2020-012
  • Bestätigen Sie mit OK.

Jetzt sind die Werte schon brauchbarer:

29.06.2020-013

Ein weiteres Beispiel für eine Solver-Berechnung habe ich am 18.12.2017 und 19.12.2017 auf diesem Blog veröffentlicht

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s