经营与财务数据透视分析及图表制作

使用切片器进行筛选
【例14】筛选某业务员的销售业绩 • 插入切片器 • 切片器字体与样式设置 • 是否显示切片器页眉设置 • 隐藏与显示切片器设置 • 删除切片器
1.5 数据透视表中的项目分组和计ቤተ መጻሕፍቲ ባይዱ设 置
• 数据透视表项目分组设置 • 值汇总方式与显示方式设置 • 插入计算字段与计算项
数据透视表项目分组设置
– OFFSET函数 – COUNTA函数
1.7 创建多重合并区域数据透视表
• 创建单个页字段的多重区域数据透视表 • 创建多个页字段的多重区域数据透视表 • 创建动态多重合并计算区域数据透视表
创建单个页字段的多重区域数据透视表
【例19】单页字段的数据透视表 • 数据透视表和数据透视图命令
– 在快速访问工具栏上添加按钮 – 【Alt+D+P】组合键
•
人生得意须尽欢，莫使金樽空对月。0 8:39:30 08:39:3 008:39 1/9/202 1 8:39:30 AM
•
做一枚螺丝钉，那里需要那里上。21. 1.908:3 9:3008: 39Jan-2 19-Jan-21
•
日复一日的努力只为成就美好的明天 。08:39: 3008:3 9:3008: 39Satur day, January 09, 2021
2.13 项目作业计划进程图
• 【例33】
2.14 任意两家分公司销售数据对比 动态分析图
• 【例34】
2.15 任选期数的现金余额变动情况 动态追踪分析图
• 【例35】
2.16 收入预算执行情况监控仪表盘
• 【例36】
•
生活中的辛苦阻挠不了我对生活的热 爱。21. 1.921.1. 9Satur day, January 09, 2021
• 在数据透视表选项对话框中设置
– 将零值显示为空值 – 将错误值显示为“-”
【例4】零值与错误值的显示方式设置
运用条件格式显示特殊数据
【例5】利用图标集分级显示销售数据 【例6】突出显示动漫拍卖最高和最低价格 【例7】突出显示销售量增长最多的销售员
1.4 数据透视表中的项目排序和筛选设 置
• 在数据透视表中自动排序 • 在数据透视表中自定义排序 • 在数据透视表中自动筛选 • 使用切片器进行筛选
【例15】数据透视表项目分组 • 按年、季、月对销售数据分组 • 按周对产量数据分组 • 手动分组与取消组合
值汇总方式与显示方式设置
• 值汇总方式
– 求和、计数、平均值、最大值、最小值……
• 值显示方式
– 列汇总的百分比、行汇总的百分比 – 总计的百分比、定基百分比 – 差异、差异百分比 – 按某一字段汇总
不规范数据表示例
规范整理数据
【例2】快速整理数据表 • 规范整理日期数据 • 规范整理文本数字 • 数据分列 • 快速删除空行 • 高效填充数据
1.2 创建和使用基本数据透视表
• 创建基本数据透视表 • 增加数据透视表层次 • 使用字段列表与经典布局 • 改变报表布局样式 • 显示或取消分类汇总 • 显示或取消行列总计
• 请指定所需的页字段数目
– 创建单页字段 – 自定义页字段
• 修改字段名称
创建多个页字段的多重区域数据透视表
• 【例20】
2 经营与财务分析图表制作
2.1 销售额变动趋势分析图 2.2 员工人数与销售额变动趋势分析图 2.3 各产品季度与年度收入对比分析图 2.4 各分公司近3年收入对比分析图 2.5 市场占有率和市场增长率分析图 2.6 各月工资变动情况分析图 2.7 两年收入对比分析 2.8 本期材料收发存情况分析图
数据透视表的幕后
• 开始创建数据透视表时，Excel会拍下数据集的快照， 并把它存放在数据透视表缓存中。
• 数据透视表缓存是一个特殊的存储子系统，可以把 数据源复制到该系统以便快速存取。
• 从独立的数据源创建的每个数据透视表都会创建它 自己的数据透视表缓存，而这会增加内存的使用量 以及文件的大小。
在数据透视表中自动排序
【例8】工资与销售数据自动排序 • 按行字段排序 • 按分类汇总值排序 • 按多个行字段排序
在数据透视表中自定义排序
【例9】销售数据手动排序 【例10】拍品价格局部排序 【例11】不合格产品按行排序 【例12】按职称自定义排序
在数据透视表中自动筛选
【例13】销售数据自动筛选 • 筛选高于某个数值的销售额 • 筛选高于平均值的销售额 • 筛选前n个最大值 • 筛选包含特定文本的数据
2.1 销售额变动趋势分析图
• 【例21】
2.2 员工人数与销售额变动趋势分析图
• 【例22】
2.3 各产品季度与年度收入对比分析图
• 【例23】
2.4 各分公司近3年收入对比分析图
• 【例24】
2.5 市场占有率和市场增长率分析图
• 【例25】
2.6 各月工资变动情况分析图
• 【例26】
1.1 数据透视表基础
• 什么是数据透视表 • 为什么应该使用数据透视表 • 数据透视表的结构 • 数据透视表的幕后 • 为数据透视表准备数据
什么是数据透视表
• 数据透视表是一种能够对大量数据快速进行汇总的 交互式制表工具。
• 它如同一个对准数据集的万花筒，通过旋转万花筒， 可以从不同的角度透视查看数据。
• 解决这一问题的方法是：选择数据透视表字段 列表下边的“推迟布局更新”命令。
• 待设置好数据透视表布局之后，再单击“更新” 命令。
自动刷新数据
• 数据透视表工具/选项/数据/刷新 • 或者：右键快捷菜单/刷新
创建动态数据透视表
【例18】基于动态数据源的数据透视表 • 利用表功能创建动态数据透视表 • 通过定义名称创建动态数据透视表
创建基本数据透视表
【例3】销售数据透视分析 • 插入/表格/数据透视表 • 选择数据集 • 选择放置数据透视表的位置 • 设置表格布局
增加数据透视表层次
使用字段列表与经典布局
改变报表布局样式
• 数据透视表工具/设计/布局/报表布局
– 以压缩形式显示 – 以大纲形式显示 – 以表格形式显示
显示或取消分类汇总
• 有大量交易数据，难以通过其他方式汇总和分析 • 需要找出数据内部的关系并分组 • 需要找出数据中某一字段的一些列特定值 • 需要利用各种时间周期找出数据的变化趋势 • 预计需要频繁地查询数据并分析其变化情况 • 常常需要创建包含新增部分数据的分类汇总表 • 需要将数据组织成易于制成图表的格式
数据透视表的结构
谢谢大家！
【例16】值显示方式设置
插入计算字段与计算项
【例17】计算字段与计算项 • 计算各商品的加权平均单价 • 计算销售额的年增长率
1.6 更新数据透视表的数据源
• 推迟布局更新 • 自动刷新数据 • 创建动态数据透视表
推迟布局更新
• 基于大型数据源建立数据透视表时，每次向数 据透视表区域添加字段时，Excel都会对数据进 行处理，从而需要等待一定的时间。
2.7 各产品两年收入对比分析图
• 【例27】
2.8 本期材料收发存情况分析图
• 【例28】
2.9 期末材料库存量及预警分析图
• 【例29】
2.10 各产品产量预算完成情况分析图
• 【例30】
2.11 各产品销售预算目标完成情况分析图 • 【例31】
2.12 各分公司目标利润完成情况分析图 • 【例32】
•
每天都是美好的一天，新的一天开启 。21.1.9 21.1.90 8:3908: 39:300 8:39:30 Jan-21
•
相信命运，让自己成长，慢慢的长大 。2021 年1月9 日星期 六8时39 分30秒 Saturday, January 09, 2021
•
爱情，亲情，友情，让人无法割舍。2 1.1.920 21年1 月9日星 期六8 时39分3 0秒21. 1.9
• 数据透视表的强大之处在于，每一次改变布局时， 系统会立即按照新的布局重新计算数据从而得到新 的汇总表。
为什么应该使用数据透视表
• 没有数据透视表的日子
– 使用函数制作汇总表 – 使用模拟分析工具制作汇总表 – 若希望改变汇总表的布局，可能一切工作都得
从头再来。
【例1】使用函数汇总数据
何时使用数据透视表
•
让自己更加强大，更加专业，这才能 让自己 更好。2 021年1 月上午 8时39 分21.1.9 08:39J anuary 9, 2021
•
这些年的努力就为了得到相应的回报 。2021 年1月9 日星期 六8时39 分30秒 08:39:3 09 January 2021
•
科学，你是国力的灵魂；同时又是社 会发展 的标志 。上午8 时39分 30秒上 午8时3 9分08: 39:3021 .1.9
• 数据透视表工具/设计/数据透视表样式 • 84种样式
– 浅色 – 中等深色 – 深色
数字显示格式设置
• 在数据透视表中选择要设置数字格式的字段 • 数据透视表工具/选项/活动字段/字段设置 • 在“值字段设置”对话框中单击“数字格式”按钮 • 在打开的“设置单元格格式”对话框中设置
设置零值与错误值的显示方式
•
安全放在第一位，防微杜渐。21.1.921 .1.908: 39:3008 :39:30J anuary 9, 2021
•
加强自身建设，增强个人的休养。202 1年1月 9日上 午8时39 分21.1. 921.1.9
•
精益求精，追求卓越，因为相信而伟 大。202 1年1月 9日星 期六上 午8时39 分30秒 08:39:3 021.1.9
• 数据透视表只从缓存中获取其需要的数据，与数据 源之间没有连接。
为数据透视表准备数据
• 有效数据源的特点
– 第一行是列标题 – 每一列表示唯一类别的数据 – 每一行表示各列中的单独一项 – 不存在空行、空列或空单元格 – 不存在合并单元格 – 日期型数据应该是合法日期 – 需进行数学运算的数据不应是文本型数字
经营与财务数据透视分析 及图表制作
课程提纲
1 经营与财务数据透视分析 2 经营与财务分析图表制作
1 经营与财务数据透视分析
1.1 数据透视表基础 1.2 创建和使用基本数据透视表 1.3 自定义数据透视表 1.4 数据透视表中的项目排序和筛选设置 1.5 数据透视表中的项目分组和计算设置 1.6 更新数据透视表的数据源 1.7 创建多重合并区域数据透视表
显示或取消行列总计
数据透视表的其他常用操作
• 移动数据透视表 • 复制数据透视表 • 删除数据透视表 • 显示/隐藏数据透视表字段列表 • 改变字段列表布局
1.3 自定义数据透视表
• 数据透视表样式设置 • 数字显示格式设置 • 设置零值与错误值的显示方式 • 运用条件格式显示特殊数据
数据透视表样式设置
第二部分 经营与财务分析图表制作
2.9 期末材料库存量及预警分析图 2.10 各产品产量预算目标完成情况分析图 2.11 各产品销售预算目标完成情况分析图 2.12 各分公司利润预算目标完成情况分析图 2.13 项目作业计划进程图 2.14 任意两家分公司销售数据对比动态分析图 2.15 任选期数现金余额变动情况动态追踪分析图 2.16 预算目标执行情况监控仪表盘