|
'0用空替代,可把注释去除不满足条件就为0
'数据量较大,大概需要15s
Option Explicit
Sub test()
Dim arr, i As Long, j As Long, k As Long, t, dic, dt, s As String
dt = Timer
Set dic = CreateObject("scripting.dictionary")
arr = Sheets("sheet1").[a1].CurrentRegion.Value
For j = 2 To UBound(arr, 2)
For i = 2 To UBound(arr, 1)
If arr(i, j) = 1 Then
If dic.exists(arr(i, 1)) Then
If InStr(dic(arr(i, 1)), arr(1, j) & "|") = 0 Then
dic(arr(i, 1)) = dic(arr(i, 1)) & arr(1, j) & "|"
End If
Else
dic(arr(i, 1)) = arr(1, j) & "|"
End If
End If
Next i, j
Debug.Print Timer - dt
With Sheets("sheet2")
arr = .[a1].CurrentRegion.Value
.[b2].Resize(UBound(arr, 1) - 1, UBound(arr, 2)).ClearContents
arr = .[a1].CurrentRegion.Value
End With
Debug.Print Timer - dt
For j = 2 To UBound(arr, 2)
s = dic(arr(1, j))
For i = 2 To UBound(arr, 1)
If arr(i, 1) = arr(1, j) Then
' arr(i, j) = "0"
Else
t = Split(dic(arr(i, 1)), "|")
For k = 0 To UBound(t)
If Len(t(k)) > 0 Then
If InStr(s, t(k) & "|") > 0 Then
arr(i, j) = "1": Exit For
End If
End If
Next
' If k = UBound(t) + 1 Then arr(i, j) = "0"
End If
Next i, j
Debug.Print Timer - dt
Sheets("sheet2").[a1].Resize(UBound(arr, 1), UBound(arr, 2)) = arr
Debug.Print Timer - dt
End Sub |
|