配列数式の用法

配列数式を使用することで、強力でありながらシンプルなワークシートを構成できます。ここでは、配列数式の用法を示すいくつかの例を紹介します。

<p>配列数式は、いくつもの数式を使用する代わりに、行および列内のセルに対して一括して計算を実行します。次に、例を示します。</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// 全員の合計得点を計算する。</span> sheet.setArrayFormula(<span class="hljs-number">1</span>, <span class="hljs-number">5</span>, <span class="hljs-number">6</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'=SUBTOTAL(9,OFFSET($B$2,ROW($A$1:$A$6)-ROW($A$1),,1,3))'</span>) </code></pre> <p>一定範囲にわたるアイテムを検索する場合に、配列数式を使用できます。次に、例を示します。</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// A1:A9にデータを含まない、B1:B5の最初の値を探します。</span> sheet.setArrayFormula(<span class="hljs-number">2</span>, <span class="hljs-number">5</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'=INDEX(B1:B5,MATCH(TRUE,ISNA(MATCH(B1:B5,A1:A9,0)),0))'</span>) </code></pre> <p>配列数式を使用することで、特定範囲を対象とし、関連する値に基づく統計情報を収集できます。次に、例を示します。</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// 売上合計:Brownが売ったFax。</span> sheet.setArrayFormula(<span class="hljs-number">2</span>, <span class="hljs-number">5</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'=SUM((A2:A10="Fax")*(B2:B10="Brown")*(C2:C10))'</span>); <span class="hljs-comment">// 論理和(Fax AND Brown)。</span> sheet.setArrayFormula(<span class="hljs-number">5</span>, <span class="hljs-number">5</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'=SUM((A2:A10="Fax")*(B2:B10="Brown"))'</span>); </code></pre>
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"], ["noodel"], ["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, "NomalFormula 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 NAND (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="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 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; }