数组公式的初步应用--实例

1、数组公式的初步认识整理by Jimmy (foodorwater)目录1. 利用数组公式实现单条件求和22. 将二维数组按列转换为一维数组33. 使用数组公式实现部门评价等级转换44. 使用数组公式按条件筛选数据55. 利用数组公式按比赛成绩进行降序排列66. 使用excel2010数组公式计算一个或多个结果77. 实例讲解使用数组公式显示的日历88. 用数组公式统计区域中的错误值99. 利用数组公式计算条件计数1110. 在excel2007中使用数组进行条件求和计算1211. excel2007中数组计算的基础1312. 在excel2007中编辑或删除数组公式1413. excel200

2、7单元格区域与输入数组的范围不符1514. 在excel2007中使用数组进行计算1515. 使用数组公式对数据排序1616. 用数组函数返回一列中最后一个数值1617. excel范围中相隔n个数的数值求和1718. 确定范围中最接近的数值1819. 对单元格中整数数字进行求和1820. 确定一个范围是否包含有效数值1921. 返回范围中的最长文本的公式1922. 查找范围中某个值第n次出现时的行1923. excel2003中多单元格数组公式介绍2024. excel数组公式不利的一面2125. 返回范围中惟一元素的列表2126. 值范围的动态分类221. 利用数组公式实现单条件求和如图展

3、示了一份某商场中商品进货明细表，下面利用统计函数与数组公式两种方法进行数据统计。 统计进货量大于5台的型号个数 G3单元格利用COUNTIF函数统计公式如下： =COUNTIF(D:D,5) G4单元格得用SUM函数数姐公式如下： =SUM($D$2:$D$135)*1) 统计进货最大于5且小子10台的总进货量 G9单元格利用SUMIF函数统计公式如下： =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)*

4、($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)公式主要利用IF函数将数据表记录分别与日期、客户名进行判断，将满足条件的记录的行号提取出来，再利用INDEX函数返回结果，最后将H6公式复制到I10单元格即得结果。5. 利用数组公式按比赛成绩进行降序排列如图展示了一份某公司秋季运动会的比赛成绩明细表，下面的公式将实现根据比赛成绩进行部门排名。RANK函数化零为整排序法H3单元格部门列表数组公式如下

5、：=INDEX($A:$A,RIGHT(SMALL(RANK($E$3:$E$8,$E$3:$E$8)*100000+ROW($E$3:$E$8),ROW()-ROW($2:$2),5)I3单元格积分公式如下：=VLOOKUP($H3,$A:$E,5,)其中，部门列表公式主要利用RANK函数与ROW函数重新生成内存数姐，再利用SMALL函数从小到大进行提取，最后利用INDEX函数生成部门名称。利用RANK函数将数值化零为整地转换为数值排名，在带有小数的数值排名应用中非常有用，可以免受Excel有效位数最多为15位的限制。SMALL函数和LARGE函数排名法L3单元格积分提取公式如下：=INDE

6、X($A:$A,SMALL(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输入公式外，创建数组公式的方

7、法与创建其他公式的方法相同。某些内置函数是数组公式，并且必须作为数组输入才能获得正确的结果。计算单个结果此类数组公式通过用一个数组公式代替多个公式的方式来简化工作表模式。例如，下例计算一组股票价格和股份的总价值，而不是使用一行单元格来计算并显示出每支股票的总价值。如图当将公式=SUM(B2:D2*B3:D3)作为数组公式输入时，该公式将每支股票的“股份”和“价格”相乘，然后再将这些计算结果相加。计算多个结果一些工作表函数返回多组数值，或需要将一组值作为一个参数。如果要使数组公式能计算出多个结果，则必须将数组输入到与数组参数具有相同的列数和行数的单元格区域中。例如，如果给出了对应于三个月份（列

8、A 中）的三个销售额（列B中），则 TREND 函数会返回销售额的直线拟合值。若要显示公式的所有结果，则应在列 C 的三个单元格 (C1:C3) 中输入该公式。当将公式=TREND(B1:B3,A1:A3) 作为数组公式输入时，它会根据三个月的三个销售量得到三个不同的结果（22196、17079和11962）。7. 实例讲解使用数组公式显示的日历在本例中，将使用数组公式实现一个动态的日历，根据本机的时间显示当前一个月的日历。下面先说明这个日历的实现步骤：1.在A1单元格内输入公式“=TODAY()”，在A2单元格内输入“=A3”，将A2单元格复制到B2:G3单元格区域。2.设置A2:G2单元格

9、的格式，使其分类为日期的星期，选中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,4,5,6,7+0;1;2;3;4;5*7”，构成了这个日历的框架。如果在一个6 行7列的单元格区域内输入这个输入公式，可以得到6行7列

10、的二维数组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单元格，来得到本月一日的日期。本月一日的日期和6行7列的二维数组相加，又可得到一个6行7列的二维数组。这个二维数组实现了日期的逐一显示。但是这个按照曰期逐一显示的

11、二维数组是从2号开始的，而且2号显示在了星期一的位置。“WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),2)”部分用来对日期的位置进行调整。以2007年1月为例(2007年1月1日为星期一)，可以得到值“-1”。这样就对按日期逐一显示的6行7列二维数组做出了调整，2007年1月1日出现在二维数组第一行的第一个位置，而之后的日期逐一显示。通过IF函数，可以使日历中的非当月日期不显示。4.选中A3:G8单元格区域，把公式修改为：=IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)MONTH(DATE(YEAR(A1),MONTH(A1),1)-WEEKDA

12、Y(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;1;2;3;4;5*7)最后按Ctrl+Shift+Enter组合键确认输入。这样就以数组公式的方式显示出日历。通过修改各单元格的格式，可以使显示更加美观。8. 用数组公式统计区域中的错误值在工作表中， 经常会出现公式计算得出错误值的情况。下面的例子演示了对错误值的统计。实例：如图所示，单元格区域内有多个错误值

13、，现希望统计出其中包含错误值#NULL!的单元格个数。具体步骤如下：选中E14单元格，输入公式“=COUNT(IF(ERROR.TYPE(A1:E9)=1,1)”，按Ctrl+Shift+Enter组合键确认输入。这样即在E14单元格内统计出了非空单元格的个数。现分析这个数组公式：ERROR.TYPE函数判断A1:F9单元格区域内各个单元格内的错误值类型。如果单元格内含有错误值，ERROR.TYPE函数返回相应数值。对于错误值#NULL!，返回“1”，而不含错误值的单元格，ERROR.TYPE函数返回错误值“#N/A”。IF函数根据ERROR.TYPE函数的返回值是否等于1进行判断，如果是则返

14、回“1”。由上一步返回的各值构成一个数组并存储在内存中。COUNT函数统计出这个数组中数字元素(即“1”) 的个数。即是A1:E9单元格区域内包含错误值#NULL!的单元格个数。根据下表中的公式，可以对含有其他类型错误值的单元格进行统计。#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.T

15、YPE(A1:E9)=5,1)#NUM!：=COUNT(IF(ERROR.TYPE(A1:E9)=6,1)#N/A：=COUNT(IF(ERROR.TYPE(A1:E9)=7,1)9. 利用数组公式计算条件计数在实际应用中，可能会需要对单元格区域内某一类型的数据(文本或数字)进行统计。下面的例子只展示了一个应用。如果修改例子中的公式，则可以达到其他的统计目的。实例：如图所示，在表格中有各种数据类型的多个数据，现希望统计出有多少个单元格中有数据。具体步骤如下：选中C12单元格，输入公式“=COUNT(IF(ISBLANK(A1:E9),1)”，按Ctrl+Shift+Enter组合键确认输入。这

16、样即在C12单元格内统计出了非空单元格的个数。现分析这个数组公式：IS8LANK函数判断A1:E9单元格区域内各个单元格是否为空。如果单元格内含有数字、文本、逻辑值、公式或错误值，IS8LANK函数返回逻辑值“FALSE”，而对于空单元格，IS8LANK 函数返回逻辑值“TRUE”。IF函数根据IS8LANK函数的返回值进行判断，对逻辑值“FALSE”返回空值，而逻辑值“TRUE”返回“1”。由上一步返回的各值构成一个数组并存储在内存中。COUNT函数统计出这个数组中数字元素(即“1”)的个数，即是A1:E9单元格区域内有数据的单元格个数。10. 在excel2007中使用数组进行条件求和计算

17、在excel2007中，我们可以利用SUM、AVERAGE、COUNT等函数对某单元格区域内的数据进行加总、平均或计数等操作。下面通过两个例子介绍应用数组只对单元格区域中符合某些条件的数据进行求和运算。实例1如图的左边所示，在A1:F16单元格区域内存储的数字有正有负。现只希望对其中的正数求和。具体操作步骤如下：选中B18单元格，在编辑栏内输入“=SUM(IF(A1:F16)0,(A1:F16),)”，按Ctrl+Shift+Enter组合键确认输入。现分析这个数组公式如下：IF公式对A1:F16单元格区域内的数据进行判断，如果数据大于零，则返回原数据，如果数据小于或等于零，则返回空值。由于I

18、F函数的返回值构成一个新的数组(存储在内存中)，原单元格区域内大于零的数据直接成为新数组中的元素，原单元格区域内小于或等于零的数据变为空值出现在新数组中。SUM公式对新数组内的元素进行加总，从而得到了A1:F16单元格区域内大于零的数据的和。根据IF函数的定义，可以简化这个公式为“=SUM(IF(A1:F16)O,(A1:F16)”。实例2有12个评委在比赛中评分，需要去掉一个最高分和一个最低分，再以其余分数的和作为选手的得分，如图的右边所示。具体步骤如下：在I15单元恪内输入“=MAX(I2:I13)”，求出评委给出的最高分。在I16单元恪内输入“=MIN(I2:I13)”，求出评委给出的最

19、低分。在I17单元格内输入“=SUM(I2:I13I15)*(I2:I13I16)*I2:I13)”。按Ctrl+Shift+Enter组合键确认输入。现分析这个数组公式如下：在这个公式中，首先计算的是两个判断条件，“I2:I13I15”和“I2:I13I16”，分别用来判断是否是最高分或最低分。当最高分或最低分参加运算时，两个判断条件之一返回逻辑值“FALSE”；当非最高分或最低分参加运算时，两个判断条件均返回逻辑值“TRUE”。随后的乘法计算(I2:I13I15)*(I2:I13I16)*I2:I13将前一步得出的两个逻辑值和数据本身相乘(逻辑值“FALSE”在计算中以“0”计，逻辑值“T

20、RUE”在计算中以“1”计)。这样， 最高分和最低分在计算中返回“0”，而其他值返回原值。由上一步返回的各值构成一个数组并存储在内存中。SUM函数对这个数组的各元素加总求和，即得出选手的得分。11. excel2007中数组计算的基础本文举例演示了数组在简单运算中的表现。这些都是理解数组参与复杂公式的基础。实例1在本例中使用一个数组和一个常数相乘进行计算。具体操作步骤如下：选中A2:F2单元格区域，在编辑栏内输入“=1,2,3,4,5,6*3”，按Ctrl+Shift+Enter组合键确认输入。计算结果在A2:F2单元格区域内显示为一个新的数组3,6,9,12,15,18，如下图所示。如果一个

21、数组和一个常数进行计算(相加、相减、相乘或相除) ，则数组中的每一个元素分别和常数计算，并由计算的结果值构成一个新的数组，而数组的结构(即元素的组织形式)不发生变化。实例2在本例中使用两个不同元素个数的一维数组相加计算。选中A4:E4单元格区域，在编辑栏内输入“=1,2,3,4,5+2,4,6,8”，按Ctrl+Shift+Enter组合键确认输入。计算结果在A4:E4单元格区域内显示为数组3,6,9,12,#N/A，如下图实例3在本例中使用一个横向一维数组和一个纵向一维数组相加进行计算。选中A6:E9单元格区域，在编辑栏内输入“=1,2,3,4,5+2;4;6;8”，按Ctrl+Shift+

22、Enter组合键确认输入。在A6:E9单元格区域内显示出一个4行5列的二维数组：3,4,5,6,7;5,6,7,8,9;7,8,9,10,11;9,10,11,12,13两个数组相加(相减、相乘或相除)时，对应位置的元素进行相应运算，成为新数组的元素。如果两个数组在横向上或纵向上元素个数不同，则只在两数组同时有元素的位置返回元素相应运算的结果值，不匹配的位置返回错误值#N/A。12. 在excel2007中编辑或删除数组公式下面来说明在excel2007中编辑或删除数组公式的方式。首先尝试下面的操作。当用户选中数组公式中的其中某一个单元格，然后按Delete键，Excel弹出提示框，提示“不能

23、更改数组的某部分”的操作错误。同样，如果尝试在编辑栏修改数组公式中的其中某一个单元格，也会弹出这个提示框。这是因为， Excel把存储着数组或数组公式的区域当作一个整体来对待，无法单独修改或删除其中一个单元格的内容，也不能在这个区域内添加(或删除)单元格(或行或列)。这样， 使用数组公式就大大减少了公式被意外修改或删除的可能性。数组和数组公式存储在由多个单元格组成的一个单元格区域里时，这些单元格在编辑操作方面失去了独立性，而成为一个整体。当需要编辑修改一个数组或数组公式时，需要对这个区域整体操作。可以归纳编辑或删除数组公式(或数组)的不同情况如下：如果要把区域内的数组公式(或数组)修改为另一个

24、数组公式(或数组)，需选中其中任一单元格或整个区域，编辑后按Ctrl+Shift+Enter组合键确认输入。如果要删除整个区域内的数组公式(或数组)，需选中整个区域再删除。如果要把区域内的数组公式(或数组)修改为常规公式，需先删除后再输入。如果要修改数组公式(或数组)的区域，需先删除原数组公式(或数组)，更改区域范围，再写入新的数组公式(或数组)。如果要移动显示数组公式的单元恪区域，需选中整个区域再移动。13. excel2007单元格区域与输入数组的范围不符在向Excel2007中输入数组时，如果选定的单元格区域横向上小于数组横向的元素个数，或纵向上小于数组纵向的元素个数，数组元素将按照横向

25、从左向右、纵向从上向下的顺序逐一显示在选定区域的各单元格中。而在选定的单元格区域内没有对应单元恪位置的数组元素不被显示。例如，只选定了横向的4个单元格A1、B1、C1和D1，并输入数组1,2,3,4,5,6，则在A1、B1、C1和D1这4个单元格中逐一显示数组1,2,3,4,5,6中左起前4 个元素。同样的，只选定了纵向的5个单元格A1:A5，并输入数组星期一;星期二;星期三;星期四;星期五;星期六;星期日，则只显示从上向下的前5个元素。如果选定的单元格区域横向上大于数组横向的元素个数，或纵向上大于数组纵向的元素个数，则在无对应数组元素的选定单元格内显示错误值#N/A。例如，在输入数组O,1,

26、2,3;TRUE,FALSE,TRUE,FALSE;优,良,可,差时，选定的B2:F12单元格区域(5行11列)大于数组的范围(3行4列)，数组元素横向从左向右、纵向从上向下地以显示在选定的单元格区域内左上角起的对应单元格内，而无对应位置的单元格均由错误值#N/A填充。14. 在excel2007中使用数组进行计算本文将介绍如何在公式中使用数姐。从以下这些基本计算的实例中，可以体现出数组在公式中如何参与计算。掌握数组在基本运算中的特性，才可以在复杂的计算中应用数组。实例1在本例中使用一个数组作为函数的参数进行计算。在单元格A1内输入公式“=AVERAGE(1,2,3,4,5,6)”，按Ente

27、r键结束输入。A1单元格显示公式计算的结果“3.5”，这个公式中，AVERAGE函数的参数是一个数组1,2,3,4,5,6。公式计算了这个数组里6个元素的平均值。实例2在本例中使用两个数组作为函数的参数进行计算。在A1单元格内输入“= AVERAGE(1,2,3,4,5*6,7,8,9,10)”，按Enter键结束输入。A1单元格显示公式计算的结果“26”。在这个例子中，函数的计算用到了两个相同元素个数的一维横向数组1,2,3,4,5和6,7,8,9,10，最后产生一个结果值。现逐步分析这个公式的计算过程如下：1.Excel先以这两个数组对应元素相乘，由乘积组成一个新的数组“6,14,24,3

28、6,50”。这个新数组被存储在内存里。2.AVERAGE函数计算这个新数组中各元素的平均值。15. 使用数组公式对数据排序 通常，对一个范围中的数据进行排序是非常有帮助的。例如，如果我们的工作表包含了20位销售人员的年销售量，我们希望知道从高到低，每个人的排序情况。如果你曾经使用过Excel的RANK函数，也许会注意到该函数所产生的排序不会按你所希望的方式处理平局问题。例如，两个数值都处于第三位，RANK函数对这两个数值的排序都是3。你也许更喜欢为每一个数值赋予序号的平均值(或者中点)。换句话说，把处于第三位的两个数值赋于3.5的序号。假设在一个工作表中使用两种方法对数值列(名为Sal) 排序

29、。第一种方法(C列)使用Excel的RANK函数。D列使用数组公式计算顺序。下面是位于单元格D2中的数组公式=SUM(1*(B2=Sales)-(SUM(1*(B2=Sales)-1)/2 该公式被复制到它下面的单元格中。每个函数的计算过程是，计算比较大的数值的个数，再减去该数值减一的一半。16. 用数组函数返回一列中最后一个数值假设我们有个工作表，由于向列中添加新的数据而经常需要更新这个工作表。我们也许需要一种方法来引用A列中最后一个数值(最新输入的数值)。如果A包含非空单元格，答案相对容易一些。并且不需要数组公式：OFFSET(A1,COUNTA(A:A)-1,0)该数组公式使用COUNT

30、A函数计算A列中非空单元格的数量。这个数值(负1)用做OFFSET函数的第二个参数。例如，如果最后的数值位于100行，COUNTA函数返回100。OFFSET函数返回位于从单元格A1开始下数99个单元格中的数值。它们位于同一列。如果A列之间含有一个或者多个空的单元格，这是经常出现的情况，前面的公式不能正常使用，因为COUNTA函数不能对空单元格计数。下面的数组公式返回A列中前500行中最后一个非空单元格：=INDEX(A1:A500,MAX(ROW(A1:A500)*(A1:A500)当然，我们可以对这个公式进行修改，使它能作用到其他列上。要使它作用到不同列上，只要把对A的4次列引用修改为我们

31、所需要的列上即可。如果在一行上的最后一个非空单元格出现的位置位于500之下，需要把两个常数“500”变成一个较大的数值。在公式中引用的行越少，计算速度越快。提示：如果想返回一行中最后一个数值，请使用下面的公式：=INDEX(1:1,MAX(COLUMN(1:1)*(1:1)公式中的1:1指的是行号，如果是其它行，请更改为其它行的行号。17. excel范围中相隔n个数的数值求和假设我们有个数值范围，并且希望计算列表中每隔三个数的数值和，如第一、第四、第七等。一个解决办法是将相应单元格的地址硬编码到公式里。但是，比较好的解决办法是使用数组公式。假设在一个工作表中，数值保存在名为Data的范围内，

32、n的值在单元格E6(名为n)中。下面的数组公式返回范围中相隔n个数的数值和：=SUM(IF(MOD(ROW(INDIRECT(1:&COUNT(Data)-1,n)=0,Data,)该公式创建一个连续整数的数组，并且MOD函数把它用做第一个参数。MOD函数的第三个参数是n的数值。MOD函数创建另一个数组，把每一个行数除以n。得到的余数就存放在这个数组里。当数组元素为0时(即行数能被n除尽)。Data范围中相应的元素包含在和中。我们将会发现，当n为0时，该公式计算失败(即没有任何元素求和)。下面修改后的公式使用IF函数来处理这种情况：=IF(n=0,0,SUM(IF(MOD(ROW(INDIRE

33、CT(1:&COUNT(Data)-1,n)=0,data,)只有当Data范围由单列数值构成时，该公式才能使用。它不能用于多列的范围，或者用于单行数值。要使该公式适用于横向范围，我们需要转置使用ROW函数所生成的数组。下面修改后的数组公式只用于纵向的Data范围：=IF(n=0,0,SUM(IF(MOD(TRANSPOSE(ROW(INDIRECT(1:&COUNT(Data)-1,n)=0,Data,)18. 确定范围中最接近的数值下面的数组公式返回在名为Data范围中的一个数值，它与另外一个数值(名为Target)最接近。=INDEX(Data,MATCH(SMALL(ABS(Targe

34、t-Data),1),ABS(Target-Data),0)如果在Data范围中有两个数值等于Target数值，该公式返回列表中的第一个数值。假设在Data的范围内包括以下数值：10,15,12,50,23,68；Target值为25，则该函数返回“23”，因为23与25最为接近。19. 对单元格中整数数字进行求和下面的数组公式计算一个正整数数字的和，该整数保存在单元格A1里。例如，如果单元格A1包含值409。公式返回13(4、0和9的和)。=SUM(MID(A1,ROW(INDIRECT(1:&LEN(A1),1)*1)要了解该公式的工作原理，让我们先使用ROW函数，表示如下：ROW(IND

35、IRECT(1:&LEN(A1)这个函数返回一个连续整数的数组，以1开始，以单元格A1中数值的数字位数结束。例如，如果单元格A1包含409，则LEN函数返回3，并且由ROW 函数产生的数组为：1,2,3该数组然后用做MID函数的第二个参数。公式的M1D部分简化了一些并且表示为数值，表示如下：=MID(409,l,2,3,1)*1该函数生成一个拥有3个元素的数组：4,0,9通过进一步简化并且添加SUM函数，公式如下所示：=(4,0,9)它生成的结果是13。请注意，该公式不能使用负数，因为负数的符号不是数字值。下面的公式解决了这个问题，它使用ABS函数强回数字的绝对值。=SUM(VALUE(MID

36、(ABS(A2),ROW(INDIRECT(1:&LEN(ABS(A2),1)20. 确定一个范围是否包含有效数值我们也许存一个元素列表，需要把它与另一个列表进行检查对照。例如，我们可能把一个部分数据的列表输入到一个名为MyList 的范围中，并且希望保证这些数据是有效的。我们可以这样做，把输入列表中的元素与部分数据的主列表(名为Master)里的元章进行比较。如果在名为MyList范围中的每一个元素都能在名为Master的范围中找到，下面的数组公式返回TRUE。这两个范围都必须由一个单列构成，但是，它们没有必要包含相同的行数。=ISNA(MATCH(TRUE,ISNA(MATCH(MyLis

37、t,Master,O),O)下面的数组公式返回各元素的数量。换句话说，它返回MyList中没有出现在Master中的元素数量。=SUM(1*ISNA(MATCH(MyList,Master,0)要返回MyList中的第一个无数元素，使用下面的数组公式：=INDEX(MyList,MATCH(TRUE,ISNA(MATCH(MyList,Master,0),0)21. 返回范围中的最长文本的公式下面的数组公式显示范围(名为Data)中一个文本串，它包含的字符最多。如果有多个单元格包含最长的文本串，返回第一个单元格的文本。=INDEX(Data,MATCH(MAX(LEN(Data),LEN(Da

38、ta),FALSE),1)该公式使用两个数组，它们都包含Data范围中每个元素的长度。MAX函数确定最值，它对应最长的文本元素。MATCH函数计算包含最大长度的单元格的偏移量。INDEX函数返回包含最多字符的单元格的内容。只有当Data范围由单列构成的情况下，该函数才能执行。22. 查找范围中某个值第n次出现时的行下面的数组公式返回一个单列范围内的行号，该范围名为Data，一个名为Value的单元格在它上面出现了第n次：=SMALL(IF(Data=Value,ROW(Data),),n)IF 函数创建一个新的数组，它包含Data范围中一个与Value相等的值的行号。Data范围中与Value

39、不相等的值用空字符串代替。SMALL函数作用在这个新的数组上，并且返回第n个最小的行号。如果没有找到Value或者n超过了范围中该值的数量，则公式返回#NUM!。23. excel2003中多单元格数组公式介绍如图表示个用于计算商品销售额的工作表。通常情况下，你也许会使用下面的公式计算D列的值(每种商品的销售总额)，然后把这个公式复制到其所在列的其他单元格之中。=B2*C2公式复制完之后，工作表在D列中包含了六个公式。一个可替代的方法是使用一个单一的公式(数组公式)计算所有在D2:D7范围内的六个值。这个单一的公式占据六个单元格并且返回个六个值的数组。要创建一个单一的数组公式来完成这个计算，按

40、下列步骤进行：选择保存结果的范围。在本例中，范围是D2:D7。输入下列公式：=B2:B7*C2:C7通常情况下，我们按Enler键输入公式。然而，由于这里输入的是数组公式，因此要按Ctrl+Shift+Enter组合键。这个公式输人到所选定的六个单元格之中。如果我们查看公式栏，会看到下面的结果：=B2:B7*C2:C7Excel在公式两端加上括号表示它是一个数组公式。这个公式执行它的计算并且返回一个拥有六个元素的数组。这个数组公式实际上使用了另外两个数组，其中每个数组都存储在特定范围内。第一个数组的值存储在B2:B7范围内，第二个数组的值存储在C2:C7范围内。因为在个单元格里不可能显示多个值，因此需要多个单元格来显示结果数组。这就解释了为什么在输入数组公式之前要选择六个单元格的原因。当然，这个数组公式返回的数值与在D2:D7范围内的单元格中分别输入公式得到的结果完全相同。=B2*C2=B3*C3=