Generische Formel
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Zusammenfassung
Um eindeutige Werte mit einer oder mehreren Bedingungen zu zählen, können Sie eine Formel verwenden, die auf UNIQUE und FILTER basiert. In dem gezeigten Beispiel lautet die Formel in H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
Dies gibt 3 zurück, da B6 drei eindeutige Namen enthält: B15, die dem Omega-Projekt zugeordnet sind.
Hinweis: Für diese Formel sind dynamische Array-Formeln erforderlich, die nur in Excel 365 verfügbar sind. Mit einer älteren Version von Excel können Sie komplexere alternative Formeln verwenden.
Erläuterung
Im Kern verwendet diese Formel die UNIQUE-Funktion, um eindeutige Werte zu extrahieren, und die FILTER-Funktion wendet Kriterien an.
Die Funktion FILTER wird von innen nach außen verwendet, um Kriterien anzuwenden und nur Namen zu extrahieren, die dem "Omega" -Projekt zugeordnet sind:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Das Ergebnis von FILTER ist ein Array wie das folgende:
("Jim";"Jim";"Carl";"Sue";"Carl")
Als nächstes wird die UNIQUE-Funktion verwendet, um Duplikate zu entfernen:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
was zu einem neuen Array wie diesem führt:
("Jim";"Carl";"Sue") // after UNIQUE
Zu diesem Zeitpunkt haben wir eine eindeutige Liste von Namen, die mit Omega verknüpft sind, und wir müssen sie nur zählen. Aus den unten erläuterten Gründen tun wir dies mit der LEN-Funktion und der SUMME-Funktion. Um die Dinge klarer zu machen, schreiben wir zuerst die Formel neu, um die eindeutige Liste einzuschließen:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Die LEN-Funktion ermittelt die Länge jedes Elements in der Liste und gibt ein Array von Längen zurück:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Als nächstes prüfen wir, ob die Längen größer als Null sind:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Verwenden Sie ein doppeltes Negativ, um die Werte TRUE und FALSE auf 1s und 0s zu zwingen:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Schließlich addieren wir die Ergebnisse mit der SUMME-Funktion:
=SUM((1;1;1)) // returns 3
Dieses Array wird direkt an die COUNTA-Funktion übergeben, die eine endgültige Zählung zurückgibt:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Beachten Sie, dass leere oder leere Zellen, die die Kriterien erfüllen, ignoriert werden, da wir die Länge jedes von UNIQUE zurückgegebenen Elements überprüfen. Diese Formel ist dynamisch und wird sofort neu berechnet, wenn die Quelldaten geändert werden.
Zählen Sie eindeutig mit mehreren Kriterien
Um eindeutige Werte basierend auf mehreren Kriterien zu zählen, kann die "Include" -Logik in FILTER erweitert werden. Verwenden Sie beispielsweise Folgendes, um eindeutige Namen für das Omega-Projekt nur im Juni zu zählen:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Dies ist ein Beispiel für die Verwendung der Booleschen Logik, um mehr als eine Bedingung anzuwenden. Der Ansatz wird hier näher erläutert.
Weitere Informationen finden Sie in diesem Schulungsvideo: Filtern nach mehreren Kriterien.
COUNTA
Es ist möglich, eine einfachere Formel zu schreiben, die auf die COUNTA-Funktion antwortet. Eine wichtige Einschränkung ist jedoch, dass COUNTA 1 zurückgibt, wenn keine übereinstimmenden Werte vorhanden sind. Dies liegt daran, dass die FILTER-Funktion einen Fehler zurückgibt, wenn keine Daten den Kriterien entsprechen, und dieser Fehler schließlich von der COUNTA-Funktion gezählt wird. Die COUNTA-Grundformel sieht folgendermaßen aus:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Diese Formel gibt wieder 1 zurück, wenn keine übereinstimmenden Daten vorhanden sind. Es enthält auch leere Zellen, die die Kriterien erfüllen. Die auf LEN und SUM basierende Formel ist eine bessere Option.
Keine dynamischen Arrays
Wenn Sie eine ältere Version von Excel ohne Unterstützung für dynamische Arrays verwenden, können Sie eine komplexere Formel verwenden. Eine allgemeinere Beschreibung der Alternativen für dynamische Arrays finden Sie unter: Alternativen zu dynamischen Array-Formeln.
