LET

LET函数为计算结果分配名称。 这允许在公式内存储中间表达式,值或定义的名称。 这些名称仅适用于LET函数内的范围。 与编程中的变量类似,LET函数是通过Excel内置的公式语法完成的。

要使用LET函数,您需要定义名称和与之关联的值,以及使用名称和值所组成的最终计算表达式。 您必须定义至少一个名称/值(一个变量),LET函数最多支持126组这样的变量。

语法 参数 描述 name1 (必需) 第一个要分配的变量名称。 必须以字母开头。 不能是公式的输出结果或与区域的语法冲突。 name_value1 (必需) 分配给name1的值。 calculation_or_name2 (必需) 以下之一: 使用LET函数中名称和值组成的最终计算表达式。 这必须是LET函数中的最后一个参数。或者是第二次要分配给第二个name_value2键值对的名称。如果指定了名称,则name_value2 以及 calculator_or_name3是必选的。 name_value2 (可选) 分配给calculation_or_name2的值。 calculation_or_name3 (可选) 以下之一: 使用LET函数中所有名称的计算表达式。 LET函数中的最后一个参数必须是计算表达式。第三次要分配给第三个name_value3键值对的名称。 如果指定了名称,则name_value3 以及 calculator_or_name4是必选的。 使用说明 LET函数旨在公式中声明变量来更加容易地编写更复杂的公式。 命名变量后,可以为其分配静态值或基于计算的值。 这样,公式就可以根据需要来多次使用名称来引用变量,而变量的名称只能被分配一次。 优势 提升了性能 如果您在公式中多次写入相同的表达式,则Excel将多次计算该结果。而LET函数允许您按名称调用表达式,并且只让Excel计算一次。 易于阅读和编写 无需记住特定区域或单元格的引用指的是什么,您的计算表达式需要做的只有复制、粘贴表达式。 通过声明和命名变量的功能,您可以为自己和公式的使用者提供有意义的上下文。
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(5); spread.suspendPaint(); spread.suspendCalcService(); initSheet1(spread.getSheet(0)); initSheet2(spread.getSheet(1)); initSheet3(spread.getSheet(2)); initSheet4(spread.getSheet(3)); initSheet5(spread.getSheet(4)); 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 introSecStyle = new GC.Spread.Sheets.Style(); introSecStyle.name = 'introSec'; introSecStyle.font = 'normal bold 12px Segoe UI'; introSecStyle.foreColor = "#000"; spread.addNamedStyle(introSecStyle); var introGrayStyle = new GC.Spread.Sheets.Style(); introGrayStyle.name = 'introGray'; introGrayStyle.font = 'normal bold 12px Segoe UI'; introGrayStyle.foreColor = "gray"; spread.addNamedStyle(introGrayStyle); 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('Use Case'); var table1Source = { name: 'Support Engineer Employment Length', data: [ { engineer: 'Bob', start: new Date(2014,4,25), salary: 2790 }, { engineer: 'Jim', start: new Date(2019,6,20), salary: 2216 }, { engineer: 'Kevin', start: new Date(2017,2,1), salary: 2498 }, { engineer: 'Sarah', start: new Date(2020,6,14), salary: 1989 } ] }; sheet.addSpan(1, 1, 1, 4); sheet.setValue(1, 1, table1Source.name); sheet.getCell(1, 1).hAlign(1).font("normal bold 15px Calibri"); sheet.setColumnWidth(1, 132); sheet.setColumnWidth(2, 87); sheet.setColumnWidth(3, 147); sheet.setColumnWidth(4, 63); var table1 = sheet.tables.add('Table1', 2, 1, 5, 4); table1.style(GC.Spread.Sheets.Tables.TableThemes.medium7); var table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "engineer", "Support Engineer"); var table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "start", "Start Date"); var table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, null, "Time with Company"); var table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, "salary", "Salary"); table1.autoGenerateColumns(false); table1.bind([table1Column1, table1Column2, table1Column3, table1Column4], 'data', table1Source); table1.setColumnDataFormula(2, '=LET(time,(YEAR(TODAY())-YEAR([@[Start Date]])),SWITCH(TRUE,time>5,"5+ years",time>=1,"1-4 years",time=0,"<= 1 Year"))'); var style = new GC.Spread.Sheets.Style(); style.backColor = 'rgb(112,173,71)'; style.foreColor = 'rgb(255,255,255)'; style.font = 'normal bold 14px Calibri'; sheet.setStyle(8, 1, style); sheet.setValue(8, 1, 'Salary Calculations'); sheet.getCell(8, 2).backColor('rgb(227,239,218)'); sheet.getCell(8, 3).backColor('rgb(227,239,218)'); var lineStyle = GC.Spread.Sheets.LineStyle.dotted; var lineBorder = new GC.Spread.Sheets.LineBorder('rgb(143,193,104)', lineStyle); var sheetArea = GC.Spread.Sheets.SheetArea.viewport; sheet.getRange(8, 1, 1, 3).setBorder(lineBorder, { left: true, right: true, top: true, bottom: true }, sheetArea); var combo = new GC.Spread.Sheets.CellTypes.ComboBox(); combo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value); combo.items([ { text: 'Min', value: 1 }, { text: 'Max', value: 2 }, { text: 'Average', value: 3 } ]); sheet.setCellType(8, 2, combo, GC.Spread.Sheets.SheetArea.viewport); sheet.setValue(8, 2, 3); sheet.setFormula(8, 3, '=LET(data,Table1[Salary],calc,C9,aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate, data))'); } function initSheet2(sheet) { sheet.name('#1'); sheet.setValue(1, 1, 'LET basic usage'); sheet.setStyle(1, 1, 'intro'); // Sample pair varible var formula = '=LET(x,2,x+3)'; sheet.setValue(3, 1, 'Sample pair varible'); sheet.setStyle(3, 1, 'introSec'); sheet.setValue(4, 1, formula); sheet.setStyle(4, 1, 'formula'); sheet.setValue(5, 1, 'Result'); sheet.setStyle(5, 1, 'result'); sheet.setFormula(5, 2, formula); // Multiple pair varibles var formula = '=LET(x,1,y,2,z,3,x+y+z=x*y*z)'; sheet.setValue(7, 1, 'Multiple pair varibles'); sheet.setStyle(7, 1, 'introSec'); sheet.setValue(8, 1, formula); sheet.setStyle(8, 1, 'formula'); sheet.setValue(9, 1, 'Result'); sheet.setStyle(9, 1, 'result'); sheet.setFormula(9, 2, formula); // Invoked formula var formula = '=LET(x,1,y,2,SUM(x,y))'; sheet.setValue(11, 1, 'Invoked formula'); sheet.setStyle(11, 1, 'introSec'); sheet.setValue(12, 1, formula); sheet.setStyle(12, 1, 'formula'); sheet.setValue(13, 1, 'Result'); sheet.setStyle(13, 1, 'result'); sheet.setFormula(13, 2, formula); // Dynamic Array var formula = '=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))'; sheet.setValue(15, 1, 'Dynamic Array'); sheet.setStyle(15, 1, 'introSec'); sheet.setValue(16, 1, formula); sheet.setStyle(16, 1, 'formula'); sheet.setValue(17, 1, 'Range'); sheet.setStyle(17, 1, 'source'); sheet.setArray(17, 2, [[1,2,3]]); sheet.setValue(18, 1, 'Result'); sheet.setStyle(18, 1, 'result'); sheet.setFormula(18, 2, formula); } function initSheet3(sheet) { sheet.name('#2'); sheet.setValue(1, 1, 'LET local varible & custom name'); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, 'This sheet includes a custom name "user" which be assigned "Michael" value'); sheet.setStyle(2, 1, 'introGray'); sheet.addCustomName('user', '="Michael"'); sheet.setValue(3, 1, 'user'); sheet.setStyle(3, 1, 'source'); sheet.setFormula(3, 2, '=user'); // Always use let local varible first var formula = '=LET(user,"Ivy","The actual user is: "&user)'; sheet.setValue(5, 1, 'Always use let local varible first'); sheet.setStyle(5, 1, 'introSec'); sheet.setValue(6, 1, formula); sheet.setStyle(6, 1, 'formula'); sheet.setValue(7, 1, 'Result'); sheet.setStyle(7, 1, 'result'); sheet.setFormula(7, 2, formula); // Use custom name if not avalible local varible var formula = '=LET(user,user,"The actual user is: "&user)'; sheet.setValue(9, 1, 'Use custom name if not avalible local varible'); sheet.setStyle(9, 1, 'introSec'); sheet.setValue(10, 1, formula); sheet.setStyle(10, 1, 'formula'); sheet.setValue(11, 1, 'Result'); sheet.setStyle(11, 1, 'result'); sheet.setFormula(11, 2, formula); } function initSheet4(sheet) { sheet.name('#3'); sheet.setValue(1, 1, 'LET nested'); sheet.setStyle(1, 1, 'intro'); // Always use the current scope varible var formula = '=LET(var,"First scope",LET(var,"Second scope",var))'; sheet.setValue(3, 1, 'Always use the current scope varible'); sheet.setStyle(3, 1, 'introSec'); sheet.setValue(4, 1, formula); sheet.setStyle(4, 1, 'formula'); sheet.setValue(5, 1, 'Result'); sheet.setStyle(5, 1, 'result'); sheet.setFormula(5, 2, formula); // Use the top scope varible if not found the avalible varible in current scope var formula = '=LET(var,"First scope",LET(var,var,var&" [from the second scope]"))'; sheet.setValue(7, 1, 'Use the top scope varible if not found the avalible varible in current scope'); sheet.setStyle(7, 1, 'introSec'); sheet.setValue(8, 1, formula); sheet.setStyle(8, 1, 'formula'); sheet.setValue(9, 1, 'Result'); sheet.setStyle(9, 1, 'result'); sheet.setFormula(9, 2, formula); } function initSheet5(sheet) { sheet.name('#4'); sheet.setValue(1, 1, 'LET simplify the complex formula'); sheet.setStyle(1, 1, 'intro'); // Filter the data to show one person var formula = '=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),"-",filteredRange))'; sheet.setValue(3, 1, 'Filter the data to show one person'); sheet.setStyle(3, 1, 'introSec'); sheet.setValue(4, 1, formula); sheet.setStyle(4, 1, 'formula'); var data = [ ["Rep", "Region", "Product", "Profit"], ["Amy", "East", "Apple", 1.33 ], ["Fred", "South", "Banana", 0.09], ["Amy", "West", "Mango", 1.85], ["Fred", "North", null, 0.82], ["Fred", "West", "Banana", 1.25], ["Amy", "East", "Apple", 0.72], ["Fred", "North", "Mango", 0.54] ]; sheet.setStyle(5, 1, 'tableHeader'); sheet.setStyle(5, 2, 'tableHeader'); sheet.setStyle(5, 3, 'tableHeader'); sheet.setStyle(5, 4, 'tableHeader'); sheet.setArray(5, 1, data); sheet.setValue(6, 6, 'Rep'); sheet.setStyle(6, 6, 'source'); sheet.setValue(7, 6, 'Result'); sheet.setStyle(7, 6, 'result'); sheet.setValue(6, 7, 'Fred'); sheet.setFormula(7, 7, formula); // Generate all dates between May 1, 2020 and May 15, 2020 sheet.setColumnWidth(2, 72); sheet.setColumnWidth(7, 72); var formula = '=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))'; var formatter = '[$-en-US]dd-mmm-yy;@'; sheet.setValue(15, 1, 'Generate all dates between May 1, 2020 and May 15, 2020'); sheet.setStyle(15, 1, 'introSec'); sheet.setValue(16, 1, formula); sheet.setStyle(16, 1, 'formula'); sheet.setValue(17, 1, 'Start'); sheet.setStyle(17, 1, 'tableHeader'); sheet.setValue(18, 1, 'End'); sheet.setStyle(18, 1, 'tableHeader'); sheet.setValue(17, 2, new Date(2020, 4, 1)); sheet.setValue(18, 2, new Date(2020, 4, 15)); sheet.setFormatter(17, 2, formatter); sheet.setFormatter(18, 2, formatter); sheet.setValue(17, 6, 'Result'); sheet.setStyle(17, 6, 'result'); sheet.setFormula(17, 7, formula); for (var i = 0; i < 11; i ++) { sheet.setFormatter(i + 17, 7, formatter); } } 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/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/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/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; }