Excel·VBA集合函数交集、并集、差集

获取两个数组的交集、并集、差集

Function collect(ByVal arr1, ByVal arr2, Optional mode$ = "&")
    '集合函数，计算交集&、并集|、差集-(arr1含arr2不含)
    Dim dict As Object, a, k, i&
    Set dict = CreateObject("scripting.dictionary")
    For Each a In arr1
        dict(a) = 1
    Next
    For Each a In arr2
        dict(a) = dict(a) + 2
    Next
    ReDim result(1 To dict.Count)
    For Each k In dict.keys
        If mode = "&" And dict(k) > 1 And dict(k) Mod 2 = 1 Then   '交集
            i = i + 1: result(i) = k
        ElseIf mode = "|" Then  '并集
            i = i + 1: result(i) = k
        ElseIf mode = "-" And dict(k) = 1 Then  '差集
            i = i + 1: result(i) = k
        End If
    Next
    ReDim Preserve result(1 To i)
    collect = result
End Function

举例

Sub 集合()
    Dim i&, j&, x&, a, b, c
    arr = [a2:e4]: brr = [a7:e9]: x = 12
    For i = 1 To UBound(brr)
        b = Application.index(brr, i)
        For j = 1 To UBound(arr)
            a = Application.index(arr, j)
            c = collect(b, a)
            Cells(x, "a").Resize(1, UBound(c)) = c: x = x + 1
        Next
    Next
End Sub

获取数组2中每一行与数组1中每一行的交集