Excelで重複データがあるかチェックして合算、削除する：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、重複データから重複を除く抽出リストを作成して、ひも付く数値を合計する方法と、Excel 2007より追加されたRemoveDuplicatesメソッドを使って重複データを削除する方法を解説します。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013

　今回は「重複データ」について解説する。例えば、売上表のようなデータの中には、重複したデータも存在する場合があるだろう。そういった場合、重複データを一つにまとめ、その合計金額を表示する「重複を除く抽出リストの作成」や、「重複データの削除」の2つのTipsについて解説する。

　最初のTipsでは「フォーム」が登場するが、「フォーム」に関しては、記事「Excelに入力フォームを作成、コントロールを追加、表示、ボタンでイベント実行」を参照しておいてもらいたい。

Collectionオブジェクトとフォームで重複を除く抽出リストの作成

　図1のように、「氏名」「お買い上げ金額」の表と「重複しないデータの抽出」ボタンがあり、「氏名」には同姓同名の人物が複数存在するとしよう。図1の各書式はExcelメニューの［ホーム］から設定している。

図1　「氏名」「お買い上げ金額」「重複しないデータの抽出」ボタンの配置されたデータがある

　重複を除く抽出リストの作成にはCollectionオブジェクトを使用する。Collectionオブジェクトを使うと、「文字列」「数値」「オブジェクト」を要素とする、独自のオブジェクトを作成できる。

　ただし、Collectionオブジェクトには重複した「値」の指定はできない。このことを利用して重複しないデータを取り出す。

　続いて、図2のようなフォームを作成する。

図2　各コントロールを配置した

　配置するコントロールは表1のようになる。

表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

リスト1　「重複しない氏名」を「一覧リストボックス」に表示するマクロ

　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　「一覧リストボックス」から任意の「氏名」を選択したときの処理

　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

リスト3　「フォーム」を表示するマクロ

　ShowメソッドでUserForm1を表示するだけのマクロで、図3は実行結果だ。

図3　フォームが表示され、「重複しない氏名」がリストボックスに表示される。任意の「氏名」を選択すると、その「合計金額」が表示される

重複データを削除するRemoveDuplicatesメソッド

　次に、重複データの削除についてだ。「重複データの削除」という新しいシートを追加して、その中にデータを表示している。

　膨大なデータの中には間違って入力され、重複されたデータも存在する可能性はある。そんな場合、目でいちいち確認しながら重複データを削除していたのでは、非効率的だ。

　例えば「重複データの削除」シートの中に図4のようなデータがあったとする。

図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

リスト4　重複データを削除するマクロ

　データの表示されている「B2」〜「C23」のセルで、RemoveDuplicatesメソッドを使って重複している「列」である「Columns」に「1」を指定する。通常「B」列であるから「2」と考えそうだが、Rangeオブジェクトで指定した「B2:C23」を起点とするので、この場合は「B列」は「1列目」ということになる。間違って「2」を指定するとエラーになるので注意してほしい。

　「Header」には、ヘッダー情報が含まれていることを示す「xlYes」を指定する。データが入力されている範囲を、ヘッダーを外して、「B3:C23」と指定する場合は、Headerに「xlNo」を指定する必要がある。

　リスト4のマクロを「重複データの削除」に関連付け、実行すると図5のように重複した「氏名」が削除されて表示される。

図5　重複したデータが削除された。図4と比較してみてほしい

手作業で削除していくことは、効率が悪く見逃しが起こる場合もある

　今回は「データの重複」に関する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）。