如何用公式批量计算员工多列审计得分的平均值

针对你需要自动计算员工跨列QA Accuracy平均值的需求，这里提供两个通用公式方案，适配姓名和分数列位置变动的场景：

方案1：FLATTEN + FILTER 组合（推荐） #

假设你要计算的员工姓名放在单元格A2，公式如下：

=AVERAGE(FILTER(FLATTEN(B:Z), FLATTEN(A:Y)=A2, ISNUMBER(FLATTEN(B:Z))))

公式说明： #

• FLATTEN(B:Z)：将所有分数所在列转换为一维数组，方便统一筛选
• FLATTEN(A:Y)：将所有对应姓名所在列转换为一维数组（与分数列一一对应，每列分数的左侧列为姓名）
• FILTER(...)：筛选出姓名匹配A2且为数字的有效分数
• AVERAGE(...)：对筛选出的分数计算平均值

如果姓名和分数列并非严格左-右相邻，可手动指定姓名列和分数列范围，示例：

=AVERAGE(FILTER(FLATTEN(B:D,F:H), FLATTEN(A:C,E:G)=A2, ISNUMBER(FLATTEN(B:D,F:H))))

方案2：QUERY 函数实现 #

适合需要更灵活筛选规则的场景，公式如下：

=ARRAYFORMULA(AVERAGE(QUERY({A:B;C:D;E:F}, "select Col2 where Col1 = '"&A2&"' and Col2 is not null")))

公式说明： #

• {A:B;C:D;E:F}：将每组姓名-分数列合并为一个二维数组
• QUERY(...)：筛选出姓名等于A2的有效分数（自动排除空值）
• AVERAGE(...)：计算筛选结果的平均值

若列数较多，可借助SEQUENCE自动生成列对，无需手动罗列：

=ARRAYFORMULA(AVERAGE(QUERY(REDUCE({},SEQUENCE(COLUMNS(A:Z)/2,1,1,2),LAMBDA(a,v,{a;OFFSET(A1,0,v-1,ROWS(A:A),2)})), "select Col2 where Col1 = '"&A2&"' and Col2 is not null")))

注意事项 #

• 若存在姓名格式不一致（如空格、大小写差异），可添加TRIM函数处理：FLATTEN(ARRAYFORMULA(TRIM(A:Y)))=TRIM(A2)
• 分数列中的非数字文本（如"未审核"）会被ISNUMBER自动排除

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