XLOOKUP

XLOOKUPは、垂直方向および水平方向のセル範囲で検索を実行し、最も近い (近似)一致、「*」、「?」などのワイルドカード文字による部分一致、および完全一致をサポートします。デフォルトでは、XLOOKUP関数は完全一致の結果を返します。

本関数は、他の参照関数（VLOOKUP、HLOOKUP、LOOKUPなど）よりも比較的柔軟で強力です。 XLOOKUP関数は、次の場合に役に立ちます。

検索する値の右側または左側のデータを参照する場合。
テーブルからデータを取得する場合。
複数の列から結果を返す場合。
垂直方向データと水平方向データの両方で機能します。
先頭の項目または末尾の項目から逆方向にデータを検索する場合。
XLOOKUP関数は、値だけではなく範囲を返し、汎用配列でも機能します。

たとえば　XLOOKUP関数を使用することで、ユーザーは製品IDによる製品の価格の決定、列内での一致する税率の検索、従業員IDに基づく従業員名の検索など、他の多くの検索操作を実行できます。

構文

XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

引数

本関数の有効な引数は次のとおりです。

引数	説明
lookup_value	参照値。
lookup_array	検索する配列またはセル範囲。
return_array	返す配列またはセル範囲。
[if_not_found]	（オプション）有効な一致が見つからない場合は、返される値を参照します。この関数の引数を指定せず、有効な一致が見つからない場合、#N/A が返されます。ユーザーが無効な検索モードを指定した場合（例：Excelの場合「-0」）、#VALUEエラーが返されます。
[match_mode]	（オプション）次の値に従って、一致の種類を指定します。 0 - 完全一致。見つからない場合は、#N/A が返されます。これが既定の設定です。 -1 - 完全一致。見つからない場合は、次の小さなアイテムが返されます。 1 - 完全一致。見つからない場合は、次の大きなアイテムが返されます。 2 - *、?、および ? が特別な意味を持つワイルドカードの一致。
[search_mode]	（オプション）次の値に従って、検索モードを指定します。 0 - 一致したすべての値が返される「すべて検索」モードを実行します。 [このモードはExcelでは使用できません。] 1 - 先頭の項目から検索を実行します。これが既定の設定です。 -1 - 末尾の項目から逆方向に検索を実行します。 2 - 昇順で替えられたlookup_array引数に依存するバイナリ検索を実行します。 -2 - 降順で替えられたlookup_array引数に依存するバイナリ検索を実行します。
not_found	（オプション）＃N/Aエラーをオーバーライドするために使用できます。not_foundの一般的な値は、「見つかりません」、「一致なし」、「結果なし」などです。

解説

XLOOKUP関数を使用する際、次の項目に留意する必要があります。

参照値が見つからない場合XLOOKUP関数は#N/Aエラーを返します。
有効な結果を得るために、lookup_array引数の次元は、return_array引数と互換性がある必要があります。そうでない場合、#VALUE!エラーが返されます。

データ型

数値データを受け取り、範囲またはテーブル内の値を検索し、複数のアイテムを持つ配列を返します。

サンプル

次の画像は、XLOOKUP関数の基本的な使用法を示します。

次のコードは、XLOOKUP関数の基本的な使用法を示します。この例では、XLOOKUPを使用して Movie の完全一致に基づいてRankを取得します。

$(document).ready(function () {
    // Spreadを初期化します
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // 動的配列のサポートを有効にします
    spread.options.allowDynamicArray = true;
    // スタイルを作成します
    var style = new GC.Spread.Sheets.Style();
    style.font = "bold 12px Arial";
    style.foreColor = "black";
    style.backColor = "#EDFDF4";
    style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    style.vAlign = GC.Spread.Sheets.VerticalAlign.center;

    // スタイルを作成します
    var formulaStyle = new GC.Spread.Sheets.Style();
    formulaStyle.font = "bold 12px Arial";
    formulaStyle.foreColor = "black";
    formulaStyle.backColor = "#D3F0E0";
    formulaStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;


    // sheet1を取得します
    var sheet1 = spread.getSheet(0);
    // 列の幅を設定します
    sheet1.setColumnWidth(6, 180);
    // 式を作成します
    var formula_Exact = '=XLOOKUP(G4,A5:A9,C5:C9)';
    // 値を設定します
    sheet1.setValue(0, 6, 'Basic exact match');
    sheet1.setValue(1, 6, formula_Exact);
    // データを作成します
    var data = [
        ["Movie", "Year", "Rank", "Sales"],
        ["Fargo", 1996, 5, 61],
        ["L.A. Confidential", 1997, 4, 126],
        ["The Sixth Sense", 1999, 1, 673],
        ["Toy Story", 1995, 2, 362],
        ["Unforgiven", 1992, 3, 159]
    ];
    // データを設定します
    sheet1.setArray(3, 0, data);
    // 値を設定します
    sheet1.setValue(3, 5, 'Movie');
    sheet1.setValue(4, 5, 'Sales');
    sheet1.setValue(3, 6, 'Toy Story');
    // 式を設定します
    sheet1.setFormula(4, 6, formula_Exact);

    // スタイルを設定します
    for (var i = 0; i < 4; i++) {
        sheet1.setStyle(3, i, style, GC.Spread.Sheets.SheetArea.viewport);
    }
    sheet1.setStyle(3, 5, style, GC.Spread.Sheets.SheetArea.viewport);
    sheet1.setStyle(4, 5, style, GC.Spread.Sheets.SheetArea.viewport);
    sheet1.setStyle(0, 6, formulaStyle, GC.Spread.Sheets.SheetArea.viewport);
    sheet1.setStyle(1, 6, formulaStyle, GC.Spread.Sheets.SheetArea.viewport);
});

次の画像は、SUMとXLOOKUP関数を使用して2つの範囲のすべての値の合計を示します。

xlookup1

次のコードは、SUMとXLOOKUP関数を一緒に使用する方法を示します。

$(document).ready(function () {
    // Spreadを初期化します
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
    // 動的配列のサポートを有効にします
    spread.options.allowDynamicArray = true;

    // sheet7を取得します
    var sheet7 = spread.getSheet(0);

    // テキストを設定します
    sheet1.setValue(1, 1, 'XLOOKUP return reference');
    sheet1.setValue(2, 1, '=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))');
    sheet1.addSpan(1, 1, 1, 3, GC.Spread.Sheets.SheetArea.viewport);
    sheet1.addSpan(2, 1, 1, 7, GC.Spread.Sheets.SheetArea.viewport);

    sheet1.setValue(4, 1, 'Start');
    sheet1.setValue(4, 2, 'End');
    sheet1.setValue(4, 3, 'Total');
    sheet1.setValue(5, 1, 'Grape');
    sheet1.setValue(5, 2, 'Banana');

    // データを作成します
    var data = [
        ["Product", "Quantiy", "Price", "Total"],
        ["Apple", 23, 0.52, 11.9],
        ["Grape", 98, 0.77, 75.28],
        ["Pear", 75, 0.24, 18.16],
        ["Banana", 95, 0.18, 17.25],
        ["Cherry", 42, 0.16, 6.8]
    ];
    // データを設定します
    sheet7.setArray(7, 1, data);
    // 式を設定します
    sheet7.setFormula(5, 3, "=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))");

    // スタイルを設定します
    for (var i = 1; i < 5; i++) {
        sheet7.setStyle(7, i, style, GC.Spread.Sheets.SheetArea.viewport);
    }
    for (var i = 1; i < 4; i++) {
        sheet7.setStyle(4, i, style, GC.Spread.Sheets.SheetArea.viewport);
    }
    sheet7.setStyle(1, 1, formulaStyle, GC.Spread.Sheets.SheetArea.viewport);
    sheet7.setStyle(2, 1, formulaStyle, GC.Spread.Sheets.SheetArea.viewport);
});

XLOOKUP

構文

引数

解説

データ型

サンプル

関連トピック