Excel公式技巧88：使用FREQUENCY函数统计不同值、唯一值和连续值（上）

FREQUENCY函数是一个较难掌握的Excel工作表函数，这篇文章收集整理了一组运用FREQUENCY函数的公式，用来统计不同值、唯一值和连续值的数量，希望能够帮助有兴趣的朋友更进一步熟悉掌握FREQUENCY函数。

先回顾一下FREQUENCY函数的语法：

FREQUENCY(data_array,bins_array)

其中：

• data_array，必需，数组或引用，代表要计算频率的一组值。如果 data_array不包含任何值，则FREQUENCY函数返回一个零值数组。
• bins_array，必需，数组或引用，代表要将data_array中的值分组的区间。如果bins_array不包含任何值，则FREQUENCY返回 data_array 中的元素数。

注意：

• FREQUENCY函数在选择要在其中显示返回的分布的一系列相邻单元格后，作为数组公式输入。
• FREQUENCY函数返回的数组中的元素数比bins_array中的元素数多1。返回数组中的额外元素是高于最高间隔的任何值的数量。例如，在计算输入到三个单元格中的三个值范围（间隔）时，一定要在四个单元格中输入FREQUENCY函数以获得结果，额外的单元格返回data_array中大于第三个间隔值的值的数量。
• FREQUENCY函数忽略空单元格和文本。
• 返回数组的公式必须以数组公式输入。

统计不同值

仅数值

如下图1所示，在单元格区域B4:B12中有一列数值，我们想要知道有多少个不同值。

图1

很显然，在列表中的不同数值是1、2、3、7，共4个，使用的公式是：

=SUM(--(FREQUENCY(B4:B12,B4:B12)>0))

下面对这个公式进行解析，以帮助理解。

首先，使用单元格值来代替单元格引用。注意，这个公式不是数组公式。

=SUM(--(FREQUENCY({1;2;3;1;7;2;2;1;1},{1;2;3;1;7;2;2;1;1})>0))

这里的关键是：

• bins_array中重复的返回0。
• 在bins_array中有9个值作为间隔，FREQUENCY函数返回的数组中有10个值。其中最后一个值是大于最大指定间隔的值的数量。

因此，公式解析为：

=SUM(--({4;3;1;0;1;0;0;0;0}>0))

数组中的第1个数字4表明在列表中有4个1，第2个数字3表明列表中有3个2，依此类推。

现在，我们对列表中有多少个1、2等不感兴趣，只是对它们中至少有一个感兴趣。我们使用>0比较数组中的每个元素，从而为我们提供一个逻辑值数组。

=SUM(--{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE})

接下来，两个一元减号运算符将TRUE值强制转换为1，将FALSE值强制转换为0。必须这样做，因为SUM函数不能对数组或引用中的逻辑值求和，但它可以对数字求和。

=SUM({1;1;1;0;1;0;0;0;0;0})

最终返回结果4。

文本和/或数值

如果想要在包含文本值的数据中获得不同值的数量，那么就会变得更加复杂，因为FREQUENCY函数会忽略文本值。

如下图2所示，在单元格区域B4:B12中包含一系列数值/文本数据，想要统计不同的数据数量。

图2

下面是使用的数组公式：

=SUM(--(FREQUENCY(IF(B4:B12<>"",MATCH("~"&B4:B12,B4:B12&"",0)),ROW(B4:B12)-ROW(B4)+1)>0))

如果数据中只包含数字，这个公式也能正常工作，但这个公式对于更多的数据明显会比前面的公式慢。

下面对这个数组公式进行解析。

• 因为FREQUENCY()公式中有一个IF函数，所以我们必须使用SUM()数组公式。注意，数组公式使用CTRL+SHIFT+ENTER结束，Excel会自动用{}括号将公式括起来。
• 使用B4:B12<>""检查以便忽略空单元格，返回数组：{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}。
• MATCH函数中的波浪号~用作预防通配符作为其中一个单元格中的起始字符。如果确定单元格值永远不会以这些会带来问题的字符（* ? 和 ~）开头，那么可以将“~”&替换为“”&。同样，如果确定单元格值都是文本数据类型，那么也不需要“”&，因此可以简单地从公式中完全删除“~”&。还值得注意的是，如果任何单元格在文本中的其他位置包含通配符（尤其是*），则公式可能会失败。
• MATCH函数的lookup_array参数中的值使用&""强制转换为文本数据类型。如果单元格值只是文本数据类型，那么可以将其删除。

于是，MATCH函数返回每个值第一次出现的位置：

{1;2;3;3;5;6;7;8;8}

结合上述情形，公式中的IF部分解析为：

IF({TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE},{1;2;3;3;5;6;7;8;8})

进一步返回：

{1;2;3;3;5;FALSE;7;8;8}

其中的FALSE值对应那个空单元格。

接下来，公式的ROW(B4:B12)-ROW(B4)+1部分返回一个连续整数数组，例如{1;2;3;4;5;6;7;8;9}。

因此，公式解析为：

=SUM(--(FREQUENCY({1;2;3;3;5;FALSE;7;8;8},{1;2;3;4;5;6;7;8;9})>0))

进一步得到：

=SUM(--({1;1;2;0;1;0;1;2;0;0}>0))

评估为：

=SUM(--{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE})

得到：

=SUM({1;1;1;0;1;0;1;1;0;0})

返回结果：

6

统计唯一值

“唯一值”与“不同值”的区别在于，这些值仅出现1次。

仅数值

如下图3所示，想要获得单元格区域B4:B12中的唯一值的数量。

图3

很显然，列表中唯一出现1次的数值是3和7，即有2个数值。使用公式：

=SUM(--(FREQUENCY(B4:B12,B4:B12)=1))

我们仅想得到只出现1次的数值的数量，因此公式中使用=1。

文本和/或数值

如下图4所示，想要获得单元格区域B4:B12中的唯一值的数量。

图4

很显然，列表中唯一出现1次的数值是1、2、e和b，共4个。使用数公式：

=SUM(--(FREQUENCY(IF(B4:B12<>"",MATCH("~"&B4:B12,B4:B12&"",0)),ROW(B4:B12)-ROW(B4)+1)=1))

这个公式的原理与上文第2个公式相同，只是将原公式中的>0修改为=1。