Excel 进阶之数据透视表

 
数据透视表是一个快速对明细数据表进行各种分类汇总的数据分析工具
 
 
一. 数据录入
     保证无空标题行、无重复行标题、无合并单元格、无非法日期和数字格式的数据明细表。
  • 如果明细表有标题为空,将无法制作数据透视表,需要将标题补充完整。
  • 如果存在相同的标题,数据透视表会自动添加序号以区分,所以尽量不要存在相同的列标题。
  • 如果存在合并单元格,除第1个单元格外,其他均作为空值处理。所以尽量取消合并的单元格,填充完整。
  • 如果存在非法日期,生成的数据透视表中,无法按日期格式进行年、月、日格式的筛选和组合,尽量转换成 Excel 认可的日期格式。
  • 如果存在文本型数字,将无法在数据透视表中正确求和。需将文本转换成数值型数字才可以进行相关的汇总统计。
 
 
二. 创建透视表
 
  1. 选中要汇总的数据内容
  2. 点击插入
  3. 插入数据透视表
  4. 在弹出的数据透视表菜单中,设置常规选项
    可以手动更改要汇总的数据内容
    可以选择在当前表格还是新建表格中创建透视表
  5. 点击确定,就生成了一张空的数据透视表
初始化
 
 
三. 透视表结构
  1. 字段列表
    明细数据表的所有第一行列标题都会显示在“字段列表”中,相当于数据透视表的数据源。
    可以通过「数据透视表分析」—>「字段列表」—>打开透视表生成器
  2. 字段设置区域。把要显示的字段拖放在相应区域内,就可以动态的根据区域进行汇总了。
  3. 数据透视表的显示区域。组合成的数据透视表会在这儿显示。
  • 筛选器,可按指定条件过滤筛选数据进行汇总统计。
  • 行标签,该区域的字段会按照上下排列显示。
  • 列标签,该区域的字段会按照左右排列显示。
  • 数值,将要统计的数据列放在该栏目内就行,可选各种汇总统计方式,如计数、求和、平均等。
2透视表生成器
 
四. 汇总统计
  1. 选中要汇总统计的列,拖进「透视表生成器」行区域中
  2. 选中要汇总统计的值列,拖进「透视表生成器」值区域中
  3. 选择要汇总的方式,常规的有求和、计数、平均值、最大/小值等
    默认数字是按照求和汇总,文字按照计数汇总。
    可通过点击下图中叹号区域,调出「字段设置」菜单,也可通过右键点击「汇总列」菜单调出。
  4. 选择要汇总字段的显示方式,一般默认就行,其它常用的有汇总百分比等
3透视表汇总
 
五. 结构布局
选中要汇总统计的列,拖进「透视表生成器」行区域中
  1. 选取数据透视表,在顶部找到「设计」—>「报表布局」
  2. 选取想要的布局方式
  • 压缩式布局,默认布局方式。
  • 大纲式视图,按列标题分列显示,汇总显示在每项的上方
  • 表格视图,按标题分列显示,汇总显示在每项的下方
  • 重复所有项目标签,可以填充相同的内容。
  • 不重复所有项目标签,取消重复,是上一步的逆操作。
  1. 分类汇总选项
    不显示分类汇总
    在组底部显示所有分类汇总
    在组顶部显示所有分类汇总
  2. 总计选项
    为行和列禁用
    为行和列启用
    仅为行启用
    仅为列启用
  3. 空行选项
    在每个项目后插入空行
    删除每个项目后的空白行
  4. 数据透视表选项
    显示,展开/折叠按钮控制、字段标题筛选、错误值/空值的替代、打印选项
    布局,自动调整列宽(默认选中,调格式时需去掉)、更新时保留单元格格式开关,合并单元格控制
    数据,数据刷新控制
     4透视表布局
 
  上图报表布局为表格视图,不显示分类汇总,禁用总计,合并单元格
 
六. 高级功能
  1. 切片器。在数据源字段中,想要查看经过某一字段进行过滤筛选后的数据汇总,可动态交互使用。
    调出方法为:「数据透视表」—>「插入切片器」
  2. 创建计算字段。当原始数据表中没有某些统计时,需要计算产生一列数据,可通过「数据透视表」—>「字段、项目和集」创建计算字段。然后进行汇总统计
    如下图中的计算字段:有效问题占比,按照如图所示进行设置,拖进透视表生成器的「值栏目」中即可
5高级交互功能
其它小技巧
  • 如何批量打开 Excel 文件:一次选中多个,然后按「回车键」即可。
  • 新建工作薄快捷方式:Ctrl + N
  • 选取某列全部内容:选取第一行,按住 shift 键不松,双击选中的单元格下边线(鼠标放到下边线时,光标「指针」会变成「小手」)
  • 查找重复行:选取数据区域—>开始—>条件格式—>突出显示单元格规则—>重复值;使用筛选器可精准查找;( 常用的还有 数据—>删除重复项 )
  • 快捷筛选:选取要筛选的值—>右键—>筛选—>按单元格值筛选(多种筛选方式及排序方式)
  • 输入日期:输入 6-22 即可自动补全年份;「Ctrl + ;」 默认为当前日期。
  • 插入多行:选取多行,插入
  • 删除空行:选取列—> Ctrl + G —>定位—>定位条件—>空值—>删除 ;或者使用筛选器,筛选空值,然后批量删除
Excel 是分析处理数据的工具,数据是各种事务的量化表达,这背后都是基于人性的考量
分享到:

发表评论

昵称

沙发空缺中,还不快抢~