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

Excel 数组公式从入门到精通之入门篇 Excel 中函数、公式大家都非常熟了，但“数组公式”你是否听过、用过？什么是数组公式呢？根据微软官方给出的解释，数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。一、什么是数组公式？直接看微软的解释，也许并不是很容易理解，根据个人的理解，让其更直白一点，可以这样简单理解，引用了数组（可以是一个或多个数值，或是一组或多组数值），并在编辑栏可以看到以“” 括起来的公式就是数组公式。而数组公式的作用就是对一组（单个数据可以看成是一组）、多组数据进行处理，然后得到想要的结果。二、如何输入数组公式既然数组公式是以“”括起来的，那是不是在编辑栏在公式的两端分别输入“”就可以了呢？答案是否定的，在 Excel 中要输入数组公式，必须以特定的方法来输入，算是告诉Excel，我们这里输入的是数组公式。在某个单元格输入数组公式的方法如下：1.在编辑栏输入完整的公式，并使编辑栏仍处在编辑状态；2.按下 Ctrl+Shift+Enter 快捷键经过以上两步操作以后，编辑栏会自动脱离编辑状态，并且选中单元格后，在编辑栏可以看到公式的两端有“”符号标记，而双击进入公式的编辑状态时，你会发现“”符号是不存在的。三、数组公式有什么用？这里做了一个类似微软官网上的例子，这里详细说明一下用法及好处。以上面图片中的内容为例，假设我一共买了三支股票，其股份及买入价格分别如图中所示，现在我要计算我的总股本。正常情况下我应该如何做？在 B4 输入“=B2*B3”，然后填充至 D4 单元格，这样 B4、C4、D4 就分别是每一支股票的股本了，然后在 B5 单元格再输入“=SUM(B4:D4)”，这样总股本就出来了。上面的计算过程可以说一点问题没有，也绝对正确。但是试想一下，如果类似的数据有很多，如果不是计算我自己买的几支股票而是其他类似情况的数据处理的时候，采用上面的方法时，其工作量可想而知了。我们再回到第一幅图中的公式“=SUM(B2:D2*B3*D3”，此公式便是一个典型的数组公式的应用，此公式的作用就是计算 B2*B3、C2*C3 以及 D2*D3 的和。而 B2:D2*B3*D3 便是一个数组，其中包含三个元素，各元素的值就分别是各项的乘积。为了更好的验证数组说法，分别在 B4、C4、D4 单元格中分别输入=B2*B3、=C2*C3、=D2*D3，B7 、C7 、D7 单元格中全部输入=B2:D2*B3:D3，结果如下图所示。从上面的图中可以看到，第 4 行和第 7 行的计算结果是一样的，这就是数组的效果，数组会根据当前单元格所在位置自动取数组中对应序列的数值，如果将“=B2:D2*B3:D3”算式输入到其他的列中（非 B、C 、D 列），这时你会看到“#VALUE!”的错误数值结果，因为在其他列时，Excel 无法判断该取数组中的哪一个数值。如果将第 7 行中的算式外面加上 SUM，你会发现结果仍然一样，因为默认情况下，数组算式只取对应序列的值，再加上 SUM 也只是对应的值，如果使用数组公式，便是告诉Excel 计算数组中所有数值的和，也就是单元格 B5 中的结果。三、数组公式应用进阶数组公式最典型的应用应该是使用 SUM 替代 SUMIF，虽然 SUMIF 很好用，但在 Office 2007 之前，也就是 SUMIFS 函数出现之前，如果想利用 SUMIF 进行一次多重条件判断的求和计算是很难实现的。为了更便于理解，这里再用上面的例子进行一个比较简单的运算，上面只是列出了三支股票，如果我同时购买了多支股票，现在想知道这些股票当中，股价小于 5 元的股票有几支（这一功能可以使用 Countif 函数来实现，这里为了让数组公式更便于理解，所以使用 SUM、IF 相结合的数组公式来实现。在 B10 单元格输入公式“=SUM(IF(B3:D35,1)”，按钮按下 Ctrl+Shift+Enter组合键，使公式变成数组公式“=SUM(IF(B3:D35,1)”。从上图中可以看到，计算结果为 2，计算正确。如果对此怀疑，可以扩大数据区域，从而更容易理解。下面说一下公式的整个运算过程。1.IF(B3:D35,1)，计算 B3:D3 区域内数值小于 5 的个数，因为使用 IF 判断，数值小于 5 时，取值 1，所以该公式计算的结果是 1，false，1，然后 SUM 进行数组求和也就是 1+false+1，所以结果为 2。SUM 在求值时会自动忽略 False，我们也可以把 False直接当作 0 来处理。或者将公式改成 IF(B3:D35,1,0)，这样计算的结果就是 1，0，1 了。如果要统计股价低于 5 的股价和要怎么办呢？将上述公司修改成“=SUM(IF(B3:D35,B3:D5)”即可，因为数组公式是一一对应的，也就是说 IF 条件满足时就取对应的数值，所以此公式的计算结果为 4.04，false，4.43，最后的结果是 4.04+false+4.43=8.47。不知道看完了上面这些，大家对数组公式是否有所了解了，更进阶的应用，且听下回分解。上次写了篇 Excel 数组公式从入门到精通之入门 篇，不觉已十多天过去了，今天补上 “精通篇”。当然说“精通” 可能有点过了，但是希望大家通过这两篇博文能够真正认识“数组公式” ，并且在工作中使用数组公式帮我们解决实际问题。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 号加工所有机器的“实绩”，也就是说在右侧的数据中先过滤日期为 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，