数组函数讲座.docVIP

数组公式讲座（一）：数组公式入门 数组公式是我自学的，下面写的东西可能和外面的相关参考书有出入，但结果和思路是正确的。如有错误，请大家指正。 1、 数组公式的实现方法：在论坛中常可以看到许多在头尾带有“{}”的公式，有些朋友把这些公式直接复制粘贴到单元格中，但没有出现正确的结果， 这是为什么呢？其实这些都是数组公式，数组公式的输入方法是将公式输入后，不要直接按回车键（Enter），而是要同时按Ctrl+Shift+Enter，这时电脑自动会为你添加“{}”的。在论坛上，为了告诉大家这是数组公式，故在公式的头尾都加上了“{}”。如果不小心按回车了，可以用鼠标点一下编辑栏中的公式，再按Ctrl+Shift+Enter。 2、数组公式的原理（自编）：数组公式，说白了就是同时对一组或几组数同时处理，然后得到需要的答案。运用数组公式的最重要的原理是数于数之间一一对应 下面举一列说明一下（例1）： A1:A10有下列一排数： 32 14 34 25 36 21 33 12 12 16 第二问：求大于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函数进行求和,亦可用COUNT函数进行计数。 第三问：求大于20的数的个数减去小于等于20的数的个数的值。答案为：1，公式为{=SUM(IF(A1:A1120,1,-1))}，EXCEL处理的原理：首先由IF函数进行判断，如果大于20，则为1，否则为-1。经过IF函数的处理，原来的A1:A10处理后变成：1-111111-1-1-1然后用SUM函数进行求和。 数组公式入门讲座之二（上） …;998;999;1000}，然后用SUM()进行求和，就得到答案了。额外提醒大家的是，通过SUM()这个函数，出来的结果就是一个值，不会是数组的。（COLUMN()函数也可以，这里就不讲了）。 答案：{=SUM(ROW(1:1000))}（CHENJUN兄另一种使用INDIRECT()的做法，是为了避免行范围有行删除、插入，这是在实际使用中出现问题地解决办法，这次暂不讨论{=SUM(ROW(INDIRECT(1:1000)))}）。 第二题：求在1到1000中，是7的倍数的数的和，包括7在内。7+14+21+28+...=？ 这题，是在上题的基础上，演变而来的，也就是一个数组的处理的问题。既然得到了1到1000这样子的数组，那么如何得到7的倍数的数的数组？方法很多，下面一一讲解：用的最多的方法是： {=SUM(ROW(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，那么对应的这个数不能被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)*71000,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(WEE