AVERAGE函数特殊用法.doc

AVERAGE函数的特殊用法 ——谈excel函数功能的开发和应用 内容提要：excel电子表中AVERAGE函数的运用常见的是一些单元格或区域进行平均值计算。本文运用数学中的类比思想，将excel中sum(if())函数的计数功能合理的运用到average函数中，经过复验证，总结出average函数的数组功能，在中小学分班成绩统计分析中，有着重要的运用价值。 关键词 average函数 特殊运用 成绩分析 动态管理 一、问题的提出 　　Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，AVERAGE函数可以对单元格或单元格区域进行平均值运算。但是，我们在学校日常工作中，对每学期的成绩管理往往预设的条件很多，这就给我们使用AVERAGE函数带来了更高的要求。 工作实例1:我们学校七年级有8个教学班级，每次考试都需要全年级混编场，试卷登统后再分别计算各班的均分、及格率、优秀率等。 　　传统的数据分析方法是：在excel电子表格中，运用它的筛选功能将各班筛选出来，再复制、粘贴，然后分别计算，或者运用数据透视表。但是这些方法存在的缺点是： 　　1.在班级较多的情况下，分析数据的工作是很繁琐的。 　　2.分析完毕后，如果要对数据源表有修改，那么刚才的分析工作就会推翻重新来，也就是无法动态管理分析表。 　　3.各校对成绩的使用有不同的要求，如：对有些学生的成绩是不计入教师考核的，但这部分学生的成绩又不能删除掉，所以分析起来就比较麻烦。 　　本文旨在运用average函数的数组功能克服上述缺点实现对数据的动态管理，进一步提高工作效率。 二、AVERAGE函数特殊用法的尝试 　　对excel函数功能有一定了解的人士对sum函数的使用不陌生，Sum函数的作用本来是对某些单元格区域求和，但是，当我们把它和if函数结合在一起使用的时候就成为了数组函数——计数函数，它可以把单元格中符合条件的数据个数统计出来。如在一张10个班级混合编场登统后的成绩表中，为了统计出一次考试中各班的及格人数会使用数组公式 　　{=sum(if(等统表!e$2:e$500=c$18,if(登统表!$c$2:$c$500=$m4,1)))} 按“Ctrl+Shift+Enter”确认。 图一 　　Average函数的作用是取平均值，通过上面sum(if())函数计数功能的使用，给了我们很大的启示，如果将sum(If())函数的计数功能作一个简单的类比，把average()函数改为Average(if())能否用来求各班各科目的平均分呢？经笔者长达半个多月的反复验证、修改终于获得成功，使用方法如下： 　工作实例2. 首先建立下面的平均分统计表，只要在C4单元格中输入数组公式{=average(if(录分表!$C$2:$C$496=$M4,录分表!E$2:E$496))}，按“Ctrl+Shift+Enter”确认，再向下、向右进行拖动即可完成班级平均分数的统计，这样只要录分表中的数据发生变化，班级平均分也会相应的发生改变，从而真正实现对班级平均分的动态管理。 AVERAGE(if())函数功能的进一步挖掘 　　各学校对教师所任学科成绩的考核都有自己的量化办法，一般说来也就是采用对教师任教的所有班级的平均分、及格率、优秀率测算来考核。上面我们解决了单班级均分问题，现在我们要面临的是一个教师少则教两个班，多则三个或四个班等，怎样计算两个班、三个班甚至四个班、五个班的平均分问题呢？当然我们可以使用excel的高级筛选功能将符合条件的班级筛选出来，然后再分别计算。但是，同样无法动态管理数据。我们能不能对上面的公式加以改进呢？也就是说在公式中是否可以添加更多的限制条件的问题，经过笔者多次实战演练得出的结论是可行的。下面介绍它的使用方法：如下图，这是计算两个班级的平均分，{=average(if(录分表!$c$2:$c$494 =1,录分表！E$2:E$494,if(录分表!$C$2:$C$494=2，E$2:E$494)))} ,按“Ctrl+Shift+En”确认。 对于多班级的平均分就是对上面公式中再添加条件即可。如下图四，是四个班级的平均分（表格中有公式显示）。这些数据都是动态显示的，是随着录分表中的数据变化而变化的。对于个别缺考学生（或不计入教师考核的学生）的成绩的计算，我们只需在录分表的班级列中将班级序号拖放在最后一列即可。 下图是四个班的平均