Autor: Peter Haserodt  --- Aus Excel Standard - Gruppe: Funktionen

Summenprodukt

Autor: Peter Haserodt - Erstellt: ?      - Letzte Revision: ?
Summenprodukt - Eine Funktion der besonderen Art

Hinweis:

Sie sollten die Grundlagen von Matrizen und Logischen Werten verstehen.
Diese werden ausführlich im Matrix-Tutorial startend mit: Matrix Station 1 ... erklärt.

Aus dieser Vorbemerkung kann man schon sehen, dass SummenProdukt matrixorientiert ist.
Sie ist eine Matrixfunktion (Was man aber nicht mit einer Matrixformel verwechseln sollte)

Am Anfang denkt man, dass diese Funktion so nützlich ist wie ein Kropf.
Denn wozu braucht man sowas:
 
 ABCDE
1     
2 10220 
3 1003300 
4   320 
5     
6 Als Summenprodukt  
7 320   
8     
Formeln der Tabelle
D2 : =B2*C2
D3 : =B3*C3
D4 : =SUMME(D2:D3)
B7 : =SUMMENPRODUKT(B2:B3;C2:C3)
 

Vielleicht in irgendwelchen besonderen Berechnungsarten aber der Sinn verschließt sich erstmal.

Anhand des Beispieles können wir aber die Funktionsweise nachvollziehen:
Summenprodukt multipliziert die Matrizen (wie immer Positionsbezogen) und summiert die Ergebnisse dieser Produkte.
In unserem Fall werden die Elemente der grünen Matrix mit den Elementen der blauen Matrix multipliziert, wie aufgezeigt.

Tatsächlich wird das Ganze richtig interessant in Verbindung mit Wahrheitswerten!

Blicken wir auf ein Beispiel mit Summenprodukt im Einsatz:
 Peter Haserodt
  • Formelhilfe
  • Makroentwicklung
  • VBA-Programmierung
  • + + + + + + + + +
 
 ABCDE
2     
3 ArtRestwertFarbe 
4 PKW11.000,00Blau 
5 LKW15.000,00Grün 
6 PKW8.000,00Rot 
7 LKW9.000,00Blau 
8 PKW14.000,00Rot 
9 LKW30.000,00Blau 
10 PKW11.000,00Grün 
11 LKW15.000,00Blau 
12 PKW8.000,00Blau 
13 LKW9.000,00Grün 
14 PKW14.000,00Rot 
15 LKW30.000,00Blau 
16 PKW20.000,00Blau 
17     
18 Anzahl PKW Restwert > 10000 5 
19 Anzahl PKW Restwert > 10000 und Farbe Blau 2 
20     
21 Anzahl PKW mit Summenprodukt 7 
22     
Formeln der Tabelle
D18 : =SUMMENPRODUKT((B4:B16="PKW")*(C4:C16>10000))
D19 : =SUMMENPRODUKT((B4:B16="PKW")*(C4:C16>10000)*(D4:D16="Blau"))
D21 : =SUMMENPRODUKT((B4:B16="PKW")*1)
 

Was die einzelnen Formeln ermitteln, sieht man auf Anhieb.
Die Funktionsweise ist aber näher zu beleuchten:
Wir erzeugen Wahrheitswerte und in deren Produkt kann nur 0 oder 1 herauskommen.
(Man beachte in der Formel in D21 die Multiplikation mit 1, um den Wahrheitswert als Zahl zu erhalten)


Wie dies nun wirklich funktioniert, soll die nächste Tabelle verdeutlichen:
(Die Formeln in den Zeilen 5 bis 16 wurden weggelassen, diese können Sie einfach aus Zeile 4 herunterziehen)
 
 ABCDEFGHIJK
2     Hilfstabelle zur Erklärung
3 ArtRestwertFarbe       
4 PKW11.000,00Blau 111 11
5 LKW15.000,00Grün 010 00
6 PKW8.000,00Rot 100 00
7 LKW9.000,00Blau 001 00
8 PKW14.000,00Rot 110 10
9 LKW30.000,00Blau 011 00
10 PKW11.000,00Grün 110 10
11 LKW15.000,00Blau 011 00
12 PKW8.000,00Blau 101 00
13 LKW9.000,00Grün 000 00
14 PKW14.000,00Rot 110 10
15 LKW30.000,00Blau 011 00
16 PKW20.000,00Blau 111 11
17         52
Formeln der Tabelle
F4 : =(B4="PKW")*1
G4 : =(C4>10000)*1
H4 : =(D4="blau")*1
J4 : =F4*G4
K4 : =F4*G4*H4
J17 : =SUMME(J4:J16)
K17 : =SUMME(K4:K16)
 


In den Spalten F bis H erzeugen wir Nullen und Einsen für die jeweiligen Prüfungen.
Z.B. in Spalte F ob in Spalte B das Wort PKW steht.
(Den Wert erhalten wir durch die Multiplikation mit 1, sonst würde immer WAHR oder FASCH dort stehen)


Spalte J ist nun unser Beispiel für PKW'S > 100000 Restwert und Spalte K auch noch mit Farbe Blau.

Sie sehen, wir haben in den Summenproduktformeln nichts anderes getan, als hier mit Hilfszellen erreicht wurde.
(Vielleicht etwas eleganter)

Ich denke, wenn man die Beispiele nachbaut und studiert, sollte sich Summenprodukt weitgehend erschließen aber trotzem noch eine Kombination von Wahrheitswerten und Zahlenwerten als Beispiel:

Ergänzen Sie die Beispieltabelle in Zeile 23 noch wie folgt:
 
 ABCD
23 Restwerte aller PKW's mit blauer Farbe: 39000
Formeln der Tabelle
D23 : =SUMMENPRODUKT((B4:B16="PKW")*(D4:D16="Blau")*(C4:C16))
 

Hier wird nun die Summe aller Restwerte der blauen PKW's ermittelt.
Die Logik hinter der ganzen Sache ist wie oben:
Ich erhalte Multiplikationen der Art:
a*b*c wobei c hier unser Wert in der Matrix der Restwerte ist.
a und b sind Wahrheitswerte. Wird einer dieser beiden falsch - also 0 - so wird das Produkt 0.
Ich summiere am Ende also nur die Werte, in welchen unsere Bedingungen zutreffen.

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben