この記事では、「重複する行を探す」作業を自動化する方法について解説します。
EXCELの機能で「重複の削除」がありますが、重複する行の削除はしてくれますが、どの行が重複していたかは教えてはくれません。
データ入力ミスで重複していたのか、本当に重複していたのかわからず、データ修正ができません。
そこで、今回はデータ一覧から重複する行を見つけてくれるツールを作りました。
重複する行に色を付けるので、どの行が重複しているのかを見つけることができます。
重複する行を見つける機能は、汎用的に作っていますので、好きなデータを利用することが可能となっています。
また、重複行と判断する列を好きに指定可能ですので、思ったように重複行を探すことができます。
それでは、さっそく重複データを探す機能を作っていきましょう。
困っている女性
困っている女性
困っている女性
解決する男性
解決する男性
困っている女性
もくじ
「ツール概要図」（重複する行を探す）
今回作成するツールの概要図は、以下です。
重複行を探すツール機能図
今回の「重複する行を探すツール」は、以下を作成する必要があります。
- 重複行を判断する列を指定するセル：ツールシート
- 調査対象のデータを貼り付けするシート：データシート
- VBA（指定された列で調査対象データの重複行を抽出する）
手っ取り早く、このツールを利用したい方は、以下から「ダウンロード購入」できます。
BOOTH）重複する行を探したい：【ExcelVBAツール】
Youtube動画に「重複する行を探すVBAツール」の作り方をアップしました。
よかったら、見てください。
それでは、順番にツールの作り方を説明します。
重複行を判断する列を指定する欄を準備する
列を指定する欄を作成します。
「ツール」シートを作成する
ツールシートを作成する
- 「ツール」という名前のシートを作成します。
- A1セルに「キー列」のタイトル欄を作成します。
- A1セルの背景色をつけておきます。（サンプルでは、「薄い緑」にしています。）
- A2セル以降については、重複を判断する列を入力する欄になります。
対象データにより、A2セル以降に指定する列は随時変更することになります。
重複を調査するデータを貼り付けるシートを準備する
つぎに、調査対象データを貼り付けるシートを作成していきます。
「データ」シートを作成する
データシートを作成する
- 「データ」という名前のシートを作成します。
- このシートに調査対象のデータを貼り付けます。
サンプルデータとして、A1セルからE9セルまで貼り付けています。
お好きなデータをA1セルから貼り付けてください。
VBAで重複する行を探す機能の作成
開発タブの「Visual Basic」をクリックして、「Microsoft Visual Basic for Applications」を起動します。
Microsoft Visual Basic for Applicationsを起動する
「Microsoft Visual Basic for Applications」にVBAプログラムを入力します。
重複する行を探すVBAプログラム
標準モジュールを追加して、以下のソースを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
Sub 重複行をハイライト() ' 変数を宣言する Dim shtTool As Worksheet Dim shtData As Worksheet Dim toolLastRow As Long Dim dataLastRow As Long Dim i As Long Dim j As Long Dim k As Long Dim keyCol As Long Dim blnSame As Boolean ' 各シートを変数に格納する Set shtTool = ThisWorkbook.Sheets("ツール") Set shtData = ThisWorkbook.Sheets("データ") ' ツールシートのキー列の最終行を取得する toolLastRow = shtTool.Cells(shtTool.Rows.Count, 1).End(xlUp).Row ' データシートの最終行を取得する（キー列番号の最初の列番号を利用） dataLastRow = shtData.Cells(shtData.Rows.Count, CLng(shtTool.Cells(2, 1))).End(xlUp).Row ' データシートのハイライトを全てクリアする shtData.Cells.Interior.ColorIndex = xlColorIndexNone ' 重複チェックする For i = 1 To dataLastRow - 1 For j = i + 1 To dataLastRow blnSame = True 'キー列の値を比較する For k = 2 To toolLastRow keyCol = shtTool.Cells(k, 1) If shtData.Cells(i, keyCol).Value <> shtData.Cells(j, keyCol).Value Then blnSame = False Exit For End If Next ' 重複があれば黄色でハイライトする If blnSame Then shtData.Rows(i).Interior.Color = vbYellow shtData.Rows(j).Interior.Color = vbYellow End If Next Next MsgBox "重複がある行は、黄色でハイライトしました。", vbInformation End Sub |
次に、EXCELを「◯◯◯.xlsm」というような感じでマクロ有効ブック形式で保存します。
重複する行を探すツールの使い方
それでは、重複する行を探すツールの使い方を説明します。
「ツール」シートのA2セル以降にキー列を入力します。
今回のサンプルでは、A2セルに「2」、A3セルに「4」を入力します。
キー列を指定する
「開発」タブの「マクロ」をクリックします。
VBAプログラムを実行する
先程作成したVBAプログラム「重複行をハイライト」が一覧に表示されていますので、選択します。
次に、「実行」ボタンをクリックします。
重複行をハイライトを実行する
しばらく待つと、「データ」シートの重複する行が黄色でハイライトされます。
重複行がハイライトされます。
B列「従業員NO」とD列「部署名」が重複する行がハイライトされました。
先ほど、「ツール」シートで「2」と「4」を指定したからです。
それでは、キー列を「2、4、5」に変更して実行してみましょう。
キー列を変更する
今度は、B列「従業員NO」とD列「部署名」とE列「勤務地」が重複する行のみハイライトされました。
ハイライト行が変更された
好きなデータで「重複する行を探すプログラム」を動かしてみましょう。
ちなみに、サンプルデータではE列までの5列のデータですが、10列でも20列でも好きな列数のデータを利用できます。
重複する行を探すVBAプログラム（重複行をハイライト）の説明
それでは、重複する行を探すVBAプログラム（重複行をハイライト）を説明します。
①変数を宣言する
|
1 2 3 4 5 6 7 8 9 10 |
' 変数を宣言する Dim shtTool As Worksheet Dim shtData As Worksheet Dim toolLastRow As Long Dim dataLastRow As Long Dim i As Long Dim j As Long Dim k As Long Dim keyCol As Long Dim blnSame As Boolean |
②各シートを変数に格納する
|
1 2 3 |
' 各シートを変数に格納する Set shtTool = ThisWorkbook.Sheets("ツール") Set shtData = ThisWorkbook.Sheets("データ") |
③ツールシートのキー列の最終行を取得する
|
1 2 |
' ツールシートのキー列の最終行を取得する toolLastRow = shtTool.Cells(shtTool.Rows.Count, 1).End(xlUp).Row |
例えば、A3セルまで入力されていた場合、3を取得します。
④データシートの最終行を取得する
|
1 2 |
' データシートの最終行を取得する（キー列番号の最初の列番号を利用） dataLastRow = shtData.Cells(shtData.Rows.Count, CLng(shtTool.Cells(2, 1))).End(xlUp).Row |
「ツール」シートのキー列に指定されている最初の列番号を利用して、「データ」シートのその列番号の最終行を取得しています。
つまり、「ツール」シートのA2セルに「2」と入力されていた場合、「データ」シートの2列目の最終行を取得します。
⑤データシートのハイライトを全てクリアする
|
1 2 |
' データシートのハイライトを全てクリアする shtData.Cells.Interior.ColorIndex = xlColorIndexNone |
前回実行でセットされた黄色ハイライトを全てクリアしています。
⑥重複チェックし、重複する行をハイライトする
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' 重複チェックする For i = 1 To dataLastRow - 1 For j = i + 1 To dataLastRow blnSame = True 'キー列の値を比較する For k = 2 To toolLastRow keyCol = shtTool.Cells(k, 1) If shtData.Cells(i, keyCol).Value <> shtData.Cells(j, keyCol).Value Then blnSame = False Exit For End If Next ' 重複があれば黄色でハイライトする If blnSame Then shtData.Rows(i).Interior.Color = vbYellow shtData.Rows(j).Interior.Color = vbYellow End If Next Next |
外側のループ（ i ）では、データシートの1行目から最終行-1行目まで処理を行います。
内側のループ（ j ）では、（ i ）＋1行目からデータシートの最終行目まで処理を行います。
外側のループが1行目なら1行目の値と、内側のループが2行目から最終行までの値を比較します。
比較する列は、「ツール」シートで指定されたキー列すべてが同じになっているか確認します。
すべての列が同じなら外側のループ行と内側のループ行の両方を黄色でハイライトします。
列の値が１つでも異なれば、次のループ処理に行くようにしています。
この機能を利用して、重複する行を探す作業の負担を減らしましょう。
以上です。
手っ取り早く、このツールを利用したい方は、以下から「ダウンロード購入」できます。
BOOTH）重複する行を探したい：【ExcelVBAツール】
最後まで読んでいただきありがとうございました。
この記事をシェアしていただけると喜びます。
