![如何计算多个单元格中以逗号分隔的唯一名称](https://linux22.com/image/1542503/%E5%A6%82%E4%BD%95%E8%AE%A1%E7%AE%97%E5%A4%9A%E4%B8%AA%E5%8D%95%E5%85%83%E6%A0%BC%E4%B8%AD%E4%BB%A5%E9%80%97%E5%8F%B7%E5%88%86%E9%9A%94%E7%9A%84%E5%94%AF%E4%B8%80%E5%90%8D%E7%A7%B0.png)
我是 VBA 新手,我想计算范围内用逗号和空格分隔的唯一名称,我从同一个网站获得了 UDF,但它只查看一个单元格。
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
有人可以修改单元格范围吗?谢谢 rao
答案1
此公式还将替换所有 vba:
=SUMPRODUCT(--(ISERROR(FIND(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,A:A),",",REPT(" ",999)),(ROW(1:100)-1)*999+1,999)),MID(SUBSTITUTE(TEXTJOIN(",",TRUE,A:A),",",REPT(" ",999)),1,(ROW(1:100)-1)*999)))))
TEXTJOIN 是在 Office 365 Excel 中引入的。如果您没有,请使用此代码来模拟该函数:
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function