大体でIT

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

HOME >  Excel VBA >  データの操作
大体でIT

Excel VBAで、重複するデータをチェックする方法と、カウントする方法について、ご紹介します。重複のチェックやカウントを、高速にしたい場合は、「Dictionary」を使った方法が、速いです。VBAコードをシンプルにしたい場合は、「COUNTIF関数」を使うと簡単になります。

はじめに

この記事では、重複するデータのチェックと、カウントする方法について、ご紹介します。

使う方法は、次の4つです。

  • COUNTIF関数
  • WorksheetFunction.CountIf
  • ForとIfの組み合わせ
  • Dictionary

重複のチェックやカウントでは、「Dictionary」を使った方法がダントツに速いです。

VBAコードが一番シンプルになるのは、「COUNTIF関数」です。

高速に処理したい場合は、「Dictionary」を使うといいですね。

データ量がそこまで多くないという場合は、「COUNTIF関数」が簡単です。

場面に応じて、使う方法を変えるといいです。

では、重複するデータのチェックと、カウントする方法について、解説していきます。

この記事で紹介すること

  • 重複するデータのチェック
  • 重複するデータのカウント

こちらの記事も参考になります

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

目次

VBAで重複チェック

VBAで重複をチェックする方法について、ご紹介します。

データ解析などで、重複して入力してしまったデータをチェックする際に、使えるテクニックです。

重複チェックについて、4つの方法を紹介して、実行速度を比較してみます。

やりたいこと

重複するデータは、1つ目のデータは重複なしとして、2つ目から重複と判定します。

こんな感じです。

重複のチェック

重複チェックの仕方

例えば、「A」が重複していて、1つ目の「A」は「重複なし」として、2つ目以降の「A」は「重複あり」とします。

では、重複のチェックについて、VBAコードを作成してみます。

COUNTIF関数を使う

3つ目は、COUNTIF関数を使う方法です。

エクセル関数を使って重複をチェックする方法です。

COUNTIF関数を使って、重複をチェックするVBAコードはこちらです。

Sub TEST1()
    
    With ActiveSheet
        '重複をチェックするCOUNTIF関数を、入力
        .Range("C2:C11") = "=IF(COUNTIF($B$1:B1,B2)>0,""重複"","""")"
        .Range("C2:C11").Value = .Range("C2:C11").Value '値に変換
    End With
    
End Sub

エクセル関数のCountIfをセルに入力しています。

重複チェックの手順

商品の列をループして、自分のセルより上のセルに「重複」する値があるかをチェックします。

自分のセルより上のセルに重複する値があるかをチェック

自分のセルより上のセルに「重複する」値があれば、「重複」を配列に入力します。

自分のセルより上のセルに重複する値があれば重複を入力する

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

重複データ

重複したデータ

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

COUNTIF関数で重複チェック

COUNTIF関数を使って重複をチェックした結果

COUNTIF関数を使って重複をチェックできました。

VBAコードとしては、一番シンプルになります。

WorksheetFunctionのCountIfを使う

次は、WorksheetFunctionのCountIfを使って、重複をチェックしてみます。

WorksheetFunctionを使えば、エクセル関数を、VBAで使うことができます。

結果としては、VBAコードが複雑なうえに遅いです。

WorksheetFunctionのCountIfを使って、重複をチェックするVBAコードです。

Sub TEST2()
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 11, 1 To 1)

    '「商品」の列をループ
    For i = 2 To 11
        '該当するセルの上の行までで、重複する数をカウント
        A = WorksheetFunction.CountIf(Range("B1:B" & i - 1), Cells(i, "B"))
        '重複があった場合
        If A > 0 Then Data(i, 1) = "重複"
    Next
    
    '配列をセルに入力
    Range("C1").Resize(UBound(Data, 1)) = Data
    
End Sub

重複のチェックの考え方は同じで、自分より上の行に、自分と同じ値がある場合は、「重複」と判定します。

では、重複のあるデータを用意しました。

重複データ

重複のあるデータ

VBAコードを実行して、重複をチェックします。

WorksheetFunctionのCountIfで重複チェック

WorksheetFunctionのCountIfを使って、重複をチェックした結果

WorksheetFunctionのCountIfを使って、重複をチェックできました。

ForとIFを使う

次は、VBAの基本のForとIfを使う方法です。

ForとIfで重複をチェックするVBAコードは、こちらになります。

Sub TEST3()
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 10, 1 To 1)
    
    '「商品」の列をループ
    For i = 2 To 11
        '1行目から、該当セルの上の行までをループ
        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
    
End Sub

自分の行より上の値に、同じ値があるかをIfで判定しています。

自分の行より上に、同じ値がある場合は、「重複」となります。

次の表で試してみます。

重複するデータ

重複するデータ

いくつか重複するデータがある表です。

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

ForとIfで重複チェック

ForとIfを使って重複チェックした結果

ForとIfを使って、重複をチェックできました。

Dictonaryを使う

「Dictionary」を使って、重複をチェックします。

この方法が一番高速です。

ただ、ちょっとわかりづらいではあります。

Sub TEST4()
    
    'Dictionaryのオブジェクトを作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 11, 1 To 1)
    
    '「商品」の列をループ
    For i = 2 To 11
        'まだ登録されていない場合
        If A.exists(Cells(i, "B").Value) = False Then
            A.Add Cells(i, "B").Value, 1 '登録する（1はなんでもいい）
        '既に登録されている場合
        Else
            Data(i, 1) = "重複" '配列に「重複」を入力
        End If
    Next
    
    '配列をセルに入力
    Range("C1").Resize(UBound(Data, 1)) = Data
        
End Sub

「Dictionary」は、ユニークのデータを登録することができます。

同じデータを登録することができないので、その性質を利用して、重複するデータを判定します。

「Dictionary」の手順

「商品」の列をループして、辞書にまだ登録されていない場合は、「辞書に登録」します。

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

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

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

辞書に登録されているかを判定するのが高速なので、重複の判定が速いです。

では、次の表で、やってみます。

重複する表

重複する表を用意する

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

重複をチェックできた

重複をチェックできた

重複をチェックできました。

重複チェックで高速なのは？

では、重複をチェックする4つの方法で、実行時間を計測してみます。

30,001行のデータを用意しました。

30,001行の重複データ

30001行のデータを用意

この重複するデータで重複のチェックをしてみます。

COUNTIF関数で重複チェック

3つ目は、COUNTIF関数を使って重複をチェックしてみます。

Sub TEST5()
    
    t = Timer
    
    With ActiveSheet
        '重複をチェックするCOUNTIF関数を入力
        .Range("C2:C30001") = "=IF(COUNTIF($B$1:B1,B2)>0,""重複"","""")"
        .Range("C2:C30001").Value = .Range("C2:C30001").Value '値に変換
    End With
    
    Debug.Print Timer - t & " 秒"
    
End Sub

実行してみます。

COUNTIF関数を使って重複をチェックした結果

重複をチェックできました。

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

ちょっと時間かかりますね。

WorksheetFunctionのCountIfで重複チェック

次は、WorksheetFunctionのCountIfで重複をチェックしてみます。

Sub TEST6()
    
    t = Timer
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 30001, 1 To 1)
    
    '「商品」の列をループ
    For i = 2 To 30001
        '該当するセルの一つ上の行までで、重複をカウント
        A = WorksheetFunction.CountIf(Range("B1:B" & i - 1), Cells(i, "B"))
        '重複があった場合
        If A > 0 Then Data(i, 1) = "重複"
    Next
    
    '配列をセルに入力
    Range("C1").Resize(UBound(Data, 1)) = Data
        
    Debug.Print Timer - t & " 秒"
    
End Sub

実行してみます。

WorksheetFunctionのCountIfで重複をチェック

重複をチェックできました。

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

先ほどより遅くなりました。

ForとIfで重複チェック

次は、「For」と「If」で重複をチェックする方法です。

Sub TEST7()
    
    t = Timer
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 30001, 1 To 1)
    
    '「商品」の列をループ
    For i = 2 To 30001
        '1行目から、該当するセルの1つ上の行までループ
        For j = 1 To i - 1
            '重複している場合
            If Cells(i, "B") = Cells(j, "B") Then
                Data(i, 1) = "重複"
                Exit For
            End If
        Next
    Next
    
    '配列をセルに入力
    Range("C1").Resize(UBound(Data, 1)) = Data
    
    Debug.Print Timer - t & " 秒"
    
End Sub

実行してみます。

ForとIfで重複チェックをした結果

重複をチェックできました。

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

結構、速いです。

「Dictionary」で重複チェック

次は、「Dictionary」で重複チェックしてみます。

Sub TEST8()
    
    t = Timer
    
    'Dictionaryのオブジェクトを作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 30001, 1 To 1)
    
    '「商品」の列をループ
    For i = 2 To 30001
        'まだ登録されていない場合
        If A.exists(Cells(i, "B").Value) = False Then
            A.Add Cells(i, "B").Value, 1 '登録する（1はなんでもいい）
        '既に登録されている場合
        Else
            Data(i, 1) = "重複" '配列に「重複」を入力
        End If
    Next
    
    '配列をセルに入力
    Range("C1").Resize(UBound(Data, 1)) = Data
       
    Debug.Print Timer - t & " 秒"
       
End Sub

実行してみます。

Dictionaryで重複チェックした結果

重複をチェックできました。

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

かなり速いです。

結果として、大量の重複チェックを高速にしたい場合は、「Dictionary」を使うといいです。

そこまでデータ数が多くない場合は、「COUNTIF関数」を使うと、VBAコードをシンプルなので簡単です。

VBAで重複するデータをカウント

VBAで重複するデータをカウントする方法を、ご紹介します。

やりたいこと

やりたいことは、重複するデータをカウントする、ということです。

重複するデータをカウントしたい

次の3パターンでやってみます。

  • COUNTIF関数を使う
  • ForとIFを使う
  • Dictionaryを使う

という感じです。

WorksheetFunction.CountIfは遅いし、コードも複雑なので省きます。

では、COUNTIF関数からです。

COUNTIF関数を使う

「COUNTIF関数」を使って、重複のカウントをしてみます。

やり方は、重複のチェックをした後に、「重複」の数をカウントします。

「COUNTIF関数」で重複のカウントをするVBAコードです。

Sub TEST9()
    
    '重複のチェック
    Range("C2:C11") = "=IF(COUNTIF($B$1:B1,B2)>0,""重複"","""")"
    Range("C2:C11").Value = Range("C2:C11").Value '値に変換
        
    '重複をカウント
    Cells(2, "D") = "=COUNTIF(C2:C11,""重複"")" 'COUNTIF関数で「重複」をカウント
    Cells(2, "D").Value = Cells(2, "D").Value '値に変換
    
End Sub

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

重複データ

重複したデータ

実行してみます。

最初に、重複データをチェックします。

重複データをチェック

重複したデータをチェックできます

「COUNTIF関数」で重複をカウントします。

COUNTIF関数で重複をカウント

COUNTIF関数を使って、重複をカウントした結果

結果は、「4」個となりました。

重複をカウントできています。

ForとIFを使う

「For」と「If」を使って、重複のカウントをしてみます。

「For」と「If」で重複のカウントをするVBAコードです。

Sub TEST10()
    
    Dim Cn
    Cn = 0
    '「商品」の列をループ
    For i = 2 To 11
        '1行目から、該当するセルの1つ上の行までループ
        For j = 1 To i - 1
            '重複している場合
            If Cells(i, "B") = Cells(j, "B") Then
                Cn = Cn + 1 'カウントアップ
                Exit For
            End If
        Next
    Next
    
    '重複している個数を入力
    Range("D2") = Cn
    
End Sub

重複するデータをカウントする手順です。

重複するデータをカウントする手順

「商品」の列で、自分より上のセルの重複をチェックします。

自分より上のセルの重複をチェックする

自分より上のセルに重複があればカウントアップする、という感じです。

自分より上のセルに重複があればカウントアップする

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

重複データ

重複したデータ

実行してみます。

ForとIfで重複をカウント

ForとIfを使って、重複をカウントした結果

結果は、「4」個となりました。

重複をカウントできています。

Dictionaryを使う

「Dictionary」を使って、重複のカウントをしてみます。

Sub TEST11()
    
    'Dictionaryのオブジェクトを作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim Cn
    Cn = 0
    '「商品」をループ
    For i = 2 To 11
        'まだ登録されていない場合
        If A.exists(Cells(i, "B").Value) = False Then
            A.Add Cells(i, "B").Value, 1 '登録する（1はなんでもいい）
        '既に登録されている場合
        Else
            Cn = Cn + 1 'カウントアップ
        End If
    Next
    
    '重複している回数を入力
    Cells(2, "D") = Cn
        
End Sub

重複するデータをカウントする手順です。

重複するデータをカウントする手順

「商品」の列で、登録されていない場合は登録します。

登録されていない場合は登録する

すでに登録されている場合はカウントアップする、という感じです。

すでに登録されている場合はカウントアップする

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

重複データ

重複したデータ

実行してみます。

「Dictionary」で重複をカウント

Dictionaryを使って、重複をカウントした結果

結果は、「4」個となりました。

重複をカウントできています。

重複データのカウントで高速なのは？

では、重複データのカウントで実行速度を計測してみます。

次の重複データを使って試してみます。

30001行のデータを用意

30001行のデータを用意しました

では、「COUNTIF関数」の方法からやってみます。

「COUNTIF関数」を使う

「COUNTIF関数」を使って、重複データのカウントするVBAコードです。

Sub TEST12()
    
    t = Timer
    
    '重複のチェック
    Range("C2:C30001") = "=IF(COUNTIF($B$1:B1,B2)>0,""重複"","""")"
    Range("C2:C30001").Value = Range("C2:C30001").Value '値に変換
    
    '重複をカウント
    Cells(2, "D") = "=COUNTIF(C2:C30001,""重複"")" 'COUNTIF関数で「重複」をカウント
    Cells(2, "D").Value = Cells(2, "D").Value '値に変換
        
    Debug.Print Timer - t & " 秒"
    
End Sub

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

最初に重複のデータが入力されます。

重複のデータが入力されます

重複するデータがカウントできます。

COUNTIF関数で重複するデータのカウントができました

重複するデータがカウントできました。

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

「COUNTIF関数」は、遅いです。

「ForとIf」を使う

「ForとIf」を使って、重複データのカウントするVBAコードです。

Sub TEST13()
    
    t = Timer
    
    Dim Cn
    Cn = 0
    '「商品」の列をループ
    For i = 2 To 30001
        '1行目から、該当するセルの1つ上の行までループ
        For j = 1 To i - 1
            '重複している場合
            If Cells(i, "B") = Cells(j, "B") Then
                Cn = Cn + 1 'カウントアップ
                Exit For
            End If
        Next
    Next
    
    '重複している個数を入力
    Range("D2") = Cn
    
    Debug.Print Timer - t & " 秒"
    
End Sub

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

ForとIfで重複するデータのカウントができました

重複するデータがカウントできました。

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

「Dictionary」を使う

「Dictionary」を使って、重複データのカウントするVBAコードです。

Sub TEST14()
    
    t = Timer
    
    'Dictionaryのオブジェクトを作成
    Dim A
    Set A = CreateObject("Scripting.Dictionary")
    
    Dim Cn
    Cn = 0
    '「商品」をループ
    For i = 2 To 30001
        'まだ登録されていない場合
        If A.exists(Cells(i, "B").Value) = False Then
            A.Add Cells(i, "B").Value, 1 '登録する（1はなんでもいい）
        '既に登録されている場合
        Else
            Cn = Cn + 1 'カウントアップ
        End If
    Next
    
    '重複している回数を入力
    Cells(2, "D") = Cn
    
    Debug.Print Timer - t & " 秒"
        
End Sub

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

Dictionaryで重複するデータのカウントができました

重複するデータがカウントできました。

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

「Dictionary」、かなり高速です。

速さを求めるなら、「Dictionary」ですね。

遅くてもいいので、シンプルなVBAコードがいい場合は、「COUNTIF関数」を使うといいです。

おわりに

この記事では、重複するデータのチェックと、カウントする方法について、ご紹介しました。

使った方法は、次の4つです。

  • COUNTIF関数
  • WorksheetFunction.CountIf
  • ForとIfの組み合わせ
  • Dictionary

重複のチェックやカウントでは、「Dictionary」を使った方法がダントツに速いです。

VBAコードが一番シンプルになるのは、「COUNTIF関数」です。

高速に処理したい場合は、「Dictionary」を使うといいですね。

データ量がそこまで多くないという場合は、「COUNTIF関数」が簡単です。

場面に応じて、使う方法を変えるといいです。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す