Excel公式技巧87：使用FREQUENCY()求非连续区域上的条件平均值

问题的提出：我们有一些有关在客户服务团队中工作的三个人的电话呼叫信息，如下图1所示。

图1

对于每个人，电话呼叫数量拆分成两类：ACD和AMS，我们需要从数据集中得到ACD的平均数，并且统计的平均值不应考虑0值所在的单元格，因此正确的答案应该是56：

(24+21+99+67+87+6+88)/7=56

在这种情况下，我们要执行条件平均：要忽略包含0的单元格。通常，我们可以使用AVERAGEIF函数来执行此操作，但由于ACD数据位于三个单独的或不连续的单元格区域内，因此我们无法利用此函数执行此操作。此公式将返回#VALUE！错误，因为AVERAGEIF函数无法处理非连续区域：

=AVERAGEIF((B3:B7,D3:D7,F3:F7),"<>0")

要获取不连续的区域的平均值，我们通常可以使用SUM/COUNT函数，如下所示：

=SUM(B3:B7,D3:D7,F3:F7)/COUNT(B3:B7,D3:D7,F3:F7)

但问题是，COUNT函数统计结果将包括0，因此上面的公式返回26.13，显然是不正确的。

试图使用COUNTIF函数替换COUNT函数来忽略0值，但是COUNTIF函数不能用于不连续区域，因此公式将返回#VALUE!：

=SUM(B3:B7,D3:D7,F3:F7)/COUNTIF((B3:B7,D3:D7,F3:F7),"<>0")

因此，问题是如何从这些单元格中获得非零值的数量？

解决方法

要获得正确的答案，可以使用下面的公式：

=SUM(B3:B7,D3:D7,F3:F7)/INDEX(FREQUENCY((B3:B7,D3:D7,F3:F7),0),2)

注意，这不是一个数组公式，因此不需要按Ctrl+Shift+Enter组合键。

公式中：

SUM(B3:B7,D3:D7,F3:F7)

很好理解，求这三个区域的数值之和。

公式中：

FREQUENCY((B3:B7,D3:D7,F3:F7),0)

其中，data_array是(B3:B7,D3:D7,F3:F7)，bins_array是0，将返回一个包含两个数值的数组，第一个值是data_array中等于0的数量，第二个值是data_array中大于0的数量，因此将返回数组：

{8;7}

传递给INDEX函数：

INDEX({8;7},2)

得到：

{7}

即上述区域中不等于0的数值的数量。

因此，公式等价于：

=392/{7}

结果：

56

如果有空单元格，或者即使非连续区域的大小不同，该公式仍然适用。

其它公式

其它公式1：

=SUM(B3:B7,D3:D7,F3:F7)/(COUNTIF(B3:B7,"<>0")+COUNTIF(D3:D7,"<>0")+COUNTIF(F3:F7,"<>0"))

其它公式2：

=AVERAGE(IF(B2:G2="ACD",IF(B3:G7>0,B3:G7)))

或：

=AVERAGE(IF((B3:G7>0)*(B2:G2="ACD"),B3:G7))

注意，公式是数组公式，因此应按Ctrl+Shift+Enter组合键完成公式输入。

其它公式3：

=SUM(B3:B7,D3:D7,F3:F7)/SUM(COUNTIF(INDIRECT({"B3:B7","D3:D7","F3:F7"}),"<>0"))

你还有没有好的解决方法呢？

注：本文整理自colinlegg.wordpress.com，供有兴趣的朋友学习参考。

undefined

undefined