Pareto

The pareto sparkline can be used to highlight the most important items in a set of values.

You can create a pareto sparkline using the ParetoSparkline function in a formula: =PARETOSPARKLINE(points, pointIndex, colorRange?, target?, target2?, highlightPosition?, label?, vertical?, targetColor?, target2Color?, labelColor?, barSize?). The function has the following parameters: points: Reference that represents the range of cells that contains the values, such as "B2:B7" pointIndex: Number or reference that represents the segment's index of the points, The pointIndex is >= 1 such as 1 or "D2". colorRange: (optional) Reference that represents the range of cells containing the color for the segment box, such as "D2:D7"; default value is null. target: (optional) Number or reference that represents the "target" line position, such as 0.5; default value is null. target2: (optional) Number or reference that represents the "target2" line position, such as 0.5; default value is null. highlightPosition: (optional) Number or reference that represents the rank of the segment to be colored in red, such as 3; default value is null. label: (optional) Number that represents whether the segment's label is displayed as the cumulated percentage (label = 1) or the single percentage (label = 2) or none (label = 0), default value is none. vertical: (optional) Boolean that represents whether the box's direction is vertical; default value is false. targetColor: (optional) String that represents the target line color. target2Color: (optional) String that represents the target2 line color. labelColor: (optional) String that represents the label fore color. barSize: (optional) Number that represents that the percentage of bar width/height according to the cell height/ width. (value > 0 && value <= 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); document.getElementById("target1").addEventListener('change',applyChanges); document.getElementById("target2").addEventListener('change',applyChanges); document.getElementById("color1").addEventListener('change',applyChanges); document.getElementById("color2").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) { var formula = '=PARETOSPARKLINE($B$3:$B$8,ROW()-2,$C$3:$C$8,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',' + highlightPosition + ','+ labelType +',false,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$C$3:$C$8,$D$3:$D$8)'; for (var i = 2; i <= 7; i++) { sheet.setFormula(i, 4, formula); } } else { var formulaVertical = '=PARETOSPARKLINE($B$3:$G$3,COLUMN()-1,$B$4:$G$4,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',' + highlightPosition + ','+ labelType +',true,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$B$4:$G$4,$B$5:$G$5)'; for (var index = 1; index < 7; index++) { sheet.setFormula(5, index, formulaVertical); } } } } 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.setArray(1, 0, [ ["Reason","Cases","Color","Bar Size","Diagram"], ["Traffic",20,"#CCCCCC",0.1], ["Child care",15,"#82BC00",0.3], ["Public transportation",13,"#F7A711",0.5], ["Weather",6,"#00C2D6",0.7], ["Overslept",4,"#FFE7BA",0.8], ["Emergency",1,"#000000",1] ]); sheet.setValue(10, 0, '*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".'); for (var index = 2; index < 8; index++) { sheet.setFormula(index, 4, '=PARETOSPARKLINE($B$3:$B$8,ROW()-2,$C$3:$C$8,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',0,2,false,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$C$3:$C$8,$D$3:$D$8)'); } //styling for(var i = 1; i<4; i++) { sheet.setColumnWidth(i, 80); } sheet.setColumnWidth(4, 340); sheet.setColumnWidth(0, 140); sheet.setRowHeight(0, 35); sheet.addSpan(0, 0, 1, 5); sheet.getCell(0, 0).value("Late Arrivals by Reported Cause") .font("17px Arial") .vAlign(spreadNS.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(2,0,6,4).hAlign(spreadNS.HorizontalAlign.left); sheet.getRange(1, 0, 1, 5) .font("bold 13px Arial") .setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.thin), { bottom: true }); sheet.resumePaint(); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.setArray(1, 0, [ ["Reason","Traffic","Child care","Public transportation","Weather","Overslept","Emergency"], ["Cases",20,15,13,5,4,1], ["Color","#CCCCCC","#82BC00","#F7A711","#00C2D6","#FFE7BA","#000000"], ["Bar Size",0.1,0.3,0.5,0.7,0.8,1], ["Diagram"] ]); sheet.setValue(8, 0, '*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".'); for (var index = 1; index < 7; index++) { sheet.setFormula(5, index, '=PARETOSPARKLINE($B$3:$G$3,COLUMN()-1,$B$4:$G$4,'+ document.getElementById('target1').value + ',' + document.getElementById('target2').value +',0,2,true,"'+ document.getElementById('color1').value + '","' + document.getElementById('color2').value +'",$B$4:$G$4,$B$5:$G$5)'); } //styling for(var i = 0; i<7; i++) { sheet.setColumnWidth(i, 120); } sheet.setColumnWidth(3, 150); sheet.setRowHeight(5, 160); sheet.setRowHeight(0, 35); sheet.addSpan(0, 0, 1, 7); sheet.getCell(0, 0).value("Late Arrivals by Reported Cause") .font("17px Arial") .vAlign(spreadNS.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(1, 0, 5, 1) .font("bold 13px Arial") .setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.thin), { right: true }); sheet.getRange(1,0,4,7).hAlign(spreadNS.HorizontalAlign.left); sheet.resumePaint(); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.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-row"> <label><b>Sparkline Settings:</b></label> </div> <hr> <div class="option-row"> <label for="labelType">Label type:</label> <select id="labelType"> <option value="0">None</option> <option value="1">Cumulated percentage</option> <option value="2" selected>Single percentage</option> </select> </div> <div class="option-row"> <label for="highlightPosition" >Highlight item:</label> <select id="highlightPosition"> <option value="0" selected>None</option> <option value="1">1st</option> <option value="2">2nd</option> <option value="3">3rd</option> <option value="4">4th</option> <option value="5">5th</option> <option value="6">6th</option> </select> </div> <div class="option-row"> <label for="target1">Target1:</label> <input type="text" style="margin: 0 20px 0 6px;width:120px;" id="target1" value="0.5"/> </div> <div class="option-row"> <label for="target2">Target2:</label> <input type="text" style="margin: 0 20px 0 6px;width:120px;" id="target2" value="0.8"/> </div> <div class="option-row"> <label for="color1">Target1 Color:</label> <select id="color1" style="margin: 0 20px 0 6px"> <option value="#BBBBBB" selected>Grey</option> <option value="#82BC00">Green</option> <option value="#000000">Black</option> <option value="#F7A711">Orange</option> </select> </div> <div class="option-row"> <label for="color2">Target2 Color:</label> <select id="color2" style="margin: 0 20px 0 6px"> <option value="#BBBBBB">Grey</option> <option value="#82BC00">Green</option> <option value="#000000">Black</option> <option value="#F7A711" selected>Orange</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; } select { padding: 4px 6px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }