解读FREQUENCY函数

gouweicao78

函数学习资料之——解读FREQUENCY函数
【声明】原创文章，非允勿转

FREQUENCY(data_array,bins_array)

一个只有2个参数的貌似简单的统计函数，经过高手们的运用，在解决不重复问题和连续问题方面发挥出了奇妙的作用。

【帮助文件】解读

1、概述：计算数值在某个区域内的出现频率，然后返回一个垂直数组。例如，使用函数 FREQUENCY 可以在分数区域内计算测验分数的个数。由于函数 FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。注意：返回的是一个垂直数组，必须以多单元格数组公式的形式输入。参考：多单元格数组公式是如何输入的？

2、语法
FREQUENCY(data_array,bins_array)
Data_array   是一个数组或对一组数值的引用，您要为它计算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 将返回一个零数组。
Bins_array   是一个区间数组或对区间的引用，该区间用于对 data_array 中的数值进行分组。如果 bins_array 中不包含任何数值，函数 FREQUENCY 返回的值与 data_array 中的元素个数相等。

说明

• 在选择了用于显示返回的分布结果的相邻单元格区域后，函数 FREQUENCY 应以数组公式的形式输入。 也就是多单元格数组公式
• 返回的数组中的元素个数比 bins_array 中的元素个数多 1 个。多出来的元素表示最高区间之上的数值个数。例如，如果要为三个单元格中输入的三个数值区间计数，请务必在四个单元格中输入 FREQUENCY 函数获得计算结果。多出来的单元格将返回 data_array 中第三个区间值以上的数值个数。 这一点非常重要，经常有些人忘了这一点导致数组尺寸不匹配产生#N/A错误。
• 函数 FREQUENCY 将忽略空白单元格和文本。
• 对于返回结果为数组的公式，必须以数组公式的形式输入。

[ 本帖最后由 gouweicao78 于 2009-12-22 14:41 编辑 ]

补充内容 (2017-10-29 08:54):
在ExcelHome技术论坛的最佳学习方法：http://club.excelhome.net/thread-117862-1-1.html

gouweicao78

应用示例1：求分布频率。

如图：A2:A8存放成绩，C2:C5存放分段的间隔（注意成绩以整数为例，如果有0.5分，可以将69等改为69.9等。
解法：选择D2:D6（注意：比C2:C5多一个元素，垂直数组，不能选择横向的单元格哦），输入=FREQUENCY(A2:A8,C2:C5)并按下CTRL+SHIFT+ENTER结束，形成多单元格数组公式。

这个是FREQUENCY函数的基本用法。实质上，FREQUENCY函数返回的是一个内存数组，可以用于下一步运算。

应用示例2：统计不重复之一
如图：A2:A15包含空单元格、文本和有重复的数值，要统计共有多少个不重复的数值。
普通公式：=COUNT(1/FREQUENCY(A2:A15,A2:A15))
1、为什么是普通公式，而无须按三键形成数组公式？
猜测：设计FREQUENCY函数本身返回的就是数组，也就是相当于给COUNT(1/{Frequency})一个类似于常量数组的待遇。

2、FREQUENCY(A2:A15,A2:A15)的运算解析过程


由此可见：FREQUENCY(data，data)——第1、2参数相同时：
1、第一次出现的数值，将返回其个数，第2次出现就返回0；
2、忽略空单元格和文本（直接跳过统计）

因此，1/Frequency，分母为0将返回#DIV/0!错误，再利用COUNT函数忽略错误值，统计不重复数值个数。

应用示例3：统计不重复之二
A2:A15都是数字{1;1;5;1;1;6;9;8;4;9;8;1;8;3}，没有空单元格格和文本。求第3大的数（2个9算1次，3个8算1次，因此不重复的第3大值是6）
公式1：

1. 
   
2. =LARGE(IF(FREQUENCY(A2:A15,A2:A15),A2:A15),3)
   

复制代码

公式2：

1. {=LARGE((FREQUENCY(A2:A15,A2:A15)>0)*A2:A16,3)}

复制代码

通过应用示例2的分析，可以理解，因为A2:A15没有需要忽略的东西，因此返回的数组保持了与A2:A15一一对应的关系（即第1次数值出现的位置，返回个数，第2次出现及以后都返回0），并且多一个元素。
对比两个公式，区别：
一是IF对Frequency函数多出的那一行元素并不感冒，公式1的IF用A2:A15或A2:A16都可以（前提，A16是空单元格）；而且在IF函数中，Frequency仍然享受了无需按三键形成数组公式的待遇；
二是作为数组相乘（Frequency>0）*A2:A16，要求元素个数一致，因此要记得A16也算进去。当然，公式2的A16位置返回的是0，如果A列有负数，或LARGE改SMALL，则统计会有问题。


应用示例3：统计“连续”问题
如图：统计A2:A15中连续出现最多的次数

公式：

1. =MAX(FREQUENCY(IF(A2:A14=A3:A15,ROW(2:14)),IF(A2:A14<>A3:A15,ROW(2:14))))+1

复制代码

利用IF函数，将上下行内容相同（也就是连续出现）的记录，返回为上行的行号（数值，FREQUENCY只玩数值）以及FALSE
利用IF函数，将上下行内容不同（也就是间隔的内容）的记录，返回为上行的行号以及FALSE

再利用frequency对两组行号和FALSE组成的数值进行统计，忽略FALSE，只计算：
FREQUENCY(满足条件的行号，不满足条件的行号)
得到满足条件的行号的分布频率，也就是连续出现次数-1

最后再用MAX(FREQUENCY)+1返回结果。

[ 本帖最后由 gouweicao78 于 2009-12-22 14:39 编辑 ]

biaotiger1

哈
坐版主的沙发
真不容易

谢谢

zhaozyuan

表示强烈……
的支持

zhaozyuan

原帖由 biaotiger1 于 2009-8-5 21:55 发表
哈
坐版主的沙发
真不容易

谢谢

你做的板凳了 沙发人家自己占着呢

biaotiger1

刚刚发现文件是07版的，正在遗憾中，，，发现后面有配图
兴奋中。。。。

biaotiger1

{=LARGE((FREQUENCY(A2:A15,A2:A15)>0)*A2:A16,3)}
求第N大的值公式又缩短了

syz105729913

占位学习，先下载收藏了

syz105729913

我想知道FREQUENCY的第二参数是乱序的时候它的分段点是怎么定的？
例   =FREQUENCY({1,2,4,6,7,8,9},{2,6,3,7,1})

gouweicao78

乱序的用法，除了类似COUNT(1/FREQUENCY(A1:C10,A1:C10))统计不重复个数外，还比较少用。