ひとつで２役、XLOOKUP関数 Excel2021 Microsoft 365
XLOOKUP関数は、VLOOKUP関数を使う上で、不便だったことを解決した新しい関数です。
ひとつで２役、XLOOKUP関数 関数 07b回
XLOOKUP関数とは
さて今度は VLOOKUP 関数の代わりに Xlookup 関数を使って売上金額を出してみたいと思います。
XLOOKUP関数は縦方向にデータを検索するVLOOKUP関数と、横方向にデータを検索するHLOOKUP関数の両方を備える関数です。
さらに、スピル対応の関数でもあります。スピルはVLOOKUP関数内でも利用することはできましたが、どこが違うのでしょうか？
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。
一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。 *省略した場合、XLOOKUPは検索範囲に空白のセルを返します。
（サンプルファイルは、こちらから サンプルデータ）
XLOOKUP関数の構文
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
| 検索値 | 参照する値、セル |
|---|---|
| 検索範囲 | 検索する配列または範囲 |
| 戻り範囲 | 返す配列または範囲 |
| 見つからない場合 | 見つからない場合は、指定したテキストを返す。 |
引数、[見つからない場合],[一致モード],[検索モード]は省略可能です。
| 一致モード | 一致の種類を指定する。 0 (既定)完全一致。 見つからない場合は、#N/A を返す。 -1 完全一致。 見つからない場合は、次の小さなアイテムを返す。 1 完全一致。 見つからない場合は、次の大きなアイテムを返す。 2 ＊、？、および ～ ワイルドカードを使う。 |
|---|---|
| 検索モード | 検索モードを指定 1 (既定)先頭の項目から検索を実行。 -1 末尾の項目から逆方向に検索を実行。 2 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行 並べ替えられていない場合、無効な結果を返す。 -2 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行 並べ替えられていない場合、無効な結果を返す。 |
XLOOKUP関数で表引き
- G3セルに XLOOKUP関数の数式を入力します。まず引数、検索値は、 E3から E10を範囲指定して=XLOOKUP(E3：E10としてやります。
すると自動的にスピルを感知してE3＃と入力されているのが確認できます。
- 第２引数、検索範囲は、I2からI10ですが、範囲を選択すると、I3＃と入力されます。自動的にスピルを感知します。
- 第３引数、戻り値は、単価J3:J9です。この列は数値が入力されているセルですので、スピルは使われていません。以上で単価を表引きする数式は入力できました。
XLOOKUP関数は、スピルに対応した関数なので、手入力で修正しなくても、スピル対応の数式になります。
- 最後に、売上個数を乗算して、売上金額を出す数式は完成です。
=XLOOKUP(E3#,I3#,J3:J9)*F3#
- G３セルにだけ、数式が入力されており、それ以外は、スピルしているので、グレーアウトで薄く表示されています。
