[Excel] 数据透视表

数据透视表在本质上是一个从数据库生成的动态汇总报表。这里所指的数据库既可以位于一个工作表中(以表格形式)，也可以位于外部数据文件中

对数据进行汇总，可以通过排序数据和编辑公式加以解决，但是通常来说，选择数据透视表是更好的选择，因为创建数据透视表的过程只需要几秒钟，不需要任何公式，而且还可以生成美观的报表。不仅如此，与创建公式相比，数据透视表更不容易出错

数据透视表能从不同的角度或方面观察数据汇总

创建数据透视表的过程是一个交互的过程，需要不断尝试各种布局，直到得出满意的结果

本文练习数据透视表的数据源

数据透视表(数据源)https://download.youkuaiyun.com/download/Hudas/85504246

---

某企业2020年所有分店、产品、渠道分类的销售明细记录表共包含10000条记录，放置在"数据源"工作表中，部分数据如下图所示:

问题1:企业要求根据数据源中的销售明细记录，按照分店和渠道两个维度对销售金额进行分类汇总

1.在数据区域内选定任意单元格(如B2单元格)，创建数据透视表

2.弹出"创建数据透视表"对话框，选择数据源区域以及数据透视表的放置位置

3.单击"确定"按钮，Excel将创建一个空白数据透视表，并显示"数据透视表字段"列表任务窗格

"数据透视表字段"任务窗格一般位于Excel窗口的右侧，拖动其标题栏可将它移动到你喜欢的位置

提示

1.设置数据透视表的实际布局:将字段名称从"数据透视表字段"任务窗格的顶部拖到"数据透视表字段"任务窗格的底部4个框中的任何一个区域

2.要从数据透视表中去掉某个字段，可以从"数据透视表字段"任务窗格底部选择该字段，然后将其拖走即可

3.如果某个区域中有多个字段，那么可以通过拖动字段名来更改字段顺序，此操作将决定嵌套方式，也将影响数据透视表的显示外观

4.要从数据透视表中临时删除一个字段，可以在"数据透视表字段"任务窗格的顶部去掉该字段名左侧的复选标记。这样，数据透视表将不再显示该字段。重新勾选字段名称后，该字段将会出现在其原来的位置

5.如果在"筛选"区域增加一个字段，则该字段将出现在下拉菜单中，从而使得你能够通过一个或多个项来筛选所显示的数据

按日期筛选的数据透视表标题

将"日期"字段拖动到筛选区域，现在数据透视表只显示单独一天(从单元格B2的下拉列表中选择)的数据

根据问题1的要求，选中分店、分类和金额字段并将其拖曳到指定的透视表区域内，即可得到分类汇总结果

注意

"数据透视表工具"选项卡是Excel中的上下文选项卡，需要选中数据透视表的任意位置才会出现，如果选中的是空白单元格则该选项卡会在功能区中自动隐藏

为了将A4单元格的"行标签"和B3单元格的"列标签"显示为具有实际含义的字段名称，设置数据透视表的报表布局为"表格形式"

经过简单的鼠标拖曳操作，即可从一万条数据中轻松得到想要的分类汇总结果如上图所示

数据透视表可按照条件对海量数据进行快速分类汇总，可以根据用户需求快速调整报表布局和统计分析维度

问题1.1:按照产品和渠道分类两个维度对销售金额进行分类汇总

仅需调整数据透视表的字段布局，将数据透视表行区域中的"分店"换成"产品"，即可将工作表中的数据透视表结果同步更新

问题1.2:对全年销售记录表按照渠道分类、分店、产品3个维度进行分类汇总

仅需调整数据透视表的字段布局，在数据透视表行区域中放置"分类"和"分店"字段，在数据透视表列区域中放置"产品"字段，即可将工作表中的数据透视表结果同步更新

多维度分类汇总需求，用数据透视表可以在短短几秒内轻松满足

---

数据透视表还可以自定义分组将数据归类分组汇总，如将全年数据按季度、月份分类汇总

某企业的2020年全年销售部分记录如下图所示:

工作中经常需要按照时间周期对数据进行分组和统计分析，数据源中的仅有日期字段，且并没有月份、季度等字段，我们可以借助数据透视表中的分组功能，将日期按月份、季度自动分类汇总

问题2:将日期按照季度和月份对销售金额进行分类汇总

1.首先根据数据源创建数据透视表，在"数据透视表字段"窗格中选中"日期"字段并将"日期"字段拖曳至数据透视表行区域，在数据透视表的日期所在位置单击鼠标右键，选择"组合"

2.弹出"组合"对话框，同时选中"月"和"季度"，单击"确定"按钮

这样即可让数据透视表自动将日期按照季度和月份分组显示

3.由于要求是对销售金额进行分类汇总，所以将"金额"字段拖曳至数据透视表的值区域，即可轻松实现同时按照季度和月份对金额进行分类汇总

4.由于默认生成的数据透视表采用的是报表布局是压缩形式，季度和月份两个字段都被压缩在A列显示，要想让季度和月份两个字段分别在不同列上展示，可以调整数据透视表的报表布局为"表格形式"

这样得到的数据透视表中，季度字段放置在A列显示，月份字段放置在B列显示

此时数据透视表中B列的月份数据的字段名称是"日期"，选中B3单元格，在编辑栏中将其改为"月" ，修改完毕后，字段名称即可被命名为"月"

这样就完成了将全年数据按季度、月份分类汇总的要求

问题2.1:按照季度和月份两个维度的基础上，再添加分店维度对对销售金额进行分类汇总，这时仅需将"分店"字段拖曳至数据透视表列区域即可

在使用了分组功能的数据透视表中，同样可以根据需要调整或添加数据透视表字段，数据透视表的结果会自动更新

灵活运用数据透视表的分组功能、合理调整字段布局，即可完成多个维度的数据统计分析，大幅提升工作效率

---

问题3: 根据用户需求将汇总结果向下钻取，得到支持和构成这个结果的明细数据

某企业在全年工作总结会议上，当展示到和平路店在零售渠道的护发素产品销售额为8618元时，要求查看构成这个数字的具体销售明细记录

可利用数据透视表的数据透视功能快速完成

1.双击数据8618所在的单元格(D6单元格)，数据透视表将从此数据自动向下钻取，新建工作表展示构成这个汇总数据的明细数据

这个新生成的工作表"Sheet1"中所有记录，都是同时满足数据透视表各种行字段、列字段条件约束的，如当前案例的钻取明细同时满足一下3个条件:

分店 = 和平路店

分类 = 零售

产品 = 护发素

查看完明细后即可将该工作表删除，删除该工作表不会对数据透视表产生任何影响

当要求查看的明细变动时，可以采用同样的方法按指定条件瞬间展示数据明细来源

数据透视表的这种向下钻取功能是默认打开的，可以通过设置关闭此功能，关闭后在数据透视表中双击数据则会弹出错误提示

要想打开数据透视表的钻取数据功能，可以进入数据透视表选项进行设置，操作如下:

1.在数据透视表中选中任意单元格(如C5单元格)，单击鼠标右键，选择"数据透视表选项"

 2.弹出"数据透视表选项"对话框，在"数据"选项卡下选中"启用显示明细数据"复选框

 这样即可在数据透视表中开启双击数据钻取明细数据的功能，如果你想保护数据透视表，也可以取消选中"启用显示明细数据"复选框，不让别人随意查看明细数据

问题4:在实际工作中经常会遇到数据源的数据会发生增减变动的情况，如何设置数据透视表跟随数据源的变动自动更新?

方法1:手动调整数据透视表的数据源范围

某企业的2020年全年销售记录如下图所示:

按分店、渠道分类以及产品三个维度对销售金额进行分类汇总处理

1.当销售记录不断增加时，可以手动调整数据透视表的数据源

对于当前案例，由于数据源放置在当前工作薄中，所以直接选择"更改数据源"即可;当数据源是其他Excel文件或其他渠道等外部数据来源时，可以选择"连接属性"对外部数据源进行具体设置

2.选择"更改数据源"后，Excel会弹出"更改数据透视表数据源"对话框，根据改动后的数据源范围为修改引用区域即可

3.在修改数据透视表的数据源后，为了保证数据透视表结果能够同步更新，可以刷新数据透视表

当前案例中仅有一个数据透视表，可以直接选择"刷新"；当工作薄包含多个数据透视表时，可以选择"全部刷新"，将当前工作薄中所有数据透视表批量刷新

方法2:设置动态引用数据源，创建动态数据透视表

手动调整数据透视表数据源的方法适用于数据源范围偶尔变动时，当数据源范围经常变动时，可以采用设置动态引用区域创建动态数据透视表的方法，让数据透视表结果跟随数据源自动更新

1.将光标定位到数据源中任意单元格(如A1单元格)，按<Ctrl + T>组合键或使用菜单操作创建Excel超级表

创建完毕后，生成超级表如下所示:

注意

超级表的"表格工具"是上下文选项卡，当选中超级表区域时才会显示"表格工具"选项卡;当选中空白单元格时，此选项卡将隐藏

由于超级表具有随着数据源的变动自动调整表格范围的特性，所以将普通区域创建为超级表就可以利用超级表的行列自动扩展功能，为数据透视表创建动态数据源

2.创建好超级表"表1"后，更改数据透视表的数据源为"表1"即可

这样得到的数据透视表即可跟随数据源的范围变动而自动更新，免去了手动调整数据透视表数据源的麻烦

提示

如果要将超级表转换为普通区域，可以单击"表格工具"下的"设计"选项卡下的"转换为区域"按钮

当数据透视表的数据源从超级表转换为普通区域后，也就同时失去了自动更新数据透视表结果的功能

---

如果是根据工作表中的数据创建数据透视表，则最好先为区域创建一个表格(选择"插入"|"表格"|"表格"命令)，这样做之后如果通过增加新行扩展了表格，则Excel将会自动刷新数据透视表，而不需要手动声明新的数据区域

---

如何让报表能够与用户交互动态更新?

当数据透视表中的字段较多、用户的查询条件经常变动时，与其每次都重新调整一遍数据透视表的字段布局，不如给数据透视表植入选择器(切片器)，让报表能够与用户交互动态更新

切片器是一个交互式的控件，使用它可以很容易地筛选数据透视表中的数据

每个切片器表示一个特定的字段

按分店、渠道分类以及产品三个维度对销售金额进行分类汇总处理得到如下数据透视表

问题5:根据指定的店员快速查询其所在分店的报表结构

1.选中数据透视表中的任意单元格(如A3单元格)，插入切片器

2.插入切片器后，切片器的位置及大小可以自定义设置

3.如果需要查看店员"高平"的销售数据，只要在切片器中单击"高平"即可，数据透视表中的数据会同步更新

数据透视表切片器中的选项可以采用按住鼠标左键不松开连续选择，或按<Ctrl>键并单击不连续选项这两种方法进行选择

当需要清除所有筛选条件时，单击切片器右上角的"清除筛选器"按钮即可

数据透视表中可以根据需要插入多个切片器，当使用多个切片器同时筛选时，数据透视表会展示同时满足所有切片器中条件的数据结果

当不再需要某些切片器时，选中切片器并按<Delete>键即可删除

灵活运用数据透视表中的切片器，可以让报表按用户需求快速实现交互动态更新

---

工作中有时会遇到将报表按条件拆分为多张分表并分别放置到不同的工作表中，当总表更新时，所有分表同步更新的需求，为此我们可以使用报表筛选功能批量实现多表拆分

某企业按季度和分店分类汇总得到的数据透视表如下所示:

问题6: 要求将上述总表按季度拆分为4张分表，并且与总表保持同步更新

1.将要求的拆分条件所在字段(如"季度"字段)放置在数据透视表的筛选区域

2.选中数据透视表任意单元格(如A3单元格)，在"数据透视表工具"下的"分析"选项卡中选择"显示报表筛选选项"

3.数据透视表则会执行自动拆分多表的操作，生成4张工作表，分别放置在第一季、第二季、第三季、第四季的分表数据

第一季

第二季

第三季

第四季

这些自动生成的分表与数据透视表总表共用同一个数据缓存，当数据源变动时，所有分表会跟随总表同步更新，一劳永逸地帮助我们解决多表拆分及同步更新问题

注意

由于所有分表的报表布局和字段结构都与总表相同，所以如需调整报表结构，要先删除所有分表，然后按新的需求调整好总表的报表结构，最后利用数据透视表的报表筛选功能重新批量生成多张分表

由总表拆分生成的分表在不需要时可以随意删除，删除分表不会对数据透视表总表产生任何影响，即使删除所有分表，也可以在有需要时再次从总表重新拆分生成分表，十分方便