EXCEL数组公式在条件统计中应用.docVIP

EXCEL数组公式在条件统计中应用 　　摘要：阐述了EXCEL数组公式的概念、类型与编辑的方法，以实例的形式说明了数组公式在多字段条件下进行各类统计的独特长处。 　　关键字：EXCEL；数组公式；条件统计 　　中图分类号：TP39文献标识码：A 文章编号：1009-3044(2008)30-0747-02 　　 　　EXCEL array Formula in Terms of Statistics 　　YE Jian-liang 　　(Shaoxing County Vocational Education Center,Shaoxing 312030,China) 　　Abstract: EXCEL expounded the concept of an array formula, type and edit approach to examples in the form of an array formula that the conditions in many fields under the unique strengths of various types of statistics. 　　Key words: EXCEL; Array formula; Statistics conditions 　　 　　作为微软OFFICE组件之一的EXCEL，不仅具有制表方便灵活、输出数据格式丰富美观的特点，而且因为具有强大的的数据检索、排序、筛选与统计功等功能，被广泛应用于企事业单位的信息处理之中。但在数据统计方面，EXCEL系统提供的系统函数相对较少，如无条件相关的SUM，AVERAGE与条件相关的COUNTIF，SUMIF等，面对多字段条件的数据统计，系统函数显得束手无策。笔者经过较为深入、系统的研究，认为使用EXCEL的数组公式，可较好地解决此类问题。本文以实例方式探讨了数组公式的有关知识及其在多字段条件下进行数据统计的公式编写。 　　 　　1 数组公式简介与编辑 　　 　　1.1 概念 　　所谓EXCEL数组公式，即公式中使用了数组，对一组或多组值执行多重计算，并返回一个或多个结果，其形式表现为在地址栏中公式或数据面被“{ }”括住，但公式首尾的“{ }”由系统自动生成而不是手工添加上去的（如图1）。 　　1.2 类型 　　数组公式按其取得结果单元格的多少，可以分为占据多个单元格的数组公式（如图C4：C8）与只占一个单元格的数据公式（如图D4），在条件统计中，基本上为后者。数组有常量与区域两类，常量数组中数据要放在“{}”中，而且内部各列的数值要用逗号“,”隔开（如图D10：“=SUM({1,2,3}*{4,5,6})”，当二维数组时各行的数值要用分号“;”隔开）。区域数组用工作表中的单元格区域表示，如“A4:A8”、“B4:B8”。 　　1.3 创建与编辑公式 　　1.3.1 创建 　　A 选择要保存结果的单元格区域，如C4：C8（或D4） 　　B 输入公式 =A4：A8*B4：B8 (或=sum(A4：A8*B4)) 　　C 同时按Ctrl+Shift+Enter三键，则单元格中自动产生数组公式“{=A4：A8*B4：B8}”。 　　1.3.2 编辑 　　编辑数组公式，应先选定数组公式的范围，然后在地址栏中加以修改（或按F2），编辑完毕后再按Ctrl+Shift+Enter三键。如果是删除公式，则在选定范围后直接按DELETE键。 　　 　　2 数组公式中的条件表达与相关统计函数 　　 　　2.1 条件表达 　　数据统计中条件的类型一般不外乎“与、或、非”三种。在数组公式应用中， “非”关系一般用“”表示，字段之间的“与”关系，用IF语句嵌套实现或用条件间相乘（“*”）来表示，而不用AND函数来表达，字段之间的“或”关系，用条件间相加（“+”）来表示，而不用OR函数来表达。 　　2.2 相关统计函数 　　根据统计要求的实际情况，一般下列函数在数组公式中应用较多，其名称与作用如表1。 　　3 数组公式应用实例 　　图为我校07级计算机应用专业1班学生入学情况，由于人员变动，已对其状态作了调整。总体情况为，入学总人数48人，已转出1人，流生5人，现在册42人，其中男生24人，女生18人。现就数组公式在条件统计中的应用举例如下： 　　公式：{=SUM(IF((D7:D54)=在册,1,0))} 　　公式分析：IF函数分别检测单元格D7至D54，如果其值为“在册”，则返回值1，否则为0，SUM函数对返回的值进行求和。 　　例2：求班级全部入学学生社会科目在50至60之间的总成绩 　　公式：{=SUM(IF(((J7:J54)=50)*(