ここでは珍しく、Excelの話。
超便利な VLOOKUP 関数ですが、検索値が左端になくてはならなかったりと、柔軟さに欠ける所もあります。
ワークシート関数だと、INDEX と MATCH を組み合わせた方法がありますが、VBA(xslm ファイル)を使える環境用に、それっぽいユーザ定義関数を作ってみました。
関数名は、CrossLookUpです。
CrossLookUp(検索値, 参照セル, 検索方向, オフセット数)
機能としては、VLOOKUP 関数と HLOOKUP 関数を合体させたような感じ。キーは、端の行(列)になくても構いません。
引数は以下の通り。全て必須(Optional 無し)。
- 検索値:調べたい範囲に存在する事を期待する値、もしくは、値が入ったセルの参照
- 参照セル:検索値が存在する事を期待する、列もしくは行
- 検索方向:検索したい方向。垂直か水平(VLOOKUP と HLOOKUP に相当)
- オフセット数:垂直探索の場合、左または右、水平探索の場合、上または下のセルに対してオフセットする
戻り値は、オフセット先のセルです。
ポイントは、検索値が、検索対象範囲のどこにあっても良いという所と、範囲(VLOOKUP の table_array)を指定しなくて構わない所ですね。
たとえば、次のような表があって(画像ですみません。左上端は A1 セルです)、
特のりタル弁当を探したいとします(メニューデータは、ほっともっと のページを参照しました⇒お弁当)。
VLOOKUP だと、カロリーまで出したければ、(B2:D13)を検索範囲に設定しなくてはなりません(構造化参照などを使わない場合)。
CrossLookUp では、参照は、B 列の値が入っているセルのどこでも選択して構いません(B1 でも B12 でも)。1 つ選択すれば、動的に範囲を取得します。
検索方向は、文字列で指定。英語の垂直水平の頭文字ですね。直感的にやりやすいようにしました。
オフセットは、検索範囲の列もしくは行を基準(ゼロ)にして、正負どちらでも指定出来ます。オフセットで取りに行きます。オフセットは、検索範囲に交差する方向です。
各種例外処理を入れてあります。
検索は、完全一致のみです。
たとえば、こんな感じですね↓
数式↓
細かい流れは、コード中にコメントで記しています。以下に掲載します。
時間をかけてテストしていないので、不具合があるかも知れません。
色々ごちゃごちゃやっているので、パフォーマンスもよろしく無いでしょうね。
ここはこうしたほうが良いのでは、とか、間違ってるんじゃ? みたいな所があれば、ご指導頂ければありがたいです。