1、数组公式的初步认识整理 by Jimmy (foodorwater)1 / 23目录1. 利用数组公式实现单条件求和 .22. 将二维数组按列转换为一维数组 .33. 使用数组公式实现部门评价等级转换 .44. 使用数组公式按条件筛选数据 .55. 利用数组公式按比赛成绩进行降序排列 .66. 使用 excel2010 数组公式计算一个或多个结果 .77. 实例讲解使用数组公式显示的日历 .88. 用数组公式统计区域中的错误值 .99. 利用数组公式计算条件计数 .1110. 在 excel2007 中使用数组进行条件求和计算 .1211. excel2007 中数组计算的基础 .1312.
2、在 excel2007 中编辑或删除数组公式 .1413. excel2007 单元格区域与输入数组的范围不符 .1514. 在 excel2007 中使用数组进行计算 .1515. 使用数组公式对数据排序 .1616. 用数组函数返回一列中最后一个数值 .1617. excel 范围中相隔 n 个数的数值求和 .1718. 确定范围中最接近的数值 .1819. 对单元格中整数数字进行求和 .1820. 确定一个范围是否包含有效数值 .1921. 返回范围中的最长文本的公式 .1922. 查找范围中某个值第 n 次出现时的行 .19数组公式的初步认识整理 by Jimmy (foodorwat
3、er)2 / 2323. excel2003 中多单元格数组公式介绍 .2024. excel 数组公式不利的一面 .2125. 返回范围中惟一元素的列表 .2126. 值范围的动态分类 .221. 利用数组公式实现单条件求和如图展示了一份某商场中商品进货明细表，下面利用统计函数与数组公式两种方法进行数据统计。统计进货量大于 5 台的型号个数G3 单元格利用 COUNTIF 函数统计公式如下：=COUNTIF(D:D,“5“)G4 单元格得用 SUM 函数数姐公式如下：=SUM($D$2:$D$135)*1)统计进货最大于 5 且小子 10 台的总进货量G9 单元格利用 SUMIF 函数统计公
4、式如下：数组公式的初步认识整理 by Jimmy (foodorwater)3 / 23=SUM(SUMIF(D:D,“5“,“=10“)*1,-1)G10 单元格利用 SUMPRODUCT 函数的统计公式如下：=SUMPRODUCT($D$2:$D$135)*($D$2:$D$13SUMPRODUCT($B$2:$B$10=$H$2)*($C$2:$C$10=$H$3),“,ROW()-5)H6 单元格中的查询数组公式如下：=IF($G6=“,“,INDEX(D:D,SMALL(IF($B$2:$B$10=$H$2)*($C$2:$C$10=$H$3),ROW($B$2:$B$10),$G6
5、)公式主要利用 IF 函数将数据表记录分别与日期、客户名进行判断，将满足条件的记录的行号提取出来，再利用 INDEX 函数返回结果，最后将 H6 公式复制到 I10 单元格即得结果。数组公式的初步认识整理 by Jimmy (foodorwater)6 / 235. 利用数组公式按比赛成绩进行降序排列如图展示了一份某公司秋季运动会的比赛成绩明细表，下面的公式将实现根据比赛成绩进行部门排名。 RANK 函数化零为整排序法H3 单元格部门列表数组公式如下：=INDEX($A:$A,RIGHT(SMALL(RANK($E$3:$E$8,$E$3:$E$8)*100000+ROW($E$3:$E$8)
6、,ROW()-ROW($2:$2),5)I3 单元格积分公式如下：=VLOOKUP($H3,$A:$E,5,)其中，部门列表公式主要利用 RANK 函数与 ROW 函数重新生成内存数姐，再利用 SMALL 函数从小到大进行提取，最后利用 INDEX 函数生成部门名称。利用 RANK 函数将数值化零为整地转换为数值排名，在带有小数的数值排名应用中非常有用，可以免受 Excel 有效位数最多为 15 位的限制。 SMALL 函数和 LARGE 函数排名法L3 单元格积分提取公式如下：数组公式的初步认识整理 by Jimmy (foodorwater)7 / 23=INDEX($A:$A,SMALL
7、(IF($E$3:$E$8=$L3,ROW($E$3:$E$8),COUNTIF($L$2:$L3,$L3)K3 单元格提取部门名称的数组公式如下：=LARGE($E:$E,ROW()-ROW($2:$2)公式中最关键的是利用了 COUNTIF 函数混合引用的动态统计的技巧，便于提取出相同积分的部门名称。6. 使用 excel2010 数组公式计算一个或多个结果在 excel2010 中，数组公式可以执行多项计算并返回一个或多个结果。数组公式对两组或多组名为数组参数的值执行运算。每个数组参数都必须有相同数量的行和列。除了用 Ctrl+Shift+Enter 输入公式外，创建数组公式的方法与创建
8、其他公式的方法相同。某些内置函数是数组公式，并且必须作为数组输入才能获得正确的结果。计算单个结果此类数组公式通过用一个数组公式代替多个公式的方式来简化工作表模式。例如，下例计算一组股票价格和股份的总价值，而不是使用一行单元格来计算并显示出每支股票的总价值。如图当将公式=SUM(B2:D2*B3:D3)作为数组公式输入时，该公式将每支股票的 “股份” 和“价格”相乘，然后再将这些计算结果相加。计算多个结果一些工作表函数返回多组数值，或需要将一组值作为一个参数。如果要使数组公式能计算出多个结果，则必须将数组输入到与数组参数具有相同的列数和行数的单元格区域中。例如，如果给出了对应于三个月份（列 A
9、中）的三个销售额（列 B 中），则 TREND 函数会返回销售额的直线拟合值。若要显示公式的所有结果，则应在列 C 的三个单元格 (C1:C3) 中输入该公式。当将公式=TREND(B1:B3,A1:A3) 作为数组公式输入时，它会根据三个月的三个销售量得到三个不同的结果（22196 、17079 和 11962）。数组公式的初步认识整理 by Jimmy (foodorwater)8 / 237. 实例讲解使用数组公式显示的日历在本例中，将使用数组公式实现一个动态的日历，根据本机的时间显示当前一个月的日历。下面先说明这个日历的实现步骤：1. 在 A1 单元格内输入公式 “=TODAY()”，
10、在 A2 单元格内输入“=A3”，将 A2 单元格复制到 B2:G3 单元格区域。2. 设置 A2:G2 单元格的格式，使其分类为日期的星期，选中 A3:G8 单元格区域。输入以下公式：DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2)+1,2,3,4,5,6,7+0;1;2;3;4;5*7)3. 按 Ctrl+Shift+Enter 组合键确认输入。现逐步分析输入的这个数组公式：因为一个月最多会跨越 6 个星期，所以需要一个 6 行 7 列(一星期 7 天)的单元格区域来显示这个日历。这个公式的后部，即“1,2,3
11、,4,5,6,7+0;1;2;3;4;5*7”，构成了这个日历的框架。如果在一个 6 行 7 列的单元格区域内输入这个输入公式，可以得到 6 行 7 列的二维数组1,2,3,4,5,6,7;8,9,10,11,12,13,14;15,16,17,18,19,20,21;22,23,24,25,26,27,28;29,30,31,32,33,34,35;36,37,38,39,40,41,42这个数姐的元素按照从第一行由左到右，再由下一行由左到右顺序，逐个加一递增。这个数组公式以此来实现每日的递增。这个公式的前部，即“DATE(YEAR(A1),MONTH(A1),1)”，通过调用 A1 单元格
12、，来得到本月一日的日期。本月一日的日期和 6 行 7 列的二维数组相加，又可得到一个 6 行 7 列的二维数组。这个二维数组实现了日期的逐一显示。但是这个按照曰期逐一显示的二维数组是从 2 号开始的，而且 2 号显示在了星期一的位置。“WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2)”部分用来对日期的位置进行调整。以 2007 年 1 月为例(2007 年 1月 1 日为星期一) ，可以得到值“-1” 。这样就对按日期逐一显示的 6 行 7 列二维数组做出了调整，2007 年 1 月 1 日出现在二维数组公式的初步认识整理 by Jimmy (foodorwater)
13、9 / 23数组第一行的第一个位置，而之后的日期逐一显示。通过 IF 函数，可以使日历中的非当月日期不显示。4. 选中 A3:G8 单元格区域，把公式修改为：=IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)MONTH(DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2)+1,2,3,4,5,6,7+0;1;2;3;4;5*7),“,DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(C4YEAR(A1),MONTH(A1),1),2)+1,2,3,4,5,6,7+0;
14、1;2;3;4;5*7)最后按 Ctrl+Shift+Enter 组合键确认输入。这样就以数组公式的方式显示出日历。通过修改各单元格的格式，可以使显示更加美观。8. 用数组公式统计区域中的错误值在工作表中， 经常会出现公式计算得出错误值的情况。下面的例子演示了对错误值的统计。实例：如图所示，单元格区域内有多个错误值，现希望统计出其中包含错误值#NULL!的单元格个数。具体步骤如下：选中 E14 单元格，输入公式“=COUNT(IF(ERROR.TYPE(A1:E9)=1,1)” ，按 Ctrl+Shift+Enter 组合键确认输入。这样数组公式的初步认识整理 by Jimmy (foodor
15、water)10 / 23即在 E14 单元格内统计出了非空单元格的个数。现分析这个数组公式：ERROR.TYPE 函数判断 A1:F9 单元格区域内各个单元格内的错误值类型。如果单元格内含有错误值，ERROR.TYPE 函数返回相应数值。对于错误值#NULL!，返回“1”，而不含错误值的单元格，ERROR.TYPE 函数返回错误值“#N/A”。IF 函数根据 ERROR.TYPE 函数的返回值是否等于 1 进行判断，如果是则返回“1”。由上一步返回的各值构成一个数组并存储在内存中。COUNT 函数统计出这个数组中数字元素( 即“1”) 的个数。即是 A1:E9 单元格区域内包含错误值#NUL
16、L!的单元格个数。根据下表中的公式，可以对含有其他类型错误值的单元格进行统计。#NULL!：=COUNT(IF(ERROR.TYPE(A1:E9)=1,1)#DIV/O!： =COUNT(IF(ERROR.TYPE(A1:E9)=2,1)#VALUE!：=COUNT(IF(ERROR.TYPE(A1:E9)=3,1)#REF!：=COUNT(IF(ERROR.TYPE(A1:E9)=4,1)#NAME?：=COUNT(IF(ERROR.TYPE(A1:E9)=5,1)#NUM!：=COUNT(IF(ERROR.TYPE(A1:E9)=6,1)#N/A：=COUNT(IF(ERROR.TYPE(A1:E9)=7,1)
