Bei der Arbeit mit großen Datensätzen benötigen wir oft die Anzahl der eindeutigen und unterschiedlichen Werte in Excel. Obwohl dies in vielen Fällen erforderlich ist, verfügt Excel über keine vordefinierte Formel zum Zählen eindeutiger und unterschiedlicher Werte. In diesem Tutorial lernen Sie einige Techniken kennen, um eindeutige und unterschiedliche Werte in Excel zu zählen.
Eindeutige Werte sind die Werte, die nur einmal in der Liste vorkommen, ohne dass es Duplikate gibt. Die eindeutigen Werte sind alle unterschiedlichen Werte in der Liste.
In diesem Beispiel haben Sie eine Liste mit Zahlen im Bereich von 1-6. Die eindeutigen Werte sind diejenigen, die nur einmal in der Liste vorkommen, ohne dass es zu Duplikaten kommt. Die eindeutigen Werte sind alle unterschiedlichen Werte in der Liste. Die folgenden Tabellen zeigen die eindeutigen und unterscheidbaren Werte in dieser Liste.
Zählen eindeutiger Werte in Excel
Sie können die Kombination aus den Funktionen SUMME und COUNTIF verwenden, um eindeutige Werte in Excel zu zählen. Die Syntax für diese kombinierte Formel lautet = SUMME(WENN(1/COUNTIF(Daten, Daten)=1,1,0)). Hier zählt die COUNTIF-Formel, wie oft jeder Wert im Bereich vorkommt.
Das resultierende Array sieht aus wie {1;2;1;1;1;1}. Im nächsten Schritt dividieren Sie 1 durch die resultierenden Werte. Die WENN-Funktion implementiert die Logik so, dass dieser Schritt 1 ergibt, wenn die Werte nur einmal im Bereich vorkommen, andernfalls ist es ein Bruchwert. Die Funktion SUMME summiert dann alle Werte und gibt das Ergebnis zurück. Da es sich um eine Array-Formel handelt, müssen Sie sie mit Strg + Umschalt + Enter zuweisen.
Das folgende Beispiel enthält eine Liste von Automobilprodukten mit ihrer Produkt-ID und ihren Namen. In diesem Beispiel werden die eindeutigen Elemente gezählt.
{=SUM(IF(1/COUNTIF(A2:A11,A2:A11)=1,1,0))}
Damit wird die Anzahl der eindeutigen Werte in der Liste gezählt. Sie folgt der oben genannten Syntax und gibt die Anzahl der eindeutigen Elemente zurück, die 5 beträgt.
{=SUM(IF(ISNUMBER(A2:A11)*COUNTIF(A2:A11,A2:A11)=1,1,0))}
Damit wird die Anzahl der eindeutigen numerischen Werte in der Liste gezählt. Der einzige Unterschied zur vorherigen Formel besteht in der verschachtelten ISNUMBER-Formel, die sicherstellt, dass nur die numerischen Werte gezählt werden. Gibt die Zahl 3 zurück, die die Anzahl der eindeutigen numerischen Werte ist.
{=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A11,A2:A11)=1,1,0))}
Funktioniert genauso wie die vorherige Formel, zählt aber die eindeutige Anzahl der Textwerte. Die Funktion ISTEXT wird verwendet, um sicherzustellen, dass nur die Textwerte gezählt werden. Gibt die Zahl 2 zurück.
Zählen unterschiedlicher Werte mithilfe eines Filters
Sie können die eindeutigen Werte aus einer Liste mithilfe des Dialogfelds Erweiterter Filter extrahieren und die Funktion Zeilenzählung verwenden, um die eindeutigen Werte zu zählen. So zählen Sie die eindeutigen Werte aus dem vorherigen Beispiel:
Wählen Sie den Bereich der Zellen A1:A11.
Gehen Sie zu Daten > Sortieren & Filtern > Erweitert.
Klicken Sie im Dialogfeld Erweiterter Filter auf An eine andere Stelle kopieren.
Setzen Sie sowohl den Listenbereich als auch den Kriterienbereich auf $A$1:$A$11.
Setzen Sie Kopieren auf $F$2.
Lassen Sie das Kontrollkästchen Nur eindeutige Datensätze aktiviert. Klicken Sie auf OK.
Spalte F enthält nun die eindeutigen Werte.
Wählen Sie H6.
Geben Sie die Formel =ROWS(F3:F9) ein. Klicken Sie auf Enter.
Dies zeigt die Anzahl der eindeutigen Werte an, 7.
Unterschiedliche Werte mit Formeln zählen
Sie können die Kombination der Funktionen IF, MATCH, LEN und FREQUENCY mit der SUM-Funktion verwenden, um eindeutige Werte zu zählen.
{=SUM(IF(A2:A11<>““,1/COUNTIF(A2:A11, A2:A11), 0))}
Zählt die Anzahl der unterschiedlichen Werte zwischen den Zellen A2 bis A11. Wie bei der eindeutigen Zählung gibt die Funktion COUNTIF einen Zählwert für jeden einzelnen Wert zurück, der dann als Nenner zur Division von 1 verwendet wird. Die zurückgegebenen Werte werden dann summiert, wenn sie nicht 0 sind. Auf diese Weise erhalten Sie eine Zählung für die einzelnen Werte, unabhängig von ihrem Typ.
=SUM(IF(FREQUENCY(MATCH(A2:A11,A2:A11,0),MATCH(A2:A11,A2:A11,0))>0,1))
Erfüllt dieselbe Aufgabe wie die vorherige Formel. Der einzige Unterschied ist die Verwendung der Funktionen FREQUENCY und MATCH. Die Häufigkeitsfunktion gibt die Anzahl der Vorkommen eines Wertes für das erste Vorkommen zurück. Für das nächste Vorkommen dieses Wertes wird 0 zurückgegeben. Die MATCH-Funktion wird verwendet, um die Position eines Textwertes im Bereich zurückzugeben. Die zurückgegebenen Werte werden dann als Argument für die Funktion FREQUENCY verwendet, die die Gesamtzahl der unterschiedlichen Werte ermittelt.
=SUM(IF(FREQUENCY(A2:A11,A2:A11)>0,1))
Zählt die Anzahl der unterschiedlichen numerischen Werte. Da die Funktion FREQUENCY Text und Leerzeichen ignoriert, gibt sie 5 zurück, was der Anzahl der numerischen Werte entspricht.
{=SUM(IF(ISTEXT(A2:A11),1/COUNTIF(A2:A11, A2:A11),““))}
Gibt die Anzahl der unterschiedlichen Textwerte in einem Bereich zurück. Wie im ersten Beispiel werden die eindeutigen Werte gezählt, aber die Funktion ISTEXT stellt sicher, dass nur die Textwerte gezählt werden.