Generische Formel
=SUMPRODUCT(1/COUNTIF(data,data))
Zusammenfassung
Um die Anzahl der eindeutigen Werte in einem Zellbereich zu zählen, können Sie eine Formel verwenden, die auf den Funktionen COUNTIF und SUMPRODUCT basiert. In der Beispielshow lautet die Formel in F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Erläuterung
COUNTIF wird von innen nach außen auf Werte im Bereich B5: B14 konfiguriert, wobei alle diese Werte als Kriterien verwendet werden:
COUNTIF(B5:B14,B5:B14)
Da wir 10 Werte für Kriterien angeben, erhalten wir ein Array mit 10 Ergebnissen wie folgt zurück:
(3;3;3;2;2;3;3;3;2;2)
Jede Zahl stellt eine Zählung dar - "Jim" erscheint dreimal, "Sue" erscheint zweimal und so weiter.
Dieses Array ist als Divisor mit 1 als Zähler konfiguriert. Nach der Teilung erhalten wir ein weiteres Array:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Alle Werte, die nur einmal im Bereich vorkommen, werden als Einsen angezeigt, aber Werte, die mehrfach auftreten, werden als Bruchwerte angezeigt, die dem Vielfachen entsprechen. (dh ein Wert, der viermal in Daten vorkommt, erzeugt vier Werte = 0,25).
Schließlich summiert die SUMPRODUCT-Funktion alle Werte im Array und gibt das Ergebnis zurück.
Umgang mit leeren Zellen
Eine Möglichkeit, mit leeren oder leeren Zellen umzugehen, besteht darin, die Formel wie folgt anzupassen:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Durch die Verkettung einer leeren Zeichenfolge ("") mit den Daten verhindern wir, dass Nullen in dem von COUNTIF erstellten Array landen, wenn die Daten leere Zellen enthalten. Dies ist wichtig, da eine Null im Divisor dazu führt, dass die Formel einen # DIV / 0-Fehler auslöst. Dies funktioniert, da bei Verwendung einer leeren Zeichenfolge ("") für Kriterien leere Zellen gezählt werden.
Obwohl diese Version der Formel bei leeren Zellen keinen # DIV / 0-Fehler auslöst, werden leere Zellen in die Zählung einbezogen. Wenn Sie leere Zellen von der Zählung ausschließen möchten, verwenden Sie:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Dies hat zur Folge, dass die Anzahl der leeren Zellen aufgehoben wird, indem der Zähler für die zugehörigen Zählungen auf Null gesetzt wird.
Langsame Leistung?
Dies ist eine coole und elegante Formel, die jedoch viel langsamer berechnet wird als Formeln, die FREQUENCY verwenden, um eindeutige Werte zu zählen. Bei größeren Datenmengen möchten Sie möglicherweise zu einer Formel wechseln, die auf der Funktion FREQUENCY basiert. Hier ist eine Formel für numerische Werte und eine für Textwerte.
