中职Office办公软件应用案例教程课件Excel2016 unit 8

图2-7-16 “1”按钮下总计项
图2-8-17 数据透视图效果
办公软件实训教程
1. 将费用明细表中，人事部、工艺部、销售部三个部门的数据进行合并计算，并使 合并后的数据动态更新。
方法如下： ①打开“费用明细表.xlsx”，另存为“费用明细表效果.xlsx”。在效果表中新建工作表 Sheet4。 ②选中Sheet4左上角的A1单元格。单击“数据”选项卡下“数据工具”组的“合并计算”按 钮。 ③弹出合并计算对话框，“函数”设置“求和”。 单击 按钮，设置“引用位置”。切换到人事部表，选择 A1：B6单元格区域，单 击 按钮回到合并计算对话框，完成人事部的引用位置，如图2-8-3所示。单击“添加” 按钮，添加引用位置。 接下来，按上述操作步骤，完成工艺部和销售部的引用位置添加，最终效果如图28-4所示。 ④添加引用位置完成后，勾选“标签位置”下面的两个选项，“首行”和“最左列”。 ⑤勾选“创建指向源数据的链接”复选框，这样合并结果就可以动态更新了。 ⑥单击“确定”。查看合并结果，三张表中的数据汇总到一张表中，如图2-8-5所示。 ⑦保存文件“费用明细表效果.xlsx”，存盘退出。 2. 为网店销售明细表中的数据创建数据透视表。网店销售明细表的数据样表如图28-18所示。 ①新建工作簿，将 “网店销售明细表.xlsx”的内容复制下来，另存为“网店销售明细表 效果.xlsx”，在效果表中进行操作。 ②单击“插入”选项卡下的“数据透视表”按钮。 ③在“数据透视表”对话框中，选择数据表区域；选择放置数据透视表的位置为“新工 作表”。
图2-7-21 第5题效果图
图2-7-22 第6题效果图
图2-7-23 数字筛选条件
图2-6-13 “COUNTIF”参数设置
办公软件实训教程
Excel 2016
8.在Sheet3中，利用高级筛选，筛选“销售业绩>85”且“表达能力>83”的人员。 ①在工作表下方第19行，复制列标题行第1行。设置条件：“销售业绩>85”、“表达能力>83”。如图2-7-8所示。 ②单击【数据】选项卡【排序和筛选】组的“高级”按钮。 ③弹出“高级筛选”对话框如图2-7-9所示。在“列表区域”文本框中自动选择了数据区域。单击“条件区域”右侧的折叠按钮，在工作表中选择满足筛选条件的单元 格区域，即E19:F20。 ④单击“确定”，筛选出符合条件的一条记录，效果如图2-7-10所示。 9. 在Sheet3中，利用高级筛选，筛选“销售业绩>85”或“写作能力>85”的人员，将Sheet3重命名为“筛选”。 ①单击【数据】选项卡【排序和筛选】组的“清除”按钮。先清除当前的筛选条件，让数据全部显示。 ②更改筛选的条件，在单元格区域E20和G21中输入条件，如图2-7-11所示。即“销售业绩>85”和“写作能力>85”。注意：两个条件不在同一行。 ③单击【数据】选项卡【排序和筛选】组的“高级”按钮。弹出“高级筛选”对话框。设置“方式”为“将筛选结果复制到其他位置”， “列表区域”文本框中默认填充 所有数据区域， “条件区域”则选择单元格区域E19:G21，“复制到”选择单元格A24。如图2-7-12所示。 ④单击“确定”，筛选出符合条件的记录，从A24开始放置。效果如图2-7-13所示。将工作表重命名为“筛选”。
办公软件实训教程
Excel 2016
将多个表中的数据合并计算；根据数据建立透视表，编辑透视表，操作透视表， 完成数据的分析、汇总和排序，完成切片器筛选数据。
办公软件实训教程
对于放置在不同工作表中，有相同结构的表格，我们需要将相关数据合并到一起，以便对所有信息有一个
直观的了解。要达成此目的可以使用数据透视表，除此之外 Excel还为我们提供一种简单快捷有效的工具，合并
将货品名称字段拖动到行标签，销售金额字段拖动2次到值区域，如图28-13所示。
图2-8-11 切片器对话框
图2-8-12 创建切片器的显示效果
图2-8-13 字段设置
办公软件实训教程
Excel 2016
3.编辑透视表 ③排序 数据透视表还可以对所选列或行排序，如：在透视表中按货品销售金额排序，方法如下：
计算。
一.合并计算
1.操作步骤
①新建表
新建一张表，需要将人事部、工艺部、销售部的费用明细汇总到这张表中，即存放合并计算的结果。
②选中左上角的单元格，表示合并计算的结果从这个单元格开始放置。
③单击“数据”选项卡下“合并计算”按钮，如图2-8-2所示。
④弹出合并计算对话框，“函数”设置“求和”。 “引用位置”设置先单击
当用户选择货品名称切片器上的货物名称，如显示器时，数据透视表和销售 数量切片器上则显示出显示器的相关数据信息。查询方便，数据清晰、一目了 然。
按“Delete”键可删除切片器。 ②更改计算方式 数据透视表默认汇总方式是“求和”，它的计算类型还有计数、平均值、最大 值、最小值等。更改汇总方式有两种方法：一种是在“数据透视表字段”对话框中， 在“值字段”下拉列表中，选择“值字段设置”按钮，更改计算类型。第二种方法是 在透视表中鼠标右击，弹出对话框，单击“值字段设置”按钮更改。 ③排序 数据透视表还可以对所选列或行排序，如：在透视表中按货品销售金额排序， 方法如下：
Excel 2016
图2-8-5 合并计算结果
办公软件实训教程
Excel 2016
二.数据透视表 数据透视表可以将表格中的数据整合到一张表中，是一种可以快速汇总大量数据的交互式表格。使用数据透视表可以深入分析数 据、以多种方式查询数据、汇总数据。 1.创建数据透视表 ①单击“插入”选项卡下的“数据透视表”按钮，如图2-8-6所示。 ②选择要分析的数据表区域。 ③选择现有工作表或新工作表放置透视表，单击“确定”。 ④生成数据透视表框架，如图2-8-7所示。 ⑤在数据透视表字段中，选择需要的字段，如销售日期、货品名称、销售数量。系统会默认放置在列或行标签上。也可自己拖动 字段到不同的框内。效果如图2-8-8所示。
Excel 2016
人们对Excel数据透视表的强大功能赞叹不已，卓卓我还不会呢。看着数据量很大的网店销售数据和 多个部门提供的需要汇总的大量数据，卓卓我一筹莫展。好在有师傅的帮助，卓卓学习了合并计算，建立 数据透视表，在透视表中查看数据、汇总数据和排序数据。问题解决了，卓卓当然很高兴，卓卓现在首先 要完成的就是将销售部、人事部、工艺部提供的数据进行合并计算，我们来看看他是怎么做的吧。
图2-8-6 数据透视表对话框
图2-8-7 数据透视表框架
图2-8-8 数据透视表
办公软件实训教程
2.编辑透视表 系统生成透视表后，用户可以通过设置透视表的布局、 样式、选项等标签，美化编辑创建的透视表。 ①重组数据透视表 数据透视表是显示数据信息的视图，创建数据透视表 后不能直接修改透视表所显示的数据项。但表中的字段 可以在字段栏或区域栏互换。如将销售日期由“行”变为 “列”，效果如图2-8-9所示。也可以根据需要随时向透视 表增加或删除字段，在数据透视表字段上操作即可。 ②美化数据透视表。可以通过布局、样式、选项、格 式等工具美化透视表。设置样式的方法是单击“数据透视 表工具”下的“设计”按钮，弹出数据透视表的样式，可以 选择其中的一种样式，然后再设置“数据透视表样式选项” 中的行和列，如图2-8-10所示。
Excel 2016
图2-8-3 合并计算对话框
图2-8-4 添加引用位置
图2-8-5 合并计算结果
图2-8-18 网店销售明细表样表
办公软件实训教程
Excel 2016
5.按照自定义序列 “销售部、研发部、文秘部”进行排序。 ①选择数据任一单元格。 ②单击【数据】选项卡【排序和筛选】组的“排序”按钮，弹出“排序”对话框。在主要关键字中选择“部门”，在“次序”中选择“自定义序列”，如图2-7-5所示。 ③在“自定义序列”对话框的“输入序列”中输入“销售部、研发部、文秘部”，如图2-7-6所示，单击“添加”按钮，单击“确定”。回到“排序”对话框，单击“确定”。 效果如图2-7-21所示。 6.复制Sheet1工作表中的内容至Sheet3，在Sheet3中筛选“男”职工记录。 ①选择任一数据单元格，单击【数据】选项卡【排序和筛选】组的“筛选”按钮。 ②在“性别”筛选按钮下选择“男”。筛选出男员工数据记录。效果如图2-7-22所示。 7. 在Sheet3中，筛选“销售业绩>=85”记录。 ①单击【数据】选项卡【排序和筛选】组的“清除”按钮。清除当前的筛选条件，让数据全部显示。 ②在工作表中单击“销售业绩”的筛选按钮，选择“数字筛选”下的“大于或等于”选项，如图2-7-23所示。 ③在弹出的对话框中输入85，单击“确定”，最终效果如图2-7-24所示。
按钮，切换到“人事部”表，选择
要合并的数据，如图2-8-3所示。
⑤再单击
按钮，回到合并计算对话框，完成人事部数据引用。单和销售部的数据。如图2-8-4所示。
Excel 2016
图2-7-3 次要关键字 图2-7-2 排序按钮及排序对话框
图2-8-3 合并计算对话框
办公软件实训教程
Excel 2016
创建数据透视表
—— 合并计算和透视表
办公软件实训教程
Excel 2016
学习目标
能够对不同工作表中的数据进行合并计算，对数据量较大 的数据通过数据透视表查看、汇总、排序，使用切片器筛选， 并能美化透视表，建立透视图。
办公软件实训教程
Excel 2016
办公软件实训教程
图2-8-12 创建切片器的显示效果
办公软件实训教程
Excel 2016
三.创建数据透视图 操作步骤如下： 1.单击“数据透视表工具”选项卡“分析”下的“数据透视图”按钮，在弹出的“插入图表”对话框中单击“折线图”，再选择“带数据标记的折线图”， 如图2-8-16所示。 2.单击“确定”，效果如图2-8-17所示。