XLOOKUP関数のすごいところ
2019年8月28日は、全Excelユーザーにとって忘れられない日になりました。この日、Microsoftが「今度ExcelにXLOOKUP関数を搭載するよ～」と、公式にアナウンスしたからです。その前から「スピル」や「動的配列数式」などの搭載を発表し、さらに「FILTER関数」や「SORT関数」など複数のワークシート関数も追加され、今までのExcelでは不可能だった動作が可能になります。
みんな大好きVLOOKUP関数の超拡張版ということで、多くのサイトがこの新しいXLOOKUP関数についての記事を掲載しました。しかし、(私が見た限り)最も重要なことが解説されていません。XLOOKUP関数の詳細に関しては「XLOOKUP関数」を見てもらうとして、ここでは、XLOOKUP関数は何がすごいのかを簡単にご紹介します。なお、本稿では書けなかったもっとスゴイところを動画で公開しています。ぜひ、こちらもご覧ください。Youtubeでは、ほかにもたくさんの動画を公開しています。チャンネル登録をお忘れなく！
Office TANAKAチャンネル
検索範囲と結果の範囲を別々に指定できる
XLOOKUP関数の紹介では、やたらとこれがピックアップされます。今までのVLOOKUP関数では、表の左端を検索して右側を返すことしかできませんでした。それが、新しいXLOOKUP関数では、表の右側を検索して左側を返すことが可能です。
下図が、今までのVLOOKUP関数。
下図のように、右側を検索して左側を返すことは、できませんでした。
新しいXLOOKUP関数だったら、それが可能です。
こうした特徴から、
このように、XLOOKUP関数は便利です。これでもう、MATCH関数 + INDEX関数を使う必要はなくなりました。
のように言われるのですが、いやいや、同じことは今までもMATCH関数 + INDEX関数で可能でしたから。新しく「XMATCH関数」も追加されましたので、今までよりも複雑な検索も可能です。今までと同じことを、別の関数でできるようになったからといって、諸手を挙げて喜ぶような話ではありません。そうじゃないんです。実務の現場で、このXLOOKUP関数が活躍するのは、その使い方じゃないんです。
もうチマチマやらなくていい
下図のような表で考えてみましょう。
今までのVLOOKUP関数だったら、次のように操作します。まず、セルG2にVLOOKUP関数を1つ入力します。
この、セルG2に入力したVLOOKUP関数を、右方向へ、セルI2までコピーします。
すると、予期しない結果になります。
これは、セルG2の数式が、次のようにコピーされたからです。
「検索値」が入力されているセルF2や、「検索範囲」のセル範囲A2:D5は、数式のコピーでアドレスが変わっては困ります。だから絶対参照にします。それはいいのですけど、問題は「列位置」を表す"2"です。これは単なる数値です。セルのアドレスではありません。ですから、数式のコピーによって、自動的に変化はしません。だから、コピー先の各セルでも、すべて同じように2列目を参照してしまいました。これ、理想は下図のようになって欲しいんです。
どうですか？思い当たる節があるでしょう。これ、社会人でしたら、誰もが一度は経験あるはずです。しかたないので、コピーした数式をひとつずつ開いて、列位置をチマチマと手で修正します。実務で扱う表は巨大です。こんな3つや4つのセルで済む仕事ばかりではありません。でも、その数だけチマチマやらなければなりません。面倒くさいですね。もちろん、COLUMN関数を使うという手もありますけど、にしても面倒くさいです。
新しいXLOOKUP関数を使うと、もうこんなチマチマをやらないでいいんです。
上図のように、セルG2に1つだけXLOOKUP関数を入れれば、下図のようになります。
スピってます！XLOOKUP関数はスピるんです。もうコピーしなくていいんです。アクティブセルのセルI2に入力されている数式を、数式バーで確認してください。同じ数式が入っています。ちょっと色がグレイで表示されているのは、セルH2とセルI2は動的配列数式で値を書き込まれたゴーストだからです。数式をコピーしませんから、絶対参照にする必要もありません。
新しいXLOOKUP関数のメリットは「もう、MATCH関数 + INDEX関数を使う必要はないんですよ～」ということではありません。もちろん、それも多少はありますけど。ユーザーに伝えるべきは、そこじゃないです。こっちの「もう、数式のコピーでチマチマをやらなくていいんですよ～」です。
列単位の参照が簡単にできる
それだけじゃありません。XLOOKUP関数を使うと、今まではできなかったこと、できるけどメチャクチャ難しかったことが、簡単に実現できるんです。これも、実務では"あるある"です。
下図のような表があったとします。
左側のA列からD列までが、いわゆる"元データ"です。基幹システムからダウンロードしたり、CSVを開いたり、作業をするための元データです。実務は複雑です。最初の元データだけで済むような単純作業ばかりではありません。この元データを"出力データ"として加工することが多いです。たとえば、右側のF列からH列みたく、不要な列を削除し、決められた順番に列を置き換えるような。ここでは、結果などが見やすいように同じシートでやりますが、実際には別シートに作ることが多いです。
ここでのポイントは「新しいXLOOKUP関数は、1つでVLOOKUP関数とHLOOKUP関数の働きをする」ということです。これも、XLOOKUP関数の紹介では軽視されていますね。しかたありません。HLOOKUP関数なんて、ほとんど使われていないからです。ちなみにHLOOKUP関数は、検索範囲の先頭行を探して、見つかった列を返す関数です。
こんなこと、実務ではめったにやりません。でも！思い出してください。XLOOKUP関数はスピるんです！やってみましょう。
XLOOKUP関数だったら、先頭行のタイトルを指定するだけで、その列全体を参照できるんです。素晴らしい。みなさんの拍手が聞こえるようです。あとは、セルF2を右方向へ、セルG2とセルH2にコピーすれば終わりです。ただ、ここでポイントがひとつあります。本来XLOOKUP関数では「検索範囲」などのアドレスを絶対参照で指定する必要はありませんが、ここは絶対参照にしておかないと失敗します。
実務では、元データの列位置を変えて、出力用の表を作ることがよくあります。そんなときも、新しいXLOOKUP関数で一発です。このように、XLOOKUP関数は「MATCH関数 + INDEX関数の代わりになる」というだけではありません。だったらMATCH関数 + INDEX関数で十分です。そうではなく、数式のコピーによる列位置のチマチマ変更が不要になったり、今までの関数だけでは超難しかった列単位での参照が簡単になります。これからのExcelは、XLOOKUP関数によって、使い方や考え方が大きく変わります。