大体でIT

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

大体でIT

Excel VBAで、重複するリストを抽出する方法について、ご紹介します。重複するリストの抽出は、「Dictionary」を使うとかなり高速化できます。そこまで速さを求めない場合は、「CountIf関数」が簡単です。具体的に解説していきます。

はじめに

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

重複リストを抽出する方法として、次の3つを試してみます。

  • 「CountIf関数」を使う方法
  • 「For」と「If」を組み合わせる方法
  • 「Dictionary」を使う方法

結論から言うと、「Dictionary」がかなり高速です。

データの量があまりなくて、速くなくてもいいのであれば、VBAコードが簡単な、「CountIf関数」を使う方法がいいという感じです。

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

この記事で紹介すること

  • 重複するリストの抽出

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

目次

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関数で重複の判定
    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関数の列をクリア
    Columns(3).Clear
    
End Sub

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

  • ①重複するかをCountIf関数で判定
  • ②オートフィルタで「重複」をフィルタする
  • ③重複するリストをセルにコピー
  • ④オートフィルタを解除
  • ⑤CountIfの列を削除

という流れです。

ポイントとなるのは、「CountIf関数」を使うところですね。

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

重複したデータ

重複したデータ

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

重複するリストを抽出

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

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

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

時間を計測

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

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

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

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(, .Columns.Count - 1).Copy Range("E1")
        
    'オートフィルタ解除
    Range("A1").AutoFilter
    
    'CountIf関数の列をクリア
    Columns(3).Clear
    
    Debug.Print Timer - t & " 秒"
    
End Sub

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

重複するデータを抽出

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

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

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

「CountIf関数」を入力するときに、かなり時間がかかってしまいます。

ただ、VBAコード自体は、シンプルなので、速くなくてもいい場合には、使えます。

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

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

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

VBAコード

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

Sub TEST3()
    
    t = Timer
    
    '重複判定用の配列を作成
    Dim Data
    ReDim Data(1 To 20000, 1 To 1)
    For i = 2 To 20001
        For j = 1 To i - 1
            '重複がある場合
            If Cells(i, "B") = Cells(j, "B") Then
                Data(i - 1, 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
    
    '重複を判定する列をクリア
    Columns(3).Clear
    
    Debug.Print Timer - t & " 秒"
    
End Sub

手順は、

  • ①「ForとIF」で重複を判定する配列を作成
  • ②重複を判定する配列をセルに入力
  • ③オートフィルタで「重複」でフィルタする
  • ④重複するリストをコピー
  • ⑤オートフィルタを解除
  • ⑥重複を判定する列をクリア

という流れです。

手順の①と②が変わっています。

「For」と「IF」を使って重複するデータを判定しています。

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

20,001行の重複データ

重複したデータ

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

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

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

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

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

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

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

「重複」でフィルタ

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

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

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

重複するリストをコピー

フィルタした結果をコピーする

次は、オートフィルタを解除します。

オートフィルタを解除

オートフィルタを解除

オートフィルタを解除できました。

最後に作業列をクリアします。

作業列をクリア

作業列をクリア

これで、重複リストが取得できます。

重複リストを取得できた

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

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

時間を計測

では、問題の時間です。

かかった時間

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

速くなりました。

「CountIf関数」を使う場合は、「39.27 秒」だったので、速くなっています。

「ForとIF」を使った方が、重複リストの作成は速いです。

ただ、2万行ぐらいならもう少し速くしたいです。

次に説明する「Dictionary」を使うともっと速くできます。

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

VBAでDictionary+オートフィルタを使って重複リストを抽出してみます。

VBAコード

「Dictionary」を使って重複リストを作成するVBAコードです。

Sub TEST4()
    
    t = Timer
    
    '辞書のオブジェクトを作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim Data
    ReDim B(1 To 20001, 1 To 1)
    
    For i = 2 To 20001
        '辞書に登録されていない場合は登録
        If A.exists(Cells(i, "B").Value) = False Then
            A.Add Cells(i, "B").Value, 1 '登録
        '登録されている場合
        Else
            B(i - 1, 1) = "重複" '配列に入力
        End If
    Next
    
    '重複判定用の配列をセルに入力
    Range("C2").Resize(UBound(B, 1)) = B
    
    '3列目を「重複」でフィルタ
    Range("A1").AutoFilter 3, "重複"
    
    '重複するリストをコピー
    Range("A1").CurrentRegion.Resize(, 2).Copy Range("E1")
    
    'オートフィルタを解除
    Range("A1").AutoFilter
    
    '重複を判定する列をクリア
    Columns(3).Clear
    
    Debug.Print Timer - t & " 秒"
    
End Sub

手順は、

  • ①「Dictionary」を使って重複を判定する配列を作成
  • ②重複を判定する配列をセルに入力
  • ③オートフィルタで「重複」でフィルタする
  • ④重複するリストをコピー
  • ⑤オートフィルタを解除
  • ⑥重複を判定する列をクリア

という流れです。

手順の③以降は、他の方法と同じです。

では、実際に実行してみます。

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

20,001行の重複データ

重複したデータ

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

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

「Dictionary」を使うとかなり速く重複を判定できます。

重複を判定する「商品」の列をループしていきます。

「辞書」に登録されていない場合は、「登録」していきます。

「辞書」に登録されていない場合は「登録」する

「辞書」に登録されていない場合は「登録」する

既に登録されている場合は、「配列」に「重複」を入力します。

「辞書」に登録されている場合は「配列」に「重複」を入力

「辞書」に登録されている場合は「重複判定用の配列」に「重複」を入力する

一番下まで「商品」の列をループして、配列を完成させたら、セルに入力します。

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

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

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

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

「重複」でフィルタ

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

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

次は、重複するデータが出てきましたので、重複するデータをセルにコピーします。

重複するリストをコピー

フィルタした結果をコピーする

次は、オートフィルタを解除します。

オートフィルタを解除

オートフィルタを解除

オートフィルタを解除できました。

最後に作業列をクリアします。

作業列をクリア

作業列をクリア

これで、重複リストが取得できます。

重複リストを取得できた

Dictionaryで重複したリストを取得した結果

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

時間を計測

では、「Dictionary」を使って重複リストを作成するのに、かかった時間です。

かかった時間

結果は、「0.48 秒」となりました。

かなり速いですね。

「2万行」で1秒かかってないので、十分実務で使えるレベルです。

重複リストの作成を高速化したい場合は、「Dictionary」を使いましょう。

おわりに

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

重複リストを抽出する方法として、次の3つを試してみました。

  • 「CountIf関数」を使う方法
  • 「For」と「If」を組み合わせる方法
  • 「Dictionary」を使う方法

データが大量にある場合で、重複リストを「高速」に抽出したい場合は、「Dictionary」が使えます。

データの量があまりなくて、速くなくてもいいのであれば、VBAコードが簡単な、「CountIf関数」を使う方法がいいです。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す