FREQUENCY会用这个Excel函数的,都是高手!

原创作者 | 李锐

微信公众号 | Excel函数与公式(ID:ExcelLiRui)

微信个人号 | (ID:ExcelLiRui520)

本文关键字:frequency

FREQUENCY

会用这个Excel函数的,都是高手!

工作中涉及到条件分段统计、数据分布统计和数值数据重复次数统计的问题,都可以借助一个函数轻松搞定,他就是FREQUENCY。

很多同学可能是头一次听说这个函数,但要想晋升到Excel中级水平,FREQUENCY是必会函数之一,至于为什么这么说,看完本篇教程你就懂了。

为了让大家认识FREQUENCY函数那些不为人知的强大功能,本文贴合办公实际场景,整理了多种应用方法。

看完教程还想系统学习的同学,点击文章底部的“阅读原文”,系统提升自己。

1  FREQUENCY函数语法解析及基础用法

FREQUENCY函数计算数值在某个区域内的出现频率,然后返回一个垂直数组,其基本语法为:

FREQUENCY(data_array,bins_array)

data_array:必需。要对其频率进行计数的一组数值或对这组数值的引用。如果data_array中不包含任何数值,则FREQUENCY返回一个零数组。

bins_array:必需。要将data_array中的值插入到的间隔数组或对间隔的引用。如果bins_array中不包含任何数值,则FREQUENCY返回data_array中的元素个数。

FREQUENCY函数将data_array中的数值以bins_array为间隔进行分组,计算数值在各个区域出现的频率。

FREQUENCY函数的data_array可以升序排列,也可以乱序排列。无论bins_array中的数值升序还是乱序排列,统计时都会按照间隔点的数值升序排列,对各区间的数值个数进行统计,并且按照原本bins_array中间隔点的顺序返回对应的统计结果,即按n个间隔点划分为n+1个区间。

对于每一个间隔点,统计小于等于此间隔点且大于上一个间隔点的数值个数。结果生成了n+1个统计值,多出的元素表示大于最高间隔点的数值个数。

对于data_array和bins_array相同时,FREQUENCY函数只对data_array中首次出现的数字返回其统计频率,其后重复出现的数字返回的统计频率都为0。

说明:

(1)FREQUENCY函数将忽略空白单元格和文本。

(2)对于返回结果为数组的公式,必须以数组公式的形式输入。

(3)返回的数组中的元素比bins_array中的元素多一个。返回的数组中的额外元素返回最高的间隔以上的任何值的计数。

例如,在对输入到三个单元格中的三个值范围(间隔)进行计数时,确保将FREQUENCY函数输入到结果的四个单元格。额外的单元格将返回data_array中大于第三个间隔值的值的数量。

如下图所示,需要将A2:A12单元格区域中的数据按C2:C4单元格区域的区间分割点分割,并统计每个区间的数据个数。

选定D2:D5单元格区域,输入以下数组公式,按<Ctrl+Shift+Enter>组合键。

{=FREQUENCY(A2:A12,C2:C4)}

FREQUENCY函数按{1;5;7}进行区间分割,结果为{1;4;2;4},分别表示:

小于等于1的数据个数为1;大于1且小于等于5的数据个数为4;大于5且小于等于7的数据个数为2;大于7的数据个数为4。

2  FREQUENCY函数按条件统计给定区间内的数据个数

FREQUENCY函数不但可以对数据进行多段区间的个数统计,配合其它函数还可以实现意想不到的效果,比如下面这个例子:

左侧的A列成绩是数据源区域,要在其中统计同时满足大于70且小于等于80的成绩个数。

在D2单元格输入以下公式。

=INDEX(FREQUENCY(A2:A12,{70,80}),2)

3  FREQUENCY函数分段计数统计

常规的FREQUENCY函数分段统计,是按照左开右闭区间统计,如果需要按照左闭右开区间统计,应该如何实现呢?

下图展示的是某班级的学生成绩表,需要按区间分割点统计对应区间内的数据个数。

选定E2:E6单元格区域,输入以下数组公式,按<Ctrl+Shift+Enter>组合键。

=FREQUENCY(B2:B8,{60,80}-0.1)

4  FREQUENCY函数统计最大连续次数

FREQUENCY函数不但可以分段统计和根据给定区间统计,还可以统计最大连续次数,比如求冠军榜最高蝉联次数。

下图展示的是某企业销售冠军团队的月登记表,记录了每个月荣获冠军的团队名称,现在需要统计同一团队连续夺冠的最多次数。

在D2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键。

=MAX(FREQUENCY(ROW(2:8),IF(B2:B8<>B3:B9,ROW(2:8))))

5  FREQUENCY函数统计文本分布频率

FREQUENCY函数不但可以搞定数值数据的分段统计,而且可以处理简单的字符统计。

下图展示的是联欢晚会上用的气球拱桥的颜色排列,现在需要统计每种颜色的气球个数。

选定E2:E4单元格区域,输入数组公式,按<Ctrl+Shift+Enter>组合键。

=FREQUENCY(CODE(B2:B8),CODE(D2:D4))

还想进一步系统学习的同学,长按下图,识别二维码

进入页面右上角点击+关注

+关注”按钮变为“已关注”后,可免费接收课程动态

跟李锐学Excel,高效工作,快乐生活!

一次购买,永久学习,手机、iPad、电脑任意时间听课

长按上图↑识别二维码,了解详情

6  FREQUENCY函数计算不重复值的个数

根据FREQUENCY函数对重复数值只在首次出现时统计个数,其余分段点返回0的特点,可以完成很多与不重复值有关的计算。

下图所示,左侧是数据源区域,需要统计B列不重复得分的个数。

D2单元格输入以下公式。

=COUNT(0/FREQUENCY(B2:B6,B2:B6))

7  FREQUENCY函数统计字符串内不重复字符数

如下图所示,需要统计A列字符串内不重复字符数。

1、区分大小写统计字符串内不重复字符

在B2单元格输入以下公式。

=COUNT(0/FREQUENCY(FIND(MID(A2,ROW($1:$99),1),A2),ROW($1:$99)))

2、不区分大小写统计字符串内不重复字符

在C2单元格输入以下公式。

=COUNT(0/FREQUENCY(SEARCH(MID(A2,ROW($1:$99),1),A2),ROW($1:$99)))

8  FREQUENCY函数按重复次数生成数据

如下图所示,需要在E列按B列指定的重复次数生成A列数据的列表。

提供两种解法,并分别解析公式原理。

在E2单元格输入以下数组公式,按<Ctrl+Shift+Enter>结束输入,将公式向下复制到D11单元格。

=INDEX(A:A,1+MATCH(1,FREQUENCY(ROW(A1),SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$4)))),))&""

再多提供一种解法。这种解法是普通公式,直接按Enter输入即可。

=LOOKUP(1,0/FREQUENCY(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$9)),">0")),A:A)&""

9  FREQUENCY函数提取不重复的前3名成绩

下图展示的是某班级的考试成绩表,现在需要统计前3个不重复的名次对应的成绩。如成绩表中最高分98有两个学生并列,但只占用一个名次。

在E2单元格输入以下公式,将公式向下复制到E4单元格。

=LARGE(IF(FREQUENCY(B$2:B$7,B$2:B$7),B$2:B$7),ROW(A1))

10  FREQUENCY函数查找最接近目标值的数

某企业组织员工竞猜销售额,对猜的最准的给予奖励,如下图所示,左侧是数据源区域,右侧的黄色单元格位置要使用公式智能判断猜的最准的员工姓名。

在D2单元格输入以下公式。

=LOOKUP(1,0/FREQUENCY(0,(B2:B8-E2)^2),A2:A8)

11  FREQUENCY函数按条件提取不重复值

结合下面这个具体案例,介绍根据条件提取不重复值列表的方法。

表格左侧是数据源,右侧的黄色区域需要写公式来提取不重复值,而且要根据D2选择类别而自动更新结果。

在E2单元格输入以下公式,并向下填充。

=LOOKUP(1,0/FREQUENCY(1,ISNA(MATCH($B$1:$B$11,E$1:E1,))*($A$1:$A$11=D$2)),B:B)&""

如果你觉得有用,就分享给朋友们看看吧~

别忘了帮忙去底部点赞

(0)

相关推荐