スポンサーリンク/Sponsored Link

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

XLOOKUP関数 ノートパソコンでExcelを使う人の手元の写真
Excel(エクセル)
スポンサーリンク/Sponsored Link

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

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

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

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

随時更新していきます。

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

スポンサーリンク/Sponsored Link

XLOOKUP関数の書式

XLOOKUP( 		検索値 , 検索範囲 , 戻り値範囲 , 		[見つからない場合] , 		[一致モード] ,		 [検索モード])
XLOOKUP関数の書式。[ ] 内は省略可能

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

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

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

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

シンプルな完全一致検索

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

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

XLOOKUP関数の検索範囲と戻り値範囲
省略できる引数はすべて省略した、一番シンプルなXLOOKUP関数の使用例

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

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

VLOOKUP関数の第四引数の注意点 
第四引数を完全に省略すると	「近似一致」になる
第四引数を、カンマだけ打って値を省略すると
	ゼロとみなされ、FALSEと解釈され、「完全一致」になる

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

XLOOKUP関数の「一致モード」
どちらの省略方法でも、「完全一致」の動作をするようになった

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

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

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

入場料の集計。大人、中人、小人と横方向に配置された料金表から、XLOOKUP関数で料金を検索して表示
XLOOKUP関数を使った、よこ方向の検索例

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

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

入場料の集計。XLOOKUP関数で、大人、中人、小人と横方向に検索し、たて方向に料金を読み取らせようとしたが、エラーになった
横方向に検索し、たて方向に料金を読み取らせようとしたが、エラーになった

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

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

入場料の集計。XLOOKUP関数で、大人、中人、小人と横方向に検索し、たて方向に配置された料金をTRANSPOSE関数で転置して読み取らせた例
横方向に検索し、たて方向に配置された料金をTRANSPOSE関数で転置して読み取らせることができた
スポンサーリンク/Sponsored Link

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

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

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

XLOOKUP関数による、複数列一括取得。社員番号を入力すると、氏名列のXLOOKUP関数が、一気に他の項目もスピルして出力している

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

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

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

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

氏名の間の空白がバラバラな名簿を、名前で検索。検索値も、検索範囲も、ともにSUBSTITUTE関数で空白を除去して検索している
氏名の間の空白がバラバラな名簿を、氏名で検索したところ。検索値も、検索範囲も、ともにSUBSTITUTE関数で空白を除去して検索している

ポイントは、数式内の

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

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

範囲をSUBSTITUTE関数にかけると、置換された結果が範囲としてスピルして出力される
範囲をSUBSTITUTE関数にかけると、置換された結果が範囲としてスピルして出力される

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

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

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

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

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

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

引数「見つからない場合」に範囲を指定した場合。14番の商品は「存在しない商品です」価格ゼロと表示されている。

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

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

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

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

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

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

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

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

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

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

スピルの動作サンプル。Excel365では、単一セルに =A1:C3 などと範囲を数式として記述すると、その範囲がスピルして表示される。Excel2019では、この表記は受け入れられずエラーになる
Excel365とでは、単一セルに =A1:C3 などと範囲を数式として記述でき、スピルして表示される。
Excel2019では、この表記は受け入れられずエラーになる

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

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

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

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

さきほどと同じ名簿抽出をテーブル内でおこなったところ。氏名列のXLOOKUP関数が、スピルできずエラーになっている
氏名列のXLOOKUP関数が、スピルできずエラーになっている

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

テーブル内でXLOOKUP関数をオートフィルしてみたところ。
構造化参照で絶対参照を実現するには、同じフィールド名を2つ重ねて範囲のように記述する
テーブル内でXLOOKUP関数をオートフィルしてみたところ。
構造化参照で絶対参照を実現するには、同じフィールド名を2つ重ねて範囲のように記述する

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

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

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

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

スポンサーリンク/Sponsored Link

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

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

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

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

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

XLOOKUP関数で近似一致検索 宅配の料金表から、サイズごとの発送料金を検索 サイズオーバーは「送れません」
「検索値以上の最小値」を使った近似一致検索。「見つからなかった場合」は「送れません」。
「検索モード」は省略しているので「先頭から検索」になる

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

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

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

宅配料金表を、でたらめに並べ替えた状態で、発送料金を検索するテスト。
XLOOKUP関数の、検索モード1 -1は正しく出力 モード2のバイナリ検索は正しくない VLOOKUPも正しくなく、エラーも出ている
宅配料金表を、でたらめに並べ替えた状態でテスト。
XLOOKUP関数の、検索モード1 -1は正しく、モード2のバイナリ検索は正しくない。最後は参考にVLOOKUP

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

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

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

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

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

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

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

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

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

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

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

XLOOKUP関数の近似一致検索、上からと下からの違い。同じものが2つあると、先に見つかった方が優先されていることがわかる
XLOOKUP関数の近似一致検索、上からと下からの違い。同じものが2つあると、先に見つかった方が優先されていることがわかる

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

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

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

スポンサーリンク/Sponsored Link

ワイルドカード一致検索

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

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

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

XLOOKUP関数のワイルドカード一致のサンプル。商品リストから「バインダー」を含む商品のレコードをスピルして表示
B1セルに入力した文字列を含む商品のレコードを、スピルして表示。

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

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

XLOOKUP関数のワイルドカード一致のサンプル。商品リストから「ペン」を含む商品のレコードをスピルして表示するが、最初のボールペンしか表示されない
B1セルに入力した文字列を含む商品のレコードを、スピルして表示。ただし、最初の一個しか表示できません

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

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

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

部分一致検索で、複数見つかった商品をすべてスピルして表示する数式。FILTER関数とSEARCH関数をネスト
FILTER関数とSEARCH関数をネストして使用

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

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

IFERRORとSEARCH関数のネストした部分の戻り値。各レコードの品名の、何文字目に「ペン」が入っているか、という数値の配列になっている

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

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

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

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

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

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

スポンサーリンク/Sponsored Link

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

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

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

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

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

生徒の履修表から、最終履修日と履修内容を取り出す表。XLOOKUP関数で、「末尾から検索」を指定
生徒の履修表から、最終履修日と履修内容を取り出す表。XLOOKUP関数で、検索モードに「末尾から検索」-1 を指定

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

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

当教室で使用している履修表。次のレッスンの際に、氏名を入力すると、自動的に前回履修した内容が表示される
当教室で使用している履修表。次のレッスンの際に、氏名を入力すると、自動的に前回履修した内容が表示される

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

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

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

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

当教室で使用している履修表のしくみ。検索範囲・戻り値範囲を、それぞれ先頭から自分の前の行までに設定し、下から検索している

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

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

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

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

同じようにして

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

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

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

スポンサーリンク/Sponsored Link

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

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

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

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

たてに果物の名前が並び、よこにグレードABCが並んでいる表から、特定の果物の特定のグレードの価格を取り出す
たてに果物の名前、よこにグレードが並んでいる表から、特定の果物の特定のグレードの価格を取り出す

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

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

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

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

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

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

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

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

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

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

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

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関数で、運賃を検索してるってことで、ご勘弁ください…

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

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

スポンサーリンク/Sponsored Link

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関数で記述したものが、こちらになります。

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

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

こうして、最後の

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

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

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

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

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

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

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

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

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

Excel Labsのインストール。挿入→アドインを入手→Excel Labsで検索→追加
アドイン「Excel Labs」をインストール

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

ホームタブの右端にExcel Labsというボタンが表示されている

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

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

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

Excelの右側にあらわれた、Advanced Formula Environmentの画面

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

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

Advanced Formula Environmentのタブ

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

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

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

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

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

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

複雑な数式をLET関数を使って書き直したもの。Advanced Formula Environmentの表示をスクショしている

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

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

こうしてみると、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」を定義してみます。

Advanced Formula Environmentの「Modules」タブにLAMBDA関数を書き、カスタム関数「PARCEL」を定義したところ
Advanced Formula Environmentの「Modules」タブにLAMBDA関数を書き、カスタム関数「PARCEL」を定義したところ

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

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

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

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

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

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

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

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

定義したPARCEL関数で運賃を計算したところ
定義したPARCEL関数で運賃を計算したところ

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

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

スポンサーリンク/Sponsored Link

まとめ

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

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

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

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

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

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

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

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

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

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

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

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

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、オンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

キュリオステーション志木店運営をフォローする
スポンサーリンク/Sponsored Link

コメント

タイトルとURLをコピーしました