组成瀑布函数迷你图使用垂直或水平条来显示数据,这样可以更快捷的比较数值。
通过CascadeSparkline函数可以创建组成瀑布函数迷你图
CascadeSparkline函数有以下参数:
Option | Description |
pointsRange | 表示所描述的数据区域,比如 "B2:B8". |
pointIndex | 类型为数字或者引用表示某个数据点在所有数据点中的索引,而且这个索引大于等于1,比如1 或者 "D2"。 |
labelsRange | (可选参数) 类型为一个引用,所引用的这片区域的单元格包含了所有数据点的标注 例如:"A2:A8"; 默认值是空。 |
minimum | (可选参数)类型为数字或者引用,表示迷你图的展示区域的最小值,比如 -2000;默认值是所有数据值依次累加,在累加过程中的最小值。 |
maximum | (可选参数)类型为数字或者引用,表示迷你图的展示区域的最大值,比如 6000;默认值是所有数据值依次相加,在累加过程中的最大值。 |
colorPositive | (可选参数)类型为字符串,表示第一个和最后一个正向盒子(正向盒子代表这个数据点的值是正的)的颜色的字符串;默认值是 "#8CBF64"。 |
colorNegative | (可选参数)类型为字符串,表示第一个和最后一个负向盒子(负向盒子代表这个数据点的值是负的)的颜色的字符串;默认值是 "#D6604D"。 |
vertical | (可选参数)类型为布尔值,表示是否让这个盒子垂直显示;默认值是 false 。 |
你可以通过组成瀑布函数创建一个组成瀑布函数迷你图。比如:
=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical)
下面代码展示了如何创建组成瀑布函数迷你图
activeSheet.addSpan(0, 0, 1, 3); activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).value("A salesman's expenses and pay").font("20px Arial").hAlign(GC.Spread.Sheets.HorizontalAlign.center).vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("purple").foreColor("white"); activeSheet.getRange(1, 2, 7, 1, GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("transparent", GC.Spread.Sheets.LineStyle.thin),{ inside: true }); activeSheet.setValue(1, 0, "Salary"); activeSheet.setValue(2, 0, "Performance pay"); activeSheet.setValue(3, 0, "Pay for customers"); activeSheet.setValue(4, 0, "Food spending"); activeSheet.setValue(5, 0, "Hotel expenses"); activeSheet.setValue(6, 0, "Financial management"); activeSheet.setValue(7, 0, "Deposit"); activeSheet.setValue(1, 1, 3500); activeSheet.setValue(2, 1, 2500); activeSheet.setValue(3, 1, -1000); activeSheet.setValue(4, 1, -1000); activeSheet.setValue(5, 1, -900); activeSheet.setValue(6, 1, 300); activeSheet.setFormula(7, 1, '=sum(B2:B7)'); activeSheet.setColumnWidth(0,150); activeSheet.setColumnWidth(1,100); activeSheet.setColumnWidth(2, 340); activeSheet.setRowHeight(0, 30); activeSheet.setRowHeight(1, 30); activeSheet.setRowHeight(2, 30); activeSheet.setRowHeight(3, 30); activeSheet.setRowHeight(4, 30); activeSheet.setRowHeight(5, 30); activeSheet.setRowHeight(6, 30); activeSheet.setRowHeight(7, 30); activeSheet.setFormula(1, 2, '=CASCADESPARKLINE(B2:B8,1,A2:A8,,,"#8CBF64","#D6604D",false)'); activeSheet.setFormula(2, 2, '=CASCADESPARKLINE(B2:B8,2,A2:A8,,,"#8CBF64","#D6604D",false)'); activeSheet.setFormula(3, 2, '=CASCADESPARKLINE(B2:B8,3,A2:A8,,,"#8CBF64","#D6604D",false)'); activeSheet.setFormula(4, 2, '=CASCADESPARKLINE(B2:B8,4,A2:A8,,,"#8CBF64","#D6604D",false)'); activeSheet.setFormula(5, 2, '=CASCADESPARKLINE(B2:B8,5,A2:A8,,,"#8CBF64","#D6604D",false)'); activeSheet.setFormula(6, 2, '=CASCADESPARKLINE(B2:B8,6,A2:A8,,,"#8CBF64","#D6604D",false)'); activeSheet.setFormula(7, 2, '=CASCADESPARKLINE(B2:B8,7,A2:A8,,,"#8CBF64","#D6604D",false)');
See Also
setFormula Method