SUMMENPRODUKT Teil 2 von 2
Im Teil 1 dieses Posts haben wir gelernt wie SUMMENPRODUKT funktioniert und die Logik die dahinter steckt. Im zweiten Teil fokussieren wir uns auf die Anwendung anhand eines Beispiels mit einer größeren Datenmenge.
Teil 2
Damit wir nicht einfach mit erfundenen Informationen agieren habe ich vom Schweizerischen Bundesamt für Statistik eine Excel Datei heruntergeladen mit der Ständigen Wohnbevölkerung der Schweiz seit 1981 bis 2010. Die Informationen stehen der Öffentlichkeit zur Verfügung. Wer mag kann mit dem gleichen Datensatz den ich benutze also dem Beispiel folgen.
Hier meine Datei vom BSF zum mitmachen (so macht das Ausprobieren mehr Spass! :- )
Beispiel D
Wir möchten wissen wie viele Gemeinden es in der Schweiz gibt mit einer Bevölkerung von über 10’000 Nasen. Das können wir mit der SUMMENPRODUKT Formel ganz einfach herausfinden.
Die Formel lautet: =SUMMENPRODUKT((C$12:C$2598>=10000)*1)
Wir können die Abfrage mit sämtlichen Mathematischen Berechnungen ausführen welche mit WAHR und FALSCH beantwortet werden können. Im Beispiel ober wollten wir wissen welche Werte grösser oder gleich gross sind, was man in Excel so >= schreibt. Zuletzt multiplizieren wir das Ganze wiederum mit 1 damit wir die Anzahl der übereinstimmenden Werte erhalten!
Abfrage Varianten
Abfragen mit der SUMMENPRODUKT Formel kann man in verschiedenen Varianten erstellen. Sämtliche aufgeführten Formeln führen zum selben Ergebnis:
=SUMMENPRODUKT((Bevölkerung); (Gemeinde=”Scuol”)*1)
=SUMMENPRODUKT((Bevölkerung); N(Gemeinde=”Scuol”))
=SUMMENPRODUKT((Bevölkerung); – -(Gemeinde=”Scuol”))
=SUMMENPRODUKT((Bevölkerung); (Gemeinde=”Scuol”)+0)
…jede Variante zwingt Excel die Auswertung von Gemeinde=”Scuol” als Zahl zu berechnen. Wir können aber die ganze Formel auch in nur eine einzige Klammer packen. Dies ist meine bevorzugte Methode:
=SUMMENPRODUKT((Bevölkerung) * (Gemeinde=”Scuol”))
Zum Spass und um dies vorzuführen habe ich die Auswertung erweitert:
Die Formeln in der Spalte C lauten:
grösser 10’000 (Formel in C4)
=SUMMENPRODUKT((C$12:C$2598>=10000)*1)
von 4’000-9’999 (Formel in C5)
=SUMMENPRODUKT((C$12:C$2598>=4000)*(C$12:C$2598<10000)*1)
kleiner 500 (Formel in C6)
=SUMMENPRODUKT((C$12:C$2598<500)*1)
alle Gemeinden die mit “A” anfangen (Formel in C7)
=SUMMENPRODUKT((LINKS($B$12:$B$2598;1)=”A”)*1)
alle Gemeinden die “wil” im Namen enthalten (Formel in C8)
=SUMMENPRODUKT(ISTZAHL(SUCHEN(“wil”;$B$12:$B$2598))*1)
SUMMENPRODUKT in Kombination mit anderen Formeln
Bei den letzten beiden Varianten kommen andere Excel Formeln zum Einsatz. Die zweit letzte Abfrage mit der Funktion LINKS funktioniert ohne Problem.
SUCHEN hingegen liefert ein Fehlerwert wenn es alleine eingesetzt wird. Nur in der Kombination mit ISTZAHL liefert es ein Resultat. Dies ist weil SUCHEN ein Fehler erzeugt wenn der gesuchte Text nicht gefunden wird, was sich dann durch den Rest der Formel hindurchziehen würde. Mit der Zusatzabfrage ISTZAHL wandeln wir einen Fehler einfach in eine Null um.
Die WENN Formel funktioniert nicht in der Kombination mit SUMMENPRODUKT. Weshalb das so ist bleibt mir ein Rätsel. Glücklicherweise kann man aber sämtliche WENN Abfragen auch mit Hilfe von mathematischen Gleichungen machen.
Array Formeln Auswerten und Auditieren
Wer kennt das nicht? Die Formel liefert ein Fehlerwert und sämtliche Parameter sind / scheinen korrekt eingegeben zu sein. Die Syntax der Formel ist ok und die Bereiche sind auch die Richtigen. Trotzdem bekommt man nur #WERT! Zum Haare ausreisen!
Zum Glück gibt es noch den Formelauswertung Knopf im Excel Menü. Leider ist Microsoft das Pop-Up Fenster ein wenig klein geraten bei längeren Formeln, was Array Formeln ja immer sind, und lässt sich auch nicht anpassen.
Man kann Array Formeln aber auch mit F9 auswerten! Wenn man die genaue Referenz des Array in der Formel auswählt…
…und F9 drückt…
…werden die einzelnen Werte des Arrays angezeigt! Um den original Zustand wieder herzustellen muss man diese Auswahl aber mit der ESC Taste Rückgängig machen. Tut man das nicht bleiben die Werte angezeigt ohne dass dies Rückgängig gemacht werden kann!
SUMMENPRODUKT vs. MATRIX Formeln
Matrix Formeln (die mit STRG+SHIFT+RETURN eingegeben werden) sind ein Kapitel für sich. Ich will hier jedoch kurz auf den Unterschied von Matrix Formeln und SUMMENPRODUKT eingehen, denn mit Matrix Formeln kann man sehr vieles machen was SUMMENPRODUKT kann.
Meines Erachtens gibt es einen wesentlichen Unterschied der zu beachten ist, insbesondere für die Erstellung von Berichten:
- Matrix Formeln sind immer in einem Array
Arrays können nur als ganzes bearbeitet werden. Zeilen einschieben oder löschen geht also nicht. Formeln verschieben geht auch nicht. Eine einzelne Formel bearbeiten fällt ebenfalls weg.
Beim erstellen von dynamischen Excel Berichten oder Modellen sind das eher mühsame Einschränkungen.
Nachteile der SUMMENPRODUKT Formel
- Langsame Berechnung der Daten: Wer eine sehr große Tabelle hat in der er die Formel nach unten kopiert wird feststellen sein Excel plötzlich ein wenig langsamer reagiert. Je nach Rechner, CPU, und der Komplexität der Abfrage habe ich schon bei ein paar tausend Datensätzen eine erhebliche Verzögerung in der Berechnung festgestellt. Spätestens ab 100’000 Datensätzen fängt Excel aber an zu Stocken.
- Dateigrösse: Wird die Formel in einer Tabelle nach unten kopiert so wird die Datei rasant grösser.
Ein Trick der Abhilfe schafft: die Formel in der obersten Zeile stehen lassen, sämtliche Werte unterhalb kopieren und als Werte einfügen. So bleibt die Formel für den späterem Bedarf und zur auditierbarkeit erhalten ohne dass Excel die Berechnung in jeder Zelle ausführt oder die ganze Formel speichern muss.
Fazit:
Die SUMMENPRODUKT Formel ist ein äusserst Flexible und eignet sich für hervorragend für diverse Aufgaben. Für mich gehört diese Formel zum 1 x 1 der flexiblen Excel Modellierung und dynamischer Berichterstattung.
Für Anregungen, Ergänzungen, oder Kommentare ob dieser Artikel hilfreich war bin ich Euch dankbar!
Man kann die einzelnen Arrays aber nicht nur multiplizieren, sondern auch summieren, subtrahieren oder dividieren, wenn man in der Formel zwischen den Arrays anstatt dem “;” die entsprechenden Rechenzeichen setzt.
Danke für die Ergänzung! Stimmt natürlich. Ich benutze Summenprodukt meistens nur zum multiplizieren. Wenn ich addiere etc. nehme ich dann eine normale Matrix Formel. Wo setzt Du es ein in Deiner Arbeit?
Ich setzte es momentan auch nur zum multiplizieren ein, die klassische Anwendung.
Moin Lukas, es könnte/sollte uU noch ergänzt werden, dass die Verwendung von “sumproduct” als call-up bzw. Summary-Funktion nicht zwischen Groß-und Kleinschreibung unterschiedet.
Wer darauf wert legt, muesste dann den Weg “step-bystep” verfolgen und die jeweilige Bedingung Zeile für Zeile prüfen und die “true-cases” anschliessend per +countif() durchzählen
VG Henning
Moin Lukas, eine weitere Sache kommt mir gerade noch in den Sinn:
Bei der Verwendung des sumproduct als Call-Up, quasi als Erweiterung von “+index( match() ;match() )” , um mehr als 2 Dimensionen ins Kalkül zu ziehen, bzw. von den auch hier bereits beschriebenen logischen Bedingungen gebrauch zu machen…, darf in dem Array, aus dem heraus der Wert aufgerufen werden soll, auf keinen Fall irgendein (!!) Text stehen…. ansonsten –> #VALUE! :(
VG Henning
SUMMENPRODUKT ist eine flexible Funktion, aber für die Anzahl nicht so bedeutend, hier würde ich doch eher auf Zählenwenn(s) gehen:
Statt:
=SUMMENPRODUKT((C$12:C$2598>=10000)*1)
bevorzuge ich
=ZÄHLENWENN(C$12:C$2598;”>=10000″)
und statt:
=SUMMENPRODUKT((C$12:C$2598>=4000)*(C$12:C$2598=4000″;C$12:C$2598;”<10000")
Ist vielleicht Geschmacksache oder gibt es einen besonderen Grund, warum Summenprodukt hier besser ist?
Spätestens hier wird es aber doch deutlich einfacher:
=SUMMENPRODUKT((LINKS($B$12:$B$2598;1)="A")*1)
wird zu
=ZÄHLENWENN($B$12:$B$2598;"A*")
und:
=SUMMENPRODUKT(ISTZAHL(SUCHEN("wil";$B$12:$B$2598))*1)
wird zu
=ZÄHLENWENN($B$12:$B$2598;"*wil*")
Ups, Fehler beim abschicken (???):
statt:
=SUMMENPRODUKT((C$12:C$2598>=4000)*(C$12:C$2598=4000″;C$12:C$2598;”<10000")
Irgendwas stimmt da nicht mit dem Abschicken, dabei wird was rausgeschnitten.
ZÄHLENWENNS(C$12:C$2598;”>=4000″;C$12:C$2598;”<10000")
Hi Anonymous, ich bin einverstanden dass es heute mit ZÄHLENWENNS, etc. einfacher geht. Ich habe den Beitrag geschrieben als Excel 2010 das “neuste” war. Kurze Zeit später kam dann Excel 2013, mit eben diesen Funktionen. Tatsächlich war SUMMENPRODUKT für mich früher neben dem SVERWEIS eine Power Formel die ich überall eingesetzt habe. In den letzten 7 Jahren habe ich es nur noch sehr spärlich eingesetzt… und inzwischen fast nicht mehr!