任务五　数据透视表

任务描述

对于数据量庞大的Excel工作表,当用户需要对其中的数据进行多种复杂比较时,可以使用数据透视表来完成。

任务分析

为了能够灵活地汇总和分析表格中的各种数据,用户可以使用数据透视表。数据透视表具有很强的交互性,在创建数据透视表后,用户可以根据需要对数据进行多种排序和筛选,还可以显示区域中的数据明细。

知识点链接

数据透视表是一种非常有用的数据分析工具,无须借助公式或函数就能够自动汇总和分析数据。与分类汇总以及分级显示通过修改用户表格的结构进而显示对数据的汇总不同,数据透视表是在工作簿里创建新的元素,当用户添加或编辑表格中的数据时,所作出的更改也将在数据透视表中显示。

1.创建数据透视表

制作完用于创建数据透视表的源数据后,就可以使用数据透视表向导创建数据透视表了。具体操作步骤如下。

①打开需要创建数据透视表的工作表,单击“插入”→“表格”→“数据透视表”按钮(或者单击“数据透视表”右侧的下拉按钮,再选择“数据透视表”命令),打开“创建数据透视表”对话框。

②在“请选择要分析的数据”命令组中,选中“选择一个表或区域”单选按钮,单击“表/区域”文本框右侧的按钮。

③在工作表中选择需要作为创建数据透视表源数据的单元格区域,单击按钮返回“创建数据透视表”对话框。

④在“选择放置数据表透视表的位置”命令组中选择创建的位置,如“新工作表”。

⑤单击“确定”按钮,即可根据选择的位置在工作表中创建数据透视表。在右侧显示“数据透视表字段列表”窗格。

2.添加和删除数据透视表中的字段

在Excel中,创建的默认数据透视表中是没有数据的。可以将“数据透视表字段列表”窗格中的字段添加到数据透视表。“数据透视表字段列表”窗格分为上下两个区域:上方的字段区域显示了数据透视表中可以添加的字段,下方的4个布局区域用于排列和组合字段。数据透视表字段列表中的4个区域如下。

①报表筛选:添加字段到报表筛选区可以使该字段包含在数据透视表的筛选区域中,以便对其中独特的数据项进行筛选。

②列标签:添加一个字段到列标签区域可以在数据透视表顶部显示来自该字段的独特值。

③行标签:添加一个字段到行标签区域可以沿数据透视表左边的整个区域显示来自该字段的独特值。

④数值:添加一个字段到数值区域,可以使该字段包含在数据透视表的值区域中,并使用该字段中的值进行指定的计算。

⑤将字段添加到数据透视表有以下几种方法。

·在字段区域选中字段名称旁边的复选框,字段将按默认位置移动到布局区域的列表框中。

·右击字段区域的字段名称,在弹出的菜单中选择相应的命令“添加到报表筛选”“添加到列标签”“添加到行标签”“添加到值”。将选择的字段移动到布局区域的某个指定列表框中。

·在字段名上单击并按住鼠标左键,将其拖到布局区域的列表框中。

⑥删除字段主要有以下几种方法。

·直接将字段从布局区域拖动到布局区域外。

·取消字段区域中字段名称左侧的复选框。

·在布局区域中单击字段名称,在弹出的菜单中选择“删除字段”命令。

3.改变数据透视表中数据的汇总方式

在Excel中,数据透视表字段的汇总方式默认为“求和”,用户可以根据需要更改汇总方式,以便分析不同的数据结果。具体操作步骤如下。

①右击需要改变汇总方式的字段中任一单元格,在弹出的快捷菜单中选择“值字段设置”命令。

②弹出“值字段设置”对话框,单击“汇总方式”命令,在列表框中选择新的汇总方式,如选择“最大值”命令。

③单击“确定”按钮,即可看到最大值数据。

4.创建切片器

在Excel 2010中,可以选择使用切片器来筛选数据。单击切片器提供的按钮可以筛选数据透视表的数据。除了快速筛选之外,切片器还会指示当前筛选状态,从而便于用户轻松、准确地了解已筛选的数据透视表中所显示的内容。

5.创建数据透视图

数据透视图以图形形式表示数据透视表中的数据,此时数据透视表称为相关联的数据透视表。数据透视图是交互式的,可以对数据进行排序或筛选,来显示数据透视表数据的子集。创建数据透视图时,数据透视图筛选器会显示在图表区中,以便对数据透视图中的基本数据进行排序和筛选。在相关联的数据透视表中对字段布局和数据所做的更改会立即反映在数据透视图中。与标准图表一样,数据透视图报表显示数据系列、类别、数据标记和坐标轴。用户可以更改图表类型及其他命令,如标题、图例位置、数据标签和图表位置等。

任务设计(https://www.daowen.com)

以“数据透视表”工作表中的产品销售数据作为数据源创建数据透视表,以反映不同月份、不同业务员的产品平均销售金额情况,业务员作为行字段,产品作为列字段,月份作为筛选字段,并将透视表命名为“平均销售金额透视表”;设置产品销售基本信息透视表格式;创建“月份”和“产品”切片器,用以筛选查看不同月份不同产品的产品销售情况;创建产品销售基本信息透视图。具体操作步骤如下。

1.创建产品销售基本信息透视表

①打开“数据透视表”工作表,单击“插入”→“表格”→“数据透视表”按钮。

②打开“创建数据透视表”对话框,在“请选择要分析的数据”命令组中,选中“选择一个表或区域”单选按钮,单击“表/区域”文本框右侧的按钮。