第8章 复杂Excel表格处理.doc

第8章复杂Excel表格处理第8章复杂Excel表格处理基本操作【教学导航】教学目标（1）掌握工作簿的新建、保存、打开方法（2）掌握数据输入、修改和简单格式处理方法（3）掌握单元格的选定、格式设置等操作方法（4）掌握数据、工作表的复制与移动方法（5）掌握行和列的基本操作方法（6）掌握自动计算、公式和常用函数的使用本章重点工作表数据输入、单元格编辑、行和列的操作、工作表管理本章难点工作表数据输入、单元格编辑、工作表管理教学方法理论实践一体化，教、学、做合一课时建议4课时（含课堂实践）8.1 课堂引导训练1【任务8-1-1】制作公司产品年销售统计表【任务描述】（1）新建“公司产品全年销售统计.xls”工作簿。（2）在Sheet1工作表中按图8-2所示表格，输入表格标题及表头内容，并填充“序号”和“单位”两栏内容。（3）修改单元格数据。将“内存”的单位“个”修改为“根”，“显示器”的单位修改为“台”。（4）设置单元格格式。表格标题为宋体、24号、加粗、合并居中；表头单元格为宋体、11号、加粗、居中对齐；“序号”栏内容“水平居中”。（5）编辑工作表。在标题行的上方和下方各插入一行，设置标题行行高为30、标题行下方的空行行高为8，在“A”列前插入一列；设置“单位商品成本价”所在列的列宽为15；合并“制表单位：蝴蝶电脑公司”、“制表日期：2010-12-31”所在单元格；为表格添加粗实线外边框 、细实线内边框。（6）管理工作表。将Sheet1重命名为：利润表；将“商品销售年报表.xls”工作簿中的“内存销售记录”、“硬盘销售记录”、“显示器销售记录”、“CPU销售记录”工作表复制到“公司产品全年销售统计”工作簿中；在“利润表”前插入工作表：销售汇总表；删除Sheet2、Sheet3工作表。（7）使用公式和函数。用简单公式计算“销售收入、毛利润、毛利润率”；复制计算“销售收入、毛利润、毛利润率”的公式；设置“毛利润率”数据为百分比格式、保留1位小数；用自动求和函数计算“销售总数量、销售总收入”；使用平均值函数计算“平均毛利润率”；使用求和函数求“总毛利润”额；用组合公式计算“平均销售均价”。【操作示范】8.1.1新建空白工作簿启动Excel，系统自动新建了一个空白工作簿，“保存”工作簿。8.1.2输入工作表数据1. 输入数据在Sheet1工作表中输入利润表表头内容，在A1单元格中输入“蝴蝶电脑公司2010年利润表”，在A2单元格中输入“制表单位：蝴蝶电脑公司”，在G2单元格中输入“制表日期：2010-12-31”，在A3单元格中输入“序号”，在B3单元格中输入“类别”，依次输入其他单元格内容。2. 快速填充数据（1）填充相同的数据3.修改单元格中数据修改C4和C6单元格数据。8.1.3设置单元格格式1.设置表格标题格式设置表格标题为24号字、加粗、合并居中2.设置表头单元格格式设置表头单元格为11号字、加粗、居中对齐。3.设置列格式设置“序号”列内容“水平居中”。8.1.4编辑工作表1.插入行在标题行“蝴蝶电脑公司2010年利润表”的上方和下方各插入一行。2. 设置行高设置标题行“蝴蝶电脑公司2010年利润表”的行高为30，标题行下方的空行行高为8。3. 插入列在“A”列前插入列。4. 设置列宽 设置“单位商品成本价”所在列的列宽为15。5. 合并单元格 合并“制表单位：蝴蝶电脑公司” 、“制表日期：2010-12-31”所在单元格。6.添加表格边框为表格添加粗实线外边框 、细实线内边框。8.1.5管理工作表1. 重命名工作表将sheet1工作表重命名为：利润表。2. 工作簿间数据的复制将“素材第8章商品销售年报表.xls”中的“内存销售记录”、“硬盘销售记录”、“显示器销售记录”、“CPU销售记录”工作表复制到“公司产品全年销售统计”工作簿中。3. 新增工作表在“利润表”前新增“销售汇总表”工作表。4. 删除工作表将Sheet2、Sheet3工作表删除。8.1.6使用公式和函数1. 使用简单公式使用简单公式计算“销售收入、毛利润”，计算“销售收入”。2. 复制公式复制计算“销售收入、毛利润”的公式。3.计算“毛利润率”用简单公式计算“毛利润率”。4. 设置“毛利润率”数据列的格式设置“毛利润率”数据为百分比格式、保留1位小数。 5. 使用自动求和函数使用自动求和函数计算“销售总数量、销售总收入”，计算“销售总数量”。6. 使用平均值函数使用平均值函数计算“平均毛利润率”。7. 使用求和函数使用求和函数求“总毛利润”额。8. 使用组合公式使用组合公式计算“平均销售均价”。8.2 课堂同步训练1 【任务8-2-1】商品销售数据统计分析【任务描述】（1）打开“显示器销售记录”工作簿。（2）输入“销售数据汇总表”表内容：在B1单元格中输入“7-8月份显示器销售数据汇总表”，在B3单元格中输入“品牌”。（3）复制单元格区域：将“8月份显示器销售记录”中D3:J3中的内容复制到“销售数据汇总表”工作表B3:H3单元格中（4）设置标题格式：合并B1:H1单元格，居中对齐，字体为黑体、24号、加粗、下划线、浅青绿色底纹。设置标题所在行的行高为30。（5）设置表头文字：设置B3:H3单元格宋体、12号、加粗。（6）合并计算。将“7月份显示器销售记录”与“8月份显示器销售记录”工作表中的数据合并计算到“销售数据汇总表”表中。（7）编辑表格。在B7单元格中输入：总计；在C7、E7单元格中分别输入：。（8）计算销售总成本、总毛利润。使用填充柄复制计算销售总收入公式计算销售总成本、总毛利润。8.3 课外拓展训练1 【任务8-3-1】计算职工工资【任务描述】打开“工资表.xlsx”的工作簿，进行如下操作：（1）计算销售总数量。使用sum函数计算销售总数量。（2）计算销售总收入。使用自动求和功能计算销售总收入。（3）计算工龄工资。工龄工资=年龄5，年龄可以使用YEAR()、NOW()函数求得。（4）用Excel的自动求和功能计算合计值。第8章复杂Excel表格处理数据处理【教学导航】教学目标（1）掌握自动计算、公式和常用函数的使用（2）掌握排序、分类汇总、筛选的建立方法本章重点数据排序、分类汇总、筛选、公式和函数本章难点工作簿间数据的复制、嵌套分类汇总、高级筛选、COUNTIF函数教学方法理论实践一体化，教、学、做合一课时建议4课时（含课堂实践）8.1 课堂引导训练2【任务8-1-2】分析公司产品年销售数据【任务描述】（1）打开“公司产品全年销售统计.xls”工作簿。（2）生成“销售汇总表”和“利润表”中数据。通过复制单元格区域将“显示器销售记录”、“内存销售记录”、“硬盘销售记录”和“CPU销售记录”中数据复制到“销售汇总表”工作表中；通过“选择性粘贴”将“显示器销售记录”、“内存销售记录”、“硬盘销售记录”和“CPU销售记录”中的平均“成本价”、“销售均价”、“毛利润率”和总计“销售数量”、“销售收入”、“销售成本”、“毛利润”分别复制到“利润表”的相应单元格中。（3）对“销售汇总表”中数据进行排序。对“毛利润率”按升序排序；在“销售汇总表”中按“毛利润率”降序排列，销售“毛利润率”相同的，则按“销售成本”升序排列。（4）对“销售汇总表”中数据有条件地进行筛选。自动筛选出“类别”为“内存”的销售记录；自定义筛选出“销售数量”大于450，不超过600的记录；用高级筛选筛选出“毛利润率”小于10、销售数量大于等于400的记录。（5）使用分类汇总分类统计“销售数量、销售收入、销售成本、毛利润”。按“类别”统计“销售数量、销售收入、销售成本、毛利润”；显示按“类别”统计的“销售数量、销售收入、销售成本、毛利润”数据；删除按“类别”建立的分类汇总；按“类别”和“品牌”对“毛利润率”进行分类求平均值。8.1.7复制数据通过复制数据生成“销售汇总表”和“利润表”。1. 复制单元格区域将“显示器销售记录”、“内存销售记录”、“硬盘销售记录”和“CPU销售记录”中数据复制到“销售汇总表”工作表中 2. 使用“选择性粘贴”复制数据将“显示器销售记录”、“内存销售记录”、“硬盘销售记录”和“CPU销售记录”中的平均“成本价”数据复制到“利润表”的“单位商品成本价”中，将平均“销售均价”、“毛利润率”分别复制到“单位商品销售均价”、“毛利润率”中，将总计“销售数量”、“销售收入”、“销售成本”、“毛利润”分别复制到“销售数量”、“销售收入”、“销售成本”、“毛利润”中。8.1.8数据排序在Excel中可以对一列或多列中的数据按文本(升序或降序)、数字(升序或降序)以及日期和时间(升序或降序)进行排序。还可以按自定义序列(如大、中和小)或格式(包括单元格颜色、字体颜色或图标集)进行排序。可以按单列排序、按字母排序和多列排序。在单列排序过程中，有时遇到该列中有相同数据的情况，这时就要使用多列进一步排序。1. 对“毛利润率”按升序排序在“销售汇总表”中按“毛利润率”升序排列。2. 按“毛利润率”与“销售成本”多列排序在“销售汇总表”中按“毛利润率”降序排列，销售“毛利润率”相同的，则按“销售成本”升序排列。【注】：双关键字排序首先按主要关键字指定顺序排列，当主关键字的值相同时，按次要关键字的指定顺序排列。8.1.9数据筛选使用Excel筛选功能，可以把不符合设置条件的数据记录暂时隐藏起来，只显示符合条件的记录。筛选有自动筛选、自定义筛选和高级筛选。1. 自动筛选出“类别”为“内存”的销售记录 自动筛选就是按选定的内容进行筛选，适用于简单的筛选数据。在“销售汇总表”中筛选出“类别”为“内存”的销售记录。【注】：在Excel中进行筛选后，还可取消筛选。取消筛选只要【筛选】下级菜单中再次单击【自动筛选】命令，即取消【自动筛选】命令前的“”。2. 自定义筛选出“销售数量”大于450，不超过600的记录 在“销售汇总表”筛选出“销售数量”大于450，不超过600的记录。【注】：在“自定义自动筛选方式”对话框中的文本框中可用“？”号代表单个字符，用“*”代表任意多个字符。3. 用高级筛选筛选出“毛利润率”小于10，销售数量大于等于400的记录 高级筛选必须先建立一个条件区域，用来指定筛选数据的条件。在“销售汇总表”中筛选出“毛利润率”小于10，销售数量大于等于400的记录。8.1.10使分类汇总统计数据使用Excel分类汇总功能可以对同一类中的数据进行求和、求平均值等统计运算，这将使工作表中的数据变得更加清晰和直观。分类汇总前必须要按照分类的字段排序，否则不能达到分类汇总的目的。1. 按“类别”统计“销售数量、销售收入、销售成本、毛利润” 在“销售汇总表”中，按“类别”对“销售数量、销售收入、销售成本、毛利润”进行汇总。2. 显示按“类别”统计的“销售数量、销售收入、销售成本、毛利润”数据 单击分组显示符号按钮，将显示第二级分类汇总的结果。【注】：单击分类汇总左上角的分组显示符号按钮（见图8-61），可显示不同级别的分类汇总。3. 删除按“类别”建立的分类汇总在分类汇总数据中单击任意单元格，单击【数据】菜单中的【分类汇总】命令，打开“分类汇总”对话框，单击【全部删除】按钮。4. 按“类别”和“品牌”对“毛利润率”进行分类求平均值8.2 课堂同步训练2 【任务8-2-2】商品销售数据处理【任务描述】（1）打开“显示器销售记录”工作簿。（2）添加边框。为“销售数据汇总表”工作表的B3：H7添加粗实线外边框、细实线内边框。（3）复制工作表。复制“8月份显示器销售记录”工作表中为“显示器销售记录”。（4）排序。在“8月份显示器销售记录”工作表中按销售收入的升序排列，销售收入相同的按销售数量的降序排列。（5）分类汇总。在“7月份显示器销售记录”工作表中按“品牌”对毛利润进行分类汇总。8.3 课外拓展训练2 【任务8-3-2】职工工资统计【任务描述】打开“工资表.xlsx”的工作簿，进行如下操作：（1）计算养老保险金、住房公积金、医疗保险金。养老保险金=合计8，住房公积金=合计12，医疗保险金=合计2。（2）简单公式计算合扣款、实领金额。（3）对“实领金额”一列数据使用货币￥符号，保留1位小数。（3）使用SUM（）函数计算总计，AVERAGE（）函数求平均。（4）使用MAX（）、MIN（）函数求最高基本工资和最高实领金额、最低基本工资和最基本低实领金额。第8章复杂Excel表格处理图表操作8.1 课堂引导训练3【教学导航】教学目标（1）掌握数据透视表的建立与使用方法（2）掌握图表的创建与编辑方法本章重点数据透视表、图表的应用本章难点数据透视表、图表的应用教学方法理论实践一体化，教、学、做合一课时建议4课时（含课堂实践）【任务8-1-3】使用图表分析公司产品年销售数据【任务描述】（1）使用“数据透视表”分析每种商品的毛利润与毛利润率。按“类别”和“品牌”创建数据透视表；分析各个类别的毛利润率。（2）使用图表分析比较各类商品的“毛利润率”的高低。创建毛利润率“柱形图”；适当调整图表的位置、图表大小；修改图表标题；设置数值轴标题格式；更改图表类型为“数据点折线图”。8.1.11使用“数据透视表”分析数据使用分类汇总林峰能够了解各个类别、每一种品牌的平均毛利润率的高低，平均毛利润率高的是不是销售量也多、产生的毛利润也多呢？这在分类汇总中无法了解到，象这种问题，使用Excel中的数据透视表能方便地解决。1. 按“类别”和“品牌”创建数据透视表2. 分析各个类别的毛利润率8.1.12使用图表分析“毛利润率”在“