告别数据透视表!Excel新函数PIVOTBY太强了,一个公式搞定交叉汇总

还在手动拖拽字段？ out了！

日常工作中，我们经常需要把流水数据整理成交叉汇总的统计表格。比如，销售经理要按城市和月份统计销量，财务人员要按科目和时间分析费用……

传统做法无非两种：手动创建数据透视表，或者写复杂的函数组合。但这两种方法都有明显缺点：

• 数据透视表需要手动刷新，数据更新时容易忘记

• 使用SUMIFS等函数需要先去重、再写条件、最后拼接，步骤繁琐

现在，Excel 365带来了全新的PIVOTBY函数，只需一个公式就能生成动态交叉表！

什么是PIVOTBY？

PIVOTBY是Excel新推出的动态数组函数，可以理解为 “公式版的数据透视表” 。它能够根据指定的行字段、列字段对数据进行分组，并对数值字段执行聚合运算。

基本语法

=PIVOTBY(行字段, 列字段, 值字段, 聚合函数, [其他参数])

实战案例：按城市和月份汇总销量

假设我们有3列数据：日期、城市、销量，现在要用一个公式汇总得到按城市、按中文月份的交叉汇总表。

传统方法 vs PIVOTBY方法

传统方法需要多个步骤：

1. 先用UNIQUE函数获取不重复城市列表

2. 手动创建月份标题（01月-12月）

3. 写SUMIFS或SUMPRODUCT函数进行条件求和

4. 向右向下填充公式

PIVOTBY方法只需一步：

=PIVOTBY(B2:B100, TEXT(A2:A100, "mm月"), C2:C100, SUM, 0)

就这么简单！一个公式直接生成完整的交叉汇总表。

参数详解

• 行字段：B2:B100（城市列），作为行的分组依据

• 列字段：TEXT(A2:A100, "mm月")，将日期格式化为中文月份，作为列标题

• 值字段：C2:C100（销量列），需要汇总的数据

• 聚合函数：SUM，对销量进行求和

• 其他参数：0，表示源数据没有标题

PIVOTBY的三大优势

1. 动态自动更新

当源数据发生变化时，PIVOTBY的结果会自动更新，无需手动刷新或重新拖拽字段。

2. 公式驱动，易于复用

由于是标准函数公式，可以轻松复制到其他工作簿，或者嵌入到更大的公式流程中。

3. 灵活性强

除了求和，还可以使用其他聚合函数：

=PIVOTBY(行字段, 列字段, 值字段, AVERAGE)  # 求平均值=PIVOTBY(行字段, 列字段, 值字段, COUNT)    # 计数=PIVOTBY(行字段, 列字段, 值字段, MAX)      # 求最大值

进阶技巧：处理跨年数据

如果数据跨越多个年度，单纯按月份汇总会把不同年份的同月数据合并。这时候可以在列字段中加入年份信息：

=PIVOTBY(B2:B100, TEXT(A2:A100, "yyyy年mm月"), C2:C100, SUM, 0)

这样就会生成“2024年01月”、“2024年02月”这样的列标题，确保不同年份的数据不会混淆。

注意事项

1. 版本要求：PIVOTBY需要Excel 365最新版本，WPS用户需要确认版本支持情况

2. 动态数组：输入公式后，结果会自动溢出到相邻单元格，请确保下方有足够空白区域

3. 性能考虑：对于极大数据集（数十万行），数据透视表的性能可能更好

结语

PIVOTBY的出现，彻底改变了我们在Excel中进行数据汇总的方式。它将数据透视表的强大功能与公式的灵活性完美结合，让复杂的数据分析变得简单高效。

下次当你需要制作交叉汇总表时，不妨试试这个强大的新函数，相信它会成为你数据分析工具箱中的利器！

你学会了吗？欢迎在评论区分享你的使用心得！