巧用Excel函数实现学生成绩统计及查询

摘要：电子表格软件Excel具有强大的数据处理和分析功能，其中函数是Excel数据计算和处理的核心工具。该文利用Excel中的不同函数功能，实现对学生成绩的统计、课程分析及信息查询。

关键词：Excel；函数；公式

中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)01-0209-02

Score Statistics and Query Using Excel Functions

YU Ning

(Department of Computer Science of College of Arts and Science of Beijing Union University, Beijing 100190, China)

Abstract: Excel spreadsheet software has powerful data processing and analysis capabilities, and the Excel function is the core tool for data calculating and processing. In this paper, score statistics, curriculum analysis and information inquiries were achieved with different Excel functions.

Key words: Excel; function; formula

在学校的日常教学管理中，经常遇到需要汇总、统计或查询学生学期成绩的问题，借助Microsoft Office系列办公软件中的电子表格Excel，可以帮助我们方便、快速地完成数据表中的统计、分析，特别是灵活地运用Excel中的公式和函数，不仅能提高工作效率，还能帮助我们完成一些特殊的功能。Excel中的函数是预先定义的公式，可以完成某种特殊功能或操作[1]。Excel提供了300余种函数，借助这些函数，我们不必掌握VBA或其他专业编程语言，也可以完成日常事物处理，满足用户的需求。

下面就以一张某专业10名学生成绩为例，使用函数完成对学生成绩进行统计、分析，并设计一个学生成绩查询，即根据输入的学生姓名或学号，可查询出该学生的成绩信息。

1 成绩统计及课程分析

首先在图1所示的学生成绩统计A3:I12区域中，输入了资源环境与城乡规划管理专业10名学生的学期考试成绩（仅以此专业部分学生的成绩统计为例）。然后分别在J3单元格中创建公式=SUM(E3:I3)，在k3单元格中创建公式=ROUND(AVERAGE(E3:I3),1), 在L3单元格中创建公式= =IF(K3>=90,"优秀",IF(K3>=80,"良好",IF(K3>=70,"中等",IF(K3>=60,"及格","不及格"))))，在M3单元格中创建公式=RANK(K3,K$3:K$12)，计算高杉同学的成绩总分、平均分、成绩等级和成绩排名，再分别通过拖动J3、K3、L3和M3单元格中的填充柄填充到J4:J12、K4:K12、L4:L12、M4:M12区域中，以计算其他学生的成绩总分、平均分、成绩等级和成绩排名。

在学生统计计算公式中使用了求和SUM、求均值AVERAGE、四舍五入ROUNF、条件判断IF、计算排名RANK等函数。

完成上述成绩统计后，在成绩分析A16:G30区域中，对5门课程成绩进行分析。如图2所示。

分别在B18单元格中创建公式=MAX(E3:E12)，在B19单元格中创建公式=MIN(E3:E12)，在B20单元格中创建公式=ROUND(AVERAGE(E3:E12),1)，在B21单元格中创建公式=ROUND(STDEV(E3:E12),2)，在B22单元格中创建公式=COUNTIF(E3:E12,">=60")，在B23单元格中创建公式=B22/COUNT(E3:E12)，分别统计分析高等数学这门课程的学生成绩最高分、最低分、平均分、标准差、及格人数和及格率。在B25:B29单元区域中通过创建一个数组公式{=FREQUENCY(E3:E12,$A$25:$A$28)}，统计出学生高等数学成绩在0-59,60-69,70-79,80-89以及90分以上5个区间段内的数目。完成高等数学课程成绩分析后，再分别通过拖动B18、B19、B20、B21、B22和B23单元格中的填充柄填充到C18:F18、C19:F19、C20:F20、C21:F21、C22:F22和C23:F23区域中，以计算其他4门课程的最高分、最低分、平均分、标准差、及格人数和及格率。拖动B25:B29单元区域中的填充柄填充到C25:F29区域，计算出其他4门课程成绩分布。在成绩分析计算公式中使用了最大值MAX、最小值MIN、标准偏差STDEV、统计满足条件的单元格个数COUNTIF、计数COUNT、频率函数FREQUENCY等函数。

2 学生成绩查询

在图3所示的成绩查询表中，由输入单元格A3、输出区域A10:M11和提示信息单元格A12组成。若在A3单元格中输入欲查学生的姓名或学号，则在A10:M11区域中自动填充该学生的成绩信息。如果输入正确的学生姓名或学号，在A10:M11区域不仅会显示查询结果，而且在单元格A12中还会显示“对这个结果还满意吧！”。如果没有输入任何内容或输入错误的姓名(如没有此学生、姓名有误等)或学号(如超出学号范围)，则在A10:M11区域中显示一行“？”，而且在单元格A12中会显示提示信息“对不起，没有找到这名学生”。

所谓“自动识别”，就是如果满足情况1的条件，就执行命令1；如果是情况2的条件，就执行命令2……所以，解决这个问题显然是通过条件判断函数IF来完成的。即：

如果A3中输入的是一个正确的学生姓名或是2009010305201～ 2009010305210之间的任一学号,则显示正确的查找值。如图3、图4所示。

如果A3中输入的是错误的姓名、其它的汉字和字符，或输入不在学号范围内的数字，则显示一行“？”。如图5所示。

实现上述功能的方法是：创建一个公式，公式最外层通过IF函数判断A3单元格中的内容是否符合条件。里层首先借助ISERROR函数[2]（若测试值为任何错误值，如：#N/A、#VALUE、#NAME、#REF、#DIV/0等，则返回True）利用它与HLOOKUP按行查找函数（此函数有四个参数，它是对在第二个参数给定的数据区域的最上面的一行中查找与第一个参数给定的值，然后将返回第三个参数指定行的值，第四个参数指定查找是精确匹配还是近似匹配）的组合便可以判断A3单元格中输入的数据是否是正确的。其函数返回值为TRUE，则证明是错误的输入；若为FALSE，则证明是正确的输入。同时又用到MATCH函数（返回在指定方式下与指定数值匹配的数组中元素的相应位置），找到与A3单元格中输入的姓名或学号相匹配的元素所在位置。

最后完整的操作是：选定A11：M11区域，在编辑栏中输入公式=IF(ISERROR(HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0)),"?",HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0))。由于该公式一次性能返回一行的信息，是一个数组公式的形式。因此，在输入完上述公式后，需按组合键Ctrl+Shift+Enter确认，这时在输入公式的两端自动加上了大括号“{ }”，相应的计算结果显示在A11：M11区域中。

该公式最外层是的IF函数，是通过“ISERROR(HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0)),”判断条件式的真、假。若条件式为真，表明通过HLOOKUP函数未查找到与A3单元格相匹配的元素，则在A11：M11区域中返回一串“？”。其中HLOOKUP函数的第三个参数指定行又是由一个IF函数实现的。否则，通过“HLOOKUP(成绩统计及分析!A3:M3,成绩统计及分析!A3:M12,IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),MATCH(A3,成绩统计及分析!B3:B12,0),MATCH(A3,成绩统计及分析!C3:C12,0)),0))”查找到的与A3单元格中的输入值相匹配的元素所在行的信息。

此外，针对查询结果，在A12单元格会给出提示信息，其公式是=IF(ISERROR(MATCH(A3,成绩统计及分析!B3:B12,0)),IF(ISERROR(MATCH(A3,成绩统计及分析!C3:C12,0)),"对不起!没有找到这名学生","这个结果还满意吧！"),"对这个结果还满意吧！")。该公式通过两层嵌套的IF函数，判断若在成绩统计及分析表中的B3:B12区域或C3:C12区域中找到与成绩查询表中A3单元格所输入的学生姓名或学号，则显示提示信息“这个结果还满意吧！”。否则显示信息“对不起!没有找到这名学生”。

学会使用Excel函数真的很有趣，它可以帮助你做很多事。

参考文献：

[1] 谢柏青,张健青,刘新元.Excel教程[M].2版.北京:电子工业出版社,2003:30.

[2] 曾苗苗.Excel函数、图表与数据分析典型实例[M].北京:科学出版社,2009:73-127.