Excel 2019中AVERAGEIFS设置同列多产品筛选条件报#DIV/0!问题咨询
问题根因
原公式触发
#DIV/0!的核心逻辑问题:
AVERAGEIFS内置多条件为**逻辑与(AND)**关系,你对同一产品列(Hoofdtab!V列)连续设置4个相等判断,等价于要求「同一行的产品值必须同时等于F4、F5、F6、F7四个值」,这种场景几乎无法匹配到有效行,最终因为无可用计算样本触发除零错误- 你使用通配符
*作为“匹配所有值”的占位完全失效:="*"条件仅能匹配任意长度的文本内容,无法匹配数值型产品值、空单元格,本身不具备“全量放行”的筛选作用
适配Excel 2019的解决方案
因为Excel 2019无动态数组函数能力,结合你近2万行的数据源规模,优先推荐运算速度更快的非数组公式方案:
方案1:零数组运算的兼容公式(推荐)
公式核心逻辑是:将不同产品的筛选结果做求和汇总、计数汇总,用“总符合条件的数值和 / 总符合条件的记录数”计算平均值,自动跳过空的产品输入单元格,无需再用通配符占位。
完整公式如下,输入后直接按回车即可运行:
=IFERROR( ( IF(F4<>"", SUMIFS(Hoofdtab!AE:AE, Hoofdtab!V:V,F4, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) + IF(F5<>"", SUMIFS(Hoofdtab!AE:AE, Hoofdtab!V:V,F5, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) + IF(F6<>"", SUMIFS(Hoofdtab!AE:AE, Hoofdtab!V:V,F6, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) + IF(F7<>"", SUMIFS(Hoofdtab!AE:AE, Hoofdtab!V:V,F7, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) ) / ( IF(F4<>"", COUNTIFS(Hoofdtab!V:V,F4, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) + IF(F5<>"", COUNTIFS(Hoofdtab!V:V,F5, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) + IF(F6<>"", COUNTIFS(Hoofdtab!V:V,F6, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) + IF(F7<>"", COUNTIFS(Hoofdtab!V:V,F7, Hoofdtab!I:I,">="&F8, Hoofdtab!I:I,"<="&F9, Hoofdtab!X:X,F10), 0) ), 0 )
方案优势:
- 完全兼容Excel 2019,无需三键触发数组计算,直接回车即可生效
- 基于
SUMIFS/COUNTIFS的整列匹配逻辑运算速度更快,适配近2万行的数据源不会出现卡顿 - 自动跳过空的产品输入单元格:F4-F7任意单元格留空时,对应筛选逻辑自动失效,不会将空值误判为匹配条件
- 外层包
IFERROR,当无符合条件的样本时直接返回0,不会触发除零错误
方案2:短版数组公式(适合简化公式长度场景)
如果觉得方案1公式过长,可以用数组判断实现逻辑,注意Excel 2019中输入完公式必须按Ctrl+Shift+Enter三键组合确认,不能直接按回车:
=AVERAGE(IF( ISNUMBER(MATCH(Hoofdtab!V2:V19883, IF(F4:F7<>"",F4:F7,""), 0)) * (Hoofdtab!I2:I19883>=F8) * (Hoofdtab!I2:I19883<=F9) * (Hoofdtab!X2:X19883=F10), Hoofdtab!AE2:AE19883 ))
逻辑说明:
- 用
MATCH判断每行的产品值是否存在于F4-F7的输入列表中,实现产品多选的OR关系,自动跳过空值输入 - 其他日期范围、X列匹配条件完全保留原筛选逻辑,与产品筛选为AND关系
- 大表场景下数组逐行判断的运算速度会比方案1慢,数据量超过2万行时不建议使用
内容的提问来源于stack exchange,提问作者VHes
相关产品推荐
相关产品推荐

