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!