SUMMENPRODUKT Teil 1 von 2

Die SUMMENPRODUKT Formel gehört zum 1 x 1 der Dynamischen Berichterstattung.

SUMMENPRODUKT erlaubt es aus einer Excel Tabelle Informationen herauszuziehen oder zu aggregierten als ob man SQL einsetzen würde. Gesamte Datenbereiche können problemlos zusammengefasst werden ohne Hilfstabellen, Zwischenschritte oder VBA einzusetzen. Weil die Formel so viel bietet habe ich diesen Artikel auf zwei Posts geteilt.

• In Teil 1 lernen wir wie SUMMENPRODUKT funktioniert.
• In Teil 2 zeige ich wie die Formel eingesetzt werden kann anhand einer Auswertung mit “echten” Daten vom Bundesamt für Statistik der Schweiz.

Teil 1

Um die Formel zu verstehen fangen wir mit einem sehr einfachen Beispiel an.

Wie der Name schon sagt, die Formel gibt das aufaddierte Resultat (eine Summe) von einer oder mehreren Multiplikationsrechnungen (Produkte) zurück. Die Syntax der Formel sieht so aus:

=SUMMENPRODUKT(Array1;[Array2];[Array3];…)

Die Arrays sind einfach die Bereiche welche ausgewählt werden können. Wie immer sind die [in eckige Klammern] gesetzte Argumente optional.

Beispiel A

Wählen wir nun also den Bereich B4:B7 im obigen Beispiel als Array 1 aus. Dieser setzt sich aus folgenden Werten zusammen: (10, 20, 30, 40)

Wenn wir nun Enter drücken, so bekommen wir das Resultat 100. Dies ist nichts anderes als die Summe in dem ausgewählten Bereich. “Aber dafür haben wir doch die SUMME Formel!”, sagst Du. Stimmt schon. Wenn wir nur einen Array benutzen so bringt SUMMENPRODUKT keinen Mehrwert.

Aber SUMMENPRODUKT macht eben im Hintergrund noch einen extra Schritt der nicht sofort erkennbar ist wenn wir nur einen Array auswählen.

Es multipliziert die eingegebenen Bereiche, entweder miteinander wenn es mehr als ein Arrays ist, oder eben mit 1, wenn es nur ein Array gibt.

Mit nur einem Array können wir uns die ausgeführte Berechnung so vorstellen:

(10 * 1) + (20 * 1) + (30 * 1) + (40 * 1) = 100

Im nächsten Beispiel wird die Multiplikation sofort deutlich!

Beispiel B

Hier ist das Resultat der Formel 600, denn… wir haben zwei Arrays.

Array1 * Array2 = (B4:B7) * (C4:C7) = (10, 20, 30, 40) * (2, 4, 6, 8) = 600

Die Zahlen werden im Array immer der Reihenfolge nach multipliziert. Also der Wert in Array1 der an der ersten Stelle steht wird mit dem Wert in Array2 multipliziert der ebenfalls an der ersten Stelle steht. Wir könnten die Formel auch so aufschreiben:

Array1 * Array2 = (10 * 2) + (20 * 4) + (30 * 6) + (40 * 8) = 600

Hierzu muss noch gesagt werden dass Arrays immer gleich groß sein müssen damit die Formel funktioniert. Die untenstehende Kombination liefert einen Fehler!

Array1 hat 4 Werte und Array2 nur 3. Das Resultat der Formel in Zelle E6 ist #WERT! Excel teilt uns mit dass hier ein Argument fehlt, nämlich die Zahl mit der 40 multipliziert werden soll.

Beispiel C

Jetzt gibt es noch Spalte A. Wenn Ihr genau hingeschaut habt so steht da nicht A, B, C, D sondern A, B, A, D. Das ist gewollt, denn jetzt wo wir das Basisfunktion der Formel verstehen, kommt der Interessante Teil.

Wir wollen nur die Zeilen multiplizieren und summieren welche in der ersten Spalte den Buchstaben A haben. Ihr seht, langsam geht es in Richtung Abfragen erstellen!

Die Formel sieht nun so aus:

=SUMMENPRODUKT(B4:B7;C4:C7;(A4:A7=”A”)*1)

Was die ersten beiden Arrays machen wissen wir schon. Fokussieren wir uns also auf die dritte Array welche neu hinzugekommen ist.

(A4:A7=”A”)*1

A4:A7 ist hier einfach wieder der Bereich (Array3). Dem setzen wir nun aber noch ein Gleichzeichen hinten dran und sagen es muss äquivalent sein zum Buchstaben A.

Mit anderen Worten, wir erstellen eine Abfrage die entweder Wahr oder Falsch ist. Da Excel beim Auswerten jede Zelle im Array3 gegen den Buchstaben A prüft, sieht das Resultat so aus:

A4:A7 = (A,B,A,D)
A4:A7 = (WAHR, FALSCH, WAHR, FALSCH)

Da man WAHR und FALSCH auch als Zahlenwerte ausdrucken kann könnten wir das Resultat auch so schreiben:

A4:A7 = (1,0,1,0)

Genau hier möchten wir hin. Excel liefert uns aber eben noch keine Zahlenwerte, sondern WAHR und FALSCH. In dem wir die WAHR und FALSCH Werte mit 1 multiplizieren verwandeln wir das Ergebnis in Zahlen. So wird aus WAHR eine 1 und FALSCH eine 0.

Dies ist eine Excel Besonderheit welche uns größere Flexibilität zulässt im auswerten von Formeln! Auf Papier aufgeschrieben sieht die ganze Formel neu folgendermaßen aus:

Array1 * Array2 * Array3 = (B4:B7) * (C4:C7) * ((A4:A7=”A”)*1) = 200

(10, 20, 30, 40) * (2, 4, 6, 8) * ((WAHR, FALSCH, WAHR, FALSCH)*1) = 200

(10 * 2 * 1) + (20 * 4 * 0) + (30 * 6 * 1) + (40 * 8 * 0) = 200

Im Teil 1 haben wir nun also gelernt dass wir mit der SUMMENPRODUKT Formel Abfragen erstellen können mit einem Kriterium. Ähnlich wie die SUMMEWENN Formel außer dass wir die Bereiche auch noch multiplizieren können. Hier hebt sich SUMMENPRODUKT nun aber von den anderen Formeln ab, denn mit SUMMENPRODUKT kann man bis zu 255 Arrays haben (in Excel 2007) und jeder Array kann eine Logische Abfrage enthalten!

Genau diese Anwendung schauen wir uns in Teil 2 an!

Weiter zu Teil 2