マクロ／ユーザ定義関数を作る！ INDEX＆MATCH
エクセルではさまざまな関数が用意されていますが、自分でも好きな関数を作ることができます。
それが マクロ画面で作る ユーザ定義関数 です。Function として関数を記述します。
今回はユーザ定義関数を一つ作ってみましょう。
ここで作る LOOKUPS関数（関数名は重複しなければ好きに付けられます）は超便利です。
VLOOKUPやHLOOKUPはもう不要ともいえるかもしれません。
LOOKUPS関数を作る
とHLOOKUP関数を組み合わせたような関数を作りたい。
作る関数は図のようなイメージです。
表の縦横から一致するものがクロスするセルの値を取り出します。（図参照）
これはINDEX関数とMATCH関数を組み合わせることで実現できます。
図の例では、「葉月」と「2018」のクロスする値「80」を取得しています。
数式は次のように書けばいいでしょう。
=INDEX(B2:L14,MATCH(E16,B2:B14,0),MATCH(D16,B2:L2,0))
このことについては、
で解説しています。
でも、関数の組み合わせが難しく、長いですね。
そこで、ユーザ定義関数を作りましょう。
マクロで Function を定義することで自分好みの新しい関数を作ることができるのです。
ここでは INDEX関数とMATCH関数を組み合わせた関数を LOOKUPS関数と名づけます。
元の関数（ここではINDEX関数とMATCH関数の組み合わせたもの）が出来ていれば、ユーザ定義関数の作成自体は簡単です。
LOOKUPS関数のイメージは
＝LOOKUPS(範囲, 行検索範囲, 列検索範囲, 行検索値, 列検索値 )
とします。
＝LOOKUPS(C3:L14, B3:B14, C2:L2, “葉月”, 2018 )
＝80
を得るようなイメージです。
注意：ここでは汎用性のため、データや検索条件の範囲を前述のINDEX／MATCH関数を用いたものとは変えています。
マクロ
「Alt+F11」を押すと、エクセルマクロの編集画面が出ます。
メニューから「挿入」をクリックし、「標準モジュール」をクリックします。
画面左下の「オブジェクト名」を「Module1」から「fc_LOOKUPS」にしましょう。
この名前はどのようなものでもいいのですが、「fc_」と最初につけると、マクロの編集画面で「Function」が格納されていることが一目瞭然です。
その他、Subには「sub_」「md_」を付けるなどすると後の管理が非常にしやすくなります。
（この接頭文字はお好みでお好きなものを付けても構いません）
よくデフォルトの Module1 のまま、いろいろなマクロをずらずらと書きつらねているのを見ますが、はっきり言って整備性が最悪です。
そして、右側に Function LOOKUPS として「Enter」を押すと、図のように、
Function LOOKUPS()
End Function
となり、準備が整います。
ここでもう一度今回作る LOOKUPS関数 のイメージを見てみましょう。
＝LOOKUPS(範囲, 行検索範囲, 列検索範囲, 行検索値, 列検索値 )
です。これを Function 関数として記述していきます。
さきほどの Function LOOKUPS() ～ End Function の間にマクロの式を記述していきますが、
まず、最初の行の Function LOOKUPS() を ＝LOOKUPS(範囲, 行検索範囲, 列検索範囲, 行検索値, 列検索値 ) と合うように書き換えます。
Function LOOKUPS(area As Range, row_area As Range, column_area As Range, row_data As Variant, column_data As Variant)
ここで、各変数は次の意味です。
- area＝範囲
- row_area＝行検索範囲
- column_area＝列検索範囲
- row_data＝行検索値
- column_data＝列検索値
これらの変数名は実は何でも構いません。日本語でもOKです。
またタイプ As～を省略すると Variant 型になります。
そしてつぎのように記述します。緑色のテキストは覚え書きですのでプログラムには影響しません。
プログラムの必要部分を下に記します。
Function LOOKUPS(area, row_area As Range, column_area As Range, row_data As Variant, column_data As Variant)
With Application
LOOKUPS = .Index(area, .Match(row_data, row_area, 0), .Match(column_data, column_area, 0))
End WithEnd Function
ここで、INDEXやMATCHといった関数はマクロでは動作しませんので、Application. か WorksheetFunction. を INDEXやMATCHといったワークシート関数の前につける必要があります。
注意：例では、With ～ End With メサッドを用いています。
以上で終了です。
簡単ですよね。
では実際に使ってみましょう。
Amazonプライム会員（学割会員）であればさらにお得！
チャージするたびにポイントでおトクに
コンビニ・ATM・ネットバンキングで￥5,000以上チャージすると、
プライム会員は最大2.5%ポイント、通常会員は最大2%ポイントがもらえます。
出来た関数を使ってみる
では、できた関数を使ってみましょう。
使い方は簡単です。
＝LOOKUPS(範囲, 行検索範囲, 列検索範囲, 行検索値, 列検索値 )
ですから、まず配列でデータ範囲を、次いで行と列の検索範囲を指定します。
そして、行と列の検索値を指定するだけです。
そうすると、次のような検索をしてくれます。
次は、行検索の範囲「睦月・如月・弥生・・・・」を真ん中にしてみました。
こういう変則的な場合でも問題なく動作します。
次の図では、VLOOKUPではできない、反対方向へ検索しています。
ばらばらのところに置かれていても問題ありません。
VLOOKUPやHLOOKUP関数的な使い方もできます。
次図では、「II」と「D」にあたるところを検索しています。
他にも、改造していろいろな使い方が出来そうです。
ぜひチャレンジしてみてください。
コメント