数据透视表专题续
利用筛选字段切换数据
-
场景：在数据透视表中显示各个所属地区每个月筛选出宠物用品类别，销售部门为三科的发生额，并在会议时展示，样例如下所示。
-
具体操作
-
step1：制作数据透视表
点击任意单元格——插入选项卡——数据透视表——将“订购日期”拖拽到行字段，“所属地区”拖拽到列字段，“金额”拖拽到值字段
-
step2：筛选字段
将“产品类别”和“销售部门“分别拖拽到报表筛选字段——右击鼠标——数据透视表选项——在布局和格式上的在报表筛选区域显示字段的下拉三角，选择”水平并排“——点击确定，如下图所示
-
step3 ：筛选
在“产品类别”和“销售部门“下拉筛选分别选择宠物用品和三科
-
step4：数据可视化展示（开会是用到）
选择区域（除去总计的所有值字段（金额）区域）——开始选项卡——条件格式——选择数据条
-
-
操作演示：
利用切片器切换数据
-
场景：在数据透视表中显示各个所属地区每个月筛选出宠物用品类别，销售部门为三科的发生额，样例如下图所示
-
具体操作
-
step1：制作数据透视表
点击任意单元格——插入选项卡——数据透视表——将“订购日期”拖拽到行字段，“所属地区”拖拽到列字段，“金额”拖拽到值字段
-
step2 ：制作切片器
点击数据透视表当中的任意单元格——插入选项卡——切片器——在弹出界面勾选“产品类别”和“销售部门“——点击确定，效果如下图
-
step3 ：设计
我们希望销售部门按照一科，二科，三科，四科的顺序展示，应该进行如下操作：文件选项卡——更多——选项——高级选项，下拉找到自定义序列，输入”一科,二科,三科,四科“注意，科与科之间使用中文的逗号——点击确定，确定——将原本的切片器删除，重新制作。
选中两个切片器——切片器选项卡——切片器样式——点击其中一个切片器——切片器选项卡——列：2（表示一行里面并排放2个筛选选项）——然后再定义另一个切片器一行并排放3个筛选选项
-
step4 ：筛选
点击销售部门切片器右上角的
——选择三科——点击产品类别切片器选择宠物用品
-
-
操作演示
补充：如果Excel文件的后缀名为.xls，如何做切片器呢？
文件选项卡——另存为后缀名为.xlsx的Excel文件——关闭文件，并重新打开后缀名为.xlsx的Excel文件。这样切片器就可以正常使用了。
利用表格更新数据透视表
-
场景：在制作好数据透视表之后，如果数据源发生了业务变更，如何操作使得数据透视表实时变更。以下是两种业务变更的情形
- 在查账是发现某笔金额出现了差错
- 现有表格记录到2007-12-1日的交易信息，现在又增加了2007-12-2日的交易信息
-
针对上述两种业务具体操作分别为
-
在原有的数据透视表中右击鼠标刷新即可
-
删除旧的数据透视表——插入选项卡——表格——确定——插入选项卡——数据透视表——将“订购日期”拖拽到行字段，“所属地区”拖拽到列字段，“金额”拖拽到值字段
假设在数据源中添加一笔交易记录——来到数据透视表中右击鼠标刷新
-
-
操作演示（情景二）
表格工具的常见应用
-
自动更新（如更新数据透视表）
注意！ 插入表格时如果数据源有标题请勾选表包含标题
-
美化数据
点击数据源的任意单元格——插入选项卡——表格——确定——插入选项卡——图表，下拉选择”二维柱状图“——图表设计选项卡——样式
-
表格转化为普通区域
选中表格区域——表设计选项卡——工具”转换为区域“
-
插入切片器
插入选项卡——表格——插入选项卡——切片器——勾选‘’所属区域‘’——插入选项卡——图表，下拉选择”二维柱状图“
如果图标随着所属区域的变化而变大变小?
右击——设置图表区格式——勾选”不随单元格改变位置和大小“
数据透视表与排序工具
-
数据透视表按照金额（数字大小）排序
开始选项卡——排序和筛选条件——升序
-
数据透视表按照文字排序（默认是按照汉语拼音的顺序）
文件选项卡——更多——选项——高级选项，下拉找到自定义序列，输入”一科,二科,三科,四科“注意，科与科之间使用中文的逗号——点击确定，确定——开始选项卡——排序和筛选条件——自定义排序
筛选过去两年的销售冠军
应用场景
-
场景一：筛选过去两年的前三名的销售冠军，样例如下图左侧
-
场景二：过去2年销量最好的三个月里面前三名的销售冠军，样例如下图右侧
具体操作
-
点击任意单元格——插入选项卡——数据透视表——将“订购日期”和”销售人员“依次拖拽到行字段，“金额”拖拽到值字段——点击“订购日期”所在列的任意单元格——右击”组合“——只勾选”年“，点击确定——”销售人员“所在列的下拉三角——值筛选——前n项——弹出界面选择n=3——确定——点击金额所在列——开始选项卡——排序和筛选条件——降序
-
点击任意单元格——插入选项卡——数据透视表——将“订购日期”和”销售人员“依次拖拽到行字段，“金额”拖拽到值字段——点击“订购日期”所在列的任意单元格——右击”组合“——勾选”年“和”月“，点击确定——”月（订购日期）“所在列的下拉三角——值筛选——前n项——弹出界面选择n=3——确定——再次点击”月（订购日期）“所在列的筛选选项——其他排序选项——在弹出界面，如下图所示设置
操作演示
-
情形一
-
情形二
创建数据透视图
应用场景
-
统计不同城市的金额并制作对比柱状图，样例如下图左侧
-
单独看每个城市的商品销售情况，并使得金额降序排列，样例如下图右侧
具体操作
- 点击任意单元格——插入选项卡——数据透视表——将“所属地区”拖拽到行字段，“金额”拖拽到值字段——插入选项卡——图表，选择二维柱状图——数据透视表分析——字段按钮，选择全部隐藏——设计选项卡，选择图表样式
- 点击任意单元格——插入选项卡——数据透视表——将“所属地区”拖拽到行字段，“金额”拖拽到值字段——点击金额所在列的任意单元格——开始选项卡——排序与筛选，选择降序——插入选项卡——图表，选择二维柱状图——数据透视表分析——字段按钮，选择全部隐藏————插入选项卡——切片器——勾选产品类别——设计选项卡，选择图表样式
操作演示
-
情形一
-
情形二
创建动态数据看板
应用场景
创建两个数据透视图，分别按月份统计金额和按地区统计金额，当切换不同选项时，两个数据透视图同时变化，样例如下
具体操作
-
step1：制作数据透视图
-
按月份统计金额
点击任意单元格——插入选项卡——数据透视表——将“订购日期”拖拽到行字段，“金额”拖拽到值字段——在“订购日期”所在列，右击，组合，选择月——插入选项卡——图表，选择折线图——数据透视表分析——字段按钮，选择全部隐藏————插入选项卡——切片器——勾选产品类别——设计选项卡，选择图表样式
-
按地区统计金额
点击任意单元格——插入选项卡——数据透视表——将“所属地区”拖拽到行字段，“金额”拖拽到值字段——插入选项卡——图表，选择柱状图——数据透视表分析——字段按钮，选择全部隐藏————插入选项卡——切片器——勾选销售部门——设计选项卡，选择图表样式
-
-
step2：两图联动
选择按地区统计金额——复制数据透视图和切片器到按月份统计金额所在的子表——分别在产品类别和销售部门所在的切片器右击，报表连接——将图一和图二所在的两张数据透视表同时勾选——点击确定
操作演示
补充：一些排版问题
-
选中图片，按住Alt键不放，拖拽图片大小，图片边界会自动吸附到单元格的边框；
-
数据透视图随着数据的变换而发生位置或大小的变化如何解决?
点击数据透视表任意单元格——右击——数据透视表选项——在布局和格式中勾选更新时列宽不随变换——点击确定
数据透视表写公式
应用场景
-
通过数据透视表计算哪种商品的利润更高？（即（金额-成本）/ 金额）
字段间的计算 ：是指在数据源中存在两列，分别为金额和成本。
-
按科目名称统计预算和实际发生额之间的差额（即预算-实际发生）
计算项是指预算和实际发生在数据源中是同一列。
具体操作
-
点击任意单元格——插入选项卡——数据透视表——将“产品类别”拖拽到行字段，“金额”和”成本“分别拖拽到值字段——数据透视表分析选项卡——字段、项目和集——计算字段，在弹出界面如下图所示设置——利润率所在列——右击，设置单元格格式——百分比，保留2位小数——除0错误：在利润率所在列，右击——数据透视表选项——勾选对于错误值显示，后面的输入框里可输入不同的提示符
-
点击任意单元格——插入选项卡——数据透视表——将“科目名称”拖拽到行字段，将“费用属性”拖拽到列字段，“金额”拖拽到值字段——设计选项卡——总计——仅对列启动——鼠标选中实际发生额或预算额所在的单元格————数据透视表分析选项卡——字段、项目和集——计算项，在弹出界面如下图所示设置
补充：如果不想要差额这一列，如何操作？
鼠标选中实际发生额或预算额所在的单元格————数据透视表分析选项卡——字段、项目和集——计算项，在弹出界面的名称输入框后面的下拉三角，找到差额，点击公式（M）后面的删除——点击确定
操作演示
-
情形一
-
情形二
GetPivotData函数
该函数仅能在数据透视表中可以使用
如果不想使用该函数，可进行如下操作
数据透视表分析——选项，下拉三角——取消勾选”生成GetPivotData函数“
但是，这个一定要慎重，因为GetPivotData函数有很大的用处。例如以下应用场景：抓取数据透视表的数据填写到普通表中
操作演示
