我需要建立一个公式来遍历一列逗号分隔的值并计算唯一值看到了。我使用的是 Excel 2010。我有一些示例数据的屏幕截图...
在这种情况下，公式的输出应为 5。换句话说，公式必须计算以下值：2.3.0、2.4.1、2.4.2、2.4.3、2.4.4
我已经想出了如何简单地计算像这样的逗号分隔值的原始数量......
=SUMPRODUCT(--(M123:M127<>""),LEN(M123:M127)-LEN(SUBSTITUTE(M123:M127,",",""))+1)
但是，该公式得出的结果是7，因为它计算了 2.4.3 和 2.4.4 两次。
我不知道如何拒绝同一列的不同单元格中重复的逗号分隔的值。
由于其他系统必须与电子表格接口，答案只能使用公式；不能使用 VBA 或某种过滤器。
答案1
如果您可以在工作表中添加一些中间公式，这是一个可能的解决方案。
根据您示例中的范围：
放入单元格这将返回单元格N123左侧的部分，如果没有,,
=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)
放入单元格O123这将返回右侧的部分,，-如果没有,
=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")
放入单元格如果单元格在列表中是唯一的，则返回此值，如果单元格是唯一的，则P123返回此值1N1231O123
=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)
答案2
这是使用 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 项目编辑器窗口中）。
答案3
根据 Chris Neilsen 的意见，我将尝试修改我们的系统并使用此...来源：Mr Excel 论坛 - 发表日期：2010 年 1 月 1 日上午 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