我在Excel 2010中制作了一个文档,但是,我希望从中获得的功能似乎是不可能的(至少没有默认的Excel函数),而且我对VB编程的了解不足,无法制作自己的文档UDF。(实际上,我使用的是我在网上找到的可以满足我想要的功能,但不能满足我所有需求的功能。)
让我分解一下:
我有多个带有字段组的工作表,用户可以在其中添加数字(有些将为空白,有些将包含一个数字,某些将包含多个逗号分隔的数字)
我有一个“概述”表,我想在几个不同的部分(仅查看特定的字段组)内连接这些数字(并删除所有重复项)。
我发现了一个ConcatIf UDF可以很好地解决此问题,但是它不能处理非连续单元格的串联(例如,我想串联并从单元格D30,G30,J30和M30中删除重复项)(这是UDF :)
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
Dim i As Long, j As Long
With compareRange.Parent
Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
stringsRange.Column - compareRange.Column)
For i = 1 To compareRange.Rows.Count
For j = 1 To compareRange.Columns.Count
If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
End If
End If
Next j
Next i
ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
End Function
它也不能将“一个单元格中的多个数字”作为单独的数字处理。
有没有一种方法可以使串联UDF“解析”正在查找的单元格,以查找多个数字单元格和单个数字单元格之间的重复项,然后输出结果?最好允许它采用一系列非连续的单元格来工作(跨不同的工作表)。
抱歉,如果解释有点令人费解,这是我第一次寻求这种帮助。:X
这是一个例子:
如果我有以下单元格:
我希望能够简单地获得:
现在,我得到的是:
请尝试以下。如果需要更改定界符等,则可以适当地对其进行调整。我已经记录了它的作用以及原因。
公式示例:(=blah(A1:A7,A8,C9)
也可以从代码中调用)
输出示例: 2,4,6,8
Public Function Blah(ParamArray args()) As String
'Declarations
Dim uniqueParts As Collection
Dim area As Range
Dim arg, arr, ele, part
Dim i As Long
'Initialisations
Set uniqueParts = New Collection
'Enumerate through the arguments passed to this function
For Each arg In args
If TypeOf arg Is Range Then 'range so we need to enumerate its .Areas
For Each area In arg.Areas
arr = area.Value 'for large ranges it is greatly quicker to load the data at once rather than enumerating each cell in turn
For Each ele In arr 'enumerate the array
addParts CStr(ele), uniqueParts 'Call our sub to parse the data
Next ele
Next area
ElseIf VarType(arg) > vbArray Then 'an array has been passed in
For Each ele In arg 'enumerate the array
addParts CStr(ele), uniqueParts 'Call our sub to parse the data
Next ele
Else 'assume can be validly converted to a string. If it cannot then it will fail fast (as intended)
addParts CStr(arg), uniqueParts 'Call our sub to parse the data
End If
Next arg
'process our results
If uniqueParts.Count > 0 Then
ReDim arr(0 To uniqueParts.Count - 1)
For i = 1 To uniqueParts.Count
arr(i - 1) = uniqueParts(i)
Next i
'we now have an array of the unique parts, which we glue together using the Join function, and then return it
Blah = Join(arr, ",")
End If
End Function
'Sub to parse the data. In this case the sub splits the string and adds the split elements to a collection, ignoring duplicates
Private Sub addParts(partsString As String, ByRef outputC As Collection)
'ByRef is unecessary but I use it to document that outputC must be instantiated
Dim part
For Each part In Split(partsString, ",")
On Error Resume Next 'existing same key will raise an error, so we skip it and just carry on
outputC.Add part, part
On Error GoTo 0
Next part
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句