ルックアップ（LOOKUP）を減らそう――MATCH関数とINDEX関数による機能分離

TAKESAN

ルックアップ系関数の多用

実務的なシナリオ

実務上ではよく、

  • マスターの表があって

  • そのマスターからキーに応じた値を取り出し

  • 別の表にまとめる

というシナリオがあります。製品管理表から売上表を作ったりするような場合ですね。業務にもよりますが、資産管理のアプリケーションから出力されるインベントリーやログのデータであれば、列が数十に及ぶ時もあります。

架空データとして、次のようなテーブルを用意しました。AIに作ってもらったのを適当に整形したものです。数式のための例示なので、データ自体が実際にありそうか、の整合性はオミットします（メインメモリー128GBを積む個人用PCはそうそう無い）。

Product_Infoテーブル

製品情報として、Product_Infoテーブルとしました。要するにPCのデータベースです。業務システムなり何なりから出力していて、それをExcelで処理して集計表や管理表を作る、的なシナリオとします。説明のため、表は同じシートにまとめます（通常はマスターは別シートにするでしょう）。

よくある方法：ルックアップを使う

業務では、このようなデータベースに基づいて、何らかの観点から特定の行に着目して、そこから興味のある列の値（フィールド）を引っ張ってきて、着目したデータのみからなる表を作るわけです。そこでよく使われるのが、ルックアップ系の関数、つまりVLOOKUP関数やXLOOKUP関数です。このような観点からは、HLOOKUP関数の出番はありません。データは行の集まりであって、行を識別するには縦方向（Vertical）に検索して横を見る（Lookup）からです。
別テーブルを作ります。

Summaryテーブル

シリアルナンバーをキーにして値を取得してまとめる、Summaryテーブルとしました。ここにシリアルナンバーを入れたら、プロセッサ等の情報が自動的に表示されるようにします。プロセッサの所にVLOOKUP関数を入れます。

VLOOKUP関数
=VLOOKUP([@シリアルナンバー],
         Product_Info[[シリアルナンバー]:[製造年]],
         MATCH(Summary[[#見出し],[プロセッサ]],Product_Info[#見出し],0)-1,
         0
 )

敢えてVLOOKUP関数で作っているのは、Office 2021より前しか使えないと、このように複雑にならざるを得ない、というのを示しているからです。まだまだ2019までしか使えない現場は、たくさんあります。
もしXLOOKUP関数が使える情況なら、XLOOKUPと構造化参照を使えばほとんどの場合は事足りますので使えば良いです。
※この記事で書く内容は、XLOOKUPが使用出来る環境にも当てはまりますが、後ほど説明します

いま作った数式は、マスターのテーブルについて、シリアルナンバー列から右端の列までのデータ範囲を指定して、そこからシリアルを検索し、見つかったら横を見てプロセッサ列の値を取り出しています。列番号を取得するためにMATCH関数を使ったりしていて複雑です。さらに、普通は、見つからない場合のエラーハンドリングも入れますので、もっとごちゃごちゃします。
周知のように、VLOOKUP関数は、検索のターゲットが左端列に無くてはなりません。だから、範囲指定で左端にくるように調整するか、マスターのキー列（ここでは検索のためのキー）を左端に持ってくる必要があります。煩雑です。

煩雑で可読性を損なう数式

いま書いたような数式を、各列に入れます。

各列の値が自動的に取得される

エラーハンドリングを入れない数式でもこれなので、入れると更にごちゃごちゃします。そして、それが各列に入ります。先述したように、マスターが何十列もあって、そこから値を何十も取得して別表を作成するシナリオは実務的に充分あり得ますので、その情況を考えると、極めて複雑となります。
今回の記事では、このような複雑な状態を出来る限りシンプルにしていくのを試みます。

MATCH関数とINDEX関数による機能分離

同じ事の繰り返し

今は、マスターから値を取得するのに、VLOOKUP関数を使うのを想定しました。実務でも良くあるシチュエーションですが、これが何十列何十行に入ってくる事を考えると、もう少しシンプルにしたい所です。
ここで改めて、VLOOKUP関数の挙動を見ます（第4引数は完全一致とする）。

  1. ターゲットを

  2. ある範囲の左端列から見つけ

  3. 見つかったらc列目の同じ行にある値を返す

  4. ただし完全一致とする

このようです。この処理が、数千セルで繰り返されます。
この検索を同じターゲット、ここではシリアルナンバーに対して繰り返しています。という事は、

  1. ターゲットを

  2. ある範囲の左端列から見つけ

この処理は重複しています。考えてみれば、この検索、繰り返す必要がありますか？　もし集計表などで、数十の値を抽出する必要があるなら、一行について数十回この検索がおこなわれます。

行ごとに同じ検索が繰り返される

同じシリアルに対して検索しているのだから、これは省略できるはずです。

MATCH関数によるターゲット検索

そこで、作成したVLOOKUP関数をクリアして、新しく列を追加します。

ターゲット番号列の追加

ターゲット番号列としました。数式を入れます。

MATCH関数を入れた
=MATCH([@シリアルナンバー],Product_Info[シリアルナンバー],0)

先ほど、同じ行であれば、マスターからシリアルナンバーを検索する処理は、取り出してくる値の数だけ繰り返されると書きました。ですから、

ターゲット（ここではシリアルナンバー）を検索する処理を分離

したわけです。

  1. 同じ行にあるシリアルナンバーを

  2. マスターにあるシリアルナンバー列から探して

  3. 見つかったらその行番号を返す

具体的には上記の処理です。

次に、プロセッサ列に数式を入れましょう。

INDEX関数による値の取得

INDEX関数を入れた
=INDEX(Product_Info[プロセッサ],[@ターゲット番号])

入れたのはINDEX関数です。この処理は

  1. プロセッサ列の中で

  2. 同じ行にあるターゲット番号目のセルを返す

こうなっています。INDEX関数は、

範囲の幅か高さが1セルであれば、範囲と番号だけで書ける

という特徴があります。縦と横の両方の番号を指定しなくて良いので、とてもシンプルに書けます。そして、シリアルナンバーが見つかる行番号は、ターゲット番号列でMATCH関数を使い、別で取得してあるので、INDEX関数でその番号番目のセルを指定するだけで、所望の値が取得できるという寸法です。

検索と取得の機能分離

最初に作ったVLOOKUP関数を、再掲します。

=VLOOKUP([@シリアルナンバー],
         Product_Info[[シリアルナンバー]:[製造年]],
         MATCH(Summary[[#見出し],[プロセッサ]],Product_Info[#見出し],0)-1,
         0
 )

かなり複雑です。これは、

  1. ターゲットの値

  2. 検索範囲の指定

  3. 取得列番号の指定

  4. 一致条件

をまとめて指定するからです。更に、数値の直打ちを避ける汎用的な書きかたをすれば、中にMATCH関数（やCOLUMN関数）などを入れてネストさせるので、より複雑になります。エラーハンドリングを入れた場合の煩雑さは言わずもがなです。しかも、ターゲット検索は同じ行で繰り返されます。
これに対して、

  • ターゲットの検索

  • 値の取得

という機能を分離させた場合には、作業列を設けて、

=MATCH([@シリアルナンバー],Product_Info[シリアルナンバー],0)

MATCH関数で検索をおこない、取得はINDEX関数を用いて

=INDEX(Product_Info[プロセッサ],[@ターゲット番号])

このようにすっきりと書けます。構造化参照を使っているので、可読性も良いですね。検索はターゲット番号列のMATCH関数に担わせておき、値を取得するのはINDEX関数に任せているので、検索の処理の負担も減らせます。
※INDEX関数は参照を返しますが、ここでは値を返して取得すると考えます

VLOOKUP関数のエラーハンドリング

この種の検索で、エラーハンドリングは欠かせません。エラーをそのまま表示させても、ユーザーがそれを解釈できるとは限りません。

エラーハンドリングを、最初に作ったVLOOKUP関数に適用すると、次のようになります。

IFERROR関数によるエラーハンドリング
=IFERROR(
         VLOOKUP([@シリアルナンバー],
                 Product_Info[[シリアルナンバー]:[製造年]],
                 MATCH(Summary[[#見出し],[プロセッサ]],Product_Info[#見出し],0)-1,
                 0
         ),
         "not_found"
 )

IFERROR関数を使いました。ISERROR関数の時代よりはマシですが、これでもだいぶ複雑です。
ここで、MATCH関数とINDEX関数で機能分離した方法でエラーハンドリングがどのように書けるか、検討しましょう。

エラーハンドリングをMATCH関数に任せる

ルックアップでのエラーはどこで起こるかと言うと、ターゲットの検索の部分です。そして、機能分離によって検索はMATCH関数に任せたので、エラーハンドリングもそこに書く事が出来ます。まず、シリアルナンバーを、マスターに存在しない値にすると、次ようにエラーが発生します。

エラーの発生

MATCH関数に、エラーハンドリング処理を追加しましょう。

MATCH関数のエラーハンドリング
=IFNA(MATCH([@シリアルナンバー],Product_Info[シリアルナンバー],0),-1)

IFNA関数を使って、シリアルナンバーが見つからない場合に-1を返すようにしてあります。次はINDEX関数に処理を追加します。

INDEX関数の処理
=IF(
    [@ターゲット番号]=-1,
    "not_found",
    INDEX(Product_Info[プロセッサ],[@ターゲット番号])
 )

これは、同じ行にあるターゲット番号が-1ならエラー時のメッセージを返し、そうで無い、つまりターゲットが見つかっていれば、その行番号にあるプロセッサ列の値を取得しています。機能を分離する事により、それぞれの数式を簡潔に書けています。

エラーメッセージをテーブルに書く

今のエラーハンドリングですが、まずMATCH関数でハンドリングし、INDEX関数では、MATCH関数の結果をIF関数で判定して処理を分けました。これであれば、INDEX関数でもIFERROR関数でハンドリングすれば同じように出来るように思われますし、実際そうです。しかし、エラーハンドリングをMATCH関数に任せると、次のような処理が出来ます。

エラー処理行の追加

マスターテーブルに行を追加し、ID列とシリアルナンバー列に、存在しない事が判っている値を入れます。そして、各属性のフィールドに、見つからない場合のメッセージを入れておきましょう。
MATCH関数のエラーハンドリングで、エラーの場合の値を変えます。

エラー時に1を返す
=IFNA(MATCH([@シリアルナンバー],Product_Info[シリアルナンバー],0),1)

INDEX関数をシンプルな書きかたに戻します。

シンプルなINDEX関数
=INDEX(Product_Info[プロセッサ],[@ターゲット番号])

MATCH関数がエラーの場合、1を返すようにしたので、シリアルナンバーが見つからない場合、INDEX関数は、マスターテーブルの1行目の値を返します。そして、マスターテーブルの1行目に先ほど、エラーメッセージを入れたので、見つからない場合にエラーメッセージが表示されているわけです。これは、

エラーメッセージ自体をテーブルに書き出す

という実装です。したがって、INDEX関数のある数式にエラー処理を書く必要そのものが無くなります。また、テーブルにメッセージを書いているので、メッセージ内容の変更を、数式に手をいれずにおこなえます。

エラーメッセージをテーブルに記載
エラーメッセージ内容

ただしこのやりかたは、エラーメッセージ行を1番上に配置しておく事や、データの型を無視してエラーメッセージを入れるというものなので、それに抵抗があれば、通常のエラーハンドリングをおこなえば良いでしょう。型を厳格に扱うか、変更の柔軟さと可読性を取るか、規則や好みで決めましょう。
※メッセージ行の位置を動的に取得するのも可能ですが、その分、複雑になります

エラーハンドリング専用のテーブルを用意しておけば、ハンドリングは少々複雑になりますが、メッセージをテーブル上で管理できます。たとえば、エラーハンドリング用のテーブルを用意しMATCH関数のハンドリングを-1にして、INDEX関数を次のようにします。

別テーブルのエラーメッセージを返す
=IF(
    [@ターゲット番号]=-1,
    Error_Message[プロセッサ],
    INDEX(Product_Info[プロセッサ],[@ターゲット番号])
 )

MATCH関数の結果が-1なら、エラーメッセージ用のError_Messageテーブルを見に行ってメッセージを返し、そうで無ければマスターから値を取得しています。

XLOOKUP関数との比較

最初のほうで、この記事で検討するシナリオは主に、VLOOKUP関数を使わざるを得ないような場合であり、XLOOKUP関数が使えるならそれを使えば良い、と書きました。
先ほど作った機能分離のINDEX関数、別テーブルのメッセージを参照する数式を再掲します。

=IF(
    [@ターゲット番号]=-1,
    Error_Message[プロセッサ],
    INDEX(Product_Info[プロセッサ],[@ターゲット番号])
 )

同じような処理を、XLOOKUP関数で書きます。

XLOOKUP関数で書いた
=XLOOKUP(
         [@シリアルナンバー],
         Product_Info[シリアルナンバー],
         Product_Info[プロセッサ],
         Error_Message[プロセッサ]
 )

この処理は、

  1. 同じ行にあるシリアルナンバーを

  2. マスターのシリアルナンバー列に見に行って

  3. あれば、マスターのプロセッサ列の同じ番目の値を返す

  4. 無ければ、エラーメッセージ用テーブルのメッセージを返す

というものです。だいたい同じ構造ですね。これは、

  • XLOOKUP関数を使えば、まとめてすっきり書ける

  • 機能分離すれば、XLOOKUP関数が無くてもINDEX関数ですっきり書ける

この両方が言えます。XLOOKUP関数ならほぼ事足りるとも言えるし、MATCH関数に検索を任せておけば、INDEX関数でも、つまりXLOOKUP関数が無くてもこんなに簡単に書けるのだ、とも言えるわけです。

この記事の内容は、主にVLOOKUP関数しか使えない環境での話ですが、XLOOKUP関数が使えても成り立つ話だと書きました。それは、

XLOOKUP関数であっても、検索を繰り返すのは同じ

だからです。ですから、XLOOKUP関数が使える場合でも、MATCH関数とINDEX関数で機能を分ければ、検索の処理の負担を減らせます。実際に私は業務において、Office 2021とMicrosoft 365環境のExcelファイルであっても、データが大きくなれば、XMATCH関数とINDEX関数で機能分離して書く場合があります。

参考資料

こっそり余談

長音記号をつけたりつけなかったりしているのは、
別の場所から取得して作ったマスターには長音記号がついていなかった
というようなシナリオにしているからです。気づく人はいないでしょうけれど。

いいなと思ったら応援しよう！