Excel

Zählen Sie eindeutige Textwerte in einem Bereich

Count Unique Text Values Range

Excel-Formel: Zähle eindeutige Textwerte in einem BereichAllgemeine Formel |_+_| Zusammenfassung

Um eindeutige Textwerte in einem Bereich zu zählen, können Sie eine Formel verwenden, die mehrere Funktionen verwendet: FREQUENZ , SPIEL , REIHE und SUMMENPRODUKT .Im gezeigten Beispiel lautet die Formel in F5:



= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))

was 4 zurückgibt, da es 4 eindeutige Namen in B5:B14 gibt.

Hinweis: Eine andere Möglichkeit, eindeutige Werte zu zählen, besteht darin, Verwenden Sie die ZÄHLENWENN-Funktion . Dies ist eine viel einfachere Formel, die jedoch bei großen Datensätzen langsam ausgeführt werden kann. Mit Excel 365 , du kannst a . verwenden einfachere und schnellere Formel beyogen auf EINZIGARTIG .





Erläuterung

Diese Formel ist komplizierter als eine ähnliche Formel, die FREQUENCY verwendet, um zähle eindeutige numerische Werte weil FREQUENCY nicht mit nicht numerischen Werten funktioniert. Als Ergebnis wandelt ein großer Teil der Formel einfach die nicht numerischen Daten in numerische Daten um, die FREQUENCY verarbeiten kann.

Von innen nach außen arbeitend, wird die MATCH-Funktion verwendet, um die Position jedes Elements zu ermitteln, das in den Daten erscheint:



 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

Das Ergebnis von MATCH ist ein Array so was:

 
 MATCH (B5:B14,B5:B14,0)

Da MATCH immer die Position des zurückgibt Erste übereinstimmen, geben Werte, die mehr als einmal in den Daten vorkommen, dieselbe Position zurück. Da 'Jim' beispielsweise dreimal in der Liste vorkommt, taucht er in diesem Array dreimal als die Nummer 1 auf.

Dieses Array wird in FREQUENCY als data_array Streit.Die bins_array Argument wird aus diesem Teil der Formel konstruiert:

wie man Dinge in Excel einstuft
 
{1114466699}

was baut a fortlaufende Nummernliste für jeden Wert in den Daten:

 
 ROW (B5:B14)- ROW (B5)+1)

An dieser Stelle ist FREQUENCY wie folgt konfiguriert:

 
{12345678910}

FREQUENCY gibt ein Array von Zahlen zurück, die eine Zählung für jede Zahl im Datenarray angeben, organisiert nach Bin. Wenn eine Zahl bereits gezählt wurde, gibt FREQUENCY Null zurück. Dies ist ein Schlüsselmerkmal beim Betrieb dieser Formel. Das Ergebnis von FREQUENCY ist ein Array wie folgt:

Welche der folgenden Optionen ist für die Summenfunktion nicht geeignet?
 
 FREQUENCY ({1114466699},{12345678910})

Hinweis: FREQUENCY gibt immer ein Array mit einem Element mehr als die bins_array .

Wir können die Formel jetzt so umschreiben:

 
{30020300200} // output from FREQUENCY

Als nächstes prüfen wir, ob Werte größer als Null (>0) sind, wodurch die Zahlen in WAHR oder FALSCH umgewandelt werden, und verwenden dann einen doppelten negativen Wert (--), um die WAHR- und FALSCH-Werte in 1s und 0s umzuwandeln. Jetzt haben wir:

 
= SUMPRODUCT (--({30020300200}>0))

Schließlich addiert SUMPRODUCT einfach die Zahlen und gibt die Summe zurück, die in diesem Fall 4 beträgt.

Umgang mit leeren Zellen

Leere Zellen im Bereich führen dazu, dass die Formel einen #N/A-Fehler zurückgibt. Um leere Zellen zu behandeln, können Sie eine kompliziertere Matrixformel verwenden, die die IF-Funktion verwendet, um leere Werte herauszufiltern:

 
= SUMPRODUCT ({10010100100})

Hinweis: Das Hinzufügen von IF macht dies zu einem Array-Formel das erfordert Control-Shift-Enter.

Für mehr Informationen, siehe diese Seite .

Von Mike Givins ausgezeichnetes Buch über Array-Formeln, Strg-Umschalt-Eingabe.

Andere Möglichkeiten, eindeutige Werte zu zählen

Wenn Sie Excel 365 haben, können Sie das EINZIGARTIGE Funktion zu zähle einzigartige Werte mit einer viel einfacheren Formel.

ZU Pivot-Tabelle ist auch eine hervorragende Möglichkeit, einzigartige Werte zu zählen.

Autor Dave Bruns


^