Google Sheets：用AVERAGEIF+Arrayformula批量计算项目多列数据平均值

需求背景 #

有两个Google Sheets工作表：

• Items表：A列为项目名称（如alfa、beta、gamma）
• Values表：首行对应项目名称，下方行是各项目关联的数值（实际场景有10列数据）

需要在Items表的B1单元格输入ArrayFormula公式，自动生成每个项目对应所有数值的平均值，预期结果：

alfa → 50（即(20+40+60+80)/4 = 200/4）
beta → 55（即(30+40+70+80)/4 = 220/4）
gamma → 65（即(50+60+70+80)/4 = 260/4）

解决方案 #

方案1：用MMULT实现高效求和取平均 #

在Items表的B1单元格输入以下公式：

=ARRAYFORMULA(IF(A1:A="", "", VLOOKUP(A1:A, {TRANSPOSE(Values!A1:J1), MMULT(N(Values!A2:J), SEQUENCE(COLUMNS(Values!A2:J), 1, 1, 0))/COLUMNS(Values!A2:J)}, 2, FALSE)))

公式逻辑拆解：

• TRANSPOSE(Values!A1:J1)：将Values表首行的项目名称转置为列，用于后续匹配
• MMULT(N(Values!A2:J), SEQUENCE(COLUMNS(Values!A2:J), 1, 1, 0))：通过矩阵乘法快速计算每列数值的总和（自动适配10列范围）
• /COLUMNS(Values!A2:J)：除以列数得到平均值
• VLOOKUP(A1:A, {...}, 2, FALSE)：根据Items表的项目名称匹配对应平均值
• IF(A1:A="", "", ...)：避免空行返回错误值

方案2：用QUERY实现灵活分组统计 #

如果需要更直观的分组逻辑，可使用QUERY函数：

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A, QUERY(SPLIT(FLATTEN(Values!A1:J1&"~"&Values!A2:J), "~"), "select Col1, avg(Col2) group by Col1"), 2, FALSE)))

公式逻辑拆解：

• FLATTEN(Values!A1:J1&"~"&Values!A2:J)：将每个项目名称与对应数值配对后扁平化，生成项目名~数值格式的单行数据
• SPLIT(..., "~")：拆分后得到两列（项目名、数值）
• QUERY(..., "select Col1, avg(Col2) group by Col1")：按项目名分组计算平均值
• VLOOKUP(A1:A, ..., 2, FALSE)：匹配Items表的项目名称返回平均值
• IFNA(...)：处理Items表中存在但Values表无对应数据的情况，返回空值而非错误

内容的提问来源于stack exchange，提问作者G. Lari