大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、重複するリストを抽出する方法について、ご紹介します。重複するリストの抽出は、「For」と「If」で重複を抽出して「オートフィルタ」を使ってデータを取得する方法が、速いです。具体的なVBAコードを使って、重複するリストの抽出について、解説していきます。

はじめに

この記事では、重複するリストを抽出する方法について、ご紹介します。

重複するリストを取得できると、間違ったデータが入力されていないかなどの、データの解析ができます。

重複するリストの抽出は、「CountIf関数」を使う方法と、「For」と「If」を組み合わせた方法があります。

結果として、「For」と「If」を組み合わせた方法が、速いです。

では、重複するリストの抽出について、解説していきます。

この記事で紹介すること

  • 重複するリストの抽出

目次から見たい項目へ移動すると便利ですよ。

目次

VBAでCountIf関数とオートフィルタを使って重複リストを抽出

重複するリストの抽出は、「CountIf関数」と「オートフィルタ」を使うとできます。

重複するリストを抽出する、VBA関数やエクセルの機能はないので、エクセル関数やエクセルの機能を組み合わせて、重複するリストを抽出することになります。

手動で重複リストを抽出

まずは、エクセルの機能を使って、重複するリストを抽出してみます。

手順としては、

  • ①重複するかをCountIf関数で判定
  • ②オートフィルタで「重複」をフィルタする
  • ③重複するリストをシートへ貼り付け
  • ④オートフィルタを解除
  • ⑤CountIfの列を削除

という流れです。

重複したデータを用意しました。

重複したデータ

重複したデータ

作業用の列を追加して、「CountIf関数」で重複の判定をします。

CountIf関数で重複を判定

CountIf関数で重複の判定をする

重複があるかを判定しています。

CountIfに入力する数式は、「=IF(COUNTIF($B$1:B1,B2)>0,"重複","")」となります。

入力範囲を見るとわかりやすいです。

CountIfに入力する値

作業用の列として、CountIfの数式を入力①
作業用の列として、CountIfの数式を入力②
作業用の列として、CountIfの数式を入力③

自分の行より上の行に、同じデータがあるかを判定する数式となります。

次に、オートフィルタで「重複」をフィルタします。

重複をフィルタする

オートフィルタで「重複」をフィルタする

「重複」でフィルタしました。

このフィルタした行のデータを取得します。

フィルタしたデータを取得

オートフィルタしたデータを貼り付け

重複したデータを、シートに貼り付けることができました。

あとは、オートフィルタを解除して、作業列を削除します。

オートフィルタを解除して、CountIfの列を削除

オートフィルタを解除して作業用で作成したCountIfの列を削除

重複するリストを取得することができました。

こんな感じで、CountIf関数とオートフィルタを使うと、重複したリストを取得することができます。

これを、VBAで自動化します。

VBAで重複リストを抽出

VBAで、「CountIf関数」と「オートフィルタ」を使って重複するリストを抽出するVBAコードです。

Sub TEST1()
    
    'CountIf関数で重複の判定
    ActiveSheet.Range("C2:C11") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
    
    'オートフィルタを設定
    ActiveSheet.Range("A1").AutoFilter
    '3列目を「重複」でフィルタする
    ActiveSheet.Range("A1").AutoFilter 3, "重複"
    
    '重複するリストを貼り付け
    With ActiveSheet.Range("A1").CurrentRegion
        .Resize(, .Columns.Count - 1).Copy ActiveSheet.Range("E1")
    End With
    
    'オートフィルタ解除
    ActiveSheet.Range("A1").AutoFilter
    'CountIf関数の列をクリア
    ActiveSheet.Columns(3).Clear
    
End Sub

手順は、先ほどと同じで、

  • ①重複するかをCountIf関数で判定
  • ②オートフィルタで「重複」をフィルタする
  • ③重複するリストをシートへ貼り付け
  • ④オートフィルタを解除
  • ⑤CountIfの列を削除

という流れです。

重複したデータを用意しました。

重複したデータ

重複したデータ

では、VBAコードを実行してみます。

重複するリストを抽出

VBAでCountIf関数とオートフィルタを使って重複するリストを抽出した結果

重複するリストを抽出できました。

オートフィルタなので、フィルタ条件を変えれば、〇〇のデータで、重複しているデータを抽出とかもできるので、応用が利きます。

作業用シートを使うと便利

「CountIf関数」と「オートフィルタ」を使って、重複するリストを抽出する場合、作業用シートを使うと便利になります。

元の表に変更を加えることなく、重複したリストを取得することができます。

作業用シートで、重複するリストを抽出するVBAコードです。

「CountIf関数」と「オートフィルタ」を使います。

Sub TEST2()
    
    '左側に、シートをコピー
    ActiveSheet.Copy before:=ActiveSheet
    
    'CountIf関数で、重複を判定
    ActiveSheet.Range("C2:C11") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
    
    'オートフィルタを設定
    ActiveSheet.Range("A1").AutoFilter
    '3列目を「重複」でフィルタする
    ActiveSheet.Range("A1").AutoFilter 3, "重複"
    
    '重複するリストを貼り付け
    With ActiveSheet.Range("A1").CurrentRegion
        .Resize(, .Columns.Count - 1).Copy ActiveSheet.Next.Range("E1")
    End With
    
    'メッセージを非表示
    Application.DisplayAlerts = False
    '作業用シートを削除
    ActiveSheet.Delete
    
End Sub

手順としては、

  • ①シートをコピー
  • ②CountIf関数で重複を判定
  • ③オートフィルタを使って「重複」でフィルタ
  • ④重複するリストを元シートに貼り付け
  • ⑤作業用シートを削除

という流れです。

重複したデータを用意しました。

重複したデータ

重複したデータ

では、VBAコードを実行します。

作業用のシートを、左側に作成して、重複の判定をします。

作業用のシートを作成

作業用シートを左側に作成

「重複」でフィルタして、重複するリストを元のシートに貼り付けます。

作業用シートを使って重複するリストを抽出

作業用シート上で、CountIf関数とオートフィルタを使って重複するリストを抽出した結果

重複するリストを抽出できました。

作業用シートを使うと、元の表に変更を加えないで、重複したリストを抽出することができます。

時間を計測

CountIf関数とオートフィルタを使った方法で、時間を計測してみます。

20,001行の重複したデータを用意しました。

20,001行の重複するデータ

20,001行の重複したデータを用意

この大量のデータから、重複するデータを抽出します。

重複するデータを抽出するVBAコードは、こちらになります。

Sub TEST3()
    
    'CountIf関数で重複の判定
    ActiveSheet.Range("C2:C20001") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
    
    'オートフィルタを設定
    ActiveSheet.Range("A1").AutoFilter
    '3列目を「重複」でフィルタする
    ActiveSheet.Range("A1").AutoFilter 3, "重複"
    
    '重複するリストを貼り付け
    With ActiveSheet.Range("A1").CurrentRegion
        .Resize(, .Columns.Count - 1).Copy ActiveSheet.Range("E1")
    End With
    
    'オートフィルタ解除
    ActiveSheet.Range("A1").AutoFilter
    'CountIf関数の列をクリア
    ActiveSheet.Columns(3).Clear
        
End Sub

重複したデータを抽出します。

重複するデータを抽出

重複するデータを抽出した結果

重複するデータを抽出できました。

かかった時間は、「39.27 秒」となりました。

数式を入力しているからか、ちょっと遅いなという印象です。

次に比較のために、ForとIfを使って重複するデータを抽出した場合で、時間を計測してみます。

VBAでForとIf+オートフィルタを使って重複リストを抽出

ForとIfを使って、重複するデータを抽出した後に、オートフィルタを使って重複リストを取得します。

VBAコード

ForとIfを使って重複リストを作成するVBAコードです。

Sub TEST4()
    
    '重複判定用の配列を作成
    Dim Data
    ReDim Data(1 To 20000, 1 To 1)
    For i = 2 To 20001
        For j = 1 To i - 1
            '重複がある場合
            If ActiveSheet.Cells(i, "B") = ActiveSheet.Cells(j, "B") Then
                Data(i - 1, 1) = "重複"
                Exit For
            End If
        Next
    Next
    
    '重複判定用の配列をシートに貼り付け
    With ActiveSheet
        .Range("C2").Resize(UBound(Data, 1)) = Data
    End With
    
    'オートフィルタを設定
    ActiveSheet.Range("A1").AutoFilter
    '3列目を「重複」でフィルタする
    ActiveSheet.Range("A1").AutoFilter 3, "重複"
    
    '重複するリストを貼り付け
    With ActiveSheet.Range("A1").CurrentRegion
        .Resize(, .Columns.Count - 1).Copy ActiveSheet.Range("E1")
    End With
    
    'オートフィルタ解除
    ActiveSheet.Range("A1").AutoFilter
    '重複を判定する列をクリア
    ActiveSheet.Columns(3).Clear
    
End Sub

手順は、

  • ①重複を判定する配列を作成
  • ②重複を判定する配列をシートに貼り付け
  • ③オートフィルタで「重複」でフィルタする
  • ④重複するリストを取得して貼り付け
  • ⑤オートフィルタを解除
  • ⑥重複を判定する列をクリア

という流れです。

ちょっと長めですね。

先ほどと同じ、20,001行の重複したデータを用意しました。

20,001行の重複データ

重複したデータ

では、VBAコードを実行してみます。

まず、「For」と「If」を使って、重複を判定する配列を作成します。

重複を判定する配列を作成したら、その配列をシートを入力します。

重複を判定する配列をシートへ入力

重複を判定する配列を作成して、シートに入力

重複を判定する配列を、シートへ入力できました。

次に、オートフィルタを使って、「重複」でフィルタします。

「重複」でフィルタ

オートフィルタで「重複」をフィルタ

オートフィルタを使って「重複」でフィルタしました。

次は、重複するデータが出てきましたので、重複するデータをシートへ貼り付けです。

そのあと、オートフィルタを解除して、重複を判定する列をクリアします。

重複するリストを抽出

ForとIfで重複したリストを取得した結果

重複するリストを取得できました。

ちょっと工数が多い感じです。

時間を計測

では、問題の時間です。

かかった時間

かかった時間は、「4.69 秒」となりました。

速くなりました。

正直、「CountIf関数」を使った方が速いかと思ってました。

配列を使うのか、エクセル関数を使うのかは、場合によりけりですね。

重複したリストを抽出する際に、実行する速度を速くしたい場合は、「For」と「If」を使いましょう。

おわりに

この記事では、重複するリストを抽出する方法について、ご紹介しました。

重複リストを抽出する方法として、「CountIf関数」を使う方法と、「For」と「If」を組み合わせる方法がありました。

実行する速度は、「For」と「If」を使う方法が速いという結果となりました。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す