Excelで重複データの差分を抽出したいときはないでしょうか。
けど、そんな中で悩むことは、
・VBAでExcelの重複データ差分抽出と出力を一括で処理したいがやり方がわからない。
ですよね。
今回はそんなお悩みを解決する
Excelで重複データの差分を抽出する方法とVBAで瞬間に処理する方法について
まとめます！
もくじ
Excelで重複データがある状態とは？
例えばですが、会社で何かしらの利用申請をする機会はあるかと思います。
具体的にはパソコンとか、携帯端末の導入だったり。
そんなときに2重に申請をしてしまい、同じ内容のデータが台帳上にあるという状態が
データの重複しているという状態になります。
それを回避するため、事前に重複データがないかチェックをするということが想定されるかと思います。
タカヒロも上司や顧客からの要望で、重複データのチェックはよくやります。
Excelで重複データの差分を抽出するイメージ
VBAでExcelで重複データの差分を抽出するイメージについて説明をします。
Excelで重複データがある可能性のあるリストデータを用意します。
リストの中で2件以上あるデータを識別していきます。
重複データを抽出する方法としては、
まず初めに関数を利用し抽出していきます。
次の方法はピボットテーブルを使って抽出していきます。
最後にこれまで複数ステップの作業が必要であった処理を
VBAを使い、ワンクリックで一気に処理していきます。
それでは早速使ってみましょう！
差分抽出したいデータを用意する
まずは、差分を抽出したいリストデータを用意しましょう。
データの内容はなんでもよいですが、重複する文字列を2個以上いれるようにしてください。
サンプルは以下のようにしました。
関数で重複データの差分を抽出する
関数で重複データの差分を抽出してみましょう。
使う関数はおなじみのCOUNTIF関数です。
COUNTIF関数を隣接セルへ入力する
COUNTIF関数を重複チェックをしたいワードがある隣接セルへ入力しましょう。サンプルではB2セルへ以下の数式を入力しています。
=COUNTIF(A:A,A2)続いて、リストの最終行までB2セルに入力した数式をドラッグし、コピーします。
重複件数があるワードに絞り込む
COUNTIF関数の結果が表示されましたので、重複を示す2件以上を対象とするようフィルターで絞り込みます。
重複ワードを消去する
絞り込んだ結果を別のセルへ値のみコピーし、
「データ」タブの「データツール」欄の重複の削除をクリックし実行します。
はい、こちらでユニークなデータになりましたね。
ピボットテーブルで重複データの差分を抽出する
ピボットテーブルで重複データの差分を抽出してみましょう。
ピボットテーブルを作成する
重複データを抽出したいセルを選択します。
挿入タブからピボットテーブルボタンをクリックします。
ピボットテーブルの作成ウイザードがでたらそのままOKをクリックします。
ピボットテーブルのフィールドを設定する
ピポッドテーブルの「行」に項目名をドラッグします。同じく「値」にもドラッグし、「個数」を選択します。
COUNTIF関数とおなじ結果が得られましたね。
絞り込みの方法はCOUNTIF関数と同様になります。
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 SubVBAの設定をする
Excelで重複データの差分を抽出するVBAに先ほど作成した表データの場所を設定しましょう。
設定箇所は以下の通りです。
検索値の範囲を指定します。サンプルの範囲は「A2:A12」となります。
抽出結果の出力先セルを指定します。サンプルはD2セルとなります。
VBAを実装し実行する
VBAを実装し実行してみましょう。
実装方法は下記「VBAを実装する手順」をご覧ください。
実装できたらマクロから「重複文字を抽出し結果を出力する」を見つけ実行します。
はい、重複文字列が出力されましたね！
ちなみに、出力先は自由に設定することができます。
サンプルではD2セルからF8セルへ変更しています。
変更する場合は以下のセルの値を変えてください。
VBAの説明
Excelで重複データの差分を抽出するVBAについて説明をします。
検索先リストを配列へ格納します。
検索結果出力用の開始位置番号です。こちらは変えないでください。
重複件数カウント用の開始番号です。こちらは変えないでください。
キーワードリスト作業用のオブジェクトを作成します。
検索先リストをコピーします。
検索先リストを作業用セルに貼り付けます。
検索先リストから重複分を削除します。
最終行の位置番号を取得後、開始位置番号とマイナスし配列の要素数を算出します。
重複削除した検索先リストをキーワードリストへ格納します。
作業用領域の値のみをクリアします。
検索結果出力用の配列を作成します。
キーワードリストに格納された値すべてを対象に処理します。
キーワードリストからキーワードを取り出します。
検索先リストと照合します。
検索先リストのワードとキーワードが一致した場合の条件です。
2回一致していたら検索結果出力用の配列にキーワードを格納します。
strSerchResults(k, 1) = strSerchKey
k = k + 1
Exit For
End If
検索結果を指定セルへ出力します。
VBAの実装手順
実装手順は以下の通りです。
今回はExcel側にこのVBAを実装します。
①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。
②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。
③右ペインのウインドウに上記のVBAを入力します。
こちらで完了です。
VBAを実行する
では早速VBAの実行をしてみましょう。
①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。
②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。
さいごに
いかがでしょうか。
今回は、
Excelで重複データの差分を抽出する方法とVBAで瞬間に処理する方法について
まとめました。
また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。
この記事の関連キーワード
「抽出」の関連記事
こちらの記事の内容「抽出」に関連する記事一覧です。
コメントを残す