Excel数组公式从入门到精通之精通篇

        上次写了篇《 Excel数组公式从入门到精通之入门篇》，不觉已十多天过去了，今天补上“精通篇”。固然说“精通”可能有点过了，可是但愿你们经过这两篇博文可以真正认识“数组公式”，而且在工做中使用数组公式帮咱们解决实际问题。

Excel数组公式从入门到精通之精通篇

        1、课程回忆

        什么是数组公式呢？顾名思义就是公式中包含数组的了，详细含义请参看前文。但这里重点提醒的一点就是，若是要使用数组公式， 在编辑栏输入完公式之后必定要按下“Ctrl+Shift+Enter”组合键，使编辑栏的公式处在“{}”之中。

        2、数组公式继续深刻

        印象中是好几年前了，当时看过的一篇扫盲贴中，做者举的例子真是太实用了。具体细节记不太清楚了，大体意思就是使用函数计算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)就表示一个数组，其中包含的即是第一到第一百行的行号，即一、二、三、……、9八、9九、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，则此公式计算的结果依次是“一、0、0、一、0、0、一、0、0、一、0”，由于第一个条件为真，第二个条件确定就不为真了，由于一个单元格不可能同时等于“一班”和“二班”，因此第一个数组就是“一、0、0、一、0、0、一、0、0、一、0”。这时再判断A2:A12区域内单元格的值是不是“二班”，若是是结果则为1，不然为0，因此这个数组条件计算的结果就是“0、一、0、0、0、一、0、一、0、0、0”，中间的加号就是将这两个数组相加，也就是说最终的数组为“一、一、0、一、0、一、一、一、0、一、0”，而后使用SUM求和，结果就为7了。

        从上面的图中标注能够看出，因此的公式我所有使用了数组（A2:A12这就是一个数组），而且上图上的没有使用数组公式的公式中的数组所有可使用单个单元格替代，之因此所有列出，仍是但愿你们更好的理解一下数组。

        在Excel中，数组若是不放在数组公式中使用，一般数组在特定单元格中只表明与其特定单元格所对应的一个值（数组中的一个元素），放在数组公式中使用时，一般整个数组元素都会参与运算。

        3、 数组公式精通

        这里是一个实际工做中的例子，只是我稍微变化了一下，仍是SUM应用的例子。

        需求：以下图所示，如今要统计员工张三在1号加工全部机器的“实绩”，也就是说在右侧的数据中先过滤日期为1，而后再过滤人员为张三的数据，最后统计实绩的结果。若是使用一个公式完成这一需求，你能想到吗？固然SUMIFS是除外的，由于SUMIFS是Office 2007之后的产物。

        答案：在上图所示的C2单元格中输入“=SUM((E2:E21=A2)*(G2:G21=B2)*(H2:H21))”公式，而后按下“Ctrl+Shift+Enter”组合键，你会发现想要的结果已经出现了。数组公式就是这么简单，解决问题也是这么简单。

        此次用的公式能够看出，比以前用的公式还要简单，连IF都不要了，实际上这里的“＝”符号就是起到了一个相似IF的效果。

        这里再说明一下公式的执行过程，公式中E2:E21表示数组区域，这个相信已经不须要再说明了，放到数组公式中就是依次取数组中的各个数值，也就是依次取日期中的值。E2:E21=A2，实际上就是拿日期中的每个值依次与A2中的日期进行比对，若是相等则结果为True，即1，若是不相等则为False即为0。到了这里也许你有一点明白了，若是第一不相等，则后面的无需再继续下去了，由于公式里用的所有是“*”乘积符号，任何数乘0等于0。若是此项符合再继续判断G2:G21区域，也就是用姓名依次比对，若是和B2中的姓名相同，则为Ture，即1，若是为False，即0，继续下一个回合。若是此项也为Ture，很明显前面两项的结果为1*1＝1，再乘以H2:H21数组中对应的数字，即符合条件的“实绩”，以第一个符合条件的第一条记录为例，在数组公式运行的第一个回合为SUM(1*1*234)，结果固然为234了，而后再依次完成整个数组的运算，咱们最终的目的就达到了。

 

        数组公式很是有用，效率也高，但真正的理解、熟练掌握也不是一件很容易的事。但你们记住 数组中的数据是一一对应的，放到数组公式中使用时，数组中的数据会按顺序依次参与相应的运算。

        但愿你们可以慢慢的理解、贯通。