Irgendwo in einem Büro … eher in vielen Büros. Ganz schnell sollen ein paar Dinge ausgewertet werden, dann auf in eine Tabelle damit und ganz wichtig: Das Ergebnis hat dann genau SO auszusehen. Eine Pivot-Tabelle wäre hier der einfachere Ansatz, aber wenn es dann doch mal ohne gehen muss, hat Excel auch hier passende Formeln parat. Die Beispieldatei habe ich wie immer angehängt.

Heruntergeladen werden kann sie hier: Qualitätsanalyse

Die Datentabelle

Die Tabelle umfasst zwei Originalspalten: die Mail-ID und das Thema. Die weiteren Spalten kamen zu Analysezwecken hinzu und mussten je nach Spalte mit x oder mit einer Zahl ausgefüllt werden. Der Einfachheit halber wurden die Daten als Tabelle formatiert, so dass Spalten auch über ihren Namen angesprochen werden können. Also Daten[Thema] statt B2:B51.

Datentabelle

Das fertige Dashboard sollte dann folgendermaßen gestaltet sein:

Dashboard

Mengen zählen mit ANZAHL und ZÄHLENWENN

Die einfachste Funktion des gesamten Berichts ist wohl die Funktion ANZAHL. Mit ihr zählen wir die Anzahl aller Zellen im Bereich, die eine Zahl enthalten.

Anzahl

Hinweis: Soll eine Spalte mit Textwerten gezählt werden, muss stattdessen ANZAHL2 verwendet werden.

Wer auf die grandiose Idee kam, die nächste Funktion ZÄHLENWENN statt ANZAHLWENN zu nennen, ist mir leider ein Rätsel. Fakt ist, wir müssen wohl damit leben. Im Prinzip macht die Funktion aber genau das gleiche, zählt aber nur die Zellen, in denen ein bestimmter Wert enthalten ist. In unserem Beispiel ist das „Technik“.

Zählenwenn

Ähnliches funktioniert übrigens auch mit Summen, denn genau für diesen Zweck gibt es die Funktion SUMMEWENN.

Summen bilden mit SUMMEWENN

Wenden wir uns nun zunächst der Detailanalyse unseres Technikbereichs zu. Wir wollen wissen, wie viele Rechtschreibfehler in den Mails gefunden wurden. Im Klartext also die Summe aller Werte aus der Spalte [Rechtschreibfehler], aber nur, wenn die Spalte [Thema] den Begriff „Technik“ enthält. In Excel ausgedrückt sieht das dann folgendermaßen aus:

Summewenn

Bis jetzt konnten wir alles mit eher einfach gestrickten Formeln erledigen, doch bei so vielen verschiedenen Möglichkeiten, die Daten zu untergliedern, ist selbstverständlich noch Luft nach oben. Was passiert zum Beispiel, wenn wir bei der Summierung noch mehr Faktoren eingrenzen wollen.

Summen bilden mit SUMMENPRODUKT

Einfache Anwendungesszenarien

Eines direkt vorweg, das SUMMENPRODUKT ist nicht das Produkt von Summen, sondern in der „Grundausführung“ die Summe von Produkten (man verzeihe mir diesen kurzen Ausflug in die Erbsenzählerei).

Was bedeutet das also ganz konkret. Zunächst die Voraussetzungen: Das SUMMENPRODUKT verlangt als Parameter mehrere Spalten; sinnvollerweise mindestens zwei. Wichtig ist, dass alle Spalten gleich lang sein müssen, da Excel ansonsten keine Berechnung durchführen kann. Was die Formel genau macht, schauen wir uns am besten direkt am Beispiel an:

summenprodukt_1

Ausgeschrieben würde die Berechnung dann folgendermaßen aussehen: (1*5) + (2*3) + (4*1). Jede zusätzliche Spalte wird nach dem gleichen Prinzip multipliziert.

Leider ist die Office-Hilfe bei der Beschreibung unvollständig, denn die Formel kann auch ohne Semikolon verwendet werden … und zwar wesentlich besser. Ersetzt man es also durch einen mathematischen Operator, wird stattdessen dieser zur Berechnung herangezogen. Hier ein Beispiel mit Quotienten:

summenprodukt_quotient

Ausgeschrieben: (1/5) + (2/3) + (4/1)

Komplexe Anwendungsszenarien

Das SUMMENPRODUKT kann aber noch mehr. Innerhalb der Formel können nicht nur Zahlen, sondern auch logische Ausdrücke ausgewertet werden. Das heiße Excel prüft eine Bedingung und wenn diese wahr ist, wird 1 zurückgegeben, ansonsten 0. Dazu auch direkt ein Beispiel:

summenprodukt_logik

Ausgeschrieben: (1*5) + (0*3) + (1*1)

Hinweis: Texte immer in Anführungszeichen.

Gleiches funktioniert natürlich auch mit Zahlenvergleichen. In meinem Datenbeispiel möchte ich zum Beispiel die Quote aller Technik-Mails berechnen, die mindestens einen Rechtschreibfehler enthalten.

summenprodukt_rechtschreibfehler_technik

Ist das Thema nicht Technik, wird automatisch eine 0 zurückgegeben und egal was in den anderen Zellen steht, das Produkt ergibt immer 0. Ergebnis: Die Zeile fließt nicht in die Berechnung mit ein. Auf diese Art und Weise lässt sich also mit Hilfe einer logischen Abfrage ein Zeilenfilter erstellen.

Der Vollständigkeit halber noch eine Zusatzinformation. In der obigen Formel haben mich nur die nicht-leeren Zellen der Spalte Daten[Thema] interessiert. Die Formel funktioniert also auch folgendermaßen:

=SUMMENPRODUKT((Daten[Thema]="Technik")*(Daten[Rechtschreibfehler]<>""))/F5

Weitere Anwendungsmöglichkeiten gibt es direkt in meiner Exceldatei und wie immer gilt:

Falls es hier Rückfragen gibt, die Kommentarfunktion ist aktiv 😉