Excel 数据透视表怎样做？

发布时间：

2024-10-07 22:58

阅读量：

Excel之四步创建数据透视表与修改数据透视表

在整理数据时，我们会遇到要将大量的数据集按照一定的类别进行统计，并形成一定的总结报告。我们会以如下为例：按照“Customer Type（客户类型）”来统计出不同类型的“Total Sales（总销售额）”。

在创建数据透视表之前，我们先将原数据区域转换成“表”：

1. 点击数据区域中的某个单元格，再点击“插入”选项卡下的“表格”。

2. 打开创建表的对话框后，点击“确定”。

3. 修改“表名称”为Sales。

为数据区域创建表后，我们来通过四步创建数据透视表。

1. 先点击数据表格的某个单元格，无需选择全部，再点击“插入”选项卡最左侧的“数据透视表”按钮。

2. 打开“创建数据透视表”对话框后，会自动选择数据表格“Sales”的整个区域；我们要将数据透视表放在“新工作表”中，点击“确定”。

创建好的数据透视表如下，但这并不是我们所期望的。

3. 在右侧的“数据透视表字段”中，我们需要进行相应的设置来实现通过数据透视表展现数据统计的结果；同时在菜单栏中还出现了与数据透视表相关的“数据透视表工具”及其下面的“分析”与“设计”选项卡。

在“数据透视表字段”的工具栏中，数据表格的所有列标签都变成了字段；在此例中，我们需要勾选“Customer Type”这一字段。

4. 勾选字段中的“Total”。我们注意到“Customer Type”和“Total”两个字段分别放到了“行”和“值”这两个区域。

通过上面的四步，我们即创建出一个基础的数据透视表：按照客户类型统计各个类型的销售总额。

不仅如此，若原始数据中增加了新的“Customer Type”及对应的销售数据后，我们可以点击“刷新”按钮，使数据透视表中的数据及时得到更新。

以上是创建数据透视表的方式之一，即先点击数据区域中的某个单元格，再据此创建数据透视表。

另一种方式是，先确定我们要放置数据透视表的位置，再点击“数据透视表”按钮。例如，在A12单元格创建第二个数据透视表，按照“Account Manager”来统计其对应的销售数据。

1. 点击A12单元格，点击“插入”选项卡下的“数据透视表”。

2. 我们要告知所要分析的原始数据，在此例中为之前我们所创建的“表”Sales。

3. 点击“确定”后即可开始创建另一个数据透视表。

4. 勾选相应的字段“Account Manager”和“Total”。

同样我们通过四步完成了数据透视表的创建。

通过以上两次数据透视表的创建，我们发现，当勾选的字段为文本数据时，会自动添加到“行”区域，而数值数据会自动添加到“值”区域。

我们现在需要添加另一个字段“State”，但需要将该字段放在“列”区域中，此时不能简单的勾选，而需要我们用鼠标放在该字段上，将其拖至“列”区域。

如果我们需要将时间（字段“Order Year”）放在“列”区域，我们则需要先将“State”拖出来，再将“Order Year”拖至“列”区域。

数据透视表可以帮我们对数据进行不同的试验性操作，从不同的角度来发现数据的趋势。

Excel数据透视表的妙用

Excel的数据透视表如此强大的原因之一，在于其可根据需求方便地进行修改设置，不仅是数据的变换，还有数据统计与运算的方式。

01 值汇总方式

如下图所示，我们插入了一个数据透视表，其按照“Customer Type（客户类型）”来统计各类型的销售总额。

我们现在需要在此数据透视表的基础上进行一些修改，即统计各类型的订单数，而非销售总额（当前“Total”的统计所使用的方式是“Sum（求和）”），而在统计订单数时我们则需要用到另一种方式“Count（计数）”。

在数据透视表中可以有多种方法完成以上的修改需要，而简单直接的一种是鼠标右击当前“Sum of Total”下的某个数值单元格，在“值汇总方式”这里我们可以选择不同的统计方法，当前为“求和”，其他有“计数、平均值、最大值”等等。

在此例中，我们选择点击“计数”，完成后数据透视表即可按照客户类型统计其相应的订单数。

我们来看第二个数据透视表，将各Account Manager的各年份的销售数据展示出来并进行Grand Total（总计）的统计。

02 值显示方式

我们现在不需要看具体的销售额，而是要将其以百分比的方式展示出来。

点击销售额数据中的某个单元格，鼠标右击，在“值显示方式”下我们可选择不同的数据计算显示的方式。

在此例中我们选择点击“总计的百分比”，完成数据的销售额则会转换成百分比。

我们也可以各年份为整体，计算每个Account Manager在该年份中销售额的占比，在此需选择点击“值显示方式”下的“列汇总的百分比”。

03 数据格式的修改

我们可以对当前的数据透视表中的数据格式进行修改，鼠标右击某个数据单元格，选择点击“数字格式”。

打开数字格式的设置对话框后，点击“货币”，选择对应的货币类型和显示方式。

点击“确定”后，数据透视表中的数据即可添加对应的货币符号。

04 数据透视表的样式与布局

在修改数据透视表的样式与布局之前，我们先将当前的数据透视表的字段进行一些修改：将“State”和“Order Year”均拖至“行”区域，数据透视表即展示各地区下各个年份的销售数据。

点击“数据透视表工具”下的“设计”选项卡，我们可以在“数据透视表样式”中选择所需的样式。

我们可以在“布局”功能区域中修改“分类汇总”的方式，将原先的“在组的底部显示所有分类汇总”改成“在组的顶部显示所有分类汇总”。

在“报表布局”中，我们可以选择“以表格形式显示”。

在“以表格形式显示”的基础上，还可以选择点击“重复所有项目标签”。

若要回到最初的布局，点击“以压缩形式显示”即可。

通过以上四个方面的介绍，我们可以发现在使用数据透视表时，可以对其进行多元化的修改，如计算的方式、数据格式以及布局样式等等，这可以帮助我们在进行数据整理或报表制作时提供更多的选择与便利。

Excel数据透视表的分组、排序与筛选功能

在如下所示的数据透视表中，我们将“State”与“Order Year”都放在了“行”区域中，故数据根据各个地区按照各个年份进行展示。

01 数据透视表的分组

为了更好地演示数据透视表的分组功能，我们需要的不仅仅是年份的数据，而需要更具体的时间的数据，例如季度和月份，所以我们对当期的数据透视表进行调整。

我们将“Order Year”这一字段移出“行”区域，而将“Order Date”字段拖至“列”区域，在“列”区域中我们可以看到会出现“年、季度”标签，并且数据透视表会自动按照年份将数据进行分组，而在更低版本的Excel中这种情况是不会发生的，按照一般逻辑，当把具体的日期“Order Date”作为列标签时，数据透视表应该会按照每个具体的日期来展示数据。

既然Excel已自动为我们按照年份把数据分组，我们来看一下如何将这些数据展开。

点击数据透视表中的某个年份标签，再点击“数据透视表工具”下的“分析”选项卡。

点击“取消组合”后，数据透视表即可展开所有日期的数据。

在此例中，由于具体的日期过多，以此种方式展示数据并不是一个好的选择，所以我们可以选择按照特定的字段进行分组。

点击某个日期标签，再点击“分组字段”功能按钮，打开其对话框后，选择“季度”和“年份”两个字段。

点击“确定”后数据透视表即会按照年份和季度来展示数据。

我们可以对字段进行折叠，点击功能区的“-”按钮。

我们也可以对其中某个年份的标签展开字段，点击标签左侧的“+”即可。

02 数据透视表的排序

在当前的数据透视表中，数据默认是按照“行”与“列”标签来排序的。我们也可以根据需要更改排序的方式，例如我们想要按照“总计”数据的由小到大来排序。

点击“总计”下的某个数据单元格，再点击“数据”选项卡下的“升序”。

在数据透视表中对数据进行排序与在一般的数据区域或数据表格并无不同。

03 数据透视表的筛选

如果我们只需要展示某些地区某些年份的数据，我们可以在数据透视表中进行筛选，主要通过其“行”“列”标签来进行筛选。

点击“列标签”，只勾选2015和2016年的数据，再点击“确定”。

点击“列标签”，只勾选VIC和WA两个地区的数据，再点击“确定”。

数据透视表则筛选出两个地区的2015和2016年的数据。

如果我们要清除筛选，有如下方式：

1. 点击数据透视表中的某个数据单元格，点击“数据”选项卡下的“清除”按钮。

2. 点击“数据透视表工具”下的“分析”选项卡，点击“清除”下的“清除筛选”。

以上两种方式都是用于清除数据透视表中的所有筛选。

如果要清除“行”或“列”中的筛选，可按照如下操作：

3. 点击“行标签”或“列标签”的筛选器，点击“从X中清除筛选”。

至此，我们了解在Excel数据透视表中可以对数据进行分组、排序与筛选等操作，帮助我们根据不同的需要展示数据并进一步制作报表。

Excel数据透视表的“报表筛选”

在使用Excel的数据透视表时，我们可以通过其分组、排序与筛选等功能来展示一定条件下的数据，而在整理好这些数据后，我们还可以通过“报表筛选”这一功能将个人的数据筛选出来生成单个的工作表供其查看。

如下图所示，我们插入了一个数据透视表：各个地区在2015和2016年的按照年份与季度分组后的数据统计表。

现在我们需要将某个Account Manager在这两年的数据单独制作成数据表，这样就可以逐一发送给他们并且互不影响。

问题是，在当前的数据透视表中，并没有Account Manager这一字段或标签。在“数据透视表字段”中，我们可以将“Account Manager”字段拖至“筛选”区域，在数据透视表的上方则出现了一个有关此字段的筛选器。

当我们点击筛选器的下拉按钮后，可看到所有的Account Manager名称。

点击我们所需单独查看的人员，再点击“确定”。

数据透视表则仅展示所选之人的数据统计结果。

如果要同时展示或生成多个人的数据，我们可以按照如下的步骤：

1. 勾选“选择多项”后，会选中所有的Account Manager，如果我们只需选择其中几个人，则取消勾选“全部”，再选择所需的人员名称即可。

2. 点击“确定”后，我们看到数据透视表发生了变化，所展示的数据仅是所有选中的Account Manager的统计结果。

3. 而这并未满足我们的需要，我们点击“数据透视表工具”下的“分析”选项卡，再点击最左侧“选项”功能的下的“显示报表筛选页”。

4. 打开“显示报表筛选页”的对话框后，会显示所有的可筛选字段，此例中仅有“Account Manager”。

5. 点击“确定”后，我们发现会生成多张新的工作表，这些工作表均以所选的Account Manager的名称命名且仅展示其对应的数据。

为了更好地区分这些单独的数据透视表与Sheet1工作表中的主数据透视表，我们可以重新命名Sheet1，并且修改数据透视表本身的名称。

我们再来了解一下其他有关数据透视表的注意事项与功能按钮。

1. 数据透视表中的数据是无法直接进行修改的：当点击某个数据单元格，尝试对其修改时，会弹出一个警告对话框。

2. 虽然无法对数据进行修改，但数据透视表提供了一个有趣的功用，当鼠标双击某个数据单元格（例如C16单元格）时，会自动生成一个与此数据关联的详细数据的新工作表；在这张新工作表Sheet6中，我们可查看数据详情。

在这新工作表中，我们需要注意的是，当数据发生变化时，此数据表格并不会自动更新，不像数据透视表那样可以进行更新，这里仅是提供详细数据以供查看，建议查看后将此工作表删除。

3. 当我们不小心关闭“数据透视表字段”列表，可点击“分析”选项卡下的“字段列表”。

4. 我们可以通过“+/-按钮”来关闭或显示分组标签的“+”或“-”符号。

5. 我们可以通过“字段标题”来关闭或显示“行标签”与“列标签”。