数组公式用例

将 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("计算"); sheet.setArray(0, 0, [ ["", "数学", "物理", "化学"], ["李华", 97, 61, 53], ["张伟", 65, 76, 65], ["王强", 55, 70, 64], ["陈杰", 89, 77, 73], ["刘阳", 83, 62, 78], ["赵鹏", 86, 98, 57] ]); sheet.addSpan(0, 5, 1, 4); sheet.setValue(0, 5, "计算每个人的总分"); 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("查找"); sheet.setArray(0, 0, [ ["苹果", "苹果"], ["香蕉", "草莓"], ["豌豆", "土豆"], ["西红柿", "土豆"], ["土豆", "饺子"], ["蛋糕"], ["面条"], ["米饭"], ["汤"] ]); sheet.addSpan(0, 3, 1, 8); sheet.setValue(0, 3, "找出B1:B5中第一个不在A1:A9中的值"); sheet.setColumnWidth(3, 80); sheet.addSpan(2, 3, 1, 2); sheet.setValue(2, 3, "数组公式结果:"); 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, "普通公式结果:"); 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("统计"); sheet.options.allowCellOverflow = true; sheet.setArray(0, 0, [ ["产品", "销售员", "销售数量"], ["传真机", "李三", 1], ["电话", "王四", 10], ["传真机", "张五", 20], ["传真机", "王四", 30], ["电话", "张五", 40], ["电脑", "王四", 50], ["传真机", "李三", 60], ["电话", "赵六", 70], ["电脑", "张五", 80] ]); sheet.setValue(1, 5, "汇总销售: 李三销售的传真机数量"); sheet.setArrayFormula(2, 5, 1, 1, "=SUM((A2:A10=\"传真机\")*(B2:B10=\"李三\")*(C2:C10))"); sheet.setValue(4, 5, "逻辑与 (传真机 且 李三)"); sheet.setArrayFormula(5, 5, 1, 1, "=SUM((A2:A10=\"传真机\")*(B2:B10=\"李三\"))"); sheet.setValue(7, 5, "逻辑或 (传真机 或 张五)"); sheet.setArrayFormula(8, 5, 1, 1, "=SUM(IF((A2:A10=\"传真机\")+(B2:B10=\"张五\"),1,0))"); sheet.setValue(10, 5, "逻辑异或 (传真机 或 张五,但不同时满足)"); sheet.setArrayFormula(11, 5, 1, 1, "=SUM(IF(MOD((A2:A10=\"传真机\")+(B2:B10=\"张五\"),2),1,0))"); sheet.setValue(13, 5, "逻辑与 (除传真机和张五外的所有销售)"); sheet.setArrayFormula(14, 5, 1, 1, "=SUM(IF((A2:A10=\"传真机\")+(B2:B10=\"张五\")<>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; }