Die Mathematik-Funktion
SUMMENPRODUKT()
Beschreibung
Die Funktion SUMMENPRODUKT() ist eigentlich ganz einfach und dennoch schwierig. Der Widerspruch in sich liegt darin begründet, dass ein großer Teil der User nicht gerade zu den Mathematik-Fans gehört und die Beschreibungen in der Excel-Hilfe nicht wirklich so sind, dass sie auf den ersten Blick verständlich sind.
Ganz kurz zusammengefasst: Hiermit werden gleich große und auch gleich ausgerichtete Bereiche zeilen- oder spaltenweise einzeln multipliziert und die Ergebnisse dann addiert. Diese Bereiche werden als Matrix bezeichnet. Das ist die Grundform, die aber durch verschiedene Tricks erheblich an Funktionalität gewinnen kann.
In verschiedenen Abschnitten werden wir Ihnen Beispiele aufzeigen, wie mächtig diese Funktion ist. Und wenn Sie einmal ein gewisses Maß an Sicherheit in Sachen Umgang mit der Funktion SUMMENPRODUKT haben, dann werden Sie diese gewiss recht oft einsetzen, um rasch und sicher zum Ziel zu gelangen.
Syntax
=SUMMENPRODUKT(Matrix1; Matrix2; [Matrix…]
Argument(e)
Matrix1 .. Matrix_n sind mindestens zwei Matrizen, wo die Elemente erst multipliziert und anschließend alle Ergebnisse addiert werden. Es können bis zu 255 einzelne Matrizen angegeben werden.
Hinweise
Die jeweils als Argument übergebenen Matrizen müssen bezüglich der Zeilen- und Spaltenzahl identisch sein. Ist das nicht der Fall, wird der Fehler #WERT! zurück gegeben.
Enthält ein Element einer Matrix einen Wert, der nicht numerisch ist und auch nicht so interpretiert werden kann, dann wird statt des Zellinhaltes der Wert 0 (Null) verwendet.
Beispiel(e)
Einige grundlegende Beispiele finden Sie in dieser Datei in den Arbeitsblättern Summenprodukt (1) bis Summenprodukt (3). Wegen der Komplexität des Themas wird dieses Thema (in naher Zukunft) in einem gesonderten Beitrag behandelt werden. Hier nun Erklärungen und Hinweise zu den einzelnen Arbeitsblättern:
Summenprodukt (1)
Dieses Arbeitsblatt ist ursprünglich für unsere Seminare als Mustertabelle für die Absolute Adressierung erstellt worden, aber da die Daten so schön übersichtlich sind, packe ich die Gelegenheit beim Schopf und nutze die Daten auch hier.
Ihnen werden gewiss zwei Dinge sofort ins Auge fallen: Der Bereich E6:F8 ist nicht ausgefüllt und in G6:G9 stehen schon die Ergebnisse drin. Na ja, die Spalte G dient nur der Kontrolle für all diejenigen von Ihnen, die fremden oder fremdartigen Formeln gegenüber erst einmal misstrauisch sind. Und der Bereich, wo im Normalfall die berechneten Werte eingetragen werden, ist und bleibt leer, denn die Berechnung soll ja mit einer einzigen Formel durchgeführt werden. Und genau die steht in F9:
=SUMMENPRODUKT(C6:C8; D6:D8)
Da das Ergebnis stimmt, muss die Formel ja wohl auch richtig sein. Die beiden Matrizen sind in Spalte C und Spalte D und enthalten diese Werte:
| Matrix 1 | Matrix 2 |
| 10 | 1,82 |
| 25 | 0,62 |
| 3 | 35,00 |
Excel rechnet nun folgendermaßen: Erst wird das Produkt (Multiplikation) von C6 und D6 gebildet, also C6*D6 als Rechenoperation. Excel „merkt” sich dieses Ergebnis. Danach wird in den restlichen Zeilen (also Zeile 7 und Zeile 8) der gleiche Vorgang durchgeführt. Nun wird von allen Produkresultaten die Summe gebildet und dieses Ergebnis wird in F9 ausgegeben. Die komplette Rechenoperation sieht in diesem Fall so aus: (10*1,82)+(25*0,62)+(3*35) was ja genau dem entspricht, was typischerweise auch per Hand erledigt wird, wenn in den einzelnen Zeilen die Berechnungen durchgeführt werden. Siehe Spalte G als Kontrollspalte.
Bedenken Sie, dass es sich hier um eine Matrix-Formel handelt, auch wenn Sie die Formel ganz normal mit Eingabe abschließen und nicht automatisch die {geschweiften Klammern} um die Formel gelegt werden. Der Hinweis ist dann wichtig, wenn Sie auf der Suche nach Geschwindigkeitsbremsen sind.
Summewenn (2)
Hier geht es darum, dass der in D3 eingegebene Rabatt gleich mit berechnet wird. In der ursprünglichen Aufgabe (also ohne Verwendung von Summenprodukt()) war es das Ziel, die Zelle mit dem Rabatt als absoluten Wert einzugeben, also $D$3. Wenn ich diesen Wert nun in die Funktion einbinde, dann müssten eigentlich auch entsprechend der Zeilenzahl der einzelnen Matrix drei Rabattwerte untereinander stehen. Aber es geht auch so:
=SUMMENPRODUKT(C6:C8; D6:D8)*(1‑D3)
Natürlich werden Ihnen zwei Dinge aufgefallen sein: Die Zelladresse für den Rabatt ist nicht absolut eingegeben sondern als relative Adressierung und dass die SUMMENPRODUKT-Formel an sich nicht verändert worden ist, der Rabatt-Abzug wurde nur „angehängt”.
Bei der Gelegenheit: Wenn Ihnen der Abzug des Rabatts zu intransparent ist, dann geben Sie die Formel gerne so ein:
=SUMMENPRODUKT(C6:C8; D6:D8)*(100%-D3)
dann ist das Verstehen der Formel vielleicht für alle Anwender etwas leichter. Das Ergebnis und die Funktionalität ist identisch. Auch hier ist zu Ihrer Kontrolle in Spalte G eine „klassische” Berechnung durchgeführt worden.
Summewenn (3)
Jetzt wird es wirklich „interessant”. Die Aufgabe: Wenn von einem Artikel mehr als 20 Einheiten verkauft wurden, dann gibt es für diesen Artikel zusätzlich noch einmal 5% Nachlass. Nehmen Sie sich gerne einmal die Formeln in der Kontrollspalte (H) zu Herzen, denn das ist ja der herkömmliche Weg und zeigt ja auch das korrekte Endergebnis. Und statt der N()-Funktion können Sie dort auch ..-((C7>20)*1).. einsetzen oder auch ein WENN()-Konstrukt.
Hier noch einmal die konkrete Formel für F9 (gehört alles in 1 Zeile):
=SUMMENPRODUKT(C6:C8; D6:D8) * (1‑D3) – SUMMENPRODUKT(C6:C8; D6:D8; N(C6:C8>20) * 5%)
Und da beginnt schon die hohe Schule. Lassen Sie uns die Formel einmal analysieren. Der erste Teil =SUMMENPRODUKT(C6:C8;D6:D8) * (1‑D3) ist praktisch identisch zu der in vorherigen Tabelle erarbeiteten Formel. Der eventuelle einzige klitzekleine Unterschied: statt (100%-D3) steht nun (1‑D3) dort. Und das ist aus mathematischer Sicht identisch, denn Excel bewertet die 1 als 100%.
Zur Erinnerung: Bis jetzt haben wir das normale Ergebnis mit Abzug des Skontos, im Beispiel 3%. Jetzt soll noch von allen Artikeln, wo mehr als 20 Stück bzw. Einheiten gekauft worden sind, ein zusätzlicher Rabatt von 5% des bisher errechneten Preises für diesen Artikel abgezogen werden. Und das geschieht in diesem Teil:
- SUMMENPRODUKT(C6:C8; D6:D8; N(C6:C8>20) * 5%)
Es wird noch einmal das SUMMENPRODUKT gebildet, nun aber mit drei Argumenten. die ersten beiden Parameter sind bekannt: Anzahl * Einzelpreis. Das dritte Argument der Funktion N(C6:C8>20) * 5% ist etwas „tricky” gestaltet. Von innen nach außen betrachtet ist es leichter zu verstehen: C6:C8>20 ist ein Ausdruck, der entweder WAHR oder FALSCH ist. C6 .. C8 ist entweder größer als 20 oder nicht. Die Funktion N() dient dazu, das übergebene Argument in eine Zahl, einen numerischen Wert umzuwandeln. Das bedeutet, dass ein WAHR zu einer 1 und eine FALSCH zu einer 0 wird. Immer noch im dritten Argument wird nun diese numerische Auswertung mit 5% (oder 0,05) multipliziert.
In Zeile 6 würde das bedeuten: C6 ist ist mit 10 nicht größer als 20 und somit wird 0*5% gerechnet, was ja auch Null ergibt. In C7 ist der Wert 25, also größer als 20. Da wird dann 1*5% für die Auswertung angewendet. Und in Zeile 8 sind es dann wieder 3 Torten, die keinen Rabatt bekommen.
Jetzt noch einmal zum langsamen Nachvollziehen die einzelnen Argument-Blöcke des zweiten Summenprodukts, nur auf die Zeile 7 zusammen gekürzt: C7; D7; N(D7>20) * 5% was rechnerisch so aussieht: 25*0,62*1*5%. Oder wenn es weniger als 21 Roggenbrötchen gewesen wären, dann könnte die Berechnung beispielsweise so aussehen: 15*0,62*0*5%.
Im ersten Fall wären das dann 0,78€, im zweiten Fall jedoch 0,00 €, denn die Multiplikation mit 0 führt zum Gesamtergebnis Null. – Das Gesamtergebnis dieses Summenprodukts wird vom im ersten Teil der Formel berechneten Gesamtpreis abzüglich Skonto abgezogen und somit entsteht der neue Netto-Preis. Es sei noch einmal darauf hingewiesen: Die Preisberechnung erfolgt absolut ohne die Werte in den Spalten G:I sondern ausschließlich auf der Basis D3; C6:D8.
Schauen Sie für weitere Beispiel auch gerne einmal hier im Blog nach, dort werden nach und nach gut nachvollziehbare Aufgaben aus der Praxis diskutiert.
[NachObenLetzte Verweis=„Fn: SUMMENPRODUKT()”]