検索
連載

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

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

Share
Tweet
LINE
Hatena
「VBA/マクロ便利Tips」のインデックス

連載目次

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


Copyright © ITmedia, Inc. All Rights Reserved.

ページトップに戻る