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

目录对单元格中整数数字进行求和 18 20. 确定一个范围是否包含有效数值 19 21. 返回范围中的最长文本的公式 19 22. 查找范围中某个值第n次出现时的行 19 23. excel2003中多单元格数组公式介绍 20 24. excel数组公式不利的一面 21 25. 返回范围中惟一元素的列表 21 26. 值范围的动态分类 22 1. 利用数组公式实现单条件求和 如图展示了一份某商场中商品进货明细表，下面利用统计函数与数组公式两种方法进行数据统计。 ??? 　★统计进货量大于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$1310)*$D$2:$D$13) ??? 　以上几个公式的主要区别在于：??? 　●SUM函数除了对单元格区域进行条件统计以外，还可以对数组进行条件统计，使用SUM函数数组公式的用法则相对灵活；??? 　●COUNTIF函数或SUMIF函数可以使用整列区域进行统计，但SUM函数或SUMPROOUCT函数公式必须明确指定数据区域范围来进行统计，如$D$2:$D$13，否则数组公式返回错误值“#NUM!” 2. 将二维数组按列转换为一维数组 在如图所示的工作表中，其中A3:C6为一个二维数组，下面的公式将分别按先行后列的顺序转换为一个一维数组。 　　为了简化公式，首先定义名称如下。　　●待转换数组Array：={A,B,C;D,E,F;G,H,I;J,K,L}　　●取得总行数ROWS：=ROW(INDIRECT(1:COUNTA(Array)))　　F2:F13多单元格联合数组公式如下：??? 　{=CHOOSE(ROUNDUP(Rows/ROWS(Array),),LOOKUP(Rows,Rows,INDEX(Array,,1)),LOOKUP(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}，再利用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$11:C$14)”段即是一维数组与二维数组的乘法运算。??? 　其中，系数C3:E3区域数组arrayO1为1行3列水平数组。??? 　SUMIF(B$11:B$14,C$4:E$8,C$11:C$14)公式结果为内存数组array02，5行3列数组，公式结果得到每个部门对应的系数分。??? 　由于在水平方向上，array01与array02都具有相同的尺寸，因此数组array01会与array02连行相乘运算，结果仍然为5行3列内存数组。??? 　最后再与(B$4:B$8=E12)的部门比较过滤后的一维逻辑数组(5行1列)相乘，最后求和得出部门的综合评分。 4. 使用数组公式按条件筛选数据 在日常工作中，经常需要针对某些查找条件对数据表进行筛选，使用数据表的筛选功能往往可以临时解决查询问题。下面主要介绍如何利用数组公式返回数据表中同时满足多个查找条件的记录。　　如图所示的工作表中有一份客户进货记录表，要求根据日期和客户名查询相应的进货记录。 　　G6单元格序号公式如下：　　=IF(ROW()-5SUMPRODUCT(($B$2:$B$10=$H$2)*($C