如何在Excel PowerQuery中计算组内排除当前个体的均值偏差

解决思路 #

直接用Filter.Average嵌套筛选容易出现类型转换错误（表格转列表失败、null值报错），推荐用分组统计+表合并的方式，先预计算每组的总和与数量，再快速推导排除当前个体后的均值，避免复杂的嵌套筛选。

具体步骤（附M代码） #

1. 分组统计组内总和与数量
   按Group和Date分组，计算每组的Value总和、个体数量：

let
    源 = 你的数据源表,
    // 按Group+Date分组，计算组总和、组内个体数
    分组统计 = Table.Group(源, {"Group", "Date"}, {
        {"组总和", each List.Sum([Value]), type number},
        {"组数量", each Table.RowCount(_), type number}
    })
in
    分组统计

2. 合并原表与分组统计结果
   将原表和分组统计表按Group、Date字段匹配合并，把分组统计的字段展开到原表中：

// 接上面的代码继续
合并表 = Table.NestedJoin(源, {"Group", "Date"}, 分组统计, {"Group", "Date"}, "分组数据", JoinKind.LeftOuter),
展开分组字段 = Table.ExpandTableColumn(合并表, "分组数据", {"组总和", "组数量"}, {"组总和", "组数量"})

3. 添加百分比偏差计算列
   计算当前个体Value与「排除自身后的组内均值」的百分比偏差，同时处理组内只有1个个体的边界情况（避免分母为0）：

// 接上面的代码继续
添加偏差列 = Table.AddColumn(展开分组字段, "百分比偏差", each 
    if [组数量] = 1 then null 
    else ([Value] - ([组总和]-[Value])/([组数量]-1)) / (([组总和]-[Value])/([组数量]-1)) * 100
)

错误原因说明 #

你之前尝试的Filter.Average嵌套Table.SelectRows方案，本质是要从筛选后的表格中提取Value列转为列表，但如果筛选后无数据（比如组内仅当前个体），会返回null导致列表转换失败；另外Table.SelectRows返回的是表格对象，需要用Table.Column(筛选后的表, "Value")转为列表后再传入List.Average，但这种写法效率低且易出错，不如分组统计的方式稳定。

示例验证 #

假设A1行：IndividualID=A1，Group=G1，Date=2024-01-01，Value=100，同组同日期还有两个个体（Value=120、140）：

• 组总和=100+120+140=360，组数量=3
• 排除自身后的均值=(360-100)/(3-1)=130
• 百分比偏差=(100-130)/130*100≈-23.08%，和预期结果一致

内容的提问来源于stack exchange，提问作者Fabian Billenkamp