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

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

案例一：数据纵向扩展，无横向扩展，无小计计算

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

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

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

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

案例三：数据纵向扩展，有小计计算，无横向扩展

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

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

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

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

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

解决办法

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

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

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

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

解决办法

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

用法解释：

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

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