Excel中如何按分组计算TRIMMEAN？动态数据场景求解

需求背景 #

每日从外部网站获取动态更新的表格数据（结构示例见下方），需要生成包含state、city、修剪均值（TRIMMEAN）的结果集，要求：

• 不能修改源数据（数据来自外部且行数每日变化）
• 结果集必须固定列、支持动态行，可直接作为其他工作表的计算数据源
• 试过数据透视表，但TRIMMEAN()无法作为分组函数使用

源数据示例：

解决方案1：Excel动态数组公式（365/2021+适用） #

用动态数组函数直接生成结果，全程不碰源数据，自动适配行数变化。

假设源数据在Sheet1!A:D（表头在行1，数据从行2开始）：

1. 在结果表的A2单元格输入：

   =UNIQUE(Sheet1!A2:B)
   

   这会自动生成所有唯一的state+city组合，行随源数据动态扩展。
2. 在结果表的C2单元格输入：

   =BYROW(A2#, LAMBDA(row, TRIMMEAN(FILTER(Sheet1!D:D, (Sheet1!A:A=INDEX(row,1))*(Sheet1!B:B=INDEX(row,2))), 0.1)))
   

   • A2#是动态引用第一步生成的组合区域
   • FILTER筛选出对应分组的number数据
   • 最后一个参数0.1表示修剪10%（首尾各去5%），可按需调整比例

设置好表头（A1:State、B1:City、C1:Trimmed Avg）后，源数据更新时结果集会自动同步刷新。

解决方案2：Power Query（全Excel版本适用） #

通过Power Query做分组计算，生成可刷新的动态表，稳定性拉满。

步骤：

1. 把外部网站数据导入Power Query（数据>自网站>输入URL；如果已经导入，直接从"数据>查询和连接"打开编辑器）
2. 在编辑器里选中state和city列，点击转换>分组依据
3. 分组设置：
   • 新列名：Trimmed Avg
   • 操作选自定义
   • 输入公式：=TRIMMEAN([number], 0.1)（修剪比例可改）
4. 确定后，把数据加载到工作表（选"加载到表"，自动支持动态扩展）
5. 每日刷新：右键结果表>刷新，数据自动更新，列固定、行随源数据变化

两种方法都完全符合需求：不修改源数据，结果结构稳定，可直接作为其他工作表的数据源。

内容的提问来源于stack exchange，提问作者gt.guybrush