FREQUENCY函数

使用FREQUENCY函数，可以帮助创建频率分布。例如，计算某班学生的语文成绩的频率分布，分别在0～59、60～69、70～79、80～89、90分以上的区间的学生数。

图1

什么情况下使用FREQUENCY函数?

FREQUENCY函数以一列垂直数组返回一组数据的频率分布。它能够：

创建学生成绩的频率分布

创建百分比形式的频率分布

统计单元格区域中不重复值的数量

获取单元格区域中不重复值

FREQUENCY函数语法

FREQUENCY函数有2个参数，其语法如下：

FREQUENCY(data_array,bins_array)

data_array: 代表用来计算频率的数组，或者单元格区域。

bins_array: 由每个区间上限数字组成的数组或者单元格区域。

返回的值为一个数组，代表每个区间的数值个数且该数组包含的元素数总比参数bins_array中的个数多1，多出的这个数字是Excel自动创建的最后一个区间中的数值个数。

返回的数组为垂直数组，可以使用TRANSPOSE函数将其转换成水平数组。

该函数忽略空单元格和文本。

如果参数bins_array中有重复的值，那么重复的值统计的数为0。

FREQUENCY函数陷阱

在参数bins_array中，只需指定每个区间的上限，但不一定需要指定最后一个区间的上限（因为它可能是无穷大），它会自动包括在区间中。由于FREQUENCY函数的返回值是数组，因此输入完成后应按Ctrl+Shift+Enter键。如果参数bins_array中有n个值，那么函数输出的区域应该包括n+1个单元格，否则数据会显示不全。

FREQUENCY函数统计的结果包括区间的上限值，但不包括区间的下限值。如果想统计的结果不包括区间的上限值但包括区间的下限值，可以使用COUNTIF函数和COUNTIFS函数。

示例1: 创建学生成绩的频率分布

如下图2的示例工作表，要创建学生语文成绩的频率分布，即分别在0～59、60～69、70～79、80～89、90分以上的区间的学生数，使用数组公式：

=FREQUENCY(B3:B28,D4:D7)

返回数组{4;4;5;6;7}。

图2

示例2: 创建百分比形式的频率分布

下图3所示工作表中，命名区域Data为A1:D9。计算区域Data中分别落在0～20、21～40、41～60、61～80、81～100区间的数值数量的公式为：

=FREQUENCY(Data,G3:G7)

这是一个数组公式，因此输入完成后要按Ctrl+Shift+Enter组合键。

创建百分比形式的频率分布的数组公式为：

=FREQUENCY(Data,G3:G7)/COUNT(Data)

图3

示例3: 统计单元格区域中不重复值的数量

有多种方法可以统计单元格区域中不重复值的数量，使用FREQUENCY函数是其中的一种。如下图4所示的工作表，需要统计列A中有多少唯一的付款账户，数组公式为：

=SUMPRODUCT(–(FREQUENCY(A2:A7,A2:A7)>0))

图4

也可以使用COUNIF函数进行统计，数组公式为：

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

如果要统计的不重复值的数据是文本（如图5）或者文本和数据的混合，例如，统计图5列D中不同付款人的数量，那么可以使用数组公式：

=SUMPRODUCT(–(FREQUENCY(MATCH(D2:D7,D2:D7,0),ROW(D2:D7)-ROW(D2)+1)>0))

图5

当然，此时使用COUNTIF函数的公式将更简单。

如果要统计满足多于1个条件的不重复值的数量，例如下图6所示的工作表，要统计付款日期为2017年9月30日之后且付款金额大于等于1000的唯一付款账户数，在单元格D9中输入指定的日期，单元格D10中输入金额，那么数组公式为：

=SUM(IF(FREQUENCY(IF(B2:B7>D9,IF(C2:C7>=D10,MATCH(A2:A7,A2:A7,0))),ROW(A2:A7)-ROW(A2)+1),1))

图6

示例4: 获取单元格区域中不重复值

上面的示例统计了单元格区域中不重复值的数量，下面获取该工作表中不重复的付款人列表。如图7所示，在单元格B13中输入数组公式：

=IF(ROWS(B$13:B13)>$B$9,””,INDEX($D$2:$D$7,SMALL(IF(FREQUENCY(IF($D$2:$D$7<>””,MATCH($D$2:$D$7,$D$2:$D$7,0)),ROW($D$2:$D$7)-ROW($D$2)+1),ROW($D$2:$D$7)-ROW($D$2)+1),ROWS(B$13:B13))))

图7

将单元格B13向下拉获取不重复的付款人列表。