数组函数讲座.doc

1、数组公式讲座（一）：数组公式入门 数组公式是我自学的，下面写的东西可能和外面的相关参考书有出入，但结果和思路 是正确的。如有错误，请大家指正。 1、 数组公式的实现方法：在论坛中常可以看到许多在头尾带有“”的公式，有些 朋友把这些公式直接复制粘贴到单元格中，但没有出现正确的结果， 这是为什么呢？其实 这些都是数组公式，数组公式的输入方法是将公式输入后，不要直接按回车键（Enter）， 而是要同时按Ctrl+Shift+Enter，这时电脑自动会为你添加“”的。在论坛上，为了告 诉大家这是数组公式，故在公式的头尾都加上了“”。如果不小心按回车了，可以用鼠 标点一下编辑栏中的公式，再按Ctrl+S

2、hift+Enter。 2、数组公式的原理（自编）：数组公式，说白了就是同时对一组或几组数同时处理， 然后得到需要的答案。运用数组公式的最重要的原理是数于数之间一一对应 下面举一列说明一下（例 1）： A1:A10 有下列一排数： 32 14 34 25 36 21 33 12 12 16 第一问：求大于 20的数的和（此题亦可用 SUMIF 函数来处理，这里为的是讲清数组 公式的原理） 。答案为：181，公式为=SUM(IF(A1:A1120,A1:A11)，EXCEL 处理的原 理：首先由 IF 函数进行判断，一一对应，如果大于 20，则为本身，否则为 FALSE。经过 IF 函数的处理，

3、原来的 A1:A10 处理后变成： 32 FALSE 34 25 36 21 33 FALSE FALSE FALSE 然后用 SUM 函数进行求和，SUM 函数忽略文本。 第二问：求大于 20的数的个数（此题亦可用 COUNTIF 函数来处理，这里为的是讲清 数组公式的原理） 。答案为：6，公式为=SUM(IF(A1:A1120,1)，EXCEL 处理的原理： 首先由 IF 函数进行判断，如果大于 20，则为 1，否则为 FALSE。经过 IF 函数的处理，原 来的 A1:A10 处理后变成： 1 FALSE 1 1 1 1 1 FALSE FALSE FALSE 然后用 SUM 函数进 行

4、求和,亦可用 COUNT 函数进行计数。 第三问：求大于 20的数的个数减去小于等于 20的数的个数的值。 答案为：1，公式为=SUM(IF(A1:A1120,1,-1)，EXCEL 处理的原理： 首先由 IF 函数进行判断，如果大于 20，则为 1，否则为-1。经过 IF 函数的处理，原来的 A1:A10 处理后变成： 1 -1 1 1 1 1 1 -1 -1 -1 然后用 SUM 函数进行求和。 数组公式入门讲座之二（上） 10月 23日给大家出了三道题，其实就题目的难度来说，如果既会用函数，又懂些数组公式使用的话，那么我想这些题目应该都能解决的。下面通过对这几道题目的分析，来 讲讲数组公

5、式的运用。 第一题：求 1到 1000的和。1+2+3+4+.+997+998+999+1000=？ 这题，思路非常简单，就是如何生成一个 1到 1000这样子的数组，然后全部加起来。 所以，如果要用数组公式的话，你就得先问自己，到底需要一个怎样的数组才能达到目的。 那么，接下来，就要考虑，通过什么函数，才能达到这个目的。就这题来说，就会使用 ROW()函数，一个取得引用行号的函数，=ROW(1:1000)，通过这个函数就能生成这样一个 数组1;2;3;998;999;1000，然后用 SUM()进行求和，就得到答案了。额外提醒大家的是， 通过 SUM()这个函数，出来的结果就是一个值，不会是

6、数组的。 （COLUMN()函数也可以， 这里就不讲了） 。 答案：=SUM(ROW(1:1000)（CHENJUN 兄另一种使用 INDIRECT()的做法，是为了 避免行范围有行删除、插入，这是在实际使用中出现问题地解决办法，这次暂不讨论 =SUM(ROW(INDIRECT(“1:1000“)） 。 第二题：求在 1到 1000中，是 7的倍数的数的和，包括 7在内。7+14+21+28+.=？ 这题，是在上题的基础上，演变而来的，也就是一个数组的处理的问题。既然得到了 1到 1000这样子的数组，那么如何得到 7的倍数的数的数组？方法很多，下面一一讲解： 用的最多的方法是： =SUM(R

7、OW(1:1000)*(MOD(ROW(1:1000),7)=0) 因为是求和，把不符合条件的数字，变成 0，然后求和，就可以了，EXCEL 是如何处 理的，请看看数组讲座一。把公式写的标准化一些： =SUM(IF(MOD(ROW(1:1000),7)=0,ROW(1:1000),0) 其中，MOD(ROW(1:1000),7)是对 ROW(1:1000)生成的数组 1;2;3;998;999;1000的再处理，再生成的数组，从表面上来看新生成的数组 1;1;1;1;1;1;0;1;1;1，一点用都没有，但实际上包含了 1;2;3;998;999;1000能不能被 7整除的信息，即如果是 1，

8、那么对应的这个数不能 被 7整除，而如果是 0的话，能被 7整除，这样 IF(MOD(ROW(1:1000),7)=0,ROW(1:1000),0)再次生成一个我们都想要得数组，那就是 0;0;0;0;0;0;7;0;0;0;0;0;0;14;0;0;0，这样在用 SUM()就能得到索要的答案了。 从上面可以看出，在数组公式中，如果有 IF()参与，条件是数组公式，对或错有一个 是数组，或都是数组的话，需要一一对应。这例中，对是数组，他就和条件一一对应，才 会产生0;0;0;0;0;0;7;0;0;0;0;0;0;14;0;0;0的结果。 其他的方法： =SUM(IF(ROW(1:200)*7

9、1000,ROW(1:200)*7,0) 思路：直接生成一个 7倍数的数组，然后做一个1000 的判断，再求和。 =SUM(TRUNC(WEEKDAY(ROW(1:1000)/7)*ROW(1:1000)思路：使用 WEEKDAY()将数组转化为一个以1;2;3;4;5;6;7作为轮回的数组，正好和 7的倍数相呼应（当然如果用 =SUM(IF(WEEKDAY(ROW(1:1000)=7, ROW(1:1000),0)也是可以的） 。然后除以 7 后，再 TRUNC()后，变成0;0;0;0;0;0;1;0;0;0这样的数组，然后和 1;2;3;4;5;6;7;998;999;1000相乘，得出

10、结果。 这里，需指明的是，我在用 WEEKDAY()的时候，忽略了一个参数，那是默认值，星 期一是 1，以此类推；而 TRUNC()也忽略了一个参数，默认值为 0，即取整。 所以，思路从某种程度上讲，决定公式的使用方法，有些人排出来的公式很复杂，有 些人很简单，就是这个道理。大家在平时学用公式的时候，不仅要学习函数的使用方法， 更主要的是要学习思路，不要只求结果，不求过程。 最后一题，我想详细分析一下，加上最近有点忙，准备放在下周末，请大家谅解。CELL 全部显示全部隐藏返回某一引用区域的左上角单元格的格式、位置或内容等信息。 语法 CELL(info_type,reference) Info

11、_type 为一个文本值，指定所需要的单元格信息的类型。下面列出 info_type 的可能值及相应的结果。 如果 MICROSOFT EXCEL 的格式为 CELL 返回值 常规 “G“ 0 “F0“ #,#0 “,0“ 0.00 “F2“ #,#0.00 “,2“ $#,#0_);($#,#0) “C0“ $#,#0_);Red($#,#0) “C0-“ $#,#0.00_);($#,#0.00) “C2“ $#,#0.00_);Red($#,#0.00) “C2-“ 0% “P0“ 0.00% “P2“ 0.00E+00 “S2“ # ?/? 或 # ?/? “G“ yy-m-d 或 yy-m-d h:mm 或 dd-mm-yy “D4“ d-mmm-yy 或 dd-mmm-yy “D1“ d-mmm 或 dd-mmm “D2“ mmm-yy “D3“ dd-mm “D5“ h:mm AM/PM “D7“ h:mm:ss AM/PM “D6“ h:mm “D9“ h:mm:ss “D8“ 如果 CELL 公式中的 info_type 参数为“format”，而且以后又用自定义格式设置了单元格， 则必须重新计算工作表以更新 CELL 公式。