数组公式的初步应用

1、1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.目录利用数组公式实现单条件求和2将二维数组按列转换为一维数组3使用数组公式实现部门评价等级转换4使用数组公式按条件筛选数据5利用数组公式按比赛成绩进行降序排列6使用excel2010数组公式计算一个或多个结果7实例讲解使用数组公式显示的日历8用数组公式统计区域中的错误值9利用数组公式计算条件计数11在excel2007中使用数组进行条件求和计算12excel2007中数组计算的基础13在excel2007中编辑或删除数组公式14excel2007单元格区域与输入数组的范围不符15在excel2007中使用数组进行计算

2、15使用数组公式对数据排序16用数组函数返回一列中最后一个数值1617. excel范围中相隔n个数的数值求和 1718. 确定范围中最接近的数值1819. 对单元格中整数数字进行求和1820. 确定一个范围是否包含有效数值1921. 返回范围中的最长文本的公式1922. 査找范围中某个值第n次出现时的行 1923. excel2003中多单元格数组公式介绍 2024. excel数组公式不利的一面2125. 返回范围中惟一元素的列表2126. 值范围的动态分类221. 利用数组公式实现单条件求和如图展示了一份某商场中商品进货明细表，下面利用统计函数与数组公式两 种方法进行数据统计。G3 扇=

3、O)UHriF(D:D">5")ABCDGH1M号空场fit条计讲毎大佔的SJ号裁2空调KFR-32GV/8个数3空调KFR-35GW4COWWTIF5E 计74KFR-50LW5sraefiftSc 计75关的KFR-23GW86空调鼻的KFR-51LWT格力KFR-70LW3is计进益走大于琲于w的进貨stem8手机诺也！6810i4个敘9手机诺応E762010swir® 计4010手机西审SL653svarBOBucTcrt4011三足S308<112手杭三足S6061313手杭三足E7036统计进货量大于5台的型号个数G3单元格利用COUNTI

4、F函数统计公式如下：二COUNTIF(D:D,">5")G4单元格得用SUM函数数姐公式如下：=SUM($D$2:$D$13>5)*1)统计进货最大于5且小子10台的总进货量G9单元格利用SUMIF函数统计公式如下：二SUM (SUMIF (D: D, ">5",">二10") * 1, -1)G10单元格利用SUMPRODUCT函数的统汁公式如下：二SUMPRODUCT($D$2:$D$13>5)*($D$2:$D$13<10)*$D$2:$D$13)以上儿个公式的主要区别在于： SUM函数除了对

5、单元格区域进行条件统汁以外，还可以对数组进行条件统计，使 用SUM函数数组公式的用法则相对灵活； COUNTIF函数或SUMIF函数可以使用整列区域进行统汁，但SUM函数或SUMPROOUCT函数公式必须明确指定数据区域范围来进行统汁，如SDS2:SDS13,否 则数组公式返回错误值“#NUM!”2. 将二维数组按列转换为一维数组在如图所示的工作表中，其中A3:C6为一个二维数组，下面的公式将分别按 先行后列的顺序转换为一个一维数组。F2 /y =SH90SE (HDUI-1DUP (Rows/BS (Array), LOOKUP (RcABINDEX (Array, 2); LCOKUP(f

6、tjws, Eows+ftS(Airas1转揍公式2列i列2列3A3ABcD4D1iG5GMIJ6JELB?E8:9Array叮空TK10二测 aMDIRECI ("】:伽)C11F12I13L1为了简化公式，首先定义名称如下。待转换数组 Array：二"A", "B", "C" "D", "E", "F" "G", "H", "I" 丁, "K", "L"取得总行数

7、 ROWS： =ROW(INDIRECT C1: "SCOUNTA(Array)F2:F13多单元格联合数组公式如下：=CHOOSE(ROUNDUP(Rows/ROWS(Array),), LOOKUP(Rows, Rows, INDEX(Array, 1), LO OKUP(Rows, Rows+ROWS(Array), INDEX(Array, 2), LOOKUP(Rows, Rows+ROWS(Array) *2, INDEX (Array, ,3)公式主要利用HOUNDUP函数来等长度变换自然数序列，将1-12的序列转换为 1;1;1;1;2;2;2;2;3;3;3;3,再

8、利用LOOKUP函数分别对子数组进行分段查询，最 后生成合并后的一维数组。3. 使用数组公式实现部门评价等级转换在如图所示的工作表中，B2:E8单元格区域是部门综合考评表，B11:C14单元 格区域为优、良、中、差等级对应的分数，C3:E3单元格区域为3个考核项目的系 数。要求计算各部门的综合评分，即各部门的各项评价等级转为分数后与对应项 目系数的乘积之和。F12单元格评分数组公式如下：匸SUM(C$3:E$3*SUMIF(B$11:B$14, C$4:E$8, C$11:C$14)*(B$4:B$8二E12)公式中 “ (C$3:E$3*SUMIF(B$11:B$14,C$4:E$8,C$1

9、1:C$14) ” 段即是一维数组与二 维数组的乘法运算。其中，系数C3：E3区域数组arrayOl为1行3列水平数组。SUMIF(B$11:B$14,C$4:E$8, C$U:C$14)公式结果为内存数组 array02, 5 行 3 列数 组，公式结果得到每个部门对应的系数分。山于在水平方向上，array01与array02都具有相同的尺寸，因此数组array01会 与array02连行相乘运算，结果仍然为5行3列内存数组。最后再与(B$4:B$8二E12)的部门比较过滤后的一维逻辑数组(5行1列)相乘，最后 求和得岀部门的综合评分。4. 使用数组公式按条件筛选数据在日常工作中，经常需要针

10、对某些查找条件对数据表进行筛选，使用数据表 的筛选功能往往可以临时解决查询问题。下面主要介绍如何利用数组公式返回数据 表中同时满足多个查找条件的记录。如图所示的工作表中有一份客户进货记录表，要求根据日期和客户名查询相应的进 货记录。ABcDE I FG 1HI1日期冨户It名进找越号23虫三S100110査诲日Ml直“富户200T-lO3w-e-to9100220弟三4王五S100330査诲箔杲2U7Z-6-10李四SL004qO序号进戏单号sists丄72W-&-I0老三S1005501103w-e-io壬五SL0066025082077-JL0074093W-6-11壬五SL006

11、2010沏如1蛋三5100930G6单元格序号公式如下：=IF (ROW () -5>SUMPRODUCT ($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), RO W($B$2:$B$10),$G6)公式主要利用IF函数将数据表记录分别与日期、客户名进行判断，将满足条件的 记录的行号提取出来，再利用INDEX函数返回结果

12、，最后将H6公式复制到110单 元格即得结果。5利用数组公式按比赛成绩进行降序排列如图展示了一份某公司秋季运动会的比赛成绩明细表，下面的公式将实现根 据比赛成绩进行部门排名。或JH & -BJT1EXRIGHT(SIAL1巧厂；：厂厂.厂厂 厂订:“°-7 "hBCD1F GR.I_JK11KE力扮名方ttz3BHm分堵次»aan4wt *a啓台tr534 c§T8祓MR221设计邵32ws-a229匕ftr-ff.32处广哥3?ftrw32wear.R¥sea-RR笈一剖z?硏逆一 83cBc幻s疣£二制痕二“29 RAK函

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

14、的限制。 SMALL函数和LARGE函数排名法L3单元格积分提取公式如下：=INDEX ($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中，数组公式可以执行多项计算并返回一个或多个结果。数组 公式对两组或多组名

15、为数组参数的值执行运算。每个数组参数都必须有相同数量的 行和列。除了用Ctrl+Shift+Enter输入公式外，创建数组公式的方法与创建其他 公式的方法相同。某些内置函数是数组公式，并且必须作为数组输入才能获得正确 的结果。计算单个结果此类数组公式通过用一个数组公式代替多个公式的方式来简化工作表模式。 例如，下例讣算一组股票价格和股份的总价值，而不是使用一行单元格来计算并显 示出每支股票的总价值。如图LorernIpsurn股份5003001价格10总价值I=SUM(B2;C2*B3;C3,)当将公式二SUM（B2:D2祁3:D3）作为数组公式输入时，该公式将每支股票的 “股份”和“价格”相

16、乘，然后再将这些计算结果相加。计算多个结果一些工作表函数返回多组数值，或需要将一组值作为一个参数。如果要使数组公式 能讣算岀多个结果，则必须将数组输入到与数组参数具有相同的列数和行数的单元 格区域中。例如，如果给出了对应于三个月份（列A中）的三个销售额（列B中），则 TREND函数会返回销售额的直线拟合值。若要显示公式的所有结果，则应在列C 的三个单元格（C1:C3）中输入该公式。当将公式二TREND（B1:B3,A1:A3）作为数组公式输入时，它会根据三个月的三个销售 量得到三个不同的结果（22196、17079和11962）。12023421003JIJQQOq=TRENDCB1:B3.A1:A3)产生多个结果的数爼公式7.实例讲解使用数组公式显示的日历在本例中，将使用数组公式实现一个动态的日历，