Excel数据分析从入门到精通(十五)数据透视表
1.Excel透视表前言
当源数据表格信息较多、比较杂乱、一眼看过去无法获取有用信息时,可以使用数据透视表,甚至可以实现一些BI功能
2.Excel透视表的创建
透视表的核心价值:将繁复的明细数据透视为有价值的分析数据
-左上角的三角形选中全部数据 - 插入 - 数据透视表 - 新工作表,点击确认,即可在一个新的sheet中创建数据透视表
3.Excel透视表的组成
一共有5个部分组成
字段名称
筛选器、列、行、值
行:放需要分组的字段
列:汇总分析的内容
举例:想要汇总按行业呈现的公司数量,将【行业】拖动到【行】里,公司名称拖动到【值】里,即可获得下图
注意:列只能输出一个数字
【列】和【筛选】两个功能不常用,【行】和【值】用得比较多
4.Excel透视表的十大技巧
①如何创建汇总行
假设有公司存活信息,记录了公司关闭时间,想看哪个城市倒闭的公司更多
- 全选数据 - 新建数据透视表 - 【值】选择公司名称,【行】选择地点,即可得到每个地点的公司情况数据
可以用SQL语句来理解
`select count(公司名称) from 公司数据 group by 地点;#公司名称为字符形式时,求和相当于count函数
select sum(销售目标)from 业绩 group by 月份;#此时的销售目标是数字型
②如何展示百分比
- 在汇总列的任何一个单元格点击右键 - 值显示方式 - 列汇总百分比或其他百分比显示方式,即可显示每个数字占【总计】的百分比情况
③多级标签
- 展示每个地方的行业分布情况,可以将【行业】再拖到【行】中,即可显示行业
- 排序有先后,【行】中在上面的标签等级更高
- 可以放很多行,可以定义多级标签
④自定义分组
- 【行】中放存活月数,【值】放公司名称,统计每个月数的公司存活数量
- 存活月数如果是具体数字的话,值有些多,所以需要将月数改为区间,点击行标签右键(等光标变为向下的实心黑色箭头时) - 组合,会自动选择最小值、最大值和步长,然后点击确定,即可实现区间段效果
⑤日期自定义分组
- 行标签为日期时,也可以进行组合功能,将【成立时间】拖到【行】,【公司名称】拖到【值】
- 随意点一个日期,右键取消组合
- 再右键选择组合,会自动显示最大最小值和步长,即可自由选择(可以多选)
⑥分组排列
若有多级标签,会是一级一级的树状展示,无法直接看到想看到数据
任意选择一个单元格 - 右键 - 数据透视表选项 - 布局与格式选项卡 - 勾选【合并且居中排列带标签的单元格】 - 显示选项卡勾选【经典数据透视表布局】
⑦展示万元的金额数据
选择需要更改的数据 - 右键 - 设置单元格格式 - 数字 - 自定义 - 在【类型】种输入0!.0,万,即可显示以万元为单位
⑧切片联动
多张数据透视表需要进行联动(调整一个数据参数,其余数据参数也会随之变化)
一个sheet中建立两个数据透视表(回到源数据表格中在进行创建即可)第一个是以行业划分的人的公司数量,第二个是以地区划分的公司数量(需要来自同一个数据源)
- 分析 - 插入切片器 - 选择需要切片的字段(如行业)
- 在切片机点击右键 - 报表连接 - 选择当前sheet6的数据源,再进行选择,两个数据表就可以进行联动了,效果如下
同理,也可以按照地点来进行切片联动,效果如下图
⑨日期日程表
若源数据表格字段中有和日期时间相关的,则可以使用日期日程表
-
创建以成立时间为行,以公司名称为值的数据透视表 - 右键取消组合
-
点击【分析】 - 插入日程表,选择【成立时间】,即可进行联动
原效果:
⑩透视表在不同文件间同步
当明细数据和数据透视表不在同一个文件中时进行联动
- 在一个表中创建出透视表,进行复制,然后直接粘贴到新的表文件中,即可实现联动,点击刷新就可以更新数据