Neo lässt grüßen: Die Matrix in Excel 5

Was will uns diese Blog-Überschrift sagen? Nun, es gibt in Excel sogenannte Matrix-Funktionen. Eine davon möchte ich heute vorstellen: SUMMENPRODUKT().

Diese mächtige Funktion ist leider bei vielen Anwendern nicht oder nur wenig bekannt. Das liegt vermutlich daran, dass sich der Nutzen und die Anwendungsmöglichkeiten nicht auf den ersten Blick erschließen.

Das möchte ich heute ändern: Im folgenden Artikel werde ich dir zeigen, wie SUMMENPRODUKT() funktioniert und was man so alles damit anstellen kann. Aha-Erlebnisse sind garantiert!

Und so geht’s:

Einfache Anwendung

Zunächst für alle, denen die SUMMENPRODUKT-Funktion nicht so gut vertraut ist, eine kleine Einführung/Auffrischung.

Allgemein gesprochen werden mit SUMMENPRODUKT() die zusammengehörenden Elemente zweier Matrizen miteinander multipliziert und die einzelnen Ergebnisse anschließend aufsummiert.

Folgendes einfache Beispiel soll das verdeutlichen. Wir haben eine Produkttabelle mit verschiedenen Artikeln und den verkauften Stückzahlen. In einer zweiten Tabelle sind die Einzelpreise der Artikel gespeichert. Nun wollen wir mit Hilfe von SUMMENPRODUKT() den Gesamtumsatz ermitteln:

Beispiel 1: Einfache Produktbildung

Die Funktion multipliziert also jeden Wert in der blau markierten Spalte mit dem dazugehörigen Wert in der grün markierten Spalte und addiert am Ende die einzelnen Produkte auf. Mit „herkömmlichen“ Mitteln ist dazu etwas mehr Tipparbeit erforderlich, wie man in der nebenstehenden Kontrollrechnung im Screenshot sehen kann.

Zugegeben, das war noch nicht so richtig spannend. Daher nehmen wir im zweiten Schritt noch eine Tabelle dazu, in der die unterschiedlichen Frachtkosten je Artikel aufgelistet sind. Die Rechnung für jeden Artikel lautet also MENGE * (EINZELPREIS + FRACHTKOSTEN):

Beispiel 2: Zusätzliche Parameter

Auch hier sieht man, dass man mit der SUMMENPRODUKT-Funktion mit einer einzigen Formel das gleiche Ergebnis erzielt wie mit vielen einzelnen herkömmlichen Multiplikationen.

Fortgeschrittene Anwendung

Kommen wir jetzt zu einem erweiterten Beispiel. Ihr wahres Potential spielt die Funktion SUMMENPRODUKT() aus, wenn man mit Bedingungen arbeitet.

Wir nehmen wieder die Umsatzberechnung aus Beispiel 1, haben aber diesmal zusätzlich noch die Vertriebsregion angegeben. Neben der Ermittlung des Gesamtumsatzes wollen wir jetzt den Umsatz für eine einzelne Region sehen. Die Werte sollen also nur dann multipliziert und addiert werden, wenn als Bedingung „Region = Nord“ erfüllt ist:

Beispiel 3: Zusätzliche Bedingung

Was macht die Funktion jetzt genau?
Im ersten Teil der Formel wird geprüft, ob der Wert in Spalte B „Nord“ enthält. Das Ergebnis ist ein Wahrheitswert WAHR (Zelle enthält „Nord“) oder FALSCH (Zelle enthält nicht „Nord“). Auf unser Beispiel angewendet sieht das Ergebnis des ersten Arrays so aus:

B40:B49 = (WAHR, FALSCH, FALSCH, FALSCH, WAHR, FALSCH, FALSCH, WAHR, FALSCH, FALSCH)

Damit Excel mit WAHR und FALSCH weiterrechnen kann, multiplizieren wir diese Wahrheitswerte mit 1. Damit wird aus WAHR = 1 und aus FALSCH = 0. Der Rest der Formel ist genau wie in den vorangegangenen Beispielen und somit nichts Neues mehr.

Bezogen auf unser Beispiel im Screenshot wird aus Zeile 40 also 1 * 6 * 74 = 444, aus Zeile 41 wird 0 * 9 * 94 = 0 und so weiter.

Mit der vielleicht naheliegend erscheinenden SUMMEWENN-Funktion wären wir hier nicht so ohne weiteres zum Ziel gekommen, da Menge und Einzelpreis ja zuerst ausmultipliziert werden müssen.

Und zum Schluss die Kür

Wer bis hierher durchgehalten hat, dem zeige ich noch ein letztes Beispiel für die Anwendung von SUMMENPRODUKT().

Wie auch schon im vorangegangenen Beispiel werden wir wieder mit Bedingungen arbeiten. Diesmal wollen wir eine Tabelle auswerten, in der 20 Schüler mit Alter und Geschlecht sowie der gewählten Sportdisziplin aufgelistet sind. Diese Information wollen wir mit Hilfe von SUMMENPRODUKT() in der zweiten Tabelle rechts verdichten:

Und so sieht das Ergebnis aus. Ich habe zur besseren Nachvollziehbarkeit die Zellen farbig markiert:

Sehen wir uns beispielhaft die einzelnen Elemente der SUMMENPRODUKT-Formel in Zelle H5 an:

($C$5:$C$24>=12):
Prüft, ob das Alter des Schülers 12 Jahre oder mehr ist. Größer oder gleich 12 = 1, kleiner 12 = 0.

($C$5:$C$24<=13):
Ist das Alter <= 13? Kleiner oder gleich 13 = 1, größer 13 = 0

($D$5:$D$24=“m“):
Handelt es sich um einen männlichen Schüler? m = 1, w = 0

($E$5:$E$24=$G5):
Ist die Disziplin Laufen (in G5 steht „Laufen“)? Laufen = 1, alles andere = 0

Somit lautet das Ergebnis für den ersten Schüler der Tabelle:
0 (da älter als 13) * 0 (da weiblich) * 0 (da Hochsprung) = 0

Für den Schüler 3 sieht die Rechnung so aus:
1 (da 13 Jahre) * 1 (da männlich) * 0 (da Hochsprung) = 0

Erst beim Schüler 20 passen alle 3 Kriterien, so dass hier der Wert 1 ermittelt wird.

 
Ich weiß, dass diese Funktion am Anfang ein schwerer Brocken ist (und für die Schlaumeier: Ja, natürlich hätte man das Ganze auch elegant mit einer Pivot-Tabelle hinbekommen, aber darum geht es heute nicht).

Wenn man sich aber erst einmal die Funktion herangewagt und ein wenig damit experimentiert hat, wird man schnell merken, welche Möglichkeiten sich damit plötzlich ergeben.

Vielleicht sind dir noch weitere gute Beispiele für SUMMENPRODUKT() eingefallen? Dann lass‘ es uns in den Kommentaren wissen.

Das könnte dich auch interessieren:

Wenig(er) bekannte Datumsfunktionen

Wieviele sind das eigentlich?

Das Schweizer Taschenmesser in Excel: SVERWEIS!

Links, rechts, vor, zurück…