VBAにおけるワークシート関数――INDEX、MATCH、VLOOKUPの使い方:VBA/マクロ便利Tips
業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、Excelの数式で使う関数INDEX、MATCH、VLOOKUPを、VBAのコード上で、WorksheetFunctionオブジェクトのメソッドとして使う方法について解説する。
ワークシート関数は、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のように「役職」と「氏名」の入力されたセルと、「取得された氏名」を表示するセルと「実行」ボタンを配置しておく。
各セルにはExcelのメニューから書式を設定している。各自が好きなデザインにしても構わない。
VBE(Visual Basic Editor)を起動して「挿入」から「標準モジュール」を選択し、プロジェクト内に追加されるModule1内にコードを記述する。
範囲を指定して、列と行が交差する位置にあるセルの値を取得するコードはリスト1になる。
Sub Index関数の使い方() Dim 名前 As Variant 名前 = WorksheetFunction.Index(Range("B5:C9"), 3, 2) Range("C11").Value = 名前 End Sub
まず、バリアント型の変数「名前」を宣言する(2行目)。
WorksheetFunction.Indexの書式にのっとって、「範囲」に「(Range("B5:C9"))」を、「行番号」に「3」を、「列番号」に「2」をそれぞれ指定して、取得されるセルの値を変数「名前」に格納する(3行目)。「C11」のセルに、「名前」変数の値を表示する(4行目)。
ここでは、役職名と氏名が入っている「B3:C9」を「範囲」に指定せずに「B5:C9」としていることに注目してほしい。「C7」のセルの値を取得したい場合、「範囲」を「B5:C9」とすると、「B5」が起点となるので、列番号は「3」ではなく「2」になるのだ。
リスト1を「実行」ボタンに関連付け、実行すると図2のように表示される。
指定セル範囲の中から、指定した値のあるセルの位置を返すMATCH関数
MATCH関数をVBAで使う書式は下記のようになる。
MATCH関数をVBAで使う(WorksheetFunction.Matchメソッドの)書式
WorksheetFunction.Match(検査値,範囲,方法)
「検査値」には、範囲内で検索する値を指定する。「範囲」には、検索する範囲を指定する。「方法」には、下記のいずれかを指定する。
- 「-1」:「検査値」以上の最小値
- 「0」:「検査値」に完全一致する値
- 「1」:「検査値」以下の最大値
図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行目)。
もし、「検査値」を入力するセルが未入力の場合は、警告メッセージを発して処理を抜ける(3〜5行目)。
WorksheetFunction.Match関数の書式にのっとって、「検査値」に「検査値」セルに入力された値を指定し、「範囲」に「氏名」の値がある「C5:C11」の範囲を指定する。最後の「0」は「検査値に完全一致する値」を検索する意味を表す(7行目)。
検索の結果として、メッセージボックスで、該当するデータが「氏名列」の何行目に存在するかを表示する(8行目)。起点は「範囲」に指定した「C5:C11」の「C5」が起点となる。
リスト2のコードを「実行」ボタンと関連付け、実行すると図4のように表示される。
指定セル範囲内の1列目で指定した値を検索し、その値がある行と指定列が交差する位置のセルにある値を返すVLOOKUP関数
VLOOKUP関数をVBAで使う書式は下記のようになる。
VLOOKUP関数をVBAで使う(WorksheetFunction.VLookupメソッドの)書式
WorksheetFunction.VLookup(検索値,範囲,列番号,検索の型)
「検索値」には、「範囲」内の1列目で検索する値を指定する。検索範囲の対象になる列は必ず指定範囲の1列目を指定する必要がある。図5の場合は、「社員NO」の列に該当する。
「範囲」には、2列以上のセル範囲を指定する。
「列番号」には、範囲内で、目的のデータが入っている列を、左端からの列数で指定する。
「検索の型」には、「False」を指定すると、検索値に完全一致する値だけが検索される。「True」を指定すると、検索値と一致する値がない場合に一番近い値が検索される。
詳細は「WorksheetFunction.VLookupメソッド (Excel)」を参照してほしい。
図5のような、「社員NO」を入力するセルと「役職」「氏名」を入力したセルと、「実行」ボタンを配置しておく。
セルの書式は各自が自由に決めてもらっていい。
指定された範囲の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
バリアント型の変数「結果」と、文字列型の変数「検索値」を宣言する(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のようになる。
まとめ
今回は、ワークシート関数の「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.
関連記事
- スクショをExcelに張り付けるのに役立つ4つのテクニック
システム開発におけるソフトウェアテスト(結合テスト〜システムテスト)において重要視されるエビデンス(作業記録)。前後編の2回にわたって、エビデンスとしてスクリーンショットをキャプチャし、テスト仕様書や納品書に張り付けていく作業を自動化するためのVBA/マクロのテクニックを紹介する。後編は、画像ファイルをシートに張り付け、Excel 2013のメニューからスクショを直に張り付け、画像を縮小し、指定した時間にマクロを実行する方法を解説。 - [Esc]キーによるExcel VBAの実行中断を防止する
ExcelではVBA(Visual Basic for Applications)によってさまざまな処理を自動的に実行できる。しかし、VBAの実行中にユーザーが[Esc]キーあるいは[Ctrl]+[Break]キーを押すと、自動処理が止まってしまう。止めたくない場合は、Application.EnableCancelKeyプロパティの設定を変更する。 - Windows TIPSディレクトリ > プラットフォーム別 > Office > Excel