跳转至

PIVOTBY 函数

函数概述

PIVOTBY 是一个“用公式做透视汇总”的动态数组函数：你给它行字段、列字段、值字段以及聚合方式，它就会自动生成类似数据透视表的交叉汇总结果，并且源数据变化后会自动重算。

它支持沿两个轴分组（行/列）、聚合，并可进一步控制字段表头显示、总计/小计深度、排序与筛选。 注意：虽然输出形式相似，但 PIVOTBY 并不等同于 Excel 的“数据透视表”功能。

基础语法

1
2
3
4
5
6
=PIVOTBY(
  row_fields, col_fields, values, function,
  [field_headers], [row_total_depth], [row_sort_order],
  [col_total_depth], [col_sort_order], [filter_array],
  [relative_to]
)

语法与参数顺序如上（前 4 个必填，其余可选）。

参数说明

必需参数

参数 含义 要点
row_fields 行分组字段 需为“按列组织”的区域/数组；可多列形成多级行分组
col_fields 列分组字段 同上；可多列形成多级列分组
values 待汇总的数值列 可多列，输出将出现多个聚合结果
function 聚合函数 可用 SUM/AVERAGE/COUNT 等，或自定义 LAMBDA；也可给“函数向量”做多聚合

可选参数

参数 含义 常用取值
field_headers 指定源数据是否含表头，以及结果是否返回字段表头 省略=自动；0=无；1=有但不显示；2=无但生成；3=有且显示
row_total_depth 行总计/小计深度 省略=自动；0=无；1=总计；2=总计+小计；-1/-2=总计(及小计)置顶
col_total_depth 列总计/小计深度 取值规则同 row_total_depth
row_sort_order 行排序依据 用数字指定按哪一列排序：先对应 row_fields 的列，再对应 values 的列；负数=降序
col_sort_order 列排序依据 规则同 row_sort_order，作用在列标题方向
filter_array 筛选数组 一列布尔值（TRUE/FALSE），长度需与数据行数匹配
relative_to 百分比等“二参聚合”参照 仅当 function 需要两个参数时生效，常配合 PERCENTOF；0=列汇总(默认)、1=行总计、2=总计、3=父列、4=父行

补充：PERCENTOF 用于“子集/全体”的占比计算，逻辑上等价于 SUM(subset)/SUM(all)，并常与 GROUPBY/PIVOTBY 搭配。

基础用法示范

示例数据

假设有一张明细表（例如命名为 tSales），字段如下：

日期 年份 产品 地区 销售额
2025-01-03 2025 A 华东 1200
2025-02-18 2025 A 华北 800
2025-03-02 2025 B 华东 1500
2024-11-20 2024 A 华东 600
2024-12-05 2024 B 华北 900
2024-12-21 2024 B 华东 1100

下文为便于阅读，均用结构化引用：tSales[产品]、tSales[年份]、tSales[销售额] 等。

1) 按产品×年份求和

1
=PIVOTBY(tSales[产品], tSales[年份], tSales[销售额], SUM)

这会生成“行=产品、列=年份、值=销售额求和”的交叉表。PIVOTBY 支持按行/列字段分组并聚合。

2) 按销售额降序排产品

1
=PIVOTBY(tSales[产品], tSales[年份], tSales[销售额], SUM, , , -2)

row_sort_order 用数字指定排序依据，负数表示降序；排序数字会按“row_fields 的列、再到 values 的列”的顺序编号。

3) 显示总计与小计

1
=PIVOTBY(tSales[产品], tSales[年份], tSales[销售额], SUM, , 1)

row_total_depth/col_total_depth 控制总计/小计的显示深度（如 1=总计，2=总计+小计，-1/-2 置顶）。

4) 用筛选数组做条件汇总

仅汇总“华东”地区：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
=PIVOTBY(
  tSales[产品],
  tSales[年份],
  tSales[销售额],
  SUM,
  ,
  ,
  ,
  ,
  ,
  (tSales[地区]="华东")
)

filter_array 是一列 TRUE/FALSE，用来决定哪些数据行参与计算，长度必须与数据行数匹配。

5) 汇总占比（PERCENTOF）

按产品×年份计算“占列合计百分比”（默认参照列汇总）：

1
=PIVOTBY(tSales[产品], tSales[年份], tSales[销售额], PERCENTOF)

PERCENTOF 用于返回子集占总体的比例；当 function 需要两个参数时，relative_to 决定“总体”是谁（列/行/总计/父级）。

如果想改为“占行总计百分比”，把 relative_to 设为 1（中间用逗号跳过可选参数）：

1
=PIVOTBY(tSales[产品], tSales[年份], tSales[销售额], PERCENTOF, , , , , , , 1)

relative_to 可取 0~4，其中 1 表示行总计。

总结

  • PIVOTBY 的核心是：行字段 + 列字段 + 值字段 + 聚合函数，用一个公式生成动态的交叉汇总表。
  • 需要更“像透视表”的控制时，用可选参数补齐：表头（field_headers）、总计/小计（row/col_total_depth）、排序（row/col_sort_order）、筛选（filter_array）。
  • 做占比分析时，优先考虑 PERCENTOF + relative_to，能直接得到“按列/按行/按总计/按父级”的百分比口径。