－ちょっと使えるネタを紹介－
Excel VBAで、重複するリストを抽出するには、「CountIf関数」と「AdvancedFilter」が使えます。高速に重複リストを抽出したい場合は、「AdvancedFilter」で、シンプルさを求める場合は、「CountIf関数」が簡単です。重複データの操作に慣れていきましょう。
この記事では、重複するリストを抽出する方法について、ご紹介します。
重複リストを抽出する方法として、次の3つを試してみます。
結論から言うと、「AdvancedFilter」がかなり高速です。
データの量があまりなくて、速くなくてもいいのであれば、VBAコードが簡単な、「CountIf関数」を使う方法がいいという感じです。
では、重複するリストの抽出について、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
重複するリストの抽出は、「CountIf関数」と「オートフィルタ」を使うとできます。
重複するリストを抽出する、VBA関数やエクセルの機能はないので、エクセル関数やエクセルの機能を組み合わせて、重複するリストを抽出することになります。
まずは、エクセル関数を使って、重複するリストを抽出してみます。
手順としては、
という流れです。
重複したデータを用意しました。
作業用の列を追加して、「CountIf関数」で重複の判定をします。
重複があるかを判定しています。
CountIfに入力する数式は、「=IF(COUNTIF($B$1:B1,B2)>0,"重複","")」となります。
入力範囲を見るとわかりやすいです。
自分の行より上の行に、同じデータがあるかを判定する数式となります。
次に、オートフィルタで「重複」をフィルタします。
「重複」でフィルタしました。
このフィルタした行のデータを取得します。
重複したデータを、セルにコピーしました。
あとは、オートフィルタを解除して、作業列を削除します。
重複するリストを取得することができました。
こんな感じで、CountIf関数とオートフィルタを使うと、重複したリストを取得することができます。
これを、VBAで自動化します。
VBAで、「CountIf関数」と「オートフィルタ」を使って重複するリストを抽出するVBAコードです。
Sub TEST1()
'CountIf関数で重複の判定
Range("C2:C11") = "=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:C11").Clear
End Sub
手順は、先ほどと同じで、
という流れです。
ポイントとなるのは、「CountIf関数」を使うところですね。
重複したデータを用意しました。
では、VBAコードを実行してみます。
重複するリストを抽出できました。
オートフィルタなので、フィルタ条件を変えれば、〇〇のデータで、重複しているデータを抽出とかもできるので、応用が利きます。
CountIf関数とオートフィルタを使った方法で、時間を計測してみます。
20,001行の重複したデータを用意しました。
この大量のデータから、重複するデータを抽出します。
重複するデータを抽出するVBAコードは、こちらになります。
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を使って重複するデータを抽出した場合で、時間を計測してみます。
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」を使って重複するデータを判定しています。
重複したデータを用意しました。
では、VBAコードを実行してみます。
まず、「For」と「If」を使って、重複を判定する配列を作成します。
自分より上の値に、同じ値があるかを確認していきます。
自分より上の値に、同じ値がある場合は、配列に「重複」を入力していきます。
同じように最終行まで繰り返していきます。
重複を判定する配列を作成したら、その配列をシートを入力します。
次に、オートフィルタを使って、「重複」でフィルタします。
次は、重複するデータが出てきましたので、重複するデータをシートへ貼り付けです。
次は、オートフィルタを解除します。
最後に作業列をクリアします。
これで、重複リストが取得できます。
重複するリストを取得できました。
先ほどと同じ、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＋オートフィルタを使って重複リストを抽出してみます。
「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
手順は、
という流れです。
手順の④以降は、他の方法と同じです。
では、実際に実行してみます。
重複したデータを用意しました。
では、VBAコードを実行してみます。
最初に、仮の「重複」を入力します。
AdvancedFilterを使って、重複しない値でフィルタします。
フィルタ結果の「重複」を削除します。
次に、オートフィルタを使って、「重複」でフィルタします。
次は、重複するデータが出てきましたので、重複するデータをセルにコピーします。
次は、オートフィルタを解除します。
最後に作業列をクリアします。
これで、重複リストが取得できます。
重複するリストを取得できました。
先ほどと同じ、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
では、VBAコードを実行してみます。
重複するリストを取得できました。
では、「AdvancedFilter」を使って重複リストを作成するのに、かかった時間です。
結果は、「0.37 秒」となりました。
かなり速いですね。
「2万行」で1秒かかってないので、十分実務で使えるレベルです。
重複リストの作成を高速化したい場合は、「AdvancedFilter」を使いましょう。
この記事では、重複するリストを抽出する方法について、ご紹介しました。
重複リストを抽出する方法として、次の3つを試してみました。
データが大量にある場合で、重複リストを「高速」に抽出したい場合は、「AdvancedFilter」が使えます。
データの量があまりなくて、速くなくてもいいのであれば、VBAコードが簡単な、「CountIf関数」を使う方法がいいです。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する