XLOOKUP関数の「戻り範囲」へ特殊な順番の複数の列を登録する方法

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関数の基礎

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関数の役割

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

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

HSTACK関数の機能

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

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

最終的な式の動作のまとめ

  1. 検索:XLOOKUP関数がセルG5に入力された書籍番号をA列の範囲内で検索します。
  2. 結合: HSTACK関数が指定された列範囲を水平方向に結合し、一つの配列を作成します。
  3. 戻り値の抽出: XLOOKUP関数は、検索値に対応する行から、HSTACKによって結合された配列の該当する部分を抽出し、結果として返します。

まとめ・実践のポイント

  • 検索範囲と戻り値範囲の行数が一致していることを確認してください。
  • 戻り値として複数列を組み合わせる際、HSTACK関数を活用します。
  • 結果のスピル範囲が他のセルのデータに重ならないように注意してください。

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

練習用のサンプル表

書籍番号在庫書名発刊日金額
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

コメント