連載

2015年1月30日

Excelで重複データを抽出・削除して、重複していないデータをリストボックスに表示するには：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、重複するデータを抽出・削除して重複していないデータをリストアップする方法について。繰り返しや条件分岐を組み合わせてアルゴリズムを考えてみましょう。

[薬師寺国安，PROJECT KySS] PC用表示関連情報

LINE

Hatena

連載目次

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

重複データの抽出こそ、プログラミングの真骨頂

　今回もリストボックスコントロールの使い方について解説する。重複データを抽出・削除して重複してないデータをリストボックスに表示する方法を紹介しよう。

　大量のデータを扱うときに問題なのが、重複するデータだ。データの性質上必要なものでも、時には重複するデータを抽出・削除して重複してないデータをリストアップしたい場合もあるだろう。

　そんなときはExcelの検索機能だけではデータの抽出に時間がかかってしまう。しかし、重複データの抽出こそ、プログラミングを使えば短時間で解決できる、プログラミングの真骨頂ともいうべき処理だ。

　繰り返しや条件分岐を組み合わせるので、立派な「アルゴリズム」といえる。今回紹介するマクロを覚えることで、さまざまなアルゴリズムへ発展させ、さらに複雑なデータ処理が可能となるので、頭の体操がてら、ぜひ覚えてみてほしい。

リストボックスに重複しないデータを表示する準備

　新しい、「重複データ」という名前のシートを追加し、その中に図1のような氏名が重複しているデータを作成し、「重複しないデータを表示フォーム」ボタンを配置しておく。

図1　データと「フォームを表示」というボタンを追加

　次に、ユーザーフォームを作成しUserForm1のCaptionプロパティに、「重複しないデータを表示フォーム」と指定しておく。

　ユーザーフォーム上には、「リストボックス」を1個配置しておく。「リストボックス」の「オブジェクト名」には、「一覧リストボックス」としておく。

　コントロールを配置すると、図2のようになる。

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

ユーザーフォームがアクティブになったときの処理

　ユーザーフォームがアクティブになったときに、「一覧リストボックス」に重複しないデータを表示するコードはリスト1だ。UserForm_Activateイベント内に記述する。

Option Explicit
Private Sub UserForm_Activate()
  Dim i As Integer
  Dim j As Integer
  Dim 判定 As Boolean
  For i = 3 To 28
    If 一覧リストボックス.ListCount = 0 Then
      一覧リストボックス.AddItem Cells(i, 2)
    Else
      判定 = False
      For j = 0 To 一覧リストボックス.ListCount - 1
        If Cells(i, 2) = 一覧リストボックス.List(j) Then
          判定 = True
          Exit For
        End If
      Next
      If 判定 = False Then 一覧リストボックス.AddItem Cells(i, 2)
    End If
  Next
End Sub

リスト1　「一覧リストボックス」内に重複しないデータを表示するコード

　まず、3～5行目でInteger型の変数「i」と「j」を宣言する。ブール型変数「判定」を宣言する。

　6～19行目で変数「i」を「3」から「28」まで反復処理をする。この値は「行番号」に該当する。

　7行目で「一覧リストボックス」に、何もデータが追加されていない場合は、AddItemメソッドで、「i」行目の「2列目（B列）」のデータを追加する（8行目）。そうでない場合は、10行目以下の処理を行う。

　10行目では、ブール型変数をFalseで初期化している。

　11～16行目では、変数「j」を「一覧リストボックス」に登録されている項目の個数分、反復処理を行う。ListCount-1としているのは、「一覧リストボックス」のインデックスは「0」から始まるためだ。

　もし「i」行目の「2列目（B列）」の値が、「一覧リストボックス」のListプロパティが格納している配列のインデックス「j」に該当する値と同じなら（データが重複していたら）（12行目）、ブール型変数をTrueで初期化し（13行目）、反復処理を抜ける（14行目）。

　17行目では、ブール型変数「判定」が、False（データが重複していない）なら、「一覧リストボックス」に重複しない値を追加する。

リストボックスで選択したときの処理

　次に、リストボックスから「氏名」が選択されたときのコードはリスト2になる。一覧リストボックス_Changeイベント内に記述する。

Private Sub 一覧リストボックス_Change()
  Dim 人物名 As Range
  Dim 最初に見つかった人物 As Range
  Dim 結果 As Range
  Set 人物名 = Cells.Find(What:=一覧リストボックス.Text)
  Set 最初に見つかった人物 = 人物名
  Set 結果 = 人物名
  Range("B:B").Interior.ColorIndex = 2
  Do
    Set 人物名 = Cells.FindNext(人物名)
    If 人物名.Address = 最初に見つかった人物.Address Then
      Exit Do
    Else
      Set 結果 = Union(結果, 人物名)
    End If
  Loop
  結果.Interior.ColorIndex = 3
  Range("B2").Interior.ColorIndex = 6
End Sub

リスト2　リストボックスから「氏名」が選択され、Excel上の該当する氏名の背景色が「3（赤）」で塗りつぶされるコード

　2～4行目で、Range型の「人物名」「最初に見つかった人物」「結果」という変数を宣言しておく。

　5行目で「一覧リストボックス」より選択した「氏名」を変数「人物名」にセットする。6行目で変数「最初に見つかった人物」に変数「人物名」の値をセットする。そして7行目で、変数「結果」に、変数「人物名」の値をセットする。

　8行目で「氏名」の「B」列の背景を一度「2（白）」にしておく。

　9～16行目ではDo～Loopで繰り返し処理を行う。

　10行目では、FindNextメソッドに変数「人物名」の値を指定すると、この引数に指定したセルの、次のセルから検索を開始する。その値を変数「人物名」に順次セットしていく。

　変数「人物名」と「最初に見つかった人物名」の値が同じなら（11行目）、繰り返し処理を抜ける（12行目）。要するに、該当する人物が1名しかいなかった場合だ。

　それ以外の場合は（13行目）、Unionメソッドに、変数「結果」と「人物名」の値を指定して、該当する複数のセル範囲を集合させ、1つのオブジェクトのように参照して、変数「結果」にセットしておく（14行目）。

　17行目では、変数「結果」で検索された人物名の背景を「3（赤）」で塗りつぶす。最初「B列」を「2（白）」で塗りつぶしているため、「氏名」と表示された「B2」のセルも「2（白）」で塗りつぶされる。そのため、「6（黄）」で再度塗りつぶしておく（18行目）。

フォームを表示する処理

　次にModule1内にリスト3のコードを記述する。

Sub 重複しないデータ表示フォーム()
  UserForm1.Show vbModeless
End Sub

リスト3　「重複しないデータ表示フォーム」を表示するコード

　ShowメソッドでUserForm1をモードレスで表示する。

実行結果

　リスト3のコードを「重複しないデータを表示フォーム」ボタンと関連付け、実行した結果が図3だ。

図3　リスト3の実行結果

　「一覧リストボックス」に重複しない「氏名」が表示され、「氏名」を選択するとExcelシート上の該当するデータ全部の背景色が、「3（赤）」になる。

連載目次

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

著者プロフィール

薬師寺国安（やくしじくにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。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）。

Microsoft MVP for Development Platforms-Windows Platform Development (Oct 2014-Sep 2015）。