大体でIT

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

大体でIT

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

はじめに

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

重複するデータをチェックできると、データの解析や、入力ミスを探す作業に使えます。

結論から言うと、重複のチェックとを高速にしたい場合は、「For」と「If」を使った方法が速いです。

シンプルにVBAコードを書きたい場合は、「CountIf関数」を使う方法が簡単です。

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

この記事で紹介すること

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

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

目次

VBAで重複チェック

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

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

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

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

こんな感じです。

重複のチェック

重複チェックの仕方

例えば、「A」が重複していて、2行目の「A」は重複なしで、4列目の「A」は重複ありとします。

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

ForとIFを使う

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

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

Sub TEST1()
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 10, 1 To 1)
    
    With ActiveSheet
        '2行目から11行目をループ
        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 With
    
End Sub

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

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

次の表で試してみます。

重複するデータ

重複するデータ

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

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

ForとIfで重複チェック

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

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

WorksheetFunctionのCountIfを使う

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

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

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

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

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

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

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

重複データ

重複のあるデータ

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

WorksheetFunctionのCountIfで重複チェック

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

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

CountIf関数を使う

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

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

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

Sub TEST3()
    
    With ActiveSheet
        '重複をチェックするCountIf関数を、入力
        .Range("C2:C11") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
        .Range("C2:C11").Value = .Range("C2:C11").Value '値に変換
    End With
    
End Sub

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

R1C1で入力するのが、ポイントです。

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

重複データ

重複したデータ

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

CountIf関数で重複チェック

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

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

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

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

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

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

30,001行の重複データ

30001行のデータを用意

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

ForとIfで重複チェック

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

Sub TEST4()
    
    t = Timer
    
    '「重複」を保存する配列
    Dim Data
    ReDim Data(1 To 30000, 1 To 1)
    
    With ActiveSheet
        '2行目から30001行目をループ
        For i = 2 To 30001
            '1行目から、該当するセルの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 With
    
    Debug.Print Timer - t & " 秒"
    
End Sub

実行してみます。

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

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

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

結構、速いです。

WorksheetFunctionのCountIfで重複チェック

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

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

実行してみます。

WorksheetFunctionのCountIfで重複をチェック

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

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

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

CountIf関数で重複チェック

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

Sub TEST6()
    
    t = Timer
    
    With ActiveSheet
        '重複をチェックするCountIf関数を入力
        .Range("C2:C30001") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
        .Range("C2:C30001").Value = .Range("C2:C30001").Value '値に変換
    End With
    
    Debug.Print Timer - t & " 秒"
    
End Sub

実行してみます。

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

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

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

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

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

そこまでデータ数が多くない場合は、「CountIf関数」を使うと、VBAコードを少なくできます。

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

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

重複するデータのカウントは、先ほどの重複チェックのVBAコードを組み合わせます。

手順としては、

  • ①重複のチェックをする
  • ②「重複」のカウントする

という流れです。

重複のチェックは、「CountIf関数」を使う方法を使います。

重複のカウントは、先ほどと同じように、3パターンでやってみます。

ForとIFを使う

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

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

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

Sub TEST7()
    
    '重複のチェック
    With ActiveSheet
        .Range("C2:C11") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
        .Range("C2:C11").Value = .Range("C2:C11").Value '値に変換
    End With
    
    Count = 0 '重複カウント
    For i = 2 To 30001
        '「重複」お場合
        If ActiveSheet.Cells(i, "C") = "重複" Then
            Count = Count + 1 'カウントアップ
        End If
    Next
    
    '重複のカウントをセルに入力
    ActiveSheet.Cells(2, "D") = Count
    
End Sub

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

重複データ

重複したデータ

実行してみます。

ForとIfで重複をカウント

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

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

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

WorksheetFunctionのCountIfを使う

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

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

WorksheetFunctionの「CountIf」で重複のカウントをするVBAコードです。

Sub TEST8()
    
    '重複のチェック
    With ActiveSheet
        .Range("C2:C11") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
        .Range("C2:C11").Value = .Range("C2:C11").Value '値に変換
    End With
    
    '「重複」をカウントする
    a = WorksheetFunction.CountIf(ActiveSheet.Range("C2:C30001"), "重複")
    '重複のカウントを入力
    ActiveSheet.Cells(2, "D") = a
    
End Sub

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

重複データ

重複したデータ

実行してみます。

WorksheetFunctionのCountIfで重複をカウント

WorksheetFunctionのCountIfを使って、重複をカウントした結果

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

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

CountIf関数を使う

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

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

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

Sub TEST9()
    
    '重複のチェック
    With ActiveSheet
        .Range("C2:C11") = "=IF(COUNTIF(R1C2:R[-1]C[-1],RC[-1])>0,""重複"","""")"
        .Range("C2:C11").Value = .Range("C2:C11").Value '値に変換
    End With
    
    With ActiveSheet
        '重複をカウントする
        .Cells(2, "D") = "=COUNTIF(C2:C30001,""重複"")"
        .Cells(2, "D").Value = .Cells(2, "D").Value '値に変換
    End With
    
End Sub

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

重複データ

重複したデータ

実行してみます。

CountIf関数で重複をカウント

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

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

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

重複をカウントする方法は、「For」と「If」を使う方法が、シンプルですし、範囲の変更もしやすいです。

おわりに

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

重複のチェックを高速にしたい場合は、「For」と「If」を使った方法が速いです。

重複のチェックの際に、できるだけ短くVBAコードを書きたい場合は、「CountIf関数」がシンプルに書けます。

重複のカウントは、「For」と「If」でカウントした方が、シンプルにVBAコードを書くことができます。

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

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す