excel最常用的八个函数_EXCEL函数在成绩分析中的全面应用
学校每学期都有各类考试，以此来分析学情。
考后成绩的细致分析工作十分重要，不仅反映了学生近段时间的学习情况，也为发放学生奖学金和考核任课教师的教学效果提供了重要依据。
特定条件的平均分计算 1．AVERAGE与IF结合解决0值问题 我们都知道，计算平均分一般使用AVERAGE函数就可以实现，如果考生参加考试考分为0，单独使用AVERAGE函数计算平均分时0值将参与计算，影响班级平均分值。
当不需要计入0值即排除0分计算全班平均分时，结合AVERAGE函数与IF条件语句构建数组公式就可以实现。
假设工作表中某班语文成绩在D列区域为D3:D88，则数组公式为：{=AVERAGE(IF((D$3:D$88)>0,D$3:D$88))}。
公式说明：利用IF语句判断区域大于0值数据，条件为真时返回相应区域数值，条件为假时则返回逻辑值FALSE，最后利用AVERAGE函数对生成的数组进行平均值计算。
注意逻辑值FALSE不参与平均值计算，数组公式中的一对大括号不能手工输入，必须通过按“Ctrl+Shift+Enter”组合键实现输入。
公式中使用混合引用实现向右自动填充，从而完成其他列科目平均分计算，增加了公式的灵活性。
2. AVERAGE与OFFSET和COUNTA函数结合解决排除“最后5名”考生成绩问题 由于班级排名最后5位学生的成绩对班级整体的平均分影响较大，就是俗话说的“拖班级后腿”，有时我们需要排除此种因素，只计算去掉排名最后的5位学生各科成绩后的班级平均分。
逐一删除各班最后5名考生成绩操作比较麻烦，结合AVERAGE、OFFSET和COUNTA函数就可以保证在全班考生成绩完整的情况下计算出不含“最后5名”的平均分。
假设某班成绩表已经按总分从高到低作降序排列，姓名在C列区域为C3:C88，语文成绩仍在D列区域为D3:D88，则公式为：=AVERAGE(OFFSET(D$3,,,(COUNTA($C$3:$C$88)—5),))。
公式说明：COUNTA函数计算姓名列不为空值的个数即全班考生总人数，再减5（去掉最后5名），利用OFFSET函数以D3单元格为基点引用除最后5名学生的语文成绩的区域即D3:D83，最后利用AVERAGE函数计算该区域的平均分即不含“最后5名”考生的平均分。
公式中姓名列使用绝对引用，同样，D$3单元格使用混合引用，使公式可以向右自动填充完成其他科目成绩的同样计算。
当然，根据实际情况也不仅仅只限最后5名，只需对公式稍作修改即可完成。
及格率与优秀率计算 1.一般情况下的及格率和优秀率计算标准及方法 及格率的计算方法是：满分为150分的学科成绩大于等于90分为及格，满分为100分的学科成绩大于等于60分为及格。
优秀率的计算方法是：满分为150分的学科成绩大于等于120分为优秀，满分为100分的学科成绩大于等于80分为优秀。
一般情况下的及格率和优秀率结合COUNTIF函数和COUNTA函数就可以完成计算。
同上，仍以计算语文学科及格率为例，及格率公式为：=COUNTIF(D3:D88,">=90")/COUNTA(D3:D88)，其他科目的及格率和优秀率同理，只需更改公式中的计算条件即可。
另外，使用百分比显示结果，请设置“单元格格式”（数字→分类→百分比），其他不再赘言。
2.不含“最后5名”考生的及格率与优秀率 在计算不含“最后5名”考生的及格率与优秀率时，可以使用SUM函数结合OFFSET函数和COUNTA函数构建数组公式，同上，仍以计算语文学科为例，及格率数组公式为：{=SUM(——(OFFSET(D$3,,,(COUNTA($C$3:$C$88)—5),)>=90))/(COUNTA($C$3:$C$88)—5)}。
公式说明：公式分子部分是先通过OFFSET函数引用一个除最后5名学生的语文成绩区域（D3:D83），用此区域比较出一个大于等于90的数组，该数组是一个逻辑值数组，用“—”负号进行两次数学运算，使逻辑值变为数值（TRUE为1，FLASE为0）。
排名及前N名人数统计 1.使用RANK函数进行多班级间的成绩排名 一般我们把一个班级的成绩单独存放在一个工作表中，而把一个年级的所有班级成绩保存在一个工作薄中。
这里为了清晰方便地使用公式引用计算，我们最好把表名修改为与班级对应的阿拉伯数字，如工作表名“1“存放高一（1）班的成绩。
例如，高一年级有14个班，则需要建立14个工作表，这里需要注意，要确保14个班的成绩存放在连续的工作表中即表名依次从1到14，且每个工作表中的各科成绩及总分列都在相同区域，这样才可以使用“引用运算符”，按工作表的排列顺序将起始表名和终止表名进行连接，从而实现多班级间总分排名。
比如我们需要统计1~7班考生总分在这7个班级中的排序情况，假设7个工作表中每班总分都在M列且区域为M3:M88，我们可以在不合并这7个工作表的情况下对总分进行排名，数组公式为：{=RANK(M3,"1:7"!$M$3:$M$88)}。
另外，EXCEL有一个隐藏功能，可以很方便地生成对除当前工作表之外的左右两侧多个工作表相应区域的引用，而不用考虑所引用的工作表是否连续。
以上述公式为例，公式修改后为：{=RANK(M3,"*"!$M$3:$M$88)}。
2. 结合RANK、COUNTIF、INDIRECT函数完成前N名人数统计 假如上述公式{=RANK(M3,"1:7"!$M$3:$M$88)}的计算结果都在7个班级工作表中的P3:P88区域，如图1所示。
图1 进一步计算各班在7个班中总排名中进入前200名的人数分布，使用COUNTIF函数和INDIRECT函数可以完成，在此使用辅助单元格（$A$8）来引用名次所在列区域(如上图所示：$P$3:$P$88)。
以计算1班为例，公式为：=COUNTIF(INDIRECT(C$3&"!"&$A$8)," 图3 4.各班单科优秀生进入四校前N名人数统计 在分析完四校的总体情况后，我们还要对本校某年级各班各科进入四校前N名人数做进一步详细地分析。
如图4所示，高二文科分数（以“/”划分文理）为四校第100名分数，理科为四校第300名分数，人数为各班超过相应分数的人数。
以计算1班总分进入人数为例，单元格C3中公式为：——IF(ISNUMBER(C$2),COUNTIF(INDIRECT(C$2&"!"&$Q2), ">="&——RIGHT($B4,LEN($B4)—FIND("/",$B4))),COUNTIF(INDIRECT(C$2&"!"&$Q2),">="&——LEFT($B4,FIND("/",$B4)—1)))。
公式说明：①图中B列总分、语文、数学、英语的文理成绩以“/”划分，格式是文本，取“/”左右的数值需要用到LEFT和RIGHT函数，定位“/”在字符串中位置需使用FIND函数，取左（文科）还是取右（理科）分数需要知道该班级是理科班还是文科班，这里有个技巧，表中第2行标明“班”的为理科班，未标明“班”的为文科班。
理科班实为自定义的数值（例如1班―3班和9班、10班），文科班为文本型的数字（4、5、6、7、8），文本型数字输入时先输入一个单引号再输入数字。
如此一来，使用IF条件判断语句和ISNUMBER函数就可以依据班级单元格格式自动判断文理，一旦班级文理发生变化只需更改数值类型即可，方法简单、公式灵活，省去反复修改公式这一烦琐工作，增强了公式的智能性和通用性。
②单元格有阴影部分为理科物理、化学、生物，没有“/”分隔，则C7单元格的公式可以简化为：=——IF(ISTEXT(C$2),0,COUNTIF(INDIRECT(C$2&"!"&$Q5),">="&$B7))。
同理，可以计算出其他科目。
③合计各科总人数时也要首先判断文理，这里主要使用SUM 函数、IF语句和ISNUMBER函数完成。
例如，N3单元格内计算理科总分进入总人数数组公式为：{=SUM(——IF(ISNUMBER($C$2:$L$2),$C3:$L3))}，而M3单元格内计算文科总分进入总人数公式则可以为：{=SUM($C3:$L3)—$N3}，显示结果仍是自定义数字格式以标明文理。
如果不想显示计算结果0值，在“工具”→“选项”→“视图”中取消 “零值”对勾。
5.第N名成绩计算 临近高考，对全市高三“第一次大练习”成绩分析更为详细，其中有一项就是文理各科全市第N名成绩计算。
如图5所示，单元格B3内的公式为：{=INDEX(文科!$D:$J,MAX(((文科!L$2:L$20000)=(MAX((文科!L$2:L$200000,"已获年级奖",SUM(N(IF($A$2:$A$1000=A787,$C$2:$C$1000,0)>C787))+1)}。
公式说明：在全年级中进行分班计算班级名次是关键，公式使用SUM函数、N函数和IF函数构建数组公式，公式通过IF判断出是否属于同一个班级，然后返回同一班级的所有总分成绩的数组，把数组与相对应的总分列数组进行比较产生一个逻辑值数组，用N函数转换该逻辑值数组，最后用SUM函数求和后加1就得出了班级名次，最后通过最外层的IF判断语句判断出是否获得年级奖。
该公式运算灵活，可对名次列进行升序或降序排列而不会出现易失性等错误。
年级奖学金和班级奖学金的计算方法一样，关键是使用LOOKUP根据名次引用定义名称中的金额，在此不再赘言。
（作者单位：河南洛阳市第一中学）。