Excel

Zählen Sie eindeutige Werte mit Kriterien

Count Unique Values With Criteria

Excel-Formel: Einmalige Werte mit Kriterien zählenAllgemeine Formel |_+_| Zusammenfassung

Um eindeutige Werte mit einer oder mehreren Bedingungen zu zählen, können Sie eine Formel verwenden, die auf basiert EINZIGARTIG und FILTER . Im gezeigten Beispiel lautet die Formel in H7:



= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))

was 3 zurückgibt, da es drei eindeutige Namen in B6:B15 gibt, die mit dem Omega-Projekt verbunden sind.

Hinweis: Diese Formel erfordert Dynamische Array-Formeln , nur erhältlich in Excel 365 . Mit einer älteren Excel-Version können Sie komplexere alternative Formeln .





Erläuterung

Im Kern verwendet diese Formel die UNIQUE-Funktion, um eindeutige Werte zu extrahieren, und die FILTER-Funktion wendet Kriterien an.

pmt-Funktion zur Berechnung der monatlichen Zahlung

Von innen nach außen arbeiten, die FILTER-Funktion wird verwendet, um Kriterien anzuwenden und nur Namen zu extrahieren, die mit dem 'Omega'-Projekt verknüpft sind:



 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))

Das Ergebnis von FILTER ist ein Array so was:

 
 FILTER (B6:B15,C6:C15=H6) // Omega names only

Als nächstes die EINZIGARTIGE Funktion wird verwendet, um Duplikate zu entfernen:

 
{'Jim''Jim''Carl''Sue''Carl'}

was zu einem neuen Array wie folgt führt:

 
 UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})

An diesem Punkt haben wir eine einzigartige Liste von Namen, die mit Omega verbunden 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 SUM-Funktion. Zur Verdeutlichung schreiben wir zunächst die Formel so um, dass sie die eindeutige Liste enthält:

 
{'Jim''Carl''Sue'} // after UNIQUE

Die LEN-Funktion Ruft die Länge jedes Elements in der Liste ab und gibt ein Array von Längen zurück:

 
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))

Als nächstes prüfen wir, ob Längen größer als Null sind:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

Und benutze a Doppel negativ um die TRUE- und FALSE-Werte auf 1s und 0s zu erzwingen:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

Zum Schluss addieren wir die Ergebnisse mit dem Summenfunktion :

Wie friere ich Zellen in Excel ein?
 
--({TRUETRUETRUE}) // returns {111}

Dieses Array wird direkt an die Funktion COUNTA übergeben, die eine endgültige Zählung zurückgibt:

 
= SUM ({111}) // 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 einzigartig mit mehreren Kriterien

Um eindeutige Werte basierend auf mehreren Kriterien zu zählen, kann die 'einschließen'-Logik in FILTER erweitert werden. Um beispielsweise eindeutige Namen für das Omega-Projekt nur im Juni zu zählen, verwenden Sie:

 
= COUNTA ({'Jim''Carl''Sue'}) // returns 3

Dies ist ein Beispiel für die Verwendung Boolesche Logik mehr als eine Bedingung anwenden. Der Ansatz ist hier genauer erklärt .

wie man Summe auf Excel macht

Weitere Informationen finden Sie in diesem Schulungsvideo: So filtern Sie mit mehreren Kriterien .

ANZAHL

Es ist möglich, eine einfachere Formel zu schreiben, die auf die COUNTA-Funktion . Ein wichtiger Vorbehalt 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 grundlegende COUNTA-Formel sieht wie folgt aus:

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))

Auch diese Formel gibt 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 a komplexere Formel . Eine allgemeinere Diskussion über dynamische Array-Alternativen finden Sie unter: Alternativen zu dynamischen Array-Formeln .

Dynamische Array-Formeln sind erhältlich in Büro 365 nur. Autor Dave Bruns


^