Excelで重複データの差分を抽出したいときはないでしょうか。
けど、そんな中で悩むことは、
・VBAでExcelの重複データ差分抽出と出力を一括で処理したいがやり方がわからない。
ですよね。
今回はそんなお悩みを解決する
Excelで重複データの差分を抽出する方法とVBAで瞬間に処理する方法について
まとめます!
もくじ
Excelで重複データがある状態とは?
例えばですが、会社で何かしらの利用申請をする機会はあるかと思います。
具体的にはパソコンとか、携帯端末の導入だったり。
そんなときに2重に申請をしてしまい、同じ内容のデータが台帳上にあるという状態が
データの重複しているという状態になります。
![タカヒロ](/wp-content/uploads/2020/11/taka_kao.gif)
それを回避するため、事前に重複データがないかチェックをするということが想定されるかと思います。
タカヒロも上司や顧客からの要望で、重複データのチェックはよくやります。
Excelで重複データの差分を抽出するイメージ
VBAでExcelで重複データの差分を抽出するイメージについて説明をします。
Excelで重複データがある可能性のあるリストデータを用意します。
リストの中で2件以上あるデータを識別していきます。
重複データを抽出する方法としては、
まず初めに関数を利用し抽出していきます。
次の方法はピボットテーブルを使って抽出していきます。
最後にこれまで複数ステップの作業が必要であった処理を
VBAを使い、ワンクリックで一気に処理していきます。
それでは早速使ってみましょう!
差分抽出したいデータを用意する
まずは、差分を抽出したいリストデータを用意しましょう。
データの内容はなんでもよいですが、重複する文字列を2個以上いれるようにしてください。
サンプルは以下のようにしました。
関数で重複データの差分を抽出する
関数で重複データの差分を抽出してみましょう。
使う関数はおなじみのCOUNTIF関数です。
COUNTIF関数を隣接セルへ入力する
COUNTIF関数を重複チェックをしたいワードがある隣接セルへ入力しましょう。サンプルではB2セルへ以下の数式を入力しています。
=COUNTIF(A:A,A2)
続いて、リストの最終行までB2セルに入力した数式をドラッグし、コピーします。
重複件数があるワードに絞り込む
COUNTIF関数の結果が表示されましたので、重複を示す2件以上を対象とするようフィルターで絞り込みます。
重複ワードを消去する
絞り込んだ結果を別のセルへ値のみコピーし、
「データ」タブの「データツール」欄の重複の削除をクリックし実行します。
![](https://extan.jp/wp-content/uploads/2021/10/100821_0901_6.png)
はい、こちらでユニークなデータになりましたね。
![](https://extan.jp/wp-content/uploads/2021/10/100821_0901_10.png)
ピボットテーブルで重複データの差分を抽出する
ピボットテーブルで重複データの差分を抽出してみましょう。
ピボットテーブルを作成する
重複データを抽出したいセルを選択します。
挿入タブからピボットテーブルボタンをクリックします。
ピボットテーブルの作成ウイザードがでたらそのままOKをクリックします。
ピボットテーブルのフィールドを設定する
ピポッドテーブルの「行」に項目名をドラッグします。同じく「値」にもドラッグし、「個数」を選択します。
COUNTIF関数とおなじ結果が得られましたね。
絞り込みの方法はCOUNTIF関数と同様になります。
![タカヒロ](/wp-content/uploads/2020/11/taka_kao.gif)
VBAで重複データの差分を抽出する
これまでいくつかの手作業が必要でしたが、何度も続くとちょっとしんどいですよね。
そこで、VBAを使い、ワンクリックで処理をするようにしましょう。
データはこれまでと同様で、さらに差分を抽出して出力するセルを追加します。
サンプルではデータがあるシートのD2セル以降としていますので、空白にしておきましょう。
次にExcelで重複データの差分を抽出するVBAを準備します。
サンプルコードは以下の通りです。
Sub 重複文字を抽出し結果を出力する()
Dim strSerchKeys As Variant
Dim strSerchlist As Variant
Dim strSerchResults As Variant
Dim strSerchKey As String
Dim strSerchResultsCell As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim rangeTemp As Object
Dim objSerchlist As Object
'設定 --ここから
'検索先リストの範囲を指定します。
Set objSerchlist = Range("A2:A12")
'抽出結果の出力先セルを指定します。
strSerchResultsCell = "D2"
'設定 --ここまで
'検索先リストを配列へ格納します。
strSerchlist = objSerchlist
'検索結果出力用の開始位置番号です。
k = 1
'重複件数カウント用の開始番号です。
l = 0
'キーワードリストを作成します。--ここから
'キーワードリスト作業用のオブジェクトを作成します。
Set rangeTemp = Range(strSerchResultsCell).Resize(UBound(strSerchlist, 1), 1)
'検索先リストをコピーします。
objSerchlist.Copy
'検索先リストを作業用セルに貼り付けます。
Range(strSerchResultsCell).PasteSpecial Paste:=xlPasteValues
'検索先リストから重複分を削除します。
Range(strSerchResultsCell).RemoveDuplicates Columns:=1, Header:=xlNo
'最終行の位置番号を取得後、開始位置番号とマイナスし配列の要素数を算出します。
intLastRow = Cells(Rows.Count, Range(strSerchResultsCell).Column).End(xlUp).Row - Range(strSerchResultsCell).Row + 1
'重複削除した検索先リストをキーワードリストへ格納します。
strSerchKeys = Range(strSerchResultsCell).Resize(intLastRow, 1)
'キーワードリストを作成します。--ここまで
'作業用領域の値のみをクリアします。
rangeTemp.ClearContents
'検索結果出力用の配列を作成します。
strSerchResults = rangeTemp
'キーワードリストに格納された値すべてを対象に処理します。
For i = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
l = 0
'キーワードリストからキーワードを取り出します。
strSerchKey = strSerchKeys(i, 1)
'検索先リストと照合します。
For j = LBound(strSerchlist, 1) To UBound(strSerchlist, 1)
'検索先リストのワードとキーワードが一致した場合の条件です。
If strSerchlist(j, 1) = strSerchKey Then
l = l + 1
'2回一致していたら検索結果出力用の配列にキーワードを格納します。
If l = 2 Then
strSerchResults(k, 1) = strSerchKey
k = k + 1
Exit For
End If
End If
Next j
Next i
'検索結果を指定セルへ出力します。
Range(strSerchResultsCell).Resize(UBound(strSerchResults, 1), 1) = strSerchResults
Set objSerchlist = Nothing
Set rangeTemp = Nothing
End Sub
VBAの設定をする
Excelで重複データの差分を抽出するVBAに先ほど作成した表データの場所を設定しましょう。
設定箇所は以下の通りです。
検索値の範囲を指定します。サンプルの範囲は「A2:A12」となります。
抽出結果の出力先セルを指定します。サンプルはD2セルとなります。
VBAを実装し実行する
VBAを実装し実行してみましょう。
実装方法は下記「VBAを実装する手順」をご覧ください。
実装できたらマクロから「重複文字を抽出し結果を出力する」を見つけ実行します。
はい、重複文字列が出力されましたね!
ちなみに、出力先は自由に設定することができます。
サンプルではD2セルからF8セルへ変更しています。
![](https://extan.jp/wp-content/uploads/2021/10/100821_0902_4.png)
変更する場合は以下のセルの値を変えてください。
VBAの説明
Excelで重複データの差分を抽出するVBAについて説明をします。
検索先リストを配列へ格納します。
検索結果出力用の開始位置番号です。こちらは変えないでください。
重複件数カウント用の開始番号です。こちらは変えないでください。
キーワードリスト作業用のオブジェクトを作成します。
検索先リストをコピーします。
検索先リストを作業用セルに貼り付けます。
検索先リストから重複分を削除します。
最終行の位置番号を取得後、開始位置番号とマイナスし配列の要素数を算出します。
重複削除した検索先リストをキーワードリストへ格納します。
作業用領域の値のみをクリアします。
検索結果出力用の配列を作成します。
キーワードリストに格納された値すべてを対象に処理します。
キーワードリストからキーワードを取り出します。
検索先リストと照合します。
検索先リストのワードとキーワードが一致した場合の条件です。
2回一致していたら検索結果出力用の配列にキーワードを格納します。
strSerchResults(k, 1) = strSerchKey
k = k + 1
Exit For
End If
検索結果を指定セルへ出力します。
VBAの実装手順
実装手順は以下の通りです。
今回はExcel側にこのVBAを実装します。
①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。
②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。
![](https://extan.jp/wp-content/uploads/2019/05/050919_1542_VBAFunctio2.png)
③右ペインのウインドウに上記のVBAを入力します。
こちらで完了です。
VBAを実行する
では早速VBAの実行をしてみましょう。
①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。
②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。
さいごに
いかがでしょうか。
今回は、
Excelで重複データの差分を抽出する方法とVBAで瞬間に処理する方法について
まとめました。
また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。
コメントを残す