AGGREGATE 函数

AGGREGATE 函数可将19种不同的聚合计算应用于列表或数组,并提供忽略隐藏行和错误值的选项。

SpreadJS支持AGGREGATE函数来执行聚合操作。 语法与Excel相同。 AGGREGATE 第一个参数是编号(1-19),表示使用的函数。 函数编号函数 1AVERAGE 2COUNT 3COUNTA 4MAX 5MIN 6PRODUCT 7STDEV.S 8STDEV.P 9SUM 10VAR.S 11VAR.P 12MEDIAN 13MODE.SNGL 14LARGE 15SMALL 16PERCENTILE.INC 17QUARTILE.INC 18APERCENTILE.EXC 19QUARTILE.EXC AGGREGATE 第二个参数指定在计算中忽略某些值。 选项行为 0 或省略忽略嵌套的SUBTOTAL和AGGREGATE函数 1忽略隐藏的行,嵌套的SUBTOTAL和AGGREGATE函数 2忽略错误的数据,嵌套的SUBTOTAL和AGGREGATE函数 3忽略隐藏的行,错误值,嵌套的SUBTOTAL和AGGREGATE函数 4不忽略 5忽略隐藏的行 6忽略错误的数据 7忽略隐藏的行和错误的数据 函数编号1-13中,其余参数为一个或多个引用。 函数编号14-19需要一个数组或对引用作为第三个参数,并使用k来指定该函数的参数。
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var salesData = [ ["Salesperson", "Birthdate", "Region", "SaleAmount", "Commission%", "CommissionAmount"], ["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26], ["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99], ["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141], ["Erich", new Date("1994/05/23"), "West", '', 0, 49.2], ["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120], ["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135], ["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110], ["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2], ["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35], ["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76] ]; var sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.setArray(8, 1, salesData); sheet.setFormula(12, 6, "=E13*F13"); var filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(9, 1, salesData.length - 1, salesData[0].length)); sheet.rowFilter(filter); var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, { compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains, expected: "th" }); filter.addFilterItem(3, condition); filter.filter(3); sheet.setText(0, 2, "Filter the data and check the formula result changes."); sheet.setArray(2, 2, [["Ignore hidden rows", "SUM CommissionAmount"], ["", "MAX CommissionAmount"], ["", ""], ["Ignore error", "SUM CommissionAmount"], ["", "MAX CommissionAmount"]]); sheet.setFormula(2, 4, "=AGGREGATE(9,5,G10:G19)"); sheet.setFormula(3, 4, "=AGGREGATE(14,5,G10:G19, 1)"); sheet.setFormula(5, 4, "=AGGREGATE(9,6,G10:G19)"); sheet.setFormula(6, 4, "=AGGREGATE(14,6,G10:G19, 1)"); sheet.setFormula(2, 5, "=FORMULATEXT(E3)"); sheet.setFormula(3, 5, "=FORMULATEXT(E4)"); sheet.setFormula(5, 5, "=FORMULATEXT(E6)"); sheet.setFormula(6, 5, "=FORMULATEXT(E7)"); sheet.getRange(9, 5, 10, 1).formatter(new GC.Spread.Formatter.GeneralFormatter("0.00%")); sheet.getRange(8, 1, 1, 6).backColor('rgb(219,225,240)'); initStyle(sheet); sheet.resumePaint(); } function initStyle(sheet) { sheet.defaults.colWidth = 100; sheet.setColumnWidth(2, 140); sheet.getRange(9, 2, 10, 1).formatter("mm-dd-yyyy"); sheet.setColumnWidth(3, 160); sheet.setColumnWidth(5, 160); sheet.setColumnWidth(6, 160); sheet.addSpan(2, 2, 2, 1); sheet.addSpan(5, 2, 2, 1); var lineStyle = GC.Spread.Sheets.LineStyle.thin; var sheetArea = GC.Spread.Sheets.SheetArea.viewport; var lineBorder = new GC.Spread.Sheets.LineBorder('orange', lineStyle); sheet.getRange(0, 2, 1, 3).setBorder(lineBorder, { outline: true }, sheetArea); lineBorder = new GC.Spread.Sheets.LineBorder('red', lineStyle); sheet.getRange(2, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea); lineBorder = new GC.Spread.Sheets.LineBorder('green', lineStyle); sheet.getRange(5, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea); lineBorder = new GC.Spread.Sheets.LineBorder('blue', lineStyle); sheet.getRange(8, 1, 11, 6).setBorder(lineBorder, { outline: true }, sheetArea); }
<!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> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; border: 1px solid gray; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }