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

1、Excel 数组公式从入门到精通之精通篇一、课程回忆什么是数组公式呢？顾名思义就是公式中包含数组的了，详细含义请参看前文。但这里重点提醒的一点就是，如果要使用数组公式， 在编辑栏输入完公式以后一定要按下“ Ctrl+Shift+Enter ”组合键，使编辑栏的公式处在“ ”之中。二、数组公式继续深入印象中是好几年前了，当时看过的一篇扫盲贴中，作者举的例子真是太实用了。 具体细节记不太清楚了， 大致意思就是使用函数计算1 到 100 的和。 这里同样以此为例。1. 求 1 到 100 的和在往下看之前，大家想一下，如果让你来处理该如何来处理呢？只用一个函数解决 1 到 100 的和， 当然也可以

2、是1000、 10000 甚至更多。 讨论具体的数值没有太大意义，此处只是希望通过此例让大家更进一步的了解数组公式的用法。解答： =SUM(ROW(1:100)问题分析：求1至ij 100的和，答案是5050 (小学生都知道 )，但Excel必须是你告诉了它正确的方法，它才能知道。计算从1 到 100 的和，实际上就是计算1+2+3+4+98+99+100,好了，答案出来了，在编辑栏中输入“=sum(1+2+3+4+98+99+100) ” 。 相信聪明的一定对此答案不满意， 虽然能得到正确的结果， 但很明显是 “错误”的方法。要得到1至ij 100的正确数列，最简单的方法就是使用Row()或

3、是Column()函数，由于个人习惯，我比较习惯于Row() ，所以这里以 Row() 函数为例。熟悉Row()函数：在A1单元格中输入“=Row()”，使用填充柄填充至 A5,文件二忤臂-Mier看到什么结果？是不是每一个单元格中值就是其对应的行数。再贴板匕Ijri=ROW 0)播入, 史画布同公S工AlA1234毛惊喜：Row()表示单前行，如果使用Row(1:100)就表示一个数组，其中包含的便是第一到第一百行的行号，即1、2、3、98、99、100这些数值，现在我们就把这个数组应用到公式中。在工作表的任意一个单元格中输入“=sum(Row(1:100) :然后按Ctrl+Shift+E

4、nter组合键，你会惊喜的发现，我们要的结果出现了。二- Microsoft Exce福入页面育局公式苗用 崛图二一鼻冒室与至第”电帮玷72.在 Excel2003 中享受 “ SUMIFSSUMIF函数应该很多人都用过，非常好用。但如果遇到多条件判断的怎么办呢？从Office 2007 开始，引入了 SUMIFS函数，可以解决这种多条件求和问题。但如果仍然使用Office 2003 怎么办呢？其实使用 SUM IF再结合数组公式即可实现 SUMIFSB勺效 果。如下图所示，某教师有一张任教的几个班级的学生成绩表。班一二-I二一三二一二.三 一.1一123456?891 01 11 2名三四五

5、六七A朝汗龙虎昭一 姓张李王就钱超王马张赵展绩贰优良优良良优优良优良世任务：统计出“一班”、“二班”共计多少人?此题要如何解决？ SUMIF用两次？或是 COUNT!两次？这里还是演示数组公式的用法，所以先用 SUM口 IF组合的形式。在任一单元格中输入 “ =SUM（IF（A2:A12= 一班）+（A2:A12=二班）,1,0）”公式按下回车键，是不是发现结果是“ #VALUE ”，再次进入编辑栏然后按下“Ctrl+Shift+Enter ”快捷键，是不是发现正确的结果出来了?- MicroExcel福入m，AT = SUM （IF （一2 :虱拄 一班）十（A2: A12一二班1；。） 1

6、广插 尹刑除 二A殳月后K任2 A0到丑N到期到s到到到 班二一I-二二二二三c二I1234567891 onl21 3B名三四五六七八朝汗龙虎昭 姓张李王起钱赵王马张赵展c成优良优良良优优良优良优J b丈面布舄公式 腿 亩间 视图这里再次解释一下这个公式“=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

7、、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”，然后使用SUMt和，结果就为7 了。TfEl - Microscrft Excel滕卷人反面在意公式 翠挺 而阅 视图A*一唯入T臼塔式*a：软年序和曜港2,字口.单元

8、格=SUM （IF （ +。比 A12=二班1. 0）H12345L-7S510 u12131415161718C绩一一-四五.班二一-E姓名小（A2A12 ;“一班1二if（A2：A12三二班”/,0）二 D2：D=sum（F2:F12IBOffl 100% W .： 二从上面的图中标注可以看出，所以的公式我全部使用了数组（A2:A12这就是一个数组），并且上图上的没有使用数组公式的公式中的数组全部可以使用单个单元格替 代，之所以全部列出，还是希望大家更好的理解一下数组。在Excel中，数组如果不放在数组公式中使用，通常数组在特定单元格中只代表与其特定单元格所对应的一个值（数组中的一个元素）

9、，放在数组公式中使用时， 通常整个数组元素都会参与运算。三、数组公式精通这里是一个实际工作中的例子，只是我稍微变化了一下，还是 SUW用的 例子。需求：如下图所示，现在要统计员工张三在 1号加工所有机器的“实绩”，也就是说在右侧的数据中先过滤日期为1,然后再过滤人员为张三的数据，最后统计实绩的结果。如果使用一个公式完成这一需求，你能想到吗？当然SUMIFS是除外的，因为SUMIFS是Ofice 2007 以后的产物。126 龙4ECDF1日期人员实绩日期机器号21张三12152637心1210111412233m11151567-17e181191201211Z223G =员一二四五上八七龙三

10、虎三朝汗三昭一二四五八一二三四 人张李王赵钱张张赵张王马张展张李王赵张张李 12 3 15 G 789012315 & 78901* IX IX 1- 1- 1 1X 1- 11实4343443423742363654 23224334332 5 644-5 4 74542 3 2 3 包了 3323834 3答案：在上图所示的 C2单元格中输入“=SUM(E2:E21=A2)*(G2:G21=B2)*(H2:H21)” 公式，然后按下 “Ctrl+Shift+Enter ”组合键，你会发现想要的结果已经出现了。数组公式就是这么简单，解决问题也是这么简单。这次用的公式可以看出， 比之前用的公式

11、还要简单， 连 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数组中对应的数字，即符