AGGREGATE関数

AGGREGATE関数は、19の集計計算および関数オプションをサポートしています。

<p>SpreadJSは、集計操作を行うAGGREGATE関数をサポートしています。 構文はExcelと同じです。</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">//A1:C5の範囲に含まれる値の合計を返します。</span> =AGGREGATE(<span class="hljs-number">9</span>, <span class="hljs-number">4</span>, <span class="hljs-attr">A1</span>:C5) <span class="hljs-comment">//A1:C5の範囲の最大値を返します。</span> =AGGREGATE(<span class="hljs-number">4</span>, <span class="hljs-number">3</span>, <span class="hljs-attr">A1</span>:C5) <span class="hljs-comment">//A1:C5の範囲で2番目に大きい値を返します。</span> =AGGREGATE(<span class="hljs-number">14</span>, <span class="hljs-number">3</span>, <span class="hljs-attr">A1</span>:C5, <span class="hljs-number">2</span>) </code></pre> <p><strong>第1引数には目的の集計値を得るための計算の種類を1〜19の値で指定します。</strong></p> <table border="1" cellspacing="0" class='table-style'> <tr><th class = 'table-th'>集計方法</th><th class = 'table-th'>関数</th></tr> <tr><td class = 'table-td'>1</td><td class = 'table-td'>AVERAGE</td></tr> <tr><td class = 'table-td'>2</td><td class = 'table-td'>COUNT</td></tr> <tr><td class = 'table-td'>3</td><td class = 'table-td'>COUNTA</td></tr> <tr><td class = 'table-td'>4</td><td class = 'table-td'>MAX</td></tr> <tr><td class = 'table-td'>5</td><td class = 'table-td'>MIN</td></tr> <tr><td class = 'table-td'>6</td><td class = 'table-td'>PRODUCT</td></tr> <tr><td class = 'table-td'>7</td><td class = 'table-td'>STDEV.S</td></tr> <tr><td class = 'table-td'>8</td><td class = 'table-td'>STDEV.P</td></tr> <tr><td class = 'table-td'>9</td><td class = 'table-td'>SUM</td></tr> <tr><td class = 'table-td'>10</td><td class = 'table-td'>VAR.S</td></tr> <tr><td class = 'table-td'>11</td><td class = 'table-td'>VAR.P</td></tr> <tr><td class = 'table-td'>12</td><td class = 'table-td'>MEDIAN</td></tr> <tr><td class = 'table-td'>13</td><td class = 'table-td'>MODE.SNGL</td></tr> <tr><td class = 'table-td'>14</td><td class = 'table-td'>LARGE</td></tr> <tr><td class = 'table-td'>15</td><td class = 'table-td'>SMALL</td></tr> <tr><td class = 'table-td'>16</td><td class = 'table-td'>PERCENTILE.INC</td></tr> <tr><td class = 'table-td'>17</td><td class = 'table-td'>QUARTILE.INC</td></tr> <tr><td class = 'table-td'>18</td><td class = 'table-td'>APERCENTILE.EXC</td></tr> <tr><td class = 'table-td'>19</td><td class = 'table-td'>QUARTILE.EXC</td></tr> </table> <p><strong>第2引数には関数内の値を無視するオプションを指定します。</strong></p> <table border="1" cellspacing="0" class='table-style'> <tr><th class = 'table-th'>オプション</th><th class = 'table-th'>動作</th></tr> <tr><td class = 'table-td'>0または省略</td><td class = 'table-td'>ネストされたSUBTOTALおよびAGGREGATE関数を無視します。</td></tr> <tr><td class = 'table-td'>1</td><td class = 'table-td'>非表示の行、ネストされたSUBTOTALおよびAGGREGATE関数を無視します。</td></tr> <tr><td class = 'table-td'>2</td><td class = 'table-td'>エラー値、ネストされたSUBTOTALおよびAGGREGATE関数を無視します。</td></tr> <tr><td class = 'table-td'>3</td><td class = 'table-td'>非表示の行、エラー値、ネストされたSUBTOTALおよびAGGREGATE関数を無視します。</td></tr> <tr><td class = 'table-td'>4</td><td class = 'table-td'>何も無視しません。</td></tr> <tr><td class = 'table-td'>5</td><td class = 'table-td'>非表示の行を無視します。</td></tr> <tr><td class = 'table-td'>6</td><td class = 'table-td'>エラー値を無視します。</td></tr> <tr><td class = 'table-td'>7</td><td class = 'table-td'>非表示の行とエラー値を無視します。</td></tr> </table> <p>「function<code>_</code>num」に1~13の範囲内の値が含まれる場合、書式がAGGREGATE(function<code>_</code>num, options, ref1, [ref2], …) として適用されます。</p> <p>また、「function<code>_</code>num」に14~19の範囲内の値が含まれる場合、書式がAGGREGATE(function<code>_</code>num, options, array, k)として適用されます。</p> <pre><code class="hljs js language-js"> =AGGREGATE(function_num, options, ref1, [ref2], ...) <span class="hljs-comment">// function_num 1-13</span> =AGGREGATE(function_num, options, array, k) <span class="hljs-comment">// function_num 14-19</span> </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var salesData = [ ["SalesPers", "Birth", "Region", "SaleAmt", "ComPct", "ComAmt"], ["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, "データをフィルタリングし、式の結果の変更を確認します。"); sheet.setArray(2, 2, [["非表示の行を無視", "SUM ComAmt"], ["", "MAX ComAmt"], ["", ""], ["エラーを無視", "SUM ComAmt"], ["", "MAX ComAmt"]]); 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)"); initStyle(sheet); sheet.resumePaint(); }; function initStyle(sheet) { sheet.defaults.colWidth = 100; sheet.setColumnWidth(2, 140); sheet.getRange(9, 2, 10, 1).formatter("yyyy/mm/dd"); 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="ja-jp" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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; }