Как подсчитать уникальные значения через запятую в Excel 2010

Question

Мне нужно построить формулу, чтобы пройти столбец значений, разделенных запятыми, и подсчитать количество увиденных уникальных значений . Я использую Excel 2010. У меня есть скриншот некоторых данных примера ...

Выход формулы в этом случае должен быть 5. Другими словами, формула должна учитывать следующие значения: 2.3.0, 2.4.1, 2.4.2, 2.4.3, 2.4.4

Я выяснил, как я могу просто посчитать необработанное количество значений через запятую, как это ...

= СУММПРОИЗВ (- (M123:M127 <> ""), LEN (M123:M127)-LEN (ПОДСТАВИТЬ (M123:M127, "", ""))+1)

Тем не менее, эта формула дает 7 , потому что она насчитывает 2,4,3 и 2,4,4 дважды.

Я не знаю, как отклонить повторяющиеся значения, разделенные запятыми, в разных ячейках одного столбца.

Из-за других систем, которые должны взаимодействовать с электронной таблицей, ответы могут использовать только формулу; они не должны использовать VBA или какой-то другой фильтр.

Answer 1

Вот еще одно решение с использованием функций VBA. Вставьте следующее в модуль.

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

Затем в вашем рабочем листе вы можете использовать следующую формулу:

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

где A1 - ваш список через запятую.

ПРИМЕЧАНИЕ. Вам потребуется добавить ссылку на Microsoft Scripting Runtime в проект VBA (в разделе « Tools > « References... в окне «Редактор проекта VBA»).

Answer 2

Если вы можете добавить некоторые промежуточные формулы на свой лист, вот возможное решение.

Основываясь на диапазонах в вашем примере:

Поместите в ячейке N123 Это возвращает часть слева или целую клетку , если ,

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

Поместите в ячейку O123 Это возвращает часть , или - если ,

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

Поместить в ячейку P123 Возвращает 1 если ячейка N123 уникальна в списке, плюс 1 если ячейка O123 уникальна.

=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<>"-")

Скопируйте ячейки N123..P123 до N123..P127

Количество уникальных предметов =SUM(P123:Q127)

Предполагается, что в каждой ячейке данных находится не более 2 элементов. Если это не так, вам нужно добавить дополнительные промежуточные столбцы и соответствующим образом расширить формулу в O123 . Если их много больше 2, это быстро выйдет из-под контроля.

FWIW, вы говорите, что решение VBA не возможно, но хорошо ли определенная пользователем функция VBA?

Это будет выглядеть примерно так =CountUnique(M123:M127)

Answer 3

Основываясь на мнении Криса Нильсена, я попытаюсь изменить наши системы и использовать это ... Источник: Mr Excel Forums - Сообщение от 1 января 2010 года в 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