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

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

» 2014年08月08日 18時00分 公開
[薬師寺国安PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本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)。


Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。