EXCEL数据透视表在数据分析中的.pptx
EXCEL数据透视表在数据分析中的高级运用,1,何为数据透视表,数据透视表(Pivot Table)是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。之所以称为数据透视表，是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。,2,数据透视表的格式,了解数据透视表结构了解几个术语透视表布局的几种方法设置透视表的样式设置报表布局修改字段和项目名称显示/隐藏分类汇总显示隐藏表格总计,合并居中字段标签处理数据透视表中的错误值改变字段的汇总方式对数据进行手动/自动排序设置数字格式显示/隐藏空数据项刷新数据透视表复制移动数据透视表,3,了解数据透视表的结构,透视表分4个区域 行标签（行字段）：用于分类字段下的项目，垂直排列 列标签（列字段）：用于分类字段下的项目，水平排列 数值（数据项）：用于汇总计算字段下的数据 报表筛选（页字段）：用于筛选整个数据透视表,4,透视表布局的几种方法,基本布局方法：1、在右侧的字段列表和四个小窗格中拖放字段,或者勾选字段 2、注意:勾选字段时 如果字段是文本型,自动出现在“行标签”区域 如果字段是数值型,自动出现在“数值”区域经典布局方法 1、即03版的布局方法,直接在字段列表和透视表之间拖放字段 2、恢复经典显示模式的方法:右键“数据透视表选项”勾选经典布局模式”数据量大,布局出现停止响应怎么办?解决方法:推退布局更新,待全部布局完毕后,再统一更新,5,设置透视表的样式,创建的透视表是默认的样式和格式,可以根据需要选择一个喜欢的样式 方法:在“设计计”选项卡中进行 不需要默认的样式时,可以清除,6,设置报表的布局,报表布局有三种1、压缩形式：所有字段压缩一列显示2、大纲形式：所有字段分列显示，分类汇总在顶部3、表格形式：所有字段分列显示，分类汇总在底部,7,修改字段和项目名称,数值字段默认的名称一般为“求和项:销售额”、“计计数项:销售额”,很难看,需要修改名称方法：在单元格直接修改 双击字段名称单元格,在对话框里修改字段下的项目名称也可以修改,比如把“当月汇总”修改为“当月合计 方法:直接在单元格里修改,8,显示/隐藏分类汇总,在默认情况下,每个分类字段都有分类汇总,也就是我们常说的“小计”。当不需要这个分类汇总时,可以隐藏起来不显方法：右键快捷菜单的分类汇总”*“命令,9,显示/隐藏表格总计,每个透视表都有两个总计：行总计和列总计 行总计：透视表右侧一列或几列的总计 列总计：透视表底部一行的总计 行总计不需要时，可以隐藏起来，具体方法：在“数据透视表选项”里设置 在命令按钮里设置,10,列总计,合并居中字段标签,当行字段和列字段中有两个以上的字段时，外层的字段项目可以合并居中显示。方法：在“数据透视表选项”对话框中进行,11,处理数据透视表中的错误值,如果在数据源中有错误值单元格，或者透视表中使用计算公式，那么数据透视表中也会出现错误值。此时需要将这些错误值进行适当处理（比如不显示）方法：在“数据透视表”对话框中进行。,12,改变字段的汇总方式,在默认情况下，数值型字段的计算方式是“求和”，文本型字段的计算方式是“计数”可以根据实际情况和需要，更改字段默认的计算方式。方法：右键快捷菜单中的“数据汇总依据”命令 或者在值字段对话框中进行,13,对数据进行手动/自动排序,列字段和行字段会按照一般的排序规则进行了自动升序非序,不一定满足实际需要可以手工进行重新排序,方法:拖动字段项目单元格边框也可以进行自定义排序 先定义一个自定义序列 再进行自定义排序,14,设置数字格式,透视表的数字格式可以根据需要进行任意设置方法：选择区域，实用普通的单元格格式命令 在“值字段设置”对话框中进行，或者右键快捷命令,15,显示/隐藏空数据项,默认情況下,分类字段中没有数据的项目会被隐藏,这样就会对表格结构的完整性造成破坏。可以一直显示没有数据的空数据项。方法:在“字段设置”对话框中进行,16,刷新数据透视表,数据源变化后，可以刷新数据透视表，更新数据方法:单击右键，执行“刷新”命令辅助功能:刷新透视表还可以自动调整列宽,17,复制/移动数据透视表,复制透视表的正确方法是：1、选择整个透视表 2、复制 3、粘贴移动透视表 命令按钮,18,设置字段分类汇总方式,字段可以有很多汇总方式，从而进行不同的分类汇总，分析不同的指标 求和 计数 最大值 最小值方法 快捷菜单命令 值字段设置对话框,19,设置字段显示方式,字段可以设置为各种显示方式，从而得到不同的分析结果 普通 差异 差异百分比 百分比 按某一字段汇总 占同行汇总的百分比 占同列汇总的百分比方法 值字段设置对话框 案例03 设置字段显示方式,20,自动/手动组合项目,组合：就是把字段下的某些项目进行组合，使之成为一个新的项目三种数据的组合方式 文本：手动组合 数字：自动组合 日期：自动组合方法：快捷菜单中组合（创建组）命令,21,添加自定义计算字段,我们还可以在数据透视表中添加计算字段，以便得到新的分类汇总和分析指标计算字段：在透视表中创建公式，对现有的字段进行计算，得到的一个新的字段（在数据源中没有）方法：07版：“选项”“公式”“计算字段”10版：“选项”“域、项目和集”“计算字段”案例,22,添加自定义计算项,计算项：就是在某个字段下，对已有的项目进行计算，得到新的项目方法 先定位要插入计算项的字段 07版：“选项”“公式”“计算项”10版：“选项”“域、项目和集”“计算项”案例11,23,查看明细数据,双击某个汇总数据单元格（即值单元格），就可以查看该数值的明细数据当行标签和列表前内有多个字段时，双击某个外层字段，就会折叠或展开该字段的下一级字段明细,24,注意数据的外泄,双击数值单元格，可以查明细数据，由于这个功能的存在，当涉及敏感数据时，最好不要把透视表发给外人。解决方法 把透视表转换为普通的数值单元格(选择性粘贴),25,以多个二维表格数据创建,当多个工作表数据是二维表格（即数据区域的第一列和第一行是文本，其它的都是数字）时，可以使用多重合并计算数据区域透视表进行合并。具体方法 快捷键 ALT+D+P(P要按2下)案例,26,创建动态数据源的数据透视表,普通命令(插入透视表)的方法创建的数据透视表,其数据源是一个固定的单元格区域,如果数据源发生变化(主要是增加),新增加的数据不会自动添加到透视表.解决数据变化的方法:使用动态名称(OFFSET)1.选择 公式-定义名称。2.在弹出的对话框中输入名称，3.在引用位置后面输入=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5)然后点确定，这样一个动态的数据源就建好了。,27,
