ZÄHLENWENN und ZÄHLENWENNS solltest Ihr nicht ungenutzt lassen, wenn Ihr große Datensätze auswerten wollt. Ich möchte dazu in diesem Beitrag ein Beispiel mit Kundenstammdaten verwenden und versuchen, einige Antworten zu finden: Wie viele Kunden nutzen Produkt A des Unternehmens beziehungsweise Produkt B oder beide Produkte zusammen? Diese Daten sollen dann letztendlich noch nach Altersgruppen aufgeschlüsselt werden. Mit ZÄHLENWENN und ZÄHLENWENNS können derartige Aufgaben in wenigen Rechenschritten gelöst werden, und das größtenteils unabhängig von der zu verarbeitenden Datenmenge.

So wird die Formel gebildet

Die Excel-Datei mit dem Beispiel steht hier als Download zur Verfügung:

1. Die Excel-Funktionen

ZÄHLENWENN ermittelt die Anzahl der Zellen in einem Bereich, deren Inhalt ein bestimmtes Kriterium erfüllt. Der Syntax:

=ZÄHLENWENN(Bereich;Suchkriterium)

Bereich beschreibt einen zusammenhängende Zellbereich, in dem die Prüfung stattfinden soll. Für das Suchkriterium wird ein Vergleich festgelegt (Gleichung, Ungleichung). Es können z.B. Textstrings, Zahlenwerte oder Kalenderdaten mit einbezogen werden. ZÄHLENWENN(A1:B5;“=Excel“) gibt für den Zellbereich A1:B5 die Anzahl der Zellen aus, deren Inhalt der Zeichenkette Excel entspricht. Ist das Suchkriterium textbasiert, kann außerdem mit den Platzhaltern (?) und (*) gearbeitet werden. Das (?) lässt ein Zeichen im Suchkriterium als Variable stehen. ZÄHLENWENN(A1:A5;“=???“) zählt dann also die Zellen im Bereich A1:A5, deren Inhalt einem Textstring mit 3 Zeichen entspricht. Dahingegen repräsentiert (*) eine Zeichenkette von beliebiger Länge. Mit diesem Platzhalter arbeitet ZÄHLENWENN wie eine Zelle-enthält-Text-Prüfung. Falls dir (?) und (*) jetzt fehlen, hier kommt die Entwarnung: setze einfach eine Tilde (~) vor das Zeichen, und (?) oder (*) werden stattdessen wie normale Zeichen behandelt.

ZÄHLENWENNS ist der große Bruder von ZÄHLENWENN. Die Funktion prüft mehrere Zellbereiche parallel. Die Bereiche müssen dazu nicht undbedingt benachbart sein. Die Zeilen- und Spaltenzahl der zusätzlichen Bereiche muss allerdings mit Kriterienbereich1 identisch sein.

ZÄHLENWENNS(Kriterienbereich1;Kriterien1;[Kriterienbereich2; Kriterien2]…)

2. Ein einfaches Beispiel zum Einstieg

ZÄHLENWENNS gibt die Anzahl der Zeilen an, für die alle zuvor festgelegten Kriterien gleichermaßen erfüllt sind, wobei jeweils das Prüfkriterium X auf Zellbereich X angewandt wird. Dazu ein einfaches Beispiel:

=ZÄHLENWENNS(A2:A6;”=Pizzateig”;B2:B6;”=Tomatensauce”;A9:A13;”=Mozzarella”;B9:B13;”=Basilikum”)

Bei 2 der 5 Pizzen handelt es sich um eine Pizza Margherita. Nur wenn die passende Zutatenreihe vorliegt (Pizzateig – Tomatensauce – Mozzarella – Basilikum) wird die zugehörige Zeile gezählt. Die einzelnen Wertebereiche müssen nicht benachbart sein und dürfen sogar über das Tabellenblatt verteilt sein. Die Spalten- und Zeilenzahl der Kriterienbereiche müssen jedoch immer mit Kriterienbereich 1 identisch sein, sodass sie sich gedanklich zu einer zusammenhängenden Matrix verbinden lassen.

3. Der Anwendungsfall – Kundendaten in einem Unternehmen

Mit dieser Einleitung möchte ich zum anfangs genannten Anwendungsfall übergehen: Ein Telekommunikationsunternehmen hat die Produkte Internet und Mobilfunk im Portfolio. Nun soll der Kundenstamm analysiert werden.

Zum Einstieg eine einfache Prüfung über ZÄHLENWENN – wie viele Kunden sind bis 25 Jahre alt?

=ZÄHLENWENN(B3:B27;”<=25″)

Das Alter der Kunden steht in Spalte B. Der Bereich wird also von B3 bis B27 definiert. ZÄHLENWENN gibt immer eine Anzahl an. Im Beispiel sind 7 Kunden nicht älter als 25 Jahre.

Interessanter wäre vielleicht noch zu wissen, welcher Kundentyp welche Produkte bevorzugt. Dazu gliedern wir die Kunden in Altersklassen zu je 5-Jahres-Intervallen auf. Mit der folgenden Formel wird berechnet, wie viele Kunden im Alter zwischen 21 und 25 Jahren das Produkt Internetanschluss nutzen:

=ZÄHLENWENNS(B3:B27;”<=25“;C3:C27;”=Ja”)-ZÄHLENWENNS(B3:B27;”<=20“;C3:C27;”=Ja”)

Eine weitere Formel könnte berechnen, wie viele Kunden im Alter 31 bis 35 beide Produkte der Firma nutzen, also Internet und Mobilfunk:

=ZÄHLENWENNS(B3:B27;”<=35″;C3:C27;”=Ja”;D3:D27;”=Ja”)-ZÄHLENWENNS(B3:B27;”<=30″;C3:C27;”=Ja”;D3:D27;”=Ja”)

4. Daten generieren mit Auto-Vervollständigen

Noch bequemer wäre es, viele Werte auf einmal zu erhalten, also eine Auto-Vervollständigung. Dazu müssen zunächst alle Wertebereiche mit dem ($)-Zeichen fixiert werden, die die festen Wertebereiche definieren. Danach werden die Altersgrenzwerte durch einen Zellenbezug ersetzt. Die folgende Formel berechnet, wie viele Kunden einer bestimmten Altersklasse beide Produkte nutzen, also DSL und Mobilfunk. Dabei verweisen die Zellbezüge auf die Hilfswert-Spalte, die Du unten in der Abbildung siehst.

=ZÄHLENWENNS($B$3:$B$27;”<=”&E5;$C$3:$C$27;”=Ja”;$D$3:$D$27;”=Ja”)-ZÄHLENWENNS($B$3:$B$27;”<=”&E4;$C$3:$C$27;”=Ja”;$D$3:$D$27;”=Ja”)

Achte darauf, den Zellenbezug korrekt in die Formel einzubinden. Als Teil eines Formelarguments muss einem Zellenbezug das &-Zeichen vorangestellt werden. Alle fixen Matrizen sind als fester Bereich definiert und es wurden Zellenbezüge als Variablen zugewiesen. Daraus kannst Du jetzt im Handumdrehen die folgende Wertetabelle generieren:

Der Anschaulichkeit halber kann aus diesen Daten noch ein Balkendiagramm erstellt werden. Die horizontalen Achsenbeschriftungen (Rubrik) werden aus Spalte F importiert. Die Spalten G, H, I liefern die Datenreihen und E wird als Hilfsspalte verwendet, um Zellbezüge für die Formeln zur Verfügung zu stellen (die Grenzwerte der Altersklassen). Am Ende generiert uns Excel ein übersichtliches Diagramm:

Diese Statistik von Hand auszuwerten wäre doch deutlich mühsamer gewesen. Ich hoffe ich konnte euch mit meinem Beitrag ein paar interessante Aspekte für zukünftige Excel-Vorhaben vorstellen. Und ich bin natürlich gespannt, eure Kommentare zu lesen. Hattet Ihr schon Aufgaben wie im Beispiel oben und wie habt Ihr sie gelöst? Habt Ihr dazu andere Methoden genutzt?