excel菜鸟和高手（EXCEL经验分享十二）

如果你对数组函数的概念还是有些懵懂的的话，那就这样去理解：凡是一般函数涉及到一个单元格的、一个逻辑判断条件的，都可以换成多个单元格或者多个逻辑判断条件。当然这个话不能太绝对，这个只是我在学习数组函数时候的一个感觉。好比LEFT函数，他是对一个单元格内的内容从左边取字符，但是他也可以从多个单元格内的每一个单元格从左边取字符形成了一个数组；又比如IF函数的第一个参数是一个条件，但这个条件可以设置成多个比较，也可以形成数组函数。总之，数组函数的概念简单点说是一个变成了多个。

当然遇到问题并非只有数组函数才能解决，数组函数我在上一节的内容说过他是函数的升级版，肯定不是全新的函数，只不过有些问题需要通过几步来完成的通过数组函数有时候一步就可以完成。

本福特定律

说到数组函数的应用，我首先就用本福特定律来举例了。

本福特定律，也称为本福特法则，说明一堆从实际生活得出的数据中，以1为首位数字的数的出现概率约为总数的三成，接近直觉得出之期望值1/9的3倍。推广来说，越大的数，以它为首几位的数出现的概率就越低。它可用于检查各种数据是否有造假。

这个定律应用到财会数据中最合适不过了，因为会计数据充满了大量的数字。而他的要求就是每个数字的第一个数字所出现的概率大致在本福特定律规定的范围内，否则就差不多是说这个帐是假账了。

先来看下每个数字在本福特定律中出现的概率。

数字1：30.1%；数字2：17.6%；数字3：12.5%；数字4：9.7%；数字5：7.9%；数字6：6.7%；数字7：5.8%；数字8：5.1%；数字9：4.6%

可见每个数字的概率并非1/9左右。这些很多都是百度百科上贴来的。

那么如果你有一堆财务数据，如何用EXCEL来验证本福特定律呢？

其实也不是很难，第一步先把所有数字的第一位数字取出来，所以先用LEFT函数；第二步就是进行统计1-9的数字分别出现了多少次，这种统计可以用COUNTIF函数，当然有9个数字，你就分别要做9次统计了；第三步就是将每个数字出现的次数和总的数字个数求得概率了。

如果用数组函数那基本上可以说是一步到位了。首先LEFT函数是可以对一片区域内的所有单元格从左边取出字符的。其次，当LEFT函数形成数组之后就和1-9这些数字进行比较，比如先和1进行比较得到的就是TRUE和FALSE的数组了。我之前也说过TRUE和FALSE其实就是1和0，如果对一堆0和1的数字进行求和，得到的结果自然就是等于数字1的个数了。好，我这就演示一下，当然为了方便显示，我这里所用到的数据量不大，另外所有数字都是我随机生成的。演示中，我会拆解步骤每一次公式我都用F9来显示一下结果，这样大家看起来就会明白很多了。

大家看到没有，当我LEFT函数取出第一位的数字然后比较的不是数字1而是字符串”1”，是有双引号的，所以说LEFT函数所形成的结果是字符串格式的，如果和数字1进行比较即便看起来都是1，但实际是不一样的。

上面的演示到最后一步，我们得到了TRUE和FALSE的数组，如何转化成0和1的数组呢，那就简单了把TRUE和FALSE都乘1，就可以了。想一想就能理解，0和1相乘得到0，1和1相乘得到的还是1。然后对这个数组总的进行一次求和，得到的就是数字1出现的次数了。那么下面我就做一个全面的演示，把数据量扩大一点，当然样本数字还是随机的，所以得到的最后结果可能是“假账”。

得到的结果异常的奇怪，不过方法是没错的，这里面特别注意，最后一步需要依次分别按下CTRL SHIFT ENTER，表示这个函数是数组函数。

从这个例子中大家可以看到，如果你不用数组函数也可以用其他方法进行统计，无非数组函数更为简便。另外一点就是数组函数对于单元格区域的操作非常有用，我们可以想想，很多的一般函数都是对一列或者一行单元格进行操作的，对一片单元格操作就比较困难，甚至可能要拆分几步来做。这大概就是数组函数的优势了。

数组函数的复杂问题

这个题目我也不知道怎么取，可能光看题目不知所谓吧。我这里举一个例子，这个例子在现实工作中可能不太出现，但是无论如何如果你看懂了这些内容，那说明数组函数你是彻底理解了。

结合上面的例子，我们知道数组函数在单元格区域内进行操作是很简便的，另外TRUE和FALSE的数组也是非常有用的，根据这两个特点，我举个例子。比如有一片区域从A1到F10各个单元格内有国家的名字，现在要统计出出现次数最多的国家的名字。 如图所示。

有一个假设，就是这些国家事先并不知道都涉及到哪些国家，另一个假设是出现次数最多的国家只有一个。我这里用的是随机函数，所以我也不知道最大值是否唯一了。这个问题，不知大家在日常工作中是否有遇到类似的，总之这类问题不通过数组，也是可以通过其他操作分步骤来解决的。

首先，要统计出现次数的，自然就想到COUNT计数一类的函数，另外他是需要将单元格区域内的所有单元格都和这个区域的单元比较做统计，所以肯定是COUNTIF带条件计数的函数。COUNTIF函数第一个参数是计数区域，第二个参数是条件，计数区域这没什么好说的，至于条件我们一般都是等于某某值，但其实利用数组的概念把条件设置为就是计数区域，那么我们就理解为将计数区域内的每个单元格都计数一次，所以形成的结果自然就是一个个计数结果的数组，有多少个单元格就有多少计数值。

COUNTIF函数自然就是：=COUNTIF(A1:F10,A1:F10)，当然这是一个数组函数，所以需要依次按下CTRL SHIFT ENTER，大家可以试下并且用F9来显示一下结果，可以看到出现一串数字，这些数字代表每个单元格在计数区域内出现的次数。如果你对这些数字求最大值即=MAX(COUNTIF(A1:F10,A1:F10))，你得到的只是出现最多的次数但并不知道是哪个国家。

接下去如何找到对应最大次数的国家是有点复杂的。不过数组看起来是一串数字，实际上顺序还是按照区域单元格的顺序来排列的，就是从A1到F10，所以接下去的步骤自然是要得到行和列来确定单元格。如果用最大值去等于比较计数数组函数，那么得到的就是一串TRUE和FALSE的数组了，即MAX(COUNTIF(A1:F10,A1:F10))= COUNTIF(A1:F10,A1:F10)，而这串0和1的数组也是按照单元格顺序来排列的，正因为只有0和1，所以乘以计数区域每个单元格的行号我们可以得到最大值对应的行号，而非最大值只能是0，同理列号也可以这么处理。而行和列的函数分别是ROW和COLUMN，因此针对行来说公式就是=(MAX(COUNTIF(A1:F10,A1:F10))= COUNTIF(A1:F10,A1:F10))*ROW(A1:F10)，得到的结果不是0，就是行号，然后在求一次最大值就知道行号了，同理乘以COLUMN(A1:F10)并求最大值就得到列号了，通过INDIRECT引用函数以及我们得到的行号列号，就知道所在单元格了。

上面的做法其实比较复杂，还是可以简化的，简化的方法就是利用TEXT函数。TEXT函数是通过自定义的格式来返回结果，我之前说过INDIRECT引用单元格的格式可以是R1C1格式，即通过行号列号来确定单元格，我们得到行号如果都乘以100，然后在加上列号，再通过TEXT函数转化就可以得到R1C1格式了。

到底什么意思呢？比如我们得到的行是2，列是5，那么行乘以100再加上列号，得到结果205，如果TEXT(205,”R0C00”)，会得到什么结果呢？首先公式中的0是数字占位符C00表示列在1-99之间，即最多两位数，如果不满两位数则用0代替，所以205通过TEXT转化结果就是R2C05，而这个字符串格式对于INDIRECT函数他是认可的，即INDIRECT(TEXT(205,”R0C00”,FALSE)，函数会知道是第二行第五列单元格的值。这种做法首先要保证你的单元格区域的列最多不超过100，也就是说先确定列最多是多少，如果最多三位数，那么行就乘以1000，然后格式上就是“R0C000”，以此类推下去。

好了说了这么多，大家可能都晕了。经过我这么文笔不是特别好的分析之后，最终公式就定格为：

=INDIRECT(TEXT(MAX(((MAX(COUNTIF(A1:F10,A1:F10))=COUNTIF(A1:F10,A1:F10)))*ROW(A1:F10)*100 COLUMN(A1:F10)),"R0C00"),FALSE)

最后不要忘记依次按下CTRL SHIFT ENTER。

这样的例子够复杂的了吧，所以我之前提到数组函数是可以解决各种稀奇古怪甚至变态的问题。我们虽然工作中会面临这样那样的问题，但我相信方法总比困难多，数组函数只是提供给大家的一种方法，毕竟无论黑猫白猫，能抓到老鼠就是好猫。

数组函数介绍完了，如果大家还不能很理解的话，我建议先从小问题来分析，这其中多多利用F9快捷键显示结果，来验证自己的想法，慢慢的循序渐进的学习，那数组函数就根本不是那么难学了。我建议大家可以从个人所得税练练手，如何用数组来计算个人所得税。个人所得税关键就是在如何判断数字在某一个范围内，那么这个范围已经规定好了差不多有7档，那就是一个7个数字组成的数组，所以大家可以研究下。