【Googleスプレッドシート】VLOOKUP"配列展開"のススメ

はじめに

VLOOKUPとXLOOKUPの比較の記事中で少し触れましたが、VLOOKUPは配列展開させる使い方がとても便利です。

特に第三引数の"指数"(列番号)にMATCHを組み合わせて使うとかなり柔軟に対応できます。
(基本的には引用元と引用先の項目名が一致していることが前提となりますが、違っている場合でも工夫次第で何とかなることも多いです。)

この点に関してはXLOOKUPよりも便利に使えますのでどんどん展開させていきましょう…！

配列展開

【配列展開とは】
配列展開とは、「1つの数式が単一の値ではなく"値の集合(配列)"を返し、それがシート上の複数のセルに一括で出力される挙動」を指します。

最近のExcelでいうところの"スピル"ですね。
(もはや"最近"というほどでもない…？)

もともとそれ自体が配列展開することを前提とした関数もありますが(FILTER・UNIQUE・SORT・SPLIT・SEQUENCEなど)、ARRAYFORMULAを使うことでそうではない関数についても配列処理することが可能になり、結果を配列展開させることができます。

本来配列展開しない関数をARRAYFORMULAで配列展開させる場合、イメージとしては「1つの数式を連続してコピペ(オートフィル)した時に得られる結果」を1セルへの入力だけで得られるものだと思ってもらえれば大体OKです。

【配列処理】
ちょっと細かいのですが、言葉の使い分けとして…

「配列展開」は"結果が複数セルに出力される"ことまで定義に含まれています。
では結果として出力されない、"数式内で配列として扱われるもの"をどう呼べば良いかというと、「配列化」や「配列として処理する」などとなります。

まとめると、「ARRAYFORMULA」は"配列展開させるための関数"ではなく、"配列処理を可能にする関数"ということになり、
「各要素を"配列として処理"することで、結果を"配列展開"する」
という整理になります。

うーーん、こう書くことでむしろ分かりにくくなってしまうかもしれないとは思いつつ…
いやでも、ここは言葉として区別しておきたいなぁ…という思いもあり……

書いておいてなんですが、ざっくりと「ARRAYFORMULAを使って配列展開させる」とだけ抑えてもらえれば大丈夫です…！

ARRAYFORMULA-VLOOKUP

ここからはARRAYFORMULAとVLOOKUPを組み合わせる具体的な式を例にしながら話を進めていきたいと思います。
【例】

=ARRAYFORMULA(IF(A2:A="",,IF(B1:1="",,(VLOOKUP(A2:A,'マスタ'!A:AD,MATCH(B1:1,'マスタ'!1:1,0),0)))))

はい、なんとなくありそうな、「'マスタ'というシートから情報を引っ張ってくる」ことを想定した例です。

• ARRAYFORMULAは一番外側に書いておくことでその内側全域で対象を配列として処理することが可能になります。

• 各引数を配列化することで、IFの判定式やMATCH、VLOOKUPを配列として処理しています。

• 最終的に結果が二次元に配列展開されます。

【補足】
「IF(A2:A="",,IF(B1:1="",, 」の部分は、縦の検索値や項目名をぴったりと選択するのであれば不要な部分ですが、このように指定しておくと検索値や必要な項目が増減しても自動で反映されるので便利です。
「項目は固定です」という場合には「IF(B1:1="",, 」は消してしまっても良いですね。

検索値が"縦方向の配列"、指数(列番号)にあたるMATCHが"横方向の配列"になっていることがポイントで、このように直交する配列を指定することで結果を二次元に展開することができます。

【配列展開のメリット】
「それができることは分かったけど、『コピペ(オートフィル)した時に得られる結果と同じ』だと言うのであれば、コピペで良くない？」というご意見もあろうかと思います。
というわけでそのメリットについて少し書いていきます。

• 効率が良い
  まずは、単純に"その方が効率が良い"が挙げられます。
  大量の数式が必要だったところが1つで済むのは実際の計算処理的にも効率が良いです。
  ただ、今の時代とんでもなく巨大な表でもなければその恩恵は感じにくい気もしますので、「これがメリットだ！」というには少し弱いかもしれませんね。

• 増減に強い
  では、上でも少し触れた「検索値や項目の増減に強い」という要素はどうでしょうか。
  「途中の行や列に検索値や項目を追加する」というケース、ままあると思います。
  この時その1行や1列にわざわざ数式をコピペする必要がありません。
  これは"手間がかからない"以上に"コピペミスが起こらない"ことが非常に大きいメリットになります。

• 勝手にいじられない
  「せっかく数式を入れていたのにいつの間にか誰かが値に変えていた」といったトラブル、ないでしょうか？
  配列展開されている領域でこれをやると一気に全体がエラーになります。
  「いや全てがエラーになるならより状況が悪いじゃないか」と思われるかもしれませんが、値にしたその1セルを消すだけでまた正しく展開されますので実際それほど大した問題ではありません。
  「確実にすぐに気付ける」ことが大きなメリットなのです。
  また「ちょっと何かすると全体のデータが消える」となればそうそう勝手にいじられることはなくなります。

一方でこれは逆に「ここだけ例外的にこうなんだよね」が気楽に出来ないということでもあります。
そういった例外処理がしばしば発生するようなケースでは配列展開はちょっと不向きですね。
使い分けていきましょう。

名前付き関数

そして、やっぱり私の記事は"名前付き関数"に話が繋がってきます。

「配列展開は便利かもしれないけど、何か式が長いし…というかそもそも"ARRAYFORMULA"が長いし、今まで通りで良いよ」
という方には、是非"名前付き関数"として登録しておくという方法をオススメいたします。

以下、本当に初期の頃に作成した名前付き関数で、VLOOKUPを二次元に配列展開させるだけのものを紹介させて頂きます。
引数は4つです。

【引数と引数の説明】
引数:検索値列,検索項目行,vlook範囲,表示切替

• 検索値列:1列の検索値の配列を指定します。

• 検索項目行:取得したい項目の配列を1行の形で指定します。

• vlook範囲:引用元の範囲です。VLOOKUP同様に検索対象列が左端である必要があることに加え、項目名の行が上端である必要があります。

• 表示切替:エラーをそのまま表示するか空白にするかの切替です。0でエラーを空白にします。

【定義式】

=LET(
  検索値,L_CROP_C(検索値列,1),
  検索項目,L_CROP_R(検索項目行,1),
  元データ,L_CROP_A(vlook範囲),
  項目行,INDEX(元データ,1,0),
  結果,ARRAYFORMULA(VLOOKUP(検索値,元データ,MATCH(検索項目,項目行,0),0)),

  IF(表示切替=0,IFERROR(結果,),結果)
 )

【解説】

• 動的クロップ
  前回紹介させて頂いた「動的クロップ」を使用しています。
  データが入力されている最終行や最終列を特定して、データの有効範囲を切り抜く関数です。
  (コメントで教えて頂きましたが、Excelには普通にそういう関数・機能があるらしいですね…。)
  主には上の例の「IF(A2:A="",,IF(B1:1="",, 」 の代わりとなる部分で、違いとしては「空白だったら何も返さない」ではなく「そもそも未入力領域は計算しない」ということになります。
  これによって検索値列も検索項目行もvlook範囲も全て雑に選択することが許容されます。

• 引数の省略
  第三引数(vlook範囲)で"上端を項目名にする"ことを求めているのでMATCHの検索範囲を内部で作成することができます。(「INDEX(元データ,1,0) 」)
  いちいち引数として渡すのは面倒ですので…。
  また、VLOOKUPの第四引数([並べ替え済み])は"0"(完全一致)で固定しています。
  完全一致以外で使うこと…正直ないですよね……。

• エラー表示の切替
  最後に、"エラーが出た場合そのまま表示するか空白にするか"を切り替えられるようにしました。
  ここは引数を「エラーの場合」に変更して、空白以外にも対応できるようにしても良いかもですね。

おわりに

ここまで読んで頂きありがとうございました。

本当は今回も「名前付き関数の紹介をしよう」と思って書き始めたのですが、内容が"VLOOKUPを配列展開させるだけ"だったので、名前付き関数として解説すべき部分がほとんどなく……
「この際VLOOUPの配列展開自体を主軸にしよう」と路線変更してみました。

初期に作ったものということもあって、本当に一つの用途のみを想定したシンプルなものでしたが…
実は「今ならどういうものを作るだろう…」と考え始め、そのまま「究極のLOOKUP関数」(Gemini談)を作成しましたので、次回はそれの紹介をさせて頂こうと思います。
※実際には全く"究極"ではありません……。"多目的"くらいですかね。