楼上的将VBA跟函数相结合使用,得心应手,在下佩服
- Sub ek_sky()
- Dim arr As Variant, brr(1 To 10000, 1 To 1) As Variant
- Dim i As Integer, j As Byte, z As Integer, k As Byte
- Dim t As String, L As Byte
- arr = Range("C6:H" & Cells(Rows.Count, 3).End(xlUp).Row)
- For i = 1 To UBound(arr) - 1
- t = ""
- For j = 1 To UBound(arr, 2)
- t = t & "," & arr(i, j)
- Next j
- For j = i + 1 To UBound(arr)
- m = m + 1: L = 0
- For k = 1 To UBound(arr, 2)
- If t & "," Like "*," & arr(j, k) & ",*" Then
- L = L + 1
- End If
- Next k
- brr(m, 1) = L
- Next j
- Next i
- Range("I9").Resize(k, 1) = brr
- End Sub
复制代码 |