Generische Formel
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Zusammenfassung
Um eindeutige Textwerte in einem Bereich zu zählen, können Sie eine Formel verwenden, die mehrere Funktionen verwendet: FREQUENCY, MATCH, ROW und SUMPRODUCT. Im gezeigten Beispiel lautet die Formel in F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
Dies gibt 4 zurück, da es in B5 4 eindeutige Namen gibt: B14.
Hinweis: Eine andere Möglichkeit, eindeutige Werte zu zählen, ist die Verwendung der COUNTIF-Funktion. Dies ist eine viel einfachere Formel, die jedoch bei großen Datenmengen langsam ausgeführt werden kann. Mit Excel 365 können Sie eine einfachere und schnellere Formel verwenden, die auf UNIQUE basiert.
Erläuterung
Diese Formel ist komplizierter als eine ähnliche Formel, bei der FREQUENCY zum Zählen eindeutiger numerischer Werte verwendet wird, da FREQUENCY nicht mit nicht numerischen Werten funktioniert. Infolgedessen wandelt ein großer Teil der Formel die nicht numerischen Daten einfach in numerische Daten um, die FREQUENCY verarbeiten kann.
Von innen nach außen wird die MATCH-Funktion verwendet, um die Position jedes Elements zu ermitteln, das in den Daten angezeigt wird:
MATCH(B5:B14,B5:B14,0)
Das Ergebnis von MATCH ist ein Array wie das folgende:
(1;1;1;4;4;6;6;6;9;9)
Da MATCH immer die Position der ersten Übereinstimmung zurückgibt, geben Werte, die mehr als einmal in den Daten vorkommen, dieselbe Position zurück. Da "Jim" beispielsweise dreimal in der Liste vorkommt, wird er in diesem Array dreimal als Nummer 1 angezeigt.
Dieses Array wird als data_array- Argument in FREQUENCY eingespeist . Das Argument bins_array wird aus diesem Teil der Formel erstellt:
ROW(B5:B14)-ROW(B5)+1)
Dadurch wird für jeden Wert in den Daten eine fortlaufende Liste von Zahlen erstellt:
(1;2;3;4;5;6;7;8;9;10)
Zu diesem Zeitpunkt ist FREQUENCY folgendermaßen konfiguriert:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY gibt ein Array von Zahlen zurück, die eine Anzahl für jede Zahl im Datenarray angeben, die nach bin organisiert ist. Wenn eine Zahl bereits gezählt wurde, gibt FREQUENCY Null zurück. Dies ist eine wichtige Funktion beim Betrieb dieser Formel. Das Ergebnis von FREQUENCY ist ein Array wie das folgende:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Hinweis: FREQUENCY gibt immer ein Array mit einem Element mehr als bins_array zurück .
Wir können die Formel jetzt folgendermaßen umschreiben:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Als nächstes prüfen wir, ob Werte größer als Null (> 0) sind, wodurch die Zahlen in TRUE oder FALSE konvertiert werden, und verwenden dann ein doppeltes Negativ (-), um die TRUE- und FALSE-Werte in 1s und 0s zu konvertieren. Jetzt haben wir:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;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 Array-Formel verwenden, die die IF-Funktion verwendet, um leere Werte herauszufiltern:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Hinweis: Durch Hinzufügen von IF wird dies zu einer Array-Formel, für die Control-Shift-Enter erforderlich ist.
Weitere Informationen finden Sie auf dieser Seite.
Andere Möglichkeiten, eindeutige Werte zu zählen
Wenn Sie über Excel 365 verfügen, können Sie die Funktion UNIQUE verwenden, um eindeutige Werte mit einer viel einfacheren Formel zu zählen.
Eine Pivot-Tabelle ist auch eine hervorragende Möglichkeit, eindeutige Werte zu zählen.
