一个公式干掉数据透视表? Excel 新函数 PIVOTBY 从入门到精通

点击上方蓝色字体，查看更多Excel技巧！！！

01　PIVOTBY 是什么：一个公式，输出一张交叉汇总表

一句话定位：按"行字段 × 列字段"分组，对数值做聚合，输出一张动态汇总表。

它和传统透视表最本质的区别在这三点：

1. 是公式，不是对象
   ——透视表是插进工作表的一个"物件"，PIVOTBY 就是一条普通公式；
2. 自动更新
   ——数据改一个数字，结果立即跟着变，没有"刷新"这个动作；
3. 结果可以被引用
   ——溢出区域可以直接喂给其他公式和图表，做联动看板。

代价也很明确：没有透视表的样式、切片器和钻取能力（后面细说怎么选型）。

02　先对版本：你的 Excel 有没有它

PIVOTBY 属于新函数（2023 年底预览、2024 年起在 Microsoft 365 正式推送），版本门槛要先确认：

10 秒自检：随便找一格，输入 =PIVOTBY(——如果弹出函数补全和参数提示，说明能用；如果回车后返回 #NAME?，就是版本不支持。

买断版不确定的，用这招一试便知。公司电脑长期收不到更新的，可能是走了半年发布渠道（Semi-Annual），找 IT 管理员确认。

03　示例数据：12 行销售明细

后面所有示例都基于这张表（放在 A1:D13），你也可以换成自己的数据：

04　30 秒上手：一个公式出交叉表

想看"各区域 × 各产品"的销售额汇总，一条公式：

=PIVOTBY(A2:A13, B2:B13, D2:D13, SUM)

四个参数依次是：行字段（区域）、列字段（产品）、数值（销售额）、聚合方式（求和）。回车，结果自动溢出成一张表：

注意：行列总计默认自带，不用你做任何设置。

把列字段换成季度，公式只改一个字母：

=PIVOTBY(A2:A13, C2:C13, D2:D13, SUM)

就这么多——入门部分到此结束，确实和写一条 XLOOKUP 的工作量差不多。

05　语法全貌：4 个必需 + 7 个可选

完整签名长这样（别慌，前 4 个够用 80% 的场景）：

=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])

一个提前剧透的细节：排序和总计是"行、列各一套"——想让行按值降序、列照常排，就只动 row_sort_order，这正是它比想象中灵活的地方。

06　聚合随便换：从 SUM 到去重计数

function 参数直接写裸函数名，不带括号：SUM、AVERAGE、MAX、COUNT……换一个词，换一种口径，同一个"华东 × 手机"的结果立刻不同：

真正拉开差距的是自定义 LAMBDA——很多透视表做起来费劲的口径，这里一个公式搞定。

去重计数（透视表要勾"添加到数据模型"才能做的 Distinct Count）：

=PIVOTBY(A2:A13, B2:B13, C2:C13, LAMBDA(x, ROWS(UNIQUE(x))))

数值区域换成季度列，统计每个组合出现在几个季度——3。

聚合文本（以前在 Excel 里几乎不可能的事）：

=PIVOTBY(A2:A13, B2:B13, C2:C13,
         LAMBDA(x, ARRAYTOTEXT(SORT(UNIQUE(x)))))

每个格子直接列出该组合覆盖的季度：Q1, Q2, Q3。

07　多级分组：HSTACK 拼出"区域-产品"双层行

行字段传两列，就得到两级分组。用 HSTACK 把区域和产品拼起来，选区记得带上表头，field_headers 写 3，row_total_depth 写 2 打开小计：

=PIVOTBY(HSTACK(A1:A13, B1:B13), C1:C13, D1:D13, SUM, 3, 2)

多级行 + 小计 + 总计，一条公式全出来了——这就是以前透视表"拖三个字段进行区域，再开小计"的效果。

08　精装修四件套：表头、总计、排序、筛选

8.1　field_headers：表头控制

省略时是"自动猜"：第一个值是文本、第二个是数字，就默认你有表头。正式报表建议显式指定——推荐用法是选区包含表头 + 写 3（如第 07 节示例）。

8.2　row_total_depth / col_total_depth：总计控制

行、列各自独立设置。比如"列要总计、行不要"：

=PIVOTBY(A2:A13, C2:C13, D2:D13, SUM, , 0, , 1)

总计行放表格顶部（很多领导偏好的样式）：

=PIVOTBY(A2:A13, C2:C13, D2:D13, SUM, , -1, , -1)

8.3　row_sort_order / col_sort_order：排序

排序序号的规则要记住：序号先数行（列）字段，接着数值列。本例 1 个行字段 + 1 个数值列，所以 1 = 区域、2 = 销售额——负数降序：

=PIVOTBY(A2:A13, C2:C13, D2:D13, SUM, , , -2)

行按汇总值从大到小：华南（76000）排到华东（61200）前面。想做"业绩榜"，就靠这个 -2。

列同理，比如季度倒着排（Q3、Q2、Q1）：

=PIVOTBY(A2:A13, C2:C13, D2:D13, SUM, , , , -1)

8.4　filter_array：公式内筛选

第 10 个参数是一个 TRUE/FALSE 数组，长度和源数据行数一致，TRUE 的行才参与汇总。只汇总手机：

=PIVOTBY(A2:A13, B2:B13, D2:D13, SUM, , , , , , B2:B13="手机")

只看单笔一万元以上的明细：

=PIVOTBY(A2:A13, B2:B13, D2:D13, SUM, , , , , , D2:D13>=10000)

注意公式里那一串连续逗号——中间不用的可选参数必须留空位占着，从 SUM 数过去第 6 个逗号后面才是 filter_array。这是新手最容易写错的地方。

09　百分比报表：PERCENTOF 与 relative_to

配套新函数 PERCENTOF 专做占比：PERCENTOF(子集, 全集) = 子集求和 ÷ 全集求和。直接当聚合函数用：

=PIVOTBY(A2:A13, B2:B13, D2:D13, PERCENTOF, , 0, , 0)

（总计关掉，输出更清爽）每个格子自动变成占所在列总计的比重：

想换"分母"，调 relative_to：

写自己的双参数 LAMBDA 时按这个模式来：LAMBDA(subset, totalset, SUM(subset)/SUM(totalset))——百分比口径就完全由你定义。

10　三种方案对比：PIVOTBY / 透视表 / SUMIFS

选型建议：固定格式的月报、Dashboard、要喂图表的数据源 → PIVOTBY；给自己或领导做交互式探索分析 → 透视表；表格要发给用旧版 Excel 的同事 → 老老实实 SUMIFS，或者直接发 PDF。

顺带认识它的姊妹函数 GROUPBY：=GROUPBY(A2:A13, D2:D13, SUM)——只按行分组汇总，没有列维度。逻辑和 PIVOTBY 完全一致，参数还少几个，做一维汇总用它更轻。

11　避坑清单（建议截图）

1. 公式返回 #NAME? → 版本不支持，用第 02 节的方法自检；
2. function 参数写 SUM，不是 SUM()——裸函数名，带括号会先被求值成 0，直接报错；
3. #SPILL!
    → 结果区右下方有单元格挡路，清空溢出区域再回车；
4. #VALUE!
    → 行字段、列字段、数值三个区域行数不一致；filter_array 的长度也必须等于源数据行数；
5. field_headers 的"自动"是靠猜的，正式报表显式写 0/1/2/3；
6. 中间不用的可选参数要留逗号占位，别把 filter_array 写到排序的位置上；
7. 它不是透视表：没有样式、没有切片器——汇报用够炫，探索分析还是透视表。

12　终极速查表

最后补一个压箱底技巧：把源数据 Ctrl+T 转成智能表格（比如命名"销售表"），公式改成结构化引用：

=PIVOTBY(销售表[区域], 销售表[产品], 销售表[销售额], SUM)

以后每天往表里追加新行，汇总表自动扩展、自动重算——配一个引用溢出区域（=F2#）的图表，就是一张零维护的迷你 Dashboard。

写在最后

透视表代表的是"手动建模"时代的交互智慧，PIVOTBY 代表的是"公式即报表"的新思路——数据始终是活的，报表始终是最新的。

现在打开你的 Excel，输入 =PIVOTBY(，用最近一张明细表试跑第一条公式。

如果这篇对你有帮助，点个「赞」和「在看」；也欢迎转发给那个每次交报表前都在狂点"刷新"的同事——这可能是你今天对他最大的帮助。