复杂报表本地编辑模式公式说明
1. 复杂报表公式说明
复杂报表（GuanReport）是嵌入在观远一站式智能数据分析平台中的一套拓展图表。您可以查看本文档了解复杂报表的本地编辑模式的公式说明。除了Excel内置公式外，我们支持原生公式和派生公式两大类。
2. 复杂报表公式语法要求
本地编辑模式的公式始终需以英文and符号 (&) 开头。
3. 本地编辑模式下的原生公式
3.1 纵向扩展
（1）语法
&=视图名称.字段名
或者
&=[视图名称].[字段名]
（2）示例
| 模板公式 | 对应的输出结果 | |
| &=view1.大区 | &=view1.销售金额 | .png) |
3.2 横向扩展
（1）语法
&=视图名称.字段名(horizontal)
或者
&=[视图名称].[字段名](horizontal)
（2）参数
| 术语 | 定义 |
| horizontal | 表示从左到右而不是从上到下写入数据 |
（3）示例
| 模板公式 | 对应的输出结果 |
| &=view1.大区(horizontal) |  |
| &=view1.销售金额(horizontal) |
3.3 分组数据
（1）语法
&=视图名称.字段名(group:normal/merge/repeat)
或者
&=[视图名称].[字段名](group:normal/merge/repeat)
（2）参数
| 术语 | 定义 |
| group | 表示标记允许按字段对数据进行分组，支持三种类型的组，您可以在其中进行选择。normal-即正常，但不会对相同分组值字段的单元格进行合并，而是将对每个分组数据组打印一次。merge-与正常类型相同的行为，但不同之处在于它会对相同分组值字段的单元格进行合并。repeat-依据字段的值对相应的记录重复分组，即表示按照原始数据顺序以列表的样式来显示扩展内容。 |
（3）示例
| 模板公式 | 对应的输出结果 |
normal类型：.png) | .png) |
merge类型：.png) | .png) |
repeat类型：.png) |  |
此外normal类型后面可以添加(merge:正整数),可以实现纵向分组扩展时且需要合并多个单元格；正整数：表示水平向右合并的单元格数目；例如：期望省份字段可以合并2列（A列、B列）且纵向扩展时对相同分组值字段（即省份字段）的单元格进行合并。.png) | .png) |
⚠️：如需使用分组数据公式则需要提前对分组字段进行排序，例如对样例中大区进行排序。如果不排序请使用派生公式（见4.1）。
3.4 格间计算
格间计算公式可以将Excel公式插入至单元格中。
（1）语法
&=={列序号}{行序号} + 运算符 + {列序号}{行序号} （仅作用于当前使用公式的单元格）
或者
&=&={列序号}{行序号} + 运算符 + {列序号}{行序号}（可作用于当前使用公式的单元格所在列中的所有单元格）
运算符支持包括常见的+、-、*、/等
例如：
&== A{r}*B{r} 表示 A列当前行值 乘以 B列当前行值
&=&={c}{r}/{c}{-1} 表示当前列的当前行值 除以 当前列的向上偏移1行值
（2）参数
格间计算公式提供以下位移坐标参数：
| 术语 | 定义 |
| 列序号 | 列序号可以使用绝对序号，也可以使用相对序号c 表示当前列号(current column)正整数 例如{1}, 表示往右偏移1个单位的列，即当前列号+1。负整数 例如{-1}, 表示往左偏移多少整数的列，即当前列号-1。 |
| 行序号 | 共有3种可选参数：r 表示当前行号(current row)正整数 例如{1}, 当前行号A列向下偏移1个单位，即当前行号+1对应的A列值。负整数 例如{-1}, 当前行号A列向上偏移1个单位，即当前行号-1对应的A列值 |
| ~(horizontal) | 可选参数，当期望使用格间计算公式进行横向扩展时，可在格间计算公式最后添加~(horizontal)，例如：&=&={c}{r}/{c}{-1}~(horizontal) |
| ~custom(<对比派生表>,<skip数>,noadd)*备注：不能与horizontal同时使 | 可选参数，当使用动态横向扩展(见4.4）且需要使用格间计算，并且期望格间计算公式也需要横向扩展并交替插入列数据时，可在格间计算公式最后添加~custom(), 例如.png) <对比派生表>: 必填参数，用于计算需要格间计算公式一共需要横向扩展多少次，例如：[view1!月份]，我们将获取月份字段值行数即12个月份，那么我们将横向扩展12个格间计算公式列；<skip数>：必填参数，需使用正整数，用于表示需要跨多少列来插入格间计算公式列；noadd：可选参数，用于表示是否插入新列，横向扩展时模板是从右至左部处理的，所以通常同一组横向交替插入数据时在最右面的～custom()不需要使用noadd参数。 |
（3）示例
| 模板公式 | 对应的输出结果 |
仅作用于当前使用公式的单元格。例如基于第一行大区的销售金额和销售数量，计算第一行大区的平均单价：.png) | .png) |
作用于当前使用公式的单元格所在列中的所有单元格。例如基于每个大区对应的销售金额和销售数量，计算所有大区的平均单价：.png) | .png) |
3.5 小计
（1）语法
&=subtotalN:Ref
（2）参数
| 术语 | 定义 |
| N | 代表1到11之间的数字，这些数字指定在计算数据列表内的小计时使用的功能。（1 = AVERAGE，2 = COUNT，3 = COUNTA，4 = MAX，5 = MIN，... 9 = SUM等）。有关更多详细信息，请参阅Microsoft Excel帮助中的小计参考。 |
| Ref | 按照哪一个列进行参照分组，例如：多个分组字段请用&进行关联，&=subtotal9:Table1.ColumnA&Table1.ColumnB在表Table1中按ColumnA和ColumnB分组字段对ColumnD进行求和操作。 |
（3）示例
| 模板公式 | 对应的输出结果 |
我们按照大区进行分组，并对每个大区的销售金额和销售数量进行小计求和，因此参数N：选择“9”，表示求和；Ref：则需填入参照的分组字段即view1.大区。 | .png) |
3.6 复制单元格样式
（1）语法
&=视图名称.字段名(copystyle)
或者
&=[视图名称].[字段名](copystyle)
（2）参数
| 术语 | 定义 |
| copystyle | 将该单元格的样式复制到该列中的所有单元格 |
（3）示例
| 模板公式 | 对应的输出结果 |
通过对大区单元格进行设置填充色并应用copystyle参数，则该样式将会复制到该列中所有单元格 | .png) |
3.7 交替插入行数据
（1）语法
&=视图名称.字段名(skip:n,noadd)
或者
&=[视图名称].[字段名](skip:n,noadd)
（2）参数
| 术语 | 定义 |
| noadd | 表示不添加额外的行； |
| skip:n | 表示每行数据跳过n行。 |
可以将参数noadd和skip结合使用以实现在交替的行上插入数据。因为模板是从底部到顶部处理的，所以可通过在第一行上添加noadd，以避免在交替行前插入多余的行。如下所示：
（3）示例
| 模板公式 | 对应的输出结果 |
.jpeg) | .jpeg) |
4. 本地编辑模式下的派生公式
使用场景：想要实现透视表，可使用以下的派生公式（⚠️：需要组合多个派生公式使用）。
4.1 派生表
（1）说明
在复杂报表中，若直接使用原生公式（见3.1 纵向扩展 / 3.2 横向扩展）对原表的字段做纵向/横向扩展时，公式并不会对原表进行去重也不聚合。当我们要实现透视表时，则需要对原表进行排序并且对组合的数据进行去重（例如需要按照大区字段进行分组）。
因此我们引申出了一个派生表 （DT 即 Dynamic Table ）概念，即将会基于从原表选取进行纵向扩展的字段派生出新的一个表并自动进行排序和去重。
⚠️：我们的派生公式全都基于我们的派生表进行应用。
（2）语法
[DT|原表名称!原表字段名称]
以英文左方括号"[" 表示开头，英文右方括号"]" 表示结束。派生表用"DT"标记，原表名称与原表字段名称
用英文感叹号"!"进行分隔。
当有多个原表字段时请以英文竖杆 "|" 进行分隔。
例如：
[DT|view1!大区|view1!渠道]
（3）参数
| 术语 | 定义 |
| DT | 表示为派生表（Dynaminc Table） |
| 原表名称 | 对应于我们复杂报表中某个数据视图卡片名称，例如view1 |
| 原表字段名称 | 对应于我们复杂报表中某个数据视图卡片名称，例如view1 |
4.2 纵向扩展（对应于 BI 平台表格卡片的维度）
（1）说明
此公式将会基于派生表的字段值进行纵向扩展，并支持分组数据(group)、复制单元格样式(copystyle)等参数配置。
（2）语法
&=[DT|原表名称!原表字段名称].原表字段名称(group:normal, copystyle)
（3）参数
| 术语 | 定义 |
| group | 算总计小计时需要，可取 normal、merge、repeat 三种值（见3.3 分组数据）：normal：每个分组只保留一个值；merge：每个分组只保留一个值且合并单元格；repeat：保留每一行数据，即表示按照原始数据顺序以列表的样式来显示扩展内容。 |
| copystyle | 扩展的 cell 保持与该公式所在单元格一样的样式。 |
（4）示例
| 模板公式 | 对应的输出结果 | |
| &=[DT|view1!大区|view1!渠道].大区(group:merge) | &=[DT|view1!大区|view1!渠道].渠道 |  |
4.3 横向扩展
4.3.1 使用场景：度量名在下，对比项在上
此场景下需要对数值和对比项进行横向扩展，且对对比项进行合并单元格，例如按大区对销售金额、利润2个数值进行合并。
（1）公式1语法
&&=expand_field(,)(copystyle)
（2）公式1参数
| 术语 | 定义 |
| N | 正整数，表示需要合并的数值(metric)的个数，例如上面示例中的销售金额与利润数值则需要用2 |
| copystyle（可选） | 扩展的单元格保持与该公式所在单元格一样的样式 |
（3）公式2语法
&&=expand_metric(,)
（4）公式2参数
| 术语 | 定义 |
| 派生表 | 该表是用于计算需要一共横向扩展多少列，例如：[DT|view1!大区]，我们将获取该派生表中大区字段数比如2个大区，那么我们将横向扩展2列； |
| 固定文本名称 | 期望显示的数值(metric)名，此场景下(度量名在下)时，多个数值之间以 | 分隔，例如销售金额|利润。 |
⚠️：该公式已默认会进行复制单元格样式，无需使用copystyle来设置扩展的 cell 保持与该公式所在单元格一样的样式。
（5）公式示例
| 模板公式 | 对应的输出结果 |
| &&=expand_field([DT|view1!大区].大区,2) | .png) |
| &&=expand_metric([DT|view1!大区],销售金额|利润) |
⚠️：该组合公式(expand_field & expand_metric)需使用同一个派生表。
4.3.2 使用场景：度量名在上，对比项在下
此场景下需要对数值和对比项进行横向扩展，且对数值项进行合并单元格，例如按销售金额、利润按照不同大区进行合并单元格。
（1）语法
&&=expand_metric(,)(merge)
（2）参数
| 术语 | 定义 |
| 派生表 | 该表是用于计算需要一共横向扩展多少列，例如：[DT|view1!大区]，我们将获取该派生表中大区字段数比如2个大区，那么我们将横向扩展2列； |
| 固定文本名称 | 期望显示的数值(metric)名，此场景下(度量名在上)时，只能填入1个固定文本名称； |
| merge | 必填参数，用于标记对多个数值项进行合并单元格。 |
（3）公式示例
| 模板公式 | 对应的输出结果 | |
| &&=expand_metric([DT|view1!大区],销售金额)(merge) | &&=expand_metric([DT|view1!大区],利润)(merge) | .png) |
| &&=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）参数
| 术语 | 定义 |
| N | 用于标记具体的聚合类型，当前支持的聚合类型有：1：average2：count4：max5：min9：sum |
| [] | 用于标记没有对比派生表时的场景； |
| 查询原表 | 该表表示为聚合计算依赖的原表，对应于我们某个视图卡片名称； |
| 查询数值字段 | 需要查询并进行聚合计算的数值字段名称，度量名在下时若有多个以 | 分隔，例如price|quantity；度量名在上时，有多个，需要写多个公式，参见横向扩展4.3.2； |
| <指定条件字段>, <指定条件位置> | 表示指定查询条件字段名称以及该条件字段的在excel中的位置（查询条件可以有多个对），例如categoryName,A{r},productName,B{r}，表示查询条件为品类字段，品类所在位置为当前行A列、产品名称字段，产品名称所在位置为当前行B列。 |
4.4.2 有对比派生表时，数值查询公式将会根据对比派生表的行数动态横向扩展1列/多列。
（1）语法
&&=guanlookupN(, , , , ,,)
例如：
&&=guanlookup9([view1!month],view1,price|quantity,categoryName,A{r},productName,B{r},month,C1)
（2）参数
| 术语 | 定义 |
| N | 用于标记具体的聚合类型，当前支持的聚合类型有：1：average2：count4：max5：min9：sum |
| 对比派生表 | 该表是用于计算需要一共横向扩展多少结果列，例如：[view1!month] （使用已注册过的派生表）或者 [DT|view1!month] (注册一个新的派生表），我们将获取month字段值行数即12个月份，那么我们将横向扩展12个结果列；注意：此处的派生表为对比项的派生表 |
| 查询原表 | 该表表示为聚合计算依赖的原表，对应于我们某个视图卡片名称； |
| 查询数值字段 | 需要查询并进行聚合计算的数值字段名称，度量名在下时若有多个以 | 分隔，例如price|quantity；度量名在上时，有多个，需要写多个公式，参见横向扩展4.3.2； |
| <指定条件字段>, <指定条件位置> | 表示指定查询条件字段名称以及该条件字段的在excel中的位置（查询条件可以有多个对），例如categoryName,A{r},productName,B{r}，表示查询条件为品类字段，品类所在位置为当前行A列、产品名称字段，产品名称所在位置为当前行B列。 |
| <派生表字段>,<派生表字段位置> | 此对参数始终写在参数的最后面，用于标记派生表在原表中的字段名称是什么以及该字段名在 excel 中的位置，例如month,C1，表示基于month生成的派生表，并位于C列第1行； |
| ~custom(<对比派生表>,,noadd) | 可选参数，当使用数值查询进行动态横向扩展且交替插入列数据时，可在数值查询公式最后添加~custom(), 例如.png) <对比派生表>: 必填参数，用于计算需要数值查询公式一共需要横向扩展多少次，例如：[view1!月份]，我们将获取月份字段值行数即12个月份，那么我们将横向扩展12个数值查询公式；：必填参数，需使用正整数，用于表示需要跨多少列来插入数值查询公式列；noadd：可选参数，用于表示是否插入新列，横向扩展时模板是从右至左部处理的，所以通常同一组横向交替插入数据时在最右面的～custom()不需要使用noadd参数 |
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）参数
| 术语 | 定义 |
| N | 用于标记具体的聚合类型，当前支持的聚合类型有：1：average2：count4：max5：min9：sum |
| 派生表.派生表字段名称(Label:<生成文本标签名称>,LabelPosition:<生成文本标签名称位置>) | 指定计算小计的派生表内维度字段，如果填入有多少个维度字段，则会进行多少层级的小计计算例如大区和渠道，则会分别对大区和渠道进行小计计算。此外还可为每个参与计算的小计行生成相关的表头并支持指定该表头的位置（可选）。当有多个需要进行小计计算的维度字段时，请以英文and"&"进行分隔。例如：[DT|view1!productName|view1!categoryName].categoryName(Label:"{0}Total",LabelPosition:-3)表示为我们对{0}可引用当前分组的维度值，labelposition 指定生成的 label 位置，为当前列向右的偏移量（-代表向左偏移）； |
| 对比派生表 | 该表是用于计算需要一共横向扩展多少结果列，例如：[DT|view1!month]，我们将获取对比派生表month字段值行数比如12个月份，那么我们将横向扩展12个结果列； |
| N | 用于表示数值字段个数，用于计算扩展的列数。 |
⚠️：需要进行小计计算的总列数 = 对比派生表的字段行数 * 数值（metric）的个数。
4.6 列总计计算
（1）使用场景
针对透视表当某列是进行动态横向扩展且需要进行按列总计计算时，则使用该公式。当列为静态时，可使用原生公式的小计公式进行总计计算（见 3.5）。
.png)
（2）语法
&&=grandtotalN(,,)
例如：
&&=grandtotal9([DT|products1!month],2,C3:C4)
（3）参数
| 术语 | 定义 |
| N | 用于标记具体的聚合类型，当前支持的聚合类型有：1：average2：count4：max5：min9：sum |
| 对比派生表 | 该表是用于计算需要一共横向扩展多少结果列，例如：[DT|view1!month]，我们将获取对比派生表month字段值行数比如12个月份，那么我们将横向扩展12个结果列。 |
| N | metric 个数，用于计算扩展的列数。 |
| 总计计算的单元格范围 | 必须填入一个起止范围，起止范围可以是同一个单元格坐标（比如C1:C1)，或是不同单元格坐标（比如C1:C4）；当使用了小计计算公式时则填入【guanlookup 函数所在单元格坐标 : 小计公式所在单元格坐标】；当没有使用小计计算公式时则填入【guanlookup 函数所在单元格坐标 : guanlookup 函数所在单元格坐标】。 |
⚠️：需要进行总计计算的总列数 = 对比派生表的字段行数 * 数值（metric）的个数，此外行总计目前会不计算小计行的总计。
4.7 行总计计算
（1）使用场景
针对透视表当某列是进行动态横向扩展且需要进行按行总计计算时，则使用该公式。例如
.png)
（2）语法
&&=columnTotal9(,,,)
例如：
&&=columnTotal9([DT|products1!month],C1,0,0)
（3）参数
| 术语 | 定义 |
| <派生表>,<派生表位置> | 用于标记派生表在原表中的字段名称是什么以及该字段名在 excel 中的位置，例如month,C1，表示基于month生成的派生表，并位于C列第1行； |
| 数值字段间隔扩展列数 | 用于标记是否存在数值字段间隔扩展（即每一个数值字段需要交替跳过多少行），例如每一个大区的销售金额需要跳过1列的销售数量，则间隔扩展列数为1； |
| 偏移数 | 1.度量名在下，对比项在上场景时：则扩展单元为某一个数值（即以单元格为单位）；.png) 2.度量名在上，对比项在下场景时：则扩展单元为某个数值字段（即以派生表字段为单位）；.png) .png) |
4.8 派生公式组合案例
（1）分析案例说明
在全国范围内，筛选上海市和浙江省，并对比2个省市区在2017、2018、2019年的下不同城市的商品含税价和零售价，并且对该省市区进行小计求和并汇总2个省市区不同年的各个对比数值的总计。
（2）公式设计说明
下载地址➡️：自定义组合公式.xlsx
（3）实现步骤说明：
- 步骤1：新建“复杂报表”卡片并选择数据集
选择“拓展图表”并选择选择“复杂报表”，选择“演示数据集”作为我们的视图数据。
- 步骤2: 数据准备
拖入省份、城市、日期（年）3个维度字段以及含税价、零售价数值字段，并按照省份字段进行筛选，选择“上海市”、“浙江省”2个选项。
.png)
- 步骤3: 编辑模块-设置固定表头
首先我们新建一个Excel，并使用Excel原生的合并单元格和插入斜线能力设计好我们所需的固定斜线表头并填充单元格颜色为蓝色，这个案例我们只需省份、城市：
.png)
- 步骤4：编辑模块-纵向扩展
基于原表选择省份和城市字段生成我们所需要的派生表。并选择按照省份、城市进行纵向扩展并选择省份字段分组数据(group:merge)，并将该单元格样式（即文字居中）应用于该列中的所有扩展的单元格中(copystyle)。见下图：
.png)
- 步骤5：编辑模块-横向扩展
此案例下我们是度量名在下（即含税价、零售价），对比项在上（即年份），因此我们将需要对数值和对比项进行横向扩展，且对对比项进行合并单元格（见4.3.1 横向扩展：度量名在下，对比项在上）。
- 首先基于原表选择年生成我们所需要的派生表。并选择按照年份进行横向扩展，并将合并的数值(metric)的个数填入为2个即含税价和零售价，此外将单元格填充为绿色，并设置单元格样式（即文字居中）应用于该行中的所有扩展的单元格内（copystyle）。
.png)
- 接下来继续按照年份派生表进行对数值的横向扩展，并输入我们期望显示的数值(metric)名即含税价、零售价并用以 | 分隔。
.png)
- 步骤6: 编辑模块-数值查询与聚合计算
按照年份的对比派生表，基于每个省份、城市以及年份的指定查询条件，在原表中查找指定条件下的对应的含税价、零售价的值，并进行求和计算。
.png)
- 步骤7: 编辑模块-小计求和
按照每个省市区实现该省市区下的各年份的含税价、零售价2个数值字段小计求和，并将“小计”标签位置设置为当前单元格的往左侧1列位置。
.png)
- 步骤8：编辑模块-总计求和
接下来对所有省份及城市实现的各年份的含税价、零售价2个数值字段的总计求和。并将左侧2个单元格进行合并，并填入“总计”固定文本名称。
.png)
- 步骤9: 编辑模块-上传模板并预览保存
最后我们调整excel的列宽以及行高等，保存该Excel文件并上传该模块并预览我们的模板效果。
.png)