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
): 個々の範囲はそれぞれ金額、書名、発刊日、在庫を表しており、これらを指定された順序で水平に結合します。
最終的な式の動作のまとめ
- 検索:XLOOKUP関数がセルG5に入力された書籍番号をA列の範囲内で検索します。
- 結合: HSTACK関数が指定された列範囲を水平方向に結合し、一つの配列を作成します。
- 戻り値の抽出: XLOOKUP関数は、検索値に対応する行から、HSTACKによって結合された配列の該当する部分を抽出し、結果として返します。
まとめ・実践のポイント
- 検索範囲と戻り値範囲の行数が一致していることを確認してください。
- 戻り値として複数列を組み合わせる際、HSTACK関数を活用します。
- 結果のスピル範囲が他のセルのデータに重ならないように注意してください。
XLOOKUP関数とHSTACK関数を組み合わせることで、複数列からなる戻り値を特定の順序でスピル配列として表示することができます。
練習用のサンプル表
書籍番号 | 在庫 | 書名 | 発刊日 | 金額 |
---|---|---|---|---|
1001 | 有 | ケーススタディ民法 | 2016/10/28 | 1400 |
1002 | 無 | 交通六法 | 2016/02/27 | 2700 |
1003 | 無 | 法人税法の基礎 | 2015/12/27 | 2700 |
1004 | 無 | 会社法 | 2014/10/27 | 2800 |
1005 | 無 | 民法総則 | 2015/03/27 | 2700 |
1006 | 有 | 判例六法 | 2016/01/27 | 2400 |
1007 | 有 | 基礎法入門 | 2014/09/27 | 2500 |
1008 | 無 | やさしい経済法 | 2015/08/27 | 2500 |
1009 | 有 | 刑法各論 | 2016/07/28 | 2100 |
1010 | 有 | 現近代民法 | 2014/10/27 | 1400 |
1011 | 有 | 会社法概論 | 2014/04/27 | 2000 |
1012 | 有 | 商法総則 | 2014/04/27 | 1900 |
1013 | 無 | 憲法 | 2016/09/28 | 2500 |
1014 | 有 | 商行為法 | 2015/11/27 | 1600 |
1015 | 無 | 倒産法入門 | 2014/01/27 | 2300 |
コメント