菜鸟级数组公式入门教程.xls

1、 了了解解数数组组 、数组就是单元的集合或是一组处理的值集合。可以写一个数组公式，即输入一 个单个的公式，它执行多个输入的操作并产生多个结果每个结果显示在一个单 元中。数数组组公公式式可可以以看看成成是是有有多多重重数数值值的的公公式式。与单值公式的不同之处在于它可以 产生一个以上的结果。一个数组公式可以占用一个或多个单元。数组的元素可多达 6500个。 、数组是怎么进行运算的，现通过下面的实例进行理解。 在“C”列中的数据为销售量，在“D”列中的数据是销售单价， 要要求求计计算算出出产产品品的的总总销销售售金金额额， 例（1） 一一般般公公式式：数数组组公公式式： 品名销售数量销售单价销售金

2、额品名销售数量销售单价销售金额 DVD/0A3088026400DVD/0A30880 DVD/0A2698025480DVD/0A26980 MP4-FA60110066000MP4-FA601100 MP4-FA80120096000MP4-FA801200 VCD-5A5035017500VCD-5A50350 DVD/0A7098068600DVD/0A70980 MP4-FA109209200MP4-FA10920 先先求求E19:E25E19:E25列列的的和和在在求求总总和和309180一一次次求求出出总总和和309180 一一般般方方法法的的公公式式: :数数组组方方法法的的公

3、公式式: : =C21*D21=SUM(H21:H27*I21:I21:I24:I25:I26:I27) =SUM(E21:E27) 数数组组的的运运算算原原理理: : 14 =SUM(2,3*4,2) =SUM(8,6)=14 11 =SUM(2,3+4,2) =SUM(2+4,3+2,)=SUM(6,5)=11 在数组运算中，*就是乘法运算，+就是加法运算（我们也可以 从“公式求值”中看出运算过程: 第一个公式Sum(2*4,3*2) Sum(8,6)14, 第二个公式Sum(2+4,3+2,)Sum(6,5)11） 由于对数组公式很默生,一直都在思考有没有一份“菜鸟级“的入门资料,经搜集

4、发现大部分 介绍数组的资料,均要对EXCEL有一定造旨才能看懂,鉴于此,就想制作一份这方面的教材与 大家共同学习，希望对你的EXCEL水平提高有帮助。同时希望大家交流有错漏的请给予指正 （如果此资料对你有帮助及有你的支持，我会得到更大的鼓舞。York888;2005-11-11） 数组常数 我们也可以在数组中使用常数值。这些值可以放在数组公式中使用区域引用的 地方。要在数据公式中使用数组常数，直接将该值输入到公式中并将它们放在括号里。 例（2）：使用数组常数进行计算。 品名销售数量销售单价销售金额品名销售数量 销售单价 销售金额 商品11001000商品11001000 商品22004000商

5、品22004000 商品33009000商品33009000 一般公式：数组常数公式： =C11*10=C9:C12*10;20;30 =C12*20 =C13*30 常数数组可以是一维的也可以是二维的。一维数组可以是垂直的也可以是水平 一维水平数组中的元素用逗号（ ，）分开。一维垂直数组中的元素用分号（；）分开 一维水平数组：一维垂直数组（6*1的数组） 10,20,30,40,50100;200;300;400;500;600 常数二维数组。对于二维数组，用逗号将一行内的元素分开，用分号将各行分开 “4 4”的数组（由4行4列组成）：“4 4”的数组（由4行4列组成）： 100,200,3

6、00,400;110;130,230,330,440100,200,300,400;110;130,230,330,440 注意：不可以在数组公式中使用列出常数的方法列出单元引用、名称或公式。 例如：2*3,3*3,4*3因为列出了多个公式，是不可用的。A1,B1,C1因为列出多 个引用，也是不可用的。不过可以使用一个区域，例如A1:C1。 对于数组常量的内容，可由下列规则构成： 数组常量可以是数字、文字、逻辑值或错误值。 数组常量中的数字，也可以使用整数、小数或科学记数格式。 文字必须以双引号括住。 同一个数组常量中可以含有不同类型的值。 数组常量中的值必须是常量，不可以是公式。 数组常量不

7、能含有货币符号、括号或百分比符号。 所输入的数组常量不得含有不同长度的行或列。 数组扩充 在公式或函数中使用数组常量时，其它运算对象或参数应该和第一个数组具有 相同的维数。必要时，Excel 会将运算对象扩展，以符合操作需要的维 数。每一个运算对象的行数必须和含有最多行的运算对象的行数一样，而列数也必 须和含有最多列数对象的列数一样。 例如: = SUM(1,2,3+4,5,6）内的第一个数组为13,得到的结果为1+4、 2+5和3+6的和,也就是21。如果将公式写成 = SUM(1,2,3+4）,则第二个数据并 不是数组，而是一个数值，为了要和第一个数组相加，Excel 会自动将数值扩充成

8、1 3 的数组。使用=SUM(1,2,3+4,4,4)做计算，得到的结果为1+4、2+4和3+4 例（1） 21=SUM(1,2,3+4,5,6)18=SUM(1,2,3+4,5,6) =SUM(1,2,3+4,5,6）内的第一个数 =SUM(1,2,3+4,4,4)做计算，得 组为13,得到的结果为1+4、2+5和3+6到的结果为1+4、2+4和3+4的和, 即18。 的和,也就是21 将数组公式输入单元格区域中时，所使用的维数应和这个公式计算所得数组维 数相同。这样，Excel 才能把计算所得的数组中的每一个数值放入数组 区域的一个单元格内。 如果数组公式计算所得的数组比选定的数组区域还小

9、，则Excel会 将这个数组扩展，以便将它填入整个数组区域内。例如：=1,2;3,4*2扩充后的公 式就会变为=1,2;3,4*2,2;2,2，则相应的计算结果为“2,4,6,8”。再如：输 入公式=1,2;3,4*2,3扩充后的公式就会变为=1,2;3,4*2,3;2,3 ，则相应 的计算结果为“2,6,6,12”。 例（2）扩充注意：输入公式后要选定行、列范围，在按：CTRL+SHIFT+ENTER 未未扩扩充充扩扩充充后后未未扩扩充充扩扩充充后后 24242626 6868612612 =1,2;3,4*2=1,2;3,4*2,2;2,2 =1,2;3,4*2,3 =1,2;3,4*2,

10、3;2,3 结果:2、4、6、8结果:2、6、6、12 数组分类计算 、有如图所示的工作表，需分别计算各商品的销售额，可利用数组公式来实现。 单元格F182中的公式为：=SUM(IF(A2:A11=商品1，B2:B11C2:C11,0)。这 个数组公式创建了一个条件求和，若在A2:A11中出现值“商品1”，则数组公式将 B2:B11和C2:C11中与其相对应的值相乘并累加，若是其他值则加零。同时，虽然 数组B2:B11和C2:C11均在工作表中，但其相乘的数组B2:B11C2:C11不在工作表 中，因此必须使用数组公式。 一般公式计算：数组公式计算： 名稱銷售價數量合計名稱銷售價數量合計 商品

11、110.3320206.6商品110.3320 商品110.6610106.6商品110.6610 商品110.3230309.6商品110.3230 商品210.5840 423.2商品210.5840 商品110.6950534.5商品110.6950 商品110.6860640.8商品110.6860 商品110.9870768.6商品110.9870 商品210.1180808.8商品210.1180 商品110.5590949.5商品110.5590 合計3516.2合計3516.2 一般公式：数组公式： =C13*D13=SUM(IF(H13:H21=“商品1“,I13:I21*J

12、13:J21,0) =SUM(E13:E22) 求各班级的优秀者 96分以上为优秀,用数组公式示例如下: 姓名考号坐号语文数学英语政治班级 A2010501020198.7753070201 B2020601020298.7964060202 C2030701020399.5699050203 D2040801020490.5807090204 E2050901020598958080205 f2010501020694.596.57080201 g2020601020793.75986596202 h2030701020898886097203 i2020901020998.37055982

13、02 语文/優秀 班级人数% 201二(1)班1 202二(2)班2 203二(3)班2 204二(4)班 205二(5)班1 206二(6)班 207二(7)班 208二(8)班 209二(9)班 =SUM($E$35:$E$43=96)*($N$35:$N$43=TEXT($B50,0) 计算B产品8月份销量 类型 日期 单价 销售数量 A2005/10/15100010 B2005/8/20100015 B2005/8/1400010 C2005/7/10400011 B2005/8/15900013 B2005/8/20900015 A2005/9/30100014 A2005/10/

14、10100020 B2005/10/15400025 B产品8月销售量:53 =SUM($B$63:$B$71=“B“)*(MONTH($C$63:$C$71)=8)*($E$63:$E$71) A产品10月销量:30 =SUM($B$63:$B$71=“A“)*(MONTH($C$63:$C$71)=10)*($E$63:$E$71) 计算8月份前不包括 B产品销量和8月后89 不包括C产品销量： 求下面数据中最大数和最小数 数据最小值最大值 16589908319 38655524738 567345657 =SUM(IF(MONTH($C$63:$C$71)=8) ($B$63:$B$7

15、1=“C“),$E$63:$E$71) 数组四舍五入求和 、将E10:E18区域中的所有数值舍入到1位小数位，然后对舍入的数值求和 一般公式：数组公式： 名稱銷售價數量合計名稱銷售價數量合計 商品1206.61商品1206.61 商品6106.62商品6106.62 商品1309.63商品1309.63 商品3310.64商品3310.64 商品1362.15商品1362.15 商品1413.66商品1413.66 商品1465.17商品1465.17 商品2516.68商品2516.68 商品1568.19商品1568.19 合計3259.40合計3259.40 一般公式：数组公式： =SU

16、M(ROUND(J7:J15,1) =ROUND(F194,1)+ROUND(F195,1)+ROUND (F196,1)+ROUND(F197,1)+ROUND(F198,1) +ROUND(F199,1)+ROUND(F200,1)+ROUND (F201,1)+ROUND(F202,1)&“普通公式“ 数组四舍五入求和 用数组去丢最大值及最小值求和 求取区域（B4:K5）去掉一个最小值去掉一个最大值之和 126451012253032 210111534891820 去丢一个最小值及最大值求和：161161 =SUM(B4:K5)-SUM(SMALL(B4:K5,1,1)-SUM(LAR

17、GE(B4:K5,1,1) 数组在定义名称中应用 、求取范围下列数值中小于0或大于5的数值之和:先定义名称为b，在计算。 121451012253032 210111534891820 小于0大于5的数值之和：200 =SUM(IF(b5),b) 数组在定义名称中应用 数组计算文本个数 区域(B7:K8)中文本单元格的个数 126451012253032 2资源11153人力891820 文本个数:4=SUM(IF(ISTEXT(B7:K8),1,0) 数组计算文本个数 数组计算不重复数 求一组数据中(I261:I270)不重复数据的个数 1264510125306 21111532891820 不重复个数:15=sum(1/countif(B8:K9,B8:K9)