Excel公式技巧87：使用FREQUENCY()求非连续区域上的条件平均值
excelperfect
问题的提出：我们有一些有关在客户服务团队中工作的三个人的电话呼叫信息，如下图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，供有兴趣的朋友学习参考。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。