C09-【计算机二级】Excel操作题(9)统计初三（14）班学生成绩

期末考试结束了，初三（14）班的班主任助理王老师需要对本班学生的各科考试成绩进行统计分析，并为每个学生制作一份成绩通知单发给家长。按照下列要求完成该班的成绩统计工作并按原文件名进行保存。

（1）打开工作簿“学生成绩.xlsx”，在最左侧插入一个空白工作表，重命名为“初三学生档案”，并将该工作表标签颜色设置为“紫色（标准色）”。

①打开工作簿“学生成绩.xlsx”，在最左侧插入一个空白工作表，重命名为“初三学生档案”

考点1：插入工作表

方法一：

方法二：

考点2：重命名

②将该工作表标签颜色设置为“紫色（标准色）”

考点3：设置工作表标签颜色

（2）将以制表符分隔的文本文件“学生档案.txt”自A1单元格开始导入到工作表“初三学生档案”中，注意不得改变原始数据的排列顺序。将第一列数据从左到右依次分成“学号”、“姓名”两列显示。最后创建一个名为“档案”，包含数据区域A1:G56、包含标题的表，同时删除外部链接。

①将以制表符分隔的文本文件“学生档案.txt”自A1单元格开始导入到工作表“初三学生档案”中

考点4：导入外部数据

方法一：

我用的是2019版本，显示有点不同，最终导入的结果最上面一行标题也很奇怪，并且总是会生成一个新的表格

因此，我一般还是选择直接复制，从复制选项中打开传统的导入方法

方法二：

Ⅰ、先复制txt文本的内容

Ⅱ、在Excel中Ctrl V粘贴过后，右下角会出现一个使用文本导入向导

Ⅲ、这时候就出现了熟悉的导入对话框

Ⅳ、选择分隔符号

Ⅴ、特别注意，身份证列单元格格式要选择文本

出生日期单元格格式要选择日期

②将第一列数据从左到右依次分成“学号”、“姓名”两列显示

考点5：给数据分列

Ⅰ、先在学号姓名右侧插入一列，否则分列后的数据会将身份证号列覆盖

Ⅱ、数据→分列

标题也别忘了改过来

③最后创建一个名为“档案”，包含数据区域A1:G56、包含标题的表，同时删除外部链接

考点6：创建表

Ⅰ、选中数据

Ⅱ、在开始选项卡下套用表格格式

或在插入选项卡下点击插入表格

Ⅲ、在表设计选项卡下的“表名称“将其更名为”档案“

（3）在工作表“初三学生档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”，出生日期“XXXX年XX月XX日”和年龄。其中，身份证号的倒数第二位用于判断性别，奇数为男性，偶数为女性；身份证号的7～14位代表出生年月日，年龄需要按周岁计算，满1年才计1岁。最后适当调整工作表的行高和列宽、对齐方式等，以方便阅读。

①输入学生性别

考点7：MID函数、MOD函数和IF函数嵌套

Ⅰ、先采用MID函数提取身份证号第17位的数字

Ⅱ、采用MOD函数判断身份证号倒数第二位是奇数还是偶数

插播一个题外话，我这里用的是MOD函数来判断奇偶数，其实可以直接用ISODD函数来判断

Ⅲ、用IF函数判断学生是男的还是女的

如果用IF函数和ISODD函数嵌套，则是

②输入学生出生年月日，出生日期“XXXX年XX月XX日”

考点7：MID函数

考点8：TEXT函数

在MID函数外面嵌套TEXT函数

③计算学生年龄

考点9：DATEDIF函数

注意：DATEDIF函数是没有函数表达式提示

它实质是：DATEDIF(起始日期,终止日期,返回日期类型)

对于本题来说就是DATEDIF(出生日期,TODAY(),”Y”)    【”Y”代表年】

④适当调整工作表的行高和列宽、对齐方式

考点10：调整行高列宽、对齐方式

在开始→格式下，有自动调整行高和自动调整列宽

（4）参考工作表“初三学生档案”，在工作表“语文”中输入与学号相对应的“姓名”；按照平时、其中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中，按成绩由高到低的顺序统计每个学生的“学期成绩”排名柄按照“第n名”的形式填入“班级名次”列中，按照下列条件填写“期末总评”。

①参考工作表“初三学生档案”，在工作表“语文”中输入与学号相对应的“姓名”

考点11：VLOOKUP函数

②按照平时、其中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中

考点12：简单计算

③按成绩由高到低的顺序统计每个学生的“学期成绩”排名柄按照“第n名”的形式填入“班级名次”列中

考点13：RANK函数

使用RANK函数对学生成绩进行降序排序

按照题目要求写入“第n名”

④按照要求填写期末总评

考点14：IFS函数

（5）将工作表“语文”的格式全部应用到其他科目工作表中，包括行高（各行行高均为22默认单位）和列宽（各列列宽均为14默认单位），并按照上述（4）的要求依次输入或统计其他科目的“姓名”、“学期成绩”、“班级名次”和“期末总评”。

考点15：统一操作

①选中所有科目的工作表

②先在语文工作表中完成一次计算

③检查其他工作表，发现相应位置都完成了计算

③在语文工作表中每一个需要计算的位置完成上一步操作，于是其他工作表也计算完成了

在期末总评列，语文和数学的规则一样，但是其他科目不一样

④取消组合工作表

⑤重新组合语文、数学外的其他工作表并修改参数

⑥将语文工作表的格式应用到其他工作表

保持选中状态下，在开始选项卡→格式→调整各单元格的行高和列宽：行高20、列宽14

（6）分别将各科的“学期成绩”引入工作表“期末总成绩”的相应列中，在工作表“期末总成绩”中依次引入姓名、计算各科的平均分、每个学生的总分，并按成绩由高到低的顺序统计每个学生的总分排名，并以1、2、3……形式标识名次，最后将所有成绩的数字格式设置为数值，保留2位小数。

考点11：VLOOKUP函数

①引入姓名

②引入语文成绩

③引入数学成绩

其余科目参照数学即可

④计算总分

采用求和公式

统计7个科目的总成绩

⑤统计排名

⑥统计各个科目的平均分

考点12：简单计算

⑦设置成绩的数值格式，保留2位小数

（7）在工作表“期末总成绩”中分别用红色（标准色）和加粗格式标出各科第一名成绩。同时将前10名的总分成绩用浅蓝色填充。

考点16：条件格式

①找出语文成绩第一名

②用格式刷找出剩下科目第一名

③将前10名的总分成绩用浅蓝色填充

（8）调整工作表“期末总成绩”的页面布局以便打印，纸张方向为横向，所见打印输出使得所有列只占一个页面宽（不得缩小列宽）水平居中打印在纸上。

考点17：打印设置

Ctrl+S快速保存