Office TANAKA - ワークシート関数[XLOOKUP 関数]

ここで解説する XLOOKUP関数は、ProPlusに追加された関数です。Excel 2016やExcel 2019では使用できませんのでご注意ください。また、この関数では「スピル」という機能が使われています。スピルに関しては「Excel 2016レビュー[Excelの使い方が激変する「スピル」]」をご覧ください。XLOOKUP関数で何ができるのかを下の動画で解説しています。ぜひ、ご覧ください。必ず驚きます。Youtubeでは、ほかにもたくさんの動画を公開しています。チャンネル登録をお忘れなく！
Office TANAKAチャンネル

XLOOKUP関数

ヤバイです。これは驚きました。まさかこんな関数を新しく追加してくるなんて。みんな大好きVLOOKUP関数の超拡張版です。VLOOKUP関数と何が違うのか、どんな使い方ができるのか、注意点は何かなど、じっくり解説します。まずは、引数から見てみましょう。

引数「検索値」は、従来のVLOOKUP関数と同じです。"田中"とか"100"など、検索したい値を指定します。

引数「検索範囲」は、VLOOKUP関数と考え方が異なります。従来のVLOOKUP関数では、この引数「検索範囲」に"VLOOKUP関数で扱う表全体"を指定しました。そして、指定した表全体の"左端列"で「検索値」を探します。それがVLOOKUP関数の仕様でした。

新しいXLOOKUP関数の引数「検索範囲」には、検索する範囲だけを指定します。表全体ではありません。これは、MATCH関数の使い方と似ています。

この「検索範囲」は"行方向"だけでなく、"列方向"も指定できます。つまり、新しいXLOOKUP関数は、従来のVLOOKUP関数とHLOOKUP関数の両方を兼ねているんです。「いやぁ、HLOOKUP関数なんて、今までも使う機会なんてないしぃ」などと思うのは早計です。これ、実はスゴイことができるんです。詳しくは後述します。

次の引数「戻り配列」は、要するにどこを返すかという範囲です。検索する範囲と、結果として得たい値が入力されている範囲を別々に指定できるのは、LOOKUP関数みたいなイメージですね。

このように、検索範囲と、検索結果の範囲を別々に指定できるので、従来のVLOOKUP関数では不可能だった右側の列を検索して左側の値を返すことが可能になります。それだけだったら、LOOKUP関数やMATCH関数+INDEX関数でもできますが、LOOKUP関数の「検索範囲が昇順で並んでいなければいけない」の制約はありませんし、MATCH関数よりもはるかに柔軟な検索が実現できます。そのへんは、後で詳しく解説します。

必ず指定しなければいけないのは、この「検索値」「検索範囲」「戻り配列」の3つです。まずは、これだけでやってみましょう。

「検索範囲」と「戻り配列」は、どちらも同じ大きさでなければいけません。

別シートのセルも指定できます。

引数「見つからない場合」には、検索値が見つからなかったときに表示する文字列や、計算する数式を指定します。この引数は、一度XLOOKUP関数が公開されたあと、ユーザーグループに寄せられたユーザーからのリクエストに応じて追加したらしいです。そりゃそうですよ。今までわれわれは、VLOOKUP関数でのエラーをいかに出さないかで、20年以上戦ってきたのですから。IF関数とISERROR関数しか武器がなかった時代には、何とかそれで対応しました。同じVLOOKUP関数を2回記述しなければならなかったので、数式がメチャクチャ長くなりましたっけ。その後、条件付き書式で見せなくするという手も流行りました。もちろん、セルの背景色が"白色"ではなかったときは苦労しましたけど。そして、Excel 2007でIFERROR関数が追加されたときに歓喜しました。これで数式が短くなるって。でも、でもですよ、何か心の中にモヤモヤは残ったんです。なんで、こんなことしなくちゃいけないんだろうって。VLOOKUP関数側で「見つからなかった場合」に対応してくれればいいのにって。同じ引数は、FILTER関数に最初から実装されていました。だったらさ、XLOOKUP関数も最初から入れておけばいいのに。まあ、ここは素直に喜びましょう。GJ！＞Microsoft

さて、XLOOKUP関数は、特に何も指定しないと「完全一致」で検索します。

これは、新しくXLOOKUP関数を作った理由のひとつとして、MicrosoftのExcel Blogにも明記されています。英語なので要約すると、次のようなことです。

今までのVLOOKUP関数って、第4引数を省略するとTRUEとみなされて"近似検索"になっちゃうよね。でもさ、実務って"完全一致"で検索する方が多くない？毎回ここにFALSEを指定するのって超面倒くさいし、うっかり忘れると変な結果になって"何だかなぁ"ってなるよね。だから新しいXLOOKUP関数は、標準で(何も指定しないと)完全一致で調べるようにしたよ。その方がみんな使いやすいっしょw

もちろん、近似検索も可能です。それどころか、今までのVLOOKUP関数では不可能な近似検索もできます。その近似検索を指定するのが、次の引数「一致モード」です。ここには、どのように検索するかを指定します。指定できるのは次の数値です。

数値	動作
0	完全一致(規定値)
-1	見つからないとき次に小さい値
1	見つからないとき次に大きい値
2	ワイルドカードによる検索

完全一致はいいですね。この引数を省略すると0を指定したとみなされて、完全一致で検索します。もしワイルドカードを指定した"あいまい検索"をするのなら、2を指定します。つまり、2を指定しない限り、＊や？などは単なる文字列として検索されます。これは嬉しいですね。今までも「＊や？を文字列として調べるときはどうするのですか？」というのは定番の質問でした。さて、新しいのは「次に小さい値」と「次に大きい値」です。これは、結果を見た方が分かりやすいでしょう。

そもそも実務ではあまり、こうした"近似検索"ってやりませんよね。よく、こうした使い方で「料金表」みたいな例を出して「○Kgに満たないときはいくら」みたいな使い方を解説しますけど、そんなことみんなやってますか？やらないでしょ。まぁ、いいや。いずれにしても、近似検索で「満たない小さい値」だけでなく「大きい値」も調べられるようになりました。

便利なのは、次の引数「検索モード」です。ここには次の数値を指定します。

数値	検索の方法
1	先頭から末尾へ検索(既定値)
-1	末尾から先頭へ検索
2	バイナリ検索(昇順で並べられているとき)
-2	バイナリ検索(降順で並べられているとき)

まずは「1：先頭から末尾へ検索」と「-1：末尾から先頭へ検索」の違いを解説します。これ、要するに検索値が複数存在していたとき、どの値を返すの？ってことです。次の表をご覧ください。

ここでは、A列の「記号」で"B"を検索します。しかし、A列には"B"が複数存在しています。このとき、先頭から末尾に向かって検索して、最初の"B"が見つかったものとする、ってゆーのが「1：先頭から末尾へ検索」です。対して、末尾から先頭に向かって検索して、最初の"B"が見つかったものとする、ってのが「-1：末尾から先頭へ検索」です。それぞれの違いをご確認ください。

「バイナリ検索」というのは、従来のVLOOKUP関数で、第4引数に「TRUE」を指定するのと同じです。VLOOKUP関数の第4引数に「FALSE」を指定すると、Excelは指定された検索値を、検索範囲の各セルに対して"1つずつ"チェックしていきます。いわゆる完全一致です。しかし、この方法は検索範囲のセル数が増えると、調べるのに時間がかかります。実務では一般的に「FALSE」の完全一致で調べることが多いです。だから、今までのVLOOKUP関数は、大量に使うと再計算に膨大な時間がかかっていました。再計算でExcelが固まり、ステータスバーに表示される「再計算○○％」という表示を見ながら、ため息をついたのは私だけではないはずです。対して、VLOOKUP関数の第4引数に「TRUE」を指定すると、「見つからない場合は、超えない最大値を返す」という検索をします。このとき、Excelは検索値を、1セルずつチェックしていません。第4引数に「TRUE」を指定できるのは、検索の元範囲が昇順に並べられているときです。Excelはまず、検索範囲の中央を調べます。もし検索値がその中央値より大きかったら、調べた中央値より前には検索値がないと分かります。次に、最初に調べた検索値から後ろ部分の中央値を調べます。このように、中央値を調べ続けるような検索を「二分探索」あるいは「バイナリサーチ」と呼びます。詳しくは、Wikipediaなどで調べてください。この方法だと、値を1つずつ調べるよりも、劇的に速く検索できます。

新しいXLOOKUP関数の引数「検索モード」に指定できる"バイナリ検索"は、まさにこの「バイナリサーチ」のことです。元データが昇順に並んでいるだけでなく、降順に並んでいる場合にも対応できます。ちなみに試してみましたが、元データが昇順または降順に並べられているとき、「検索モード」に"バイナリ検索"を指定したら、XLOOKUPでもメチャクチャ高速でした。

さて、ここからは応用編です。こんな使い方ができるよ、というのをご紹介します。

スピるんです！

まずは、スッゲェ便利なやつをご紹介しましょう。これは"応用編"ではなく、普通の使い方です。

上図のXLOOKUP関数で検索しているのは"B"です。検索範囲はA2:A5です。そして「戻り配列」には、A2:D5のように複数列のセル範囲を指定しています。このままEnterキーを押すと

スピってます！そう、XLOOKUP関数はスピるんです！これ、メチャクチャ便利じゃないですか？今までだったら、すべての列にVLOOKUP関数を入れなければなりませんでした。しかも引数「列位置」の数値を2→3→4みたく変えなくちゃならないし。COLUMN関数を使うにしても、けっこう面倒くさかったです。それが！もう複数セルに入力しないでいいんです。コピーしないでいいんです。スピってくれるから。もちろん、次のような指定も可能です。

この「XLOOKUP関数はスピる！」という特性を利用すると、今までは使い道のなかったHLOOKUP的な処理で、次のようなことができます。

もちろん、セルE1に入力した検索値を変更すると、

さらにスゴイのは、これSORT関数と組み合わせると、こんなこともできます。

SORT関数については「SORT関数」をご覧ください。

じゃ、FILTER関数と組み合わせられないかなと。やってみたら、こんなこともできました。下図のような表でやってみます。

まずは、普通にFILTER関数してみます。FILTER関数については「FILTER関数」をご覧ください。

FILTER関数だけでは、絞り込んだ結果のうち"特定の列"だけを取得することはできません。ここにXLOOKUP関数を組み合わせます。

さらに、SORT関数も使うと

こんなことも簡単にできます。これ、マクロでやるにしても、けっこう面倒くさいですよ。こちらは関数ですから、もちろん元データが変われば、自動的に結果も更新されます。XLOOKUP関数、スゴイです。

計算速度はどうなの？

今回は、とても便利なXLOOKUP関数をご紹介したわけですが、さて気になるのはお値段…もとい、気になるのは計算速度です。今までのVLOOKUP関数に比べてどうなんでしょう。なにせVLOOKUP関数は、Excel 2016で劇的に速くなったのですから。そのへんのお話は「Excel 2016レビュー[VLOOKUP関数が400倍速くなった]」をご覧ください。

検証方法は「Excel 2016レビュー[VLOOKUP関数が400倍速くなった]」と同じです。10万件の元データに対して、1万件のVLOOKUP関数とXLOOKUP関数を入力して、再計算の時間を計測します。完全一致で検索し、見つかるのは10万件目です。以下に結果をお見せしますが、「Excel 2016レビュー[VLOOKUP関数が400倍速くなった]」のコンテンツを書いたときと今回では、PCが異なります。なので、同じ数値にはならないことをご了承ください。ちなみに、関数の計算速度だけを、より正確に計測するよう、ロジックも改良しました。

	VLOOKUP	XLOOKUP
1回目	0.437秒	0.422秒
2回目	0.453秒	0.453秒
3回目	0.422秒	0.406秒
4回目	0.437秒	0.406秒
5回目	0.422秒	0.407秒
6回目	0.437秒	0.406秒
7回目	0.438秒	0.422秒
8回目	0.437秒	0.422秒
9回目	0.453秒	0.406秒
10回目	0.422秒	0.422秒
平均	0.436秒	0.417秒

充分に高速ですね。10,000件くらいだったら、再計算でイライラすることはありません。ただ、再計算で小っちゃい○がクルクル回るのは、何も"数式の計算に時間がかかっている"だけが原因ではありません。たとえば、シート内に大量の条件付き書式や、大量の(しかも不要な)入力規則などが存在していると、それだけでExcelはメチャクチャ遅くなります。ほとんどのみなさんは、再計算に時間かかかるのは"セルに大量の計算式を入れているから"と思っているようですけど、違うんですよ。もっと、他にもたくさんの原因があります。ちなみに、条件付き書式だとか、入力規則だとか、あるいはユーザー定義の表示形式だとか、Excelの動作速度に影響を及ぼす原因って、一般的には画面に表示されていません。管理用のダイアログボックスを開けば分かりますが、開かないと見えません。そうした、ブック内に含まれている"見えない情報"を、すべてリスト形式でレポートするのが「ワークシート診断ツール」です。私が作りました。これ、Excel全員が使うべきです。下記ページからダウンロードできます。完全無料です。

ワークシート診断ツール

VBAからは呼び出せない呼び出せます

もうひとつ残念なのは、このXLOOKUP関数、VBAからWorksheetFunctionでは呼び出せません。

動的配列数式として使える新しい関数ですから、VBAから使うのは、ちょっと無理があるのかもしれません。FILTER関数は、VBAから呼び出せるけど条件の指定が難しいです。でも、SORT関数やUNIQUE関数はVBAから普通に使えるのですから、もしかしてXLOOKUP関数も…と、少し期待していただけに残念です。

いずれにしても、XLOOKUP関数の登場で、Excelの使い方はさらに進化するでしょうね。

(2019/11/11 追記)

朗報です！WorksheetFunctionからXLOOKUP関数を呼び出せるようになりました。さすがMicrosoftさん！いや、私は最初から信じてましたよ。いずれ呼び出せるようになるって。

ちなみに、バージョン1912(ビルド 12231.20000)で確認しました。

いやぁ～WorksheetFunctionでXLOOKUP関数を呼び出せるってことは、VBAで"列単位の取得"が、超簡単になるってことです。これは嬉しい！今夜は祝杯をあげます。