Excelで複数のデータを検索して結果を分かりやすくする：VBA／マクロ便利Tips

連載目次

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

　今回は「検索」のTipsについて解説する。「検索」は日常の業務においても非常に重要な役割を果たす。いくらデータを多く登録しておいても、目的となるデータを見つけるのに手間取れば、作業効率は極端に落ちる。

　今回のTipsでは、基本としてまず、「一つのデータを検索する」方法を解説し、応用として「複数存在するデータを検索する」方法について解説する。特に、膨大なデータから複数の検索結果を分かりやすく表示するために背景を赤くしている点に注目してほしい。これは、ただのExcel操作ではできないVBA／マクロならではのテクニックだ。

　今回のTipsは、必ず皆さまの職場の実務において、大いに役に立つTipsだと思う。分岐処理や反復処理を使うため、マクロはちょっと難しいかもしれないが、ぜひ職場で取り入れて使ってみてほしい。

一つのデータを検索するFindメソッド

　まずは、「一つのデータを検索」から解説していこう。今回は、電話番号を検索すると、該当する電話番号のセルの背景が赤色に変わるTipsだ。

　図1のように、電話番号を入力するセルと、「実行」ボタンがあり、蓄積されたデータがあったとしよう。

　背景の枠線を図1のように消したいときには、Excelメニューの［表示］から、「枠線」のチェックを外すことで容易に実現できる。

図1　電話番号を入力するセルと「実行」ボタンがある

　｛オブジェクト｝には、対象となるRangeオブジェクトを指定する。｛検索文字列｝には、検索対象となる文字列を指定する。

　今回は電話番号のチェックに正規表現を使っている。VBAで正規表現を使うには、ちょっとした設定が必要だ。それには、記事「Excel VBAで正規表現を使うには」を参照してほしい。

　正規表現の詳細については「正規表現言語 - クイック リファレンス（MSDN）」を参考にしてほしい。

　図1で、電話番号セルに電話番号を入力して、該当するデータの背景色を変えるマクロはリスト1になる。VBE（Visual Basic Editor）を起動して、Module1内にリスト1のマクロを記述する。

Option Explicit
Sub 電話番号検索()
  Dim myRegExp As New RegExp
  Dim regCheck As Boolean
  Dim 検索電話番号 As Range
 
  myRegExp.Pattern = "\d{2,4}-\d{2,4}-\d{4}"
  regCheck = myRegExp.Test(Range("B2").Value)
  If regCheck = False Then
    MsgBox "郵便番号が不正です"
    Exit Sub
  Else
    Set 検索電話番号 = Range("G:G").Find(What:=Range("B2").Value)
    Range("G:G").Interior.ColorIndex = 2
    If 検索電話番号 Is Nothing = True Then
      MsgBox "該当電話番号はありません。"
      Exit Sub
    Else
      検索電話番号.Interior.ColorIndex = 3
    End If
    Range("G6").Interior.ColorIndex = 27
  End If
End Sub

　まず3行目で正規表現を使うための新しいRegExpクラスのインスタンス「myRegExp」オブジェクトを宣言し、4行目で正規表現にのっとっているかどうかの結果を格納するブール型の変数「regCheck」を宣言し、5行目でRange型の「検索電話番号」変数も宣言しておく。

　7行目では、電話番号の正規表現チェックを行うために、Patternプロパティに該当する正規表現（ここでは「\d{2,4}-\d{2,4}-\d{4}」）を指定する。

　9行目では、入力した値が、指定した正規表現のパターンと一致しているかどうか判定し、一致しない場合は、警告メッセージを表示して処理を抜ける。それ以外の場合は、13〜21行目の処理を行う。

　13行目では、電話番号の「G」列に対して、Findメソッドを使って、電話番号を入力する「B2」の値を、「What」に指定し、変数「検索電話番号」にセットしている。

　14行目では、初期化の意味で「電話番号」の列を、一度全て「2（白）」の背景にしている。しかし、そうすると項目名が「電話番号」の背景も「白」になってしまうため、最後に、21行目のように記述して「電話番号」項目の背景を「黄色（27）」に戻している。

　15行目では、該当する電話番号があるかどうか判定し、なかった場合は警告メッセージを発して処理を抜ける。見つかった場合は、19行目のように電話番号の該当するセルの背景を「3（赤）」で塗りつぶしている。

　リスト1のマクロを「実行」ボタンに関連付け、実行した結果が図2だ。

図2　該当する電話番号のセルの背景が「赤」になる

複数存在するデータを検索するFindNextメソッド

　次は、同姓同名の人物が複数存在するデータから、その氏名を検索すると、該当する全ての氏名の背景が赤色に変わるTipsだ。これは利用価値が高いと思うので、ぜひマスターして活用してほしい。

　新しい「複数人物の検索」シートを作成しておき、その中に、図3のように氏名を入力するセルと「実行」ボタンが配置されていて、「薬師寺国安」という氏名の人物が複数存在するデータがあったとする。

図3　氏名を入力するセルと「実行」ボタンがあり、「薬師寺国安」という同姓同名の氏名が複数存在するデータがある

　｛オブジェクト｝には、対象となるRangeオブジェクトを指定する。FindNextの｛After｝には、単一のセルを指定し、この引数に指定したセルの次のセルから、検索を開始する。

　氏名セルに人物名を入力して、「実行」ボタンをクリックし、同姓同名の人物を検索するマクロはリスト2になる。

Sub 同姓同名の人物を検索する()
  Dim 人物名 As Range
  Dim 最初に見つかった人物 As Range
  Dim 結果 As Range
 
  If Range("B2").Value = "" Then
    MsgBox "氏名を入力してください。"
    Range("B:B").Interior.ColorIndex = 2
    Range("B6").Interior.ColorIndex = 27
    Exit Sub
  Else
    Set 人物名 = Cells.Find(What:=Range("B2").Value)
    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("B6").Interior.ColorIndex = 27
    Range("B2").Interior.ColorIndex = 2
  End If
End Sub

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

　6行目では、氏名を入力するセルに、何も人物名が入力されているかどうか判定し、なかった場合は、警告メッセージを発し、氏名セルである「B」列の背景を「白」にして処理を抜ける。それ以外の場合は12〜26行目の処理を行う。

　12行目では、セル「B2」に入力された人物名を見つけ、変数「人物名」にセットしている。13行目で変数「最初に見つかった人物」に、変数「人物名」の値をセットし、14行目で変数「結果」に、変数「人物名」の値をセットする。

　15行目では、「氏名」の「B」列の背景を一度「白」にしておくが、そうすると、項目名の「氏名」まで背景が「白」になるので、最後に25行目で「黄色（27）」の背景色になるようにしておく。

　16〜23行目のDo〜Loopで繰り返し処理を行う。

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

　18行目で、変数「人物名」と「最初に見つかった人物名」の値が同じかどうか判定し、同じ場合は繰り返し処理を抜ける。該当する人物が1名しかいなかった場合は、その1名だけを検索した後、処理を抜けることになる。

　変数「人物名」と「最初に見つかった人物名」の値が異なる場合は、Unionメソッドに、変数「結果」「人物名」の値を指定して、該当する複数のセル範囲を集合させ、1つのオブジェクトのように参照して、変数「結果」にセットしておく。

　24行目では、変数「結果」で検索された人物名の背景を、「赤（3）」で塗りつぶす。先に書いたように、15行目で「B」列は背景を一度「白（2）」で塗りつぶしているので、「B2」の氏名の項目名まで、「白（2）」で塗りつぶされる。そこで25行目で再度「黄色（27）」に塗りつぶして、元に戻している。

　また、24行目で選択された人物名の背景が「赤（3）」で塗りつぶされるため、「B2」の「氏名」入力セルに入力したデータも、「赤（3）」で塗りつぶされる。そのため、26行目で「B2」のセルの背景も再度「白（2）」で塗りつぶしている。

　「氏名」を入力するセルと、データである氏名が同じ「B」列にあるため、「B2」に該当する氏名が見つかった場合、「氏名」を入力するための、自分自身の「B2」の「氏名」まで、赤の背景色で塗りつぶされてしまうからだ。この辺りは注意してほしい。

　リスト2のマクロを「実行」ボタンに関連付け、実行した結果が図4だ。

図4　同姓同名の人物が、全て選択され「赤」で塗りつぶされた

必要なデータを、必要なときに取り出せるようにしよう

　今回は「検索」に関するTipsを2個紹介した。「検索」は、データを扱う上では必要不可欠な処理だ。いくら膨大なデータが蓄積されていても、必要なデータを、必要なときに取り出せなければ、膨大なデータも何の意味もなさない。そういった意味で、今回の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）。