XLOOKUP関数で複数列のスピル配列検索結果を作成する方法

XLOOKUP関数で複数列のスピル配列を作成する方法

XLOOKUP関数は、Excelの強力な検索ツールの一つであり、特定の条件に基づいてデータを検索し、関連する情報を返します。本記事では、XLOOKUP関数を使用して複数列から成る戻り値をスピル配列として表示する基本的な手順を解説します。

VLOOKUP関数HLOOKUP関数では1つの式で1セル分の戻り値しか生成できません。しかしながらXLOOKUP関数では複数セルの戻り値(スピル配列)を生成することができるのです。

セルH5へ作成した式の戻り値はI5とJ5まで及んでいる

XLOOKUP関数の基本と構文

XLOOKUP関数は、指定された検索値を検索範囲内で見つけ、対応する戻り範囲から値を返す関数です。複数列の戻り値を取得する場合、戻り範囲に複数の列を指定することができます。

以下はXLOOKUP関数の基本構文(必須部分)です。引数はさらに設定することができるのですが、本稿では必須部分のみを解説します。

=XLOOKUP(検索値<1つ>, 検索範囲<1列>, 戻り範囲<1列~複数列>)

複数列のスピル配列を作成する手順

複数列から成る戻り値をスピル配列として表示するには、以下の手順に従います。

1. 検索値の設定(1つのみ)

検索値とは、検索範囲内で見つけたい値です。これは、特定の数値やテキスト、またはセル参照を使用して指定できます。

下のサンプル図ではセルH5で計算式の作成を開始しました。検索値にセルG5を指定しています。「1012」のように直接入力することもできます。ここへ指定できる値は1つのみです。

2. 検索範囲の指定(1列・線形配列のみ)

検索範囲は、検索値が存在する可能性がある範囲を指定します。この範囲は、列または行の連続したセル範囲・線形配列である必要があります。

サンプル図では書籍番号全体が入力されているA2:A16を指定しています。

3. 戻り範囲の指定(複数列・矩形配列の指定が可能)

戻り範囲は、検索値に対応する情報が含まれている範囲です。複数列・矩形配列の情報を戻り値として取得したい場合、この範囲に複数の列を指定します。

サンプル図ではC~E列を指定するためにC2:E16を指定しました。サイズは「検索範囲」と同じとします。また「左→右」の指定しかできません。例えばこのサンプルでは「金額、発刊日、書名」のような指定はできません(関数を追加すればできるが標準では不可)。

4. スピル配列の表示

XLOOKUP関数を実行すると、指定された複数列の情報がスピル配列として自動的に展開されます。

参考:元の表とは異なる順番で戻り値を登録する方法

抜粋:レート差と期待勝率の早見表(10ずつ、1000差まで小数第3位表示)
イロレーティング(Elo rating)に基づく、レーティング差と期待勝率の早見表です。 レート差と勝率の早見表(2ずつ、50まで、小数第3位表示) 小数第4位以下は四捨五入しています。K値は「32」でも「16」でも期待勝率には影響はありま...

実践例

=XLOOKUP(検索値, A列範囲, C列:E列範囲)

このXLOOKUP関数の例では、A列の範囲内で指定された検索値を見つけ、対応するC列からE列までの情報を戻り値として返します。

例えば下の表から書籍番号が「1008」の書名、発刊日、金額を検索させる式は以下の通りです。

=XLOOKUP(1008, A2:A16, C2:E16)

またセルG5に入力されている書籍番号の書名、発刊日、金額を検索させる式は以下の通りです。

=XLOOKUP(G5,A2:A16,C2:E16)

検索表を「テーブル甲」というテーブルとして登録してある場合には以下のような式となります。

=XLOOKUP(G5,テーブル甲[書籍番号],テーブル甲[[書名]:[金額]])

サンプルの検索表

書籍番号在庫書名発刊日金額
1001ケーススタディ民法2016/10/281400
1002交通六法2016/02/272700
1003法人税法の基礎2015/12/272700
1004会社法2014/10/272800
1005民法総則2015/03/272700
1006判例六法2016/01/272400
1007基礎法入門2014/09/272500
1008やさしい経済法2015/08/272500
1009刑法各論2016/07/282100
1010現近代民法2014/10/271400
1011会社法概論2014/04/272000
1012商法総則2014/04/271900
1013憲法2016/09/282500
1014商行為法2015/11/271600
1015倒産法入門2014/01/272300

まとめ

XLOOKUP関数を使って複数列の情報をスピル配列として取得する方法は、データ分析やレポート作成において非常に有用です。上記の手順に従って関数を使用することで、効率的に必要なデータを抽出できます。

VLOOKUP関数HLOOKUP関数では複数の式が必要である作業も、1つの式で完結させることができます。

コメント