Schmidt Jutta - Smart Data statt Big Data

Nachdem nun Solver installiert ist, darf endlich auch ein Optimierungsproblem auftauchen: Sie erfahren, dass Sie am Tag 2.499 Kalorien benötigen. Welches ist die kleinste Anzahl an Artikeln, die Sie am Imbissstand kaufen müssen, um diesen Kalorienwert zu erreichen? Es ist offensichtlich, dass Sie diesen Wert mit dem Kauf von zehn Portionen Eiscreme-Sandwich erreichen, die jeweils 240 Kalorien haben, aber gibt es vielleicht eine Alternative, die das Ergebnis mit weniger Artikeln erreicht?

Legen Sie als Erstes eine Kopie des Arbeitsblatts KALORIEN an. Wenn Sie nicht wissen, wie Sie in Excel ein Arbeitsblatt kopieren: Klicken Sie die Registerkarte des Arbeitsblatts, das Sie kopieren wollen, mit der rechten Maustaste an und wählen Sie VERSCHIEBEN ODER KOPIEREN. Geben Sie dem Arbeitsblatt einen neuen Namen (zum Beispiel Kalorien-Solver) und vergessen Sie nicht, das Kontrollkästchen vor KOPIE ERSTELLEN zu aktivieren, bevor Sie das Dialogfeld durch Klicken auf OK wieder verlassen. Dadurch erhalten Sie ein Arbeitsblatt wie das in Abbildung 1.21.

Abb. 1.21Das kopierte Arbeitsblatt »Kalorien-Solver«

Um Solver ans Arbeiten zu bekommen, müssen Sie dem Add-In einen Zellenbereich angeben, der als Grundlage für die Entscheidungsfindung dienen soll. In diesem Fall muss Solver entscheiden, wie viele Exemplare welcher Artikel gekauft werden sollen. Beschriften Sie deshalb Zelle C1 mit Wie viele? oder so, wie es Ihnen gefällt.

Excel geht davon aus, dass leere Zellen 0 enthalten, weshalb Sie in solche Zellen nichts eintragen müssen.

Summieren Sie in Zelle C16 die Anzahl an Artikeln, die Sie kaufen müssen:

=SUMME(C2:C15)

In der Zelle darunter können Sie die Kalorien dieser Artikel summieren (was insgesamt 2.400 ergeben sollte). Sie verwenden hierfür die Formel SUMMENPRODUKT:

=SUMMENPRODUKT(B2.B15;C2:C15)

Damit erhalten Sie ein Arbeitsblatt wie das in Abbildung 1.22.

Abb. 1.22Die Vorbereitungen für das Zählen der Artikel und der Kalorien sind abgeschlossen.

Jetzt sind Sie so weit, das Modell aufzubauen. Starten Sie das Solver-Fenster, indem Sie auf der Registerkarte DATEN die Schaltfläche SOLVER anklicken.

Hinweis

Das Solver-Fenster von Excel 2010, das Abbildung 1.23zeigt, ist fast identisch mit dem von Excel 2011 und 2013. Das Layout sieht in Excel 2007 etwas anders aus, aber der einzige größere Unterschied besteht darin, dass es dort kein Feld für die Auswahl der Lösungsmethode gibt. Sie erfahren später alles Nötige über diese Elemente.

Abb. 1.23Das »jungfräuliche«Solver-Fenster

Die wichtigsten Elemente, die Sie in das Solver-Fenster (aus Abbildung 1.23) eintragen, um ein Problem zu lösen, sind eine Zielzelle, eine Optimierungsrichtung (Minimieren, Maximieren oder Erreichen eines bestimmten Wertes), einige Variablen für die Entscheidungsfindung, die von Solver geändert werden können, und ein paar Bedingungen.

In unserem Fall ist das Ziel, in Zelle C16 die Zahl der Artikel so klein wie möglich zu halten. Bei den Zellen, die geändert werden können, handelt es sich um die Artikelauswahl in C2:C15. Und als Bedingung gilt, dass in C17 die Summe der Kalorien 2.400 lauten soll. Außerdem müssen wir eine Bedingung hinzufügen, die aussagt, dass unsere Entscheidungen mit Zahlen zu tun haben, die nicht negativ werden dürfen. Aktivieren Sie deshalb das Kontrollkästchen vor NICHT EINGESCHRäNKTE VARIABLEN ALS NICHT-NEGATIV FESTLEGEN (das in Excel 2007 über die Schaltfläche OPTIONEN erreicht werden kann) und fügen Sie den Bedingungen eine ganzzahlige Einschränkung hinzu, weil Ihnen vielleicht sonst empfohlen wird, 1,7 Flaschen Limonade zu kaufen. Ganzzahlige Einschränkungen werden im Einzelnen in Kapitel 4behandelt.

Um die Bedingung hinzuzufügen, wie viele Kalorien es insgesamt sein müssen, klicken Sie auf die Schaltfläche HINZUFüGEN und legen fest, dass C17 gleich 2400 ist (siehe Abbildung 1.24).

Abb. 1.24Die Kalorien-Bedingung hinzufügen

Fügen Sie auf die gleiche Weise die Bedingung hinzu, dass die Zellen C2:C15 nur ganzzahlige Werte enthalten dürfen (siehe Abbildung 1.25).

Abb. 1.25Hinzufügen, dass nur ganzzahlige Werte verwendet werden dürfen

Klicken Sie auf OK.

Achten Sie darauf, dass die Lösungsmethode in Excel 2010, 2011 und 2013 SIMPLEX-LP ist. SIMPLEX-LP ist die für unser Problem geeignete Wahl, weil es sich um ein lineares Problem handelt (wie Kapitel 4zeigt, steht das L in LP für linear ). Wenn ich von linear spreche, meine ich, dass es bei dem Problem im Bereich von C2 bis C15 nur um lineare Kombinationen der Entscheidungen (Summen, Produkte mit Konstanten wie Kalorienwerten und so weiter) geht.

Wenn es in diesem Modell nichtlineare Berechnungen gäbe (zum Beispiel die Quadratwurzel einer Entscheidung, einen Logarithmus oder eine Exponentialfunktion), könnten Sie einen der anderen Algorithmen verwenden, die Excel in Solver zur Verfügung stellt. Kapitel 4behandelt diesen Themenkomplex ausführlicher.

In Excel 2007 legen Sie in dem Dialogfeld, das auftaucht, wenn Sie auf die Schaltfläche OPTIONEN klicken, fest, dass das Problem linear ist. Wenn Sie alle Daten eingegeben haben, sollte das Fenster SOLVER-PARAMETER aussehen wie das in Abbildung 1.26.

Abb. 1.26Die Solver-Einstellungen, die benötigt werden, um mit so wenig Artikeln wie möglich 2.400 Kalorien zu erhalten

Nun klicken Sie auf die Schaltfläche LÖSEN. Excel sollte eigentlich sofort eine Lösung finden. Und diese Lösung, die Abbildung 1.27zeigt, lautet 5. Es kann sein, dass Ihr Excel über andere Artikel zu diesem Ergebnis kommt, als es Abbildung 1.27zeigt, aber Sie müssen auf jeden Fall mindestens fünf Teile kaufen, um auf die gewünschten 2.400 Kalorien zu kommen.

Abb. 1.27Die optimierte Artikelauswahl

Dieses Buch sollte ursprünglich nur mit dem in Excel eingebauten Solver auskommen. Es hat sich aber gezeigt, dass aus mysteriösen und nicht kommunizierten Gründen in bestimmten Excel-Versionen Funktionen aus Solver entfernt worden sind.

Dies bedeutet, dass das gesamte Buch problemlos funktioniert, wenn Solver in Excel 2007 oder Excel 2011 für Mac verwendet wird, während sich Solver in Excel 2010 und Excel 2013 ab und an darüber beschwert, dass ein lineares Optimierungsmodell zu groß sei (ich sage Ihnen in diesem Buch jedes Mal Bescheid, wenn ein Modell zu komplex werden könnte).

Glücklicherweise gibt es ein kostenloses Werkzeug mit dem Namen OpenSolver, das bei Excel für Windows diese Defizite beseitigt. Sie können Ihre Modelle beim Einsatz von OpenSolver auch weiterhin in der normalen Solver-Oberfläche zusammenbauen, und OpenSolver stellt eine Schaltfläche zur Verfügung, die Sie anklicken, um einen Simplex-LP-Algorithmus zu nutzen, der bemerkenswert schnell ist.

Um OpenSolver einzurichten, gehen Sie zu OpenSolver.orgund laden sich die ZIP-Datei herunter. Entpacken Sie diese Datei in einen Ordner, und jedes Mal, wenn Sie ein dickes Modell lösen müssen, arbeiten Sie zunächst ganz normal in einem Excel-Arbeitsblatt. Dann führen Sie auf der Datei OpenSolver.xlsm einen Doppelklick aus, was in Excel auf der Registerkarte DATEN zu einem OPENSOLVER-Abschnitt führt. Klicken Sie auf die Schaltfläche SOLVE, um ein vorhandenes Modell zu lösen. Wie Abbildung 1.28zeigt, wende ich OpenSolver in Excel 2010 auf das Modell aus dem letzten Abschnitt an, und es werden fünf Stück Pizza gekauft. Dieser Solver ist mir richtig sympathisch.