Tom需要对一系列不连续的单元进行平均,不包括可能包含零值的任何单元。具体来说,他需要对单元格A1,C1,E1,G1和J1求平均,但只对那些不包含零的单元格进行计数。

在探讨什么有效之前,让我们看一下哪些无效。

首先,简单地将单元格相加并除以5并不可行。不会考虑零值。其次,在公式的分母中使用COUNTIF无效,如下所示:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

这是行不通的,因为它会检查和计数A1:J1整个范围内的单元,而不仅仅是检查您希望在平均值中考虑的五个单元。您可能还认为您可以选择五个不连续的单元格,为其命名,然后在公式中使用该名称。尽管Excel允许您创建名称,但以下给出了错误:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

似乎COUNTIF仅适用于单个连续范围,因此MyCells范围的非连续性质使函数陷入混乱。如果您尝试通过AVERAGEIF函数使用不连续的范围,则会发生类似的问题:

=AVERAGEIF(MyCells, "<>0")

由于您无法使用任何可能要使用的函数,因此您只能依靠更长的公式来计算平均值。您可以通过在分母上应用“ trikery”来计算这五个单元的平均值,方法是:

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

对分母中每个单元格进行的求值返回1(对于True)还是0(对于False),具体取决于该单元格是否包含非零值。这一系列值加在一起,为分母提供非零单元格的必要计数。

请注意,这里的讨论完全是关于公式中的分母,而不是分子。原因很简单-您可以将所有五个值添加到分子中;零值并不重要。

它们唯一重要的地方是分母,这使得计算该平均值如此棘手。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(7845)适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。您可以在此处为Excel的较早菜单界面找到此技巧的版本: