FREQUENCY函数的精彩应用
发布时间:2022-06-23 11:20:40
FREQUENCY函数以一列垂直数组返回一组数据的频率分布,在之前的文章中,我们详细解了该函数的基础语法及应用示例。下面,我们以示例解FREQUENCY函数的一些精彩应用。
示例1:统计不同数值的个数
如下图1所示的工作表,在单元格区域A1:A8中有一列数值,我们需要使用公式算出该区域中不同数值的个数。此时,可以使用公式:
=SUM(–(FREQUENCY(A1:A8,A1:A8)>0))
计算结果为5,如图1中的单元格D2所示。在列表中,不同的数值是1、3、5、6、7共5个。
图1
注意到,这个公式不是数组公式,但它在计算过程中能够生成数组。
首先,公式会转换成:
=SUM(–(FREQUENCY({1;1;3;5;6;3;1;7},{1;1;3;5;6;3;1;7})>0))
由于FREQUENCY函数对于重复的值统计的数为0,并且计数区间有8个,会产生9个统计数字。因此,上面的式子又被转换成:
=SUM(–({3;0;2;1;1;0;0;1;0}>0))
从上面式子中的数组可知,要统计的区域中有3个1,2个3,1个5,1个6,1个7。这个数组与0比较,生成逻辑值数组:
=SUM(–({TRUE;FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}))
公式中的双减号将逻辑值转换成数字,其中TRUE转换成1,FALSE转换成0,得到:
=SUM({1;0;1;1;1;0;0;1;0})
求和得到结果5。
也可以使用SUMPRODUCT函数:
=SUMPRODUCT(–(FREQUENCY(A1:A8,A1:A8)>0))
获得同样的结果。
上面的例子中,统计的数值全部都是数字,如果单元格区域中除了数字外,还有文本呢?例如下图2所示的工作表,求单元格区域A1:A10中不同数值的个数。
图2
这里使用的是数组公式:
=SUM(–(FREQUENCY(IF(A1:A10<>””,MATCH(“~”& A1:A10,A1:A10 & “”,0)),ROW(A1:A10)-ROW(A1)+1)>0))
公式中,A1:A10<>””检查是否是空单元格以便忽略空单元格,得到数组:
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}
在MATCH函数中的波浪号(~)用于预防单元格中起始字符为通配符的情况。如果你确定单元格中的值不会以通配符开始,那么可以使用””&代替”~”&。如果你确定单元格中的值都是文本,那么也不需要””&。注意,如果任何单元格中包含通配符(特别是*),那么公式可能失败。
在MATCH函数的参数lookup_array中通过使用&””将值强制转换为文本。如果单元格中的值都是文本,则可将其删除。
MATCH函数返回每个数值出现的位置:
{1;2;2;4;4;6;7;8;9;9}
那么,公式中IF函数转换为:
IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},{1;2;2;4;4;6;7;8;9;9})
结果为:
{1;2;2;4;4;6;FALSE;8;9;9}
FALSE值对应着空单元格。
公式中的ROW(A1:A10)-ROW(A1)+1)返回一个连续的整数值组成的数组:
{1;2;3;4;5;6;7;8;9;10}
此时,公式转换为:
=SUM(–(FREQUENCY({1;2;2;4;4;6;FALSE;8;9;9},{1;2;3;4;5;6;7;8;9;10})>0))
与前面的例子所一样,公式再转换为:
=SUM(–({1;2;0;2;0;1;0;1;2;0;0}>0))
式子评估为:
=SUM(–{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE})
得到:
=SUM({1;1;0;1;0;1;0;1;1;0;0})
最后得到结果:
6
示例2:统计不重复值的个数
不重复的值指单元格区域中只出现1次的值。例如,下图3所示的工作表,在单元格区域A1:A8中数值5、6、7只出现了1次,因此有3个不重复值。统计单元格区域A1:A8中不重复值个数的公式为:
=SUM(–(FREQUENCY(A1:A8,A1:A8)=1))
公式计算仅出现1次的值之和。
图3
如果要统计的单元格区域中不仅有数字,还有文本,如下图4所示,可以看出,不重复的值为1、s、c共3个。公式为:
=SUM(–(FREQUENCY(IF(A1:A10<>””,MATCH(“~”& A1:A10,A1:A10 & “”,0)),ROW(A1:A10)-ROW(A1)+1)=1))
这是一个数组公式。
图4
上述两个公式的详细过程参见示例1,本例中只是将示例1中的大于号改成了等于号。
示例3:统计满足条件的不同值的个数
如下图5所示,要统计单元格区域A1:A10中的内容是“是”对应的单元格B1:B10中不同值的个数,可以看出,这些值是1、2、a、3,共有4个不同值。公式为:
=SUM(–(FREQUENCY(IF((A1:A10=C2)*(B1:B10<>””),MATCH(“~”& B1:B10,B1:B10 & “”,0)),ROW(B1:B10)-ROW(B1)+1)>0))
这是一个数组公式。
图5
这个公式与上例中的公式相似,只是在公式中包含(A1:A10=C2)*从而添加了一个“与”条件。
示例4:统计满足条件的不重复值的个数
如下图6所示,要统计单元格区域A1:A10中的内容是“是”对应的单元格B1:B10中不重复值的个数,可以使用数组公式:
=SUM(–(FREQUENCY(IF((A1:A10=C2)*(B1:B10<>””),MATCH(“~”& B1:B10,B1:B10 & “”,0)),ROW(B1:B10)-ROW(B1)+1)=1))
图6
示例5:出现最多的数值出现的次数
如下图7所示,我们可以看出,在单元格区域A1:A10中1出现的次数最多,共出现了5次,使用下面的公式可以得到这个结果:
=MAX(FREQUENCY(A1:A10,A1:A10))
图7
公式中,FREQUENCY(A1:A10,A1:A10)返回{5;0;3;2;0;0;0;0;0;0;0},MAX函数从这个数组中返回最大值5。
如果单元格区域中不仅有数字还有文本,如何统计最多的数值出现的次数呢?如下图8所示,出现次数最多的是2、a、3,都是2次。
图8
数组公式为:
=MAX(FREQUENCY(IF(A1:A10<>””,MATCH(“~”& A1:A10,A1:A10 & “”,0)),ROW(A1:A10)-ROW(A1)+1))
示例6:出现最少的数值出现的次数
如下图9所示,要统计单元格区域A1:A10中出现最少的数值出现的次数,可以看出,出现最少的数值是3、5、6,都是1次。数组公式为:
=MIN(IF(FREQUENCY(A1:A10,A1:A10)>0,FREQUENCY(A1:A10,A1:A10)))
图9
因为要忽略数组中可能出现的0,因此公式比统计出现最多的数值出现的次数要复杂一些。
公式中FREQUENCY(A1:A10,A1:A10)返回{5;0;2;1;0;0;1;0;0;1;0},因此公式转换为:
=MIN(IF({5;0;2;1;0;0;1;0;0;1;0}>0,{5;0;2;1;0;0;1;0;0;1;0}))
接着,公式评估为:
=MIN({5;False;2;1;False;False;1;False;False;1;False})
由于MIN函数忽略逻辑值,因此返回值1。
正如前面的例子中一样,如果单元格区域中还包含文本呢?如下图10所示,单元格区域A1:A10中的1、s、c都只出现了1次,数组公式为:
=MIN(IF(FREQUENCY(IF(A1:A10<>””,MATCH(“~”& A1:A10,A1:A10 &””,0)),ROW(A1:A10)-ROW(A1)+1),FREQUENCY(IF(A1:A10<>””,MATCH(“~”& A1:A10,A1:A10 & “”,0)),ROW(A1:A10)-ROW(A1)+1)))
图10
示例7:指定值连续出现的最大次数
如下图11所示,想要求出单元格B2中指定的值1在单元格区域A1:A10中连续出现的最大次数,可以使用数组公式:
=MAX(FREQUENCY(IF($A$1:$A$9=B2,ROW($A$1:$A$9)),IF($A$1:$A$9<>B2,ROW($A$1:$A$9))))
图11
公式中,利用IF($A$1:$A$9<>B2,ROW($A$1:$A$9))生成统计区间,真是一个妙招!
示例8:计算非连续区域的满足某条件的平均值
如下图12所示,要计算三个超市水果销售量的平均值,但不能包括销售量为0的水果。
图12
由于示例数据较少,我们可以手算出平均值:
(50+38+68+21+15+21+19+25)/8=32.125
通常想到的是使用AVERAGEIF函数求条件平均值,但是示例数据在三个不连续的区域,下面的公式:
=AVERAGEIF((C3:C7,E3:E7,G3:G7),”<>0″)
返回#VALUE!,因为AVERAGEIF函数不能处理非连续区域。
使用SUM函数和COUNT函数相结合呢?
=SUM(C3:C7,E3:E7,G3:G7)/COUNT(C3:C7,E3:E7,G3:G7)
结果是17.133,因为COUNT函数将0值单元格也统计了。
使用COUNTIF函数代替COUNT函数忽略0值单元格:
=SUM(C3:C7,E3:E7,G3:G7)/COUNTIF((C3:C7,E3:E7,G3:G7),”<>0″)
返回#VALUE!,因为COUNTIF函数不能用于非连续区域。
以上公式都不得到正确的结果,因为不能够获得这些非连续单元格区域中非零值的个数。可以使用FREQUENCY函数来解决,公式为:
=SUM(C3:C7,E3:E7,G3:G7)/INDEX(FREQUENCY((C3:C7,E3:E7,G3:G7),0),2)
FREQUENCY函数可用于非连续单元格区域,FREQUENCY((C3:C7,E3:E7,G3:G7),0)返回包含两个值的数组:
{7;8}
即非连续单元格区域数值组成的数组中等于0的个数以及大于0的个数。
传递给INDEX函数:
INDEX({7;8},2)
得到结果8,即为非连续单元格区域中大于0的个数。
结语
充分利用FREQUENCY函数的基本特性,结合实际场景灵活运用,往往会为我们的实际应用提供一些巧妙的解决方案。


猜你喜欢
- 在Win8系统中,关闭、重启、注销电脑等操作对入门者是比较困难的事情,由于Win7或者XP等系统的关操作都是通过开始菜单进行的,而Win8系
- win10玩cf穿越火线cf不能全屏显示该怎么办?升级win10以后,玩游戏不爽了,在玩穿越火线的时候,竟然不能全屏显示,真的很郁闷,该怎么
- 问题概述在MacOS下,Chrome还是很受欢迎的,但是经常会导致CPU占用过高,比如页面包含Flash,风扇就会呼呼地转。通常来说Chro
- 我们在excel中输入文字的时候,默认对齐方式都是左对齐的,但是我们想要设置为居中该如何操作呢?下面随小编一起看看吧。excel文字居中输入
- 回想第一次使用 macOS ,找不到关机键,没事还在桌面点右键找刷新。作为 10 年的 Windows 党表示真的很脸红,不同操作系统的操作
- 微软先前推送了19H1慢速预览版18362,但因为出现了重大Bug遭到撤回,现在又要重新推送,下文小编就为大家带来了详细介绍,一起看看吧随着
- 升级win10正式版后,很多用户都会立刻选择在新系统中安装一些自己常用的软件。不过,有位用户在win10中安装比较旧版软件时,却遇到了错误提
- Win10怎么关闭系统提示音?电脑任何操作都会有提示音,用耳机听歌,每次都能听到提示音,真的很烦,该怎么才能关闭win10系统提示音呢?下面
- 随着Win11的推出,很多用户都迫不及待升级上新系统了。但是有用户升级之后发现不习惯新系统界面和操作,就想将Win11装回原先的Win7系统
- 当WPS文档编辑文字后,保存发给别人,如果别人使用的是OFFIE办公软件,就会遇到打不开文件的问题。这是什么原因呢?怎么解决?下面小编来告诉
- 百度阅读APP怎么导入本地文件?百度阅读APP是款免费的电子小说阅读软件,用户们不但能够在软件中看到各种热门的小说,还可从网站上下载小说导入
- 格式工厂是一款强大的文件格式转换器,支持多种格式,让转换质量无破损,操作简单,一键即可实现快速的转换,那么怎么改变视频大小呢?相信还有用户不
- 有不少网友在电脑出现问题无法进入系统的时候,想要通过u盘装系统解决,但是不知道怎样用u盘装系统。现在u盘装系统的工具很多,今天小编就通过咔咔
- win10占空间怎么办?其实我们可以停用一些功能,下面小编就来教大家一些win10精简功能教程win10正式版即将推出,很多网友已经提前使用
- 使用Continuum模式下全新开始屏幕的操作方法:1、首先打开“注册表编辑器”,可以通过“Windows徽标键+R”打开“运行”,并输入“
- 怎么在VB6.0代码中添加注释语句?在打开VB6.0的时候,发现有很多注释语句,但是却不懂怎么添加注释语句。下面就是在VB6.0代码中添加注
- 现在使用Win10系统小伙伴有很多,但不少使用Win10小伙伴发现在玩红警的时候会一直卡,有时候甚至会被卡退的情况出现,那么这种问题应该怎么
- 微软在今天凌晨推出了Win11第四个预览版,版号为22000.100,很多用户都去更新了,但是有很多用户反应在更新完Windows11 22
- 最近不少使用win7系统的朋友反映说,自己在使用电脑的时候,电脑突然间就死机了,按啥键都没反映,最好只能强制关机。但是关机后电脑却出现了蓝屏
- 麦当劳小程序怎么免费领取小食?很多小伙伴都喜欢吃麦当劳,但是大家知道麦当劳小程序可以免费领取小食吗?下面就是麦当劳小程序免费领取小食的方法,