Excelで重複データがあるかチェックして合算、削除する：VBA／マクロ便利Tips
業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、重複データから重複を除く抽出リストを作成して、ひも付く数値を合計する方法と、Excel 2007より追加されたRemoveDuplicatesメソッドを使って重複データを削除する方法を解説します。
今回は「重複データ」について解説する。例えば、売上表のようなデータの中には、重複したデータも存在する場合があるだろう。そういった場合、重複データを一つにまとめ、その合計金額を表示する「重複を除く抽出リストの作成」や、「重複データの削除」の2つのTipsについて解説する。
最初のTipsでは「フォーム」が登場するが、「フォーム」に関しては、記事「Excelに入力フォームを作成、コントロールを追加、表示、ボタンでイベント実行」を参照しておいてもらいたい。
Collectionオブジェクトとフォームで重複を除く抽出リストの作成
図1のように、「氏名」「お買い上げ金額」の表と「重複しないデータの抽出」ボタンがあり、「氏名」には同姓同名の人物が複数存在するとしよう。図1の各書式はExcelメニューの［ホーム］から設定している。
重複を除く抽出リストの作成にはCollectionオブジェクトを使用する。Collectionオブジェクトを使うと、「文字列」「数値」「オブジェクト」を要素とする、独自のオブジェクトを作成できる。
ただし、Collectionオブジェクトには重複した「値」の指定はできない。このことを利用して重複しないデータを取り出す。
続いて、図2のようなフォームを作成する。
配置するコントロールは表1のようになる。
| コントロール名 | オブジェクト名 |
|---|---|
| リストボックス | 一覧リストボックス |
| ラベル | デフォルト値 |
| ラベル | 合計金額 |
「UserForm1」のプロパティからCaptionプロパティに、「重複しないデータフォーム」と指定している。「合計金額」のラベルのプロパティから、BorderStyleプロパティに「fmBorderStyleSingle」を選択して、枠線を表示している。
「一覧リストボックス」内に、「重複しない氏名」が表示されるマクロはリスト1になる。任意の「氏名」を選択すると、その人物の「合計金額」が「合計金額」ラベル内に表示されるマクロはリスト2になる。
UserForm1を選択して、マウスの右クリックで表示される「マクロの表示」を選択してマクロを記述する。
Option Explicit
Private Sub UserForm_Activate()
Dim 重複しない氏名 As New Collection
Dim i As Long
On Error Resume Next
For i = 3 To 23
重複しない氏名.Add Cells(i, 2), Cells(i, 2)
Next
For i = 1 To 重複しない氏名.Count
一覧リストボックス.AddItem 重複しない氏名(i)
Next
End Sub
3行目で新しいCollection型のインスタンス「重複しない氏名」オブジェクトを作成し、4行目でLong型の変数「i」を宣言する。
5行目では、「On Error Resume Next」文で、ランタイムエラーが発生したとき、エラーが発生したステートメントの直後のステートメントに制御が移り、そのステートメントから処理が継続されるようにしている。
6〜8行目で変数「i」を「3」から「23」まで繰り返す。この数値は、「氏名」データの入力されている行の「行番号」に該当する。反復処理の過程で、「重複しない氏名」オブジェクトに、Addメソッドで「B3」から「B23」までの範囲の、「重複しない氏名」を取り出して追加していく処理を行う。
7行目では、新しいCollectionのインスタンスに、Addメソッドで「i」行目の「B列（2）」の値を追加している。同じ「Cells(i,2)」を重複して指定することで、先にも書いたように、Collectionオブジェクトには重複した（値）の指定はできない。このことを利用して重複しないデータを取り出している。
10〜12行目では「重複しない氏名」の個数分反復処理を行い、「一覧リストボックス」に、「重複しない氏名」を追加していく。これでリストボックスに「重複しない氏名」の一覧が表示される。
リスト2は「一覧リストボックス」から選択された、人物の「合計金額」が表示される処理のマクロだ。
Private Sub 一覧リストボックス_Change()
Dim 合計 As Long
合計 = 0
Dim i As Long
Dim 選択された氏名 As String
選択された氏名 = 一覧リストボックス.Text
For i = 3 To 23
If Cells(i, 2) = 選択された氏名 Then
合計 = 合計 + Cells(i, 3)
End If
Next
合計金額.Caption = Format(合計, "#,###")
End Sub
2〜6行目は初期化処理だ。Long型の変数「合計」を宣言し、「0」で初期化しておく。Long型の変数「i」を宣言する。文字列型の変数「選択された氏名」を宣言する。「一覧リストボックス」より選択された値を、Textプロパティで取得し、変数「選択された氏名」に格納する。
8〜12行目で変数「i」を「3」から「23」まで繰り返す。先にも書いたように、この数値は、「氏名」データの入力されている行の「行番号」に該当する。反復処理の過程で9〜11行目の処理を行う。
9行目では、変数「i」行目の「2列目（B列）」に該当する値が、変数「選択された氏名」と同じかどうか判定している。同じであった場合は、変数「合計」に、「i」行目の「お買い上げ金額」列の列番号である、「C列（3）」の値を加算していく。
最後に13行目で「合計金額」ラベルのCaptionプロパティに、Format関数を使って、3桁区切りで「合計」変数の値を表示する。
次に、図1の「重複しないデータの抽出」ボタンに関連付けるマクロはリスト3になる。
VBE（Visual Basic Editor）のメニューから［挿入］→［標準モジュール］と選択してModule1を追加し、この中にリスト3のマクロを書いていく。
Option Explicit Sub フォームの表示() UserForm1.Show End Sub
ShowメソッドでUserForm1を表示するだけのマクロで、図3は実行結果だ。
重複データを削除するRemoveDuplicatesメソッド
次に、重複データの削除についてだ。「重複データの削除」という新しいシートを追加して、その中にデータを表示している。
膨大なデータの中には間違って入力され、重複されたデータも存在する可能性はある。そんな場合、目でいちいち確認しながら重複データを削除していたのでは、非効率的だ。
例えば「重複データの削除」シートの中に図4のようなデータがあったとする。
重複データを削除するRemoveDuplicatesメソッドの書式
｛オブジェクト｝.RemoveDuplicates(｛Columns｝,｛Header｝)
このメソッドはExcel 2007より追加されたものだ。｛オブジェクト｝には、対象となるRangeオブジェクトを指定する。｛Columns｝には、重複した情報を含む列のインデックスの配列を指定する。
｛Header｝には、最初の行にヘッダー情報が含まれているかどうかを指定する。「xlNo」が既定値だ。Excelにヘッダーを判断させるには、「xlGuess」を指定する。ヘッダー情報が含まれている場合は「xlYes」を指定する。
重複する「氏名」セルと、「お買い上げ金額」セルを削除するマクロはリスト4になる。
Sub 重複データの削除()
Range("B2:C23").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub
データの表示されている「B2」〜「C23」のセルで、RemoveDuplicatesメソッドを使って重複している「列」である「Columns」に「1」を指定する。通常「B」列であるから「2」と考えそうだが、Rangeオブジェクトで指定した「B2:C23」を起点とするので、この場合は「B列」は「1列目」ということになる。間違って「2」を指定するとエラーになるので注意してほしい。
「Header」には、ヘッダー情報が含まれていることを示す「xlYes」を指定する。データが入力されている範囲を、ヘッダーを外して、「B3:C23」と指定する場合は、Headerに「xlNo」を指定する必要がある。
リスト4のマクロを「重複データの削除」に関連付け、実行すると図5のように重複した「氏名」が削除されて表示される。
手作業で削除していくことは、効率が悪く見逃しが起こる場合もある
今回は「データの重複」に関するTipsを2個紹介した。「重複した氏名」の人物が複数あることは、実際のデータではよくあることだと思う。そういった場合に、今回のTipsを利用すれば、重複したデータを一つにまとめて、それぞれの合計金額を求めることができる。今回は利用した「フォーム」に関しては、別の回でTipsを紹介する際に詳説する予定だ。
また、データを入力するのが人間である以上、間違って同じデータを入力することは、絶対にないとはいえない。そんな重複データを目視と手作業だけでチェック、削除していくことは、効率が悪く見逃しが起こる場合もある。今回紹介したTipsを利用すれば、重複データは一瞬で削除できる。
今回紹介したTipsを応用して、ぜひ職場でも利用していただければうれしい限りだ。
著者プロフィール
薬師寺 国安（やくしじ くにやす） ／ 薬師寺国安事務所
薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。
1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。
1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。
2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。
Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。
Microsoft MVP for Development Platforms - Client App Dev（Oct 2003-Sep 2012）。
Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。
Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。
Copyright © ITmedia, Inc. All Rights Reserved.