☰

Wie kann ich eindeutige Werte in einer Spalte basierend auf Kriterien in zwei anderen Spalten zählen?

tag-icon • tag-icon microsoft-excel-2010
Wie kann ich eindeutige Werte in einer Spalte basierend auf Kriterien in zwei anderen Spalten zählen?

Ich versuche, die Anzahl der eindeutigen Einträge in Spalte A zu zählen, bei denen in Spalte C NEIN und in Spalte D JA steht. Ich kann jedoch keine Formel dafür entwickeln, die auf zwei unterschiedlichen Kriterien basiert. Wie könnte ich so etwas tun?

Ich möchte beispielsweise die Anzahl der einzelnen Länder wissen, in denen ein Event auf Abruf, aber nicht live angesehen wurde. Im folgenden Beispiel wäre das 4:

country  |  Preview  |  Live  |  On Demand
GB       |  NO       |  NO    |  YES
GB       |  NO       |  YES   |  YES
ES       |  NO       |  YES   |  YES
DE       |  NO       |  NO    |  YES
FR       |  NO       |  NO    |  YES
US       |  NO       |  NO    |  YES

Mit den in der Antwort unten vorgeschlagenen Formeln habe ich es geschafft, Excel dazu zu bringen, die folgende Formel zu akzeptieren, aber sie gibt keinen Wert zurück. Ziel ist es, eine 1 in Spalte F einzutragen, wenn E gleich 1 ist. Für jede Hilfe wäre ich dankbar:

=IF(E=1, IF(FREQUENCY(MATCH(A2:A214, A2:A214, 0), MATCH(A2:A214, A2:A214, 0)) > 0, 1, 0))

Antwort1

Würden Sie in Erwägung ziehen, eine Formel hinzuzufügen, die einfach die Werte beider Spalten kombiniert, und eine weitere, die neue/eindeutige Werte zählt?

Formel für E=$C:$C&$D:$D

Formel für F =IF(COUNTIF(E$1:E2,E2)=1,1,0)(dies ist ein Beispiel für Zelle (F2))

country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0

Antwort2

Mit der folgenden Array-Formel können Sie die Anzahl eindeutiger Datensätze ermitteln, die alle Kriterien erfüllen. Fügen Sie Folgendes in die Formelleiste ein und drücken Sie Ctrl+ Shift+ Enter.

=SUMPRODUCT((C2:C8="NO")*(D2:D8="YES")/IF(COUNTIFS(A2:A8,A2:A8,C2:C8,"NO",D2:D8,"YES")=0,1,COUNTIFS(A2:A8,A2:A8&"",C2:C8,"NO",D2:D8,"YES")))

Es ist lang und ein wenig repetitiv, aber es sollte für Sie funktionieren. Diese Formel verwendet den 1/COUNTIF(...)Trick, der oben gezeigt wurdeviele malauf dieser Site in der Vergangenheit zum Zählen eindeutiger Datensätze. Ich musste die Wiederholungsbedingung einführen, IFum Fehler zu vermeiden #DIV/0!.

Was die Formel bewirkt:

SUMPRODUCTmit einem Array-Argument addiert einfach die Elemente des Arrays. Die Multiplikationsterme ergeben 0, wenn eine der Bedingungen nicht erfüllt ist, und 1, wenn beide erfüllt sind. Die Division durch COUNTIFSist eine Möglichkeit, diesen Wert zu skalieren, um die eindeutige Anzahl zu erhalten. Wenn es beispielsweise zwei Datensätze für GBmit On Demand, aber nicht Live gäbe, würde jeder dieser Datensätze herunterskaliert, um 1/2in der Gesamtsumme als ein Datensatz zu zählen. Wenn es drei solcher Datensätze gäbe, würde jeder als zählen . Wenn die Summe diese Datensätze also addiert, ist 1/3die Gesamtsumme für On Demand, aber nicht Live 1, z. B. + = . So wird die Eindeutigkeit erfasst.GB1/21/21

Antwort3

Erstellen Sie ein Makro und nennen Sie es "CountMyUniqueEntries". Öffnen Sie den VBA-Editor und kopieren Sie den folgenden Codeausschnitt in Modul1:

Sub CountMyUniqueEntries()
    Dim cells As Range: Set cells = Excel.Selection
    'the selection does only contain data, no column-headers
    Dim i
    Dim uniqueCountries As New Collection
    For i = 1 To cells.Rows.Count
        Dim Live As Boolean: Live = (UCase(cells(i, 3)) = "YES")
        Dim OnDemand As Boolean: OnDemand = (UCase(cells(i, 4)) = "YES")
        Dim country As String: country = UCase(cells(i, 1))
        If Not Live And OnDemand Then
            If Not Contains(uniqueCountries, country) Then
                uniqueCountries.Add country, country
            End If
        End If
    Next
    MsgBox "The number of unique countries is: " & uniqueCountries.Count 
End Sub
Private Function Contains(col As Collection, ByVal entry As String) As Boolean
    On Error Resume Next
    If IsEmpty(col(entry)) Then: 'DoNothing
    Contains = (Err.Number = 0)
    On Error GoTo 0
End Function

Eventuell musst du zuerst das Menü "Entwickler-Tools" in den "Optionen" aktivieren

Antwort4

Dies ist eine ziemlich komplexe Formel. Ich werde sie in Teile aufteilen, damit klarer und offensichtlicher wird, wie sie funktioniert.
Sie haben bereits 4 Spalten A, B, C, D. Sie benötigen noch vier weitere Spalten E, F, G, H.

Kopieren Sie in Spalte E diese Formel in jede Zelle (kopieren Sie mit dem +-Zeichen rechts unten in der ersten Zelle):

=IF(C2="NO", IF(D2="YES", 1, 0), 0)

Kopieren Sie in Spalte F diese Formel in die erste Zelle:

=IF(FREQUENCY(MATCH(A2:A8, A2:A8, 0), MATCH(A2:A8, A2:A8, 0)) > 0, 1, 0)

Wählen Sie alle Zellen in Spalte F aus, drücken Sie "F2" und anschließend "Strg"+"Umschalt"+"Eingabe".

Kopieren Sie in Spalte G diese Formel in jede Zelle:

=IF(E2=1, IF(F2=1, 1, 0), 0)

Verwenden Sie eine zusätzliche Zelle, beispielsweise in Spalte H, und summieren Sie die Einsen von G mit:

=SUM(G:G)

viel Spaß

verwandte Informationen