[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
组成瀑布迷你图通常用于分析随时间变化的值,例如年销售额,总利润,净税等。它被广泛用于金融,销售,法律和建筑等行业。
下面列出了CASCADESPARKLINE公式的不同参数:
选项 | 描述 |
---|---|
pointsRange | 表示包含值的单元格范围的引用,例如“ B2:B8”。 |
pointIndex | 代表点索引的数字或参考。 pointIndex>=1,例如1或“ D2”。 |
labelsRange | 表示包含标签的单元格范围的引用,例如“ A2:A8”。 此设置是可选的。 默认值为无标签。 |
minimum | 代表显示区域最小值的数字或参考。 此设置是可选的。 默认值是总和(点的总和)的最小值,例如-2000。 您设置的最小值必须小于默认最小值。 否则,将使用默认的最小值。 |
maximum | 代表显示区域最大值的数字或参考。 此设置是可选的。 默认值为总和(点的总和)的最大值,例如6000。您设置的最大值必须大于默认的最大值;否则,最大值为0。 否则,使用默认最大值。 |
colorPositive | 一个字符串,代表第一个或最后一个正形迷你图框的颜色(此点的值为正)。 此设置是可选的。 默认值为“#8CBF64”。 如果第一个或最后一个框表示正值,则框的颜色将设置为colorPositive。 中间的正框设置为比colorPositive浅的颜色。 |
colorNegative | 一个字符串,代表第一个或最后一个负迷你图的框的颜色(此点的值为负)。 此设置是可选的。 默认值为“#D6604D”。 如果第一个或最后一个框代表负值,则该框的颜色将设置为colorNegative。 中间的负色框设置为比colorNegative浅的颜色。 |
vertical | 表示框的方向是垂直还是水平的布尔值。 此设置是可选的。 默认值为FALSE。 您必须将一组公式的垂直设置为true或false,因为所有公式都代表整个迷你图。 |
itemTypeRange | 表示数据范围的所有项目类型的数组或引用。 值应为{“-”,“ +”,“ =”}或“ A1:A7”,它们引用{“ +”,“-”,“ =”}“的值,其中“ +”表示 正变化,“-”表示负变化,“ =“表示总列数。 此设置是可选的。 |
colorTotal | 一个字符串,当itemTypeRange不存在时,代表最后一个迷你图的框的颜色;或者在itemTypeRange存在时,代表最终的迷你图的框的颜色。 此设置是可选的。 |
根据CASCADESPARKLINE公式中使用的参数,组成瀑布函数迷你图有不同用例。 下一节讨论了两个这样的用例。
用例 1
组成瀑布函数迷你图可用于使用迷你图来描述“推销员的费用和付款”,其中CASCADESPARKLINE公式与以下参数一起使用。
= 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)');
用例 2
级联迷你图还可以用于专门描述初始值如何增加和减少,从而达到最终值。 为此,您可以在Cascade公式中使用 itemTypeRange  参数。 itemTypeRange参数在会计和财务应用程序中非常有用,可显示导致组织产生的净流入和流出。
CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange, colorTotal)
请参考以下示例,在表单的E2:E15单元格区域中已应用 itemTypeRange 参数,其中CASCADESPARKLINE公式用于显示 公司的净销售额。 “营业利润”,“税前利润”和“净利润”的斜线表示总列,这就是为什么它们的对应itemType为“ =”。 具有正负变化的闪烁线用“ +”和“-” itemType表示。
在此用例中,以下代码使用公式创建级联迷你图。
// 在单元格中设置文本
sheet.setText(0, 1, '000$');
sheet.setText(0, 2, 'LABELS');
sheet.setText(0, 3, 'ITEMS');
sheet.setText(0, 4, 'ITEM TYPE');
// 创建displayLabels数组
var displayLabels = [
'CONSOLIDATED PROFIT & LOSS ACCOUNT', 'Net Sales',
' Cost of Sales', ' Research and development', ' Selling, general and administrative', ' Customer Finance impairment',
' Impairment of goodwill', ' Amortization of goodwill', 'Operating Profit', ' Share of results of associates', ' Financial income and expenses',
'Profit before tax', ' Tax', ' Minority interests', 'Net Profit'
];
// 创建项目数组
var items = [
29616, 17278, 7052, 6239, 292, 103, 206, 1554, 3300, 1506, 3252, 1484, 92, 1676
];
// 创建标签数组
var labels = [29616, 12338, 5286, -953, -1245, -1348, -1554, 1554, 4854, 6360, 3252, 1768, 1676, 1676
];
// 创建itemTypes数组
var itemTypes = [
'+', '-', '-', '-', '-', '-', '-', '=', '+', '+', '=', '-', '-', '='
];
// 设置数组
sheet.setArray(0, 0, displayLabels);
sheet.setArray(1, 2, items);
sheet.setArray(1, 3, labels);
sheet.setArray(1, 4, itemTypes);
// 设置CASCADESPARKLINE公式
sheet.setFormula(1, 1, '=CASCADESPARKLINE(D2:D15, SEQUENCE(COUNTA(D2:D15),1,1,1),C2:C15,,,"#66C2A5","#FF7722",false,E2:E15,"#A9A9A9")'0);