（中职）Office办公软件应用案例教程-excel第8课 创建数据透视表ppt教学课课件

1、YCF正版可修改PPT（中职）Office办公软件应用案例教程-excel第8课 创建数据透视表ppt教学课课件创建数据透视表 合并计算和透视表办公软件实训教程Excel 2016X学习目标 能够对不同工作表中的数据进行合并计算，对数据量较大的数据通过数据透视表查看、汇总、排序，使用切片器筛选，并能美化透视表，建立透视图。办公软件实训教程Excel 2016办公软件实训教程Excel 2016 人们对Excel数据透视表的强大功能赞叹不已，卓卓我还不会呢。看着数据量很大的网店销售数据和多个部门提供的需要汇总的大量数据，卓卓我一筹莫展。好在有师傅的帮助，卓卓学习了合并计算，建立数据透视表，在透视

2、表中查看数据、汇总数据和排序数据。问题解决了，卓卓当然很高兴，卓卓现在首先要完成的就是将销售部、人事部、工艺部提供的数据进行合并计算，我们来看看他是怎么做的吧。图2-8-1 样表办公软件实训教程Excel 2016将多个表中的数据合并计算；根据数据建立透视表，编辑透视表，操作透视表，完成数据的分析、汇总和排序，完成切片器筛选数据。办公软件实训教程Excel 2016办公软件实训教程Excel 2016图2-7-2 排序按钮及排序对话框对于放置在不同工作表中，有相同结构的表格，我们需要将相关数据合并到一起，以便对所有信息有一个直观的了解。要达成此目的可以使用数据透视表，除此之外 Excel还为我

3、们提供一种简单快捷有效的工具，合并计算。一.合并计算1.操作步骤新建表新建一张表，需要将人事部、工艺部、销售部的费用明细汇总到这张表中，即存放合并计算的结果。选中左上角的单元格，表示合并计算的结果从这个单元格开始放置。单击“数据”选项卡下“合并计算”按钮，如图2-8-2所示。弹出合并计算对话框，“函数”设置“求和”。 “引用位置”设置先单击 按钮，切换到“人事部”表，选择要合并的数据，如图2-8-3所示。再单击 按钮，回到合并计算对话框，完成人事部数据引用。单击“添加”按钮，将数据添加到引用位置中。按同样方法添加工艺部和销售部的数据。如图2-8-4所示。图2-7-3 次要关键字图2-8-3 合

4、并计算对话框图2-8-4 添加引用位置办公软件实训教程Excel 2016 图2-8-5 合并计算结果一.合并计算添加数据完成后，勾选“标签位置”下面的两个选项，“首行”和“最左列”。单击“确定”。查看合并的结果，三张表中的数据汇总到一张表中如图2-8-5所示。2. 让合并结果动态更新。若用户希望合并后的结果能够随着数据源的变化而自动更新时，可以在“合并计算”对话框中勾选“创建指向源数据的链接”复选框，这样合并结果就可以动态更新了。3. “合并计算”对话框中，如果不勾选 “首行”和“最左列”，合并计算的结果是没有首行和最左列的。在本例中添加了最左列，求和时是按照最左列的字段进行分类汇总计算的，

5、若其他两表中字段的顺序、内容不一致，会将不同的行或列的数据根据字段进行分类汇总。“合并计算”命令的默认计算方式为求和。可以在“合并计算”对话框的函数列表中更改。“合并计算”命令的计算方式包含求和、计数、平均值、最大值、最小值。小贴士：对不同表格的数据进行合并计算时，表格中要有相同的字段名。 对不同表格的数据进行合并计算时，表格中要有相同的字段名。小贴士：二.数据透视表数据透视表可以将表格中的数据整合到一张表中，是一种可以快速汇总大量数据的交互式表格。使用数据透视表可以深入分析数据、以多种方式查询数据、汇总数据。1.创建数据透视表单击“插入”选项卡下的“数据透视表”按钮，如图2-8-6所示。选择

6、要分析的数据表区域。选择现有工作表或新工作表放置透视表，单击“确定”。生成数据透视表框架，如图2-8-7所示。在数据透视表字段中，选择需要的字段，如销售日期、货品名称、销售数量。系统会默认放置在列或行标签上。也可自己拖动字段到不同的框内。效果如图2-8-8所示。办公软件实训教程Excel 2016图2-8-6 数据透视表对话框 图2-8-7 数据透视表框架图2-8-8 数据透视表办公软件实训教程Excel 2016图2-8-9 销售日期由“行”变为“列”效果图2-8-10 数据透视表的样式2.编辑透视表系统生成透视表后，用户可以通过设置透视表的布局、样式、选项等标签，美化编辑创建的透视表。重组

7、数据透视表数据透视表是显示数据信息的视图，创建数据透视表后不能直接修改透视表所显示的数据项。但表中的字段可以在字段栏或区域栏互换。如将销售日期由“行”变为“列”，效果如图2-8-9所示。也可以根据需要随时向透视表增加或删除字段，在数据透视表字段上操作即可。美化数据透视表。可以通过布局、样式、选项、格式等工具美化透视表。设置样式的方法是单击“数据透视表工具”下的“设计”按钮，弹出数据透视表的样式，可以选择其中的一种样式，然后再设置“数据透视表样式选项”中的行和列，如图2-8-10所示。办公软件实训教程Excel 2016图2-8-11 切片器对话框 图2-8-12 创建切片器的显示效果3.编辑透

8、视表切片器Excel切片器可以快速实现数据筛选，用户可在切片器的数据上查询当前数据状态。方法：单击“数据透视表工具”中“分析”下面的“插入切片器”命令，在“插入切片器”对话框中选择字段，如货品名称和销售数量，如图2-8-11所示，单击“确定”。货品名称和销售数量两个切片器就生成了，效果如图2-8-12所示。当用户选择货品名称切片器上的货物名称，如显示器时，数据透视表和销售数量切片器上则显示出显示器的相关数据信息。查询方便，数据清晰、一目了然。按“Delete”键可删除切片器。更改计算方式数据透视表默认汇总方式是“求和”，它的计算类型还有计数、平均值、最大值、最小值等。更改汇总方式有两种方法：一

9、种是在“数据透视表字段”对话框中，在“值字段”下拉列表中，选择“值字段设置”按钮，更改计算类型。第二种方法是在透视表中鼠标右击，弹出对话框，单击“值字段设置”按钮更改。排序数据透视表还可以对所选列或行排序，如：在透视表中按货品销售金额排序，方法如下：将货品名称字段拖动到行标签，销售金额字段拖动2次到值区域，如图2-8-13所示。图2-8-13 字段设置办公软件实训教程Excel 2016图2-8-11 切片器对话框 图2-8-12 创建切片器的显示效果3.编辑透视表排序数据透视表还可以对所选列或行排序，如：在透视表中按货品销售金额排序，方法如下：将货品名称字段拖动到行标签，销售金额字段拖动2次

10、到值区域，如图2-8-13所示。在销售金额2字段下拉列表中选择“值字段设置”。在 “值字段设置”对话框中设置值显示方式为“降序排列”，基本字段为“商品名称”，如图2-8-14所示。单击“确定”，商品按销售金额降序排序，效果如图2-8-15所示。办公软件实训教程Excel 2016图2-7-16 “1”按钮下总计项三.创建数据透视图操作步骤如下：1.单击“数据透视表工具”选项卡“分析”下的“数据透视图”按钮，在弹出的“插入图表”对话框中单击“折线图”，再选择“带数据标记的折线图”，如图2-8-16所示。2.单击“确定”，效果如图2-8-17所示。图2-8-17 数据透视图效果办公软件实训教程Ex

11、cel 2016图2-8-18 网店销售明细表样表图2-8-3 合并计算对话框1. 将费用明细表中，人事部、工艺部、销售部三个部门的数据进行合并计算，并使合并后的数据动态更新。方法如下：打开“费用明细表.xlsx”，另存为“费用明细表效果.xlsx”。在效果表中新建工作表Sheet4。选中Sheet4左上角的A1单元格。单击“数据”选项卡下“数据工具”组的“合并计算”按钮。弹出合并计算对话框，“函数”设置“求和”。 单击 按钮，设置“引用位置”。切换到人事部表，选择 A1：B6单元格区域，单击 按钮回到合并计算对话框，完成人事部的引用位置，如图2-8-3所示。单击“添加”按钮，添加引用位置。接

12、下来，按上述操作步骤，完成工艺部和销售部的引用位置添加，最终效果如图2-8-4所示。添加引用位置完成后，勾选“标签位置”下面的两个选项，“首行”和“最左列”。勾选“创建指向源数据的链接”复选框，这样合并结果就可以动态更新了。单击“确定”。查看合并结果，三张表中的数据汇总到一张表中，如图2-8-5所示。保存文件“费用明细表效果.xlsx”，存盘退出。2. 为网店销售明细表中的数据创建数据透视表。网店销售明细表的数据样表如图2-8-18所示。新建工作簿，将 “网店销售明细表.xlsx”的内容复制下来，另存为“网店销售明细表效果.xlsx”，在效果表中进行操作。 单击“插入”选项卡下的“数据透视表”

13、按钮。在“数据透视表”对话框中，选择数据表区域；选择放置数据透视表的位置为“新工作表”。 图2-8-4 添加引用位置图2-8-5 合并计算结果办公软件实训教程Excel 2016图2-7-21 第5题效果图5.按照自定义序列 “销售部、研发部、文秘部”进行排序。选择数据任一单元格。单击【数据】选项卡【排序和筛选】组的“排序”按钮，弹出“排序”对话框。在主要关键字中选择“部门”，在“次序”中选择“自定义序列”，如图2-7-5所示。在“自定义序列”对话框的“输入序列”中输入“销售部、研发部、文秘部”，如图2-7-6所示，单击“添加”按钮，单击“确定”。回到“排序”对话框，单击“确定”。效果如图2-

14、7-21所示。6.复制Sheet1工作表中的内容至Sheet3，在Sheet3中筛选“男”职工记录。选择任一数据单元格，单击【数据】选项卡【排序和筛选】组的“筛选”按钮。在“性别”筛选按钮下选择“男”。筛选出男员工数据记录。效果如图2-7-22所示。7. 在Sheet3中，筛选“销售业绩=85”记录。单击【数据】选项卡【排序和筛选】组的“清除”按钮。清除当前的筛选条件，让数据全部显示。在工作表中单击“销售业绩”的筛选按钮，选择“数字筛选”下的“大于或等于”选项，如图2-7-23所示。在弹出的对话框中输入85，单击“确定”，最终效果如图2-7-24所示。图2-7-22 第6题效果图图2-7-23

15、 数字筛选条件 图2-6-13 “COUNTIF”参数设置8.在Sheet3中，利用高级筛选，筛选“销售业绩85”且“表达能力83”的人员。在工作表下方第19行，复制列标题行第1行。设置条件：“销售业绩85”、“表达能力83”。如图2-7-8所示。单击【数据】选项卡【排序和筛选】组的“高级”按钮。弹出“高级筛选”对话框如图2-7-9所示。在“列表区域”文本框中自动选择了数据区域。单击“条件区域”右侧的折叠按钮，在工作表中选择满足筛选条件的单元格区域，即E19:F20。单击“确定”，筛选出符合条件的一条记录，效果如图2-7-10所示。9. 在Sheet3中，利用高级筛选，筛选“销售业绩85”或“

16、写作能力85”的人员，将Sheet3重命名为“筛选”。单击【数据】选项卡【排序和筛选】组的“清除”按钮。先清除当前的筛选条件，让数据全部显示。更改筛选的条件，在单元格区域E20和G21中输入条件，如图2-7-11所示。即“销售业绩85”和“写作能力85”。注意：两个条件不在同一行。单击【数据】选项卡【排序和筛选】组的“高级”按钮。弹出“高级筛选”对话框。设置“方式”为“将筛选结果复制到其他位置”， “列表区域”文本框中默认填充所有数据区域， “条件区域”则选择单元格区域E19:G21，“复制到”选择单元格A24。如图2-7-12所示。单击“确定”，筛选出符合条件的记录，从A24开始放置。效果如

17、图2-7-13所示。将工作表重命名为“筛选”。办公软件实训教程Excel 2016图2-7-9 “与条件高级筛选”对话框图2-7-10“与”筛选的数据图2-7-11 “或”条件设置图2-7-12 “或条件筛选”对话框图2-7-13 “或”筛选出的数据办公软件实训教程Excel 201610.复制Sheet1工作表至Sheet4，在Sheet4中按部门汇总平均分。查看“总计”项和“各部门分类汇总”项。将Sheet4重命名为“汇总”。首先对“部门”列数据进行排序。选择数据清单里的任一数据单元格。单击【数据】选项卡【分级显示】组的“分类汇总”按钮。弹出“分类汇总”对话框，设置“分类字段”为部门，“汇

18、总方式”为平均值，“汇总项”为平均分，如图2-7-14所示。最后单击“确定”按钮，效果如图2-7-15所示。查看“总计”项和“各部门分类汇总”项按部门汇总后，在图的左上角有 字样，单击“1”按钮，显示总计项，如图2-7-16所示。单击“2”按钮，显示二级汇总项数据，如图2-7-17所示。将工作表更名为“汇总”。11.将文件另存为“员工考评表效果.xlsx”到自己姓名文件夹下。图2-7-14 分类汇总对话框图2-7-15 按部门分类汇总平均分图2-7-16 “1”按钮下总计项 图2-7-17 “2”按钮下各部门分类汇总”办公软件实训教程Excel 2016图2-7-26 练技能第4题效果1.新建工作簿，将“员工考评表.xlsx” 内容复制到工作表中，保存文件名为“练一练效果.xlsx”。 将 “练一练效果.xlsx”中Sheet1的内容复制到Sheet2、Sheet3 、Sheet4中。在Sheet2进行如下操作。2.对数据按“总分”降序排列。3.先