【XLOOKUP】Googleスプレッドシート新関数検証 -5　近似値一致に逆引きで自由自在！

2022年10月22日 21:25

これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。といいつつ、こっちの方が最新ネタだからか人気ですが。。
可能な範囲で、土日に新しい記事を出していこうかなと思います。

前回の記事
【LAMBDA / XLOOKUP】Googleスプレッドシート新関数検証 -4

2022年9月から使えるようになった新関数と新機能。
検証記事の 1～3で LAMBDA・ヘルパー関数を、そして先週の4では XLOOKUPの基本動作を検証・解説してきました。

今回は XLOOKUP の特徴・応用的な使い方について書いていきます。

前回の XLOOKUP検証でわかったこと

■前回のXLOOKUPの特徴（前半）でわかったこと。
XLOOKUPは引数が多い
XLOOKUPは VLOOKUP、HLOOKUPどちらの代わりも出来る
XLOOKUPは結果範囲を指定して様々なケースに対応できる
XLOOKUPはケースによっては記述が長くなることも
XLOOKUPは普通に検索する時は検索モードを気にしなくてよい
XLOOKUPは IFERRORいらず

とりあえず前回までのおさらいです。

基本操作を通じて、上記のことがわかりました。やっぱ便利ですね。

今回は、もう少し特殊なケースでXLOOKUPを使ってみましょう。

XLOOKUPの特徴（後半）

XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)

途中、説明が至らない部分があるかもしれません。
そこは公式の解説も参考にしてください。

4つの一致モードでモードチェンジだ！

XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)

第5引数は「一致モード」。ここの引数に応じてモードチェンジをします。

モードチェンジといっても、「モードエレガント」で豪華なロングドレス姿に変身するわけではありません。

前回紹介した省略時のデフォルト（完全一致）を含め 一致モードが4つもあるのが XLOOKUPの特徴です。

0 　完全一致モード　（省略時のデフォルト）
1 　完全一致または検索キーより大きい次の値　（近似値モード大）
-1 完全一致または検索キーより小さい次の値　（近似値モード小）
2 　ワイルドカード利用可能で一致する値　（ワイルドモード）

完全一致モードは前回の記事で触れたので、それ以外のモードを検証していきましょう。

並べ替え不要で近似値一致だぞ XLOOKUP

XLOOKUPの一致モードの 1 と -1 は近似値一致です。一致モード -1の（近似値モード小）ですが、

VLOOKUPで第4引数を TRUE とした時と同じ動きなのか？

というと、そうではありません。

VLOOKUP の近似値一致は データが昇順で並んでいることが前提（必須条件）ですが、XLOOKUPは並び順は不問 なのです。

要はXLOOKUPは、データの並び順がバラバラの状態でも検索値に一番近い（次に大きい、または次に小さい）値を見つけてくれるってことです。

ここが大きな違いで、XLOOKUPの便利な点です。

上の画像のケースだと、Code 29 が検索キーですが、 A列（検索列） A11セルの 29を加工しているため、完全一致ではヒットしません。

また、A列（検索列）のCode（数値）は並び順がバラバラの為、VLOOKUPのモード TRUEでは、なぜか 9のうしがヒットしています。これは求めている結果ではありません。

つまり、VLOOKUPは並びがバラバラのデータでは近似値一致が出来ないってことです。

一方、XLOOKUPは、

モード1 （近似値モード大）　・・・ 30 の「すぷれっどしーと」
モード -1 （近似値モード小）・・・ 28 の「めだか」

と 求めている結果が返ってきています。

XLOOKUPでは データの並びを気にせず、検索値 29 が見つからなかった場合は、29に最も近い（近似値の）大きい、または小さい値が取得できるということです。

■近似値一致は数値で使うのが一般的

近似値一致は、数値以外でも一応出来ます。

上記は B列の商品名（ひらがな）を対象に B列には存在しない「テスラ」を近似値一致で検索した結果です。

昇順で並んでいないデータなので、当然こちらも VLOOKUPでは近似値一致が出来ません。

一方、XLOOKUPでは近似一致で大・小が取得できています。

B列を SORTしたデータを見ればわかりますが、「テスラ」が入る場所の上下のセルの文字「だんぼ」と「とうきょう」を返してるのがわかります。文字列でも近似値一致が出来ています。

ただ、名前などの漢字を含むデータでは、基本が音読みのGoogleスプレッドシートでは思うような動作をしません。

数値以外で近似値一致を利用することはあまり無いでしょうが、漢字を含むデータで利用する時は注意が必要です。

■近似値一致の使いどころは限定的だけど便利

数値でもデータバラバラでの近似値一致って、使える場面は限定的だと思います。ただ 降順データでの利用ケースは、お仕事によってはあるんじゃないでしょうか？

例えば学校の先生が、テストの点をベースに成績ランクをつけるとします。

点数に応じた成績ランクの基準表を用意する場合、通常はこんな感じで 上の成績（点数がいい方）からランク表を作成すると思います。

この基準表だと、点数が 降順なので VLOOKUPの近似値一致が使えません。

=XLOOKUP(F3,$A$3:$A$9,$C$3:$C$9,,-1)
モードチェィンジ -1 近似値モード小！

でも XLOOKUPであれば、この表をそのまま使って近似値一致小 で検索ができます。（下にオートフィルすることを想定して、範囲は絶対参照にしましょう）

結構便利かも。

もう一つ別の例、こんどは近似値一致大を使うケースです。

こんな感じの所得税率の一覧表をベースに、各社員の年間所得から税率を関数で取得したい時があったとします。

※ 一覧表の一番下は通常は 4,000万円以上という表記ですが、「以下」で揃える必要があるので、そこだけ 10億以下という形に修正しています。年収10億以上の人は、すいませんが対象外ｗ

一覧表の所得の金額は昇順となっていますが、〇〇万円以下という区分けなので、例えば年間所得 500万の人は近似値小の 330の行 10%ではなく、近似値大の695の行 20% の方を参照しなければなりません。

残念ながら 近似値一致大を取得したい場合は、並び順にかかわらず VLOOKUPでは対応できないのです。

=XLOOKUP(F3,$A$3:$A$9,$C$3:$C$9,,1)
モードチェィンジ 1 近似値モード大！

でも、XLOOKUPだとモードチェンジ 1でサクッといけちゃうというわけです。

これはさすがに「XLOOKUP だーいすき。」って言っちゃうかもｗ

■VLOOKUPでも無理すれば出来なくもない

でも Googleスプレッドシート職人としては、

Excelから「私が来た。」

って感じでオールマイトのごとく XLOOKUPが登場して全て解決ってのは、No2ヒーローのエンデヴァーと同じく 気持ち的には面白くないわけです。

XLOOKUPがないと出来ない。（出来なかった）とは言えませんｗ
VLOOKUPでも 「やればー、できる！！」

じゃあ、どうやるか？

前回もVLOOKUPでも出来るもんで活躍した、Googleスプレッドシートならではの 範囲加工、範囲結合を駆使すれば 良さそうです。

これで、さっきは「だーいすき」って言っちゃったけど・・・。

べ、別にアンタ（XLOOKUP）がいなくても、平気なんだから。
VLOOKUPがあれば対応できるんだからね。勘違いしないでよね！

とツンデレ返しが出来ますねｗ

成績のケースをVLOOKUPでやる場合

=VLOOKUP(F12,SORT($A$3:$C$9),3,true)

たとえば成績のケースであれば、基準表の検索キー（A列）が昇順であればよいわけですから、式の中で SORT関数で並び替えしたものを範囲としてしまえばVLOOKUPで対応可能です。

SORT関数は、正しくは SORT($A$3:$C$9,1,true) と書く必要がありますが、1列のデータを単純に昇順にする場合は、簡略形で記述できます。

所得税率のケースをVLOOKUPでやる場合

=IFERROR(VLOOKUP(F12,$A$3:$C$9,3,FALSE),
　VLOOKUP(F12,{{0;$A$3:$A$8},$C$3:$C$9},2,TRUE))

所得税のケース（近似値一致大）は、ちょっと複雑です。

表が昇順になってるので、VLOOKUPの近似値一致小でヒットした一つ下を返せば良さそうってことで、前回みたいな 範囲を一つずらして結合すればいけるかなと考えます。

でも、それだと検索値が 120 や 90といった 表内の最小値 195 以下の場合は #N/A(見つかりません）を返してしまいます。

そこで {0;$A$3:$A$8} として、A3からA8 （表の 195～4,000）の上に 0を付けた 1列の配列を作成した上で、さらに $C$3:$C$9 と横方向で結合させ

{ { 0 ; $A$3:$A$8 } , $C$3:$C$9 }

という配列を作成します。
つまり式の中で以下のように表を加工してるわけです。

これだと年間の所得が100とか 150の人は近似値で 0を参照して 5%を返せますね。

これを使えば VLOOKUPの近似値一致小でいける！？と満足しそうですが、表の数値との完全一致のケースを考慮しなければなりません。

〇〇円以下 となっているので、年間所得が 330ちょうどの人は作成したバーチャル配列（加工表）でVLOOKUPすると 20%ですが、正しくは 10%を返す必要があります。

そこで、一旦完全一致で VLOOKUPした上で、（完全一致で見つからない）エラーの時だけ上記のバーチャル配列を使った近似値一致で再度VLOOKUPという処理にしています。

出来はしたけど、ちょっと面倒ですね。

やっぱアンタ（XLOOKUP）がいないとダメかも・・・。
と再びデレになってしまいそうですｗ

上記のケース、XLOOKUPはもちろん Arrayformula の記述にできますが、VLOOKUPでの代用式も Arrayformulaが使えます。

また、今回は例として VLOOKUPで代用のきく 縦方向 の検索でしたが、横方向の場合は HLOOKUP で同じように代用できます。
※ただし、横方向だと並び替えがより手間がかかります。

XLOOKUPの代替式としては、FILTER関数とSORT関数 を組み合わせる方法もあるのですが、Arrayformulaでスピル対応出来る VLOOKUPでの方法を紹介しました。

もちろんXLOOKUPが使える今は、無理に代替式を使う意味はありません。

ワイルドだろー？ XLOOKUP

モード2 　ワイルドカード利用可能で一致する値　（ワイルドモード）

4つのモードの最後、ワイルドモードとはなんでしょうか？
※語感が面白いので、mirがワイルドモードと勝手に言ってるだけです。公式ではワイルドモードという言葉は使われていません。

こちらは、記載の通りワイルドカードを使った「あいまい検索」（部分一致）が使えるモードになります。

〇〇を含む、もしくは〇〇で始まる（〇〇で終わる）セルを検索したい、つまり完全一致ではなく 部分一致検索をしたい時に活躍します。

ワイルドカードとして使える 特殊文字 は2種類あって、

*　　任意の文字列
?　　任意の一文字

となります。これは知ってる人も多いかと。

あいまい文字の数が 3文字とか 4文字と決まっていて、それで絞りたい場合には ?が有効ですが、滅多にないケースですね。

実践では * は結構使うけど ? はあまり使わない かも。

使い方としては、検索キーを A1セル参照とした場合は、

A1の文字列を含む　"*"&A1&"*"
A1の文字列から始まる　A1&"*"
A1の文字列で終わる　　"*"&A1
A1の文字列の前になにかしら 3文字がつく　"???"&A1

このように活用します。

■VLOOKUPでも出来る部分一致検索

でも、これって VLOOKUP時代から使える機能なんですよね。

モードを指定する以外は、VLOOKUPもXLOOKUPも使い勝手は一緒です。
詳しくは、VLOOKUPの解説サイトを参考に。

じゃあ、わざわざモード切替とかしないで、

VLOOKUPと同じようにモード指定なしでXLOOKUPも使えればよかったんじゃね？

って思うかもしれませんが、ワイルドカードとしてではなく 普通に * や ? を含む文字列を検索したい時に困るのです。（滅多にないケースですが）

無理やり、これに該当する事例を用意しました。

例えば左の「おそ松表」から、 *松（あすたりすくまつ）や ?松（くえすちょんまつ）を検索して続柄を取得したい！という要望があったとします。

E列の式で普通に VLOOKUPで完全一致検索をした場合は、* や ? がワイルドカードとして扱われてしまいます。そのため、

*松　・・・　松で終わる文字列　→　一番最初に 「おそ松」がヒット
?松　・・・　1文字 + 松の文字列 →　一番最初に「一松」がヒット

となってしまい、欲しい結果が返ってきません。

一方 F列の式、XLOOKUPでモードを指定しない場合は、通常の完全一致モードでワイルドモードはONになっていない為、* や ? も通常の文字列として扱われ求めていた結果が得られます。

■VLOOKUPでもワイルドカードはエスケープできる

ただ、VLOOKUPでは回避策はないのか？ワイルドカードを文字列として扱えないのか？ というと、そんなことは無くて、上記画像の G列の式のように

=VLOOKUP("~"&D2,$A$2:$B$9,2,false)

と、* や ? の前に ~（チルダ）をつけることで、エスケープさせる（ワイルドカードとして扱わない）ことが出来ます。

ちなみに、 ~ が検索文字列に含まれる場合は ~~ とする必要があります。

これらのワイルドカード、エスケープは Googleスプレッドシートならではというわけではなく、昔から Excelで使える手法 です。

エスケープが必要なケースはめったに無いでしょうし、ワイルドモードに関してはVLOOKUPからの大きな進歩はないかなと。

第5引数、4つの一致モードの特徴・使い方は以上となります。

検索を逆からいけるぞ XLOOKUP

XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)

最後の第6引数は「検索モード」となります。

1 　先頭から末尾に向かって検索　（省略時のデフォルト）
-1 　末尾から先頭に向かって検索
-2 バイナリ検索 (範囲は昇順である）
2 　バイナリ検索 (範囲は降順である）

バイナリ検索は、二分探索というやつですね。
大量データを検索する際、検索スピードが改善されるようです。

数万行くらいじゃ速度の違いはピンとこないです。数百万行とかそのレベルの大量データだと違ってくるのかも。

ただ、正直Googleスプレッドシート上でそんなに大量データを検索することがあるかなー？という印象。

もちろん大量データをGoogleスプレッドシートで扱っていて、普通にXLOOKUPを使った時の検索時間に頭を悩ませている人には、このオプションが効果を発揮すると思います。

範囲は昇順（または降順）である、という前提条件があるので注意。
バイナリモードについては、特に検証は不要でしょう。

そうすると、検証すべきは

　-1 　末尾から先頭に向かって検索

これだけですね。どんなメリット・活用方法があるんでしょうか？

■最新データはたいてい下か右に追加されていく

Googleフォームから出力したデータや、日々更新されていくデータなどは、基本的に新しいものほど下（右）に追加されていきます。

たとえば上のような、営業毎のその日までの売上実績を日々更新していくようなデータがあった場合、田中の直近の実績はＢ列に複数ある中の一番下の田中の右の数字になります。

普通に =XLOOKUP(E3,B2:B,C2:C) としてしまうと、一番上の田中にヒットしてしまい、150という実績が返ります。これは、デフォルトが上から下への検索だからです。

これは求めている直近の田中の実績ではありません。

これを検索モード -1 で逆からの検索とする式

=XLOOKUP(E3,B2:B,C2:C,,,-1)

こうすれば、下から検索して一番最初に見つかる = 一番下の 田中（2022/10/14）の実績である1,220 という正しい結果が得られます。

こんな感じで、直近データの取得に使えますね。

■その昔、下から検索をVLOOKUPでやっていた時

Googleスプレッドシートの場合は、SORT関数で上下を逆にして検索という手が使えます。

=SORT(B2:C,ROW(B2:B),0)

SORT関数は並べ替えの対象範囲と同じサイズの 1列の配列であれば、なんでも並び替え条件に使えます。

また、FILTERやINDEXと同じく Arrayformulaなしで自動スピる関数なので、配列をそのまま引数に使えます。

今回のような 上下を反転させたい時は、ROW関数で得られる行番号を並び替え条件の列として、0(FALSE) で降順を指定すれば OK。

この並び替えしたデータを対象範囲としてVLOOKUPすることで、XLOOKUPの下からの検索と同じことが出来ます。

■XLOOKUP 逆から検索の活用例：一番下（右）の値を取得

XLOOKUPの 検索モード -1 の活用例です。
縦方向ばっかりだったので、横方向の例にしてみましょう。

こんな形の備品貸出表があったとします。
最後に借りた人を C列に出力させたい場合、各行毎の 一番右側の空白じゃないセルの値となります。

※ただし *で直接拾えるのは文字列限定。数値、日付などは一工夫必要です。

「空白セルじゃない」という検索 → 一致モード 2（ワイルドモード）利用
一番右側の　→　検索モード -1 （逆から検索）利用

=XLOOKUP("*",$D2:$AI2,$D2:$AI2,,2,-1)

同じように、ある列の一番下の空白じゃないセル といった検索も可能です。

XLOOKUP登場前までは、この一番右（下）の空白じゃないセルの取得は結構面倒で、FILTER と INDEXを使ったり、範囲を反転させて VLOOKUPといった処理をしてましたが、XLOOKUP登場でシンプルに 対象の行の一番右の値を取得できるようになりました。

これらは残念ながら検索キーではなく、検索範囲・結果範囲がスライドしていく処理なので Arrayformulaが使えません。

ただし、LAMBDA BYROWなら一つの式でスピル可能です。簡単な式なので、LAMBDAを使いこなしたい人はチャレンジしてみてください。

検索モードの検証は以上になります。

今回の後半応用編では XLOOKUPの特徴の中でも、特殊なモード（設定）の活用を検証しました。

これで、すべての引数、すべてのモード についての検証が完了です。

■今回のXLOOKUPの特徴（後半）でわかったこと。
XLOOKUPは一致モードが4つもある
XLOOKUPは並びがバラバラでも近似値一致が使える
XLOOKUPは VLOOKUPにはない近似値モード大がある
XLOOKUPは一致モードを切り替えて「あいまい検索」が出来る
XLOOKUPは検索モードも 4つある
XLOOKUPはバイナリ検索でスピードアップ（要並び順）
XLOOKUPは下（右）から検索が出来て便利

「特徴」に関しては以上となります。

次回は XLOOKUP検証の最後、XLOOKUPで出来ること・出来ないこと、そしてそれをどう対処すればよいか？を検証していきます。

XLOOKUPは本当に検索系関数最強なのか？

それはまだ……混沌の中。

それが……XLOOKUP！

■このシリーズの次の記事

この記事が気に入ったらサポートをしてみませんか？