Excel多条件计算同一列筛选平均值的方法及公式报错解决

问题背景 #

• 现有两个Excel数据对象：
  • SurveyData：存储AI生成和用户生成的质量评分，其中用户生成评分的Survey Method列为空值，完成的评分配有Completed状态标记
  • PersonData：需在其数据透视表中新增每周每人的两类评分平均值
• 尝试用指定公式计算用户生成评分平均值时，FILTER函数的AND条件始终返回假值导致报错，但单个条件可正常运行，需排查原因并提供替代方案

原公式问题定位 #

原公式：

=IF(XLOOKUP(AgentData[@[Access Key]],SurveyData[AccessKey],SurveyData[Survey Method],"", 0,1) = "", AVERAGE(FILTER(SurveyData[Score],AND(SurveyData[AccessKey] = AgentData[@[Access Key]], SurveyData[Status] = "Completed"))),"")

问题核心：Excel的AND()函数仅返回单个布尔值，无法生成与数据行对应的布尔数组，而FILTER函数需要逐行匹配的数组条件，因此AND()直接用于FILTER内部会导致条件逻辑失效。

替代解决方案 #

方案1：用逻辑乘（*）替换AND() #

利用Excel中TRUE=1、FALSE=0的特性，用*实现多条件的逐行逻辑与，修改后公式：

=IF(XLOOKUP(AgentData[@[Access Key]],SurveyData[AccessKey],SurveyData[Survey Method],"",0,1)="",AVERAGE(FILTER(SurveyData[Score],(SurveyData[AccessKey]=AgentData[@[Access Key]])*(SurveyData[Status]="Completed"))),"")

方案2：用SUMIFS/COUNTIFS组合（兼容旧版Excel） #

如果使用不支持FILTER函数的Excel版本，可通过求和与计数的比值计算平均值：

=IF(XLOOKUP(AgentData[@[Access Key]],SurveyData[AccessKey],SurveyData[Survey Method],"",0,1)="",SUMIFS(SurveyData[Score],SurveyData[AccessKey],AgentData[@[Access Key]],SurveyData[Status],"Completed")/COUNTIFS(SurveyData[AccessKey],AgentData[@[Access Key]],SurveyData[Status],"Completed"),"")

两类评分的区分计算方法 #

由于AI生成与用户生成评分的调查数量占比约20:1，需分别计算两类评分的平均值：

1. 用户生成评分平均值 #

筛选条件：Survey Method为空 + 状态为Completed + 匹配当前用户Access Key

=AVERAGE(FILTER(SurveyData[Score],(SurveyData[AccessKey]=AgentData[@[Access Key]])*(SurveyData[Status]="Completed")*(SurveyData[Survey Method]="")))

2. AI生成评分平均值 #

筛选条件：Survey Method不为空 + 状态为Completed + 匹配当前用户Access Key

=AVERAGE(FILTER(SurveyData[Score],(SurveyData[AccessKey]=AgentData[@[Access Key]])*(SurveyData[Status]="Completed")*(SurveyData[Survey Method]<>"")))

数据透视表实现方式 #

若在PersonData的透视表中区分两类评分：

• 将Access Key、周设为行字段，Survey Method设为列字段
• 值字段选择Score的平均值，通过Survey Method的空值/非空值自动拆分两类评分的平均值

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