Sumproduct,Countif函数的方法运算效率低,字典可达成目的,且明显提速
Sub model()
Dim d As New Dictionary
Dim row!, col!, str$
For col = 3 To 26 '数据载入
For row = 11 To 1000
str = Cells(row, col)
If str = Empty Then Exit For
If Not d.Exists(str) Then
d.Add str, 1
Else
d(str) = d(str) + 1
End If
Next row
Next col
For row = 11 To 1000 '数据提取
Cells(row, 2) = IIf(d(Cells(row, 1).Value) = Empty, 0, d(Cells(row, 1).Value))
Next row
MsgBox "ok"
End Sub