VBAにおけるワークシート関数――INDEX、MATCH、VLOOKUPの使い方：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、Excelの数式で使う関数INDEX、MATCH、VLOOKUPを、VBAのコード上で、WorksheetFunctionオブジェクトのメソッドとして使う方法について解説する。

[薬師寺国安，PROJECT KySS]

連載目次

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

ワークシート関数は、VBA関数とは別物

　今回はVBAにおけるワークシート関数の使い方について解説する。

　ワークシート関数はExcelの「数式」で使える関数であり、VBAの「関数」とは別物だ。名前が同じで、同じ機能を持つものもあれば、名前が同じでも機能が多少異なるものもあるので、注意してほしい。

　VBAでワークシート関数を呼び出して利用するには、ApplicationオブジェクトのWorksheetFunctionオブジェクトを取得して「メソッド」として利用する。Applicationオブジェクトは省略が可能だ。

　WorksheetFunctionのメソッド一覧については、「WorksheetFunction メンバー (Excel)」を参照してほしい。

　ここからは代表的なワークシート関数である「INDEX」「MATCH」「VLOOKUP」を例に、VBAにおけるワークシート関数の使い方を解説しよう。

指定セル範囲の中から、指定行／列が交差する位置のセルにある値を返すINDEX関数

　INDEX関数をVBAで使う書式は下記のようになる。

INDEX関数をVBAで使う（WorksheetFunction.Indexメソッドの）書式

WorksheetFunction.Index(範囲,行番号[,列番号])

　「範囲」には、セルの範囲を指定する。「範囲」が1列のみである場合は、「Index(範囲,行番号)」「範囲」が、1行のみである場合は、「Index(範囲,列番号)」と指定できる。

　「行番号」には、セルの行位置を数値で指定する。「列番号」には、セルの列位置を数値で指定する。

　図1のように「役職」と「氏名」の入力されたセルと、「取得された氏名」を表示するセルと「実行」ボタンを配置しておく。

図1

　各セルにはExcelのメニューから書式を設定している。各自が好きなデザインにしても構わない。

　VBE（Visual Basic Editor）を起動して「挿入」から「標準モジュール」を選択し、プロジェクト内に追加されるModule1内にコードを記述する。

　範囲を指定して、列と行が交差する位置にあるセルの値を取得するコードはリスト1になる。

Sub Index関数の使い方()
  Dim 名前 As Variant
  名前 = WorksheetFunction.Index(Range("B5:C9"), 3, 2)
  Range("C11").Value = 名前
End Sub

リスト1　範囲を指定して、列と行が交差する位置にあるセルの値を取得するコード

　まず、バリアント型の変数「名前」を宣言する（2行目）。

　WorksheetFunction.Indexの書式にのっとって、「範囲」に「(Range("B5:C9"))」を、「行番号」に「3」を、「列番号」に「2」をそれぞれ指定して、取得されるセルの値を変数「名前」に格納する（3行目）。「C11」のセルに、「名前」変数の値を表示する（4行目）。

　ここでは、役職名と氏名が入っている「B3:C9」を「範囲」に指定せずに「B5:C9」としていることに注目してほしい。「C7」のセルの値を取得したい場合、「範囲」を「B5:C9」とすると、「B5」が起点となるので、列番号は「3」ではなく「2」になるのだ。

　リスト1を「実行」ボタンに関連付け、実行すると図2のように表示される。

図2　「C7」のセルから取得された「氏名」

指定セル範囲の中から、指定した値のあるセルの位置を返すMATCH関数

　MATCH関数をVBAで使う書式は下記のようになる。

MATCH関数をVBAで使う（WorksheetFunction.Matchメソッドの）書式

WorksheetFunction.Match(検査値,範囲,方法)

　「検査値」には、範囲内で検索する値を指定する。「範囲」には、検索する範囲を指定する。「方法」には、下記のいずれかを指定する。

• 「-1」：「検査値」以上の最小値
• 「0」：「検査値」に完全一致する値
• 「1」：「検査値」以下の最大値

　図3のように「検査値」を入力するセルと、「役職」「氏名」が入力されたセルと、「実行」ボタンを配置しておく。

図3

　MATCH関数を用いて、「検査値」に入力した「氏名」に該当するセル位置を検索するコードはリスト2になる。

Sub Match関数の使い方()
  Dim 結果 As Variant
  If Range("C2").Value = "" Then
    MsgBox "氏名を入力。"
    Exit Sub
  End If
  結果 = WorksheetFunction.Match(Range("C2"), Range("C5:C11"), 0)
  MsgBox Range("C2").Value　＆ “は氏名列の” & 結果 & "行目にあります。"
End Sub

リスト2　「検査値」に入力した「氏名」に該当するセル位置を検索するコード

　バリアント型の変数「結果」を宣言する（2行目）。

　もし、「検査値」を入力するセルが未入力の場合は、警告メッセージを発して処理を抜ける（3〜5行目）。

　WorksheetFunction.Match関数の書式にのっとって、「検査値」に「検査値」セルに入力された値を指定し、「範囲」に「氏名」の値がある「C5:C11」の範囲を指定する。最後の「0」は「検査値に完全一致する値」を検索する意味を表す（7行目）。

　検索の結果として、メッセージボックスで、該当するデータが「氏名列」の何行目に存在するかを表示する（8行目）。起点は「範囲」に指定した「C5:C11」の「C5」が起点となる。

　リスト2のコードを「実行」ボタンと関連付け、実行すると図4のように表示される。

図4　「検査値」に入力した値が「氏名」列の3行目に位置する旨のメッセージが表示された

指定セル範囲内の1列目で指定した値を検索し、その値がある行と指定列が交差する位置のセルにある値を返すVLOOKUP関数

　VLOOKUP関数をVBAで使う書式は下記のようになる。

VLOOKUP関数をVBAで使う（WorksheetFunction.VLookupメソッドの）書式

WorksheetFunction.VLookup(検索値,範囲,列番号,検索の型)

　「検索値」には、「範囲」内の1列目で検索する値を指定する。検索範囲の対象になる列は必ず指定範囲の1列目を指定する必要がある。図5の場合は、「社員NO」の列に該当する。

　「範囲」には、2列以上のセル範囲を指定する。

　「列番号」には、範囲内で、目的のデータが入っている列を、左端からの列数で指定する。

　「検索の型」には、「False」を指定すると、検索値に完全一致する値だけが検索される。「True」を指定すると、検索値と一致する値がない場合に一番近い値が検索される。

　詳細は「WorksheetFunction.VLookupメソッド (Excel)」を参照してほしい。

　図5のような、「社員NO」を入力するセルと「役職」「氏名」を入力したセルと、「実行」ボタンを配置しておく。

図5

　セルの書式は各自が自由に決めてもらっていい。

　指定された範囲の1列目で、指定された「社員NO」の値を検索し、その範囲内の別の列の同じ行にある値を返すコードはリスト3になる。

Sub Vlookup関数の使い方()
  Dim 結果 As Variant
  Dim 検索値 As String
  検索値 = Range("C2").Value
  
  If 検索値 = "" Then
    MsgBox "社員NOを入力。"
    Exit Sub
  End If
 
On Error GoTo myError
  結果 = WorksheetFunction.VLookup(検索値, Range("A5:C11"), 3, False)
  MsgBox 結果
  Exit Sub
myError:
  MsgBox "検索結果が見つかりません。"
  Exit Sub
End Sub

リスト3　指定された範囲の1列目で、指定された「社員NO」の値を検索し、その範囲内の別の列の同じ行にある値を返すコード

　バリアント型の変数「結果」と、文字列型の変数「検索値」を宣言する（2〜3行目）。変数「検索値」には、セル「C2」の値を格納する（4行目）。セル「C2」が未入力の場合は警告メッセージを発して処理を抜ける（6〜9行目）。

　検索結果が見つからなかった場合はエラーが発生するので、エラー処理を行っておく。11行目の「On Error GoTo myError」は、エラーが発生した場合は、15行目のmyErrorラベル以下を実行する。myErrorラベル以下では、検索結果が見つからなかった旨を表示し、処理を抜ける（16〜17行目）。

　WorksheetFunction.VLookup関数の書式にのっとって、「検索値」には変数「検索値」の値を指定し、「範囲」には、セル「A5:C11」の範囲を指定する。「列番号」には「範囲」を起点とした「3列目」の「氏名列」を指定し、「検索の型」には、完全一致を表す「False」を指定する。これで取得できた値を変数「結果」に格納する（12行目）。

　最後に、変数「結果」の値をメッセージで表示する（13行目）。

　このリスト3を「実行」ボタンと関連付け、実行すると図6のようになる。

図6　「社員NO」に指定した「氏名」が表示された

まとめ

　今回は、ワークシート関数の「INDEX」「MATCH」「VLOOKUP」の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）。