如何计算分散非空、非零指定单元格的平均值,规避预填零值干扰
分散指定单元格的有效支出平均值计算方案
核心问题说明
普通AVERAGE函数会默认将参数列表内的所有单元格数值纳入计算,包括空单元格、预填充=SUM(A4:A7)返回的固定0值,既会虚增统计分母拉低结果,当所有待统计单元格无有效数值时,还会触发#DIV/0!报错,无法满足仅统计实际已发生支出的需求。
对应解决方案
方案1:Excel 365/2021及以上版本适用
直接使用FILTER函数先筛出所有大于0的有效支出项,再计算平均值,搭配IFERROR处理无有效数据的场景:=IFERROR(AVERAGE(FILTER((F11,M11,S11,Y11,AE11,AK11,AQ11,AW11,BC11,BI11,BO11,BU11),(F11,M11,S11,Y11,AE11,AK11,AQ11,AW11,BC11,BI11,BO11,BU11)>0)),0)
- 若需要保留无有效数据时的
#DIV/0!报错提示,移除外层的IFERROR即可
方案2:全版本Excel适用(含2019及更早版本)
通过SUMPRODUCT分别统计符合条件的支出总和、有效单元格个数,二者相除得到平均值,兼容性更强:=IFERROR(SUMPRODUCT(--((F11,M11,S11,Y11,AE11,AK11,AQ11,AW11,BC11,BI11,BO11,BU11)>0)*(F11,M11,S11,Y11,AE11,AK11,AQ11,AW11,BC11,BI11,BO11,BU11))/SUMPRODUCT(--((F11,M11,S11,Y11,AE11,AK11,AQ11,AW11,BC11,BI11,BO11,BU11)>0)),0)
注意事项
- 公式内的判断条件设置为
>0,适配家庭支出为正向数值的记账场景;若你有支出记为负数的习惯,可将筛选条件调整为<>0,也可追加ISNUMBER判断排除空文本类干扰项 - 若你的Excel为欧洲区域设置(即你之前编写的公式用
;作为分隔符),需将上述公式内的,全部替换为;即可正常运行;国内默认格式下直接复制公式即可
内容的提问来源于stack exchange,提问作者Sufficient Happiness
相关产品推荐
相关产品推荐

