中国式报表Pro--动态格间计算

背景：

      格间计算，即不同单元格之间的引用或者计算，例如 C2/D2，SUM(C2)。使用观远BI的中国式报表Pro制作报表时，格间计算的单元格转换为模板单元格并设置父格后，可以跟随父格动态扩展。但是有些场景下格间计算会因为引用的单元格位置不固定而无法计算或者计算错误。本文将介绍在中国式报表Pro里如何进行动态格间计算。

案例：

      以下为一张销售报表，纵向表头为大区和省份，横向表头为商品分类，展示了每个大区、省份下每个商品分类的销售额，其中地区占比、品类占比和年同比是用格间计算实现的。计算逻辑和模板如下图，但是预览后发现很多计算结果错误。

校验数据：

      点击「公式--显示公式」，然后预览，可以校验动态扩展后Excel公式实际计算的单元格位置是否符合预期。

      显示公式后，发现「地区占比」「品类占比」（红框部分）计算的分母都偏移了，计算结果错误，只有「年同比」（绿框部分）计算正确。

分析：

      格间计算里分子分母都跟随格间计算的父格扩展并偏移了，但实际分子、分母引用的单元格的父格可能不同，扩展不应该同步。例如「地区占比」里的大区占比 C4 ：=B4/B3 ，分子B4和当前单元格C4都跟随父格A4（大区）纵向扩展，但是分母B3是固定位置的单元格，一旦跟着偏移就错了。相反，「年同比」和它的分子分母都在同一行，且三者父格相同，所以扩展是完全同步的，单元格间距相对固定，计算结果就是正确的。

解决方案：

1. 对位置固定的单元格，添加$符号来切换成绝对引用或混合引用。

      Excel单元格引用方式参考下图：

      「地区占比」里的大区占比 C4 ：=B4/B3 需要改为 =B4/$B$3 ，因为分子B4和C4都跟随父格A4（大区）纵向扩展，但是分母B3不扩展，位置固定不变，用$B$3改为绝对引用。

      「品类占比」E5：=D5/B5 需要改为 =D5/$B5 ，因为分子D5和E5都跟随父格A5和D1 同时纵向+横向扩展，但分母B5仅跟随父格A5纵向扩展，一直保持在B列，用$B5改为混合引用。E3、E4同理，分母都要改为混合引用。

2. 对扩展不同步的单元格，添加辅助列实现扩展同步

      「地区占比」里C5计算的是省份对比大区的占比，分子分母2个单元格父格不同，扩展不同步，无论使用绝对引用还是相对引用，都无法准确定位到扩展后分母所在的单元格。针对单元格扩展不同步的格间计算，可以通过添加辅助列，借助Excel函数计算，再隐藏辅助列来实现。

实现步骤：

      在「地区占比」前插入一列，在单元格输入公式，设置为模板单元格，并设置父格，实现把分母重复填充到每一行，使分子分母位于同一行，同步扩展，相除计算得到「地区占比」。写法参考下图。

C4：{{==B4(S=None,C=A4)}}  或者 {{=SUM(ds1.销售金额)(S=None,C=A4)}}
C5：{{==C4(S=None,C=A5)}}
D5：{{==B5/C5(S=None,C=A5)}}

预览效果如下图，预览无误后把这个辅助列隐藏即可。

最终模板和预览效果参考下图。

效果预览：

小技巧：

      格间计算里除法的分母为空或0时，计算结果会显示为 #DIV/0! ，此时可以使用Excel 函数=IFERROR(value, value_if_error) 来替换掉错误值（例如：#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？或 #NULL！）。

重要提醒：

      观远BI 7.0版本对中国式报表Pro做了重大升级，对格间计算逻辑进行了优化，部分场景里不需要添加$符号和辅助列也可以正确计算。7.0以下的版本推荐以上用法，后续即使升级到7.0也是兼容的，仍可以正常计算和使用。