XLOOKUP関数のしくみ
これまでの話
データを見つけ出す2つの方法
前回と前々回では、ある範囲から見つけたいデータを探し出す2通りの方法を紹介しました。
この2つのやりかたの概要は次の通りです。※寿司の価格、ここではサーモン皿について調べたいとします
VLOOKUP関数
ネタの集まりとそれに対応する価格を含んだ範囲について
左端の縦並びからサーモンを探し
見つかったら、その真横にある何番目かのものを取り出す
INDEX関数とMATCHの組み合わせ
ネタの集まりとそれに対応する価格を含んだ範囲について：INDEX関数
サーモンが縦の何番目かにあるかを探し：縦方向のMATCH関数
価格が横の何番目かにあるかを探して：横方向のMATCH関数
範囲の中で縦と横が交差する所にあるものを取り出す：INDEX関数
それぞれの特徴
VLOOKUP関数は、関数自体がルックアップ機能を実現するもので比較的シンプルなしくみを持っている。対してINDEX関数とMATCH関数の組み合わせは、VLOOKUP関数における左端からしか見つけられないなどの性質を補える機能を持っているが、数式が複雑になって可読性を損なう場合がある、などの、それぞれのメリット・デメリットがあるのでした。
XLOOKUP関数の登場
VLOOKUP関数には、
左端からしか見つけられない
見つけたい範囲と取り出したい範囲を含めた範囲を指定する必要がある
見つけかたの標準が完全一致では無い
などの性質があり、それに対する不満がユーザーから指摘される事が、しばしばありました。そして、そういう指摘を反映してか、近年になって、新しい関数がExcelに実装されました。それがXLOOKUP関数です。
XLOOKUP関数のしくみ
シナリオ
お馴染みの、寿司食べた表です。
この真ん中の表から、所望のネタ皿の価格を探し出して、左の表に取り出すのが目的であるシナリオです。
関数の構造
さっそくXLOOKUP関数の構造を見ましょう。おさらい。関数とは、材料を受け取ってそれを処理し、結果を返してもらう機能の事です。
=XLOOKUP(見つけたいもの, 見つけに行く範囲, 取り出しに行く範囲)これがXLOOKUP関数です。VLOOKUP関数に似ていますが、範囲が2つある所が特徴です。
2つの範囲
VLOOKUP関数は、見つけに行く縦並びと取り出しに行く縦並びを含んだ1つの範囲を対象としました。そして、取り出したい縦並びが左から何番目にあるかを指定したのです。
いっぽうXLOOKUP関数は、それぞれを分けて指定します。ですから、材料として入れる範囲が2つになっています。いまは、
ネタの並び
単価の並び
を対象とします。
シンプルな構造
後は簡単です。いま示した2つの範囲と、探したいもの、ここではネタですが、それを合わせて3つの材料をXLOOKUP関数に入れれば良いのです。
=XLOOKUP(B2,$G$2:$G$9,$I$2:$I$9)
とてもシンプルですね。
どちらにあっても
XLOOKUP関数はVLOOKUP関数と違って、見つけに行く範囲と取り出しに行く範囲を分けて指定します。この事は、
それぞれの範囲の位置を問わない
というメリットをもたらします。いや、そうなるように実装された、と言ったほうが正しいのかも知れませんが。
VLOOKUP関数は、1つの範囲を指定して、その左端の縦並びからしか見つける事が出来ませんでした。対してXLOOKUP関数は、範囲を分けて指定できるので、取り出しに行く範囲が、見つけに行く範囲の左側にあっても構いません。
単価並びを左端に、ネタ並びを右端に配置しました。このようであっても、関数に入れる材料の範囲を分けて指定する、つまり、前の材料にJの並びを入れ、後の材料にGの並びを入れれば、問題なく目的の機能を果たします。
縦どころか
次の図をご覧ください。
単価を取りに行く表の向きが変わっています。つまり、まぐろであれば、その単価・皿の色、カロリーが、横方向から縦方向に変更されました。そうすると当然、数式が壊れます。見に行く範囲が縦並びだったのが横並びになったので、エラーが出ています。#REF!エラーは、見に行く範囲を見失う、つまり参照ができないのを表すものでした。
実は、XLOOKUP関数は、このようなケースでも対応可能です。
関数に入れる２つ目の範囲を、縦並びから横並びに変えました。上手く機能しています。この事からXLOOKUP関数は、
範囲の向きを問わない
のが解ります。
見つけかた
いまXLOOKUP関数に入れた材料は3つでした。考えてみると、VLOOKUP関数で入れた見つけかたがありません。
VLOOKUP関数などでは、見つけかたが重要であり、近いものを探す場合と完全に一致するものを探す場合とがあって、既定が前者であるから、文字列検索では主に完全一致が求められるので、FALSEまたは0を明示するのが重要なのでした。
いっぽうXLOOKUP関数では、完全一致が既定です。
0 - 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。
完全一致が既定なので、それを指定する必要がありません。結果的に、数式が簡潔になります。表計算アプリケーションでは、これは大きなメリットです。
ちなみに、材料の内、入れても入れなくても良いものをオプションと言います。いまの材料である見つけかたは、オプションの一つです。
LOOKUP系関数とXLOOKUP関数と
XLOOKUP関数は、範囲が横並びでも機能しました。同じ事をVLOOKUP関数でやってみましょう。
エラーが発生しました。#N/Aは、見つからないという意味のエラーでした。ここで、VLOOKUP関数の材料の3番目を思い出してみましょう。
そうです。VLOOKUP関数の3番目の材料は、縦並びで何番目というものでした。ここで列とは縦並びを示します。VLOOKUP関数はそもそも、縦並びから探すという機能を持つ、言いかたを換えれば、そういう機能しか持たない関数なのです。
では横並びの表からは探せないかと言えば、そうではありません。実はExcelには、
HLOOKUP関数
なる関数があります。HLOOKUPのHはHorizontal（水平）を表します。やってみましょう。
ちゃんと機能していますね。
HLOOKUP関数は、VLOOKUP関数に比べておそらく知名度はかなり低いと思われます。表というのは基本的に、それぞれの性質を表したデータが縦に並べてあるからです。もしデータが横に並んでいて件数が多くなってくると、横にスクロールをしなくてはならず、それは操作もやりにくいですね。Excelのような表計算アプリケーションのシートは、縦に並んでいる数のほうが遥かに大きくなっています。
とは言え、横並びに構成する表は使わないとは言えません。だからこそ、HLOOKUP関数が用意されている訳です。しかし、やりたい事のしくみ、つまりルックアップの機能は同じなのに、範囲の並び方向で関数を変えなくてはならないというのは、いかにも非効率的です。
考えかたとしては、たとえば、関数に入れる材料を増やして、"H"なら横並び、"V"なら縦並びに対応させる、のような手もあります。そうすれば1つの関数で対応できます。関数名は、方向を問わないLOOKUP関数としましょうか……と言いたいですが、これだと材料が1つ増えて煩雑になりますし、実はそもそも、LOOKUP関数は既に存在します。
これも、初めて見たかたが多いかも知れません。LOOKUP関数というのは、指定する範囲の幅が1の時に使う関数です。一見、XLOOKUP関数に似ていますが、見に行く範囲が昇順に並んでいなければならないなどの制限があります。VLOOKUP関数にあった、見つけかたの材料がありません。
特に文字列の検索では昇順に並んでいる事はそれほど無いですし、多くは縦並びから探すので、VLOOKUP関数を使えば済みます。したがってLOOKUP関数は、あまり使われません。
兼ね備えたXLOOKUP関数
これらのLOOKUP関数の特徴を踏まえたXLOOKUP関数は、
見つけに行く範囲と取りに行く範囲を分ける
範囲の方向を問わない
完全一致が既定
などの性質を備えています。これは、色々のシチュエーションがXLOOKUP関数で事足りる、つまり汎用性が高く、更に、数式を簡潔に書けるというメリットに繋がります。
見つからない場合
これまでの説明では、所望のもの、ここでは寿司ネタが、
見つけに行く範囲に入っている
のが前提でした。では、それが無い場合はどうでしょうか。実務では、対象を見つけに行くのと同時に、見つからない際の処理が必要な事があります。たとえば、見つけに行く範囲が商品一覧で、そこに新製品を登録しているとします。売上表を作る時にそこを見に行くが、商品一覧に登録ができていなければ、見つからない旨の表示をさせたい、といったシチュエーションです。
上の図は、最初のネタを玉子にしたものです。単価に#N/Aエラーが入っています。これは見つからなかったという意味でしたね。ここを、見つからなかったと明示したいです。
今度はエラーが出ていません。見つかりませんと表示されています。数式を見てみましょう。
最初、XLOOKUP関数に入れた材料は3つでしたが、上の図では4つ目の材料を入れています。関数の下に説明が書いてあります。4つ目は、[見つからない場合]とあります。これが、見つからない場合に何を返すか、という材料なのです。これであれば、そのままでは不明瞭なエラーを避けて、他のユーザーにとっても解りやすいメッセージを表示させられます。ちなみに、材料が角括弧で括ってあるのは、オプションであるのを示します。だから最初は、3つの材料だけで機能した訳です。
面倒なエラー処理
XLOOKUP関数に対し、VLOOKUP関数などでは、このような便利な材料はありません。オプション含めて、入れられる材料は4つです。では、先ほどと同じように、見つからない場合のエラー処理はどうするのでしょうか。
上の図のように書きます。IFERROR関数なる関数が入っています。IFERROR関数というのはこのようなしくみです。
=IFERROR(エラーかどうかを判定したいもの, エラーの場合に返すもの)関数とは、材料を入れて結果を返す機能です。そしてIFERROR関数は、エラーを返し得るものを材料として渡して、それがエラーである時に結果を返す機能を持っているのです。これを踏まえて再び、先ほどの数式を見てみましょう。
IFERROR関数の材料には式を入れる事が出来ます。つまり関数そのものを材料に入れられる訳です。であれば、VLOOKUP関数をIFERROR関数で挟むようなかたちにすれば、VLOOKUP関数の結果がエラーであれば、その結果を判定して、"見つかりません"の文字をIFERROR関数が返してくれるという寸法です。そして、VLOOKUP関数が上手く見つけてエラー以外の結果を返してくれれば、IFERROR関数は、それをそのまま返します。つまり、単なるVLOOKUP関数が成功した場合と同じとなります。
関数を材料として入れるのは、前回で紹介した、INDEX関数とMATCH関数との組み合わせもそうでした。最終的に、MATCH関数2つをINDEX関数で挟む形にしたのですね。そこでの説明でも書いたように、数式を数式で挟んでいく、つまり入れ子にすると、途端に読みにくくなります（可読性の低下）。
XLOOKUP関数は、このエラー処理部分まで標準の機能として搭載しています。ですから、IFERROR関数を使った処理も必要無く、簡潔に数式を書けるのです。
ちなみに、以前のExcelにはIFERROR関数はありませんでした。ISERRORなる、エラーを判定するだけの関数しか無く、それでエラー判定して、更にそれを、IF関数という、場合分けの関数を使って処理していたのです。
=IF(
ISERROR(VLOOKUP(B2,$G$2:$J$7,2,0)),
"見つかりません",
VLOOKUP(B2,$G$2:$J$7,2,0)
)まずVLOOKUP関数を機能させ、それがエラーを返すかをISERROR関数で判定して、エラーならメッセージを出し、エラーで無ければ再びVLOOKUP関数の結果を返す、という構造です。いやあ、これは面倒ですね。
XLOOKUP関数の注意点
ここから、XLOOKUP関数で注意しておく所を説明します。
見つけに行く範囲の幅は1
VLOOKUP関数では、指定する範囲の幅は、2以上が多いです。ネタと単価のように、横にあるものを取り出すのが主な目的だからです。対してXLOOKUP関数は、見つけに行く範囲と取りに行く範囲が分かれているので、見つけに行く範囲の幅は1でなくてはなりません。
取りに行く範囲をずらさない
XLOOKUP関数は、2つの範囲を別々に指定します。VLOOKUP関数などは1つの範囲を対象にしますが、XLOOKUP関数では、次のような事が起こり得ます。
単価が0になっています。変ですね。範囲の指定がおかしければエラーが出るはず。数式を見てみましょう。
数式を選択すると、指定した範囲、つまり参照範囲が表示されます。赤枠が見つけに行く範囲、紫枠が取りに行く範囲です。これが、ずれてしまっています。要するに、取りに行く範囲の起点が下がった訳です。うには8番目にありますが、210を起点にした8番目は空白です。ルックアップ系関数では、取りに行く範囲に空白が入っていたら0を返すので、このようになった次第。
範囲の長さを揃える
見つけに行く範囲と取りに行く範囲は、長さを同じにします。たとえば、ネタの縦並びが8個のセルからなっている場合、取りに行く範囲の長さも8に揃えます。
上の図では、それぞれの範囲の長さが違います。結果はエラーとなっています。
余談ですが、
範囲がずれても機能する
範囲の長さが合えば機能する
これらを踏まえつつ発想を転換すると、次のような事もできます。
上の図のように、ネタの一覧が入っている表と、単価の一覧が入っている表が分かれていても、その並びが対応していれば、ちゃんと単価を取得してくれます。もちろん、同じ位置のものを取り出してくるというルックアップ機能を期待しているのですから、敢えてこういう事をする意味はそんなにありません。あくまで、しくみ的にはこのような働きも可能となる、くらいの話です。
互換性
XLOOKUP関数は、2020年頃に出てきた関数です。Microsoft Officeは、新しい機能が実装された場合、それより古いバージョンにそれが適用されるとは限りません。関数はむしろ、実装されないのが普通です。したがって、XLOOKUP関数は、Excel 2019までのバージョンでは使用する事ができません。これは、実務上の問題を発生させる場合があります。
実務では、アプリケーションの選定において、ライセンスやパッケージの購入などの問題があり、ユーザー全体が同一のバージョンを使用できない事があります。組織の規模が大きくなるとなおさらです。
個人使用に留まるのが判っているのであれば躊躇なく使えますが、
他部署や顧客などに配布・共有する可能性がある
使用ユーザーのアプリケーションバージョンが不明な場合がある
このようなシナリオが想定される場合、使用には慎重になるべきです。
これは実話に基づいたものですが…。
リモートで別端末に乗り込んで、集計をおこなうという業務がありました。いつも使用している端末で作ったExcelファイルをリモート先にコピーして集計作業をおこなった所、結果が変です。何かおかしいと思って調べた所、リモート先にインストールされていたのはExcel 2016であり、いつも使っていた端末でのバージョンは、Microsoft 365（常に最新バージョン）だったのです。使ったExcelファイルでは、XLOOKUP関数が入っていたのでした…。
リモート先が古めの端末であるのを把握していなかったが故の事象です。このようなケースは実務的に、大いに起こり得るシナリオです。
まとめ
前々回から今回にわたって、VLOOKUP関数、INDEX関数とMATCH関数の組み合わせ、そしてXLOOKUP関数の説明をおこなってきました。基本的なルックアップのしくみから、複数の関数の組み合わせ、最新の関数の使いかたと、順序立てて、それなりに丁寧に説明できたのではないかと思います。
最新の関数であるXLOOKUP関数は、簡潔かつ汎用性高く使えます。その意味では、VLOOKUP関数などを知らなくても構いません。実際にそう主張する人もあります。けれども、上のほうでも書いたように、Excelにはバージョンによる実装の違いがあり、実務上ではその違いがトラブルを生じさせかねません。もちろん、使用ユーザーのバージョンがはっきりしているのであれば、大いに使えば良いです。私自身も、それが判っている範囲では、以前に作ったExcelファイルの数式は、ほとんどXLOOKUP関数に入れ替えをおこなっています。要は使い所ですが、使い所を把握して使い分けるには、何がどう使えるかを解っていなくてはなりません。私が書いた記事によって、その助けになれば幸いです。
参考資料
スピルの説明にはまだ早い
この記事が気に入ったらサポートをしてみませんか？