Excel 列单元格匹配行专属列表字符串时计算AVERAGEIF的公式问询

以下方案根据你使用的Excel版本选择即可，默认数据结构参考：A列为Item列表，B列为对应数值，C列为当前行的Other items字符串，D列为输出的平均值结果，公式内的范围可根据你的实际表格行数调整。

Office 365 / 2021 及以上版本（最简方案） #

直接使用AVERAGE+FILTER组合公式，无需数组操作，输入后直接回车生效：

=AVERAGE(FILTER($B$2:$B$4,ISNUMBER(SEARCH($A$2:$A$4,C2))))

公式逻辑：

• 用SEARCH遍历所有Item，判断是否存在于当前行的Other items字符串中
• ISNUMBER将搜索结果转为布尔值，匹配成功为TRUE，失败为FALSE
• FILTER筛选出所有匹配成功的对应数值，最后用AVERAGE计算平均值

如果需要避免子字符串误匹配（比如Item为"A1"，Other items内有"A11"导致误判），可以优化搜索逻辑，强制匹配完整条目（假设Other items用逗号分隔）：

=AVERAGE(FILTER($B$2:$B$4,ISNUMBER(SEARCH(","&$A$2:$A$4&",",","&C2&","))))

旧版本Excel（2019及以下，不支持FILTER函数） #

使用SUMPRODUCT组合实现，直接回车即可生效：

=SUMPRODUCT(ISNUMBER(SEARCH($A$2:$A$4,C2))*$B$2:$B$4)/SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$4,C2)))

公式逻辑：

• 前半段SUMPRODUCT计算所有匹配成功的数值总和
• 后半段SUMPRODUCT计算匹配成功的条目总数
• 两者相除得到平均值，同样可以参照上面的方法添加分隔符避免误匹配

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