Excel+Power BI数据分析电子课件第5章.pptx

《Excel+Power BI数据分析电子课件第5章.pptx》由会员分享，可在线阅读，更多相关《Excel+Power BI数据分析电子课件第5章.pptx（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第5章 数据透视表汇总海量数据教师：Office助手 Pivot tables summarize massive amounts of data实例51 海量数据快速分类汇总实际工作中经常会遇到包含海量数据的工作表，面对众多的数据使用公式或函数等方法进行处理，不仅运算操作复杂，而且容易出错，此时，使用数据透视表仅需用鼠标进行相关操作即可满足大部分需求，既简化了操作，又提高了工作效率。如图所示的表中包含了几百条销售明细记录，存放在“年度统计”工作表中，现要求根据年度统计中的销售明细记录，按照分部和销售渠道对销售额进行汇总。实例51 海量数据快速分类汇总此例可利用数据透视表按照要求快速实现汇总，

2、操作步骤如下：步骤1：单击A2:J373区域中的任意一个单元格(例如D3单元格)。打开“插入”选项卡，单击“数据透视表”按钮，弹出“创建数据透视表”对话框。步骤2：选中“选择单元格区域”单选按钮，在其下方的文本框中输入要分析的数据区域，通常默认选择（如果系统给出的区域选择不正确，那么用户可拖动鼠标重新选择区域），然后选中“新工作表”单选按钮，单击“确定”按钮，进入如图所示的数据透视表设计环境。实例51 海量数据快速分类汇总步骤3：在右侧“数据透视表字段”窗格中，拖动“分部”到“行”区域；拖动“销售渠道”到“列”区域；拖动“销售额”到“值”区域，即可按照分部和销售渠道得到销售额的汇总结果，如图所

3、示。实例51 海量数据快速分类汇总步骤4：将A4单元格中的“行标签”设置为具有实际含义的字段名称，需要将数据透视表的报表布局设为“以表格形式显示”，设置方法如左图所示，效果如右图所示。通过鼠标简单的拖动操作，即可从几百条数据记录中快捷的得到所需的汇总结果实例51 海量数据快速分类汇总数据透视表不仅可以按照条件对海量数据进行分类汇总，还可以灵活地改变分类汇总的方式，以多种不同方式展示数据的特征。如图所示中，要求按照商品类别和销售渠道对销售金额进行分类汇总，只需要更改数据透视表的字段布局，将数据透视表“行”区域中的“分部”更换为“商品类别”，即可将数据透视表的分类汇总结果自动更新，如图所示。实例5

4、1 海量数据快速分类汇总如果再添加新的要求，要求按照销售渠道、分店、商品类别对全年销售记录进行分类汇总，只需要更改数据透视表的字段布局，将“销售区域”和“分店”字段分别拖动到“行”区域中，将“商品类别”字段拖动到“列”区域中，即可将数据透视表的分类汇总结果自动更新，如图所示。实例52 将数据按照季度、月份分类汇总如果数据源中有日期字段，利用数据透视表的分组功能将日期归类分组汇总到月份、季度，然后按照月份、季度进行分类汇总，下面通过实例介绍具体介绍。如图所示，要求将其按照季度和月份对销售额进行分类汇总，操作步骤如下：实例52 将数据按照季度、月份分类汇总步骤1：首先将数据源创建数据透视表，在“数

5、据透视表字段”窗格中，将“日期”字段拖动到“行”区域，打开“分析”选项卡，单击“组字段”按钮，如左图所示，弹出“组合”对话框。步骤2：在对话框中选中“月”和“季度”，如中间图所示，单击“确定”按钮，将日期按照“月”和“季度”分组显示，效果如右图所示。实例52 将数据按照季度、月份分类汇总步骤3：在“数据透视表字段”窗格中，将“销售额”字段拖到“值”区域，即可实现按照季度和月份对销售额进行分类汇总，如左图所示。步骤4：在A列中季度和月份两个字段被压缩显示在一列，若将季度和月份两个字段分别显示在两列，可将数据透视表的报表布局更改为“以表格形式显示”，更改方法如右图所示。实例52 将数据按照季度、月

6、份分类汇总步骤5：更改报表布局后的效果如左图所示，此时，季度字段位于A列，月份位于B列，字段名为“销售日期”。步骤6：单击“销售日期”单元格，在编辑栏中将其改为“月份”，如右图所示，更改后的效果如图所示。这样就完成了将上半年销售记录按照季度、月份对销售额进行分类汇总的要求。实例52 将数据按照季度、月份分类汇总数据透视表使用分组功能后，根据需要可以再添加或调整数据透视表字段，数据透视表的结果会同步更新。例如，在按季度和月份汇总的基础上，再添加“分部”对销售额分类汇总，此时，只需将“数据透视表字段”窗格中的“分部”字段拖动到“列”区域即可，汇总结果同步更新，如图所示。实例53 动态更新数据透视表

7、结果创建数据透视表后，如果数据源的数据发生了变化，那么数据透视表中的结果也应该随之发生变化。数据源的数据变化有2种情况，一是原有数据源范围不变，数据变化，二是原有数据源范围增大或减小，数据增多或减少。1.原有数据源范围不变，数据变化若要更新数据透视表结果，单击数据透视表中的任意一个单元格(如C4)，打开“分析”选项卡，单击“刷新”按钮，如图所示，或按快捷键Alt+F5，当前数据透视表的结果随数据源内容的变化而变化。若要更新工作簿中所有的数据透视表，则单击“全部刷新”按钮。实例53 动态更新数据透视表结果2.原有数据源范围变化，数据变化使用刷新功能，更新后的数据透视表的结果有可能不准确，此时可以

8、使用以下两种方法更新数据透视表以确保结果正确。方法1：重新选择数据透视表的数据源范围如左图所示的销售记录得到右图所示的数据透视表，当销售记录持续增加时，需要重新选择数据透视表的数据源，才能确保更新后的数据透视表结果正确，操作步骤如下：实例53 动态更新数据透视表结果步骤1：单击数据透视表中的任意一个单元格，打开“分析”选项卡，单击“更改数据源”按钮，打开的下拉列表中选择“更改数据源”，如左图所示。如果数据源不在当前工作簿中，而是来自于其他Excel文件或其他外部渠道的数据源时，单击“连接属性”，对外部数据源进行设置。步骤2：单击“更改数据源”后，弹出“更改数据透视表数据源”对话框，重新选择更改

9、后的数据源区域即可，如右图所示。实例53 动态更新数据透视表结果步骤3：重新选择数据透视表的数据源后，若使数据透视表结果同步更新，打开“分析”选项卡，单击“刷新”按钮，即可更新当前数据透视表。方法2：创建超级表，动态更新数据透视表的数据源范围重新选择数据透视表数据源后，需要单击“分析”选项卡中“刷新”按钮，才能确保数据透视表结果同步更新，这种更新方式适用于数据源范围偶尔变动。如果数据源范围频繁变动，可将普通表创建为超级表，利用超级表的行列自动扩展功能，为数据透视表创建动态数据源，从而使数据透视表结果随着数据源的变化而自动更新。创建超级表的操作步骤如下：步骤1：单击数据源中的任意一个单元格(例如

10、D1单元格)，按快捷键CtrlT或者打开“插入”选项卡，单击“表格”按钮，弹出“创建表”对话框，单击“确定”按钮，即可将普通表创建为超级表。步骤2：此时，超级表的名称自动显示为“表1”，每个列标题名的右侧出现筛选符号，数据区域隔行填充颜色，选项卡区域出现“表格工具”的“设计”选项卡，当单击空白单元格时，此选项卡隐藏。实例53 动态更新数据透视表结果步骤3：创建超级表“表1”后，“表1”会随着数据源的变化自动调整表格范围，即为数据透视表创建动态数据源。如果数据透视表的数据源发生变化，更改数据源为“表1”，如左图所示，数据透视表即可随着数据源范围的变化而同步更新。步骤4：如果要取消每个列标题名的右

11、侧的筛选符号，单击数据源中的任意一个单元格，打开“数据”选项卡，单击“筛选”按钮，如右图所示。实例53 动态更新数据透视表结果若要取消超级表，将其转换为普通区域，则单击“设计”选项卡中的“转换为区域”按钮，如图所示。超级表转为普通区域后，数据透视表失去随数据源的变化而自动更新结果的功能。实例54 汇总表快速转换为明细表数据透视表通常展示数据汇总结果，这些汇总结果是由多项数据汇总得到的。根据用户需求可以将汇总结果显示为构成这个结果的明细数据，下面通过实例介绍将汇总表转换为明细表的方法。在图所示的数据透视表中，要求显示构成这个数字的具体销售明细记录。在本例中要显示的明细记录同时满足以下3个条件：分

12、部=北京总公司 销售渠道=实体店 商品类别=计算机实例54 汇总表快速转换为明细表步骤1：双击要显示明细的数据954727.05所在的单元格，此时，Excel自动创建一个新的工作表Sheet1，用来显示汇总数据954727.05对应的明细数据，如图所示。步骤2：显示明细后可将该工作表删除，删除该工作表对数据透视表不会产生任何影响，使用同样的方法可以显示数值区域任意数值的明细数据。实例54 汇总表快速转换为明细表在数据透视表中显示明细数据功能默认是打开的，此时双击数值所在的单元格即可显示构成该数值的明细数据。如果不希望他人随意查看明细数据，可将该功能关闭，关闭方法如下：步骤1：在数据透视表的任意

13、一个单元格(例如C4单元格)上右键单击，弹出的快捷菜单中单击“数据透视表选项”。步骤2：弹出“数据透视表选项”对话框，在“数据”选项卡中取消选中“启用显示明细数据”复选框，如图所示，单击“确定”按钮，即可在数据透视表中关闭显示明细数据功能。关闭后在数据透视表中双击数据则会弹出错误提示。实例55 将总表拆分为多表并同步更新利用数据透视表的报表筛选功能可以将总表按照条件批量拆分为多张分表并放置在不同的工作表中，当总表更新时，所有的分表同步更新。下面结合实例介绍总表拆分为多表的方法。某商贸公司按商品类别和分部分类汇总得到的数据透视表如图所示，要求将该总表按商品类别拆分为6张分表，并与总表保持同步更新

14、。操作步骤如下：实例55 将总表拆分为多表并同步更新步骤1：将拆分条件字段(商品类别)拖动到数据透视表的“筛选器”区域，如左图所示。步骤2：单击数据透视表中的任意一个单元格(例如B4单元格)，打开“数据透视表工具”中的“分析”选项卡，进行如右图所示的设置。实例55 将总表拆分为多表并同步更新步骤3：数据透视表按照选定的筛选项(商品类别)自动批量生成6张工作表，如图所示，分别放置冰箱、电视、计算机、空调、热水器、洗衣机的分表数据。实例55 将总表拆分为多表并同步更新6张分表中的数据来源于数据透视表的总表，因此，当总表数据变化时，所有分表中的数据跟随总表的变化同步更新。当拆分条件发生变化时，分表的

15、字段结构和报表布局随之发生变化。例如，图是按照销售渠道和商品类别分类汇总的总表，现要求以分部为条件将总表拆分为每个分部一张工作表。实例55 将总表拆分为多表并同步更新6张分表中的数据来源于数据透视表的总表，因此，当总表数据变化时，所有分表中的数据跟随总表的变化同步更新。当拆分条件发生变化时，分表的字段结构和报表布局随之发生变化。如图所示是按照销售渠道和商品类别分类汇总的总表，现要求以分部为条件将总表拆分为每个分部一张工作表。实例55 将总表拆分为多表并同步更新将拆分条件(分店)字段拖动到数据透视表“筛选器”区域，在“分析”选项卡中进行如左图所示的设置，即可以分部为条件将总表拆分为每个分部一张工

16、作表，如右图所示，每个分表的字段结构和报表布局与总表相同。实例56 添加切片器将数据动态更新数据透视表中的切片器是一个强大的筛选工具，利用切片器可以将数据按照查询条件动态更新，切片器不能在普通表格中使用，只能在智能表格或数据透视表中使用。在图所示的数据透视表中，根据指定的销售日期快速查询该销售日期所在分部的报表结果，操作步骤如下：实例56 添加切片器将数据动态更新步骤1：单击数据透视表中的任意一个单元格(如A3单元格)，打开“分析”选项卡，单击“插入切片器”按钮，弹出的对话框中选中“销售日期”复选框，单击“确定”按钮，插入“销售日期”切片器。步骤2：若要查看销售日期“2022年1月10日”的销

17、售数据，在切片器中单击“2022年1月10日”，数据透视表中的数据同步动态更新，如图所示。实例56 添加切片器将数据动态更新步骤3：如果要同时查看多个销售日期的销售数据，在切片器中按住鼠标左键进行拖动选择连续的多个销售日期，或者按住Ctrl键选择不连续的多个销售日期。步骤4：若要清除所有筛选条件，按快捷键AltC，或者单击切片器右上角的“清除筛选器”按钮，如图所示。实例56 添加切片器将数据动态更新根据需要可以在数据透视表中插入多个切片器，当对多个切片器同时进行筛选时，数据透视表显示是的同时满足所有切片器中指定条件的数据结果。若要删除切片器，选中切片器按Delete键即可删除。实例56 添加切片器将数据动态更新根据需要可以在数据透视表中插入多个切片器，当对多个切片器同时进行筛选时，数据透视表显示是的同时满足所有切片器中指定条件的数据结果。若要删除切片器，选中切片器按Delete键即可删除。