数组公式入门讲座之二（上）

dongmu · 发表于 2003-11-1 07:13

10月23日给大家出了三道题，其实就题目的难度来说，如果既会用函数，又懂些数组公式使用的话，那么我想这些题目应该都能解决的。下面通过对这几道题目的分析，来讲讲数组公式的运用。

QUOTE:

数组公式讲座（一）：数组公式入门

http://club.excelhome.net/viewthread.php?tid=587&replyID=&skin=0

[此贴子已经被山菊花于2006-7-16 20:51:06编辑过]

dongmu · 发表于 2003-11-1 07:14

第一题：求1到1000的和。1+2+3+4+...+997+998+999+1000=？这题，思路非常简单，就是如何生成一个1到1000这样子的数组，然后全部加起来。所以，如果要用数组公式的话，你就得先问自己，到底需要一个怎样的数组才能达到目的。那么，接下来，就要考虑，通过什么函数，才能达到这个目的。就这题来说，就会使用ROW()函数，一个取得引用行号的函数，=ROW(1:1000)，通过这个函数就能生成这样一个数组{1;2;3;…;998;999;1000}，然后用SUM()进行求和，就得到答案了。额外提醒大家的是，通过SUM()这个函数，出来的结果就是一个值，不会是数组的。（COLUMN()函数也可以，这里就不讲了）。答案：{=SUM(ROW(1:1000))}（CHENJUN兄另一种使用INDIRECT()的做法，是为了避免行范围有行删除、插入，这是在实际使用中出现问题地解决办法，这次暂不讨论{=SUM(ROW(INDIRECT("1:1000")))}）

dongmu · 发表于 2003-11-1 07:15

第二题：求在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)*7<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}相乘，得出结果。这里，需指明的是，我在用WEEKDAY()的时候，忽略了一个参数，那是默认值，星期一是1，以此类推；而TRUNC()也忽略了一个参数，默认值为0，即取整。

dongmu · 发表于 2003-11-1 07:17

所以，思路从某种程度上讲，决定公式的使用方法，有些人排出来的公式很复杂，有些人很简单，就是这个道理。大家在平时学用公式的时候，不仅要学习函数的使用方法，更主要的是要学习思路，不要只求结果，不求过程。最后一题，我想详细分析一下，加上最近有点忙，准备放在下周末，请大家谅解。放上word版本：

ZAXhK5nA.rar (5.06 KB, 下载次数: 3096)

cxffxc · 发表于 2003-11-1 09:25

大受启发谢谢！

hxhgxy · 发表于 2003-11-1 20:34

深入浅出！

zhongl5555 · 发表于 2003-11-2 19:05

好感动/辛苦了

楚楚东人 · 发表于 2003-11-3 12:11

入木三分，真是经典

楚楚东人 · 发表于 2003-11-5 12:43

想问一个捣乱的问题，求1到X的和。1+2+3+4+...+997+998+999+...+(X-2)+(X-1)+X=？当X>65535时，怎么办？

sunnet · 发表于 2003-11-5 13:13

想让 X 等于多少？够了吗： {=SUM(ROW(1:65535),(ROW(1:65535)+65535))} {=SUM(ROW(1:65535),(ROW(1:65535)+65535),(ROW(1:65535)+65535*2))} …… ……

[此贴子已经被作者于2003-11-7 15:36:50编辑过]

		自动登录	找回密码
密码			免费注册

[分享] 数组公式入门讲座之二（上）

评分