02-Excel初阶操作-学习笔记

数据透视表专题续

利用筛选字段切换数据

场景：在数据透视表中显示各个所属地区每个月筛选出宠物用品类别，销售部门为三科的发生额，并在会议时展示，样例如下所示。
具体操作
- 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错误：在利润率所在列，右击——数据透视表选项——勾选对于错误值显示，后面的输入框里可输入不同的提示符
点击任意单元格——插入选项卡——数据透视表——将“科目名称”拖拽到行字段，将“费用属性”拖拽到列字段，“金额”拖拽到值字段——设计选项卡——总计——仅对列启动——鼠标选中实际发生额或预算额所在的单元格————数据透视表分析选项卡——字段、项目和集——计算项，在弹出界面如下图所示设置