Zählen Sie eindeutige Werte in Excel mit der ZÄHLENWENN-Funktion

In diesem Tutorial erfahren Sie, wie Sie eindeutige Werte in Excel mithilfe von Formeln zählen (Funktionen ZÄHLENWENN und SUMMENPRODUKT).

So zählen Sie eindeutige Werte in Excel

Nehmen wir an, wir haben einen Datensatz wie unten gezeigt:

Für dieses Tutorial werde ich den Bereich A2: A10 als NAMEN benennen. In Zukunft werden wir diesen benannten Bereich in den Formeln verwenden.

Siehe auch: So erstellen Sie benannte Bereiche in Excel.

In diesem Datensatz gibt es eine Wiederholung im Bereich NAMES. Um die Anzahl der eindeutigen Namen aus diesem Datensatz (A2:A10) zu ermitteln, können wir eine Kombination der Funktionen ZÄHLENWENN und SUMMENPRODUKT verwenden, wie unten gezeigt:

=SUMMENPRODUKT(1/ZÄHLENWENN(NAMEN,NAMEN))

Wie funktioniert diese Formel?

Lassen Sie uns diese Formel aufschlüsseln, um ein besseres Verständnis zu erhalten:

• ZÄHLENWENN(NAMEN,NAMEN)
  • Dieser Teil der Formel gibt ein Array zurück. Im obigen Beispiel wäre es {2;2;3;1;3;1;2;3;2}. Die Zahlen hier geben an, wie oft ein Wert im angegebenen Zellenbereich vorkommt.
    Der Name ist beispielsweise Bob, der zweimal in der Liste vorkommt, daher würde er die Zahl 2 für Bob zurückgeben. In ähnlicher Weise kommt Steve dreimal vor und daher wird 3 für Steve zurückgegeben.
• 1/ZÄHLENWENN(NAMEN,NAMEN)
  • Dieser Teil der Formel würde ein Array zurückgeben - {0.5;0.5;0.333333333333333;1;0.3333333333333333;1;0.5;0.3333333333333333;0.5}
    Da wir 1 durch das Array geteilt haben, gibt es dieses Array zurück.
    Das erste Element des oben zurückgegebenen Arrays war beispielsweise 2. Wenn 1 durch 2 geteilt wird, wird 0,5 zurückgegeben.
• SUMMENPRODUKT(1/ZÄHLENWENN(NAMEN,NAMEN))
  • SUMPRODUCT addiert einfach all diese Zahlen. Beachten Sie, dass, wenn Bob zweimal in der Liste vorkommt, das obige Array .5 zurückgibt, wo immer Bob name in der Liste auftauchte. Da Steve dreimal in der Liste vorkommt, gibt das Array .3333333 zurück, wenn Steve name erscheint. Wenn wir die Zahlen für jeden Namen hinzufügen, wird immer 1 zurückgegeben. Wenn wir alle Zahlen hinzufügen, wird die Gesamtzahl der eindeutigen Namen in der Liste zurückgegeben.

Diese Formel funktioniert einwandfrei, bis Sie keine leeren Zellen im Bereich haben. Aber wenn Sie leere Zellen haben, würde es ein #DIV/0 zurückgeben! Error.

Wie behandelt man leere Zellen?

Lassen Sie uns zuerst verstehen, warum es einen Fehler zurückgibt, wenn sich eine leere Zelle im Bereich befindet. Angenommen, wir haben den unten gezeigten Datensatz (wobei Zelle A3 leer ist):

Wenn wir nun dieselbe Formel verwenden, die wir oben verwendet haben, gibt der ZÄHLENWENN-Teil der Formel ein Array {2;0;3;1;3;1;2;3;1} zurück. Da in Zelle A3 kein Text vorhanden ist, wird die Anzahl als 0 zurückgegeben.

Und da wir 1 durch dieses gesamte Array teilen, gibt es ein #DIV/0 zurück! Error.

Um diesen Teilungsfehler bei leeren Zellen zu behandeln, verwenden Sie die folgende Formel:

=SUMMENPRODUKT((1/ZÄHLENWENN(NAMEN,NAMEN&””)))

Eine Änderung, die wir an dieser Formel vorgenommen haben, ist der Kriterienteil der Funktion ZÄHLENWENN. Wir haben NAMES&”” anstelle von NAMES verwendet. Auf diese Weise würde die Formel die Anzahl der leeren Zellen zurückgeben (früher gab sie 0 zurück, wenn eine leere Zelle vorhanden war).

HINWEIS: Diese Formel würde leere Zellen als eindeutigen Wert zählen und im Ergebnis zurückgeben.

Im obigen Beispiel sollte das Ergebnis 5 sein, aber es gibt 6 zurück, da die leere Zelle als einer der eindeutigen Werte gezählt wird.

Hier ist die Formel, die sich um die leeren Zellen kümmert und sie nicht im Endergebnis zählt:

=SUMMENPRODUKT((NAMEN””)/ZÄHLENWENN(NAMEN,NAMEN&””))

In dieser Formel haben wir anstelle von 1 als Zähler NAMES““ verwendet. Dies gibt ein Array von TRUEs und FALSE zurück. Es gibt FALSE zurück, wenn eine leere Zelle vorhanden ist. Da bei Berechnungen TRUE 1 und FALSE 0 entspricht, werden leere Zellen nicht gezählt, da der Zähler 0 (FALSE) ist.

Nachdem wir nun das Grundgerüst der Formel fertig haben, können wir einen Schritt weiter gehen und verschiedene Datentypen zählen.

So zählen Sie eindeutige Werte in Excel, die Text sind

Wir verwenden das gleiche Konzept wie oben beschrieben, um die Formel zu erstellen, die nur Textwerte zählt, die eindeutig sind.

Hier ist die Formel, die eindeutige Textwerte in Excel zählt:

=SUMMENPRODUKT((ISTEXT(NAMEN)/ZÄHLWENN(NAMEN,NAMEN&””)))

Wir haben lediglich die Formel ISTEXT(NAMES) als Zähler verwendet. Es gibt TRUE zurück, wenn die Zelle Text enthält, und FALSE, wenn dies nicht der Fall ist. Es werden keine leeren Zellen gezählt, aber Zellen, die eine leere Zeichenfolge ("") haben.

So zählen Sie eindeutige Werte in Excel, die numerisch sind

Hier ist die Formel, die eindeutige numerische Werte in Excel zählt

=SUMMENPRODUKT((ISNUMBER(NAMES))/COUNTIF(NAMES,NAMES&””))

Hier verwenden wir ISNUMBER(NAMES) als Zähler. Es gibt TRUE zurück, wenn die Zelle einen numerischen Datentyp enthält, und FALSE, wenn dies nicht der Fall ist. Es zählt keine leeren Zellen.