素晴らしき XLOOKUP 関数の世界。できること、できないこと、他の方法は?

Microsoft 365 Excelで、XLOOKUP関数が使えるようになって、もうずいぶんたちました。

今まで「VLOOKUP」と打っていたところに、「XLOOKUP」と打つようになったわけですが、

なかなかに奥深く、まだまだ工夫や研究の余地がありそうです。

当店で実際に使用しながら、気づいた点、便利な使い方、できないことは他の方法でどうやる? など。

随時更新していきます。

※本記事内の数式は、基本的にExcel 2021で動作するように書いています。
一か所、CHOOSECOLS関数を使用しており、この数式はMicrosoft 365サブスクリプションのExcelが必要です。

XLOOKUP関数の書式

XLOOKUP関数は、これまでとこんなに違う

XLOOKUP関数は、これまでのVLOOKUP/HLOOKUP関数とどこが違うのか?

書き出してみると、こんなに違います。

• 検索範囲と戻り値範囲を別々に指定
• たて検索、よこ検索両方に対応
• スピルによる複数列一括出力が可能
• 戻り値が、値ではなく範囲である
  結果がスピルされることのほか、XLOOKUPの戻り値を、範囲を要求する他の関数の引数として再使用することもできます。
• [見つからなかった場合]の追加
  他の関数を併用しなくても「#N/A」を防げる
• [一致モード] の改良
  0:完全一致(既定)
  → 省略した場合の動作が統一された
  -1:近似一致(検索値以下の最大値)
  1:近似一致(検索値以上の最小値)
  →以下だけでなく以上にも対応
  2:ワイルドカードに対応
• [検索モード] の追加
  1:先頭から(既定)
  -1:末尾から
  →新設 先頭/末尾どちらからでも検索できるように
  →従来とは検索方法が違い、並べ替えしていなくても見つけてくれる
  2:バイナリ検索(昇順並べ替え必須)
  -2:バイナリ検索(降順並べ替え必須)
  →従来の検索方法は「バイナリ検索」として区別されることに
• 動作が高速化された(らしい)
  ※当店ではまだ、検証というほどの検証はできていません

シンプルな完全一致検索

最低限の引数で、シンプルに完全一致検索

省略できる引数をすべて省略すると、「検索範囲を先頭から順に、完全一致で検索」という動作になります。

引数を省略した際の動作が統一され、分かりやすくなった

VLOOKUP関数の第四引数は、省略するときに注意が必要でした。

この分かりにくさが、XLOOKUP関数では改善されました。

よこ方向の検索も、同じXLOOKUP関数で

従来は、たて方向はVLOOKUP、よこ方向はHLOOKUP、と使い分けていましたが、XLOOKUP関数では両方とも同じ関数で対応できます。

検索範囲、戻り値範囲をよこ方向で指定するだけです。

たて・よこの混在した検索はエラーになる

どういう仕組みになっているか、確認するために、検索範囲はよこ、戻り値範囲はたて、という、たてよこ混在した検索をさせてみると、エラーになりました。

この結果から、XLOOKUP関数は、「与えた配列内の何番目」という認識をしているのではなく、配列の行列構造を認識しているように思えます。

もし、たてよこ混在した検索をしたい場合は、TRANSPOSE関数をかませて、片方を転置するとエラーなく検索できます。

複数列をまとめてスピルで取得する

「戻り値範囲」に複数列を指定して、一気に取得

もうひとつ、XLOOKUP関数の新しい機能として「スピルによって複数列/行を取得できる」ことがあげられます。

使い方も簡単で、これから活用場面が増えるのではないかと思います。

「検索範囲」「戻り値範囲」を数式内で加工して検索

スピルという動作の特性から、「検索範囲」「戻り値範囲」を関数で加工して検索することができます。

例えば、「氏名」の間の空白が、あったりなかったり、全角だったり半角だったりバラバラな場合です。

ポイントは、数式内の

SUBSTITUTE(SUBSTITUTE(名簿2[氏名]," ",""),"　","")

という部分で、「氏名」列の範囲そのものをSUBSTITUTE関数にかけています。
この部分の出力は次のようになります。

このように、列内の各値が全部SUBSTITUTEされた状態でスピルして出力されます。

このスピルされた結果を「検索範囲」に指定して検索できるということです。

従来は、作業列を作って、空白を除去したデータをつくり、そこで検索する必要があったわけですが、こうすると、数式内ですべて処理が完結できます。

同様に、「戻り値範囲」についても、数式を使って加工した状態で出力することができます。

引数「見つからない場合」にもスピルが使える

地味に便利なのは、引数「見つからない場合」にも範囲を指定でき、スピルして表示されることです。

この例では、「見つからない場合」のためのデータを16行目に入れて、参照していますが

=XLOOKUP(A3,商品マスタ[No],商品マスタ[[品名]:[価格]],{"存在しない商品です",0})

のように、配列として直接与えても動作しました。

「検索値」「検索範囲」には、さすがに範囲は使えませんでした

それならば、と、複数列にわたる検索値を、複数列にわたる検索範囲で検索してみたらどうだろうか、と試してみましたが、これはさすがにできませんでした。

検索値は単一の値、検索範囲も一行または一列である必要があるようです。

もしできたら、ちょっと便利かなと思ったんですけどね。だめでした。

Excel2021から導入された「スピル」

そもそもスピルって何なのか、ということなんですが

図のように、セルに「範囲」を数式として入力することが許容されました、ということです。

したがって、関数の戻り値として「値」でなく「範囲」が戻ってきた場合も、同様に、範囲そのものとして拡張して表示されます。

とてもシンプルかつ大胆な変更で、エクセルの数式の組み方を根本的に変えてしまうといっても過言ではないと思います。

テーブルの中ではスピルはできない

とても便利なスピルですが、テーブルの中では機能しないので注意が必要です。

仕方がないので、スピルを使わずXLOOKUP関数をオートフィルして、むりくりやってみたものがこちら。

構造化参照なので絶対参照がなかなかややこしく、すごく、むりくりな感じの数式になってしまいました。

スピルという「範囲」を扱う動作が、レコード・フィールドで管理される「テーブル」とは、概念的にかみ合わないのだと思います。

今後、例えば、スピル予定の数式そのものを、テーブルの特殊なフィールドとして包含するなどの手法が導入されれば、この点は変わるのかもしれません。(その場合は、例えば、スピルされた列は、元のフィールドに対する「サブフィールド」といったことになるのかもしれません)

そんなこと実現するかどうか分かりませんが。

刷新された、近似一致検索

まずはVLOOKUP関数でいつもやってる近似一致から

XLOOKUP関数は、近似一致の検索も刷新されました。

まずは普通に、VLOOKUPの代わりに使った例から。
[一致モード]は 1 (検索値以上の最小値)を使っています。

※Microsoft Supportでは「完全一致。 見つからない場合は、次の大きなアイテムが返されます。」という表記になっています。なかなか謎めいた日本語です。
「一致すれば、一致したアイテム。見つからない場合は、それより大きい中でいちばん小さいアイテム」
と読み込むと、「検索値以上の最小値」であることがわかります。

「見つからない場合」を指定することで、IF関数やIFERROR/IFNA関数を使わなくても「送れません」と表示できるようになりました。

検索範囲が昇順(降順)でない場合の動作に「驚き」

XLOOKUP関数で、すごく変わったなと思うのは、検索範囲の並び順がデタラメな場合の動作です。

XLOOKUP関数の「検索モード」の設定において、

• 検索モード 1:先頭から
• 検索モード -1:末尾から

の、どちらを選んでも、でたらめに並んでいる料金表から、正しい料金を検索できました。

これは、モード 1 と モード -1 が、ともに「バイナリ検索」ではなく、検索範囲内のすべての要素を確認してから戻り値を決定しているからだ、と思われます。

意図的に変な並び順にする必要はありませんけども、日々更新されるデータや、並べ替えを忘れた場合などにも、正しい結果が返ってくると期待されます。

• 検索モード 2:バイナリ検索・昇順

の場合は、VLOOKUP関数とはまた少し違いますけれども、正しい結果が出ませんでした。

広い範囲から高速で検索してほしい時には、従来通り、昇順/降順で並べ替えて、検索モード2/ -2 を使うとよさそうです。

検索モード 1(上から)と -1(下から)の違い

検索範囲のすべての値を確認してくれるのならば、
上からでも、下からでも、同じ結果になるのではないか?と思われますが

検索範囲に2つ以上、同じ値が存在した場合に影響がありました。
(ここでも、一致モードは 1 検索値以上の最小値 を使用)

この例では、「3辺合計」が「120」の項目が２つあり、金額は上の方は1530、下の方は1780となっています。

この例で
上から検索すると、110cmのところは1530円となり
下から検索すると、110cmのところは1780円となっています。

「先に見つかった値を優先的に採用している」ことが分かります。

ワイルドカード一致検索

続いて、新しい　一致モード:2 ワイルドカード一致 を試します。

結果を一個しか表示できず、イマイチ用途が微妙?

多くのサイトを拝見しましたが、やはり、このように部分一致検索の用途になりそう。

ただし、XLOOKUP関数では、見つかった最初の一個しか表示できません。

例えば、検索モード0: 上から下へ で「*ペン*」を検索すると、このような結果になります。

3個あるのに、先頭の1個しか表示されていないのが分かります。

複数ある検索結果をすべて表示するには…FILTER関数

複数ある検索結果を、すべて表示するためには、例えば次のような方法をとる必要があるでしょう。

少々分かりにくいのですが、FILTER関数でワイルドカードが使えないため、ワイルドカードの使えるSEARCH関数を援用しています。

=IFERROR(SEARCH(B1,商品マスタ[品名]),FALSE)
の戻り値が、次のような配列になっています。

この配列の、FALSEではない部分が、FILTER関数の戻り値としてピックアップされている形です。

※SEARCH関数はワイルドカードが使えますが、もともと文字列の部分一致検索であるため、上記のケースでは必要ありません。「ペン」の部分を「ペ?」とすると、ぺを含む2文字の単語を含む検索ができます。

なんだか微妙にちぐはぐで、使いにくいのでは…

こうしてみると、微妙に使いにくい、ちぐはぐなところを感じます。

• XLOOKUP関数では、ワイルドカードが使えるが、一個しか表示できない。
• FILTER関数では、複数の結果がまとめて取得できるが、ワイルドカードが使えない。

ワイルドカードそのものが、「合致するものが複数あるかも」という可能性を内包していると思われ、結果の複数表示はニーズがあると思います。今後改良が切望されます。

XLOOKUP関数の新機能「末尾から検索」が面白い

今までの検索関数になかった新機能、それが「末尾から検索」です。

なかなか面白いことができます。

例えばこんな使い方・「前回の履修内容を自動的に表示する」

例えば、日々追加されるテーブルの中から「該当する最終レコードを取り出す」ような使い方です。

実際は、次のように、もうひとひねりして使っています。

当教室で実際に使用している「カルテ」(履修表)は、もうひとひねりして、次のようにしています。
(名前などはすべて架空のものです)

D列には、XLOOKUP関数を使用した数式が入っています。

=XLOOKUP([@氏名],OFFSET([@氏名],-1,0,1,1):$B$1,OFFSET([@内容],-1,0,1,1):$C$1,"",0,-1)

ひとつ上の行の参照をOFFSET関数で出して、そこから先頭までを検索範囲・戻り値範囲とし、検索モード -1「末尾から先頭へ」を使っています。

こうすることで、履修表の本日のところに名前を追加するだけで、自動的に前回の履修内容が表示されるようになっています。

XLOOKUP関数ができるまでは、わざわざ、上下逆さまになるように作った作業列をVLOOKUP関数で検索する、という、ややこしいことをしていたのですが

XLOOKUP関数にして、とてもすっきりし、作業列も不要になりました。

急なご予約でも、サッと前回の内容が参照できるので、たいへん重宝しています。

「末尾からの検索」さまざまな応用ができそうです

同じようにして

• 前回の受注内容を表示する
• 直前のチェック日を表示する
• 直前に乗務したドライバー名を表示する

など、いろいろな応用が可能なのではないかと思います。

これらは、「末尾から検索」がないころには、まったく想像もしなかった使い道で、なにか新しい世界がひとつ開けたような気がしています。

たて・よこを組み合わせた検索・INDEX+MATCHのような使い方

XLOOKUP関数の戻り値は「範囲」である性質を利用

XLOOKUP関数は、INDEX+MATCHの代わりにも使えます。

つまり、たてよこに検索が必要な表から、必要な値を取り出すことができるのです。

外側のXLOOKUP関数で、Bグレードの価格を取り出すのですが

「戻り値範囲」として、価格表全体でなく「もも」の価格表を与えなければなりません。

そこで、内側のXLOOKUP関数で、「もも」の価格表を取り出します。これはABC全グレードの「もも」の価格を含む範囲で、単独で取り出すとスピルして表示されるものです。

そのあと、出力された「もも」の価格表から、Bグレードをよこ検索で取り出すことになります。

このように、XLOOKUP関数は範囲を返すことから、ネスティングすることで、ひじょうに複雑な検索をこなすことができます。

応用例・複雑な宅配便運賃の検索

応用例として、複雑な宅配便運賃の検索例を示します。

運賃表は、こちらに掲載されているものから、都道府県名だけ削除して別の表に取り出す加工をして、サンプルとして使用しました。

宅急便運賃一覧表 全国一覧 | ヤマト運輸

クロネコヤマトでおなじみ、ヤマト運輸のウェブサイトの「宅急便運賃一覧表：全国一覧」ページです。

www.kuronekoyamato.co.jp

「サイズ」には、実測した三辺合計の寸法を入力します。
「コンパクト」にチェックを入れると、入力した数値に関わらず、コンパクトサイズの運賃を表示します。

C5セルの数式は、次のようになっています。

=IF(AND($E$2>200,NOT($D$3)),"送れません",IFNA(XLOOKUP(XLOOKUP($A$2,地域一覧!$A$2:$A$48,地域一覧!$B$2:$B$48),運賃表!$A$1:$N$1,XLOOKUP($E$2,CHOOSECOLS(FILTER(運賃表,運賃表!$A$1:$A$109=XLOOKUP($B$2,地域一覧!$A$2:$A$48,地域一覧!$B$2:$B$48)),2),FILTER(運賃表,運賃表!$A$1:$A$109=XLOOKUP($B$2,地域一覧!$A$2:$A$48,地域一覧!$B$2:$B$48)),,1*NOT($D$3))),"正しく入力してください"))

なんだかわけのわからない複雑な数式ですが、まあ、とにかく
XLOOKUP関数が複数個入っているわけです。

途中のXLOOKUP関数は範囲を返しつつ、最後のIFNAのあとのXLOOKUP関数で、運賃を検索してるってことで、ご勘弁ください…

…

…

…といって済ませる当教室ではございません。

ここから、ちょっと脇道のお話をさせていただきます。

LET関数、LAMBDA関数と合わせ技で、複雑な計算を手軽に

複雑な数式は、LET関数で分かりやすく記述してみよう

はい、話題のLET関数です。

「何に使うの?」「初心者お断り?」といった声も聞かれますが

ここで、LET関数のすばらしさをお見せしておこうと思います。

ただいまのややこしい数式

=IF(AND($E$2>200,NOT($D$3)),"送れません",IFNA(XLOOKUP(XLOOKUP($A$2,地域一覧!$A$2:$A$48,地域一覧!$B$2:$B$48),運賃表!$A$1:$N$1,XLOOKUP($E$2,CHOOSECOLS(FILTER(運賃表,運賃表!$A$1:$A$109=XLOOKUP($B$2,地域一覧!$A$2:$A$48,地域一覧!$B$2:$B$48)),2),FILTER(運賃表,運賃表!$A$1:$A$109=XLOOKUP($B$2,地域一覧!$A$2:$A$48,地域一覧!$B$2:$B$48)),,1*NOT($D$3))),"正しく入力してください"))

これをLET関数で記述したものが、こちらになります。

1. 【着地】【発地】→与えた都道府県名を、都道府県と地方名の対応表を使って、地方名に変換
2. 【着地別一覧】→料金表全体から、着地の地方に該当する部分をFILTER関数で抜き出し。すべてのサイズ、すべての着地を含む範囲が出力される
3. 【運賃区分】→取り出された【着地別一覧】の中から、各サイズのサイズ名の列だけを取り出したもの。次の検索で「検索範囲」として使う。
4. 【着地サイズ運賃】→【着地別一覧】の中から、指定のサイズの行だけを取り出して出力

このような順番で、名前を定義しながら計算を進めています。

こうして、最後の

XLOOKUP(発地, 運賃表!$A$1:$N$1, 着地サイズ運賃)

が、【着地サイズ運賃】の中から、発地をよこ検索し、最終結果を導く数式になります。

LET関数は、刷新された Advanced Formula Environment で編集しよう

LET関数の書式は、次のようなものです。

LET(名前,名前の数式,名前,名前の数式,…えんえんと続き…,計算式)

LET関数で書くと、たしかに、何度も同じ関数を書く必要がなく、短く書けたり、演算速度も上がることが期待されます。

しかし、LET(—-たくさん複雑なこと—)　となるため、狭い数式バーの中で編集しようとすると、パンクしてわかんなくなるのが難点でした。

そこで、刷新されたAdvanced Formula Environmentを使います。

これは拡張機能です。インストールして使います。
アップデートがあり、「Excel Labs」という拡張機能の中に含まれました。

すると、「ホーム」タブのはしっこに、ビーカーのようなボタンが出ますので、ここを押します。

右ペインにこのような画面が出ますので、Advanced formula environmentの「open」をクリック

※ちなみに下にあるLABS.GENERATIVEAI functionとは、OpenAI API Key をセットすると、関数でOpenAIの各モデルにアクセスできる機能です。

こうすると、Excelの右側に、数式編集用のスペシャル画面が登場するのです。
※これ以降のスクリーンショットは、Excel Labsになる前のものを使用しています。Advanced formula environment自体の構成は現在のものと同じです。

刷新されて、以前はなかった「Grid」モードが登場!

緑色のタブの部分はこのようになっていて

Grid は、アクティブセル ひとます分の数式を、読みやすく展開して編集するモードです。
今回はこちらを使用します。以前はなかったモードなのですが、アップデートで登場しました。
それで「刷新された」と書いています。これは便利で感動です。

Names は、名前付き範囲を分かりやすく編集するモードです。

Modules は、このブックに付属する数式モジュールを、コードとして編集するモードです。

NamesとModulesは、おもにLambda関数の定義に使用します。

AFEの「Grid」モードで、LET関数もスイスイ書ける

さきほどお示ししたスクショは、この「Grid」モードで数式を表示したものです。

まるでプログラムを書くように、順々に部分的な計算を定義して、名前に置き換えていきます。

すると、最終的な数式は、定義した名前を使って、とてもシンプルに記述することができます。

こうしてみると、LET関数、めっちゃ使える奴やん! という気がしてきませんでしょうか?

IFやIFNAを使ったことのご説明

宅配便運賃計算の数式、少しご説明しておきたいと思います。

せっかくのXLOOKUP関数なのに、IFやIFNAを使って例外処理したのはなぜ?

XLOOKUP関数では「見つからない場合」という引数が新設されています。

なら、サイズや着地・発地がイレギュラーな場合の例外処理は、「見つからない場合」でやればいいのでは? と普通思いますね。

先頭のIF関数が必要な理由は、「コンパクト」という文字列が検索範囲に混じっているため、こうしないと「送れません」が表示できなかったのです。

着地サイズ運賃, XLOOKUP(
                $E$2,
                運賃区分,
                着地別一覧,
                ,
                1 * NOT($D$3)
            )

「検索モード」は、「コンパクト」のチェックが入っていると 0(完全一致)、入っていないと 1(検索値以上の最小値)になるように仕掛けをしています。

ところが、「コンパクト」がチェックオフの状態で、最大サイズ200を超える数値で検索すると、見つからないわけではなく「コンパクト」という文字列が検索されてしまいました。

そこで、あらかじめIF関数で、「コンパクト」がチェックオフで、かつ、サイズオーバーの場合を分けておくことが必要になってしまったものです。

IFNA関数が必要な理由は、XLOOKUP関数をネスティングした際、内側のXLOOKUP関数で「#N/A」が出てしまうと、外側のXLOOKUP関数では「見つからない場合」が発動せず、「#N/A」になってしまったためです。

であれば、内側のXLOOKUP関数のすべてに、ダミーの「見つからない場合」、例えば “ERR”を設定すればよいのかもしれませんが

ダミーの値がそこら中に散らばっている状態は、なんだか汚くて分かりにくいと思いました。それで、まとめて外側でIFNA()で例外処理をすることを選択しました。

LAMBDA関数で、カスタム関数にまとめてみる

複雑にネスティングしたXLOOKUP関数を、きれいに使いこなそう、ということで、脇道に入ってきたわけですが

脇道の最後に、

この運賃計算を、ブック内のどこでも簡単にできるようにする

ということを考えてみます。

LET関数を使って、読みやすくまとめたわけですが

あちこちでこの計算をする場合には、その場所に全部このLET関数を記述する必要があります。

それはちょっと、わかりにくくなりそうです。

発地、着地などの参照も、その都度全部、直さなければなりません。

そこで、この計算式をLAMBDA関数を使って、カスタム関数にまとめてみることにします。

サポート記事にリンクしてみましたが、これだけ読んでも難しいので、実際にやってみます。

カスタム関数の作成は、AFEの「Modules」タブで

カスタム関数の定義には、Advenced Formula Environmentの「Modules」タブを使います。

カスタム関数「PARCEL」を定義してみます。

２行目以降は、ほとんどさきほどのLET関数のコピペです。

「コンパクト」という文字列が与えられた場合の処理だけ、チェックボックス無しで対応するために、書き替えました。

Modulesタブの中の「+NEW」について

Modulesタブの中には、複数のコードモジュールを置くことができます。

今回は、シンプルにWorkbookモジュール、つまりこのブックの基本モジュールに書きました。

モジュールを追加すると便利なのは、他のブックで同じカスタム関数を使い回す場合や、GitHub Gistに公開されているモジュールをインポートして使う場合などです。

定義したカスタム関数をつかってみる

さて、このように定義すると、ワークシート内の運賃計算はこんなに簡単になります。

今回のカスタム関数は、運賃表などの参照範囲がブック内になければ動作しないため、本当の汎用関数とはいえませんが、いったん作ってしまえば手軽に複雑な計算ができます。

従来は、VBAをいじらなければカスタム関数は作れませんでした。それと比べれば、格段に世界が広がったと思います。

まとめ

長い記事になりましたので、まとめておきます。

XLOOKUP関数を使った応用例を追い求めていくうちに、LET関数、Advanced Formula Environmentまで書き進んでしまいました。

まるでExcelの記事じゃないみたいです。

いいえ、これが、これからのExcelなんですね。

Excelは、これからまだ大きく変化すると思います。

マイクロソフトが、ChatGPT の OpenAI に多額の出資をしていることが話題ですが

Excelの未来像として予想していますのは「こういう計算がしたい」と自然言語で記述すると、必要な数式やシートの案を出してくる、という姿です。

そうなると、ちょっと怖いけど、なんだか便利そうです。

でもたとえそうなっても、出てきた案をチェックし、仕上げるのは人間だと思います。

そうやって仕上げたものをもとに、またAIが学んでいくでしょう。

なのでやっぱり、人間のスキルアップは大事ですね。

…こんな結論でいいんでしょうか。まあいいとしてください。がんばりましょうね。