| |
▼PePe さん：
時間があったので、Findと CountIf と Dictionary とで、
どのくらい処理時間に差が出るのか、２つのシートに
2万行のランダム数値データを置いて、テストしてみました。
Option Explicit
Declare Function timeGetTime Lib "winmm.dll" () As Long
’// 2万行データの生成
Sub GenData()
Const Lo = 1000, Hi = 9999
Const MAX = 20000
Dim i&
Randomize
ReDim v(1 To MAX, 1 To 1)
For i = 1 To MAX
v(i, 1) = Int((Hi - Lo + 1) * Rnd() + Lo)
Next
Cells(1).Resize(MAX).Value = v
End Sub
Sub test1_Find()
Const MAX = 20000
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim r1 As Range, r2 As Range, c As Range
Dim LstR1 As Long, LstR2 As Long
Dim v
Dim t&
t = timeGetTime()
Set WS1 = Worksheets(1)
Set WS2 = Worksheets(2)
With WS1
LstR1 = MAX '.Cells(Rows.Count, 1).End(xlUp).Row
Set r1 = .Range("A1:A" & LstR1)
LstR2 = MAX ' WS2.Cells(Rows.Count, 1).End(xlUp).Row
'WS2 A列をLoop
For Each r2 In WS2.Range("A1:A" & LstR2)
v = r2.Value
Set c = Nothing
'Bk2シートA列のあるセル値が、Bk1側にあるか調べる
Set c = r1.Find(v, LookIn:=xlFormulas, LookAt:=xlWhole)
'↓無かった時の処理
If c Is Nothing Then
LstR1 = LstR1 + 1
.Cells(LstR1, 1).Value = v
End If
Next r2
End With
Debug.Print "Find", timeGetTime() - t
End Sub
Sub test2_CountIf()
Const MAX = 20000
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim r1 As Range, r2 As Range
Dim LstR1 As Long, LstR2 As Long
Dim v
Dim t&
t = timeGetTime()
Set WS1 = Worksheets(1)
Set WS2 = Worksheets(2)
With WS1
LstR1 = MAX '.Cells(Rows.Count, 1).End(xlUp).Row
Set r1 = .Range("A1:A" & LstR1)
LstR2 = MAX ' WS2.Cells(Rows.Count, 1).End(xlUp).Row
'WS2 A列をLoop
For Each r2 In WS2.Range("A1:A" & LstR2)
v = r2.Value
'Bk2シートA列のあるセル値が、Bk1側にあるか調べる
If WorksheetFunction.CountIf(r1, v) = 0 Then
'↓無かった時の処理
LstR1 = LstR1 + 1
.Cells(LstR1, 1).Value = v
End If
Next r2
End With
Debug.Print "CountIf", timeGetTime() - t
End Sub
Sub test3_Dictionary()
Const MAX = 20000
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim r1 As Range, r2 As Range
Dim LstR1 As Long, LstR2 As Long
Dim i&, v
Dim dic As Object
Dim t&
t = timeGetTime()
Set dic = CreateObject("Scripting.Dictionary")
Set WS1 = Worksheets(1)
Set WS2 = Worksheets(2)
With WS1
LstR1 = MAX '.Cells(Rows.Count, 1).End(xlUp).Row
v = .Range("A1:A" & LstR1).Value
For i = 1 To UBound(v)
dic(v(i, 1)) = Empty
Next
LstR2 = MAX ' WS2.Cells(Rows.Count, 1).End(xlUp).Row
'WS2 A列をLoop
v = WS2.Range("A1:A" & LstR2).Value
For i = 1 To UBound(v)
'Bk2シートA列のあるセル値が、Bk1の辞書にあるか調べる
If Not dic.Exists(v(i, 1)) Then
'↓無かった時の処理
LstR1 = LstR1 + 1
.Cells(LstR1, 1).Value = v(i, 1)
End If
Next i
End With
Debug.Print "Dictionary", timeGetTime() - t
End Sub
■結果です(１回だけ 単位：ミリ秒)
CountIf 69390
Find 59691
Dictionary 256
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
