论文部分内容阅读
Microsoft Office中的Excel是功能丰富，数据处理功能强大的表格软件。利用Excel管理、组织和处理成绩，将成绩生成电子表格、图表和进行数据的统计分析，方便快捷，省时省力，是办公自动化的好助手，常被学校和教师用来处理学生成绩。然而，由于学校每学期上课的班级、专业、课程及班级人数经常变化，因此每学期结束时对所教课程的成绩统计与分析都需要重新操作，这就降低了我们的工作效率。本文介绍了用Excel实现成绩采集、加工、统计的自动化，并通过每个环节的优化，探讨提高成绩管理效率的途径和方法。
一、表格的制作
新建一个工作簿文件，包含四张表格，分别命名为“基本信息”“百分制成绩表”“学分制成绩表”“成绩分析”。
1.“基本信息”表
“基本信息”表主要包括以下内容：
（1）初始信息：包括学校名称、专业、班级、学期、课程名称、任课教师及课程学分等内容。这部分信息主要用于输入“成绩表”的表头的内容。
（2）各项成绩在总评中所占比例：这部分信息用于统计总评成绩。因课程的不同，各项的比例也会变化。
（3）成绩分数段点：这部分信息主要用于“成绩分析”表中统计各分数段的人数时的参数。
（4）学生基本信息字段：包括学号、姓名及学科。
2. “百分制成绩”表
（1）表头的制作。成绩表的格式可根据各学校所需样式进行设计，在设计的过程中将每个变化的部分空一个单元格。比如在A4单元格中输入“专业 ：”，则在空着的B4单元格中输入“=IF(基本信息!D1="","",基本信息!D1)”；“基本信息”表的D1单元格是专业名称的内容。只要在“基本信息”表的D1单元格中输入了专业名称如“针灸推拿”，那么这个专业名称就会在“成绩表”的相应位置显示。插入一个IF函数的目的是：当“基本信息”表的专业名称未输入时，“百分制成绩”表的相应单元格显示为空格，而不是显示为“0”。其他信息的内容如学年、学期、班级、学号、姓名等用同样的方法制作。
（2）成绩的自动生成。在自动生成成绩时，要插入一个IF函数。如在第一个学生的第一次测验结果所在的单元格中输入“=IF(ISBLANK(基本信息!C6),"",基本信息!C6)”（基本信息!C6中存放的是第一个学生第一次测验的结果）。然后将这个函数复制到所有考试项目并复制60行。如果不插入IF函数，而是直接使用“=基本信息！C6”；当在“基本信息”表中输入成绩过程中某个学生没参加考试却被误输入其他学生的成绩，在将这个单元格内容清除后，目标单元格结果将显示“0”，这将会影响该生的总评成绩。
在计算总评时，在第一个学生的总评单元格中输入“=IF(基本信息!K9="","",AVERAGE(C9:F9)*基本信息!P$2 AVERAGE(G9:J9)*基本信息!P$3 J9*基本信息!P$4 K9*基本信息!P$5)”。在这个公式中，各项成绩的比例以“基本信息”表中我们输入的数据为依据。在这个公式中插入IF函数的目的是只有在输入了期末考试的成绩后，才计算总评成绩。如果不插入IF函数，在未输入期末考试成绩之前，目标单元格都会显示错误信息。
学分的计算：在第一名学生的学分单元格中输入“=IF(L6="","",IF(L6>=60,基本信息!F$2,0))”。这个函数的作用是如果总评栏没有成绩显示空格，如果有成绩并且及格显示基本信息表F$2单元格中输入的学分值，不及格则显示“0”。引用的基本信息表F$2单元格要使用绝对引用。
在备注栏内可填入成绩排名，如果排名，则在第一名学生的备注栏内输入 “=IF(L6="","",RANK(L6,L$6:L$54)”。其中数字列表的引用要使用绝对引用。
3.“学分制成绩” 表
（1） 表头的制作。与“百分制成绩”表格式一样，可同时制作。
（2）成绩的生成。学分制成绩即用优、良、中、及格、不及格来表示学生的成绩，因此我们只要使用IF函数将百分制成绩表中的成绩转化一下就行了。在“学分制成绩”表的C6单元格输入“=IF(ISBLANK(基本信息!C6),"",IF(基本信息!C6>=90,"优",IF(基本信息!C6>=80,"良",IF(基本信息!C6>=70,"中",IF(基本信息!C6>=60,"及格",IF(基本信息!C6>=0,"不及格",))))))”。这个函数的意思是：如果“百分制成绩表”C6不为空且不小于90显示“优”，不小于80显示“良”，不小于70显示“中”，不小于60显示“及格”，小于60显示“不及格”，如果为空则显示空格。
4.“成绩分析”表
（1）最高分、最低分等的生成。在C6单元格中输入“=IF(SUM(基本信息!C6:C55)=0,"",MAX(基本信息!C6:C55))”。此函数的作用是如果有成绩则统计最高分，如果没有成绩则显示空格。再用“MIN、AVERAGE”统计出最低分与平均成绩。统计“不及格率”时可在C10单元格中输入“=IF(SUM(基本信息!C6:C55)=0,"",COUNTIF(基本信息!C6:C55,"< 60")/COUNT(基本信息!C6:C55)*100%)”。函数“COUNTIF”用于统计不及格人数。
（2）各分数段人数分布的生成。分数段的设置要与“基本信息”表中分段点的设置一致，分数段的行数要比分段点的行数多一行。在“成绩分析”表的C12单元格中输入“=IF(SUM(基本信息!C6:C55)=0,"",FREQUENCY(基本信息!C6:C55,基本信息!$M2:$M10))”。此函数的作用：如果“基本信息”的C6:C55单元格中没有数据，显示空格，如果有数据，则按照“基本信息”的$M2:$M10单元格中设置的分段点返回各分数段的频率分布；函数输入完成后，选中C12：C21单元格，按“F2”键后再按组合键“Ctrl Shift Enter”，则各分数段的频率分布自动生成。
（3）成绩分布图的生成。先选中要生成图表的数据，再按照图表向导的提示，即可生成相应考试项目的图表。
二、表格的格式化
1.页面设置
通过“页面设置”菜单进行纸张大小、页边距的设置；在学生人数较多需多页打印时，可通过“工作表”标签设置顶端标题，使标题在每一页打印，在顶端标题行中输入“$1:$5”。
2.设置行高和列宽
适当调整列宽，使各列均能在一页中打印；再适当调整行高，使每页显示20或25名学生的成绩。
3.突出显示不及格成绩
选中所有成绩区数据，执行“格式”菜单中“条件格式”命令，将不及格成绩显示为红色或所在单元格加底纹（有彩色打印机时设置为红色，普通打印机时设置底纹）。
4.设置字体和字号
根据需要将不同的内容设置不同的字体，再根据单元格的大小调整各自的字号。
5.设置边框
选择“格式/单元格”菜单中的“边框”标签，用不同的线条设置内外框线。
最后生成某校的成绩分析表（如下图）。
通过以上方法，在每次新课结束后，只要输入了原始数据，成绩表及成绩分析表将自动生成如果有新的分析指标需要，只要做适当调整，就可满足要求。这样就基本达到一劳永逸了。
（作者单位：江苏省海安针灸推拿学校）
一、表格的制作
新建一个工作簿文件，包含四张表格，分别命名为“基本信息”“百分制成绩表”“学分制成绩表”“成绩分析”。
1.“基本信息”表
“基本信息”表主要包括以下内容：
（1）初始信息：包括学校名称、专业、班级、学期、课程名称、任课教师及课程学分等内容。这部分信息主要用于输入“成绩表”的表头的内容。
（2）各项成绩在总评中所占比例：这部分信息用于统计总评成绩。因课程的不同，各项的比例也会变化。
（3）成绩分数段点：这部分信息主要用于“成绩分析”表中统计各分数段的人数时的参数。
（4）学生基本信息字段：包括学号、姓名及学科。
2. “百分制成绩”表
（1）表头的制作。成绩表的格式可根据各学校所需样式进行设计，在设计的过程中将每个变化的部分空一个单元格。比如在A4单元格中输入“专业 ：”，则在空着的B4单元格中输入“=IF(基本信息!D1="","",基本信息!D1)”；“基本信息”表的D1单元格是专业名称的内容。只要在“基本信息”表的D1单元格中输入了专业名称如“针灸推拿”，那么这个专业名称就会在“成绩表”的相应位置显示。插入一个IF函数的目的是：当“基本信息”表的专业名称未输入时，“百分制成绩”表的相应单元格显示为空格，而不是显示为“0”。其他信息的内容如学年、学期、班级、学号、姓名等用同样的方法制作。
（2）成绩的自动生成。在自动生成成绩时，要插入一个IF函数。如在第一个学生的第一次测验结果所在的单元格中输入“=IF(ISBLANK(基本信息!C6),"",基本信息!C6)”（基本信息!C6中存放的是第一个学生第一次测验的结果）。然后将这个函数复制到所有考试项目并复制60行。如果不插入IF函数，而是直接使用“=基本信息！C6”；当在“基本信息”表中输入成绩过程中某个学生没参加考试却被误输入其他学生的成绩，在将这个单元格内容清除后，目标单元格结果将显示“0”，这将会影响该生的总评成绩。
在计算总评时，在第一个学生的总评单元格中输入“=IF(基本信息!K9="","",AVERAGE(C9:F9)*基本信息!P$2 AVERAGE(G9:J9)*基本信息!P$3 J9*基本信息!P$4 K9*基本信息!P$5)”。在这个公式中，各项成绩的比例以“基本信息”表中我们输入的数据为依据。在这个公式中插入IF函数的目的是只有在输入了期末考试的成绩后，才计算总评成绩。如果不插入IF函数，在未输入期末考试成绩之前，目标单元格都会显示错误信息。
学分的计算：在第一名学生的学分单元格中输入“=IF(L6="","",IF(L6>=60,基本信息!F$2,0))”。这个函数的作用是如果总评栏没有成绩显示空格，如果有成绩并且及格显示基本信息表F$2单元格中输入的学分值，不及格则显示“0”。引用的基本信息表F$2单元格要使用绝对引用。
在备注栏内可填入成绩排名，如果排名，则在第一名学生的备注栏内输入 “=IF(L6="","",RANK(L6,L$6:L$54)”。其中数字列表的引用要使用绝对引用。
3.“学分制成绩” 表
（1） 表头的制作。与“百分制成绩”表格式一样，可同时制作。
（2）成绩的生成。学分制成绩即用优、良、中、及格、不及格来表示学生的成绩，因此我们只要使用IF函数将百分制成绩表中的成绩转化一下就行了。在“学分制成绩”表的C6单元格输入“=IF(ISBLANK(基本信息!C6),"",IF(基本信息!C6>=90,"优",IF(基本信息!C6>=80,"良",IF(基本信息!C6>=70,"中",IF(基本信息!C6>=60,"及格",IF(基本信息!C6>=0,"不及格",))))))”。这个函数的意思是：如果“百分制成绩表”C6不为空且不小于90显示“优”，不小于80显示“良”，不小于70显示“中”，不小于60显示“及格”，小于60显示“不及格”，如果为空则显示空格。
4.“成绩分析”表
（1）最高分、最低分等的生成。在C6单元格中输入“=IF(SUM(基本信息!C6:C55)=0,"",MAX(基本信息!C6:C55))”。此函数的作用是如果有成绩则统计最高分，如果没有成绩则显示空格。再用“MIN、AVERAGE”统计出最低分与平均成绩。统计“不及格率”时可在C10单元格中输入“=IF(SUM(基本信息!C6:C55)=0,"",COUNTIF(基本信息!C6:C55,"< 60")/COUNT(基本信息!C6:C55)*100%)”。函数“COUNTIF”用于统计不及格人数。
（2）各分数段人数分布的生成。分数段的设置要与“基本信息”表中分段点的设置一致，分数段的行数要比分段点的行数多一行。在“成绩分析”表的C12单元格中输入“=IF(SUM(基本信息!C6:C55)=0,"",FREQUENCY(基本信息!C6:C55,基本信息!$M2:$M10))”。此函数的作用：如果“基本信息”的C6:C55单元格中没有数据，显示空格，如果有数据，则按照“基本信息”的$M2:$M10单元格中设置的分段点返回各分数段的频率分布；函数输入完成后，选中C12：C21单元格，按“F2”键后再按组合键“Ctrl Shift Enter”，则各分数段的频率分布自动生成。
（3）成绩分布图的生成。先选中要生成图表的数据，再按照图表向导的提示，即可生成相应考试项目的图表。
二、表格的格式化
1.页面设置
通过“页面设置”菜单进行纸张大小、页边距的设置；在学生人数较多需多页打印时，可通过“工作表”标签设置顶端标题，使标题在每一页打印，在顶端标题行中输入“$1:$5”。
2.设置行高和列宽
适当调整列宽，使各列均能在一页中打印；再适当调整行高，使每页显示20或25名学生的成绩。
3.突出显示不及格成绩
选中所有成绩区数据，执行“格式”菜单中“条件格式”命令，将不及格成绩显示为红色或所在单元格加底纹（有彩色打印机时设置为红色，普通打印机时设置底纹）。
4.设置字体和字号
根据需要将不同的内容设置不同的字体，再根据单元格的大小调整各自的字号。
5.设置边框
选择“格式/单元格”菜单中的“边框”标签，用不同的线条设置内外框线。
最后生成某校的成绩分析表（如下图）。
通过以上方法，在每次新课结束后，只要输入了原始数据，成绩表及成绩分析表将自动生成如果有新的分析指标需要，只要做适当调整，就可满足要求。这样就基本达到一劳永逸了。
（作者单位：江苏省海安针灸推拿学校）