第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章复杂Excel表格处理使用求和函数求“总毛利润”额;用组合公式计算“平均销售均价”。【操作示范】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.新增工作表第8章复杂Excel表格处理在“利润表”前新增“销售汇总表”工作表。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章复杂Excel表格处理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的记录;用高级筛选筛选出“毛第8章复杂Excel表格处理利润率”小于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的记录第8章复杂Excel表格处理高级筛选必须先建立一个条件区域,用来指定筛选数据的条件。在“销售汇总表”中筛选出“毛利润率”小于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)计算养老保险金、住房公积金、医疗保险金