大学计算机基础 课件 5.4.2 任务实现

大学计算机基础——基于计算思维（Windows10+Office2016）5.4.2任务实现第5章Excel基本应用5.4课程成绩计算1.任务分析

（1）将“相关素材.xlsx”工作簿中的成绩空白表复制到“大学计算机成绩.xlsx”工作簿中的“课堂考勤成绩”工作表之后，将其命名为“课程成绩”工作表，并设置出勤成绩、课堂表现、课后实训以及大作业的比重分别为20%，20%，20%，40%。并将学生的出勤成绩、课堂表现、课后实训、大作业以及期末考试成绩复制到相应位置。制作如图所示的表格，要求：（2）计算学生的平时成绩及总评成绩（平时成绩和总评成绩四舍五入为整数）。平时成绩=出勤成绩×出勤成绩比重+课堂表现×课堂表现比重+课后实训×课后实训比重+大作业×大作业比重，总评成绩=平时成绩×平时成绩比重+期末成绩×（1-平时成绩比重）。1.任务分析

（3）计算学生的课程绩点。课程总评成绩为100分的课程绩点为4.0，60分的课程绩点为1.0，60分以下课程绩点为0，课程绩点带一位小数。60分～100分间对应的绩点计算公式如下：制作如图所示的表格，要求：

（4）计算学生的总评等级。总评成绩>=90分计为A，总评成绩>=80分计为B，总评成绩>=70分计为C，总评成绩>=60分计为D，其他计为F。（5）根据学生的总评成绩进行排名。1.任务分析

（6）将班级前10名的数据用灰色底纹进行标注。制作如图所示的表格，要求：（7）锁定平时成绩、总评成绩、课程绩点、总评等级、总评排名区域，防止误输入。（8）对“课程成绩”工作表进行打印设置。打印要求为：上、下页边距为3cm，左、右页边距为2cm，纸张采用B5纸横向、水平居中打印。如果成绩单需要分多页打印，则每页需要打印标题（第1行到第4行）信息。2.实现过程

1）工作表制作完成如图所示的工作表的样式及内容设置。操作步骤如下：①打开“相关素材.xlsx”工作簿，选择“课程成绩空白表”工作表，鼠标定位在工作表名称处，单击鼠标右键，在弹出的快捷菜单中选择“移动或复制（M）…”。在如图所示的“移动或复制工作表”对话框中，选择工作表移至“大学计算机成绩.xlsx”工作簿的“Sheet2”工作表之前，并勾选“建立副本”。2.实现过程

1）工作表制作完成如图所示的工作表的样式及内容设置。操作步骤如下：②将“大学计算机成绩.xlsx”工作簿的“课程成绩空白表”工作表重命名为“课程成绩”工作表。③选择“课程成绩”工作表的C5:F5单元格区域，将其数据格式设为“百分比”，通过“减少小数位数”按钮，设置小数位数为0，如图所示。2.实现过程

1）工作表制作完成如图所示的工作表的样式及内容设置。操作步骤如下：④选择C6单元格，输入“=”，切换到“课堂考勤成绩”工作表，选择O5单元格，按下回车键确认输入，C6单元格编辑栏区域的内容为“=课堂考勤成绩!O5”，表示C6单元格中的出勤成绩引用自“课堂考勤成绩”工作表的O5单元格。选择C6单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成公式的复制。⑤在不改变表格格式的前提下将“相关素材.xlsx”工作簿的“成绩数据素材”工作表中的课堂表现、课后实训、大作业以及期末考试成绩复制到相应位置。平时成绩=∑▒〖各项成绩×各项成绩比重〗，总评成绩=平时成绩×平时成绩比重+期末成绩×（1-平时成绩比重）。由于在公式复制时，各项比重的引用地址不随公式位置的变化而变化，因此需要使用绝对地址。操作步骤如下：2.实现过程

2）计算平时成绩及总评成绩①选择G6单元格，输入“=”，用鼠标选取C6单元格，输入“*”，用鼠标选取C5单元格，按下F4键，将C5转换为绝对地址，此时，G6单元格编辑区域的内容为“=C6*$C$5”。在此基础上，输入“+”，重复前面的操作，完成平时成绩=∑▒〖各项成绩×各项成绩比重〗的计算。G6单元格编辑区域的内容最终为“=C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5”。②选择I6单元格，完成总评成绩的计算。I6单元格编辑区域的内容最终为“=G6*$L$3+H6*(1-$L$3)”。2.实现过程

3）将平时成绩和总评成绩四舍五入为整数①选择G6单元格编辑栏中“=”之后的内容，按下Ctrl+X键，将平时成绩计算公式（C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5）剪切下来。单击“公式”选项卡/“函数库”组/“数学与三角函数”的ROUND函数，弹出如图5-48所示的函数参数对话框。将鼠标定位在Number区域，按下Ctrl+V键，将平时成绩计算公式粘贴到此处。在Num_digits区域输入0，单击确定按钮。G6单元格编辑区域的内容最终为“=ROUND(C6*$C$5+D6*$D$5+E6*$E$5+F6*$F$5,0)”。2.实现过程

3）将平时成绩和总评成绩四舍五入为整数②对I6单元格中的内容进行四舍五入取整运算。I6单元格编辑区域的内容最终为“=ROUND(G6*$L$3+H6*(1-$L$3),0)”。③分别选择G6和I6单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。课程绩点计算公式

结果带一位小数（四舍五入）。操作步骤如下：2.实现过程

4）计算成绩绩点

课程绩点计算公式

结果带一位小数（四舍五入）。操作步骤如下：2.实现过程

4）计算成绩绩点

③选择J6单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。④选择J6:J41单元格区域，设置其数值格式为“数字，带1位小数”。由于总评成绩>=90分计为A，总评成绩>=80分计为B，总评成绩>=70分计为C，总评成绩>=60分计为D，其他计为F，因此可以用IF嵌套来实现，对应的逻辑结构如图所示。2.实现过程

5）计算总评等级操作步骤如下：①选择K6单元格，单击“公式”选项卡/“函数库”组/“逻辑”的IF函数，在对应的参数区域输入如表所示的第1行参数后，将光标定位在Value_if_false区域，选择需要嵌入的函数IF，将弹出第2个IF函数，如图所示。2.实现过程

5）计算总评等级IF函数层次Logical_testValue_if_trueValue_if_false1I6>=90A光标定位在此处，选择第2层IF2I6>=80B光标定位在此处，选择第3层IF3I6>=70C光标定位在此处，选择第4层IF4I6>=60DF光标定位在这里后进行函数选择②在第2个IF函数中对应的参数区域输入如表所示的第2行参数后，再将光标定位在Value_if_false区域，选择需要嵌入的函数IF，将弹出第3个IF函数。③在第3个IF函数中对应的参数区域输入如表所示的第3行参数后，再将光标定位在Value_if_false区域，选择需要嵌入的函数IF，将弹出第4个IF函数。④在第4个IF函数中对应的参数区域输入如表所示的第4行参数。K6单元格编辑栏的最终内容为“=IF(I6>=90,"A",IF(I6>=80,"B",IF(I6>=70,"C",IF(I6>=60,"D","F"))))”。⑤选择K6单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。2.实现过程

5）计算总评等级根据学生的总评成绩进行排名，分数最高的排名第1。可以用RANK.EQ函数来实现，参与排位的数据区域需要使用绝对地址。操作步骤如下：2.实现过程

6）总评成绩排名①选择L6单元格，单击单元格编辑栏前的“插入函数”按钮，在弹出的“插入函数”对话框中选择“统计函数”的RANK.EQ函数。2.实现过程

6）总评成绩排名②在如图所示的RANK.EQ函数的Number参数处输入“I6”，Ref参数处选择单元格区域I6:I41，选择Ref参数I6:I41，按下F4键，将其转变为绝对地址区域$I$6:$I$41，在Order参数处输入“0”。L6单元格编辑栏的最终内容为“=RANK.EQ(I6,$I$6:$I$41,0)”，表示按降序方式计算I6在单元格区域$I$6:$I$41中的排名。2.实现过程

6）总评成绩排名③选择L6单元格，移动鼠标，当鼠标变成黑色填充柄时，双击鼠标左键，完成函数及公式的复制。可以看到，在所有RANK.EQ中，Number参数随着单元格位置的变化而变化，而Ref的参数都为$I$6:$I$41，表示要计算的总是在$I$6:$I$41区域中的排名。将班级前10名的数据用灰色底纹标识出来，可以用条件格式来实现。操作步骤如下：2.实现过程

7）特别标注信息①选择单元格区域A6:L41，单击“开始”选项卡/“格式”组/“条件格式”按钮的“新建规则”。②在弹出的如图所示的“新建格式规则”对话框中，选择规则类型为“使用公式确定要设置格式的单元格”。单击“为符合此公式的值设置格式”区域，选择L6单元格，按下F4键两次，编辑规则公式显示为“=$L6”，在其后面输入“<=10”，编辑规则公式为“=$L6<=10”。选择“格式”，设置为“灰色底纹”，单击“确定”按钮。将班级前10名的数据用灰色底纹标识出来，可以用条件格式来实现。操作步骤如下：2.实现过程

7）特别标注信息可以看到，总评排名为前10名的所有单元格都用灰色底纹进行标注了。公式“=$L6<=10”指明的条件格式为：如果对应行的第L列的值<=10，则对所有符合条件的单元格区域用灰色底纹标识出来了。由于平时成绩、期末成绩、总评成绩、成绩绩点、总评等级以及总评排名等是通过公式和函数计算出来的，为防止误修改，可以对这些指定的单元格进行锁定。操作步骤如下：2.实现过程

8）利用审阅功能锁定单元格①鼠标单击“课程成绩”工作表的行标题和列标题交接处图标，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”。②在如图所示的“设置单元格格式”对话框中，切换到“保护”选项卡，不勾选“锁定”和“隐藏”复选框，单击确定。2.实现过程

8）利用审阅功能锁定单元格③选择不允许编辑的单元格区域（G6:G41，I6:L41），单击鼠标右键，选择“设置单元格格式”，在格式设置中，勾选“锁定”复选框。④选择当前工作表的任一单元格，切换到“审阅”选项卡/“保护”组/“保护工作表”按钮，在弹出的如图所示的“设置保护工作表”对话框中，选取“保护工作表及锁定的单元格内容”复选框，勾选“选取锁定单元格”和“选定解除锁定的单元格”复选框，清除其他复选框，单击确定。这样，平时成绩等区域中的单元格就不能被编辑了。①单击“页面布局”选项卡/“页面设置”组/“页边距”按钮的“自定义边距”，在弹出的“页面设置”对话框的“页边距”选项卡中，设置上、下页边距为3cm，左、右页边距为2cm，勾选“水平”居中方式。②单击“页面布局”选项卡/“页面设置”组/“纸张方向”按钮的“横向”。③单击“页面布局”选项框/“页面设置”组/“纸张大小”按钮的“B5（JIS）”。④单击“页面布局”选项卡/“页面设置”组/“打印标题”按钮，在弹出的“页面设置”对话框的“工作表”选项卡中，光标定位在“顶端标题行”输入框，用鼠标选定1