ExcelでVLOOK関数がいまいち使いにくく、いちいちINDEX関数とMATCH関数で値を引くのが面倒なので、マクロで実装した
領域やテーブルから値を引っ張ってくるのはVLOOKUP関数が一般的。最近ではXLOOKUP関数なるものがあるようだが、会社で支給されているPCのMS-OfficeのライセンスはOffice2019なのでXLOOKUP関数はない。なので、XLOOKUP関数を使ったこともない。
VLOOKUP関数は便利なのだが以下の難点がある。
- キー値が必ず領域/テーブルの一番左の列にある必要がある
- マッチした行の値を引いてくるのに数値で何列目と指定する必要がある
- 横の列にコピペでず、列指定値をいちいちハンドで書き直す必要がある
- 何より何列目かを数えるのが面倒
- VLOOKUPを書くときだけR1C1形式表示にオプション指定して、また戻すのも面倒
文句ばかり言っていても埒が明かないので、INDEX(配列,MATCH(...),MATCH(...)) で書いていた表現を関数化してみた。
使い方
以下のような表 (セルB1が X、セルB2が AX と読んでくだされ) を使う。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | X | Y | Z | |
| 2 | A | AX | AY | AZ |
| 3 | B | BX | BY | BZ |
| 4 | C | CX | CY | CZ |
INDEX関数とMATCH関数で Y 列 B 行を検索する式は、=INDEX($A$1:$D$4,MATCH("B",$A$1:$A$4,0),MATCH("Y",$A$1:$D$1,0)) のようになる。
これを今回作成したmyLookUP関数で表すと =myLookUp($A$1:$D$4,"Y","B") となる。だいぶ簡略化できた。
行と列の指定順がINDEX関数と逆になっているのは、値を返す列をOptionalで指定するときに近くにあったほうがわかりやすいためで、例えば1列目ではなくX列をキーとしたい場合には =myLookUp($A$1:$D$4,"Y","BX","X") と指定する。
例では列のキー X や行キー B や BX をリテラルで指定しているが、表の列見出し・行見出しを検索キーとしてセル参照式で指定して、コピペする場合に強みを発揮する。
ソースコード
以下のコードをマクロとしてインポートする。関数名は適宜調整する。
' INDEX関数とMATCH関数で配列にアクセスするのが面倒なので関数化
' オプションを指定するとキーを調べる列を変更できる
' WorksheetFunction.Matchを使いたくなかったのでFindメソッドで実装
Public Function myLookUp(InspectionArea As Range, ColumnKey As String, RowKey As String, Optional SearchHeader As String = "") As Variant
Dim Row As Long, Column As Long, SearchColumn As Long
Dim Area As Range, FoundCell As Range
Dim Table As ListObject
Set Table = InspectionArea.ListObject
' 範囲指定がテーブルの場合にはテーブル全体を参照する
If Not Table Is Nothing Then
Set Area = Table.Range
Else
Set Area = InspectionArea
End If
' 調べる列を決める
If SearchHeader = "" Then
SearchColumn = 1
Else
Set FoundCell = Area.Rows(1).Find(SearchHeader, LookIn:=xlValues, LookAt:=xlWhole)
If FoundCell Is Nothing Then
' 見つからない場合はエラーメッセージを表示して終了
' MsgBox "指定されたヘッダーが見つかりません。"
myLookUp = CVErr(xlErrNA)
Exit Function
End If
SearchColumn = FoundCell.Column - Area.Column + 1
End If
' 範囲のキー列を調べる
Set FoundCell = Area.Columns(SearchColumn).Find(RowKey, LookIn:=xlValues, LookAt:=xlWhole)
If FoundCell Is Nothing Then
' 見つからない場合はエラーメッセージを表示して終了
' MsgBox "指定された行タイトルが見つかりません。"
myLookUp = CVErr(xlErrNA)
Exit Function
End If
Row = FoundCell.Row - Area.Row + 1
' 範囲の1行目を調べる
Set FoundCell = Area.Rows(1).Find(ColumnKey, LookIn:=xlValues, LookAt:=xlWhole)
If FoundCell Is Nothing Then
' 見つからない場合はエラーメッセージを表示して終了
' MsgBox "指定された列タイトルが見つかりません。"
myLookUp = CVErr(xlErrNA)
Exit Function
End If
Column = FoundCell.Column - Area.Column + 1
myLookUp = Area.Cells(Row, Column)
End Function
Synopsis
myLookUp(InspectionArea, ColumnTitle, RowTitle [, SearchHeader])
| 引数 | 説明 |
|---|---|
| InspectionArea | 範囲 or テーブル |
| ColumnKey | 値を返す列のキー (表題) |
| RowKey | 検索値の行のキー (表題) |
| SearchHeader | RowKeyの検索対象が1列目ではない場合に検索する列の表題 |
戻り値
検索した値
行や列見出しに指定したキーがInspectionAreaに存在しない場合には#N/Aエラーとなる。
余談
はじめはINDEX関数とMATCH関数に倣ってWorksheetFunction.Matchメソッドを使用して実装した。それはそれでスッキリとしていてよかったのだが、エラーが発生するとマクロがいきなり中断してデバッグがままならない。Application.Matchというものものあるようなのだか、ネットで調べても謎仕様らしい。それにWorksheetFunctionは何だか気持ちが悪いので、RangeオブジェクトのFindメソッドで実装した。思ったより冗長になってしまった。
VBAのではオブジェクトの代入には明示的に Set が必要となる。久々にコードを触ったら忘れていて、うまく動かなくて悩んだ。