Generische Formel
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Zusammenfassung
Um eindeutige Textwerte in einem Bereich mit Kriterien zu zählen, können Sie eine Array-Formel verwenden, die auf den Funktionen FREQUENCY und MATCH basiert. Im gezeigten Beispiel lautet die Formel in G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
Das gibt 3 zurück, da drei verschiedene Leute am Projekt Omega gearbeitet haben.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Dies ist eine komplexe Formel, die FREQUENCY verwendet, um numerische Werte zu zählen, die mit der MATCH-Funktion abgeleitet werden. Mit der MATCH-Funktion wird von innen nach außen die Position jedes Werts ermittelt, der in den Daten angezeigt wird:
MATCH(B5:B11,B5:B11,0)
Das Ergebnis von MATCH ist ein Array wie das folgende:
(1;1;3;1;1;6;7)
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 viermal in der Liste vorkommt, wird er in diesem Array viermal als Nummer 1 angezeigt.
Außerhalb der MATCH-Funktion werden mit der IF-Funktion Kriterien angewendet. In diesem Fall wird geprüft, ob das Projekt "Omega" ist (aus Zelle G5):
IF(C5:C11=G5 // filter on "omega"
Die IF-Funktion wirkt wie ein Filter und lässt die Werte von MATCH nur durch, wenn sie mit "Omega" verknüpft sind. Das Ergebnis ist ein Array wie folgt:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Das gefilterte Array wird als data_array- Argument direkt an die FREQUENCY-Funktion übergeben . Als nächstes wird die ROW-Funktion verwendet, um eine sequentielle Liste von Zahlen für jeden Wert in den Daten zu erstellen:
ROW(B3:B12)-ROW(B3)+1
Dadurch wird ein Array wie das folgende erstellt:
(1;2;3;4;5;6;7;8;9;10)
Dies wird das Argument bins_array in FILTER. An diesem Punkt haben wir:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY gibt ein Array von Zahlen zurück, die eine Anzahl für jeden Wert im Datenarray angeben, organisiert nach bin. Wenn eine Zahl bereits gezählt wurde, gibt FREQUENCY Null zurück. Das Ergebnis von FREQUENCY ist ein Array wie das folgende:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Hinweis: FREQUENCY gibt immer ein Array mit einem Element mehr als bins_array zurück .
An dieser Stelle können wir die Formel folgendermaßen umschreiben:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Wir suchen nach Werten größer als Null, wodurch die Zahlen in TRUE oder FALSE konvertiert werden:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Dann verwenden wir ein Doppel-Negativ, um die logischen Werte auf 1s und 0s zu zwingen:
=SUM((1;0;0;0;0;1;1;0))
Schließlich gibt die SUMME-Funktion 3 als Endergebnis zurück.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden.
Umgang mit leeren Zellen im Bereich
Wenn Zellen im Bereich leer sind, müssen Sie die Formel anpassen, um zu verhindern, dass leere Zellen an die MATCH-Funktion übergeben werden, was einen Fehler auslöst. Sie können dies tun, indem Sie eine weitere verschachtelte IF-Funktion hinzufügen, um nach leeren Zellen zu suchen:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Mit zwei Kriterien
Wenn Sie zwei Kriterien haben, können Sie die Logik der Formel erweitern, indem Sie eine weitere verschachtelte IF hinzufügen:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Wobei c1 = Kriterien1, c2 = Kriterien2 und vals = Wertebereich.
Mit boolescher Logik
Mit der booleschen Logik können Sie verschachtelte IFs reduzieren:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Dies erleichtert das Hinzufügen und Verwalten zusätzlicher Kriterien.
