每个EXCEL大佬背后都离不开一个FREQUENCY函数!

欢迎转发和点一下“在看”，文末留言互动!

置顶公众号或设为星标及时接收更新不迷路

小伙伴们好，这里是EXCEL应用之家。今天要和大家分享一道按条件统计的题目。

原题目是这样的：

有这样一组清单，现在要求我们统计每个地区的业务机构数和经营品种数。模拟结果写在了E列到F列。

有的朋友们可能会说了，直接用COUNTIFS函数就好了啊！但是不是这样的呢？在极端情况下，直接使用COUNTIFS函数有可能会出现错误值，这个问题我们在之前的帖子中有多次讨论了，今天这里不再赘述了。有兴趣的朋友们可以查阅相关内容。

下面向大家介绍两种函数公式法。

在单元格F2中输入下列公式，三键确认并向下向右拖曳即可。

=COUNT(0/FREQUENCY(ROW(A:A),MATCH(B$2:B$36,B$2:B$36,)*($A$2:$A$36=$E2)))-1

这是一则经典的FREQUENCY函数去重统计的公式。

MATCH(B$2:B$36,B$2:B$36,)

由于需要统计业务机构数和商品品种数，需要先做去重处理。MATCH函数首先来返回各个业务机构在源数据中的位置。

这里使用的的是混合引用，随着公式向右拖曳，统计的目标则自动变更为商品种类。

$A$2:$A$36=$E2

这是题目的另外一个条件。

FREQUENCY(ROW(A:A),MATCH(B$2:B$36,B$2:B$36,)*($A$2:$A$36=$E2))

利用FREQUENCY函数来计频。它返回的结果是{1;0;0;3;0;0;0;4;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1048568}。

0/FREQUENCY(ROW(A:A),MATCH(B$2:B$36,B$2:B$36,)*($A$2:$A$36=$E2))

这部分是一个常用手段。其将所有大于0的值都转变为0,0转变为错误值，为下面统计数量做好准备。

COUNT(0/FREQUENCY(ROW(A:A),MATCH(B$2:B$36,B$2:B$36,)*($A$2:$A$36=$E2)))-1

最后COUNT函数忽略错误值统计数值的个数。

由于FREQUENCY函数的特点，最后一个计频结果“1048568”其实是一个错误选项，在COUNT函数统计时多统计了一个，因此需要减去1。

我们都知道可以用COUNTIF函数来计算一组数据中不重复数字的个数，其公式可以写为如下的格式：SUM(1/COUNTIF())。

下面这条公式则是利用了这一经典应用。

在单元格F3中输入下列公式，并向右向下拖曳即可。

=SUMPRODUCT(($A$2:$A$36=$E2)/COUNTIFS($A$2:$A$36,$A$2:$A$36,B$2:B$36,B$2:B$36))

下面简单介绍一下这条公式。

COUNTIFS($A$2:$A$36,$A$2:$A$36,B$2:B$36,B$2:B$36)

利用COUNTIFS函数多条件统计各区域中机构的数量。其返回的结果如下：

{3;3;3;4;4;4;4;5;5;5;5;5;2;2;1;5;5;5;5;5;3;3;3;5;5;5;5;5;4;4;4;4;3;3;3}

$A$2:$A$36=$E2

是本题中的另外一个条件。这里它替代了上面公式中的“1”，作用的一样的。

($A$2:$A$36=$E2)/COUNTIFS($A$2:$A$36,$A$2:$A$36,B$2:B$36,B$2:B$36)

这部分返回的结果是{0.333333333333333;0.333333333333333;0.333333333333333;0.25;0.25;0.25;0.25;0.2;0.2;0.2;0.2;0.2;..}。结果的后半部分我这里省略了，全部都是0。

SUMPRODUCT(($A$2:$A$36=$E2)/COUNTIFS($A$2:$A$36,$A$2:$A$36,B$2:B$36,B$2:B$36))

现在有意思的情况出来了。上面的结果中有3个0.333333333333333；有4个0.25；有5个0.2。

利用SUMPRODUCT函数将它们相加，3个0.333333333333333和为1，4个0.25和为1，5个0.2和为1。因此SUMPRODUCT函数返回结果3。

我就知道你“在看”