1. 复杂报表公式说明

复杂报表（GuanReport）是嵌入在观远一站式智能数据分析平台中的一套拓展图表。您可以查看本文档了解复杂报表的本地编辑模式的公式说明。除了Excel内置公式外，我们支持原生公式和派生公式两大类。

2. 复杂报表公式语法要求

本地编辑模式的公式始终需以英文and符号 (&) 开头。

3. 本地编辑模式下的原生公式

3.1 纵向扩展

（1）语法

&=视图名称.字段名
或者
&=[视图名称].[字段名]

（2）示例

模板公式		对应的输出结果
&=view1.大区	&=view1.销售金额

3.2 横向扩展

（1）语法

&=视图名称.字段名(horizontal)
或者
&=[视图名称].[字段名](horizontal)

（2）参数

（3）示例

模板公式	对应的输出结果
&=view1.大区(horizontal)
&=view1.销售金额(horizontal)

3.3 分组数据

（1）语法

&=视图名称.字段名(group:normal/merge/repeat)
或者
&=[视图名称].[字段名](group:normal/merge/repeat)

（2）参数

（3）示例

模板公式	对应的输出结果
normal类型：
merge类型：
repeat类型：
此外normal类型后面可以添加(merge:正整数),可以实现纵向分组扩展时且需要合并多个单元格； 正整数：表示水平向右合并的单元格数目； 例如： 期望省份字段可以合并2列（A列、B列）且纵向扩展时对相同分组值字段（即省份字段）的单元格进行合并。

⚠️：如需使用分组数据公式则需要提前对分组字段进行排序，例如对样例中大区进行排序。如果不排序请使用派生公式（见4.1）。

3.4 格间计算

格间计算公式可以将Excel公式插入至单元格中。

（1）语法

&=={列序号}{行序号} + 运算符 + {列序号}{行序号} （仅作用于当前使用公式的单元格）
或者
&=&={列序号}{行序号} + 运算符 + {列序号}{行序号}（可作用于当前使用公式的单元格所在列中的所有单元格）
运算符支持包括常见的+、-、*、/等
例如：
&== A{r}*B{r} 表示 A列当前行值 乘以 B列当前行值
&=&={c}{r}/{c}{-1} 表示当前列的当前行值 除以 当前列的向上偏移1行值

（2）参数

格间计算公式提供以下位移坐标参数：

（3）示例

3.5 小计

（1）语法

&=subtotalN:Ref

（2）参数

（3）示例

3.6 复制单元格样式

（1）语法

&=视图名称.字段名(copystyle)
或者
&=[视图名称].[字段名](copystyle)

（2）参数

（3）示例

3.7 交替插入行数据

（1）语法

&=视图名称.字段名(skip:n,noadd)
或者
&=[视图名称].[字段名](skip:n,noadd)

（2）参数

可以将参数noadd和skip结合使用以实现在交替的行上插入数据。因为模板是从底部到顶部处理的，所以可通过在第一行上添加noadd，以避免在交替行前插入多余的行。如下所示：

（3）示例

模板公式	对应的输出结果

4. 本地编辑模式下的派生公式

使用场景：想要实现透视表，可使用以下的派生公式（⚠️：需要组合多个派生公式使用）。

4.1 派生表

（1）说明

在复杂报表中，若直接使用原生公式（见3.1 纵向扩展 / 3.2 横向扩展）对原表的字段做纵向/横向扩展时，公式并不会对原表进行去重也不聚合。当我们要实现透视表时，则需要对原表进行排序并且对组合的数据进行去重（例如需要按照大区字段进行分组）。

因此我们引申出了一个派生表 （DT 即 Dynamic Table ）概念，即将会基于从原表选取进行纵向扩展的字段派生出新的一个表并自动进行排序和去重。

⚠️：我们的派生公式全都基于我们的派生表进行应用。

（2）语法

[DT|原表名称!原表字段名称]
以英文左方括号"[" 表示开头，英文右方括号"]" 表示结束。派生表用"DT"标记，原表名称与原表字段名称
用英文感叹号"!"进行分隔。
当有多个原表字段时请以英文竖杆 "|" 进行分隔。
例如：
[DT|view1!大区|view1!渠道]

（3）参数

4.2 纵向扩展（对应于 BI 平台表格卡片的维度）

（1）说明

此公式将会基于派生表的字段值进行纵向扩展，并支持分组数据(group)、复制单元格样式(copystyle)等参数配置。

（2）语法

&=[DT|原表名称!原表字段名称].原表字段名称(group:normal, copystyle)

（3）参数

（4）示例

模板公式		对应的输出结果
&=[DT|view1!大区|view1!渠道].大区(group:merge)	&=[DT|view1!大区|view1!渠道].渠道

4.3 横向扩展

4.3.1 使用场景：度量名在下，对比项在上

此场景下需要对数值和对比项进行横向扩展，且对对比项进行合并单元格，例如按大区对销售金额、利润2个数值进行合并。

（1）公式1语法

&&=expand_field(,)(copystyle)

（2）公式1参数

（3）公式2语法

&&=expand_metric(,)

（4）公式2参数

⚠️：该公式已默认会进行复制单元格样式，无需使用copystyle来设置扩展的 cell 保持与该公式所在单元格一样的样式。

（5）公式示例

模板公式	对应的输出结果
&&=expand_field([DT|view1!大区].大区,2)
&&=expand_metric([DT|view1!大区],销售金额|利润)

⚠️：该组合公式(expand_field & expand_metric)需使用同一个派生表。

4.3.2 使用场景：度量名在上，对比项在下

此场景下需要对数值和对比项进行横向扩展，且对数值项进行合并单元格，例如按销售金额、利润按照不同大区进行合并单元格。

（1）语法

&&=expand_metric(,)(merge)

（2）参数

（3）公式示例

模板公式		对应的输出结果
&&=expand_metric([DT|view1!大区],销售金额)(merge)	&&=expand_metric([DT|view1!大区],利润)(merge)
&&=expand_field([DT|view1!大区].大区, 1)	&&=expand_field([DT|view1!大区].大区, 1)

⚠️：该组合公式需使用同一个派生表且有多个数值(metrics) 时，则需要分开写多个公式。

4.4 数值查询与聚合计算

使用场景

该公式可应用于我们需要在原表中查找数据，并在横向扩展的结果列中返回符合<查询条件字段> 和 <查询条件字段值> 参数中指定条件的行的值，并以此来执行聚合计算。 例如，可以使用计算列来查找与当前品类和产品名称下相关的所有零售价、含税价，然后对每个零售价、含税价求和。

4.4.1 没有对比派生表时，则数值查询公式只会扩展出1列/单个列。

（1）语法

&&=guanlookupN([], , , )
例如：
&&=guanlookup9([],view2,含税价,省份, A{r}, 城市, B{r})

（2）参数

4.4.2 有对比派生表时，数值查询公式将会根据对比派生表的行数动态横向扩展1列/多列。

（1）语法

&&=guanlookupN(, , , , ,,)
例如：
&&=guanlookup9([view1!month],view1,price|quantity,categoryName,A{r},productName,B{r},month,C1)

（2）参数

4.5 小计计算

（1）使用场景

针对透视表当某列是进行动态横向扩展且需要进行小计计算时，则使用该公式。当列为静态时，可使用原生公式的小计公式进行小计计算。（见 3.5）

（2）语法

&&=subtotalN(派生表.派生表字段名称(Label:,LabelPosition:),,N)
例如：
&&=subtotal9([DT|view1!productName|view1!categoryName].categoryName(Label:"{0}Total",LabelPosition:-3)&[DT|view1!productName|view1!categoryName].productName(Label:"{0}Total",LabelPosition:-1),[DT|view1!month],2)

（3）参数

⚠️：需要进行小计计算的总列数 = 对比派生表的字段行数 * 数值（metric）的个数。

4.6 列总计计算

（1）使用场景

针对透视表当某列是进行动态横向扩展且需要进行按列总计计算时，则使用该公式。当列为静态时，可使用原生公式的小计公式进行总计计算（见 3.5）。

（2）语法

&&=grandtotalN(,,)
例如：
&&=grandtotal9([DT|products1!month],2,C3:C4)

（3）参数

⚠️：需要进行总计计算的总列数 = 对比派生表的字段行数 * 数值（metric）的个数，此外行总计目前会不计算小计行的总计。

4.7 行总计计算

（1）使用场景

针对透视表当某列是进行动态横向扩展且需要进行按行总计计算时，则使用该公式。例如

（2）语法

&&=columnTotal9(,,,)
例如：
&&=columnTotal9([DT|products1!month],C1,0,0)

（3）参数

4.8 派生公式组合案例

（1）分析案例说明

在全国范围内，筛选上海市和浙江省，并对比2个省市区在2017、2018、2019年的下不同城市的商品含税价和零售价，并且对该省市区进行小计求和并汇总2个省市区不同年的各个对比数值的总计。

（2）公式设计说明

下载地址➡️：自定义组合公式.xlsx

（3）实现步骤说明：

• 步骤1：新建“复杂报表”卡片并选择数据集

选择“拓展图表”并选择选择“复杂报表”，选择“演示数据集”作为我们的视图数据。

• 步骤2: 数据准备

拖入省份、城市、日期（年）3个维度字段以及含税价、零售价数值字段，并按照省份字段进行筛选，选择“上海市”、“浙江省”2个选项。

• 步骤3: 编辑模块-设置固定表头

首先我们新建一个Excel，并使用Excel原生的合并单元格和插入斜线能力设计好我们所需的固定斜线表头并填充单元格颜色为蓝色，这个案例我们只需省份、城市：

• 步骤4：编辑模块-纵向扩展

基于原表选择省份和城市字段生成我们所需要的派生表。并选择按照省份、城市进行纵向扩展并选择省份字段分组数据(group:merge)，并将该单元格样式（即文字居中）应用于该列中的所有扩展的单元格中(copystyle)。见下图：

• 步骤5：编辑模块-横向扩展

此案例下我们是度量名在下（即含税价、零售价），对比项在上（即年份），因此我们将需要对数值和对比项进行横向扩展，且对对比项进行合并单元格（见4.3.1 横向扩展：度量名在下，对比项在上）。

• 首先基于原表选择年生成我们所需要的派生表。并选择按照年份进行横向扩展，并将合并的数值(metric)的个数填入为2个即含税价和零售价，此外将单元格填充为绿色，并设置单元格样式（即文字居中）应用于该行中的所有扩展的单元格内（copystyle）。

• 接下来继续按照年份派生表进行对数值的横向扩展，并输入我们期望显示的数值(metric)名即含税价、零售价并用以 | 分隔。

• 步骤6: 编辑模块-数值查询与聚合计算

按照年份的对比派生表，基于每个省份、城市以及年份的指定查询条件，在原表中查找指定条件下的对应的含税价、零售价的值，并进行求和计算。

• 步骤7: 编辑模块-小计求和

按照每个省市区实现该省市区下的各年份的含税价、零售价2个数值字段小计求和，并将“小计”标签位置设置为当前单元格的往左侧1列位置。

• 步骤8：编辑模块-总计求和

接下来对所有省份及城市实现的各年份的含税价、零售价2个数值字段的总计求和。并将左侧2个单元格进行合并，并填入“总计”固定文本名称。

• 步骤9: 编辑模块-上传模板并预览保存

最后我们调整excel的列宽以及行高等，保存该Excel文件并上传该模块并预览我们的模板效果。