跳到主要内容
版本：6.3.0

复杂报表交替插入数据案例分享

场景​

在案例复杂报表实现多层表头横向扩展的场景里，只写了一列公式，就实现了多个字段横向扩展的效果。但是这些数值字段只能采用统一的计算方式和数值格式，如果遇到以下任何一种情况，就必须为每个数值字段单独设置一列公式，然后设置交替插入数据。

  1. 要扩展的多个数值字段的内容来源于不同的视图；

  2. 要扩展的多个数值字段需要用不同的计算方式（例如求和、平均值、最大值，或者格间计算）；

  3. 多个数值字段要设置不同的格式（例如金额、百分比）

本文基于上个案例同一个场景，需要横向扩展出每个月每个城市线的零售流水、目标，以及完成率。最终效果如下图。

image.png

实现方法​

主要通过在复杂报表里使用公式并配合设置“动态属性”来实现横向或者纵向扩展时交替插入数据。

image.png

实现步骤​

数据准备​

新建复杂报表，新建视图，把需要呈现的必要数据先计算出来。此案例里横线扩展部分数据“流水”来源于视图“流水”里的“累计流水”， “目标”来源于另一个视图“目标”里的“累计目标”，“完成率”计算方式为累计流水/累计目标，不能直接在视图里计算得到。这里筛选了日期范围“2019年”和城市线“一线”和“二线”。

image.png

模板编辑​

A. 表头部分。进入模板编辑界面，该例子里横向扩展从 J 列开始，表头部分公式使用方法参考 复杂报表实现多层表头横向扩展。因为有三个数值字段，两个城市线，所以第一层表头“月份”需要合并6列，第二层“城市线”需要合并3列，第三层在公式里列出三个字段要显示的文本表头。三个公式都放在同一列，动态属性不需要设置，后面新增两列，表头单元格留空。（注：下图列宽非最终效果）

image.png

B. 数值部分

1) 流水：

公式：=G_LOOKUPEXP(9,流水,月|城市线,流水,累计流水,大区:A{r},月:J1,城市线:J2)**

动态属性：勾选“间隔扩展数值列”，“视图名”和“间隔扩展字段”要和公式里派生表视图名和派生字段保持一致；“间隔”填3（包含当前列）；勾选“不额外添加行列数”，如果不勾选，则会在这一列扩展结束后才会开始扩展后面字段，会插入大量空白列。

2) 目标：

公式：=G_LOOKUPEXP(9,流水,月|城市线,目标,累计目标,大区:A{r},月:J1,城市线:J2)**

动态属性：同“流水”

3) 完成率：

公式：=G_CELLCALCEXP(if(iserror({-2}{r}/{-1}{r}),"--",{-2}{r}/{-1}{r}))******

正常情况下分母为0或空值会导致除法报错，用 iserror 函数判断报错，用 if 函数把报错转换为“--”； {-2}{r} 代表当前使用公式的单元格所在行向前偏移2列，对应“流水”， {-1}{r} 代表当前使用公式的单元格所在行向前偏移1列，对应“目标”。格间计算具体表示方法请参考 复杂报表本地编辑模式公式说明。

动态属性：跟随A列维度字段“区域”设置纵向扩展，勾选“复制单元格样式”；勾选“间隔扩展数值列”；因为格间计算公式里没有用到派生表，这里需要指定用来生成派生表的视图名和字段，跟上面横向扩展表头和前面G_LOOKUPEXP公式里使用同一个派生表即可；**“间隔”填3（包含当前列）；作为横向扩展最后一列公式，不能勾选“不额外添加行列数”，勾选后可能会导致报表格式不生效或者直接报错（后续待优化）。

image.png

C. 列总计

横向扩展列总计一般使用G_GRANDTOTAL，但是该公式默认对数据求和，不能设置其他计算方式（例如平均值、最大值），本案例里“完成率”是格间计算出来的百分比，不能直接求和；另外，G_GRANDTOTAL会根据派生表自动横向扩展，且不支持设置间隔列扩展（交替插入计算），所以也不能对“流水”和“目标”使用G_GRANDTOTAL，单独对“完成率”使用格间计算公式（计算结果会被覆盖掉）。

下图为使用 =G_GRANDTOTAL(流水,月|城市线,3,J4:K4) 的计算效果。

image.png

推荐方法：

流水总计：=G_LOOKUPEXP(9,流水,月|城市线,流水,累计流水,月:J1,城市线:J2) 跟上面“流水”列计算方式相同，只是去掉了查询字段“大区”。两个公式单元格动态属性完全一致。

目标总计：=G_LOOKUPEXP(9,流水,月|城市线,目标,累计目标,月:J1,城市线:J2) 动态属性同上。

完成率总计：*=G_CELLCALCEXP(if(iserror({-2}{r}/{-1}{r}),"--",{-2}{r}/{-1}{r})) ***直接复制上面格间计算公式即可。

动态属性：只需要设置横向扩展；“间隔写入”设置为2（不包含当前列）； “不额外添加行列数”可勾选，也可不勾选（因为是本模板里最后一个扩展公式）。设置如下图。

image.png

预览保存​

为每一列单独设置数值格式和样式，点击“保存并继续”来进入下一步预览效果，然后再点击“模板编辑”回到模板上调整公式或者样式，直至达到需要的效果。最后点击“完成”按钮保存即可。