Excel新関数XLOOKUP関数の底力

8月29日、朝からExcelクラスタの皆さんはExcelに新しい関数が追加されたことで持ち切りでした。
XLOOKUP関数というLOOKUP関数シリーズの最終兵器ともいえる関数が発表になったのです。

XLOOKUP関数のキホン

今までのVLOOKUP関数は、一覧表の最左列にインデックスがないと検索できないので、次のような一覧表から値を参照するにはINDEX関数とMATCH関数の組み合わせというとても面倒な方法をとることになっていました。

このXLOOKUP関数はそのINDEX関数とMATCH関数の組み合わせを1関数でできるようになったものです。

なぜ今までなかったのか、逆に疑問ですらあります。

XLOOKUP関数の書式

XLOOKUP関数の書式は次の通りです。

=XLOOKUP(検索値,検索範囲,出力する値の範囲)

上の例だと、みかんを上の表のB列から探して、その対応するA列を出すという日本語になりますね。
VLOOKUP関数であった、列番号という考え方はXLOOKUP関数にはありません。
範囲と範囲で一致しているのが何番目かで合わせていきます。

実はXLOOKUP関数には、3つめの引数の後に入れるものもあります。

=XLOOKUP(検索値,検索範囲,出力する値の範囲,一致モード)

なんでしょう、この一致モードって。
入れられるものが、0、-1、1、2だそうです。
0、-1、1は従来のMATCH関数にあった、完全一致と、小さい順での近似値、大きい順での近似値。
ここまではわかります。
2のワイルドカードって?となるわけです。
なんと、XLOOKUP関数は、文字の一部だけで検索可能なのです!!
COUNTIF関数もできるのですが、「佐*」と指定すれば「佐藤」も「佐々木」も検索してくれます。
これをワイルドカードと呼びます。
「あ」なんとかという文字を検索するときは、「あ*」と指定します。
なんとか「月」という文字を指定するのは「*月」と指定します。
「*」はなんとかという文字列という意味で、そこに文字がなくても検索されます。「*月」は「月」も検索できます。

「?」でも指定でき、「?」は何かの1文字です。だから「??号」は2文字「号」なので、「10号」は検索しますが「1号」は検索しません。

あと「~」というのも使えるそうですが、今のところ何ができるのかわかっていません。
ここまでが一致モードです。
そしてさらに次の引数も指定できます。

=XLOOKUP(検索値,検索範囲,出力する値の範囲,一致モード,検索モード)

この検索モードは、近似値一致の時に昇順か降順を決めるそうです。これは今までのVLOOKUP関数にはない概念ですね。
バイナリというのはイマイチわかってません。文字の時に五十音で探すとかそんな感じなのかもしれませんね。

XLOOKUP関数の底力

さて、では実際に世の中の一覧表の中で、最左列にインデックスがない表がどれだけあるかと考えると、実はあまりないと思うし、そういう表にすべきではないのかなと個人的には思うのです。でもやむを得ずそうなってしまうケースもあって、そこではXLOOKUP関数はひとつのお役立ち関数になるのかなと思ったのです。
でも、私は、本当のポテンシャルは、第四引数だと思いました!

昇順降順の概念が別引数で管理されるようになった

今までのMATCH関数では、昇順にリストを並べ替えておいてより小さい方に近似値一致、もしくは降順に並べ替えていて大きい方に近似値一致でした。
VLOOKUP関数は、昇順になっていなければならず、降順に並んでいるリストは使えませんでした。
XLOOKUP関数はその昇順か降順かという概念が指定しなくてもよくなりました。
小さい方に一致か大きい方に一致かを指定するだけでもよくなったのです。
これは実はものすごいメリットが隠れています。
例えば、点数20の人はA、40の人はB、、、一番高いレベルの人はEとする場合、従来リストとしては次のように指定する必要がありました。

これってちょっと嫌じゃないですか?一番上のレベルは100にしたいじゃないですか。
そんな時は、次のようにできます。

第四引数を1にすることで、昇順の表でありながら、上側の近似値一致になるのです。
これは私にとってはものすごくインパクトがあって、VLOOKUP関数のインデックス値を累計値で設定することがあるのですが、累計値の始まりは0ではなく1個目の数値なので、そこでLOOKUPできるというのは様々なケースのレベル分けでものすごく有効に使えるのです。
伝わりますか?この便利さ。
そして、それならこのリストを並べ替えしたらどうなんだということで、降順で並べ替えます。

なんてことでしょう。
昇順、降順が関係ないようです。
でもなんか信じられないのでもうちょっとここは検証してみます。
↑もしかしたら間違った情報かも知れないという逃げです。

ワイルドカード指定でとんでもないことができる

第四引数のもう一つの使いかた、ワイルドカードについてです。
今までできなかったこんなことができるんですね。

愛称からその人の漢字を特定できます。
1行目のA列にフルネームが漢字で入っています。それに対してB列に次の関数でカタカナのフリガナを表示しています。

=PHONETIC(A1)

そして、9行目から3人の相性が入力されています。そのB列にはまた次の関数でカタカナのフリガナから、「ちゃん」「さん」をなくしています。

=SUBSTITUTE(SUBSTITUTE(PHONETIC(A9),”チャン”,””),”サン”,””)

C列には、次のXLOOKUP関数が入って、上の表からフルネームの漢字が出てきます。

=XLOOKUP(“*”&B91&”*”,B1:B5,A1:A5,2)

この例では、実務で何に使おうかという感じだと思いますが、例えば製品番号の一部に商品が属してるカテゴリコードが埋め込まれていて、そのカテゴリでLOOKUPするとかということができるわけです。
もうちょっと面白い使いかたもできそうな感じがします。

伝わりますか?この便利さ。

XLOOKUP関数はいつから使えるの?

XLOOKUP関数は残念ながらExcelユーザー全員が使えるわけではありません。

私の状況ですが、Office2016でOffice Insiderに登録している状況で使えています。
ただこれも浸透していくまで時間がかかるようなので、=XLとセルに入力したらXLOOKUP関数が出てきたら使える状態になったということです。
Office InsiderではないOffice365では使えませんでした。

一般には今年中に使えるようになるとのことです。

XMATCH関数

XLOOKUP関数と同時にXMATCH関数もリリースになりました。
書式は次の通りです。

=XMATCH(検索値,検索範囲,一致モード,検索モード)

検索結果のセルが何番目かを出します。
おそらく単独で使うのではなく、INDEX関数と組み合わせるようなことも考えられますが、それならXLOOKUP関数を使えばいいのでそれは意味ないかなと。
じゃ何に使うんだってことになりますが、ワイルドカードで検索したセル位置をVBAで参照してループ回数に使うとか、そんな使いかたが想定されます。

応用

今のところ便利だなと思った実例は以上の感じなのですが、XLOOKUP関数は最新の関数なので、FILTER関数などのスピル系関数とも、そしてスピル機能とも共存する関数です。
それらと組み合わせたり、データの種類機能で参照できるFIELDVALUE関数と組み合わせることでもしかしたら何か生まれるかもしれません。
また、条件付き書式、入力規則のリストともシナジーがありそうな気がしています。
場合によってはVLOOKUP関数とXLOOKUP関数との組み合わせ式も有り得るでしょうし、第四引数をIF関数で切り替えるような使いかたも面白そうです。
そのへんはやわらか頭で考えていきたいですね。

サトウヨシヒロ

Excelを中心としたMicrosoft Officeの魔法使い。
仙台市在住のフリーランスのパソコンインストラクターです。
1969年1月生まれ。
Excelを黎明期から30年近く使っています。Wordも使えます。
出版社様と一緒にExcelの情報発信もしています。
最近は羊に夢中です。
2019年はRPAと業務効率化を勉強しています。

Twitterでも業務で使える便利なOffice技をめっちゃつぶやくツイッタラーです。@yosatonet

コメントを残す

メールアドレスが公開されることはありません。