交叉表累计报表

概述

交叉表累计：按照从左到右对数据进行横向累加。

应用场景

如下图所示，统计不同商品各季度销售额的累计销售额

Preview

实现思路

通过下列公式实现交叉表累计计算
=B2+C2[;B1:-1]

示例说明

数据准备

在数据面板中添加数据集，可选择Json数据集和Http接口数据集，Json数据如下图所示：

[{"商品":"A产品","季度":1,"销售额":5890.56},{"商品":"A产品","季度":2,"销售额":4566.26},{"商品":"A产品","季度":3,"销售额":7861.56},{"商品":"A产品","季度":4,"销售额":5654.56},{"商品":"A产品","季度":1,"销售额":5820.96},{"商品":"A产品","季度":2,"销售额":5666.26},{"商品":"A产品","季度":3,"销售额":8661.56},{"商品":"A产品","季度":4,"销售额":5254.56},{"商品":"B产品","季度":1,"销售额":5890.56},{"商品":"B产品","季度":2,"销售额":4456.16},{"商品":"B产品","季度":3,"销售额":7561.56},{"商品":"B产品","季度":4,"销售额":3554.56},{"商品":"B产品","季度":1,"销售额":4620.96},{"商品":"B产品","季度":2,"销售额":5456.26},{"商品":"B产品","季度":3,"销售额":8891.56},{"商品":"B产品","季度":4,"销售额":5623},{"商品":"C产品","季度":1,"销售额":4600.78},{"商品":"C产品","季度":2,"销售额":5246},{"商品":"C产品","季度":3,"销售额":7761.56},{"商品":"C产品","季度":4,"销售额":8164},{"商品":"C产品","季度":1,"销售额":4645.56},{"商品":"C产品","季度":2,"销售额":6579},{"商品":"C产品","季度":3,"销售额":7591.56},{"商品":"C产品","季度":4,"销售额":7168},{"商品":"D产品","季度":1,"销售额":4678.56},{"商品":"D产品","季度":2,"销售额":6659},{"商品":"D产品","季度":3,"销售额":7567.56},{"商品":"D产品","季度":4,"销售额":7498},{"商品":"D产品","季度":1,"销售额":5246},{"商品":"D产品","季度":2,"销售额":4645},{"商品":"D产品","季度":3,"销售额":7561.8},{"商品":"D产品","季度":4,"销售额":7941},{"商品":"E产品","季度":1,"销售额":5890.56},{"商品":"E产品","季度":2,"销售额":4566.26},{"商品":"E产品","季度":3,"销售额":7861.56},{"商品":"E产品","季度":4,"销售额":5654.56},{"商品":"E产品","季度":1,"销售额":5820.96},{"商品":"E产品","季度":2,"销售额":5666.26},{"商品":"E产品","季度":3,"销售额":8661.56},{"商品":"E产品","季度":4,"销售额":5254.56},{"商品":"F产品","季度":1,"销售额":5890.56},{"商品":"F产品","季度":2,"销售额":4456.16},{"商品":"F产品","季度":3,"销售额":7561.56},{"商品":"F产品","季度":4,"销售额":3554.56},{"商品":"F产品","季度":1,"销售额":4620.96},{"商品":"F产品","季度":2,"销售额":5456.26},{"商品":"F产品","季度":3,"销售额":8891.56},{"商品":"F产品","季度":4,"销售额":5623},{"商品":"G产品","季度":1,"销售额":4600.78},{"商品":"G产品","季度":2,"销售额":5246},{"商品":"G产品","季度":3,"销售额":7761.56},{"商品":"G产品","季度":4,"销售额":8164},{"商品":"G产品","季度":1,"销售额":4645.56},{"商品":"G产品","季度":2,"销售额":6579},{"商品":"G产品","季度":3,"销售额":7591.56},{"商品":"G产品","季度":4,"销售额":7168},{"商品":"H产品","季度":1,"销售额":4678.56},{"商品":"H产品","季度":2,"销售额":6659},{"商品":"H产品","季度":3,"销售额":7567.56},{"商品":"H产品","季度":4,"销售额":7498},{"商品":"H产品","季度":1,"销售额":5246},{"商品":"H产品","季度":2,"销售额":4645},{"商品":"H产品","季度":3,"销售额":7561.8},{"商品":"H产品","季度":4,"销售额":7941}]

Preview

报表设计

将数据字段依次对应拖拽到对应单元格中，并将A2单元格中表格设置为分组以及向下扩展、B1与C1单元格合并后设置为分组以及向右扩展、B2单元格中表格设置为分组以及双向扩展，并把填入的单元格框选后添加分组。如下图所示：

Preview

交叉表累计的公式说明：
=B2+C2[;B1:-1]
C2[;B1:-1]：表示上父格 B1横向扩展出来的上一格（上季度）的 C2 的值；
B2+C2[;B1:-1]：表示当前季度的销售额加上上一季度累计销售额

效果预览

交叉表累计.json

Preview