Jak policzyć unikalne wartości oddzielone przecinkami w programie Excel 2010

Muszę zbudować formułę, aby przejść kolumnę wartości oddzielonych przecinkami i policzyć liczbę unikalnych wartości . Korzystam z programu Excel 2010. Mam zrzut ekranu z przykładowymi danymi ...

Wynik formuły w tym przypadku powinien wynosić 5. Innymi słowy, formuła musi liczyć następujące wartości: 2.3.0, 2.4.1, 2.4.2, 2.4.3, 2.4.4

Zrozumiałem, jak mogę po prostu policzyć surową liczbę wartości oddzielonych przecinkami, jak ten ...

= SUMPRODUCT (- (M123: M127 <> ""), LEN (M123: M127) -LEN (SUBSTITUTE (M123: M127, ",", "")) + 1)

Jednak ta formuła daje wynik 7, ponieważ liczy dwa razy 2.4.3 i 2.4.4.

Nie potrafię odrzucić powtarzanych wartości oddzielonych przecinkami w różnych komórkach tej samej kolumny.

Ze względu na inne systemy, które muszą łączyć się z arkuszem kalkulacyjnym, w odpowiedziach można używać tylko formuły; nie mogą używać VBA ani innego rodzaju filtra.

Jeśli możesz dodać formuły pośrednie do swojego arkusza, oto możliwe rozwiązanie.

W oparciu o zakresy w twoim przykładzie:

Umieść w komórce N123Zwraca część po lewej stronie ,lub całą komórkę, jeśli nie,

=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)

Umieść w komórce O123Zwraca część po prawej stronie ,lub -jeśli nie,

=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")

Wstaw do komórki P123Zwraca, 1jeśli komórka N123jest unikalna na liście, a także, 1jeśli komórka O123jest unikalna

=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127) 
    +MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
    +MATCH($O123,$O$123:$O$127,0),O123<>"-")

Skopiuj komórki N123..P123doN123..P127

Liczba unikalnych przedmiotów wynosi =SUM(P123:Q127)

Zakłada się, że w każdej komórce danych znajdują się maksymalnie 2 elementy. Jeśli tak nie jest, musisz dodać więcej kolumn pośrednich i odpowiednio rozszerzyć formułę O123. Jeśli jest ich więcej niż 2, to szybko wymknie się spod kontroli.

FWIW, mówisz, że rozwiązanie VBA nie jest możliwe, ale czy funkcja zdefiniowana przez użytkownika VBA jest w porządku?

To by wyglądało jakoś =CountUnique(M123:M127)

Oto inne rozwiązanie wykorzystujące funkcje VBA. Wklej następujące elementy do modułu.

Function ListCount(list As String, delimiter As String) As Long
Dim arr As Variant
arr = Split(list, delimiter)
ListCount = UBound(arr) - LBound(arr) + 1
End Function

Function RemoveDuplicates(list As String, delimiter As String) As String
Dim arrSplit As Variant, i As Long, tmpDict As New Dictionary, tmpOutput As String
arrSplit = Split(list, delimiter)
For i = LBound(arrSplit) To UBound(arrSplit)
    If Not tmpDict.Exists(arrSplit(i)) Then
        tmpDict.Add arrSplit(i), arrSplit(i)
        tmpOutput = tmpOutput & arrSplit(i) & delimiter
    End If
Next i
If tmpOutput <> "" Then tmpOutput = Left(tmpOutput, Len(tmpOutput) - Len(delimiter))
RemoveDuplicates = tmpOutput
'housekeeping
Set tmpDict = New Dictionary
End Function

Następnie w arkuszu możesz użyć następującej formuły:

=ListCount(RemoveDuplicates(A1,", "),", ")

gdzie A1jest twoja lista oddzielona przecinkami.

UWAGA: Będziesz musiał dodać Microsoft Scripting Runtime odniesienie do swojego projektu VBA (pod Tools > References... w oknie Edytora projektów VBA).

Na podstawie wkładu Chrisa Neilsena zamierzam zmodyfikować nasze systemy i użyć tego ... Źródło: Fora Mr Excel - Post z 1 stycznia 2010 o 10:04

Function UNIQUECOUNTIF(ByRef SR As Range, _
                        ByRef RR As Range, _
                        Optional ByVal Crit As Variant, _
                        Optional NCOUNT As Boolean = False, _
                        Optional POSTCODE As Boolean = False) As Long
Dim K1, K2, i As Long, c As Long, x, n As Long
K1 = SR: K2 = RR
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(K1, 1)
        If Not IsMissing(Crit) Then
            If LCase$(K1(i, 1)) = LCase$(Crit) Then
                If POSTCODE Then
                    x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
                Else
                    x = Split(LCase$(K2(i, 1)), ",")
                End If
                For c = 0 To UBound(x)
                    If POSTCODE Then
                        If IsNumeric(x(c)) Then
                            If Not .exists(x(c)) Then
                                .Add x(c), 1
                            ElseIf NCOUNT Then
                                .Item(x(c)) = .Item(x(c)) + 1
                            End If
                        End If
                    Else
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Next
            End If
        Else
            If POSTCODE Then
                x = Split(Replace(LCase$(K2(i, 1)), ",", " "), " ")
            Else
                x = Split(LCase$(K2(i, 1)), ",")
            End If
            For c = 0 To UBound(x)
                If POSTCODE Then
                    If IsNumeric(x(c)) Then
                        If Not .exists(x(c)) Then
                            .Add x(c), 1
                        ElseIf NCOUNT Then
                            .Item(x(c)) = .Item(x(c)) + 1
                        End If
                    End If
                Else
                    If Not .exists(x(c)) Then
                        .Add x(c), 1
                    ElseIf NCOUNT Then
                        .Item(x(c)) = .Item(x(c)) + 1
                    End If
                End If
            Next
        End If
    Next
    If .Count > 0 Then UNIQUECOUNTIF = Application.Sum(.items)
End With
End Function