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 将忽略空白单元格和文本。
• 对于返回结果为数组的公式，必须以数组公式的形式输入。

应用示例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：公式2：通过应用示例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中连续出现最多的次数


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

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

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

解读INDIRECT函数 http://www.exceltip.net/thread-2083-1-1-2.html
【示例文件】


通读完毕，其实看来INDIRECT很简单，就两个参数，一个是代表引用的字符串，一个是选择引用样式。
首先，我们选择熟悉的A1引用样式来解读，即默认使用一个参数或者第2参数为TRUE或非0数值：Ref在函数参数中一般指单元格引用；text则一般指字符串。这句话中，最重要的是告诉了我们INDIRECT的第一参数尽管可以变化多端，但最终必须是单元格引用。
如图：A1单元格输入"B2"，B2单元格输入"C3"，C3单元格输入"你找到这里了吗？"

【用法1】第1参数直接就是表示某单元格区域的字符串，比如：
示例1：=INDIRECT("A1")——返回对A1单元格的引用，得到"B2"
示例2：=INDIRECT("A1:C2")——返回对A1:C2单元格区域的引用。

【用法2】第1参数是引用，间接指向某单元格区域，比如：
=INDIRECT(A1)
因为A1单元格值"B2"，那么这个公式就是=INDIRECT("B2")，最终返回的是对B2单元格引用，得到"C3"；
再绕远点
=INDIRECT(INDIRECT(A1))
那么这个公式第1步变为：=INDIRECT(INDIRECT("B2"))；第2步变为=INDIRECT("C3")，因而最终返回对C3单元格的引用，得到"你找的这里了吗？"

【用法3】第1参数是定义的名称，注意，必须是引用单元格或者可以表示单元格引用的字符的名称，比如：
按CTRL+F3，定义名称X="A1"
=INDIRECT(X)——返回对A1单元格的引用；
在B1输入"X"，然后用=INDIRECT(B1)得到#REF！错误，或者=INDIRECT("X")也同样是错误，这是因为"X"是一个单纯的字符，不是合法的单元格引用，而INDIRECT(X)中的X是一个名称，它的值是字符"A1"，而"A1"是可以转换为合法单元格引用的文本。
定义名称Y=OFFSET($A$1,,,3,3)
则=INDIRECT(Y)  得到{"C3",#REF!,#REF!;#REF!,"你找到这里了吗？",#REF!;#REF!,#REF!,#REF!}，即把名称Y中对A1:C3的值分别代入，再逐一返回其对应的引用。不是合法单元格引用就返回错误。
通过上面3中用法，基本可以理解INDIRECT函数对第1参数的要求。

3楼通过对INDIRECT函数第1参数的示例讲解了简单的单元格引用。对与R1C1引用样式，INDIRECT函数的第2参数应使用FALSE或0或简写为逗号，比如=INDIRECT("Sheet1!RC",FALSE)、=INDIRECT("Sheet1!RC",0)、=INDIRECT("Sheet1!RC",)——都可以引用Sheet1表的相同位置（比如公式在C2，就引用Sheet1表的C2单元格）

在此基础上，继续拓展，讲解一下这个函数的几个典型应用：
一、跨工作表、簿引用
=INDIRECT("Sheet1!A1")——引用Sheet1表的A1单元格。
工作表名是变量，利用文本合并符：
=INDIRECT(A1&"!A1")，然后在A1单元格中输入工作表名称，比如"Sheet1"
同理，可以跨工作簿引用，比如=INDIRECT("[BOOK1.XLS]SHEET1!A1")等。
注意：完整的写法应该是在工作表名称前后加单引号，比如：=INDIRECT("'Sheet1'!A1")——尤其是工作表名称命名含有空格等字符的情况。
【应用】数据有效性、条件格式跨表引用，报表设计中本期进度及累计进度值计算等（见1楼附件）。

二、防止删除单元格产生错误
1、数组公式中常用的SMALL(IF(条件,row($1:$10),……假设删除第1行，则公式中的row变为Row($1:$9)
2、公式=A1，当A1单元格被删除时，返回#REF!错误。
这两中情况用INDIRECT都可以避免，比如ROW(INDIRECT("1:10"))，INDIRECT("A1")，因为"1:10"是一个文本，不会随引用改变而发生改变（可能这就是帮助文件中那句拗口的话想表达的意思吧）

三、多行多列求取不重复值（见1楼附件）
=INDIRECT(TEXT(MIN(IF(COUNTIF(D$1:D1,A$1:B$4)=0,ROW($1:$4)*10^3+COLUMN(A:B),65536256)),"R0C000"),FALSE)&""
将满足条件的行、列号转换为一个和值，并用text转为R1C1引用样式，再用INDIRECT返回引用。

[ ]

[biggrin]
好帖子，阅读了

dingyixia``````````