中国式报表Pro--偏移计算

函数介绍

案例一 横向扩展

场景：不同年份的商品销售金额按照年份横向扩展，要计算2023年的年同比增长率，计算方式为：2023年销售金额/2022年销售金额-1。

问题：

      参与计算的分子（2023年销售金额）和分母（2022年销售金额）来源于同一个模板单元格B3的横向扩展，行列都不固定，不能用 B3/B3-1 或者 $B3/B3-1 计算增长率。模板如下图。

解决方案：

      扩展后分子分母和增长率所在单元格（C3）之间的偏移量是固定的，可以使用OFFSET(C3,0,-2)/OFFSET(C3,0,-1) 来实现当前单元格C3左移2列的单元格除以C3左移1列的单元格，IFERROR用来替换数据无效导致的报错，最后把C3转换为模板单元格并设置父格即可。

=IFERROR(OFFSET(C3,0,-2)/OFFSET(C3,0,-1)-1,"-")

最终公式：
C3: {{==IFERROR(OFFSET(C3,0,-2)/OFFSET(C3,0,-1)-1,"-")(S=None,C=A3)}}

案例二 纵向分片扩展

场景：不同年份的商品销售金额按照年份纵向扩展，要计算2023年的年同比增长率，计算方式为：2023年销售金额/2022年销售金额-1。

问题：

      参与计算的分子（2023年销售金额）和分母（2022年销售金额）来源于不同视图，分别在B列跟随维度「大区」纵向扩展，行号都不固定，如果用 B2/B5-1 计算增长率错误。原始模板公式如下图。

实际计算逻辑如下，可以看到，总计的增长率计算正确，大区扩展的7行的增长率里分子正确，但分母并没有定位到正确的单元格。

解决方案：

      扩展后分子分母之间的偏移量是固定的，本案例里单元格间距是 9 =「大区」个数+2。如果A列「大区」不受筛选条件影响，保持扩展7行，可以使用OFFSET(B2,9,0) 来引用B2下移9行的单元格用作分母，最后转换为模板单元格并设置父格即可。

=B2/OFFSET(B2,9,0)-1

最终公式：
C2: {{==B2/OFFSET(B2,9,0)-1(S=None,C=A2)}}

      如果「大区」受筛选条件影响，扩展行数会动态变化，可以找一个位置固定、不受扩展影响、可以隐藏的空白单元格，例如 D1，用 COUNTA(A2) 或者 ROWS(A2) （无父格）把 「大区」扩展的行数先统计出来；再把C2里的分母替换为 OFFSET(B2,$D$1+2,0)；最后把D1和C2转换为模板单元格并设置父格。

=COUNTA(A2)             计算不为空的单元格个数
=ROWS(A2)               返回引用范围或数组的行数
=OFFSET(B2,$D$1+2,0)    引用B2下面 「大区」个数+2行的单元格

最终公式：
D1：{{==COUNTA(A2)(S=None)}} 或者 {{==ROWS(A2)(S=None)}} 
C2：{{==B2/OFFSET(B2,$D$1+2,0)-1(S=None,C=A2)}}

模板参考：

行数动态变化后预览效果：