概述
本 Demo 展示了 XLOOKUP 函数的多种使用场景，包括基本精确匹配、近似匹配、多值返回、双向查找、自定义未找到消息、数组参数、返回引用等。Demo 包含 9 个工作表，通过员工佣金计算、产品价格查询等实际案例，演示 XLOOKUP 函数的强大功能和灵活用法。
实现思路
启用动态数组支持，设置 spread.options.allowDynamicArray = true
创建 9 个工作表，每个工作表展示一种 XLOOKUP 应用场景
使用表格（Table）和数据绑定构建示例数据
通过公式演示不同参数组合的效果：精确匹配、近似匹配、多值返回等
结合 XMATCH 函数实现更复杂的查找逻辑
代码解析
启用动态数组
XLOOKUP 是动态数组函数，需要先启用动态数组支持。
基本精确匹配
根据电影名查找排名，使用默认的精确匹配模式（match_mode = 0）。
近似匹配
使用 match_mode = -1 实现近似匹配，如果找不到精确匹配，则返回下一个较小的项。这在查找折扣率、税率等分段数据时非常有用。
多值返回
XLOOKUP 可以返回多列数据。return_array 参数为 C9:E16，表示返回匹配行的 3 列数据。
双向查找（嵌套 XLOOKUP）
内层 XLOOKUP 根据「材料」查找对应的行范围，外层 XLOOKUP 根据「组别」查找对应的列值，实现矩阵查找。
自定义未找到消息
使用 if_not_found 参数，当查找失败时返回自定义消息而非默认的 #N/A 错误。
返回引用（用于范围计算）
XLOOKUP 返回单元格引用而非值，可以使用范围运算符（:）构建范围。这里计算「葡萄」到「香蕉」之间所有产品的总计。
综合应用：员工佣金计算
这是实际业务场景的综合应用：
第一步：使用 XMATCH 根据收入查找佣金类别（近似匹配）
第二步：使用 XLOOKUP 根据类别查找佣金百分比
第三步：计算实际佣金金额
运行效果
用例工作表：展示员工佣金自动计算，根据收入自动匹配佣金类别和百分比
基本精确匹配：输入电影名称，自动显示对应排名
基本近似匹配：输入数量，自动返回对应的折扣率
多值匹配：输入员工 ID，同时显示名字、姓氏、部门三个字段
双向查找：选择材料和组别，自动查找矩阵中的对应价格
未找到消息：查找不存在的电影时，显示「未找到」而非错误
数组参数：直接使用数组常量作为查找范围
返回引用：计算两个产品之间的总计金额
特殊情况：测试各种边界情况，如单值搜索、溢出列等
API 参考
XLOOKUP 函数
lookup_value：要查找的值（必需）
lookup_array：要搜索的数组或区域（必需）
return_array：要返回的数组或区域（必需）
if_not_found：找不到匹配项时返回的值（可选）
match_mode：匹配类型（可选）
0：精确匹配（默认），找不到返回 #N/A
-1：精确匹配或下一个较小的项
1：精确匹配或下一个较大的项
2：通配符匹配
search_mode：搜索模式（可选）
1：从第一项开始搜索（默认）
-1：从最后一项开始反向搜索
2：升序二分查找
-2：降序二分查找
allowDynamicArray 属性
启用动态数组支持，XLOOKUP 等动态数组函数需要此设置。
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss"));
spread.options.allowDynamicArray = true;
initStyles(spread);
initSpread(spread);
};
function initSpread(spread) {
spread.setSheetCount(9);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
initSheet2(spread.getSheet(1));
initSheet3(spread.getSheet(2));
initSheet4(spread.getSheet(3));
initSheet5(spread.getSheet(4));
initSheet6(spread.getSheet(5));
initSheet7(spread.getSheet(6));
initSheet8(spread.getSheet(7));
initSheet9(spread.getSheet(8));
spread.resumeCalcService();
spread.resumePaint();
}
function initStyles(spread) {
var introStyle = new GC.Spread.Sheets.Style();
introStyle.name = 'intro';
introStyle.font = 'normal bold 16px Segoe UI';
introStyle.foreColor = "#172b4d";
spread.addNamedStyle(introStyle);
var introStyle1 = new GC.Spread.Sheets.Style();
introStyle1.name = 'intro1';
introStyle1.font = 'normal bold 14px Calibri';
introStyle1.hAlign = 0;
introStyle1.vAlign = 1;
introStyle1.foreColor = "#172b4d";
spread.addNamedStyle(introStyle1);
var formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.name = 'formula';
formulaStyle.font = 'normal bold 12px Consolas';
formulaStyle.foreColor = "#c00000";
introStyle1.vAlign = 1;
spread.addNamedStyle(formulaStyle);
var tableHeaderStyle = new GC.Spread.Sheets.Style();
tableHeaderStyle.name = 'tableHeader';
tableHeaderStyle.font = "normal bold 14.7px Calibri";
tableHeaderStyle.hAlign = 1;
tableHeaderStyle.backColor = "#d9e1f2";
spread.addNamedStyle(tableHeaderStyle);
var tableContentStyle = new GC.Spread.Sheets.Style();
tableContentStyle.name = 'tableContent';
tableContentStyle.font = "normal normal 14.7px Calibri";
tableContentStyle.hAlign = 1;
spread.addNamedStyle(tableContentStyle);
var sourceStyle = new GC.Spread.Sheets.Style();
sourceStyle.name = 'source';
sourceStyle.hAlign = 0;
sourceStyle.backColor = "#fce8ce";
spread.addNamedStyle(sourceStyle);
var resultStyle = new GC.Spread.Sheets.Style();
resultStyle.name = 'result';
resultStyle.hAlign = 0;
resultStyle.backColor = "#e2efda";
spread.addNamedStyle(resultStyle);
}
function initSheet1(sheet) {
sheet.name('用例');
var table1Source = {
name: '员工季度佣金',
data: [
{ salesRap: 'Jim', quarter: 'Q1', revenue: 351 },
{ salesRap: 'Jim', quarter: 'Q2', revenue: 210 },
{ salesRap: 'Kevin', quarter: 'Q1', revenue: 687 },
{ salesRap: 'Sarah', quarter: 'Q1', revenue: 300 },
{ salesRap: 'Sarah', quarter: 'Q2', revenue: 809 },
{ salesRap: 'Kevin', quarter: 'Q2', revenue: 285 },
{ salesRap: 'Bob', quarter: 'Q1', revenue: 110 }
]
};
sheet.addSpan(1, 1, 1, 6);
sheet.setValue(1, 1, table1Source.name);
sheet.getCell(1, 1).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(1, 83);
sheet.setColumnWidth(2, 73);
sheet.setColumnWidth(3, 77);
sheet.setColumnWidth(4, 122);
sheet.setColumnWidth(5, 134);
sheet.setColumnWidth(6, 98);
var table1 = sheet.tables.add('Table1', 2, 1, 7, 6);
table1.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
var table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "salesRap", "销售人员");
var table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "quarter", "季度");
var table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "revenue", "收入");
var table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, null, "佣金类别");
var table1Column5 = new GC.Spread.Sheets.Tables.TableColumn(5, null, "佣金百分比", "0%");
var table1Column6 = new GC.Spread.Sheets.Tables.TableColumn(6, null, "佣金");
table1.autoGenerateColumns(false);
table1.bind([table1Column1, table1Column2, table1Column3, table1Column4, table1Column5, table1Column6], 'data', table1Source);
var table2Source = {
name: "佣金表",
data: [
{ category: 1, sales: 100, percentage: 0.05 },
{ category: 2, sales: 200, percentage: 0.1 },
{ category: 3, sales: 400, percentage: 0.15 },
{ category: 4, sales: 800, percentage: 0.20 }
]
};
sheet.addSpan(1, 8, 1, 3);
sheet.setValue(1, 8, table2Source.name);
sheet.getCell(1, 8).hAlign(1).font("normal bold 15px Calibri");
sheet.setColumnWidth(8, 88);
sheet.setColumnWidth(9, 57);
sheet.setColumnWidth(10, 91);
var table2 = sheet.tables.add('Table2', 2, 8, 4, 3);
table2.style(GC.Spread.Sheets.Tables.TableThemes.medium2);
var table2Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "category", "类别");
var table2Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "sales", "销售额");
var table2Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "percentage", "百分比", "0%");
table2.autoGenerateColumns(false);
table2.bind([table2Column1, table2Column2, table2Column3 ], 'data', table2Source);
table1.setColumnDataFormula(3, '=XMATCH([@收入],Table2[销售额],-1,1)');
table1.setColumnDataFormula(4, '=XLOOKUP([@[佣金类别]],Table2[类别],Table2[百分比],0,0,1)');
table1.setColumnDataFormula(5, '=[@收入]*[@[佣金百分比]]');
}
function initSheet2(sheet) {
sheet.name('基本精确匹配');
var intro = '#1 - 基本精确匹配';
var formula = '=XLOOKUP(H5,B6:B10,D6:D10)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["电影","年份","排名","销售额"],
["冰血暴",1996,5,61],
["洛城机密",1997,4,126],
["灵异第六感",1999,1,673],
["玩具总动员",1995,2,362],
["不可饶恕",1992,3,159]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, '电影');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, '排名');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, '玩具总动员');
sheet.setFormula(5, 7, formula);
}
function initSheet3(sheet) {
sheet.name('基本近似匹配');
var intro = '#2 - 基本近似匹配';
var formula = '=XLOOKUP(E6,B6:B10,C6:C10,,-1)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["数量","折扣"],
[0,0],
[10,0.05],
[25,0.1],
[50,0.2],
[100,0.25]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setValue(4, 4, '数量');
sheet.setStyle(4, 4, 'source');
sheet.setValue(4, 5, '结果');
sheet.setStyle(4, 5, 'result');
sheet.setValue(5, 4, 28);
sheet.setFormula(5, 5, formula);
}
function initSheet4(sheet) {
sheet.name('多值匹配');
var intro = '#3 - 多值匹配';
var formula = '=XLOOKUP(B6,B9:B16,C9:E16)';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["ID","名字","姓氏","部门"],
[610,"Janet","Farley","履行部"],
[798,"Steven","Batista","销售部"],
[841,"Evelyn","Monet","履行部"],
[886,"Marilyn","Bradley","履行部"],
[622,"Jonathan","Adder","市场部"],
[601,"Adrian","Birt","工程部"],
[869,"Julie","Irons","销售部"],
[867,"Erica","Tan","履行部"]
];
sheet.setArray(7, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(7 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(4, 100);
sheet.setValue(4, 1, 'ID');
sheet.setStyle(4, 1, 'source');
sheet.setValue(4, 2, '名字');
sheet.setStyle(4, 2, 'result');
sheet.setValue(4, 3, '姓氏');
sheet.setStyle(4, 3, 'result');
sheet.setValue(4, 4, '部门');
sheet.setStyle(4, 4, 'result');
sheet.setValue(5, 1, 841);
sheet.setFormula(5, 2, formula);
}
function initSheet5(sheet) {
sheet.name('双向查找');
var intro = '#4 - 双向查找';
var formula = '=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
[null,"A","B","C","D"],
["乙烯基",10,11.5,13.23,15.21],
["木材",12,13.8,15.87,18.25],
["玻璃",15,17.25,19.84,22.81],
["钢材",18,20.7,23.81,27.38],
["钛",23,26.45,30.42,34.98]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setValue(4, 7, '材料');
sheet.setStyle(4, 7, 'source');
sheet.setValue(4, 8, '玻璃');
sheet.setValue(5, 7, '组别');
sheet.setStyle(5, 7, 'source');
sheet.setValue(5, 8, 'B');
sheet.setValue(6, 7, '结果');
sheet.setStyle(6, 7, 'result');
sheet.setFormula(6, 8, formula);
}
function initSheet6(sheet) {
sheet.name('未找到消息');
var intro = '#5 - 未找到消息';
var formula = '=XLOOKUP(H4,B5:B9,E5:E9,"未找到")';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["电影","年份","排名","销售额"],
["冰血暴",1996,5,61],
["洛城机密",1997,4,126],
["灵异第六感",1999,1,673],
["玩具总动员",1995,2,362],
["不可饶恕",1992,3,159]
];
sheet.setArray(4, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(4 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
sheet.setValue(4, 6, '电影');
sheet.setStyle(4, 6, 'source');
sheet.setValue(5, 6, '排名');
sheet.setStyle(5, 6, 'result');
sheet.setValue(4, 7, '哥斯拉');
sheet.setFormula(5, 7, formula);
}
function initSheet7(sheet) {
sheet.name('数组参数');
var intro = '#6 - 数组参数';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(4, 1, '年份');
sheet.setStyle(4, 1, 'tableHeader');
sheet.setValue(5, 1, '销售额');
sheet.setStyle(5, 1, 'tableHeader');
sheet.setValue(6, 1, '年份');
sheet.setStyle(6, 1, 'source');
sheet.setValue(7, 1, '结果');
sheet.setStyle(7, 1, 'result');
sheet.setValue(4, 2, '{1996,1997,1999,1995,1992}');
sheet.setValue(5, 2, '{61,126,673,362,159}');
sheet.setValue(6, 2, 1997);
sheet.setColumnWidth(1, 126);
var formula1 = '=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})';
sheet.setValue(2, 1, formula1);
sheet.setStyle(2, 1, 'formula');
sheet.setFormula(7, 2, formula1);
}
function initSheet8(sheet) {
sheet.name('返回引用');
var intro = '#7 - 返回引用';
var formula = '=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
sheet.setValue(2, 1, formula);
sheet.setStyle(2, 1, 'formula');
var data = [
["产品","数量","价格","总计"],
["苹果",23,0.52,11.9],
["葡萄",98,0.77,75.28],
["梨",75,0.24,18.16],
["香蕉",95,0.18,17.25],
["樱桃",42,0.16,6.8]
];
sheet.setArray(7, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(7 + i, 1 + j, styleName);
}
}
// sheet.setColumnWidth(4, 100);
sheet.setValue(4, 1, '起始');
sheet.setStyle(4, 1, 'source');
sheet.setValue(4, 2, '结束');
sheet.setStyle(4, 2, 'source');
sheet.setValue(4, 3, '总计');
sheet.setStyle(4, 3, 'result');
sheet.setValue(5, 1, '葡萄');
sheet.setValue(5, 2, '香蕉');
sheet.setFormula(5, 3, formula);
}
function initSheet9(sheet) {
sheet.name('特殊情况');
var intro = '#测试 - 特殊情况';
sheet.setValue(1, 1, intro);
sheet.setStyle(1, 1, 'intro');
var data = [
["电影","年份","排名","销售额"],
["冰血暴",1996,5,61],
["洛城机密",1997,4,126],
["灵异第六感",1999,1,673],
["玩具总动员",1995,2,362],
["不可饶恕",1992,3,159]
];
sheet.setArray(1, 1, data);
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j ++) {
var styleName;
if (i === 0) {
styleName = 'tableHeader';
} else {
styleName = 'tableContent';
}
sheet.setStyle(1 + i, 1 + j, styleName);
}
}
sheet.setColumnWidth(1, 126);
var formula1 = '=XLOOKUP(C2,C2:E2,C3:E3)';
sheet.setValue(8, 1, '行搜索');
sheet.setStyle(8, 1, 'intro1');
sheet.setValue(8, 2, formula1);
sheet.setStyle(8, 2, 'formula');
sheet.setFormula(8, 5, formula1);
var formula2 = '=XLOOKUP(C4,C2:C7,D2:D7)';
sheet.setValue(9, 1, '列搜索');
sheet.setStyle(9, 1, 'intro1');
sheet.setValue(9, 2, formula2);
sheet.setStyle(9, 2, 'formula');
sheet.setFormula(9, 5, formula2);
var formula3 = '=XLOOKUP(B5,B2:B7,C2:E2)';
sheet.setValue(10, 1, '方向不一致');
sheet.setStyle(10, 1, 'intro1');
sheet.setValue(10, 2, formula3);
sheet.setStyle(10, 2, 'formula');
sheet.setFormula(10, 5, formula3);
var formula4 = '=XLOOKUP(B3,B3,E5)';
sheet.setValue(11, 1, '单值搜索');
sheet.setStyle(11, 1, 'intro1');
sheet.setValue(11, 2, formula4);
sheet.setStyle(11, 2, 'formula');
sheet.setFormula(11, 5, formula4);
var formula5 = '=XLOOKUP(B5,C4,D6)';
sheet.setValue(12, 1, '单值搜索');
sheet.setStyle(12, 1, 'intro1');
sheet.setValue(12, 2, formula5);
sheet.setStyle(12, 2, 'formula');
sheet.setFormula(12, 5, formula5);
var formula6 = '=XLOOKUP(D2,B2:E2,B3:E7)';
sheet.setValue(13, 1, '溢出列');
sheet.setStyle(13, 1, 'intro1');
sheet.setValue(13, 2, formula6);
sheet.setStyle(13, 2, 'formula');
sheet.setFormula(13, 5, formula6);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</body>
</html>
input[type="text"] {
width: 200px;
margin-right: 20px;
}
label {
display: inline-block;
width: 110px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
label {
display: block;
margin-bottom: 6px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
code {
border: 1px solid #000;
}
