Excel数组公式从入门到精通.docVIP

Excel数组公式从入门到精通一、课程回忆 　　什么是数组公式呢？顾名思义就是公式中包含数组的了，详细含义请参看前文。但这里重点提醒的一点就是，如果要使用数组公式，在编辑栏输入完公式以后一定要按下“Ctrl+Shift+Enter”组合键，使编辑栏的公式处在“{}”之中。 　　二、数组公式继续深入 　　印象中是好几年前了，当时看过的一篇扫盲贴中，作者举的例子真是太实用了。具体细节记不太清楚了，大致意思就是使用函数计算1到100的和。这里同样以此为例。 　　1.求1到100的和 　　在往下看之前，大家想一下，如果让你来处理该如何来处理呢？只用一个函数解决1到100的和，当然也可以是1000、10000甚至更多。讨论具体的数值没有太大意义，此处只是希望通过此例让大家更进一步的了解数组公式的用法。 　　解答：{=SUM(ROW(1:100))} 　　问题分析：求1到100的和，答案是5050（小学生都知道^-^），但Excel必须是你告诉了它正确的方法，它才能知道。计算从1到100的和，实际上就是计算1+2+3+4+……+98+99+100，好了，答案出来了，在编辑栏中输入 “＝sum(1+2+3+4+……+98+99+100)”。相信聪明的一定对此答案不满意，虽然能得到正确的结果，但很明显是“错误”的方法。 　　要得到1到100的正确数列，最简单的方法就是使用Row()或是Column()函数，由于个人习惯，我比较习惯于Row()，所以这里以Row()函数为例。 　　熟悉Row()函数：在A1单元格中输入“＝Row()”，使用填充柄填充至A5，看到什么结果？是不是每一个单元格中值就是其对应的行数。 惊喜：Row()表示单前行，如果使用Row(1:100)就表示一个数组，其中包含的便是第一到第一百行的行号，即1、2、3、……、98、99、100这些数值，现在我们就把这个数组应用到公式中。 　　在工作表的任意一个单元格中输入“=sum(Row(1:100))”，然后按Ctrl+Shift+Enter组合键，你会惊喜的发现，我们要的结果出现了。 　　2.在Excel2003中享受“SUMIFS” 　　SUMIF函数应该很多人都用过，非常好用。但如果遇到多条件判断的怎么办呢？从Office 2007开始，引入了SUMIFS函数，可以解决这种多条件求和问题。但如果仍然使用Office 2003怎么办呢？其实使用SUM、IF再结合数组公式即可实现SUMIFS的效果。 　　如下图所示，某教师有一张任教的几个班级的学生成绩表。 　　任务：统计出“一班”、“二班”共计多少人？ 　　此题要如何解决？SUMIF用两次？或是COUNT用两次？这里还是演示数组公式的用法，所以先用SUM和IF组合的形式。 　　在任一单元格中输入“=SUM(IF((A2:A12=一班)+(A2:A12=二班),1,0))”公式按下回车键，是不是发现结果是 “#VALUE！”，再次进入编辑栏然后按下“Ctrl+Shift+Enter”快捷键，是不是发现正确的结果出来了？ 这里再次解释一下这个公式“{=SUM(IF((A2:A12=一班)+(A2:A12=二班),1,0))}”，外侧SUM没什么好用的了，就是求（）内各数的和。中间的“IF((A2:A12=一班)+(A2:A12=二班),1,0))”的运算过程是这样的，判断A2:A12区域内单元格的值是否是“一班”，如果是则结果为1，则此公式计算的结果依次是“1、0、0、1、0、0、1、0、0、1、0”，因为第一个条件为真，第二个条件肯定就不为真了，因为一个单元格不可能同时等于“一班”和“二班”，所以第一个数组就是“1、0、0、1、0、0、1、0、0、1、0”。这时再判断 A2:A12区域内单元格的值是否是“二班”，如果是结果则为1，否则为0，所以这个数组条件计算的结果就是“0、1、0、0、0、1、0、1、0、0、 0”，中间的加号就是将这两个数组相加，也就是说最终的数组为“1、1、0、1、0、1、1、1、0、1、0”，然后使用SUM求和，结果就为7了。 　　　　查看原图（大图） 　　从上面的图中标注可以看出，所以的公式我全部使用了数组（A2:A12这就是一个数组），并且上图上的没有使用数组公式的公式中的数组全部可以使用单个单元格替代，之所以全部列出，还是希望大家更好的理解一下数组。 在Excel中，数组如果不放在数组公式中使用，通常数组在特定单元格中只代表与其特定单元格所对应的一个值（数组中的一个元素），放在数组公式中使用时，通常整个数组元素都会参与运算。三、数组公式精通 　　这里是一个实际工作中的例子，只是我稍微变化了一下，还是SUM应用的例子。 　　需求：如下图所示，现在要统计员工张三在1号加工所有机器的“实绩”，也就是说在