条件格式和函数在学生成绩统计与查询中的应用

黎菁华

(中南大学商学院，长沙410083)

1 成绩表的建立与转化

1.1 导入学生成绩原始数据并转化其类型

图1是2010级工商管理一班(30人)第一学期成绩原始数据的一部分，首先利用Excel的函数value()，将原文本型的数字转化为纯数字。如在P4单元格中输入=value(D4)，其他类推，再把整个数字区域复制到D4:K33，因学号位数较多，同一个班的学生，学号除了最后两位数不同外，其余数字均相同，所以，可用excel的单元格自定义功能进行设置。选中A4:A33，右击鼠标，点击设置单元格格式，选择单元格格式自定义对话框，在其中输入“20101008”00，再在A4单元格中输入1，在A5单元格中输入2，利用填充柄完成A6:A33单元格，即刻显示完整的学号，如图1所示:

图1 学生成绩原始数据Fig.1 Data of student achievement

1.2 应用条件格式对分数进行相关设置

选取D4:K33区域，设置条件格式:≥90的字体为蓝色，＜60的字体为红色，介于60～90的字体为绿色。如图2所示:

图2 条件格式设置Fig.2 Conditional formatting

2 成绩统计与分析

2.1 使用sum()和rank()函数进行总分的统计与排名

在L4单元格输入公式“=sum(D4:K4)”，并填充至L33，完成“总分”的计算。选中M4单元格，单击编辑栏上“fx”，选取rank函数，在函数参数对话框中输入number为“L4”，ref为“$L$4:$L$33”(使用绝对地址)。将 M4自动填充至M33，完成“排名”列的计算。Rank函数参数图形界面如图3所示:

图3 Rank函数参数设置Fig.3 Parameters setting of the Rank function

使用条件格式和sum()、rank()函数后的样式如图4所示:

图4 使用条件格式和sum()、rank()函数后的效果图Fig.4 Effect of using conditional formatting and sum()，rank()function of the renderings

2.2 应用 Countif、Sumif、Max()、Min()、Average()函数对成绩表进行统计分析

应用countif函数计算男生人数，利用sumif函数和己经求得的男生人数，可计算男生总分的平均分。同样的方法，可以计算女生人数和女生的平均分。使用countif函数还可完成各分数段人数的统计。利用max函数统计各科(或总分)最高分，利用min函数统计各科(或总分)最低分，应用Average函数统计平均分，联合运用sumif和countif函数可完成各科男生平均分和女生平均分的统计。具体步骤如下:

在C39单元格，输入=Max(D4:D33)，填充到 J39，完成全班各科最高分的统计。

在C40单元格，输入=Min(D4:D33)，填充到J40，完成全班各科最低分的统计。

在C41单元格，输入=Average(D4:D33)，再填充到J41，完成各科全班平均分的统计。

在C42单元格，输入 =SUMIF(C4:C33，“男”，D4:D33)/COUNTIF(C4:C33，“男”)，填充到 J42，完成各科男生平均分的统计。

在C43单元格，输入 =SUMIF(C4:C33，“女”，D4:D33)/COUNTIF(C4:C33，“女”)，填充到 J43，完成各科女生平均分的统计。

在C44单元格，输入 =COUNTIF(D4:D33，“＞=90”)，填充到J44，完成90分以上的分数段人数统计。

在C45单元格，输入 =COUNTIF(D4:D33，“＞=80”)－COUNTIF(D4:D33，“＞ =90”)，填充到 J45，完成各科在80～90分(不含90)的人数统计。

在C46单元格，输入 =COUNTIF(D4:D33，“＞=70”)－COUNTIF(D4:D33，“＞ =80”)，填充到 J46，完成各科在70～80分(不含90)的人数统计。

在C47单元格，输入 =COUNTIF(D4:D33，“＞=60”)－COUNTIF(D4:D33，“＞ =70”)，填充到 J47，完成各科在60～70分(不含90)的人数统计。

在 C48 单元格，输入 =COUNTIF(D4:D33，“＜60”)，填充到J48，完成各科60分以下人数统计。成绩分布如图5所示:

图5 成绩统计分析表Fig.5 Analysis of performance statistics

3 成绩查询界面的设计

运用VLOOKUP()函数制作成绩查询界面。

VLOOKUP()函数的功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值［1］。

这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号:数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、第3列……假定某数组区域为A4:M33，那么 A4:A33为第1列、B4:B33为第2列……

语法:

VLOOKUP(查找值，区域，列序号，逻辑值)

“查找值”:为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。“区域”:数组所在的区域，如“A4:M33”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。

“列序号”:即希望区域(数组)中待返回的匹配值的列序号，为1时，返回第一列中的数值。为2时，返回第二列中的数值，以此类推;若列序号小于1，函数VLOOKUP返回错误值 #VALUE!;如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。

“逻辑值”:为TRUE或FALSE。它指明函数 VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值;如果“逻辑值”为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为FALSE。另外如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP返回错误值 #N/A。如果函数 VLOOKUP找不到“查找值”且“逻辑值”为FALSE，函数 VLOOKUP返回错误值 #N/A。

成绩查询功能在于输入任意学生的座号，即可查找出该学生的姓名、各科成绩、总分以及班级排名等详细信息。

对单元格P4做同样的设置(在P4单元格中，右键点击—设置单元格格式—自定义—“2010008”00，学生座号定义后，要查询某位学生成绩与排名，只需在P4单元格输入该生学号的最后两位数字即可)，在单元格P6中输入=VLOOKUP($P$4，$A$4:$M$33，2)，在单元格 P7 中输入 =VLOOKUP($P$4，$A$4:$M$33，4)，在 P8 单元格中输入 =VLOOKUP($P$4，$A$4:$M$33，5)，在单元格P9中输入=VLOOKUP($P$4，$A$4:$M$33，6)，在单元格P10中输入 =VLOOKUP($P$4，$A$4:$M$33，7)，在单元格P11中输入 =VLOOKUP($P$4，$A$4:$M$33，8)，在单元格 P11 中输入 =VLOOKUP($P$4，$A$4:$M$33，8)，在单元格 P12中输入 =VLOOKUP($P$4，$A$4:$M$33，9)，在单元格P13中输入=VLOOKUP($P$4，$A$4:$M$33，10)，在单元格P14中输入=VLOOKUP($P$4，$A$4:$M$33，11)，在单元格 P15中输入 =VLOOKUP($P$4，$A$4:$M$33，12)，在单元格 P16 中输入 =VLOOKUP($P$4，$A$4:$M$33，13)，如果此时在P4单元格中输入30即学号为201000830的学生，则可立即查到该学生的姓名、各科成绩及排名情况，如图6所示:

4 结论

Excel函数具有强大的数据处理能力，如果能善加利用Excel的条件格式和函数参数，则会给我们的工作带来极大便利，达到事半功倍之效，利用Excel的条件格式和函数参数进行成绩统计、查询与奖学金的评定，操作简便、准确可靠、易于修改，高效可行。

图6 成绩查询界面Fig.6 Interface of query results

［1］杜茂康.Excel与数据处理(第2版)［M］.北京:电子工业出版社，2006.