总结篇--FREQUENCY函数使用终极帖

来源:Excel应用之家

很多小伙伴们可能是第一次听说FREQUENCY这个函数。FREQUENCY这个函数是EXCEL最重要的函数之一,掌握了它,你就掌握了打开更高水平之门的钥匙。FREQUENCY函数计算数值在某个区域内的出现频率,然后返回一个垂直数组。

01

分段计频

我们首先选择单元格区域E2:E4,然后输入公式“=FREQUENCY($B$2:$B$10,$D$2:$D$4)”,CTRL+SHIFT+ENTER三键回车,并向下拖曳即可。

请注意,这里需要比分段区域多选择一个单元格,以存放大于最后一个分段的数值的频率。

02

单个数值计频

和上例相同,我们要比分段区域多选择一个单元格,然后输入下面的公式。

第一种情况:

数值71在分段区间内,FREQUENCY函数在大于等于71的最小值(75)所在的位置计频1

第二种情况:

数值99大于分段计频的所有数值,FREQUENCY函数在多出来的位置计频1

第三种情况:

数值3小于分段计频的所有数值,FREQUENCY函数在大于等于3的最小值(55)位置计频1

提问:如何把计频记在最大值呢?

03

求最大连续次数

求最大连续次数,在单元格C2中输入“=MAX(FREQUENCY(ROW(1:17),(A1:A16<>A2:A17)*ROW(1:17)))”即可。

思路:

  • (A1:A16<>A2:A17)*ROW(1:17)得到一个内存数组{1;2;0;0;0;6;0;0;9;0;0;0;0;14;0;16;#N/A}

  • 利用FREQUENCY函数将ROW(1:17)在上述内存数组中计频,得到{1;1;0;0;0;4;0;0;3;0;0;0;0;5;0;2;1}

  • 利用MAX函数求得最大值,即为最大连续次数

求某一数字最大连续次数是,在单元格C5中输入“=MAX(FREQUENCY(ROW(1:17),(A1:A17<>1)*ROW(1:17)))-1”即可。

思路:

  • 思路同上面相似。只是在最后要减去1,原因是计频点的数字不是要求统计的那个数字,因此在计频时多记录了一个,因此要减去。

04

求不重复个数

下例中我们来求不重复数据的个数。这个问题我们以前也介绍过了很多种方法,利用SUM+COUNTIF可以求得不重复个数,也可以利用INDEX+SMALL+IF+MATCH可以求得不重复的清单。

在单元格D3中输入“=COUNT(0/FREQUENCY($B$2:$B$11,$B$2:$B$11))”即可。

思路:

  • 利用FREQUENCY函数计频只记在大于等于当前数据的最小数据的位置上这一特点,对$B$2:$B$11进行计频,得到{2;1;2;0;1;1;2;0;1;0;0},显示为“0”的表示该位置上的数据由重复了

  • 0/FREQUENCY($B$2:$B$11,$B$2:$B$11)得到{0;0;0;#DIV/0!;0;0;0;#DIV/0!;0;#DIV/0!;#DIV/0!}

  • 利用COUNT函数忽略错误值的特点,统计出“0”的个数,也就是不重复数据的个数了

05

求某一条件下的最大值

如下例,求A列中绝对值最大的数。

求最大的数时,在单元格C2中输入“=LOOKUP(,0/FREQUENCY(-9^9,0-(A2:A7)^2),A2:A7)”即可。

思路:

  • 0-(A2:A7)^2部分,(A2:A7)^2是处理负数的一种方法。0-(A2:A7)^2部分让其都变成负数

  • -9^9是一个足够小的负数,利用FREQUENCY函数在0-(A2:A7)^2中最小的那个值的位置上计频

  • 利用LOOKUP函数求得该计频位置上的数值

求最大的整数时,在单元格C7中输入“=LOOKUP(,0/FREQUENCY(-9^9,0-(A2:A7&100)^2),A2:A7)”即可。

思路:

  • 和上面思路大同小异。只需要注意一点:(A2:A7&100)^2,这部分使整数变得更大,小数微小变化,以便能抓取到整数

06

求某一条件下的第二大值

在单元格C2中输入“=LOOKUP(,0/FREQUENCY(1-MAX((A2:A7&100)^2),0-(A2:A7&100)^2),A2:A7)”即可。

思路:

  • 和上一例相似。1-MAX((A2:A7&100)^2)部分保证了其大于分段区间中的最小值,从而最终可以提取到第二大的值了

07

查找最接近的值

在单元格C5中输入“=LOOKUP(,0/FREQUENCY(0,(A2:A9-C2)^2),A2:A9)”即可。

思路:

  • (A2:A9-C2)^2部分,越接近,差值越小

  • FREQUENCY函数在大于0的最小值的位置上计频

  • LOOKUP函数取得数值

08

提取单条件下不重复项

在单元格E2中输入“=LOOKUP(,0/FREQUENCY(1,ISNA(MATCH($B$1:$B$12,$E$1:E1,))*($A$1:$A$12=$D$2)),$B$1:$B$12)”并向下拖曳即可。

思路:

  • 利用MATCH函数查找$B$1:$B$12在E列的位置信息,并利用ISNA函数将结果值转换为逻辑值后和条件$A$1:$A$12=$D$2的逻辑值结果相乘,得到一组内存数组{0;1;1;1;1;1;0;0;0;0;0;0}

  • FREQUENCY函数计频

  • LOOKUP函数取得数值

09

按条件计数

仍旧是上面这个例子。现在我们要求一下供应商A提供了多少种规格的产品。

在单元格F2中输入“=COUNT(0/FREQUENCY(ROW(A:A),MATCH($B$2:$B$12,$B$2:$B$12,)*($A$2:$A$12=D2)))-1”即可。

思路:

  • MATCH($B$2:$B$12,$B$2:$B$12,)*($A$2:$A$12=D2)部分按条件取得位置信息

  • FREQUENCY函数计频

  • COUNT函数统计数量

  • 由于计频时多计频了一位,所以最后要减去1

(0)

相关推荐

  • 试用期就破格升职加薪?这位新同事做的Excel自动查询表,我服!(建议收藏)

    每天一点小技能 职场打怪不得怂 编按:大型公司都会要求库存管理中心的员工学会制作"进出货查询表".在基于先进先出的原则下,能否熟练地使用EXCEL完成日常进出货的查询和记录工作,是 ...

  • 向下匹配大家都会,向上匹配还会吗?

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 日常的EXCEL查找引用操作应用中,我们经常会遇到的是自上向下或自左向右的查找.水平方向的逆向查找我们有很多方法 ...

  • 多条件统计不重复数据个数,万金油还能包打天下吗?

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 万金油经典函数组合是我们在实际工作中经常会用到的函数组合.在一对多及多对多查询.提取不重复清单的时候它发挥着巨大 ...

  • FREQUECY新玩法—提取符合某条件下的最大值

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 小伙伴们,我们都知道FREQUENCY函数是EXCEL中的计频函数,统计数 ...

  • lookup也能实现一对多的查询

    最近对怎么用函数来实现筛选的功能有点想法,所以就写几篇关于这个问题的文章.昨天说了用vlookup函数来实现一对多的查询,也就是筛选的功能.其实用lookup函数也可以实现这个功能,接下来就说一说如何 ...

  • 如何提取源数据中第n大的数字?函数表示这不是事儿!

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 小伙伴们,我在帖子总结篇--FREQUENCY函数使用终极帖中介绍过FRE ...

  • 只有想不到,没有做不到

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 小伙伴们,今天这个帖子是在我参加的一个群中做的练习题.今天特别写出来分享给 ...

  • frequency法实现按指定次数重复内容

    先看下源数据和要实现的效果.下图左表是源数据,包含要重复的内容和次数,如①所示.右边两列是重复后的效果,如②所示.也就是A重复2次,B重复3次,以此类推. 首先,我们用辅助列的方法来完成.在C列添加辅 ...

  • 多对多的数据查询技巧你掌握了吗?如果还不会,立在墙角打屁屁!

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 多对多的数据查询是我们应对日常工作的必备技能之一.这不,小明同学有遇到了工作中这样一个问题. 题目要求我们统计在 ...

  • lookup搭配frequency,干活不累!

    同学们,大家好.今天和大家分享的是如何根据姓名查找合并单元格中的部门.先来看下源数据和查询后的效果.下图左表是各部门名单表,其中部门列是合并单元格.现在要根据姓名来查询出对应的部门,效果如右表所示.E ...

  • 如何把数字提取出来

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 我们在日常的工作中遇到的最多的问题无外乎就是文本处理.统计求和查找引用了.今天就继续和大家分享一道文本处理方面的 ...

  • 你知道有哪些公式组合可以提取唯一值?

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 下面是一份某商场某些产品的进货明细.由于各个品牌的商品中还有不同型号的进货 ...

  • 又是合并单元格惹的祸,不过有技术,就能解决问题!

    送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 在数据录入时有一个原则,就是尽量不用合并单元格,避免在后续的统计过程中出现问题.今天要向大家分享的这个案例就是这 ...