Excel AVERAGEIF 值 > 0

Excel AVERAGEIF 值 > 0

我需要计算多个单元格的平均值,并且我想排除值 = 0。

单元格不连续,因此我不能使用 AVERAGEIF(A1:A10, ">0")

我需要单独选择单元格,但是我想将公式复制到多列,所以不想排除任何(手动)

我该怎么做呢?

数据示例如下: 在此处输入图片描述

我想计算 Tier 1、Tier 2 和 CBD 的所有行的平均值(不包括 0)

答案1

使用 AVERAGEIFS,它可以让您选择平均标准:

=AVERAGEIFS(A3:Z3,A$2:Z$2,"=Tier 1",A3:Z3,">0")

然后,在指定条件范围时,请确保锁定行号。(A$2:Z$2,而不是简单地将范围指定为 A2:Z2。)否则,当您向下传播后续行平均值时,Excel 将(正确地)尝试将其填充为 A3:Z3 并抛出 DIV/0 错误。

答案2

在任何版本中,您都可以使用这种类型的公式对不连续的单元格进行平均,这些单元格不为零(仅假设正值)

=SUM(A2,F3,Z5)/MAX(1,INDEX(FREQUENCY((A2,F3,Z5),0),2))

其中 A2、F3 和 Z5 是要求平均值的单元格

如果没有非零值,公式将返回零

如果愿意,您可以命名不连续单元格的范围,然后使用

=SUM(Range1)/MAX(1,INDEX(FREQUENCY(Range1,0),2))

答案3

这看起来很糟糕,并且还需要 Excel 2007 或更高版本:

=SUM(A2,D2,G2,J2,M2,P2,S2,V2)/COUNTIFS(A2,"<>0",D2,"<>0",G2,"<>0",J2,"<>0",M2,"<>0",P2,"<>0",S2,"<>0",V2,"<>0")

但它会对选定的单元格取平均值(不需要任何标准,因为SUM添加零与否不会改变总数。

相关内容