案例实践五：Excel-2.ppt

《案例实践五：Excel-2.ppt》由会员分享，可在线阅读，更多相关《案例实践五：Excel-2.ppt（32页珍藏版）》请在三一文库上搜索。

1、电子报表处理软件Excel,案例描述,二、Excel 数据计算（参考效果图） 任务1：在各“*成绩表”中计算“平时成绩”、计算各位同学的总评，其计算规则为：平时成绩15%、期中成绩15%、期末成绩70%。并按总评对其进行单科排名，总分和名次放入所对应的单元格。 任务2：在每个课程成绩工作表中，计算课程各分数段分布人数及比例、平均分、 标准方差。计算平均分和标准方差时，小数点后保留2位小数。 任务3：参考效果图，从各课程成绩表中引用总评成绩，并填入“成绩汇总表”中各相应单元格。计算单科平均分、各科总分。 任务4：参考效果图，在“成绩汇总表”中，按总分进行排名，并利用函数判断各科是否超过单科平均分

2、。,预备知识,Excel中的计算公式 公式的使用 运算符 单元格引用（绝对引用、相对引用、混合引用） 出错信息 函数 函数基本知识 常用函数（SUM、AVERAGE、MAX/MIN、COUNT） 选择性粘贴数据,任务完成,任务1 步骤1：选择“*成绩表”，用AVERAGE公式求得平时成绩填入H列相应单元格，其余同学的平时成绩可利用填充柄来完成计算。选择H3单元格，将鼠标指针移动至该单元格右下角位置，出现黑色十字形时拖动至相应需要填充的最后一个单元格。 步骤2：选中单元格K3，在编辑栏内输入“=ROUND（H3*15%+I3*15%+J3*70%,2)”。按“回车”键确认，求得总评成绩（四舍五入

3、，保留整数），用填充柄向下填充，完成所有学生的总评成绩输入。 步骤3：选中单元格L3，输入“=RANK(K3,$K$3:$K$32,0)”，回车确认，求得的排名依次填入相应单元格。,任务完成,任务2 步骤1：在每个课程成绩工作表中，在学生成绩列表下方建立各成绩段分布人数及比例平均分和标准方差的相应单元格。 步骤2：利用SUMPRODUCT函数计算各个分数段分布人数，比如“=SUMPRODUCT(K3:K32=90)*(K3:K32=100)”可以计算90-100分段的人数。同时计算该分数段的人数分布比例。 步骤3：选定K3:K32，用AVERAGE公式求得该课程的平均分。 步骤3：选定K3:K

4、32，用STDEV公式求得该课程的标准方差。,任务完成,任务3 步骤1：选择“成绩汇总表”，用VLOOKUP公式从各课程成绩表中引用总评成绩并填入相应单元格。比如，在编辑栏中输入“=VLOOKUP(A3,高等数学成绩!$A$3:$K$32,11,0)”，回车确认，再利用填充柄完成其余同学的总评成绩。 步骤2：重复步骤1，完成其他四门课程总评成绩的输入。 步骤3：选中单元格C33，单击编辑栏中的“插入函数”按钮，在弹出的“插入函数”对话框中选择“AVERAGE”函数，在打开的AVERAGE“函数参数”对话框中的第一个参数“Number1”文本框中输入C3：C32（也可以通过选择区域完成输入），单

5、击“确定”按钮完成第一位同学高等数学平均分的计算。 步骤4：其余同学可利用填充柄来完成计算。 步骤5：总分的计算可参考步骤3和步骤4，将AVERAGE函数换成SUM函数，并修改相应的数据区域C3:G3即可。,任务完成,任务4 步骤1：选中单元格K3，在编辑栏内输入“=RANK(J3,J$3:J$32,0)”。按“回车”键确认，求得按总分的排名，用填充柄向下填充，完成所有学生的排名。 步骤2：选中单元格L3，在编辑栏内输入=IF(AND(E3=$E$33,F3=$F$33,G3=$G$33,H3=$H$33,I3=$I$33),“是“,“否“)， IF函数是判断条件（第一个参数），条件为真返回“

6、是”，为假返回“否”。以此求得单科是否超过平均分。用填充柄向下填充，完成对所有同学的判断。,案例描述,三、工作表格式化 任务1：设置“学生信息”表标题格式为隶书、22号字，并要求合并及居中。 任务2：将“成绩汇总表”中的“单科平均分”保留两位小数，并将各列以“最合适列宽”显示。 任务3：将“成绩汇总表”中各科成绩低于60分的学生成绩用红色的字体。黄色的底纹显示。 任务4：为“学生信息”表中数据所在区域加田字框：粗线外框，细线内框。,预备知识,工作表的格式化 数字的格式化 字体的设置 设置列宽与行高 数据对齐方式设置 设置工作表的边框与底纹 自动套用格式设置 条件格式的设置,任务完成,任务1 步

7、骤1：选定“学生信息”表。 步骤2：选定A1：G1单元格区域，利用“格式”菜单下的“单元格”命令，或直接右击选中的单元格区域，在弹出的快捷菜单中选择“设置单元格格式”，弹出“单元格格式”对话框。 步骤3：在“单元格格式”对话框中，单击“字体”选项卡，设置相应的字体为隶书，字号为22。 步骤4：单击“对齐”选项卡，水平对齐方式选择“居中”，并选中其中的“合并单元格”复选框，最后单击“确定”按钮。,任务完成,任务2 步骤1：选定“成绩汇总表”。 步骤2：选定C33：G33单元格区域，单击“格式”菜单下的“单元格”命令，或直接右击选中的单元格区域，在弹出的快捷菜单中选择“设置单元格格式”，弹出“单元

8、格格式”对话框。 步骤3：在“单元格格式”对话框中，单击“数字”选项卡，在“分类”列表框中选中“数值”，设置小数位数为2，单击“确定”按钮。 步骤4：选定整张“成绩汇总表”中的数据区域，单击“格式”菜单下的“列”“最合适的列宽”命令，即可完成最合适的列宽的设置。,任务完成,任务3 步骤1：选定“成绩汇总表”。 步骤2：选定C3：G32单元格区域，单击“格式”菜单下的“条件格式”命令，弹出“条件格式对话框”。 步骤3：在“条件格式”对话框中的“条件1”栏中的3个组合框中分别选择或输入“单元格数值”、“小于”、“60” 。 步骤4：单击“条件格式”对话框中的“格式”按钮，在弹出的“单元格格式”对话

9、框的“字体”选项卡中选择红色字体，在“图案”选项卡中选择黄色底纹，最后单击“确定”按钮。,任务完成,任务4 步骤1：选定“学生信息”表。 步骤2：选定数据区域A1：G32，单击“格式”菜单下的“单元格”命令，或直接右击“设置单元格格式”命令，弹出“单元格格式”对话框。 步骤3：在“单元格格式”对话框中选择“边框”选项卡，在该选项卡中先选择粗线的线条样式，再单击“预置”栏内的“外边框”按钮；同样再选择细线的线条样式，单击“预置”栏内的“内部”按钮，最后单击“确定”按钮。,案例描述,四、数据库管理与分析 任务1：将“成绩汇总表”中的 “各门课程成绩”和“总分”列数据复制至“成绩表备份”中对应区域，

10、在 “姓名”列后插入一列“专业”，并从“学生信息”表中复制相关信息，填入相应单元格。 任务2：将“成绩表备份”表中的数据按总分降序排列，若总分相同的，则按“大学英语”降序排列。 任务3：查看“成绩备份表”中专业是“软件工程”且总分大于400分同学的相关信息。 任务4：查看“成绩备份表”中不及格的学生的相关信息，并将其复制到该表下方的相关区域。,案例描述,四、数据库管理与分析（续） 任务5：在“成绩备份表”中按专业对“计算机导论”课程进行平均分的分类汇总。 任务6：在“成绩备份表”中创建一个图表，显示每个课程的各个专业平均分比较，显示在J3:R20区域，要求以“各专业课程平均分比较”为标题，以“

11、专业平均值”作为图例项，图例位于图表右方。 任务7：在“成绩汇总表”中，建立一张数据透视表，要求按专业比较“高等数学”课程男、女生的平均成绩。,预备知识,数据库的概念 数据库（字段、记录） 数据清单 创建数据清单的规则 记录的排序 记录的筛选 记录的分类汇总,图表基本概念 建立图表基本步骤 数据点和数据系列 坐标轴标志 分类 图例,任务完成,任务1 步骤1：选中“成绩汇总表”，选定数据区域C2:H32，并右击，在快捷菜单中选择“复制”命令，选择“成绩备份表”中的C2单元格，右击从弹出的快捷菜单中选择“选择性粘贴”命令，在弹出的“选择性粘贴”对话框中选定粘贴“数值”，并单击“确定”按钮。 步骤2

12、：选择“成绩备份表”，单击“姓名”列后一列的列标（本任务为C），选中整列，右键弹出快捷菜单，选择“插入”命令，在“姓名”列后就生成了一空列，在单元格C2输入字段名称“专业”，再从“学生信息”表中将“专业”相关信息复制到产生的空列中（也可利用VLOOKUP函数）。,任务完成,任务2 步骤1：选择“成绩表备份”数据表数据清单中的任意一个单元格，单击“数据”菜单下的“排序”命令，弹出“排序”对话框。 步骤2：在弹出的“排序”对话框中选择主要关键字为“总分”，选中“降序”；次要关键字为“大学英语”，并选中“降序”，最后单击“确定”按钮，完成排序操作。,任务完成,任务3 步骤1：选择“成绩表备份”工作表

13、数据清单所在区域中的任一单元格，单击“数据”菜单下的“筛选”“自动筛选”命令，此时在每个列标题右侧出现一个向下的箭头。 步骤2：在“专业”字段名右侧下拉列表框中选择“软件工程”，此时系统将筛选出软件工程专业的所有记录。 步骤3：在“总分”字段名右侧下拉列表框中选择“自定义”，在弹出的“自定义自动筛选方式”对话框中输入相应条件（总分大于400），单击“确定”按钮后，数据清单中显示的信息即是任务中所需的信息。,任务完成,任务4 步骤1：在“成绩表备份”数据清单所在区域以外空白位置设置一个条件区域，本任务在D34:H39区域设置。 步骤2：选中“成绩表备份”数据清单所在区域中的任一单元格，单击“数据

14、”菜单下的“筛选”“高级筛选”命令，弹出“高级筛选”对话框。 步骤3：设置相关参数。“列表区域”的选择默认（将数据源所在的区域全选）；“条件区域”在本任务中选择E34：I39区域；“方式”设置为“将筛选结果复制到其他位置”，并在“复制到”栏内输入用来显示符合条件的信息的区域（可只输入该区域的首个单元格，本任务中选择A40），最后单击“确定”按钮。,任务完成,任务5 步骤1：选中“成绩表备份”工作表数据清单，单击“专业”字段所在列数据区域任一单元格，利用常用工具栏中的“升序排列”按钮或“降序排列”按钮将其进行排序。 步骤2：将活动单元格定位于“成绩表备份”数据区域任一位置，单击“数据”菜单下的“

15、分类汇总”命令，弹出“分类汇总”对话框。 步骤3：在弹出的“分类汇总”对话框中设置“分类字段”为“专业”，“汇总方式”为平均值，在“选定汇总项”中选中“计算机导论”，其他选项设置为默认，最后单击“确定”按钮。,任务完成,任务6 步骤1：选定“成绩备份表”。 步骤2：参照任务5，按专业对五门课程进行平均分的分类汇总。 步骤3：选中“成绩汇总表”数据区域中任何一个单元格，单击“插入”菜单下的“图表”命令，或者直接单击常用工具栏上的“图表向导”按钮，弹出“图表向导-4步骤之1-图表类型”对话框，此处Excel提供了多种不同的图表类型，我们选择图表类型为“柱形图”，子图表类型为“簇状柱形图”，并单击“

16、下一步”按钮。 步骤4：在弹出的“图表向导-4步骤之2-图表源数据”对话框中，用户可单击此处的“数据区域”右侧的“压缩对话框”按钮来选择数据区域，本任务选择C2:H2,C6:H6,C11:H11,C16:H16,C23:H23,C32:H32,C38:H38(按实际情况填写)区域，用户还可以选择系列产生在行或是列，此处选择 “行”，单击“下一步”按钮。,任务完成,任务6（续） 步骤5：在弹出的图表向导-4步骤之3-图表选项”对话框中，选择标题选项卡，设置图表标题为“各专业课程平均分比较”，再选择“图例”选项卡，选择图例位于右方，单击“下一步”。 步骤6：在弹出的图表向导-4步骤之4-图表位置”

17、对话框中，可以确定图表的位置。用户可把图表放在一张新的工作表中插入到工作簿文件中，称为图表工作表；也可让图表与原始数据放在一起，称为嵌入式图表。在本任务中选择默认（嵌入式图表），单击“完成”按钮。 步骤7：改变步骤5中插入图表的大小和位置，将其放置到J3:R20区域。,任务完成,任务7 步骤1：选定“成绩汇总表”。插入“专业”和“性别”列。 步骤2：选中“成绩汇总表”数据清单中任一单元格，单击“数据”菜单，选择“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导3步骤之1”对话框。在该对话框中选中数据源类型为“Microsoft Office Excel 数据列表或数据库”，在所创

18、建的报表类型中选中“数据透视表”，单击“下一步”按钮。 步骤3：在弹出的“数据透视表和数据透视图向导3步骤之2”对话框中，选中要建立数据透视表的数据源区域，本任务中选择A2:J32区域，然后单击“下一步按钮。,任务完成,任务7（续） 步骤4：在弹出的“数据透视表和数据透视图向导3步骤之3”对话框中，确定透视表的显示位置，本任务中选择在现有工作表中显示数据透视表，并可设置布局。 步骤5：若步骤3中单击了“布局”按钮，弹出“数据透视表和数据透视图向导布局”对话框，在该对话框中，用户可将右边的字段名拖到左边的图表结构图上，构造数据透视表，本任务是将“专业”字段拖到行上，“性别”字段拖到列上，“高等数

19、学”字段拖到数据区域中。并双击数据区域字段选择汇总方式为平均值，单击“确定”按钮回到“数据透视表和数据透视图向导3步骤之3”对话框，最后单击“完成”按钮。,案例描述,五、工作表的打印 任务1：为“学生信息”表添加页脚，中间位置内容为总页数和页码，靠右位置内容为当前的制作日期。 任务2：设置“学生信息”表上、下页边距各3厘米，并水平居中。 任务3：对以上设置进行打印预览，满意后打印输出。,预备知识,页面设置 页面 页边距 页眉/页脚 工作表 打印区域 打印输出,任务完成,任务1 步骤1：选定“学生信息”表，单击“文件”菜单下的“页面设置”对话框，单击“页眉/页脚”选项卡，本任务中单击“自定义页脚

20、”按钮来自定义页脚。 步骤2：在弹出的“页脚”对话框的中部位置输入文本“总页数：”，并单击相应图标按钮，再输入文本“当前页：”并单击相应图标按钮，在靠右位置输入文本“制作日期：”并单击相应图标按钮，最后单击“确定”按钮回到“页面设置”对话框并单击“确定”按钮。,任务完成,任务2 步骤1：选定“学生信息”表，单击“文件”菜单下的“页面设置”命令，弹出“页面设置”对话框。 步骤2：在“页面设置”对话框中选择“页边距”选项卡，设置上、下边距各3厘米，并选中下方“居中方式”为水平，设置结束后单击“确定”按钮。,任务完成,任务3 步骤：单击“文件”菜单下的“打印”命令，弹出“打印内容”对话框，用户可在此

21、对话框中设置打印机、打印范围和打印内容及份数等选项。确认无误后，即可单击“确定”按钮确定进行打印输出。对于设置效果如何，在打印前可使用“文件”菜单下的“打印预览”命令进行查看。,作业要求,1、虚拟一些学生的信息（不少于20人），新建学生信息工作表，包括：学号、姓名、性别、出生年月、年龄、专业、联系方式； 2、新建学生各个课程的成绩工作表（课程不少于5门），每个表包括：平时成绩（5次）、平时成绩总评、期中、期末、期末总评； 3、输入数据的有效性（成绩采用百分制，符合常规要求）； 4、平时成绩总评(平时成绩平均值)、期末总评(平时成绩总评*15%+期中成绩*15%+期末成绩*70%)根据公式计算得分；,作业要求,5、在每个课程成绩工作表中，计算课程各分数段分布人数及比例、平均分、标准方差；计算平均分和标准方差时，小数点后保留2位小数； 6、最后生成学生成绩汇总表，包括每个学生的各科成绩期末总评、总分、排名，并对每个学生判断“各科成绩是否均超过平均分”； 7、新建专业分析汇总表，对各个专业的学生进行分类汇总，要求分专业计算各科成绩平均分，同时建立图表（柱状图）来 显示各专业课程平均分比较情况。,