《办公自动化》-第7章教学材料

7.1任务：制作学生成绩信息表7.1.1任务描述每学期结束后，学校常常要求教师使用Excel输出学生成绩信息。由于每学期的班级、专业、课程及班级人数经常发生变化，因此，每学期结束后，都要求教师对所任班级的成绩统计与分析重新操作。通过Excel可以实现数据的采集及统计，实现高效的办公自动化。本任务以16本电商1班为例，以本班20名学生为样本进行数据采集。为了统一管理和分析成绩情况，要求汇总该班每科目的成绩，具体信息统计要求如下：序号、学号、姓名、性别、班级、专业、科目（思修、大学英语、大学计算机、管理学、经济学）、总分、平均分、等级（总分大于300分为合格，小于300分则为不合格）及排名情况（要求排名按升序）。下一页返回7.1任务：制作学生成绩信息表本任务实施要求如下：①新建一个Excel文档并命名；②录入数据信息；③计算分析数据；④修饰表格：突出不及格分数。任务效果如图7-1所示。7.1.2任务实现1.新建Excel文件上一页下一页返回7.1任务：制作学生成绩信息表启动Excel2010软件，系统会自动创建一个名为“工作簿1”的文档，如图7-2所示。在默认情况下，Excel2010新建的工作簿中包含3张工作表，分别以Sheet1、Sheet2、Sheet3命名。工作表可以重新命名，也可以新建。一个工作簿最多允许新建255张工作表。工作表建好后可以删除，也可以更改工作表之间的顺序。当工作表较多时，在工作表标签处可能无法将所有工作表标签都显示出来，此时可以单击工作表标签左侧的导航按钮切换当前显示出的工作表标签。如果把Excel的工作簿比作一本书，那么工作表就是该书的每一页。上一页下一页返回7.1任务：制作学生成绩信息表Excel2010的每张工作表由1048576行和16384列组成，行号用1、2、3等数字表示，列号用A、B、C、D等字母表示。行和列交叉的区域称为单元格，单元格是Excel最基本的单元，它是处理数据和存储数据的核心部分。任何一个单元格均由列标号和行标号组合而成，例如，C3表示第3列第3行的单元格，如图7-3所示。若要表示多个不同单元格，可以用逗号“，”表示。若要表示一个连续的单元格区域，可以用冒号“：”表示。将光标定位在任一单元格，按住Ctrl+→组合键，就会出现最后一列，即第XFD列。按住Ctrl+↓组合键，就会出现最后一行。上一页下一页返回7.1任务：制作学生成绩信息表单击菜单栏“保存”按钮，保存文件名为“学生成绩信息表.xlsx”。选择Sheet1工作表进行操作，用鼠标双击Sheet1进行重命名操作，修改Sheet1工作的名称为“学生成绩信息表”，如图7-4所示。在工作簿中，为了区别不同的工作表，除更改工作表名称外，还可以更改工作表标签的颜色，以突出显示该工作表。右击“学生成绩信息表”工作表标签，在弹出的菜单中选择“工作表标签颜色”选项，选择“红色”，如图7-5所示。2.录入学生数据信息并排版上一页下一页返回7.1任务：制作学生成绩信息表创建好Excel文件后，需要在相应工作表中录入的数据。不同的数据内容在录入时有一定的区别。录入普通文本和数值时，在选择的单元格中直接输入内容。第一步：录入表格标题文字并排版。单击工作表第1个单元格A1，将该单元格选中，直接在该单元格中输入“2016-2017第一学期学生成绩信息表”文本内容，如图7-6所示。同样，分别在A2、L2和A3单元格输入“行政班级：16本电商1班”“学生人数：20”及“辅导员：李浪滽”标题文本内容。接着选择第4行，输入学生成绩信息表字段，如图7-7所示。上一页下一页返回7.1任务：制作学生成绩信息表选中A1到O1单元格，在菜单栏“开始”选项卡中选择“合并后居中”按钮，弹出菜单后选中“合并后居中”命令，即可实现“2016-2017第一学期学生成绩信息表”文字在A1到O1单元格中居中的效果，如图7-8所示。同样，对A2到C2单元格、L2到N2单元格及A3到B3单元格进行合并居中操作。选中A4到O24单元格，在菜单栏“开始”选项卡中选择“所有框线”按钮图标，弹出菜单后选中“所有框架”命令，即可实现将A4到O24单元格加上边框，如图7-9所示。上一页下一页返回7.1任务：制作学生成绩信息表为了使文字和表格输出更具效果，将所有标题文字加粗，并设置“2016-2017第一学期学生成绩信息表”为16号字体。选中菜单栏“页面布局”选项卡，选择“页边距”命令图标，设置左、右边距都为1，如图7-10所示。设置好页边距后，发现表格标题栏有部分字体被边框遮住了，选中被遮住的“大学计算机”单元格，右击，在弹出的菜单中选择“设置单元格格式”按钮，出现对话框，选择“对齐”选项卡，在“文本控制”中将“自动换行”打上“√”，如图7-11所示，即可实现文字“大学计算机”自动换行。上一页下一页返回7.1任务：制作学生成绩信息表选中A4到O2单元格，右击，在弹出的菜单中选择“设置单元格格式”按钮，出现对话框，选择“对齐”选项卡，分别单击“文本对齐方式”中的“水平对齐”和“垂直对齐”的下拉三角形按钮，都选择“居中”，如图7-12所示。输入标题文字内容并排版后，效果如图7-13所示。第二步：录入学生成绩信息。学生成绩信息表序号的输入。在Excel中要输入连续数值内容时，可以利用Excel自动填充功能。上一页下一页返回7.1任务：制作学生成绩信息表具体操作如下：选择A5单元格，输入数值“1”后，按住Ctrl键不放，将鼠标指向所选单元格右下角，当鼠标指针右上角出现实心十字形“+”填充柄时，向下拖动至单元格A24，如图7-14所示。学生成绩信息表学号的输入。由于学号是文本数据，输入数据时，需在输入的数据前加上单引号“’”。此时也可以采用自动填充功能，操作同数值的自动填充方法一样，不同的是，不能按住Ctrl键。如图7-15所示。文本数据前加单引号“’”，可以输入首字为0的文本，如果不加单引号“’”，前面0会自动被省略。上一页下一页返回7.1任务：制作学生成绩信息表学生成绩信息表性别的输入。在Excel表格中输入数据时，为保证数据的准确性，方便用户对数据进行查找，对于相同的数据，应使相同的描述。可以应用Excel提供的“数据有效性”功能，对单元格内容添加只允许输入提供的数据序列，并在下拉菜单中进行选择。具体操作如下：①选择D5到D24之间单元格区域，单击菜单栏“数据”选项中“数据有效性”按钮，在弹出的菜单中选择“数据有效性”命令，如图7-16所示。上一页下一页返回7.1任务：制作学生成绩信息表②在弹出对话框中，在“设置”选项卡中的“允许”下拉列表框中选择“序列”，在“来源”文本框中输入“男，女”，其中各数据之间必须使用英文逗号“，”进行分隔，如图7-17所示。③单击“输入信息”选项卡中，在“标题”文本框中输入提示信息标题：性别，在“输入信息”文本框内输入提示文字：请按要求输入或选择，如图7-18所示。④单击“出错警告”选项卡中，在“样式”下拉列表框中选择“停止”，即禁止有任何错误的信息输入。输入出错警告的标题：“输入有误”错误信息：“您输入的信息有误，请重新选择下拉列表中提供的内容或重新输入，谢谢！”，如图7-19所示。上一页下一页返回7.1任务：制作学生成绩信息表在“数据有效性”对话框的“出错警告”选项卡中，用户可以设置当单元格内输入的数据不符合要求时显示的对话框样式及内容，通过“样式”选项的选择，可以更改对话框的提示方式。当使用“停止”样式时，当数据出错错误后，必须将数据更改为正确的，否则将不能进行其他操作。若使用“警告”样式，出现错误后，在弹出的警告对话框中单击“忽略”按钮，可保留不符合有效性规则的数据。若使用“信息”样式，则在输入不符合规则的数据后，仅在弹出的对话框中显示提示信息，不要求对数据进行修改。学生成绩信息表中，姓名、班级、专业等文本数据，直接输入即可，操作方法与前述文本型数据的相同。上一页下一页返回7.1任务：制作学生成绩信息表性别字段设置好数据有效性后，只需通过下拉菜单选择男、女即可，无须再输入其他文字，如图7-20所示。在学生考试科目：思修、大学英语、大学计算机、管理学和经济法字段中输入数据后，选中数据区域，单击鼠标右键，在弹出菜单中选择“设置单元格格式”，然后在“数字”选项卡的“分类”中选择“数值”，将小数位数保留1位，如图7-21所示。单击“确定”按钮，学生考试科目字段栏内就出现保留1位小数的数据，如图7-22所示。3.计算分析数据上一页下一页返回7.1任务：制作学生成绩信息表在办公过程中，经常需要对大量数据进行分析，借助Excel提供丰富的函数，可以发挥其强大的数据统计分析功能，从而满足日常办公的各种需要，以提高办公效率。（1）求和函数和平均函数在本任务中进行数据统计时，使用求和函数SUM（）和平均函数AVERAGE（）可以直接计算出结果。具体操作步骤如下。第一步：选中总分字段下方的L5单元格，单击“编辑栏”上的“插入函数”按钮，弹出“插入函数”面板，选择SUM函数，如图7-23所示。上一页下一页返回7.1任务：制作学生成绩信息表第二步：单击“确定”按钮，弹出“函数参数”面板，选择“Number1”文本框右边的按钮，如图7-24所示。第三步：选择数据区域G5：K5，如图7-25所示。第四步：选择好数据后，单击“函数参数”面板上右边的按钮，单击“确定”按钮即可完成求和，如图7-26所示。第五步：选择L5单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向下拖动到单元格L24，即可显示各学生的成绩总和，如图7-27所示。第六步：求平均分。操作步骤同上，不同的是，使用函数AVERAGE。效果如图7-28所示。上一页下一页返回7.1任务：制作学生成绩信息表（2）求等级满足总分大于300分，则等级输出为“合格”；总分小于300分，则等级输出为“不合格”。采用Excel提供的函数IF（）。具体操作步骤如下。第一步：选中等级字段下方的N5单元格，单击“编辑栏”上的“插入函数”按钮，弹出“插入函数”面板，选择IF函数，如图7-29所示。第二步：在弹出的“函数参数”面板中，在第一个函数Logical_test中输入“L5>=300”，在第二个参数Value_if_true中输入“‘合格’”文字内容，在第三个参数Value_if_false中输入“‘不合格’”文字内容，如图7-30所示。上一页下一页返回7.1任务：制作学生成绩信息表第三步：单击“确定”按钮，即会在N5单元格出现“合格”字样，如图7-31所示。第四步：选择N5单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向下拖动到单元格N24，即可实现所有学生成绩等级情况，如图7-32所示。（3）求排名在Excel中经常使用RANK.EQ（）函数进行排位计算，本任务要求计算各位学生的总分排名，具体操作如下。上一页下一页返回7.1任务：制作学生成绩信息表第一步：选中排名字段下方的O5单元格，单击“编辑栏”上的“插入函数”按钮，弹出“插入函数”面板，在“搜索函数”栏内输入“RANK”，单击“转到”按钮。在“选择函数”栏内出现RANK.EQ，选中RANK.EQ函数，如图7-33所示。在Excel2010中可以使用RANK.EQ或RANK.AVG函数计算一个数在一组数据中的排名，也可以使用早期Excel版本中用到的RANK函数进行排名计算。RANK.EQ函数与早期版本中用到的RANK函数相同；与RANK.AVG函数的区别在于：有相同排位时，前者将得到最高排位，后者将得到平均排位。上一页下一页返回7.1任务：制作学生成绩信息表第二步：选择好RANK.EQ函数后，单击“确定”按钮，出现“函数参数”对话框，第一个参数Number是一个数值，是指要排名的第一个数值，这里选择总分L5单元格数据。第二个参数Ref是一组数据，是指将Number参数中的数值与该参数中的一组数据进行比较，这里选择$L$5：$L$24单元格数据区域。第三个参数Order是排名方式，可以将该参数设置为0或省略，则将排名按降序，如果参数为其他数值，则按升序排名。参数设置如图7-34所示。在Excel中引用单元格有三种方式：相对引用、绝对引用及混合引用。相对引用是Excel默认的引用地址的方式，具体格式为：［列号］［行号］，例如：A2。上一页下一页返回7.1任务：制作学生成绩信息表当函数或公式的单元格被复制或引用位置变化时，该引用地址与函数或公式中所有单元格的相对位置保持不变。绝对引用格式为：$［列号］$［行号］，例如，$A$2。当把含有绝对地址的单元格的函数或公式复制到一个新的位置时，函数或公式中的单元格地址不会发生变化，绝对地址不会随函数或公式所在位置的改变而改变。混合引用的格式为：$［列号］［行号］或［列号］$［行号］，例如：$A2或A$2。当把含有单元格混合地址的函数或公式复制到一个新位置时，函数或公式中含有的混合地址的相对部分发生相应的变化，而绝对部分不会发生变化。上一页下一页返回7.1任务：制作学生成绩信息表第三步：选择O5单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向下拖动到单元格O24，即可实现所有学生成绩总分的排名，如图7-35所示。第四步：选择A5：O24单元格，单击菜单栏“数据”选项卡，选择“排序”按钮，弹出“排序”面板，单击“主要关键字”下拉按钮，选择“排名”字段，次序为“升序”，如图7-36所示。第五步：排序参数设置好后，单击“确定”按钮，排序效果如图7-37所示。4.修饰表格：突出不及格分数上一页下一页返回7.1任务：制作学生成绩信息表为使表格中数据显示更加清晰，班级要求统计不合格分数，这里设置单科成绩在60分以下为不合格成绩。选择各科成绩所在单元格区域G5：K24，单击菜单栏“开始”→“条件格式”→“突出显示单元格规则”→“小于”命令，如图7-38所示。在弹出的条件格式设置面板中设置“小于”值为60，颜色为“浅红填充红色文本”，单击“确定”按钮即可实现突出不合格分数，如图7-39所示。上一页返回7.2任务扩展：制作学生成绩统计分析表7.2.1任务描述学校为进一步加强各科目学生成绩管理，要求统计各科目各阶段分数分布情况，通过分析可以得出学生掌握各科目知识点的情况，以便能够更好地实施教学。具体要求：在任务7.1制作学生成绩信息表的基础上，统计各科目实考人数、最高分、最低分，各科目90分以上人数、80-89分人数、70-79分人数、60-69分人数、60分以下人数，优秀率（90分以上）、及格率及不合格率。本任务实施要求如下：①在任务7.1新建的学生成绩信息表中录入数据信息；下一页返回7.2任务扩展：制作学生成绩统计分析表②统计分析数据；③添加表格边框：外边框为细线条，内边框为虚线；④添加表头：行标题为科目，列标题为统计。任务效果如图7-40所示。7.2.2任务实现1.在任务7.1新建的学生成绩信息表中录入数据信息第一步：打开学生成绩信息表，选择学生成绩信息工作表。第二步：录入数据信息。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表在C26：G26单元格区域输入各科目名称（思修、大学英语、大学计算机、管理学及经济学）；在B27：B37单元格区域输入实考人数、最高分、最低分、90分以上人数、80-89分人数、70-79分人数、60-69分人数、60分以下人数、优秀率、及格率、不合格率。录入文字后，如图7-41所示。第三步：对于单元格内显示不了的文字行或列，如大学英语、90分以上人数、80-89分人数、70-79分人数、60-69分人数、60分以下人数字段所在的单元格（注：作者所设计的单元格显示不了的内容可能与用户的不一样，用户可以根据实际情况操作），选定该单元格，设置单元格格式，操作步骤参见任务7.1，如图7-42所示。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表第四步：选择B26：G37单元区域，右击鼠标，在弹出的菜单栏中选择“设置单元格格式”对齐选项卡中的“文本对齐方式”，设置水平对齐和垂直对齐都“居中”。效果如图7-43所示。2.统计分析数据（1）统计实考人数第一步：选中C27单元格，单击编辑栏上的“插入函数”按钮，弹出“插入函数”面板，选择COUNT函数，如图7-44所示。第二步：在“插入函数”面板上，单击“确定”按钮后，弹出“函数参数”面板，参数Value1选择G5：G24单元区域，如图7-45所示。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表第三步：在“函数参数”面板单击返回按钮，单击“确定”按钮后，统计出思修科目实考人数，如图7-46所示。第四步：选中C27单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向右拖动到单元格G27，即可实现统计所有学生科目实考人数，如图7-47所示。（2）统计最高分第一步：选中C28单元格，单击编辑栏上的“插入函数”按钮，弹出“插入函数”面板，选择MAX函数，如图7-48所示。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表第二步：在“插入函数”面板上，单击“确定”按钮后，弹出“函数参数”面板，参数Value1选择G5：G24单元格区域，如图7-49所示。第三步：在函数参数面板中单击返回按钮，单击“确定”按钮后，统计出思修科目的最高分，如图7-50所示。第四步：选中C28单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向右拖动到单元格G28，即可实现所有学生科目最高分的统计，如图7-51所示。（3）统计最低分上一页下一页返回7.2任务扩展：制作学生成绩统计分析表学生所有科目最低分的统计，操作步骤如同统计学生所有科目最高分的一样，但选择函数不同，应该选择最低分MIN函数，效果如图7-52所示。（4）统计90分以上人数、80-89分人数、70-79分人数、60-69分人数、60分以下人数第一步：选择C30单元格，单击“编辑栏”上的“插入函数”按钮，弹出“插入函数”面板，选择COUNTIF函数，如图7-53所示。第二步：在“插入函数”面板上，单击“确定”按钮后，弹出“函数参数”面板，第一个参数Range计算90分以上人数数据范围，选择G5：G24单元格区域，第二个函数Criteria表示数据区域满足的条件，输入“>=90”，如图7-54所示。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表第三步：在函数参数面板单击“确定”按钮后，即可统计出思修科目90分以上人数，如图7-55所示。第四步：选中C30单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向右拖动到单元格G30，即可实现所有学生科目90分以上人数的统计，如图7-56所示。统计80-89分人数与统计90分以上人数操作有所不同，如果插入COUNTIF函数统计，那么只能统计出80分以上学生人数，而要统计80~90分之间人数，必须使用公式编辑的方法，在公式编辑栏内输入公式：上一页下一页返回7.2任务扩展：制作学生成绩统计分析表=COUNTIF(G5:G24,">=80")-COUNTIF(G5:G24,">=90")或者=COUNTIF(G5:G24,">=80")-C30其他操作方法同上。统计70-79分人数与统计80~89分人数操作步骤一样，在公式编辑栏内输入公式为：=COUNTIF(G5:G24,">=70")-COUNTIF(G5:G24,">=80")或者=COUNTIF(G5:G24,">=70")-C30-C31上一页下一页返回7.2任务扩展：制作学生成绩统计分析表统计60-69分人数与80—89分人数操作步骤一样，在公式编辑栏内输入公式为：=COUNTIF(G5:G24,">=60")-COUNTIF(G5:G24,">=70")或者=COUNTIF(G5:G24,">=60")-C30-C31-C32统计60分以下人数与80-89分人数操作步骤一样，在公式编辑栏内输入公式为：=COUNTIF(G5:G24,"<60")效果如图7-57所示。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表（5）统计优秀率、及格率及不及格率优秀率，即满足大于等于90分的人数与班级实考人数的比率。先统计思修科目的优秀率，选中C35单元格，在公式编辑栏内输入公式“=C30/C27”，单击公式编辑栏内的图标，即可完成统计优秀率，如图7-58所示。为使优秀率单元格中的数据显示美观，要求优秀率单元格数据显示为百分比，并保留小数点后1位。选中C35单元格，右击鼠标，在弹出的菜单中选择“设置单元格格式”，在弹出的对话框的“数字”选项卡中的分类中选择“百分比”，在右边小数位数中输入“1”，如图7-59所示。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表单击“确定”按钮，即可完成C35单元格优秀率数据显示。选中C35单元格，把光标移动到单元格右下角，当鼠标出现实心十字形“+”填充柄时，按住鼠标左键不放，向右拖动到单元格G35，即可实现所有学生科目优秀率的统计，效果如图7-60所示。及格率，即满足大于等于60分的人数与班级实考人数的比率。统计及格率的操作步骤与统计优秀率的一样，在公式编辑栏内输入公式“=（C30+C31+C32+C33）/C27”。不及格率，即满足小于60分的人数与班级实考人数的比率。统计不及格率的操作步骤与统计优秀率的一样，在公式编辑栏内输入公式“=C34/C27”。上一页下一页返回7.2任务扩展：制作学生成绩统计分析表效果如图7-61所示。3.添加表格边框：外边框为细线条，内边框为虚线第一步：选择B26：G37单元格区域后，在Excel2010工具栏中选择图标，选择“其他框架”命令，如图7-62所示。第二步：在弹出的边框设置对话框中，选择“边框”选项卡，将线条样式设置为细线，内边框设置为虚线，如图7-63所示。第三步：线条设置好后，单击“确定”按钮，如图7-64所示。4.添加表头：行标题为科目，列标题为统计上一页下一页返回7.2任务扩展：制作学生成绩统计分析表第一步：选择B26单元格，然后用鼠标右键选择“设置单元格格式”，在弹出的对话框中，选择“边框”选项卡，选择图标，然后单击“确定”按钮，如图7-65所示。第二步：在单元格B26中输入“统计科目”。选择“科目”两字，右击鼠标，选择“设置单元格格式”，在弹出的对话框中，勾选“特殊效果”中的“上标”，如图7-66所示。第三步：用同样的方法，选择“统计”两字，右击鼠标，选择“设置单元格格式”，在弹出的对话框中，勾选“特殊效果”中的“下标”，如图7-67所示。最后调整一下文字，至此，学生成绩统计分析表全部制作完成。上一页返回图7-1学生成绩信息表返回图7-2新建工作簿1文档返回图7-3

C3单元格返回图7-4重新命名工作表Sheet1返回图7-5更改工作表标签颜色返回图7-6输入文本内容返回图7-7学生成绩信息表字段内容返回图7-8“2016-2017第一学期学生成绩信息表”文字居中返回图7-9将A4到O24单元格加边框返回图7-10页边距设置返回图7-11设置单元格文本自动换行返回图7-12文本对齐返回图7-13学生成