Abb. 1.19 Das Arbeitsblatt »Einnahmen je Artikel«, das durch das Kopieren von Werten aus einer Pivot-Tabelle erstellt wurde
Scheuen Sie sich nicht, so lange mit den Zeilen und Spalten der Pivot-Tabelle herumzuspielen, bis Sie genau wissen, was dabei passiert. Versuchen Sie zum Beispiel, eine Pivot-Tabelle anzulegen, die anzeigt, wie viele Kalorien je Kategorie verkauft worden sind.
1.12 Array-Formeln verwenden
In der Arbeitsmappe mit den Transaktionen des Imbissstandes gibt es ein Arbeitsblatt mit dem Namen Provision. Es hat sich herausgestellt, dass Sie von Coach O’Shaughnessy nur dann die Genehmigung zur Führung des Imbissstandes bekommen, wenn Sie ihm dafür eine Provision zahlen (um vielleicht seine Socken-Kaufsucht zu subventionieren). Das Arbeitsblatt Provision enthält in Prozent pro Artikel das, was der Coach beim Verkauf eines Artikels kassiert.
Wie viel schulden Sie ihm nun für das Spiel vom letzten Abend? Um diese Frage zu beantworten, müssen Sie die Summe der Einkünfte der einzelnen Artikel aus der zuletzt angelegten Pivot-Tabelle mit den Prozentsätzen multiplizieren, die der Coach erhält, und die Ergebnisse dann summieren.
Für diese Operation gibt es eine fantastische Formel, die das Multiplizieren und Summieren in einem Rutsch vornimmt. Als sie getauft wurde, hat man sich wenig Mühe mit der Namensgebung gemacht und sie einfach nur SUMMENPRODUKT genannt. Geben Sie auf dem Arbeitsblatt Einnahmen je Artikel in Zelle E1 die Bezeichnung Zahlung an den Coach ein und fügen Sie der Zelle E2 diese Formel hinzu:
=SUMMENPRODUKT(B2:B15;Provision!B2:O2)
Upsi! Da muss irgendetwas schiefgelaufen sein; die Zelle gibt nur #WERT! aus. Was hat da nicht geklappt?
Auch wenn Sie zwei Bereiche gleicher Größe ausgewählt und in SUMMENPRODUKT eingetragen haben, erkennt die Formel nicht, dass diese Bereiche gleich sind, weil der eine vertikal und der andere horizontal verläuft.
Glücklicherweise kennt Excel eine Funktion, um Arrays in die richtige Richtung zu »drehen«. Sie heißt MTRANS. Sie müssen die Formel deshalb abändern:
=SUMMENPRODUKT(B2:B15;MTRANS(Provision!B2:O2))
Das war wohl wieder nichts! Die Fehlermeldung ändert sich nicht.
Der Grund dafür, dass Sie immer noch mit einer Fehlermeldung zu kämpfen haben, ist eine Besonderheit von Excel: Standardmäßig gibt jede Formel dieses Programms nur einen einzigen Wert zurück. Selbst MTRANS gibt nur den ersten Wert des umgruppierten Arrays zurück. Wenn Sie wollen, dass das gesamte Array zurückgegeben wird, müssen Sie aus SUMMENPRODUKT eine »Array-Formel« machen. Dieser Begriff bedeutet genau das, was Sie sich darunter vorstellen: Array-Formeln geben Arrays und nicht nur einzelne Werte zurück.
Sie müssen nichts daran ändern, wie Sie Ihr SUMMENPRODUKT schreiben, um aus der Formel eine Array-Formel zu machen. Alles was Sie tun müssen, ist, nach dem Schreiben statt
Sieg! Wie Abbildung 1.20 zeigt, ergibt die Berechnung 57,60 Euro. (Ich schlage vor, diesen Betrag auf 50 Euro abzurunden. Wie viele Socken benötigt der Coach denn noch?)
Abb. 1.20 Über eine Array-Formel ein »SUMMENPRODUKT«erhalten
1.13 Probleme mit dem Solver lösen
Viele der Techniken, um die es in diesem Buch geht, laufen auf Optimierungsmodelle hinaus. Bei einem Optimierungsproblem handelt es sich um ein Problem, bei dem Sie die beste Entscheidung fällen müssen (die besten Investitionen auswählen, die Kosten Ihres Unternehmens minimieren, die Vorlesungen herausfinden, die niemals morgens stattfinden, und so weiter). Bei Optimierungsmodellen stoßen Sie oft auf die Begriffe »minimieren« und »maximieren«, wenn es um Zielvorgaben geht.
In der Data Science bestehen viele Methoden wie künstliche Intelligenz, Data-Mining und Prognosen aus nichts anderem als aus einer Aufbereitung von Daten, die um einen Modellierungsschritt erweitert wird, bei dem es sich um ein Optimierungsmodell handelt. Aus diesem Grund wäre es sinnvoll, Ihnen zuerst einmal etwas über Optimierung zu erzählen. Es wäre nun aber extrem schwierig, auf der Stelle alles über Optimierung zu lernen, was Sie wissen müssten. Deshalb beschäftigen Sie sich in Kapitel 4 intensiv mit diesem Thema, nachdem Sie in den Kapiteln 2 und 3 auf angenehmere Weise Probleme des maschinellen Lernens gelöst haben. Aber um die Lücke wenigstens etwas zu füllen, sollen Sie hier ein wenig Erfahrung im Umgang mit Optimierungen sammeln. Nennen Sie es Schnupperkurs.
Optimierungsprobleme werden in Excel mit einem Add-In gelöst, das Solver heißt.
● Unter Windows kann es passieren, dass Sie Solver manuell aktivieren müssen, indem Sie auf DATEI (beziehungsweise in Windows 2007 auf die linke obere Windows-Schaltfläche) klicken. Dann wählen Sie OPTIONEN|ADD-INS und im Dropdownmenü VERWALTEN den Punkt EXCEL-ADD-INS. Klicken Sie nun auf die Schaltfläche GEHE ZU und überprüfen Sie das Kontrollkästchen vor SOLVER. Klicken Sie auf OK.
● Auf dem Mac wird Solver hinzugefügt, indem Sie zu EXTRAS|ADD-INS gehen und im Menü SOLVER.XLAM auswählen.
In jeder Excel-Version erscheint spätestens jetzt im Abschnitt ANALYSE der Registerkarte DATEN die Schaltfläche SOLVER.
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.21 Das 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.