第8课 创建数据透视表

创建数据透视表

——合并计算和透视表办公软件实训教程Excel2016制作：孔红霞于靖悦学习目标【知识目标】认识不同工作表中的数据合并的计算方法，以及通过透视表查看、汇总、排序数据的方法，使用切片器筛选、美化透视表和建立透视图的方法。【能力目标】掌握数据合并计算、透视表汇总和排序、切片器筛选、美化透视表和建立透视图的技能。【素养目标】培养克服困难、迎难而上的工作态度，做有理想、敢担当、能吃苦、肯奋斗的新时代好青年。办公软件实训教程Excel2016办公软件实训教程Excel2016人们对Excel数据透视表的强大功能赞叹不已，卓卓我还不会呢。看着海量的网店销售数据和多个部门提供的需要汇总的数据，卓卓我一筹莫展。在师傅的帮助下，我认识到要不怕困难，刻苦钻研，而且学会了合并计算，建立数据透视表，并在透视表中查看和汇总数据，以及对数据进行排序。我现在首先要完成的就是将销售部、人事部、工艺部提供的数据进行合并计算（见图2-8-1）。办公软件实训教程Excel2016图2-8-1三个部门的数据将多个表中的数据合并计算；根据数据建立透视表，编辑透视表，操作透视表，完成数据的分析、汇总和排序，完成切片器筛选数据。办公软件实训教程Excel2016办公软件实训教程Excel2016图2-8-3合并计算对话框对于放置在不同工作表中，有相同结构的表格，我们需要将相关数据合并到一起，以便对所有信息有一个直观的了解。要达成此目的可以使用数据透视表，除此之外Excel还为我们提供一种简单快捷有效的工具，合并计算。一.合并计算1.操作步骤①新建表新建一张表，需要将人事部、工艺部、销售部的费用明细汇总到这张表中，即存放合并计算的结果。②选中左上角的单元格，表示合并计算的结果从这个单元格开始放置。③单击【数据】选项卡下“合并计算”按钮，如图2-8-2所示。④弹出合并计算对话框，“函数”设置“求和”。“引用位置”设置先单击按钮，切换到“人事部”表，选择要合并的数据，如图2-8-3所示。⑤再单击按钮，回到合并计算对话框，完成人事部数据引用。单击“添加”按钮，将数据添加到引用位置中。按同样方法添加工艺部和销售部的数据。如图2-8-4所示。图2-8-2合并计算图2-8-4添加引用位置办公软件实训教程Excel2016图2-8-5合并计算结果一.合并计算⑥添加数据完成后，勾选“标签位置”下面的两个选项，“首行”和“最左列”。⑦单击“确定”。查看合并的结果，三张表中的数据汇总到一张表中如图2-8-5所示。若用户希望合并后的结果能够随着数据源的变化而自动更新时，可以在“合并计算”对话框中勾选“创建指向源数据的链接”复选框，这样合并结果就可以动态更新了。“合并计算”对话框中，如果不勾选“首行”和“最左列”，合并计算的结果是没有首行和最左列的。在本例中添加了最左列，求和时是按照最左列的字段进行分类汇总计算的，若其他两表中字段的顺序、内容不一致，会将不同的行或列的数据根据字段进行分类汇总。“合并计算”命令的默认计算方式为求和。除此之外，“合并计算”命令的计算方式还包含计数、平均值、最大值、最小值，可以在“合并计算”对话框的函数列表中进行相应更改。对不同表格的数据进行合并计算时，表格中要有相同的字段名。小贴士：二.数据透视表数据透视表是一种可以快速汇总大量数据的交互式表格。可以将表格中的相关数据汇总到一张表中，是Excel最实用、最常用的功能之一。使用数据透视表可以深入分析数据、以多种方式查询数据、汇总数据。1.创建数据透视表①单击【插入】选项卡下的“数据透视表”按钮，出现如图2-8-6示的对话框。②选择要分析的数据表区域。③选择现有工作表或新工作表放置透视表，单击“确定”。④生成数据透视表框架，如图2-8-7所示。⑤在数据透视表字段中，选择需要的字段，如销售日期、货品名称、销售数量。系统会默认放置在列或行标签上。也可自己拖动字段到不同的框内。效果如图2-8-8所示。办公软件实训教程Excel2016图2-8-6数据透视表对话框图2-8-7数据透视表框架图2-8-8数据透视表办公软件实训教程Excel2016图2-8-9销售日期由“行”变为“列”图2-8-10数据透视表的样式2.编辑透视表系统生成透视表后，用户可以通过设置透视表的布局、样式、选项等标签，美化编辑创建的透视表。①重组数据透视表创建数据透视表后不能直接修改透视表所显示的数据项，但表中的字段可以在字段栏或区域栏互换。如将销售日期由“行”变为“列”，如图2-8-9所示。也可以根据需要随时在透视表中增加或删除字段（在数据透视表字段上操作即可）。②美化数据透视表。通过【数据透视表工具|设计】选项卡下的“布局”“数据透视表样式”“数据透视表样式选项”等功能组命令来美化透视表。其中，设置样式的方法是单击【数据透视表工具|设计】选项卡，会弹出数据透视表的各种样式，如图2-8-10所示，可以选择其中一种样式，然后再通过“数据透视表样式选项”功能组命令设置行或列。办公软件实训教程Excel2016图2-8-11插入切片器对话框图2-8-12创建切片器后的显示效果3.编辑透视表①切片器Excel切片器可以快速实现数据筛选，用户可在切片器的数据上查询当前数据状态。方法：单击【数据透视表工具|分析】下的“插入切片器”命令，在插入切片器对话框中选择字段，如货品名称和销售数量，如图2-8-11所示，单击“确定”。货品名称和销售数量两个切片器就生成了，如图2-8-12所示。当用户选择“货品名称”切片器上的货物名称时，如显示器，数据透视表和“销售数量”切片器上则显示出显示器的相关数据信息。使用切片器查询方便，数据清晰，一目了然。按【Delete】键可删除切片器。②更改计算方式数据透视表默认汇总方式是“求和”，它的计算类型还有计数、平均值、最大值、最小值等。更改汇总方式有两种方法：一种是在“数据透视表字段”对话框中，在“值字段”下拉列表中，选择“值字段设置”按钮，更改计算类型。第二种方法是在透视表中鼠标右击，弹出对话框，单击“值字段设置”按钮更改。③排序数据透视表还可以对所选列或行排序，如：在透视表中按货品销售金额排序，方法如下：将货品名称字段拖动到行标签，销售金额字段拖动2次到值区域，如图2-8-13所示。图2-8-13字段设置办公软件实训教程Excel2016图2-8-14排序设置图2-8-15排序后的效果图3.编辑透视表③排序在销售金额2字段下拉列表中选择“值字段设置”。在“值字段设置”对话框中设置值显示方式为“降序排列”，基本字段为“商品名称”，如图2-8-14所示。单击“确定”，商品按销售金额降序排序，效果如图2-8-15所示。办公软件实训教程Excel2016图2-7-16带数据标记的折线图三.创建数据透视图操作步骤如下：1.单击【数据透视表工具|分析】下的“数据透视图”按钮，在弹出的插入图表对话框中单击“折线图”，再选择“带数据标记的折线图”，如图2-8-16所示。2.单击“确定”，效果如图2-8-17所示。图2-8-17数据透视图办公软件实训教程Excel2016图2-8-18网店销售明细表图2-8-3合并计算对话框1.将费用明细表中，人事部、工艺部、销售部三个部门的数据进行合并计算，并使合并后的数据动态更新。方法如下：①打开“Excel素材文件”|“8”文件夹中的“费用明细表.xlsx”，另存为“费用明细表效果.xlsx”。在效果表中新建工作表Sheet4。②选中Sheet4左上角的A1单元格。单击【数据】选项卡【数据工具】组的“合并计算”按钮。③弹出合并计算对话框，“函数”设置“求和”。单击按钮，设置“引用位置”。切换到人事部表，选择A1：B6单元格区域，单击按钮回到合并计算对话框，完成人事部的引用位置，如图2-8-3所示。单击“添加”按钮，添加引用位置。如图2-8-4所示。接下来，按上述操作步骤，完成工艺部和销售部的引用位置添加。④添加引用位置完成后，勾选“标签位置”下面的两个选项，“首行”和“最左列”。⑤勾选“创建指向源数据的链接”复选框，这样合并结果就可以动态更新了。⑥单击“确定”。查看合并结果，三张表中的数据汇总到一张表中，如图2-8-5所示。⑦保存文件“费用明细表效果.xlsx”，存盘退出。2.为网店销售明细表中的数据创建数据透视表。网店销售明细表的数据样表如图2-8-18所示。①新建工作簿，将“网店销售明细表.xlsx”的内容复制下来，另存为“网店销售明细表效果.xlsx”，在效果表中进行操作。

②单击【插入】选项卡下的“数据透视表”按钮。③在“数据透视表”对话框中，选择数据表区域；选择放置数据透视表的位置为“新工作表”。图2-8-4添加引用位置图2-8-5合并计算结果办公软件实训教程Excel2016图2-8-19数据透视表效果图（5）在“数据透视表字段”中选择“货品名称”“销售日期”“销售数量”3个字段，系统会默认放置在列或行标签上，生成了数据透视表，效果如图2-8-19所示。3.编辑透视表。在上题建立的透视表中增加“销售金额”字段，并对行、列字段进行适当调整，用“数据透视表样式浅色16”来美化透视表。操作如下：①“在数据透视表字段”中选择“销售金额”字段，将它拖曳到值区域。将“销售日期”由行标签拖动到值区域，效果如图2-8-20所示。②美化透视表。单击【数据透视表工具|设计】，选择样式“数据透视表样式浅色16”。再选择“数据透视表样式选项”中的列标题、镶边列效果，美化后的透视表效果如图2-8-21所示。4.利用“插入切片器”功能，筛选“机箱”的销售数量等数据信息。①单击【数据透视表工具|分析】下的“插入切片器”命令。②在插入切片器对话框中筛选货品名称和销售数量字段，单击“确定”。③单击“货品名称”切片器中的“机箱”，在透视表和销售数量切片器中筛选出“机箱”的相关数据信息，如图2-8-22所示。5.生成新的工作表，将透视表中的“销售金额”按降序排列。①在Sheet1中，单击【插入】选项卡下的“数据透视表”按钮。②在数据透视表对话框中，选择数据表区域；选择放置数据透视表的位置为“新工作表”。③单击“确定”，生成数据透视表框架。④在“数据透视表字段”中将货品名称字段拖动到行标签，销售金额字段拖动两次到值区域，生成新的数据透视表，如图2-8-13所示。图2-8-20加减字段后的效果图图2-8-21美化后的透视表

图2-8-22筛选“机箱”相关数据办公软件实训教程Excel2016⑤在“销售金额2”字段下拉列表中选择“值字段设置”。在值字段设置对话框中设置值显示方式为“降序排列”，基本字段为“商品名称”，单击“确定”。查看透视表，发现“销售金额2”列数据中标出了降序排名序号，效果如图2-8-15所示。6.为任务2所创建的数据透视表创建透视图。首先选择任务2所生成的透视表（见图2-8-19），单击【数据透视表工具|分析】下的“数据透视图”按钮，在弹出的“插入图表”对话框中单击“折线图”，再选择“带数据标记的折线图”，单击“确定”，生成透视图效果如图2-8-17所示。7.保存文件“网店销售明细表效果.xlsx”，存盘退出。办公软件实训教程Excel2016图2-8-23合并计算效果图1.打开“Excel素材文件”|“8”文件夹中的“案例8练一练1.xlsx”文件。（1）将分别放在不同工作表中的3次培训考试的成绩合并计算，求各科的总分、平均分、最高分，并将合并计算结果分别存放在Sheet4中A1、D1、G1开始的单元格中，效果如图2-8-23所示。（2）将文件另存为“练一练1效果.xlsx”于个人文件夹下。2.新建工作簿，将“网店销售明细表.xlsx”的内容复制下来，进行如下操作。（1）创建数据透视表，选择3个字段“客户”“货品名称”“销售数量”。（2）将“客户”放在行标签，“货品名称”和“销售数量”放在值区域。（3）将“货品名称”由“值区域”拖动到“行标签”，在“值区域”增加“销售金额”字段，查看透视表的效果，如图2-8-24所示。（4）为透视表选择样式“数据透视表样式浅色17”，样式选项设置为“列标题、镶边行”，效果如图2-8-25所示。（5）插入客户和销售数量切片器，单击客户切片器中的“海阳”，查看数据变化，效果如图2-8-26所示。（6）新建一个含有客户、销售数量字段的透视表，按销售数量升序排列。要求排名