观远 BI

复杂报表列总计的不同计算方式

创建于 2022-11-01 / 最近更新于 2023-09-19 / 4702
字体: [默认] [大] [更大]

以下复杂报表案例皆需使用新版在线编辑模式公式,在3.9.0及之后的版本中均可使用。

案例一:数据纵向扩展,无横向扩展,无小计计算

image.png      

      以上图为例,4个字段都设置纵向扩展,其中“事业部、收入、成本”都是直接引用了视图里的字段,“毛利率”使用了格间计算公式 =G_CELLCALCEXP,计算方式为 (收入-成本)/收入。计算总计时,收入和成本都只需要对当列数据求和,毛利率总计需要基于同行里计算出来的收入总计和成本总计来计算,不需要用到其他数据,计算方式和同列上面各事业部毛利率计算方式一致。
此时,收入和成本总计直接使用 Excel 内置函数 SUM( ), 里面输
入要计算单元格的位置或范围;毛利率直接复制上面毛利率计算的公式即可。

image.png

案例二:数据横向扩展,允许纵向扩展和小计计算

image.png

      例如上图,“区域”列为纵向扩展,右侧月份横向扩展,“零售流水”和“目标”按照月份横向扩展,下面的“总计”也需要按照月份横向扩展。最终通过筛选器来筛选年或者月份来控制扩展范围。此时使用列总计公式 =G_GRANDTOTAL(视图名,派生字段1|字段2...,数值字段个数,范围) 可以实现自动横向扩展计算总和。具体用法如下:

image.png

公式解释:=G_GRANDTOTAL(月份,月,2,J3:J3) “月份”代表派生表的来源视图名;“月”字段来源于视图“月份”,用“月”生成派生表(对原表该字段进行去重),用来控制要扩展的次数,例如视图里有12个月,就会横向扩展12次;2代表需要计算“零售流水”和“目标”两个数值指标;J3:J3 代表数值的统计范围。

案例三:数据纵向扩展,有小计计算,无横向扩展

image.png

      上图例子中,左侧三列为维度字段,向下纵向扩展;中间“在库信息”、“昨日出库”、“近7日日均出库”共7列数据都是引用视图中计算好的数据,最后两列采用的格间计算(可用数量/昨日出库;可用数量/近7日日均出库;公式:=G_CELLCALCEXP(if(iserror(D{r}/I{r}),"-",D{r}/I{r})));同时,对每种物料也要计算小计。

这种场景里,如果仿照案例一用法,在总计行中间7列填充使用 sum(D3), sum(D4) 或者 sum(D3:D4),计算出来的总计数据都是错误的。效果参考下图

image.png

      如果仿照案例二使用G_GRANDTOTAL 公式,则会发现,该公式必须使用派生表字段,无论使用视图里哪个字段都会横向扩展,且扩展列数不符合预期,甚至会影响后面其他公式计算结果。

      下图使用公式 =G_GRANDTOTAL(总数量,物料编码,1,D3:D3) ,随着筛选的物料编码个数增多,右侧也会额外增加更多列,且可以发现“可售天数_昨日”和“可售天数_近7日”原本设置的格间计算公式没有生效,实际计算结果是上面数值求和。

      这是因为该场景并不需要横向扩展,但是 =G_GRANDTOTAL(总数量,物料编码,1,D3:D3) 会按照视图“总数量”里字段“物料编码”去重后的个数来横向扩展,个数超过7就会把格间计算的公式给覆盖掉,且超出设计的表格边界显示。

image.png

解决办法

方法一:不需要横向扩展的场景,其实扩展列数是1,那就要求派生字段必须是唯一值。可以在任一视图里新建一个固定字段,内容不限,然后拖到维度栏。公式写法:=G_GRANDTOTAL(总数量,辅助列,7,D3:D3) 其中 7代表有 7个数值字段需要参与求和计算;也可以使用 =G_GRANDTOTAL(总数量,辅助列,1,D3:D3) 分别填充到7个单元格里,D3:D3替换成所在列范围。

image.png

方法二:新建或复制一个视图,直接计算出数据总和,筛选条件要和上面要统计的数据视图保持一致;如果数据来源于不同视图,则需要新建多个视图。在线模板里,直接从左侧视图列表里把字段拖到总计行单元格里去即可。

image.png

案例四:多次横向扩展和固定列组合使用

图片.png

      上图案例里有2次分开的横向扩展,两个小计列为跟随在横向扩展后的固定列。如果使用G_GRANDTOTAL 公式,则只有第一次横向扩展可以正确计算,第二次扩展使用G_GRANDTOTAL则预览报错。因为列总计(G_GRANDTOTAL)公式里需要指定计算范围,第一次动态扩展时,计算范围的初始位置是正确的,动态扩展后计算的范围也是准确的;但是从第二次扩展开始,要计算的范围已经发生动态变化,但系统无法识别变化后的范围,导致计算失败。如果使用Excel 函数SUM(), 又无法横向扩展。

解决办法

图片.png

     第一次横向扩展依然使用 G_GRANDTOTAL之后的横向扩展使用 G_LOOKUPEXP/G_CELLCALCEXP 这类派生表公式,固定列求和使用 G_LOOKUP/SUM() 或者其他公式。

用法解释:

G_LOOKUPEXP/G_LOOKUP:需要指定查询字段,如果无查询条件,可以在视图里新建常量辅助列(例如文本常量字段「总计」)来作为查询条件。适用场景广,适合任何使用G_GRANDTOTALSUM 无法计算的场景。

G_CELLCALCEXP:格间计算,设置动态属性后,可弥补Excel原生函数不能横向扩展的缺陷。本案例里,嵌套使用的SUM() 不能使用固定范围D3:D3, 改为使用位移坐标。设置动态属性注意点:需要手动指定派生表来控制列数;需要勾选“不额外添加行列数”。

图片.png

79 人点赞过