计算机文化基础项目7-制作学生成绩统计表课件.ppt

1、计算机系08级有0801班、0802班、0803班3个教学班，期末考试科目有计算机基础、计算机组装、大学英语和高等数学等4门，各班学生的平时成绩和期末考试成绩见分别见表7-1、表7-2和表7-3。创建创建4 4张工作表：张工作表：08010801班、班、08020802班、班、08030803班、计算机专业质量分析表，班、计算机专业质量分析表，工作表标签颜色依次设置为黄色、工作表标签颜色依次设置为黄色、绿色、蓝色和红色。绿色、蓝色和红色。标题：字体黑体、字号标题：字体黑体、字号2020号、居中显示号、居中显示单科总分单科总分=平时成绩平时成绩40%+40%+期末成绩期末成绩60%60%计算出单

2、科总分的最高分、最低分和平均分计算出单科总分的最高分、最低分和平均分低于低于6060分的分数设置为分的分数设置为“加粗加粗 倾斜倾斜”、“红红色色”标题标题“*学校计算机专业学生成绩质量分析表学校计算机专业学生成绩质量分析表”在在“A1:F1A1:F1”区域居中显示、黑体、区域居中显示、黑体、2020号。号。A A、D D、E E、F F列列宽均为列列宽均为2020磅，磅，B B、C C列列宽为列列宽为1010磅，第磅，第1 1行行高行行高2424磅，第磅，第2 2行至行至4242行行高行行高1414磅，文字磅，文字1212号宋体，第号宋体，第2 2行文字加粗，浅黄色底纹。单元格行文字加粗，浅

3、黄色底纹。单元格区域区域“A2:F22A2:F22”加边框线，外框双实线，内部单加边框线，外框双实线，内部单实线，其中各科目之间用双实线分隔。实线，其中各科目之间用双实线分隔。统计各班最高分、最低分、平均分、分数段人数统计各班最高分、最低分、平均分、分数段人数和及格率。和及格率。要求每个班的学生成绩表设置数据保护。要求每个班的学生成绩表设置数据保护。要求对工作簿文件设置安全性密码保护。要求对工作簿文件设置安全性密码保护。单击电脑桌面【开始】菜单，单击【程序】|【Microsoft Office】|【Microsoft Office Excel 2003】命令。单击菜单栏上的【文件】|【保存】命

4、令，弹出文件“另存为”对话框，将当前工作簿文件命名为“08级计算机专业学生成绩表.xls”，并保存在“D：我的文档”文件夹下。单击工作表“Sheet3”标签，单击鼠标右键，弹出快捷菜单。在弹出的菜单中，单击【插入】命令，弹出【插入】对话框，由插入、电子方案表格选项卡组成。在【插入】对话框的【常用】选项卡内，单击【工作表】选项，单击【确定】按钮，工作表“Sheet 3”左侧插入了一个标签为“Sheet 4”的新工作表。右键单击工作表“Sheet 1”标签，弹出快捷菜单，单击快捷菜单中的【重命名】命令，此时标签“Sheet 1”被激活为编辑状态，重命名即可。单击【工作表标签颜色】，打开【设置工作表

5、标签颜色】选项框，可设定标签颜色。本项目中的3个工作表“0801班”“0802班”“0803班”具有相同的表结构，并且部分内容是一样的，在Excel中，像这样表结构相同的工作表，可以将它们一并处理，不必重复相同的操作。用鼠标左键单击工作表“0801班”标签，然后按住Ctrl键不放，再依次单击工作表“0802班”、“0803班”的标签，最后松开Ctrl键，这3个工作表被同时选定，变为成组工作表。在单元格区域C3：E3内，录入“平时成绩”、“期末成绩”和“单科总分”等内容，选中单元格区域“C3:E3”，拖动单元格E3下方的填充柄至目的单元格N3，完成“F3:N3”的填充。用鼠标右键单击成组工作表标

6、签处，系统弹出快捷菜单中，单击快捷菜单中的【取消成组工作表】命令，取消三张工作表的选定状态。标题“*学校计算机专业学生成绩质量分析表”的设置要求为：居中显示在单元格区域A1:F1、字体黑体、字号20号。A、D、E、F列列宽均为20磅，B、C列列宽为10磅，第1行行高24磅，第2行至42行行高14磅，文字12号宋体，第2行文字加粗，浅黄色底纹。单元格区域“A2:F22”加边框线，外框双实线，内部单实线，其中各科目之间用双实线分隔。根据单科总分的计算公式“单科总分=平时成绩40%+期末成绩60%”，可自定义公式来计算。按住Ctrl键不放，依次单击工作表“0801班”、“0802班”、“0803班”

7、的标签，同时选定3个工作表后，松开Ctrl键。单击单元格E4，输入公式“=C4*0.4+D4*0.6”（公式中的*号表示数学中的乘法运算），或单击单元格E4后，再单击编辑栏，在编辑栏中输入相同内容，公式输完后，按回车键或单击输入按钮，单元格E4显示计算结果为“90.8”。单击单元格E4，用鼠标左键拖动单元格E4的填充柄至目的单元格E33，单元格E4的公式被复制填充，“计算机基础”的“单科总分”计算结果全部自动计算并显示。参照操作，依次完成其它“单科总分”数据填充。Excel中单元格的地址有3种引用方式：相对引用地址、绝对引用地址和混合引用地址。绝对引用地址的表示方法是在行号和列号之前都加上一个

8、“$”符号，例如在上述操作中，如果在单元格E4中输入公式“=$C$4*0.4+$D$4*0.6”。拖动填充柄自动填充，结果所有学生的“计算机基础”的单科总分都是“90.8”。如果单元格引用地址的一部分为绝对引用地址，另一部分为相对引用地址，例如$C3或C$3，称为“单元格的混合引用地址”。单击单元格O4，输入公式“=E4+H4+K4+N4”，按回车键，显示计算结果“313.4”。再拖动单元格O4填充柄至目的单元格O33，完成所有学生的“总分”填充。求最大值函数MAX()函数的格式为：MAX(Number1,Number2,)它的功能是返回给定参数表中的最大值。可以将参数指定为数字、空白单元格、

9、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数为数组或引用，则只有数组或引用中的数字将被计算。如果参数不包含数字，函数 MAX 返回 0（零）。求最小值函数MIN()函数的格式为：MIN(Number1,Number2,)它的功能是返回给定参数表中的最小值。可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数中不含数字，则函数 MIN 返回 0。求平均值函数AVERAGE()函数的格式为：AVERAGE(Number1,Number2,)它的功能是返回参数的平均值（算术平均值）。参数可以

10、是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。单击单元格E34，输入“=MAX(E4:E33)”，按回车键显示最高分“93.2”。单击单元格E35，输入“=MIN(E4:E33)”，按回车键显示最低分“60”，完成最低分的计算。单击单元格E36，并输入“=AVERAGE(E4:E33)”，按回车键显示平均分“83.45333”。复制单元格区域E34:E36，分别粘贴至单元格区域H34:H36、K34:K36、N34:N36，公式自动变化，完成其他课程最高分、最低分和平均分的计算。排位函数RANK可实现名

11、次排列，它的语法格式为：RANK(number,ref,order)，Number：为需要找到排位的数字。Ref：为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order：为一数字，指明排位的方式。如果 order 为 0（零）或省略，基于 ref 为按照降序排列的列表。如果 order 不为零，基于 ref 为按照升序排列的列表。函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。单击单元格P4，并输入公式“=RANK（O4，$O$4:$O$33，0）”，它表示计算单元格O4存储的数据在单元格区域O4:O33中的降序排位，按回车键显示结果3，完成第一

12、位学生的排位。移动鼠标指针至单元格P4的填充柄，按住鼠标左键不放拖动填充柄至目的单元格P33，显示所有学生的总分排名结果。单科总分低于60分的数字格式设置为加粗倾斜、红色。按住Ctrl键不放，依次选中四个不连续的单元格区域E4:E33、H4:H33、K4:K33、N4:N33，所有单科总分所在单元格区域同时选定后，再松开Ctrl键。移动鼠标指针至菜单栏上，单击菜单栏上的【格式】|【条件格式】命令,打开“条件格式”对话框。“字形”栏内选择“加粗倾斜”，单击颜色下拉按钮，弹出颜色列表栏，选择“红色”，点击【确定】按钮。单击“介于”右端的下拉按钮，选择“小于”，在右侧编辑栏内输入数字60，表示操作对

13、象为数值小于60的单元格，单击【格式】按钮，打开“单元格格式”对话框。操作时先设定数据的自动重算功能，保证数据之间计算随源数据变化而变化。单击菜单栏上的【工具】|【选项】命令，打开“选项”对话框，单击“重新计算”选项卡，在“计算”单选栏中选择“自动重算”。计算单元格D3的值。选定单元格D3，然后单击编辑栏左侧的“插入函数”工具图标，系统打开“插入函数”对话框。选择返回最大值函数MAX 单击“Number1”右侧的拾取按钮，打开【函数参数】输入窗口 单击工作表“0801班”标签，打开该工作表，选定单元格区域E4:E33，此时【函数参数】窗口自动显示拖中的单元格区域 单击拾取按钮返回到【函数参数】

14、设置窗口，显示计算结果为93.2。参照上述操作方法依次计算出单元格D4、D11内的最低分和平均分，也可以直接在单元格D4内输入公式“=MIN(0801班!E4:E33)”，在单元格D11内输入公式“=AVERAGE(0801班!E4:E33)”，完成最低分、平均分的计算。利用工作表的引用，可实现不同工作表间的数据传输，如“=MIN(0801班!E4:E33)”表示对工作表“0801班”的工作表区域E4：E33的所有数据求最小值，工作表名称用半角单引号作为定界符，工作表与单元格之间用“！”符号分隔。本任务也可通过直接引用其它工作表的对应单元格来实现，如求0801班计算机应用基础的最高分，先在D3

15、单元格内输入“=0801班!E34”，再单击输入按钮或按回车键即可，表示单元格D3的值引用了工作表“0801班”中单元格E34的值，满足自动重算需要，并且操作更简洁。操作时先设定数据的自动重算功能，保证数据之间计算随源数据变化而变化。单击菜单栏上的【工具】|【选项】命令，打开“选项”对话框，单击“重新计算”选项卡，在“计算”单选栏中选择“自动重算”。COUNTIF函数的语法结构为：COUNTIF(range,criteria)，它的功能是计算区域中满足给定条件的单元格的个数，其参数意义为：Range：为需要计算其中满足条件的单元格数目的单元格区域。Criteria：为确定哪些单元格将被计算在内

16、的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为 32、32、32、apples 或 B4。单元格D5是统计0801班计算机应用基础总分大于等于90的人数，即返回工作表“0801班”的单元格区域E4：E33内满足大于等于90的单元格数目，利用Excel提供的条件计数函数COUNTIF可轻松完成该操作。在“函数参数”输入框中，单击Range范围栏右侧的拾取按钮，进入“函数参数”输入框。选定单元格D5后单击编辑左侧的“插入函数”工具按钮，打开【插入参数】对话框，选择COUNTIF函数，打开“函数参数”输入框。单击工作表“0801班”标签，选定单元格区域E4:E33，参数输入

17、栏显示“0801班!E4:E33”。在【函数参数】输入框中，单击Criteria条件栏，并输入条件“”=90”，显示计算结果等于5，点击【确定】按钮，完成操作。计算单元格D6的值。选定单元格D6后输入公式“=COUNTIF(0801班!E4:E33,=80)-COUNTIF(0801班!E4:E33,=90)”，表示求分数大于等于80小于90的人数。在D7单元格内输入公式“=COUNTIF(0801班!E4:E33,”=70“)-COUNTIF(0801班!E4:E33,”=80“)”，在D8单元格内输入公式“=COUNTIF(0801班!E4:E33,”=60“)-COUNTIF(0801班

18、!E4:E33,”=70“)”，在D9单元格内输入公式“=COUNTIF(0801班!E4:E33,”=50“)-COUNTIF(0801班!E4:E33,”=60“)”，在D10单元格内输入公式“=COUNTIF(0801班!E4:E33,”50“)”。及格率设置为百分比显示，保留2位小数。单击单元格D12，输入公式“=SUM（D5：D8）/30”，显示结果为1，将单元格D12的格式设置为百分比格式（操作方法参见项目六），显示为100.00%。单击“0801班”工作表标签，单击菜单栏上的【工具】|【保护】|【保护工作表】命令，打开“保护工作表”对话框。在【保护工作表】对话框中的“取消工作保护

19、时使用的密码”栏内输入密码。系统提示再次输入密码，再次输入密码。单击菜单栏上的【工具】|【选项】命令，打开“选项”对话框。在弹出的“选项”对话框中选择“安全性”选项卡，在“此工作簿的文件加密设置”中输入打开权限密码，系统提示重新输入密码确认即可。本项目设置的文件密码为123456，以便读者在打开成品文件时使用。本项目以学校常见的学生成绩表的制作为背景，详细地介绍了工作表的各类操作，如工作表的插入或删除、重命名、标签颜色等基本操作，特别是同时选定多个工作表，对成组工作表设置相同的的表结构和录入相同的内容，不仅操作简便，而且效率很高，非常适用于相同工作表的集中处理。本项目中运用了一些常见函数和简单公式，从函数和公式本身来讲不难理解，但需提醒注意的是，在书写各类公式时，如果引用到单元格或单元格区域，必须结合项目本身要求，正确地选择单元格地址的引用方式，即相对引用、绝对引用或混合引用，同时还要学会不同工作表间数据的引用。文件的安全性常见于许多工作表，如学生成绩单、员工工资表、货物销售清单等，本项目对工作表的保护和工作簿加密等知识作了介绍，操作方法简单易学，并且实用性很强。