XLOOKUP関数の「戻り範囲」へ特殊な順番の列を登録してスピル配列を生成する

Excelのデータ処理技術は日々進化しており、最新版Excel365では、より複雑なデータ操作を簡単に行える機能が追加されています。この記事では、XLOOKUP関数を使用して、特定の順序でスピル配列を表示する方法を解説します。

XLOOKUP関数の基礎

XLOOKUP関数は、指定された検索値を検索範囲内で見つけ、対応する戻り値範囲から情報を返す関数です。この強力な関数を活用することで、データの検索と表示を効率的に行うことができます。

XLOOKUP関数の基本形式は以下の通りです。より詳細な設定方法は左のリンク先を確認してください。

XLOOKUP(検索値, 検索範囲, 戻り値範囲)

検索値: 検索する要素・値。要素は1つのみ指定する。
検索範囲: 検索値を検索する1次元配列。1つの列(あるいは行)を指定する。検索値と同種のものを指定する。
戻り範囲: 検索値に対応する値を返す配列。複数列あるいは複数行の指定をするとスピル形式で結果を表示するようになる。

基本的には以下の2例のように使います。「検索値…1012」の位置を「検索範囲…A2:A16」から発見し、発見したら「戻り範囲…C2:E16」の3列の情報をスピル配列として戻す例です。

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

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

HSTACK関数は、複数の範囲または配列を水平方向に結合します。この関数を使用することで、XLOOKUP関数の戻り値範囲を自由にカスタマイズし、必要なデータを望む順序でスピル配列として表示させることが可能になります。

この関数の基本的な書式は以下の通りです。

=HSTACK(配列1, [配列2], ...)

ここで、「配列1」「配列2」…は、結合したい配列や範囲を指します。この関数は、複数の配列を水平方向に並べ、一つの連続した配列を作成します。基本的にはセル範囲を指定します。

例えば以下の式は、サンプル図において「金額→書名→発刊日→在庫」の順に並べるスピル配列を作成します。

=HSTACK(E2:E16, C2:C16, D2:D16, B2:B16)

戻り値範囲にHSTACK関数を使った独自のスピル配列を登録することで、XLOOKUP関数の出力を柔軟に制御できます。次の例では、特定の書籍番号に対応する「金額、書名、発刊日、在庫」を指定の順序で表示します。

=XLOOKUP(G5, A2:A16, HSTACK(E2:E16, C2:C16, D2:D16, B2:B16))

このセルH5へ入力した式では、セルG5に入力された書籍番号をA列で検索し、対応する金額(E列)、書名(C列)、発刊日(D列)、在庫(B列)の情報を特定の順番で水平に結合して表示します。

XLOOKUP関数は新しい検索関数で、指定した検索値を検索範囲内で見つけ、対応する戻り値範囲から情報を返すという役割を果たします。

検索値(G5): この場合、セルG5に入力された値が検索対象です。これは書籍番号などの特定の識別情報である可能性が高いです。
検索範囲(A2:A16): 検索値を探す範囲です。この例ではA列の2行目から16行目までが指定されており、ここから検索値に対応するデータを見つけ出します。
戻り値範囲: XLOOKUP関数のこの部分に、HSTACK関数を用いた複数列の結合が使われています。

HSTACK関数は、複数の配列や範囲を水平方向に結合する機能を持ちます。この式では、金額(E列)、書名(C列)、発刊日(D列)、在庫(B列)の順に列を並べ替え、一つの水平配列に結合しています。

結合される範囲(E2:E16, C2:C16, D2:D16, B2:B16): 個々の範囲はそれぞれ金額、書名、発刊日、在庫を表しており、これらを指定された順序で水平に結合します。

XLOOKUP関数とHSTACK関数を組み合わせることで、複数列からなる戻り値を特定の順序でスピル配列として表示することができます。