第8章 复杂Excel表格处理

《第8章 复杂Excel表格处理》由会员分享，可在线阅读，更多相关《第8章 复杂Excel表格处理（8页珍藏版）》请在金锄头文库上搜索。

1、第 8 章复杂 Excel 表格处理第 8 章复杂 Excel 表格处理基本操作【教学导航】教学目标（1）掌握工作簿的新建、保存、打开方法（2）掌握数据输入、修改和简单格式处理方法（3）掌握单元格的选定、格式设置等操作方法（4）掌握数据、工作表的复制与移动方法（5）掌握行和列的基本操作方法（6）掌握自动计算、公式和常用函数的使用本章重点 工作表数据输入、单元格编辑、行和列的操作、工作表管理本章难点 工作表数据输入、单元格编辑、工作表管理教学方法 理论实践一体化，教、学、做合一课时建议 4 课时（含课堂实践）8.1 课堂引导训练 1【任务 8-1-1】制作公司产品年销售统计表【任务描述】（1）新

2、建“公司产品全年销售统计.xls”工作簿。（2）在 Sheet1 工作表中按图 8-2 所示表格，输入表格标题及表头内容，并填充“序号”和“单位”两栏内容。（3）修改单元格数据。将“内存”的单位“个”修改为“根” ， “显示器”的单位修改为“台” 。（4）设置单元格格式。表格标题为宋体、24 号、加粗、合并居中；表头单元格为宋体、11 号、加粗、居中对齐；“序号”栏内容“水平居中” 。（5）编辑工作表。在标题行的上方和下方各插入一行，设置标题行行高为 30、标题行下方的空行行高为 8，在“A ”列前插入一列；设置“ 单位商品成本价”所在列的列宽为15；合并“制表单位：蝴蝶电脑公司” 、 “制表

3、日期：2010-12-31”所在单元格；为表格添加粗实线外边框 、细实线内边框。（6）管理工作表。将 Sheet1 重命名为：利润表；将“商品销售年报表.xls”工作簿中的“内存销售记录” 、 “硬盘销售记录” 、 “显示器销售记录” 、 “CPU 销售记录”工作表复制到“公司产品全年销售统计”工作簿中；在“利润表”前插入工作表：销售汇总表；删除Sheet2、Sheet3 工作表。（7）使用公式和函数。用简单公式计算“销售收入、毛利润、毛利润率” ；复制计算“销售收入、毛利润、毛利润率”的公式；设置“毛利润率”数据为百分比格式、保留 1位小数；用自动求和函数计算“销售总数量、销售总收入” ；使

4、用平均值函数计算“平均毛第 8 章复杂 Excel 表格处理利润率” ；使用求和函数求“总毛利润”额；用组合公式计算“平均销售均价” 。【操作示范】8.1.1新建空白工作簿启动 Excel，系统自动新建了一个空白工作簿， “保存”工作簿。8.1.2输入工作表数据1. 输入数据在 Sheet1 工作表中输入利润表表头内容，在 A1 单元格中输入“蝴蝶电脑公司 2010 年利润表” ，在 A2 单元格中输入 “制表单位：蝴蝶电脑公司 ”，在 G2 单元格中输入“制表日期：2010-12-31 ”，在 A3 单元格中输入 “序号” ，在 B3 单元格中输入 “类别” ，依次输入其他单元格内容。2.

5、快速填充数据（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. 合并单元格 合

6、并“制表单位：蝴蝶电脑公司” 、 “制表日期：2010-12-31”所在单元格。6.添加表格边框为表格添加粗实线外边框 、细实线内边框。8.1.5管理工作表1. 重命名工作表将 sheet1 工作表重命名为：利润表。2. 工作簿间数据的复制将“素材第 8 章商品销售年报表.xls”中的“内存销售记录” 、 “硬盘销售记录” 、 “显示器销售记录” 、 “CPU 销售记录”工作表复制到“公司产品全年销售统计”工作簿中。第 8 章复杂 Excel 表格处理3. 新增工作表在“利润表”前新增“销售汇总表”工作表。4. 删除工作表将 Sheet2、Sheet3 工作表删除。8.1.6使用公式和函数1.

7、 使用简单公式使用简单公式计算“销售收入、毛利润” ，计算“销售收入” 。2. 复制公式复制计算“销售收入、毛利润”的公式。3.计算“毛利润率”用简单公式计算“毛利润率” 。4. 设置“毛利润率”数据列的格式设置“毛利润率”数据为百分比格式、保留 1 位小数。 5. 使用自动求和函数使用自动求和函数计算“销售总数量、销售总收入” ，计算“销售总数量” 。6. 使用平均值函数使用平均值函数计算“平均毛利润率” 。7. 使用求和函数使用求和函数求“总毛利润”额。8. 使用组合公式使用组合公式计算“平均销售均价” 。8.2 课堂同步训练 1 【任务 8-2-1】商品销售数据统计分析【任务描述】（1）

8、打开“显示器销售记录”工作簿。（2）输入“销售数据汇总表”表内容：在 B1 单元格中输入“7-8 月份显示器销售数据汇总表” ，在 B3 单元格中输入 “品牌” 。（3）复制单元格区域：将“8 月份显示器销售记录”中 D3:J3 中的内容复制到“销售数据汇总表”工作表 B3:H3 单元格中（4）设置标题格式：合并 B1:H1 单元格，居中对齐，字体为黑体、24 号、加粗、下划线、浅青绿色底纹。设置标题所在行的行高为 30。（5）设置表头文字：设置 B3:H3 单元格宋体、12 号、加粗。（6）合并计算。将“7 月份显示器销售记录”与“8 月份显示器销售记录”工作表中的数据合并计算到“销售数据汇

9、总表”表中。（7）编辑表格。在 B7 单元格中输入：总计；在 C7、E7 单元格中分别输入：。（8）计算销售总成本、总毛利润。使用填充柄复制计算销售总收入公式计算销售总成本、总毛利润。第 8 章复杂 Excel 表格处理8.3 课外拓展训练 1 【任务 8-3-1】计算职工工资【任务描述】打开“工资表.xlsx”的工作簿，进行如下操作：（1）计算销售总数量。使用 sum 函数计算销售总数量。（2）计算销售总收入。使用自动求和功能计算销售总收入。（3）计算工龄工资。工龄工资=年龄5，年龄可以使用 YEAR()、NOW()函数求得。（4）用 Excel 的自动求和功能计算合计值。第 8 章复杂 E

10、xcel 表格处理数据处理【教学导航】教学目标 （1）掌握自动计算、公式和常用函数的使用（2）掌握排序、分类汇总、筛选的建立方法本章重点 数据排序、分类汇总、筛选、公式和函数本章难点 工作簿间数据的复制、嵌套分类汇总、高级筛选、COUNTIF 函数教学方法 理论实践一体化，教、学、做合一课时建议 4 课时（含课堂实践）8.1 课堂引导训练 2【任务 8-1-2】分析公司产品年销售数据【任务描述】（1）打开“公司产品全年销售统计.xls”工作簿。（2）生成“销售汇总表”和“利润表”中数据。通过复制单元格区域将“显示器销售记录” 、 “内存销售记录” 、 “硬盘销售记录”和“CPU 销售记录”中数

11、据复制到“销售汇总表”工作表中；通过“选择性粘贴”将“显示器销售记录” 、 “内存销售记录” 、 “硬盘销售记录”和“CPU 销售记录” 中的平均“成本价” 、 “销售均价” 、 “毛利润率”和总计“销售数量” 、 “销售收入” 、 “销售成本” 、 “毛利润”分别复制到“利润表”的相应单元格中。（3）对“销售汇总表”中数据进行排序。对“毛利润率”按升序排序；在“销售汇总表”中按“毛利润率”降序排列，销售“毛利润率”相同的，则按“销售成本”升序排列。（4）对“销售汇总表”中数据有条件地进行筛选。自动筛选出“类别”为“内存”的第 8 章复杂 Excel 表格处理销售记录；自定义筛选出“销售数量”

12、大于 450，不超过 600 的记录；用高级筛选筛选出“毛利润率”小于 10、销售数量大于等于 400 的记录。（5）使用分类汇总分类统计“销售数量、销售收入、销售成本、毛利润” 。按“类别”统计“销售数量、销售收入、销售成本、毛利润” ；显示按“类别”统计的“销售数量、销售收入、销售成本、毛利润”数据；删除按“类别”建立的分类汇总；按“类别”和“品牌”对“毛利润率”进行分类求平均值。8.1.7复制数据通过复制数据生成“销售汇总表”和“利润表” 。1. 复制单元格区域将“显示器销售记录” 、 “内存销售记录” 、 “硬盘销售记录”和“CPU 销售记录”中数据复制到“销售汇总表”工作表中 2.

13、使用“选择性粘贴”复制数据将“显示器销售记录” 、 “内存销售记录” 、 “硬盘销售记录”和“CPU 销售记录”中的平均“成本价”数据复制到“利润表”的“单位商品成本价”中，将平均“销售均价” 、“毛利润率”分别复制到“单位商品销售均价” 、 “毛利润率”中，将总计“销售数量” 、“销售收入” 、 “销售成本” 、 “毛利润”分别复制到“销售数量” 、 “销售收入” 、 “销售成本” 、“毛利润”中。8.1.8数据排序在 Excel 中可以对一列或多列中的数据按文本 (升序或降序)、数字(升序或降序)以及日期和时间( 升序或降序)进行排序。还可以按自定义序列(如大、中和小)或格式( 包括单元格

14、颜色、字体颜色或图标集)进行排序。可以按单列排序、按字母排序和多列排序。在单列排序过程中，有时遇到该列中有相同数据的情况，这时就要使用多列进一步排序。1. 对“毛利润率”按升序排序在“销售汇总表”中按“毛利润率”升序排列。2. 按“毛利润率”与“销售成本”多列排序在“销售汇总表”中按“毛利润率”降序排列，销售“毛利润率”相同的，则按“销售成本”升序排列。【注】：双关键字排序首先按主要关键字指定顺序排列，当主关键字的值相同时，按次要关键字的指定顺序排列。8.1.9数据筛选使用 Excel 筛选功能，可以把不符合设置条件的数据记录暂时隐藏起来，只显示符合条件的记录。筛选有自动筛选、自定义筛选和高级

15、筛选。1. 自动筛选出“类别”为“内存”的销售记录 自动筛选就是按选定的内容进行筛选，适用于简单的筛选数据。在“销售汇总表”中筛选出“类别”为“内存”的销售记录。【注】：在 Excel 中进行筛选后，还可取消筛选。取消筛选只要【筛选】下级菜单中再次单击【自动筛选】命令，即取消【自动筛选】命令前的“” 。2. 自定义筛选出“销售数量”大于 450，不超过 600 的记录 在“销售汇总表”筛选出“销售数量”大于 450，不超过 600 的记录。【注】：在“自定义自动筛选方式”对话框中的文本框中可用“？”号代表单个字符，用“*”代表任意多个字符。第 8 章复杂 Excel 表格处理3. 用高级筛选筛

16、选出“毛利润率”小于 10，销售数量大于等于 400 的记录 高级筛选必须先建立一个条件区域，用来指定筛选数据的条件。在“销售汇总表”中筛选出“毛利润率”小于 10，销售数量大于等于 400 的记录。8.1.10 使分类汇总统计数据使用 Excel 分类汇总功能可以对同一类中的数据进行求和、求平均值等统计运算，这将使工作表中的数据变得更加清晰和直观。分类汇总前必须要按照分类的字段排序，否则不能达到分类汇总的目的。1. 按“类别”统计“销售数量、销售收入、销售成本、毛利润” 在“销售汇总表”中，按“类别”对“销售数量、销售收入、销售成本、毛利润”进行汇总。2. 显示按“类别”统计的“销售数量、销售收入、销售成本、毛利润”数据 单击分组显示符号 按钮，将显示第二级分类汇总的结果。【注】：单击分类汇总左上角的分组显示符号 按钮（见图 8-61） ，可显示不同级别的分类汇总。3. 删除按“类别”建立的分类汇总在分类汇总数据中单击任意单元格，单击【数据】菜单中的【分类汇总】命令，打开“分类汇总