業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、重複するデータを抽出・削除して重複していないデータをリストアップする方法について。繰り返しや条件分岐を組み合わせてアルゴリズムを考えてみましょう。
今回もリストボックスコントロールの使い方について解説する。重複データを抽出・削除して重複してないデータをリストボックスに表示する方法を紹介しよう。
大量のデータを扱うときに問題なのが、重複するデータだ。データの性質上必要なものでも、時には重複するデータを抽出・削除して重複してないデータをリストアップしたい場合もあるだろう。
そんなときはExcelの検索機能だけではデータの抽出に時間がかかってしまう。しかし、重複データの抽出こそ、プログラミングを使えば短時間で解決できる、プログラミングの真骨頂ともいうべき処理だ。
繰り返しや条件分岐を組み合わせるので、立派な「アルゴリズム」といえる。今回紹介するマクロを覚えることで、さまざまなアルゴリズムへ発展させ、さらに複雑なデータ処理が可能となるので、頭の体操がてら、ぜひ覚えてみてほしい。
新しい、「重複データ」という名前のシートを追加し、その中に図1のような氏名が重複しているデータを作成し、「重複しないデータを表示フォーム」ボタンを配置しておく。
次に、ユーザーフォームを作成しUserForm1のCaptionプロパティに、「重複しないデータを表示フォーム」と指定しておく。
ユーザーフォーム上には、「リストボックス」を1個配置しておく。「リストボックス」の「オブジェクト名」には、「一覧リストボックス」としておく。
コントロールを配置すると、図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
まず、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〜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
ShowメソッドでUserForm1をモードレスで表示する。
リスト3のコードを「重複しないデータを表示フォーム」ボタンと関連付け、実行した結果が図3だ。
「一覧リストボックス」に重複しない「氏名」が表示され、「氏名」を選択するとExcelシート上の該当するデータ全部の背景色が、「3(赤)」になる。
薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所
薬師寺国安事務所代表。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)。
Copyright © ITmedia, Inc. All Rights Reserved.