表格excel的教学.doc

实训一 工作表的编辑与格式化制作销售统计表作业提交要求：在D盘新建一个文件夹，该文件夹命名为“学号+姓名”，将完成后的文件“EX01.xls”和“EXLX01.xls”存放到该文件夹中，并将该文件夹提交至教师机。一、 任务介绍又到了年末，颐星数码城要求商城内所有商家填写一份销售统计表用做销售量调查统计。数码城办公室的主任助理Joyce需要制作销售统计表的模板供商家填写（除商品信息外其余信息要求手工填写），如图4-1所示。金鑫电脑公司的销售统计员根据要求填写了该表，如图4-2所示。图4-1 销售统计表模板图4-2 转置粘贴数据并计算各季度销售总量u 根据要求制作并修饰销售统计表。u 转置粘贴各季度销售量，计算各季度销售总量。二、 任务分析为了顺利完成本次工作任务，Joyce和各商家的销售统计员需要应用到一些知识。u 统计报表的标准样式。u Excel电子表格处理软件中的数据输入、转置粘贴、表格的格式化、合计公式的使用、直线的绘制。u Excel文件的保存。u Excel电子表格的排版及打印。三、 任务分解本任务可以分解为以下个子任务：子任务1 建立销售统计表子任务2 格式化销售统计表子任务3 保存文件子任务4 输入数据并求合计子任务5 排版并打印销售统计表四、 任务指导子任务1、建立销售统计表（1）启动Excel 2003，新建工作簿文件。（2）在sheet1相应的单元格中输入如图4-3所示的内容。图4-3 在sheet1中建立销售统计表技巧提示：A4:A10中的序号使用序列填充的方法，有多种方法，如下：方法1：使用填充句柄填充等差数列，步骤如下：步骤1：在A4和A5分别输入1和2；步骤2：选中A4:A5；步骤3：拖动填充句柄至A10。方法2：使用Ctrl和填充句柄填充，步骤如下：步骤1：在A4输入1；步骤2：拖动填充句柄至A10，同时按住键盘Ctrl键直至松开鼠标左键。方法3：使用菜单命令填充，步骤如下：步骤1：在A4输入1；步骤2：选中A4:A10；步骤3：执行菜单“编辑”“填充”“序列”命令，依下图在对话框中设置：图4-4 填充序列对话框注：因为选中了A4:A10区域，所以无需填写终止值，如果步骤2缺省，则需要指定终止值即7。提示2：B4:B10中的产品编号使用输入文本字符及填充的方法，步骤如下：步骤1：在B4输入“01101”（注意英文半角单引号）；步骤2：拖动填充句柄至B10。提示3：E3:H3可使用自定义填充的方法，步骤如下：步骤1：在E3输入“一季度”；步骤2：执行菜单“工具”“选项”命令，打开“自定义序列”选项卡，选中“自定义序列”单选框中的“新序列”，在右侧输入序列中输入如下图所示的序列，回车换行，完成后单击“添加”按钮，确定：图4-5 选项对话框的自定义序列选项卡步骤3：选中E3，拖动句柄至H3。子任务2、格式化销售统计表（格式化效果如图4-1所示）（1）格式化标题：合并A1:H1并将标题居中，设置标题格式为黑体、18号、加粗。步骤1：选择A1:H1，单击工具栏的“合并及居中”按钮；步骤2：在工具栏中选择字体为18号黑体、加粗。（2）格式化表头：设置第三行表头格式为楷体、12号、加粗、居中，并为表头单元格填充灰色-25%的底纹。步骤1：选择A3和G3；步骤2：在工具栏中选择字体为12号楷体、加粗、居中，填充颜色为“灰色-25%”。（3）格式化数据：合并A11:D11，设置A4:H11格式为宋体、12号、居中。步骤1：选择A11:D11，单击工具栏的“合并及居中”按钮；步骤2：选择A4:H11，在工具栏中选择字体为12号宋体、居中。（4）格式化表格其他部分：设置第二行的格式为仿宋、12号、加粗，“制表人：”单元格的格式为宋体、12号。步骤1：选择A2:H2，在工具栏中选择字体为12号仿宋、加粗、居中；步骤2：选择G14，在工具栏中选择字体为12号宋体。步骤3：使用绘图工具栏的直线工具在相应的内容后面绘制直线，并适当地调整其位置。（5）添加表格线：为A3:H11添加单线边框线。步骤1：选择A3:H11；步骤2：单击工具栏“边框”按钮下拉菜单中的“所有框线”命令。子任务3、保存文件.将文件以“EXLX01.xls”为文件名保存在教师指定的文件夹中。步骤1：单击工具栏的“保存”按钮；步骤2：在“保存位置”中选择指定文件夹，将文件名修改为“EXLX01”，文件类型选择“Microsoft office工作簿”；步骤3：单击“保存”。子任务4、输入数据并求合计（格式化效果如图4-2所示）（1）从教师提供的“报表”文档中将所需的数据转置粘贴到文档中，完成销售数据的录入。步骤1：打开“报表”文档，选择B1:H6；步骤2：单击工具栏的“复制”按钮；步骤3：打开EXLX01文档，单击C4单元格，再单击编辑菜单的“选择性粘贴”按钮，选中“转置”，单击确定。（2）在E11:H11分别求出一季度四季度的销售合计总量。步骤1：选择E11:H11；步骤2：单击工具栏的“自动求和”按钮。（3）将文件以“EX01”为文件名另存在同一文件夹中。步骤1：单击文件菜单的的“另存为”按钮；步骤2：在“保存位置”中选择指定文件夹，将文件名修改为“EX01”，文件类型选择“Microsoft office工作簿”；步骤3：单击“保存”。至此，EXLX01文档的保存效果如图4-1所示，EX01文档如图4-2所示。子任务5、排版并打印销售统计表为了更好的视觉效果和充分利用纸张将销售统计表打印出来，需要在一页A4纸内排列，效果如下图所示。图4-6 在一张A4纸中排列3份销售统计表（1）设置标题行的行高为25、第2行行高为20，第A列的宽度为5、第B-D列的宽度为10、第E-H列的宽度为12。步骤1：重新打开EXLX01文档，单击行号1，在快捷菜单中选择“行高”命令，在对话框中输入25，单击确定。同样方法设置第2行行高为20；步骤2：单击列号A，在快捷菜单中选择“列宽”命令，在对话框中输入5，回车确定。拖动鼠标选择列号B-D，同样方法在对话框中输入10。同样方法设置第E-H列的宽度为12。（2）通过设置页面设置纸张为A4、方向为纵向，上下左右页边距各为1、页眉页脚边距为0，在工作表中复制出2个销售统计表。步骤1：执行菜单“文件”“页面设置”命令，打开页面设置对话框：在“页面”选项卡中设置纸张方向为纵向，纸张大小为A4；在“页边距”选项卡中设置上下左右页边距各为1、页眉页脚边距为0；确定；步骤2：拖动选择第114行，单击工具栏的“复制”按钮；单击A17，单击工具栏的“粘贴”按钮；同样方法在A33处粘贴。（3）保存文件。步骤：单击工具栏的“保存”按钮。至此，EXLX01文档的保存效果如图4-6所示。（4）打印销售统计表。步骤1：执行“文件”菜单中的“打印”命令。注意：在打印文档前，请确认以下准备工作是否完成：（1）操作系统中已安装打印机。（2）打印机与计算机连接正常。（3）确保打印机电源打开。步骤2：在出现的“打印内容”对话框中设置，如图4-7。图4-7 设置打印各选项在“打印内容”对话框中主要需要设置“名称”、“打印内容”等选项。（1）名称：即为选择您使用的打印机，包括连接到本计算机的打印机或者网络打印机。（2）打印内容：指示打印的内容是选定的单元格区域、整个工作簿的所有工作表、选定的某个或某几个工作表。五、 实训小结在本次实训中我们将制作销售统计表这项工作分解为4个子任务，通过建立销售统计表，格式化销售统计表，然后将文件保存；在表中输入数据并求销售量合计，并将文件另保存；最后对文档进行必要的页面排版，打印多份销售统计表。通过这次实训，我们可以基本掌握Excel电子表格处理软件中的文档的建立与保存、数据的输入（填充）与基本计算、表格的格式化、文档的排版及打印等相关知识。六、 课后拓展练习利用电子表格处理软件Excel建立如下所示的成绩表及后面的成绩分析统计表，其中单元格的高度与宽度可适当调整、记录行数任意，但务必保证所有内容可以在一页中显示。然后在工作簿中复制出三张相同的工作表，将四张工作表的标签分别更名为：08生物（1）班、08生物（2）班、08生物（3）班、08生物（4）班。实训二 公式与函数的使用制作成绩考核登记表一、 任务介绍学院教务部门要求各系部制作统一的成绩考核登记表，记录学生整个学期的学习综合情况。根据学院要求，生物系根据该系的具体情况制作了该系计算机应用基础课程的成绩考核登记表（如图4-9所示），并在成绩表后设计了成绩统计分析表格（如图4-10所示），从而全面体现各班该门课程学习的总体情况。08（1）班的班委会根据该班学生08-09学年第1学期的计算机应用基础课程的考核情况填写了相应的信息及各项目的成绩，现需要统计各个大项并作全班成绩分析。图4-9 成绩考核登记表样板图4-10 成绩考核登记表后的成绩分析u 计算平时作业的综合评价成绩、学期总评成绩及对应的综合排名和等级。u 统计分析各个项目的（男/女）总分、平均分、最高分、最低分、及格人数及及格率、优秀人数及优秀率、平时成绩与总评成绩的各分数段人数等。二、 任务分析为了对计算机应用基础课程的成绩做全面的统计分析，生物系08（1）班的班干部需要应用到公式和函数的相关知识。此外，由于班级人数较多，成绩考核登记表需要多页排版。u 基本公式的输入、编辑和填充。u 逻辑函数IF的使用。u 统计函数AVERAGE、MAX、MIN 、RANK及COUNTIF、FREQUENCY的使用。u 数学与三角函数SUM和SUMIF的使用。u 多页电子表格的排版。三、 任务分解本任务可以分解为以下个子任务：子任务1 计算平时作业的综合评价、学期总评并统计各分数段人数子任务2 计算个人综合排名及等级子任务3 统计各项的（男/女）总分、平均分、最高分及最低分子任务4 统计各项及格人数与及格率、优秀人数与优秀率子任务5 为多页成绩考核登记表排版四、 任务指导子任务1、在文件EXLX02.xls中计算平时作业的综合评价、学期总评并统计各分数段人数（1）用公式计算平时作业的综合评价与学期总评，结果保留一位小数，公式如下：综合评价=个人作业的平均*40%+小组作业的平均*60%学期总评=综合评价*30%+机试*40%+笔试*20%+考勤+课堂表现。步骤1：选择H7，单击编辑栏；步骤2：在编辑栏中输入：=(D7+E7)/2)*40%+(F7+G7)/2)*60%方法2：=AVERAGE(D7,E7)*40%+AVERAGE(F7,G7)*60%；步骤3：执行菜单“格式”“单元格”命令，打开“数字”选项卡，选择“数值”分类，并定义小数位数为1；步骤4：双击H7的填充句柄；步骤58：学期总评的计算方法与综合评价相似，提供参考公式如下：=H7*30%+I7*40%+ J7*20%+K7+L7（2）用统计函数FREQUENCY计算平时成绩及学期总评的分布情况，其中60分以下（不含60）为“不及格”、6085分（不含85）为“良好”、85分及以上为“优秀”。步骤1：在M60和M61分别输入59、84；步骤2：选择N60:N62区域，执行菜单“插入”“函数”命令，选择“统计”类别中的FREQUENCY函数；步骤3：单击Data_array后面的输入框，选择H7:H56作为参数，单击Bins_array后面的输入框，选择M60:M61作为参数；步骤4：然后同时按下Shift+Ctrl+Enter输入函数；图4-11 使用FREQUENCY函数计算平时成绩的分布步骤58：学期总评分布的计算方法与平时成绩相似，提供参考参数如下图所示：图4-12 使用FREQUENCY函数计算学期总评分布的参数子任务2、计算个人综合排名及等级（1）用统计函数RANK计算每个学生学期总评的综合排名。步骤1：选择N7，单击编辑栏旁边的“插入函数”按钮，选择“统计”类别中的RANK函数；步骤2：单击Number后面的输入框，选择M7作为参数，单击Ref后面的输入框，选择$M$7:$M$56作为参数，忽略参数Order，确定；步骤3：双击H7的填充句柄；（2）用逻辑函数IF分析每个学生成绩等级，其中60分以下（不含60）为“不及格”、6085分（不含85）为“良好”、85分及以上为“优秀”，检查与前面的统计结果是否相同，如果不相同是为什么？步骤1：选择O7；步骤2：在编辑栏输入：=IF(M760,不及格,IF(M785,良好,优秀)；步骤3：双击O7的填充句柄。提示：学期总评的计算实际结果均为一位小数，故FREQUENCY函数的分段点应该改为59.9和84.9才能计算出正确结果。子任务3、统计各项的（男/女）总分、平均分、最高分及最低分（1）用数学与三角函数SUM计算各种考核方式的成绩之和。步骤1：选择D58；步骤2：单击自动求和按钮；步骤3：将参数修改为D7:D56；步骤4：拖动D58的填充句柄至J58。（2）用数学与三角函数SUMIF统计男、女生各项总分。步骤1：选择D59，单击编辑栏旁边的“插入函数”按钮，选择“数学与三角函数”类别中的SUMIF函数；步骤2：逐个输入如下图所示的三个参数，然后确定；图4-13 使用SUMIF函数统计男生第一次个人作业总分步骤3：拖动D59的填充句柄至J59；步骤46：统计女生各项总分的方法与上相似，其中D60单元格的公式参考如下：=SUMIF($C$7:$C$56,女,D7:D56)。（3）用统计函数AVERAGE、MAX、MIN分别统计各项平均分、最高分和最低分。步骤1：选择D61；步骤2：在自动求和按钮的下拉菜单中选择“平均值”；步骤3：将参数修改为D7:D56；步骤4：拖动D61的填充句柄至J61；步骤512：计算最高分和最低分的方法与上相似。子任务4、统计各项及格人数与及格率、优秀人数与优秀率（1）用COUNTIF函数统计各项及格人数与优秀人数。步骤1：选择D64，单击编辑栏旁边的“插入函数”按钮，选择“统计”类别中的COUNTIF函数；步骤2：逐个输入如下图所示的三个参数，然后确定；图4-14 使用COUNIF函数统计第一次个人作业的及格人数步骤3：拖动D64的填充句柄至J64；步骤4步骤6：优秀人数的统计与上相似，其中D66单元格的公式参数如下图所示：图4-15 使用COUNIF函数统计第一次个人作业的优秀人数（2）用公式计算各项成绩的及格率与优秀率，结果用百分比表示。步骤1：选择D65，在编辑栏输入：=D64/50；步骤2：单击工具栏的“百分比样式”按钮；步骤3：拖动D65的填充句柄至J65；步骤4步骤6：优秀率的计算与上相似。子任务5、为多页成绩考核登记表排版由于成绩考核登记表需要打印上交，需要对表格进行排版。（1）设置纸张大小为A4、方向为纵向，上下页边距为2、左右为1.5，页眉边距为0、页脚边距为1，自定义页脚内容为日期左对齐、页码右对齐，参考效果如图所示：图4-16 页脚样张步骤1：执行菜单“文件”“页面设置”命令，打开页面设置对话框，在“页面”选项卡中设置纸张方向为纵向，纸张大小为A4；在“页边距”选项卡中设置上下页边距各为2、左右页边距各为1.5，页眉边距为0、页脚边距为1；步骤2：在“页眉/页脚”选项卡单击“自定义页脚”按钮，在“左”框中单击插入当前日期的按钮，在“右”框中输入“第”、“页”并在两个字中间单击插入页码的按钮：图4-17设置页脚对话框（2）设置16行为打印标题行。在第2页中页应当能够看到表格的标题和表头，因此需要将16行设置为打印标题行。步骤1：执行菜单“文件”“页面设置”命令，打开页面设置对话框；步骤2：在“工作表”选项卡中单击顶端标题行后面的输入框，拖动选择工作表的16行，确定。在预览效果中可以看到每一页都有标题和表头。五、 实训小结在本次实训中我们将制作成绩考核登记表这项工作分解为5个子任务，在已经建立好表格的情况下通过使用基本公式及逻辑函数、统计函数和数学与三角函数统计分析各项数据，全面、多角度地评价了全班的学习情况。通过这次实训，我们可以基本掌握Excel电子表格处理软件中的公式的基本编辑及常见函数的使用。六、 课后拓展练习调查你班上十位同学及他们亲人的收入情况，根据调查的数据建立收入一览表（样张如下图所示），然后利用所学的公式和函数作如下统计（结合样张，仅作参考，对应素材“收入一览表.xls”文档）：1、根据编号和姓名分别统计总人数2、求男职工的人数3、求年龄大于30岁的人数4、求公务员年收入的总和5、求最低年收入6、求第2高年收入7、求张越的收入排在第几多8、按年收入进行排名9、统计年龄30岁，30(包括30)岁到40岁，40(包括40)岁到50岁，50岁以上的各阶段人数10、根据以下条件填写代号：男医生代号为MD，工人或公务员代号均为W，其余的为空白11、根据姓名求姓氏12、查找陈醉的年龄图4-18 收入一览表实训三 财务函数的使用制作存贷款预算表一、 任务介绍良好的理财手段是保证生活质量的便捷途径，存贷款及投资预算和方案选择能帮助我们合理地理财。不论我们是贷款，还是参加银行的零存整取或是投资，Excel中提供的财务函数能帮助我们快速计算出按照既定方案需要付出的代价或是可以获取的回报。在这个实训中，首先我们需要学习如何根据既定方案计算简单的贷款代价和存款（投资）回报。然后我们就可以调整方案的细节（如存、贷款年限、考虑利率的变化有等因素），决定我们的理财方案。u 下图是根据每月存款额、年利率及存款期限计算出的最终存款额：图4-19 存款计算表1u 根据每月存款额、年利率、存款期限及期望值计算出的现值存入数额：图4-20 存款计算表2u 根据每月存款额、年利率、存款期限计算出的税后本息总额：图4-21 存款计算表3u 根据贷款总额、年利率及贷款期限计算出的每月还款额：图4-22 还款计算表1u 根据一项已经购买了的保险年金的月底回报、投资收益率、投资年限计算出的该份保险年金的价值，然后与购买金额对比看是否合算：图4-23 投资预算表以及，u 当每月存款额与存款期限固定不变时，不同的年利率对应的存款总额：图4-24 零存整取单变量模拟运算表u 当存款期限为10年不变，不同每月存款额及年利率对应的存款总额：图4-25 零存整取双变量模拟运算表u 当贷款总额与贷款期限固定时不同的年利率对应的月偿还额，或是贷款总额与年利率固定时不同的贷款年限对应的月偿还额，或是贷款年限10年不变时不同的贷款总额及年利率对应的月偿还额：图4-26 贷款单变量及双变量模拟运算表二、 任务分析为了顺利完成本次工作任务，我们除了需要巩固复习函数的基本编辑与使用等基本知识外，还需要具备以下知识。u 对基本存贷款及投资方式的了解及相应的计算方法。u 财务函数FV、PV及PMT的使用。u 单变量模拟运算的计算方法。u 双变量模拟运算的计算方法。三、 任务分解本任务可以分解为以下个子任务：子任务1 存款预算子任务2 贷款预算子任务3 投资预算子任务4 存款方案选择子任务5 贷款方案选择四、 任务指导打开EXLX05.xls文件，在相应工作表中根据存贷款方案完成预算。子任务1、存款预算：（1）某人每月存款3000元，共累计存款20年（240个月），已知存款利率为2.50%，计算最终存款额，结果添加货币符号“￥”并保留2位小数。步骤1：在C6单元格中插入FV函数，参数如下图所示：图4-27 存款计算表1的FV函数的相关参数及结果注意条件中的每月存款额应为负值，否则计算结果将为负值。步骤2：单击结果单元格，选择“格式”“单元格”命令，在“数字”选项卡中单击“货币”分类，设置小数位数为2。（2）某人预算在10年后使存款数额达到300000元，现在每月存入2000元，年利率为4.125%，计算其开始应存入银行的现值数额，结果添加货币符号“￥”并保留2位小数。提示：在C14单元格中插入PV函数，参数如下图所示：图4-28 存款计算表2的PV函数的相关参数及结果设置货币类型与小数位数的方法与上相同，不再赘述。（3）某人参加银行的零存整取储蓄，每月存入1500元，年利率为3%，请在相应单元格用函数公式计算出2年期满后的减除利息税后的本息总和,设置单元格格式例如为：￥1500.22。（注:利息税为6%,必须使用公式,取2位小数位数）步骤1：在C18：C20单元格分别输入相应的参数3%、24、-1500；步骤2：在C21单元格输入函数，参加下列提示：提示：税后本息总和=本息总和-利息税 利息税=利息*利率 利息=本息总和-本金即：税后本息总和=本息总和-（本息总和-本金）*利率。根据上述公式，在C21单元格输入如下所示的公式即可：=FV(C18/12,C19,C20)-(FV(C18/12,C19,C20)-C19*(-C20)*6%注意公式中的本金计算，引用C20单元格内容应添加负号。子任务2、贷款预算某人向银行贷款100000元，贷款年限为10年，已知贷款年利率为8%，在C6单元格中利用函数计算出“每月应还款”，结果保留2位小数。提示：在C6单元格中输入PMT函数，参数如下图所示：图4-29 贷款计算表的PMT函数的相关参数及结果子任务3、投资预算假设要购买一项保险年金，该保险可以在今后20年内，每月末回报￥600.此项年金的购买成本为80000，假定投资回报率为8%。该项投资合算吗？提示：在C7单元格输入PV函数：=PV(C4/12,C5*12,C3)。计算结果为￥-71,732.58即总收益率为￥-71,732.58，而购买成本为80000元，因此该项投资不合算。子任务4、存款方案选择（1）已知一项零存整取方案的每月存款额为1500元，存款期限为120个月，请根据年利率的不同计算得到的存款总额。步骤1：在C7单元格中输入FV函数：=FV(B7/12,C5,-C4)；步骤2：选择B7：C11单元格区域，选择“数据”“单元格”命令，在“输入引用列单元格”后面输入框中单击，选择B7单元格，单击确定。结果如图4-24所示。（2）已知一项零存整取方案的存款期限为120个月，请根据年利率的不同和月存款额的不同计算得到的存款总额。步骤1：在B15单元格输入FV函数：=FV(B22/12,120,G15)；步骤2：选择B15：F21单元格区域，选择“数据”“单元格”命令，在“输入引用行单元格”后面输入框中单击，选择G15单元格，单击确定；在“输入引用列单元格”后面输入框中单击，选择B22单元格，单击确定。结果如图4-25所示。子任务5、贷款方案选择（1）已知一项贷款方案的贷款额为100000元，贷款期限为120个月，请根据年利率的不同计算月偿还额。步骤1：在C7单元格中输入PMT函数：=PMT(B7/12,C5,C4)；步骤2：选择B7：C11单元格区域，选择“数据”“单元格”命令，在“输入引用列单元格”后面输入框中单击，选择B7单元格，单击确定。结果如图4-26左上所示。（2）已知一项贷款方案的贷款额为6000000元，年利率为6%，请根据贷款期限的不同计算月偿还额。步骤1：在F7单元格中输入PMT函数：=PMT(F5/12,E7*12,F4)；步骤2：选择E7：F11单元格区域，选择“数据”“单元格”命令，在“输入引用列单元格”后面输入框中单击，选择E7单元格，单击确定。结果如图4-26右上所示。（3）已知一项零存整取方案的存款期限为120个月，请根据年利率的不同和月存款额的不同计算得到的存款总额。步骤1：在B15单元格输入FV函数：=FV(B22/12,120,G15)；步骤2：选择B15：F22单元格区域，选择“数据”“单元格”命令，在“输入引用行单元格”后面输入框中单击，选择G15单元格，单击确定；在“输入引用列单元格”后面输入框中单击，选择B22单元格，单击确定。结果如图4-25所示。五、 实训小结在本次实训中我们将制作存贷款预算表这项工作分解为5个子任务，分别是存款预算、贷款预算、投资预算、存款方案选择及贷款方案选择，利用财务函数中常见的FV、PMT及PV函数以及变量分析的相关知识完成了本次任务。通过这次实训，学习了财务函数FV、PMT及PV的使用，及单变量分析和多变量分析的方法。六、 课后拓展练习仿照这次实训的内容，为你自己拟定一项零存整取的存款计划，在网络上查询最新的一年零存整取的利率，根据你每月计划存入的金额计算一年后的本息总和（如果时下国家收取利息税，则根据税率扣除）；然后为你的家人拟定一项投资理财计划，再根据不同的回报率及各期投资额使用双变量分析的方法计算不同的投资回报总额；最后假设你要购买一处价值50万的房产，需要向银行贷款30万，期限为20年，在网络上查询最新的贷款利率，根据贷款金额、贷款期限及利率计算你的月供是多少。实训四 图表的编辑与格式化制作空气质量指标图一、 任务介绍我国的空气质量标准共分为五级，每一级都对应空气质量的三个指标，即空气中总悬浮颗粒物，二氧化硫，二氧化氮每立方米内的毫克值。下表中给出了2007年12月7日全国主要城市空气质量指标数据（部分）。要求根据该表中的数据制作两个图表：图4-30 全国主要城市空气质量指标数据u 北京、上海、广州、深圳四大城市的空气质量达到及好于二级的天数比较图，如图4-31所示。图4-31 四大主要城市空气质量二级达标图u 北京、天津、沈阳三大重工业城市的三大空气质量指标的对比图，如图4-32所示。图4-32 三大重工业城市空气质量指标对比图二、 任务分析“全国主要城市空气质量指标”表虽然清晰、准确地提供了数据，却不够直观，也不利于作数据分析，Excel的图表功能刚好弥补了这样的不足，能够较好的为生产、生活提供科学的参考。为了完成这次任务，需要具备工作表的管理及与图表相关的一系列知识：u 图表的组成。u 图表的创建与编辑方法、技巧。u 工作表的管理。三、 任务分解本任务可以分解为以下个子任务：子任务1 创建与编辑四大城市空气质量二级达标图子任务2 创建与编辑三大重工业城市空气质量指标对比图子任务3 管理数据工作表与图表工作表四、 任务指导子任务1、创建与编辑四大城市空气质量二级达标图根据EXLX03.xls文件中Sheet1工作表的2007年7月全国主要城市空气质量指标数据表创建如图4-31所示的嵌入式图表，格式要求如下：分类轴、数值轴文本、数值轴标题：宋体、12号；图表标题：宋体、16号、加粗；数据标签：宋体、12号、加粗、红色。步骤1：按下Ctrl键，单击选择不连续区域：A3，E3，A5，E5，A12，E12，A23，E23，A31，E31（不连续选择需要一步到位正确选择，如果选择错误请重新选择）；步骤2：单击“常用”工具栏的图表向导按钮，弹出“图表向导”对话框，选择图表类型“柱形图”及子类型“簇状柱形图”；步骤3：单击“下一步”，弹出“图表源数据”对话框，选择系列产生在列，预览效果如图4-33所示：图4-33 选择数据源后的预览效果图步骤4：单击“下一步”，弹出“图表选项”对话框，将图表标题修改为“空气质量达到及好于二级的天数”，在数值轴标题输入“（天）”；在“图例”选项卡中将“显示图例”取消选择；在“数据标志”选项卡中选择数据标签包括“值”；步骤5：单击“下一步”，弹出“图表位置”选项卡，默认选择“作为其中对象插入：Sheet1”，单击“完成”；步骤6：单击图表标题，选择字体为宋体、字号16、加粗；同样的方法单击分类轴、数值轴、数据标签，按照要求选择字体、字形、字号、颜色；步骤7：执行绘图区的快捷菜单中的“绘图区格式”命令，将“区域”颜色修改为白色，如图4-34所示：图4-34 设置绘图区格式步骤8：执行数值轴的快捷菜单中的“坐标轴标题格式”命令，选择“对齐”选项卡，将“方向”修改为0度，如图4-35所示：步骤9：执行数值轴主要网格线的快捷菜单中的“网格线格式”命令，在“图案”选项卡中将线条样式改为如图4-36所示的虚线，在“刻度”选项卡中修改最小值及主要刻度单位，如图4-37所示：图4-35 设置坐标轴标题的方向图4-36 设置数值轴网格线样式图4-37 设置数值轴刻度子任务2、创建与编辑三大重工业城市空气质量指标对比图根据2007年7月全国主要城市空气质量指标数据表创建如图4-32所示的独立图表，格式要求如下：分类轴、数值轴、图例文本：宋体、12号图表标题：黑体、22号；数值轴标题：宋体、14号、加粗、倾斜、绿色；数据标签：Times New Roman，12号，加粗。步骤1：按下Ctrl键，单击选择不连续区域：A3:D3，A5:D6，A13:D13；步骤2：执行“插入”菜单中的“图表”命令，弹出“图表向导”对话框，选择图表类型“条形图”及子类型“簇状条形图”；步骤3：单击“下一步”，弹出“图表源数据”对话框，选择系列产生在行，预览效果如图4-38所示：图4-38 选择数据源后的预览效果图步骤4：单击“下一步”，弹出“图表选项”对话框，在图表标题中输入“三大重工业城市空气质量指标”，在数值轴标题输入“单位：毫克/立方米”；在“网格线”选项卡中将数值轴“主要网格线”取消选择；在“数据标志”选项卡中选择数据标签包括“值”；步骤5：单击“下一步”，弹出“图表位置”选项卡，选择“作为新工作表插入：京津沈空气质量指标”，单击“完成”；步骤6：单击图表标题，选择字体为黑体、字号22；同样的方法单击分类轴、数值轴、数值轴标题、图例、数据标签，按照要求选择字体、字形、字号、颜色，再单击拖动鼠标将数值轴标题和图例移动到合适的位置；步骤7：执行绘图区的快捷菜单中的“绘图区格式”命令，将“边框”修改为“无”，将“区域”颜色修改为白色；步骤8：单击“天津”序列的任意一个柱形，在快捷菜单中选择“数据系列格式”命令，在“图案”选项卡中设置将“内部”颜色为茶色，同样的方法设置“沈阳”序列的颜色为浅黄色、“北京”为浅青绿色；步骤9：在任意一个“数据系列格式”的“系列次序”选项卡中改变三个系列的次序，如图4-39所示：图4-39 调整数据系列的顺序子任务3、管理数据工作表与图表工作表删除工作表Sheet2，将工作表Sheet1标签改名为“全国主要城市空气质量指标（2007-12-7）”，再将该工作表移至最前面。步骤1：右键单击Sheet2的工作表标签，执行快捷菜单中的“删除”命令；步骤2：双击Sheet1的工作表标签，输入“全国主要城市空气质量指标（2007-12-7）”，回车；步骤3：单击拖动第二个工作表的标签至第一个工作表标签之前。五、 实训小结在本次实训中我们将制作空气质量指标图这项工作分解为3个子任务，根据已有的全国主要城市空气质量指标图，选取所需要的数据制作北京、上海、广州、深圳四大城市的空气质量二级达标图和京津沈空气质量指标对比图，直观的呈现出各城市空气质量的比较情况。通过这次实训，我们可以基本掌握Excel电子表格处理软件中的图表的创建与编辑的基本方法和技巧，以及在工作簿中管理多个工作表的方法。六、 课后拓展练习走访学校附近或市区的一家大型连锁药房，调查35种常见药品近半年的销售情况，根据调查来的数据利用Excel建立数据清单，然后根据表中的部分或全部数据创建“XX药房XY月药品销售统计”折线图图表。实训五 数据管理与分析制作报名数据统计表一、 任务介绍MLC，微软授权视窗应用学习中心，是微软（中国）有限公司于2002年全新推出的计算机普及教育的培训模式。MLC以开设Windows，Office四大模块及整合运用等课程为主，学员可以根据需要选择模块参加认证考核，考试合格后可以获得相应模块的微软认证证书。图4-40是MLC考试的准考证及收据样张。广美学院组织了本院近200名学生参加了今年的MLC的培训及认证考试。本次工作任务除了要求在Excel工作簿中实现输入报名号，就能计算出所有的报名信息并自动填写的功能之外，还要求对报名数据作如下统计分析：图4-40 MLC考试准考证、收据样张u 按照姓氏笔划递增排序，再根据是否交费及报考科目排序；u 筛选出建管1班和建筑1班的已交费记录；u 分班汇总各报考科目已缴报名费总额；u 分班、分报考科目统计报名人数及报名费总额。二、 任务分析使用Excel公式和函数的功能能较好的完成相关的计算，但Excel的函数种类繁多，使用起来比较复杂，用作数据分析也不够灵活。Excel提供了排序、筛选、汇总、数据透视等多项数据管理与分析的功能，方便灵活。为了准确高效的完成这次任务，需要具备下列知识：u 数据的排序。u 数据的自动筛选和高级筛选。u 数据的分类汇总。u 数据透视表和数据透视图的使用。u 查找函数VLOOKUP函数的使用。三、 任务分解本任务可以分解为以下个子任务：子任务1 制作智能化的MLC准考证及收据子任务2 工作表的复制与管理子任务3 记录的排序与恢复排序子任务4 记录的自动筛选与高级筛选子任务5 数据分类汇总子任务6 数据透视表和数据透视图的建立与编辑四、 任务指导子任务2、工作表的复制与管理将“报名表”工作表复制到名为“EXLX04.xls”的新工作簿中，并在工作簿中复制三张工作表，分别改名为：“排序”、“筛选”、“分类汇总”、“数据透视”。步骤1：右键单击“报名表”的工作表标签，执行快捷菜单中的“移动或复制工作表”命令；步骤2：选择“将选定工作表移至新工作簿”，并勾选“建立副本”，如图4-42所示；步骤3：将新工作簿以EXLX04.xls为文件名保存，并将原文件保存、关闭；步骤4：在新工作簿中将唯一的工作表复制，方法与上相似，选择“移至最后”，如图4-43所示；步骤5：依次双击各工作表的标签，输入新的标签名：“排序”、“筛选”、“分类汇总”、“数据透视”。 图4-42 复制工作表至新工作簿 图4-43 复制多个工作表子任务3、记录的排序与恢复排序（1）按照姓氏笔划递增排序。步骤1：单击“排序”工作表中姓名字段的任意单元格；步骤2：执行“数据”菜单中的“排序”命令，在弹出的对话框中单击“选项”按钮，选择“方法”为“笔划排序”，确定；步骤3：单击“主要关键字”的下拉菜单按钮，选择“姓名”字段，选择“升序”，然后确定，设置如图4-44所示。 图4-44 按照姓氏笔划递增排序 图4-45 按照是否交费与报考科目排序（2）将刚才的排序还原，根据是否交费及报考科目，将已交费的记录排在前面，在已交费和未交费的记录中再按照报考科目递增排序。步骤1：单击“撤销”按钮，将排序结果还原；步骤2：单击数据清单内任意单元格，执行“数据”菜单中的“排序”命令，依前所示将排序方法改为“拼音排序”，确定；步骤3：在“主要关键字”下拉菜单中选择“是否交费”字段、“降序”；在“次要关键字”下拉菜单中选择“报考科目”字段、升序，然后确定，设置如图4-45所示。子任务4、记录的自动筛选与高级筛选（1）在身份证列后面增加“年龄”列，使用学过的函数根据身份证号码的710位计算每个学生的年龄。步骤1：右键单击D列列标，执行快捷菜单中的“插入”命令；步骤2：在D1单元格输入“年龄”，在D2单元格输入公式如下所示：=YEAR(TODAY()-MID(C2,7,4)；步骤3：输入公式后，将计算结果单元格的数字分类修改为“常规”。（2）使用自动筛选功能查看建管1班和建筑1班的已交费记录，然后还原。步骤1：单击数据清单的任意单元格，执行“数据”菜单中“筛选”子菜单下的“自动筛选”命令；步骤2：在字段名“班级”旁边的下拉菜单中选择“自定义”，在弹出的“自定义自动筛选方式”对话框中输入两个“或”关系的条件，如图4-46所示，然后确定；图4-46 自定义条件“建管1班与建筑1班”步骤3：在字段名“是否交费”的下拉菜单中选择“是”；步骤4：取消“数据”菜单中“筛选”子菜单下的“自动筛选”命令。（3）筛选出所有报考“三科(Word，Excel，PowerPoint)”的未交费记录复制到J3为左上角的单元格区域（条件区位置任意），如图4-47所示（部分）。图4-47 筛选出报考了三科的未交费的名单并建立“未交费清单”步骤1：在“筛选”工作表中任意位置建立如图4-47所示的条件区；步骤2：单击A1单元格，按下键盘的Shift键，再单击H188单元格以选中整个数据清单，执行“数据”菜单中 “筛选”子菜单下的“高级筛选”命令；步骤3：单击选择“将筛选结果复制到其他位置”方式；单击“条件区域”后面的输入框，选择I1: J2（图中所示条件区位置）；单击“复制到”后面的输入框，单击J3，确定；（4）筛选出年龄在24岁以下或姓王的学生的记录复制到J29为左上角的单元格区域（条件区位置任意），如图4-48所示（部分）。图4-48筛选出年龄在24岁以下或姓王的学生名单步骤1：在“筛选”工作表中任意位置建立如图4-48所示的条件区；步骤2：单击A1单元格，按下键盘的Shift键，再单击H188单元格以选中整个数据清单，执行“数据”菜单中 “筛选”子菜单下的“高级筛选”命令；步骤3：单击选择“将筛选结果复制到其他位置”方式；单击“条件区域”后面的输入框，选择I26: J28（图中所示条件区位置）；单击“复制到”后面的输入框，单击J29，确定；子任务5、数据分类汇总以“报名科目”分类，汇总各科目已缴报名费总额，并将汇总结果依照图4-49显示。图4-49 分类汇总各科目报名费总额结果图步骤1：单击“分类汇总”工作表中数据清单的任意单元格，单击常用工具栏的“升序排序”按钮；步骤2：执行“数据”菜单中的“分类汇总”命令，在“分类汇总”对话框中设置各选项，如图4-50所示，然后单击确定；步骤3：单击工作表左侧的“级别按钮2”，显示效果即如4-49所示。图4-50 分报考科目汇总报名费总额6、数据透视表和数据透视图的建立与编辑以“报考科目”分页、“班级”为行字段，在“数据透视”工作表中I3为左上角的单元格区域建立数据透视表统计报名人数及报名费总额，然后显示出“三科（Word，Excel，PowerPoint）”、酒店1班和酒管1班的统计数据，结果如图4-51所示。图4-51 在数据透视表中查看记录步骤1：单击数据清单的任意单元格，执行“数据”菜单中的“数据透视表和数据透视图”命令；步骤2：在“3步骤之1”对话框中默认选择数据源类型和创建的报表类型，单击“下一步”；步骤3：在“3步骤之2”对话框中默认选择数据源区域，“下一步”；步骤4：在“3步骤之3”对话框中的“显示位置”下单击“现有工作表”，然后单击I3单元格；单击“布局”按钮，选择各项，如图4-52所示，然后单击确定；图4-52 数据透视表的布局选项设置注：选择“姓名”字段作为计数项以统计报名人数亦可；步骤5：单击完成；步骤6：在分页字段“报考科目”的下拉菜单中选择“三科（Word，Excel，PowerPoint）”，在行字段“班级”的下拉菜单中单击“全部显示”取消所有选择，然后单击选择“酒店1班”和“酒管1班”， 显示效果即如4-51所示。子任务1、制作智能化的MLC准考证及收据在EX04.xls工作簿中使用VLOOKUP函数，在准考证及收据中利用公式实现输入报名号，就能计算出所有的报名信息并自动填写的功能；在“报名日期”后填入当天日期。步骤1：选择“准考证、收据打印”工作表中的C6单元格；步骤2：单击“插入函数”按钮，选择“查找和引用函数”类别的VLOOKUP，按照图4-41所示输入各个参数：图4-41 使用VLOOKUP函数查找报名号步骤3：与上相似，分别在C7:C8、C13:C18、E6:E7各单元格输入函数，其中C15和C16的函数参考如下：C15：=VLOOKUP($B$3,报名表!$A$1:$F$188,5,FALSE)C16：=VLOOKUP($C$15,考试科目!$A$1:$E$4,3,FALSE)步骤4：单击E8单元格，在编辑栏输入：=TODAY()。五、 实训小结在本次实训中我们将制作报名数据统计表这项工作分解为6个子任务，先使用VLOOKUP函数在报名表中查找出MLC准考证及收据中各项信息，然后将报名表复制到新的工作簿中并复制多个工作表，在各工作表中分别完成数据排序、筛选、分类汇总和数据透视等多项任务。通过这次实训，我们可以基本掌握Excel电子表格处理软件中数据管理与分析的基本方法和技巧，以及VLOOKUP函数的使用，并进一步巩固了在工作簿中管理多个工作表的方法。六、 课后拓展练习统计你们班上最近的一次考试成绩，在Excel工作簿中建立成绩表，然后分成多张工作表，分别使用排序（一个关键字、多个关键字）、筛选（自定义自动筛选、高级筛选）、分类汇总