読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか？
多くの皆さんはVLOOKUP関数と答えるのではないでしょうか？
ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです
まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります
特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです
今回は、XLOOKUP関数同士を組み合わせた例を２つ紹介します
どちらの例も、表を2次元で処理できるので、利用シーンが多そうです
①マトリクス表から２つの条件に合致するものを抽出する
条件を２つ（条件①部門ｘ条件➁四半期間）をリストで指定し、２つの条件に一致する値を抽出する
➁マトリクス表でランク付けをする
事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります
ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした
尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています
但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています！
１．そもそもXLOOKUP関数とは？
２つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう
サンプルファイルでは1シート目の内容です
サンプルの内容は部門毎に対応する部門長を別表から抽出するものです
XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します（事例では北海道を検索値として、部門長の木村を抽出）
但し、VLOOKUP関数で行っていたような
「○○から右に何番目」
といった列数の指定が無く、検索する範囲（サンプルの部門）、戻り範囲（部門長）を指定しするだけです
つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます
引数は以下のような形で指定します
＝XLOOKUP（検索値、検索値範囲、戻り範囲、【省略可】)
２．マトリクス表から２つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります
仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる（値があふれ出す）状態になります
以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です
列方向でスピります
・=XLOOKUP(I3,B3:B9,C3:F9)＊3番目の引数を全範囲
以下が期間を軸に、戻り範囲を表の全範囲にした場合です
行方向でスピります
・=XLOOKUP(I4,C2:F2,C3:F9)＊3番目の引数を全範囲
この２つのスピりの交点が求まれば正解の値です
ですので、以下のように3段階でXLOOKUP関数を組み合わせます
A.部門の検索を行うXLOOKUP関数を作成
B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる
C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲＝全範囲
最終的にはこのようになります
=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))
数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください
３．マトリクス表でランク付けをする
２．で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました
ここで、5番目の引数を登場させます
VLOOKUP関数でも4番目の引数で検索方法を指定しています
FALSE=完全一致
TRUE≒完全一致
XLOOKUP関数では以下のように検索方法を指定できます
0⇒完全一致
-1⇒検索値の次に小さい値
1⇒検索値の次に大きい値
今回使用するの-1、検索値の次に小さい値です
以下の表で言えば、もし売上が30なら30より小さい値の０が該当し、前期比が15％であれば、10%が該当します
つまり、２．で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです
式は以下のようになります
=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)
企業ではABC分析の延長で、２つの変数（例：売上、利益率）で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います
＜まとめ＞
今回は大きく分けて２つの事を解説しました
ⅰ）XLOOKUP関数を行列、双方向で組み合わせる（スピるの組み合わせ）
ⅱ）XLOOKUP関数の検索方法を工夫し、自動でランク付けをする
このⅰ）ⅱ）ができれば、データ集計がとても楽に行えます
ぜひ、有効活用していきましょう！
このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください
尚、XLOOKUP関数が使えるエクセルバージョン（2020年9月19日時点）は限られており、OFFICE365ユーザー向けとなっています
2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります
にほんブログ村
