我需要计算多个单元格的平均值,并且我想排除值 = 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
添加零与否不会改变总数。