[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
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/一个错误。这是默认的匹配模式。 -1 - 指精确匹配。如果没有找到匹配项,则返回下一个较小的项。 1 - 指精确匹配。如果没有找到匹配项,则返回下一个较大的项。 2 - 引用一个通配符匹配,其中*,?字符具有表示部分匹配的特殊含义。 |
[search_mode] | (可选) 按以下值指定搜索模式: 0 - 指的是将返回所有匹配值的“搜索全部”模式。[此模式在Excel中不可用。] 1 - 引用从第一项开始的搜索。这是默认的搜索模式。 -1 - 指从最后一项开始的反向搜索。 2 - 引用依赖于lookup_array参数按升序排序的二分查找。 -2 - 引用依赖于lookup_array参数按降序排序的二分查找。 |
not_found | (可选) 此参数可用于覆盖#N/A错误。not_found的典型值可能是“未找到”、“不匹配”、“没有结果”等。 |
XLOOKUP函数与spreadsheets同时工作时,以下几点必须记住:
接收数字数据。查找区域或表中的值。返回具有多个项的数组。
下面的gif显示了XLOOKUP函数的基本用法。
下面的代码片段描述了XLOOKUP函数的基本用法。在本例中,XLOOKUP用于根据影片的精确匹配检索排名。
$(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);
});
下面的gif显示了SUM和XLOOKUP函数一起对两个区域内的所有值进行求和。
下面的代码片段描述了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);
});