パレートスパークライン

パレートスパークラインを使用すると、一式の値の中から最も重要な項目を強調表示できます。

数式にParetoSparkline関数を使用することで、パレートスパークラインを作成できます。構文は次のとおりです。=PARETOSPARKLINE(points, pointIndex, colorRange?, target?, target2?, highlightPosition?, label?, vertical?) 有効なパラメータは次のとおりです。 points: 表示するすべての値を保持するセル範囲を表す参照。「B2:B7」など。 pointIndex: データ点に対応するインデックスを表す数値または参照。1以上の値となります。「1」、「D2」など。 colorRange: (オプション)セグメントボックスの色を保持するセル範囲を表す参照。「D2:D7」など。デフォルト値はnullです。 target: (オプション)目標線の位置を表す数値または参照。「0.5」など。デフォルト値はnullです。 target2: (オプション)第2目標線の位置を表す数値または参照。「0.5」など。デフォルト値はnullです。 highlightPosition: (オプション)赤色で表示するセグメントの順位を表す数値または参照。「3」など。デフォルト値はnullです。 label: (オプション)セグメントのラベルを累積パーセンテージとして表示するのか(label = 1)、単一のパーセンテージとして表示するのか(label = 2)、または表示しないか(label = 0)を表す数値。デフォルト値は「なし(0)」です。 vertical: (オプション)ボックスの向きを垂直方向にするかどうかを表すブール値。デフォルト値はfalseです。 targetColor: (オプション) 目標線の色を表す文字列。 target2Color: (オプション) 第2目標線の色を表す文字列。 labelColor: (オプション) ラベルの前景色を表す文字列。 barSize: (オプション) セルの幅または高さに応じてバーの幅または高さをパーセンテージで表す数値。0から1までの範囲内である必要があります。
var spreadNS = GC.Spread.Sheets, spread; window.onload = function () { spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); document.getElementById("labelType").addEventListener('change',applyChanges); document.getElementById("highlightPosition").addEventListener('change',applyChanges); }; function applyChanges() { var sheet = spread.getActiveSheet(); var labelType = parseInt(document.getElementById("labelType").value, 10), highlightPosition = parseInt(document.getElementById("highlightPosition").value, 10); if (!isNaN(labelType) && !isNaN(highlightPosition)) { var formulaPart1, formulaPart2; if (spread.getActiveSheetIndex() == 0) { formulaPart1 = "=PARETOSPARKLINE(B2:B7,", formulaPart2 = ",C2:C7,B8,B9," + highlightPosition + "," + labelType + ",false,B10,B11,C2:C7,D2:D7)"; for (var i = 1; i <= 6; i++) { var f = formulaPart1 + i + formulaPart2; sheet.setFormula(i, 4, formulaPart1 + i + formulaPart2); } } else { formulaPart1 = "=PARETOSPARKLINE(A3:F3,", formulaPart2 = ",A4:F4,B8,B9," + highlightPosition + "," + labelType + ",true,B10,B11,A4:F4,A5:F5)"; for (var i = 1; i <= 6; i++) { var f = formulaPart1 + i + formulaPart2; sheet.setFormula(5, i - 1, formulaPart1 + i + formulaPart2); } } } } function initSpread(spread) { spread.options.newTabVisible = false; initHorizontalSparkline(spread.sheets[0], "Horizontal"); initVerticalSparkline(spread.sheets[1], "Vertical"); }; function initHorizontalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 1, 5); sheet.getCell(0, 0).value("The Reason of Being Late") .font("20px Arial") .hAlign(spreadNS.HorizontalAlign.center) .vAlign(spreadNS.VerticalAlign.center) .backColor("purple") .foreColor("white"); sheet.getRange(1, 2, 6, 1).setBorder(new spreadNS.LineBorder("transparent", spreadNS.LineStyle.thin), { inside: true }, spreadNS.SheetArea.viewport); sheet.setValue(1, 0, "Traffic"); sheet.setValue(2, 0, "Child care"); sheet.setValue(3, 0, "Public transportation"); sheet.setValue(4, 0, "Weather"); sheet.setValue(5, 0, "Overslept"); sheet.setValue(6, 0, "Emergency"); sheet.setValue(7, 0, "target"); sheet.setValue(8, 0, "target2"); sheet.setValue(9, 0, "targetColor"); sheet.setValue(10, 0, "target2Color"); sheet.setValue(1, 1, 20); sheet.setValue(2, 1, 15); sheet.setValue(3, 1, 13); sheet.setValue(4, 1, 5); sheet.setValue(5, 1, 4); sheet.setValue(6, 1, 1); sheet.setValue(7, 1, 0.5); sheet.setValue(8, 1, 0.8); sheet.setValue(9, 1, "#006400"); sheet.setValue(10, 1, "#00BFFF"); sheet.setValue(1, 2, "#FF1493"); sheet.setValue(2, 2, "#FFE7BA"); sheet.setValue(3, 2, "#FFD700"); sheet.setValue(4, 2, "#FFAEB9"); sheet.setValue(5, 2, "#FF8C69"); sheet.setValue(6, 2, "#FF83FA"); sheet.setValue(1, 3, 0.1); sheet.setValue(2, 3, 0.3); sheet.setValue(3, 3, 0.5); sheet.setValue(4, 3, 0.7); sheet.setValue(5, 3, 0.9); sheet.setValue(6, 3, 1); sheet.addSpan(7, 2, 4, 3); sheet.getCell(7, 2).wordWrap(true); sheet.setValue(7, 2, 'Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are "traffic", "child care" and "public transportation".'); sheet.setColumnWidth(0, 140); sheet.setColumnWidth(1, 80); sheet.setColumnWidth(2, 80); sheet.setColumnWidth(3, 80); sheet.setColumnWidth(4, 340); sheet.setRowHeight(0, 30); sheet.setRowHeight(1, 30); sheet.setRowHeight(2, 30); sheet.setRowHeight(3, 30); sheet.setRowHeight(4, 30); sheet.setRowHeight(5, 30); sheet.setRowHeight(6, 30); sheet.setRowHeight(7, 30); for (var index = 0; index < 7; index++) { sheet.setFormula(index, 4, '=PARETOSPARKLINE($B$2:$B$7,ROW()-1,$C$2:$C$7,$B$8,$B$9,4,2,false,$B$10,$B$11,C2:C7,D2:D7)'); } sheet.setText(0, 1, "Points", spreadNS.SheetArea.colHeader); sheet.setText(0, 2, "Color range", spreadNS.SheetArea.colHeader); sheet.resumePaint(); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 1, 6); sheet.getCell(0, 0).value("The Reason of Being Late") .font("20px Arial") .hAlign(spreadNS.HorizontalAlign.center) .vAlign(spreadNS.VerticalAlign.center) .backColor("purple") .foreColor("white"); sheet.getRange(4, 0, 1, 6).setBorder(new spreadNS.LineBorder("transparent", spreadNS.LineStyle.thin), { inside: true }, spreadNS.SheetArea.viewport); sheet.setValue(1, 0, "Traffic"); sheet.setValue(1, 1, "Child care"); sheet.setValue(1, 2, "Public transportation"); sheet.setValue(1, 3, "Weather"); sheet.setValue(1, 4, "Overslept"); sheet.setValue(1, 5, "Emergency"); sheet.setValue(7, 0, "target"); sheet.setValue(8, 0, "target2"); sheet.setValue(9, 0, "targetColor"); sheet.setValue(10, 0, "target2Color"); sheet.setValue(2, 0, 20); sheet.setValue(2, 1, 15); sheet.setValue(2, 2, 13); sheet.setValue(2, 3, 5); sheet.setValue(2, 4, 4); sheet.setValue(2, 5, 1); sheet.setValue(7, 1, 0.5); sheet.setValue(8, 1, 0.8); sheet.setValue(9, 1, "#006400"); sheet.setValue(10, 1, "#00BFFF"); sheet.setValue(3, 0, "#FF1493"); sheet.setValue(3, 1, "#FFE7BA"); sheet.setValue(3, 2, "#FFD700"); sheet.setValue(3, 3, "#FFAEB9"); sheet.setValue(3, 4, "#FF8C69"); sheet.setValue(3, 5, "#FF83FA"); sheet.setValue(4, 0, 0.1); sheet.setValue(4, 1, 0.3); sheet.setValue(4, 2, 0.5); sheet.setValue(4, 3, 0.7); sheet.setValue(4, 4, 0.9); sheet.setValue(4, 5, 1); sheet.addSpan(7, 2, 2, 4); sheet.getCell(7, 2).wordWrap(true); sheet.setValue(7, 2, 'Result: By the sparkline above can draw a conclusion that the reasons for 80% of the employees be late are "traffic", "child care" and "public transportation".'); sheet.setColumnWidth(0, 80); sheet.setColumnWidth(1, 100); sheet.setColumnWidth(2, 140); sheet.setColumnWidth(3, 100); sheet.setColumnWidth(4, 100); sheet.setColumnWidth(5, 100); sheet.setRowHeight(5, 160); sheet.setRowHeight(7, 30); sheet.setRowHeight(8, 30); for (var index = 0; index < 6; index++) { sheet.setFormula(5, index, '=PARETOSPARKLINE($A$3:$F$3,COLUMN(),$A$4:$F$4,$B$8,$B$9,4,2,true,$B$10,$B$11,A4:F4,A5:F5)'); } sheet.resumePaint(); }
<!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"> <div class="option-group"> <label for="labelType">ラベルの種類:</label> <select id="labelType"> <option value="0">なし</option> <option value="1">累積百分率</option> <option value="2" selected>百分率</option> </select> </div> <div class="option-group"> <label for="highlightPosition" >強調する項目:</label> <select id="highlightPosition"> <option value="0">None</option> <option value="1">1st</option> <option value="2">2nd</option> <option value="3">3rd</option> <option value="4" selected>4th</option> <option value="5">5th</option> <option value="6">6th</option> </select> </div> </div> </div> </body> </html>
.sample { position: relative; height: 100%; overflow: auto; } .sample::after { display: block; content: ""; clear: both; } .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; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } .option-group { margin-bottom: 6px; } label { display: inline-block; min-width: 90px; margin-bottom: 6px; } select { padding: 4px 6px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }