正式リリースされたExcelのXLOOKUP関数の動きを確認してみた

こんにちは、小澤です。

昨年、Microsoft Excelの中でも有名(?)な関数上位に入るであろうVLOOKUPとHLOOKUP関数双方の機能をあわせもつXLOOLUP関数が発表されました。 このXLOOKUP関数が現地時間2020/01/30にリリースされたOffice 365の更新プログラムにて正式リリースされたようです。

特に誰に言われたわけでもないのですが、何となく書けと言われているような気がしたので、 今回は、このXLOOKUP関数の動きを確認してみることにしましたｗ

• 参考 : Developers.IO 2019 Tokyoで「全部Excelだけで実現しようとして後悔するデータ分析」というタイトルで発表してきました

XLOOKUP関数の動きを見てみる

XLOOKUP関数はVLOOKUP関数(やHLOOKUP関数)と同様、何らかの値をキーにしてデータを検索し取得する関数です。

公式ドキュメントによるとその書式は以下のようになっています。

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

必須の引数は以下のようになります。

• lookup_value : 検索対象となる値
• lookuup_array : 検索を行う範囲
• return_array : 結果として返す値

VLOOKUP関数と異なる点として、結果して返す値を検索範囲から右に何列目か(HLOOKUPの何行目か)ではなく、 セルの範囲を指定する形式になっています。

実際に以下のようなデータで使ってみましょう。

ID列の値を検索対象として、商品名を取得するには以下のようにXLOOKUP関数を利用します。

=XLOOKUP(A11, $A$2:$A$6, $B$2:$B$6)

これでIDがA2:A6の範囲が1となる行を検索して、B2:B6列の範囲で同じ行の値を取得しています。

同様に以下のようなデータに対して利用すると、VLOOKUPではなくHLOOKUP相当の結果が得られます。

=XLOOKUP(H12, $I$1:$M$1, $I$2:$M$2)

オプション引数の指定

XLOOKUP関数には3つのオプションに引数があります。

オプション引数に存在する4つ目の引数if_not_foundの値を指定した場合、 ヒットしなかった場合に返す値を指定可能です。

=XLOOKUP(A13, $A$2:$A$6, $B$2:$B$6, "そんな商品ないよ！！")

これにより、従来行っていたIFERROR関数の中にVLOOKUP関数を入れるといった処理が必要なくなります。

また、XLOOKUP関数は、VLOOKUP/HLOOKUP関数と異なり、デフォルトが完全一致となるため、 オプションとなる引数でmatch_mode引数での指定が無いかぎり、近似値を取得しません。 この引数はデフォルトの値が0になっており、その指定の際は完全一致扱いとなります。 検索対象が数値の場合、-1または1が指定可能でそれぞれの動きは以下のようになります。

• -1 : その数値よりも小さい範囲で最も近いもの
• 1 : その数値よりも大きい範囲で最も近いもの

先のほどのデータで価格を以下のように「-1」を指定して存在しないID:6を検索すると、 デフォルト値である「0」ではなく6に最もと近いより小さな値であるID:5の結果が返ってきます。

=XLOOKUP(A13, $A$2:$A$6, $C$2:$C$6, 0, -1)

検索対象が文字列ある場合は、この引数に2を指定することで、「*」や「?」などのワイルドカードを使った検索が可能になります。

最後のsearch_mode引数は検索の仕方を指定します。 指定できる値は2, 1, -1, 2のいずれかとなっており、それぞれ以下のような動きをします。

• 1 : 上から順に検索していきヒットした段階でその値を返す
• -1 : 下から順に検索していきヒットした段階でその値を返す
• 2 : データが昇順で並べられている前提で二分探索を行う
• -2 : データが降順で並べられている前提で二分探索を行う

この引数を指定することはあまりないかもしれませんが、検索対象となる値に重複がある場合、1と-1どちらを指定するかで結果が異なります。

また、データ量が多くソート済みの場合、二分探索を行うことで処理時間が早くなることが想定されます。

テーブルに対して利用する

データがテーブルになっている場合、他の関数と同様にテーブル名や列名を使って範囲を指定できます。 先ほどのデータが「niino」という名前でテーブル化されている場合以下のように指定可能です。

=XLOOKUP(A11, niino[ID], niino[商品])

これによってデータがテーブル化されている場合は、他の関数と同様データの変化に対して柔軟に対応可能な記述ができます。

ところで、従来のVLOOKUP関数では検索対象から見て右に何列目を指定して値を取得していました(HLOOKUPの場合何行目か)。 これらの関数は検索対象よりも左の列(あるいは上の行)を返す値として指定できませんでした。 例えば、以下のようなテーブルではIDを検索対象として商品名を取得するといったことができません。

XLOOKUPでは、列番号ではなく範囲で返す値を指定するため、この形式でも利用可能です。 位置が異なるだけで列名が同じであれば、テーブルでの範囲指定に関しては書き換える必要はありません。 また、セルの範囲での指定でも以下のように左側の列に戻って指定できます。

=XLOOKUP(A12,$C$2:$C$6,$A$2:$A$6)

何らかの理由でキーとなる値を一番左側の列に持ってこれないデータでもそのまま利用できるのは便利ですね。

おわりに

今回は、Microsoft Excelに搭載された新しい関数であるXLOOKUPを動かしてみてその挙動を確認しました。 昔からある関数であるVLOOKUP/HLOOKUPと比較して、便利に使えるようになっており時代の進歩を感じさせてくれます。

参考

• XLOOKUP 関数 - Office サポート