大体でIT

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

大体でIT

Excel VBAで、重複するリストを抽出するには、「CountIf関数」と「AdvancedFilter」が使えます。高速に重複リストを抽出したい場合は、「AdvancedFilter」で、シンプルさを求める場合は、「CountIf関数」が簡単です。重複データの操作に慣れていきましょう。

はじめに

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

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

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

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

データの量があまりなくて、速くなくてもいいのであれば、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関数の列をクリア
    Range("C2:C11").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(, 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」を使って、重複を判定する配列を作成します。

自分より上の値に、同じ値があるかを確認していきます。

値をループして重複を確認していく

自分より上の値に、同じ値がある場合は、配列に「重複」を入力していきます。

値をループして重複を確認していく

同じように最終行まで繰り返していきます。

値をループして重複を確認していく

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

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

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

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

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

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

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

オートフィルタを解除

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

作業列をクリア

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

重複リストを取得できた

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コードを実行してみます。

重複リストを取得できた

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

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

では、問題の時間です。

かかった時間

かかった時間は、「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を使って、重複しない値でフィルタします。

AdvancedFilterを使って、重複しない値でフィルタします

フィルタ結果の「重複」を削除します。

フィルタ結果の「重複」を削除します

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

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

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

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

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

オートフィルタを解除

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

作業列をクリア

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

重複リストを取得できた

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

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

重複リストを取得できた

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

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

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

かかった時間

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

かなり速いですね。

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

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

おわりに

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

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

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

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

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

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す