如何在Google Sheets中结合模糊匹配用AVERAGEIF计算指定列平均值

嘿，我刚好碰到过类似的需求，给你分享两个实用的解决方案，完美解决Google Sheets里这种带多关键词标记列的平均值计算问题！

核心问题是单列标记含多个关键词，需要跨列匹配并计算平均值，AVERAGEIF本身不太直接支持这种子串匹配的多列场景，所以我们可以用两种更灵活的方法：SUMPRODUCT组合公式，或者ARRAYFORMULA+AVERAGE的组合。

先看数据示例 #

假设我们有一张名为Data的工作表，列首（第1行）是各列的标记文本，包含多个关键词，下面是业务数据：

预期效果 #

• 计算所有含销售关键词的列（A、B、D）的整体平均值
• 计算所有含华东关键词的列（A、C、D）的整体平均值

方法1：用SUMPRODUCT实现精准计算 #

这个方法适合需要严格统计有效数据（忽略空白）的场景，原理是先把符合条件的列的数值求和，再除以符合条件的列的有效数据行数总和。

公式示例（计算含"销售"的列的平均值） #

=SUMPRODUCT(--(ISNUMBER(FIND("销售", Data!A1:D1))), --(Data!A2:D4<>""), Data!A2:D4)/SUMPRODUCT(--(ISNUMBER(FIND("销售", Data!A1:D1))), --(Data!A2:D4<>""))

公式拆解

• --(ISNUMBER(FIND("销售", Data!A1:D1)))：判断列首是否包含"销售"关键词，返回1（符合）或0（不符合）
• --(Data!A2:D4<>"")：过滤空白单元格，避免把空白当成0计算
• 第一个SUMPRODUCT：把所有符合条件的列的有效数值相加
• 第二个SUMPRODUCT：统计所有符合条件的列的有效数据总个数
• 两者相除得到平均值

方法2：用ARRAYFORMULA+AVERAGE简化写法 #

如果不需要严格过滤空白（AVERAGE默认会忽略空值），可以用这个更简洁的公式：

公式示例（计算含"华东"的列的平均值） #

=AVERAGE(ARRAYFORMULA(IF(ISNUMBER(FIND("华东", Data!A1:D1)), Data!A2:D4, "")))

公式拆解

• ARRAYFORMULA+IF：遍历每一列，若列首包含"华东"，则保留该列的所有数据，否则返回空值
• AVERAGE：对所有保留的数值计算平均值（自动忽略空值）

关键注意事项 #

1. 大小写敏感：FIND是区分大小写的，如果要忽略大小写，把FIND换成SEARCH即可
2. 标记位置调整：如果标记文本在列尾行（比如第10行），只需要把公式里的Data!A1:D1改成Data!A10:D1
3. 数据范围扩展：如果数据行数更多，比如到第100行，把Data!A2:D4改成Data!A2:D100就行

为什么你之前用AVERAGEIF+FIND没成功？因为AVERAGEIF的条件区域和平均区域要求一一对应，而FIND返回的是匹配位置，不是布尔值，而且它默认不支持数组式的多列匹配，所以需要用上面两种方法来绕开这个限制。

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