财务部学习成长培训会
第一课——数据透视表
课程引言
图片举例
为什么学习数据透视表?
什么是数据透视表
数据透视表是一种可以快速汇总、分析大量数据表格的交互式分析工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视,并建立交叉表格,用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。
使用数据透视表可以深入分析数值数据,以帮助用户发现关键数据,并作出有关企业中关键数据的决策。
关键词
【分类】【汇总】【计算】
数据类的日常工作离不开这三个方面,在基础层面解决了这三项问题,就解决了算力不足的问题,提高了效率。
现有的数据核算工作,还涉及不到【数据分析】,先把统计的工作做好,下一步才是分析。
可以说掌握了数据透视表,就是进入了数据分析的入门阶段。对于大部分的日常工作,足够了。
这是我们日常能用到的,功能最强大的免费工具了。
其他的数据分析工具,要么学习门槛高,要么收费。
综上所述:【数据透视表】是数据类工作人员必知、必会的基础技能。在我们团队中,普及了数据透视表的应用,统一数据源表,就能实现大量数据的快速分类汇总计算。
多维度
可以对同一个数据源,进行【多维度】【多报表】【分类、汇总和计算】的【展示】。
术语解释
一维表
扩展知识:一维表与二维表
确定一个数值,必须通过行列两个条件去定位,是二维表最显著的特征。
我们常见的课程表是一种典型的二维表,作为展示使用是非常适合的,但用作数据源却是不适合的。例如我们行标题是单位的盘点单。
仅需通过单行就能确定数值的,被称为一维表。
数据源使用一维表是符合数据库设计规范的。
仅靠单行就能锁定全部信息的,就是一维表。需要行和列来定位数值的,就是二维表。
当然,一维表、二维表可以相互转换。
一维转二维用透视表,反之用逆透视。
我们把一维表称为源数据,特点是数据丰富详实,适合做流水账,方便存储,有利于做统计分析;二维表称为展示数据,特点是明确直观,适合打印、汇报。
最新的趋势是【多维表】,以后再讲。
数据源
什么是数据源?
数据源是指:用于创建数据透视表的数据源,可以是单元区域、定义的名称、另一个数据透视表数据或其他外部数据来源。我们常用的就是在工作表单元格内的数据组。
数据源的原则:
- 每列数据的第一行包含该列的标题
- 数据源中不能包含空行和空列
- 数据源中不能包含空单元格
- 数据源中不能包含合并单元格
-
超级表
建议插入超级表,优点是可以规范数据,数据源范围可以自动扩展,新增数据后不用每次调整透视范围。
虽然不插入超级表也能创建数据表,但是不能自动扩展核算区域,也不能强制检查区域内的合并单元格,和字段重名。所以希望大家在日常工作中,尽量使用超级表创建数据透视表。字段
数据源中各列的列标题,每个字段代表一类数据。字段可分为:报表筛选字段、行字段、列字段、值字段。
项
四大区域
基础操作
创建数据透视表
创建数据源
选中数据源,创建超级表。
快捷键:Ctrl+A全选数据,Ctrl+T创建超级表。创建数据透视表
点击:插入→数据透视表→选择单元格区域→新工作表(或现有工作表位置)
点击确定后,创建数据透视表成功。
创建数据透视表后,想要查看数据则点击右侧字段列表,先勾选哪个字段,哪个字段排在前面。
想要字段出现在列,则点击字段名,把字段名拖进“数据透视表区域”里的列区域里,字段就在列中展示。字段列表窗格
打开/关闭透视表字段列表窗格
+/-按钮
三种布局形式
压缩布局形式
- 大纲布局形式
- 表格布局形式(常用)
根据展示习惯,表格式布局是我们常用的布局形式。其他两种了解即可。
修改字段名
数据源更新
手动刷新
自动刷新
如何清理垃圾条目?
在日常工作中,当数据源有删改和增减时,筛选时会发现筛选项里有曾经的历史项目。我们称之为垃圾条目。
清理垃圾条目,选中数据透视表 -> 右键 -> 选中数据透视表选项 -> 数据 -> 在每个字段保留的项数里选择“无”即可。
改变数据透视表的外观
修改数据透视表的样式
选中数据透视表 -> 设计 ,红色方框里就是数据透视表的的样式
字段合并居中
设置分类汇总的显示
关闭分类汇总显示
自定义分类汇总
求和、计数、占比
在不改变数据源的情况下,修改数据透视表的数据计算方式
在值区域的任意单元格上,右键,选择“值显示方式”
父级汇总的百分比
设置总计的显示
设置数字格式显示
设置数字格式显示
设置空值和错误值的显示方式
选择数据透视表 -> 右键 -> 数据透视表选项 -> 布局与格式 -> 格式
排序
排序规则
类型:排序规则(升序)
数字:数字按从小到大排序
日期:越早的日期越小
文本:仅包含文字(中文或英文)可按字母或笔画排序;若包含数字、字母、和各种符号,将按以下顺序排列:
空格 0~9 ! “ # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ‘ { | } ~ + < = > A~Z;
逻辑值:false < true
错误值:优先级相同
空单元格:无论是升序还是降序排列,空格单元格总在最后
手动升序:
自动升序:
筛选
通过筛选字段进行筛选
对标签进行筛选
值筛选
日期筛选
根据所选内容进行筛选
刷新后如何保持调整好的列宽?
选中数据透视表 -> 右键 -> 选中数据透视表选项 -> 布局和格式 -> 取消勾选更新时自动调整列宽
进阶操作
切片器
创建切片器
使用切片器
多报表链接
分组
对数值数据分组
对文本数据分组
对日期数据分组
注意事项:日期必须是日期数据类型,否则会出现“选定区域不能分组”!
数据计算
计算字段
计算字段也称为虚拟字段,在数据透视表中创建,不会出现在数据源中。
计算字段只能出现在值区域。
有时候我们需要对汇总完的数据进行计算,但又不想或不便在数据源中增加项目,就可以使用计算字段。
计算字段的创建
修改和删除计算字段
计算项
计算项是临时添加的数据,不会改变数据源。
计算项只能出现在列区域 或 行区域。
计算项不会出现在列表窗格中。
创建计算项
计算求解次序
列出公式
高级操作
数据透视图
数据透视图与普通图表的区别
- 普通图标的数据源就是单元格,数据透视图的数据源即可以是数据工作表的数据、也可以是外部的数据库。
- 普通图表可创建“簇状柱形图”(默认),按照分类来比较值;数据透视图默认创建类型为“堆积柱形图”,主要是用来比较各个值在整个分类总计当中,所占的比例。对于数据透视图而言,它不可以使用X、Y散点图、气泡图、以及股价图。
- 普通图标不具备交互性,属于静态图表;数据透视图具备良好的交互性,是一种动态图表。
普通图标的格式,除非你手动删除掉,否则永久的存在,不会丢失;数据透视图可能刷新之后就会丢失数据标签、趋势线、误差线、以及对数据系列的一些更改。
数据透视图与数据透视表的关系
如果数据透视图是建立在数据透视表的基础上,那么数据透视图的默认样式由数据透视表决定。
数据透视表上的四大区域(筛选区、列区、行区、值区域)与数据透视图一一对应,行区域对应数据透视图的X轴,列区域对应数据透视表的Y轴,值区域对应数据透视图当中的数据系列。
数据透视图的限制
不创建散点图、气泡图、以及股价图。
- 数据透视图当中无法切换数据源的位置。
- 无法调整数据标签的大小。
如果在数据透视图当中,添加了趋势线 ,而这个趋势线所基于的数据透视表当中,添加或删除这个字段的时候,这些趋势线就会消失。
创建数据透视图
根据数据透视表创建数据透视图
- 根据数据源创建数据透视图
-
移动数据透视图
通过复制、粘贴或剪切的方式移动
- 通过鼠标移动
-
使用数据透视图
从不同布局来查看数据透视图(像数据透视表一样操作)
- 显示和关闭数据透视图的字段列表
- 字段按钮的显示与关闭
- 添加趋势线
- 刷新数据透视图
-
编辑透视图
更改数据透视图的类型
- 更改数据透视图的样式
- 设置数据透视图的标题
- 设置数据透视图的图例
- 设置数据透视图的标签
- 设置数据透视图的数据系列
- 设置坐标轴及其标题
- 设置数据透视图的数据表
- 设置数据透视图的误差线
-
美化数据透视图
设置数据透视图的绘图区域和图表
- 设置数据透视图的背景墙和基底
- 设置数据透视图的文字样式
- 使用数据透视图模板
-
将数据透视图转换为静态数据图
将数据透视表转换为静态数据
- 将数据透视图转换为图片
- 直接删除数据透视表
- 断开数据透视图与数据透视表的连接
迷你图
显示报表筛选页
明细数据透视
双击
案例举例
供应商表
往来调拨表
储值完成表
考核表
经理销售表
会计账簿