Sub TEST2()
t = Timer
'CountIf関数で重複の判定
Range("C2:C20001") = "=IF(COUNTIF($B$1:B1,B2)>0,""重複"","""")"
'3列目を「重複」でフィルタする
Range("A1").AutoFilter 3, "重複"
'重複するリストを貼り付け
Range("A1").CurrentRegion.Resize(, 2).Copy Range("E1")
'オートフィルタ解除
Range("A1").AutoFilter
'CountIf関数の列をクリア
Range("C2:C20001").Clear
Debug.Print Timer - t & " 秒"
End Sub
重複したデータを抽出します。
重複するデータを抽出
重複するデータを抽出できました。
かかった時間は、「39.27 秒」となりました。
「CountIf関数」を入力するときに、かなり時間がかかってしまいます。
ただ、VBAコード自体は、シンプルなので、速くなくてもいい場合には、使えます。
次に比較のために、ForとIfを使って重複するデータを抽出した場合で、時間を計測してみます。
VBAでForとIf+オートフィルタを使って重複リストを抽出
ForとIfを使って、重複するデータを抽出した後に、オートフィルタを使って重複リストを取得します。
ForとIf+オートフィルタで重複リストを抽出
ForとIfを使って重複リストを作成するVBAコードです。
Sub TEST3()
'重複判定用の配列を作成
Dim Data
ReDim Data(1 To 10, 1 To 1)
For i = 1 To 10
For j = 1 To i - 1
'重複がある場合
If Cells(i + 1, "B") = Cells(j + 1, "B") Then
Data(i, 1) = "重複"
Exit For
End If
Next
Next
'重複判定用の配列をセルに入力
Range("C2").Resize(UBound(Data, 1)) = Data
'3列目を「重複」でフィルタする
Range("A1").AutoFilter 3, "重複"
'重複するリストを貼り付け
Range("A1").CurrentRegion.Resize(, 2).Copy Range("E1")
'オートフィルタ解除
Range("A1").AutoFilter
'重複を判定する列をクリア
Range("C2:C11").Clear
End Sub
手順は、
①「ForとIF」で重複を判定する配列を作成
②重複を判定する配列をセルに入力
③オートフィルタで「重複」でフィルタする
④重複するリストをコピー
⑤オートフィルタを解除
⑥重複を判定する列をクリア
という流れです。
手順の①と②が変わっています。
「For」と「IF」を使って重複するデータを判定しています。
重複したデータを用意しました。
重複データを用意
では、VBAコードを実行してみます。
実行する手順
まず、「For」と「If」を使って、重複を判定する配列を作成します。
自分より上の値に、同じ値があるかを確認していきます。
自分より上の値に、同じ値がある場合は、配列に「重複」を入力していきます。
同じように最終行まで繰り返していきます。
重複を判定する配列を作成したら、その配列をシートを入力します。
次に、オートフィルタを使って、「重複」でフィルタします。
次は、重複するデータが出てきましたので、重複するデータをシートへ貼り付けです。
次は、オートフィルタを解除します。
最後に作業列をクリアします。
これで、重複リストが取得できます。
重複リストを取得できた
重複するリストを取得できました。
大量データで時間を計測
先ほどと同じ、20,001行の重複したデータを用意しました。
実行するのにかかる時間を計測してみます。
20,001行の重複データ
VBAコードは次のようになります。
Sub TEST4()
t = Timer
'重複判定用の配列を作成
Dim Data
ReDim Data(1 To 20000, 1 To 1)
For i = 1 To 20000
For j = 1 To i - 1
'重複がある場合
If Cells(i + 1, "B") = Cells(j + 1, "B") Then
Data(i, 1) = "重複"
Exit For
End If
Next
Next
'重複判定用の配列をセルに入力
Range("C2").Resize(UBound(Data, 1)) = Data
'3列目を「重複」でフィルタする
Range("A1").AutoFilter 3, "重複"
'重複するリストを貼り付け
Range("A1").CurrentRegion.Resize(, 2).Copy Range("E1")
'オートフィルタ解除
Range("A1").AutoFilter
'重複を判定する列をクリア
Range("C2:C20001").Clear
Debug.Print Timer - t & " 秒"
End Sub
では、VBAコードを実行してみます。
重複リストを取得できた
重複するリストを取得できました。
では、問題の時間です。
かかった時間
かかった時間は、「4.69 秒」となりました。
速くなりました。
「CountIf関数」を使う場合は、「39.27 秒」だったので、速くなっています。
「ForとIF」を使った方が、重複リストの作成は速いです。
ただ、2万行ぐらいならもう少し速くしたいです。
次に説明する「AdvancedFiter」を使うともっと速くできます。
VBAでAdvancedFilter+オートフィルタを使って重複リストを抽出
VBAでAdvancedFilter+オートフィルタを使って重複リストを抽出してみます。
AdvancedFilter+オートフィルタで重複リストを抽出
「AdvancedFilter」を使って重複リストを作成するVBAコードです。
Sub TEST5()
'仮に「重複」を入力しておく
Range("C2:C11") = "重複"
'重複しない値を抽出
Range("B1:B11").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'重複しない値の「重複」を削除
Range("C2:C11").Clear
'「重複」する値でフィルタする
Range("A1").AutoFilter 3, "重複"
'フィルタ結果をコピー
Range("A1").CurrentRegion.Resize(, 2).Copy Range("E1")
'フィルタを解除
Range("A1").AutoFilter
'作業列を削除
Range("C2:C11").Clear
End Sub
手順は、
①仮の「重複」を入力
②「AdvancedFilter」で重複しない値でフィルタ
③フィルタ結果の「重複」を削除
④「重複」でフィルタ
⑤重複するリストをコピー
⑥オートフィルタを解除
⑦重複を判定する列をクリア
という流れです。
手順の④以降は、他の方法と同じです。
では、実際に実行してみます。
重複したデータを用意しました。
重複データを用意
では、VBAコードを実行してみます。
実行する手順
最初に、仮の「重複」を入力します。
AdvancedFilterを使って、重複しない値でフィルタします。
フィルタ結果の「重複」を削除します。
次に、オートフィルタを使って、「重複」でフィルタします。
次は、重複するデータが出てきましたので、重複するデータをセルにコピーします。
次は、オートフィルタを解除します。
最後に作業列をクリアします。
これで、重複リストが取得できます。
重複リストを取得できた
重複するリストを取得できました。
大量データで時間を計測
先ほどと同じ、20,001行の重複したデータを用意しました。
実行するのにかかる時間を計測してみます。
20,001行の重複データ
VBAコードは次のようになります。
Sub TEST6()
t = Timer
'仮に「重複」を入力しておく
Range("C2:C20001") = "重複"
'重複しない値を抽出
Range("B1:B20001").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'重複しない値の「重複」を削除
Range("C2:C20001").Clear
'「重複」する値でフィルタする
Range("A1").AutoFilter 3, "重複"
'フィルタ結果をコピー
Range("A1").CurrentRegion.Resize(, 2).Copy Range("E1")
'フィルタを解除
ActiveSheet.Range("A1").AutoFilter
'作業列を削除
Range("C2:C20001").Clear
Debug.Print Timer - t & " 秒"
End Sub