数组公式用例

将 SpreadJS 中的一些公式转化为数组公式,可使您的表单更强大,同时对其进行简化。

用做数组公式的公式可在多个单元格的各行各列上进行计算,如果不使用数组公式,则需要使用多个公式方可进行计算。例如: 你可以使用一个数组公式来查找一片区域的值。 比如: 可以对满足多个条件的值求和。 比如:
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3 }); initSpread(spread); }; function initSpread(spread) { var spreadNS = GC.Spread.Sheets; var fbx = new spreadNS.FormulaTextBox.FormulaTextBox(document.getElementById('formulaBar')); fbx.workbook(spread); var oldArrayRange; var arrayFormulaStyle = new spreadNS.Style(); arrayFormulaStyle.backColor = "#E0E0FF"; for (var i = 0; i < spread.sheets.length; i++) { (function (sheet1) { sheet1.bind(spreadNS.Events.SelectionChanged, null, function (args) { sheet1.suspendPaint(); var formulaBar = document.getElementById("formulaBar"); var formulaInfo = sheet1.getFormulaInformation(sheet1.getActiveRowIndex(), sheet1.getActiveColumnIndex()); if (oldArrayRange && (!formulaInfo || !formulaInfo.isArrayFormula || !formulaInfo.baseRange.equals(oldArrayRange))) { for (var r = oldArrayRange.row; r < oldArrayRange.row + oldArrayRange.rowCount; r++) { for (var c = oldArrayRange.col; c < oldArrayRange.col + oldArrayRange.colCount; c++) { sheet1.setStyle(r, c, null); } } oldArrayRange = null; } if (formulaInfo && formulaInfo.hasFormula) { var formula = "=" + formulaInfo.formula; formula = formulaInfo.isArrayFormula ? "{" + formula + "}" : formula; formulaBar.value=formula; if (formulaInfo.isArrayFormula && !formulaInfo.baseRange.equals(oldArrayRange)) { oldArrayRange = formulaInfo.baseRange; for (var r = oldArrayRange.row; r < oldArrayRange.row + oldArrayRange.rowCount; r++) { for (var c = oldArrayRange.col; c < oldArrayRange.col + oldArrayRange.colCount; c++) { sheet1.setStyle(r, c, arrayFormulaStyle); } } } } else { formulaBar.value=sheet1.getValue(sheet1.getActiveRowIndex(), sheet1.getActiveColumnIndex()); } sheet1.resumePaint(); }); })(spread.sheets[i]); } setCalculation(spread.sheets[0]); setSearch(spread.sheets[1]); setStatistics(spread.sheets[2]); } function setCalculation(sheet) { sheet.name("Calculation"); sheet.setArray(0, 0, [ ["", "Match", "Physical", "Chemistry"], ["Alice", 97, 61, 53], ["Jon", 65, 76, 65], ["Bob", 55, 70, 64], ["Jack", 89, 77, 73], ["Tom", 83, 62, 78], ["Robert", 86, 98, 57] ]); sheet.addSpan(0, 5, 1, 4); sheet.setValue(0, 5, "Calculate every body's total score"); sheet.setArrayFormula(1, 5, 6, 1, "=SUBTOTAL(9,OFFSET($B$2,ROW($A$1:$A$6)-ROW($A$1),,1,3))"); } function setSearch(sheet) { sheet.name("Search"); sheet.setArray(0, 0, [ ["apple", "apple"], ["banana", "strawberry"], ["pea", "potato"], ["tomato", "potato"], ["potato", "dumpling"], ["cake"], ["noodle"], ["rice"], ["soup"] ]); sheet.addSpan(0, 3, 1, 8); sheet.setValue(0, 3, "Find out the first value on B1:B5 that does not contains on A1:A9"); sheet.setColumnWidth(3, 80); sheet.addSpan(2, 3, 1, 2); sheet.setValue(2, 3, "ArrayFormula Result:"); sheet.setArrayFormula(2, 5, 1, 1, "=INDEX(B1:B5,MATCH(TRUE,ISNA(MATCH(B1:B5,A1:A9,0)),0))"); sheet.getCell(2, 5).backColor("Accent 5 80"); sheet.addSpan(3, 3, 1, 2); sheet.setValue(3, 3, "NormalFormula Result:"); sheet.setFormula(3, 5, "=INDEX(B1:B5,MATCH(TRUE,ISNA(MATCH(B1:B5,A1:A9,0)),0))"); sheet.getCell(3, 5).backColor("Accent 6 80"); } function setStatistics(sheet) { sheet.name("Statistics"); sheet.options.allowCellOverflow = true; sheet.setArray(0, 0, [ ["Product", "Salesman", "Units Sold"], ["Fax", "Brown", 1], ["Phone", "Smith", 10], ["Fax", "Jones", 20], ["Fax", "Smith", 30], ["Phone", "Jones", 40], ["PC", "Smith", 50], ["Fax", "Brown", 60], ["Phone", "Davis", 70], ["PC", "Jones", 80] ]); sheet.setValue(1, 5, "Summing Sales: Faxes Sold By Brown"); sheet.setArrayFormula(2, 5, 1, 1, "=SUM((A2:A10=\"Fax\")*(B2:B10=\"Brown\")*(C2:C10))"); sheet.setValue(4, 5, "Logical AND (Faxes And Brown)"); sheet.setArrayFormula(5, 5, 1, 1, "=SUM((A2:A10=\"Fax\")*(B2:B10=\"Brown\"))"); sheet.setValue(7, 5, "Logical OR (Faxes Or Jones)"); sheet.setArrayFormula(8, 5, 1, 1, "=SUM(IF((A2:A10=\"Fax\")+(B2:B10=\"Jones\"),1,0))"); sheet.setValue(10, 5, "Logical XOR (Fax Or Jones but not both)"); sheet.setArrayFormula(11, 5, 1, 1, "=SUM(IF(MOD((A2:A10=\"Fax\")+(B2:B10=\"Jones\"),2),1,0))"); sheet.setValue(13, 5, "Logical AND (All Sales Except Fax And Jones)"); sheet.setArrayFormula(14, 5, 1, 1, "=SUM(IF((A2:A10=\"Fax\")+(B2:B10=\"Jones\")<>2,1,0))"); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <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$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.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 class="options-container"> <textarea id="formulaBar" rows="10" readonly></textarea> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } textarea { width: 100%; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }