ITツールの機能紹介-XLOOKUP-

つ～じ～です。ウマ娘映画でテンション上がっちゃって5回くらい観に行きました、今度はMX4Dで観てみたいなとか思ってます。4DXとMX4Dでどう違うのか自分で確かめたい。

さて、いつだかWEB広告で転職の広告が流れてきまして、その中で

『「VLOOKUP、使えますか!?」「使えます！！(ｷﾘｯ)」
転職必要なスキルとテクを貴方にお教えうんたらかんたら～』

みたいなのを見たことがあります。転職時のアピールにも有効なくらい貴重みたいですね、VLOOKUP。

VLOOKUPと言えば、ExcelやSpreadSheetにおいて抽出機能として重宝する関数なのですが、最近弊社内のとある対応で抽出機能が必要になって調査したところ、なんと2020年にVLOOKUPの上位互換であるXLOOKUPが作られたというじゃありませんか。その検索時の関連動画には
『まだVLOOKUP使ってるんですか!?』
なんて煽り文のサムネイルまで出てくる始末。

自分もさりとて現代を生きる開発者の端くれ、これは勉強せねばと思い、この度本記事にてご紹介しようと思った次第です。

※本記事ではSpreadSheetを基準に解説をしております。SpreadSheetとExcelでは微妙に関数の使い方が変わるため、予めご了承ください。

※本記事で表示している表は本記事の為に作成したダミーデータです。業務で使用したものではございません。

そもそもVLOOKUPって何？

本記事を読まれる方が皆、開発業務に精通しているというわけでもないと思われるため、まずはVLOOKUPから振り返りたいと思います。

VLOOKUPとは、抽出対象の表を縦方向に検索し、検索対象のデータを発見したら、その行の別の列のデータを返り値として抽出する関数です。

=VLOOKUP（検索値, 検索範囲, 列番号, [検索の型]）

『検索範囲』で指定した領域の中に『検索値』に一致するものがあるかを検索し、ヒットしたら、検索範囲内のヒットした行の左から『列番号』列目の位置（「1」なら検索範囲の一番左の列、「2」なら左から2列目、「3」なら左から3列目…）の値を返す。これがVLOOKUPです。表からデータを抽出するのに重宝します。

※［検索の型］はTRUEorFALSEで指定しますが、省略可能なオプションです。省略するとTRUEになります。
［検索の型］がTRUEの場合は『［検索値］に一致するデータがヒットしない時に［検索値］以下の最大値を一致する値として取り出す』という近似値検索となります。
完全一致検索にしたい場合は必ず［検索の型］をFALSEにしましょう。

長年便利関数として重宝したVLOOKUPですが、以下のような欠点もございます。

抽出対象は「検索がヒットした行」からしか抽出できない
複数行の検索が難しい
必ず範囲列の一番左の列で検索しないといけない
検索対象が見つからない時は「#N/A」になってしまう

上記の欠点を補っているのがXLOOKUPとなります。

VLOOKUPとXLOOKUPの違い

XLOOKUPもVLOOKUPと同じく抽出関数なのですが、まず以下のような指定の違いがございます。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

機能としてはVLOOKUPと同じなので説明は省きますが、使い方に以下のような違いがございます。

①抽出対象に『配列』を指定可能

XLOOKUPでは「検索範囲」と「戻り配列」を別に指定できるため、VLOOKUPのように「ｎ列目(列番号)」を指定する必要はありません。

例えば「検索範囲」で検索を行い「6行目でヒット」となった場合は、「戻り配列の6行目」を返り値として返すことができます。つまり検索範囲と違う位置にある表から値を返すことも可能とです。これで返り値指定の自由度が高まります。

※上記の仕様の都合上、「戻り配列」は必ず「検索範囲」と同サイズ(同じ行数であればOK)の範囲を指定する必要があります。サイズが違う範囲を指定するとエラーになります。

②検索値や検索範囲を複数列指定にすることが可能

XLOOKUPは以下のような記述をすることで複数行の検索が可能となります。

=XLOOKUP($C4&$D4,ARRAYFORMULA($J$4:$J$9&$K$4:$K$9),$L$13:$M$18,“なし”)

まず「$C4&$D4」という書き方をすることで複数の検索値を結合します（＆は結合子）。上記の場合は
「DAMMY01745」＋「商品」＝「DAMMY01745商品」
のような検索値になります。

そして検索範囲は「ARRAYFORMULA($J$4:$J$9&$K$4:$K$9)」のように記述します。
「$J$4:$J$9」の列と「$K$4:$K$9」の列を＆で結合し、ARRAYFORMULA関数に組み込むことで、配列数式(複数列の結合式)を検索範囲として処理できます。

※ARRAYFORMULA関数は厳密には「配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができる関数」とされているもので少しわかりづらいのですが、本稿では「列を結合して範囲として扱うためのもの」レベルでご理解いただけたら幸いです。
なおExcelにはARRAYFORMULA関数が存在しませんが、ARRAYFORMULA関数を使わずとも「$J$4:$J$9&$K$4:$K$9」だけで上記の目的を達成することができます。

上記のような検索条件にすると、

「$J$4:$J$9」の列と「$K$4:$K$9」の列を結合したら『DAMMY01745商品』になるデータ

のような条件で検索を実行してくれます。現に上記の添付画像では「$K$4:$K$9」の列が『同梱物』のデータはヒットしないようになっております。

なお、上記の列結合処理は隣接した列でなくても可能であるため、汎用性が高いです。
（A列とC列を結合、みたいなことが可能）

③戻り配列に複数列を指定可能

XLOOKUPはスピルに対応しており、隣接する値を一気に取り出せます。

※スピル：数式が配列内の複数の値を返す場合、返り値が隣接するセルに自動的に入力される機能

上記の場合、戻り配列に2列分の範囲を指定しているため、「商品名」「単価」のデータをいっぺんに取得して入力してくれています。上記の画像の場合、「商品名」の方に2列分返す関数を入力しているため、「単価」の列には関数を書いておりません。

VLOOKUPの場合は関数コピーのたびに「列番号」の数値を手打ちで直す必要がございましたが、XLOOKUPの場合はそうした作業も不要です。関数1つで複数列の入力を行うことができます。

なお、VLOOKUPでは「検索範囲の一番左の列を指定しないといけない」という制約がございましたが、XLOOKUPにはそれもありません。好きな位置の列を指定可能です。

④[見つからない場合]の指定が可能

VLOOKUPの場合はIFERROR関数と組み合わせるなどの使い方をしないと、検索対象が存在しない場合に「#N/A」となってしまいますが、

XLOOKUPの場合はオプションに[見つからない場合]の指定があるため、ここで指定しておけば見た目がすっきりします。

他にも[一致モード]、[検索モード]を指定するオプションがあるのですが、未指定でも特に問題無い部分であるため本稿では割愛いたします。

XLOOKUPの欠点としては「Excel 2013以前の旧バージョンでは利用できない」という点がございます。ここまで古い媒体を使用することは基本無いとは思いますが、PJによっては有り得るので予めご了承ください。

また余談ですが、「抽出対象の表を縦方向に検索し、検索対象のデータを発見したら、その行の別のデータを返り値として抽出するVLOOKUP」の他に、「抽出対象の表を横方向に検索し、検索対象のデータを発見したら、その列の別の行のデータを返り値として抽出するHLOOKUP」なんて関数もございます。本稿では説明を省きますが、興味のある方は調べてみると良いでしょう。

今後も技術革新に取り残されないように勉強頑張って参りたいと思います。今回はこれにて。