经营与财务数据透视分析及图表制作
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
• 【例34】
2.15 任选期数的现金余额变动情况 动态追踪分析图
• 【例35】
2.16 收入预算执行情况监控仪表盘
• 【例36】
经营与财务数据透视分析 及图表制作
课程提纲
1 经营与财务数据透视分析
2 经营与财务分析图表制作
1 经营与财务数据透视分析
1.1 数据透视表基础 1.2 创建和使用基本数据透视表
1.3 自定义数据透视表
1.4 数据透视表中的项目排序和筛选设置
1.5 数据透视表中的项目分组和计算设置
1.6 更新数据透视表的数据源
2.9 期末材料库存量及预警分析图
• 【例29】
2.10 各产品产量预算完成情况分析图
• 【例30】
2.11 各产品销售预算目标完成情况分析图
• 【例31】
2.12 各分公司目标利润完成情况分析图
• 【例32】
2.13 项目作业计划进程图
• 【例33】
2.14 任意两家分公司销售数据对比 动态分析图
– 在快速访问工具栏上添加按钮
– 【Alt+D+P】组合键
• 请指定所需的页字段数目
– 创建单页字段
– 自定义页字段
• ቤተ መጻሕፍቲ ባይዱ改字段名称
创建多个页字段的多重区域数据透视表
• 【例20】
2 经营与财务分析图表制作
2.1 销售额变动趋势分析图 2.2 员工人数与销售额变动趋势分析图
2.3 各产品季度与年度收入对比分析图
【例15】数据透视表项目分组
• 按年、季、月对销售数据分组
• 按周对产量数据分组
• 手动分组与取消组合
值汇总方式与显示方式设置
• 值汇总方式
– 求和、计数、平均值、最大值、最小值……
• 值显示方式
– 列汇总的百分比、行汇总的百分比 – 总计的百分比、定基百分比 – 差异、差异百分比 – 按某一字段汇总
【例6】突出显示动漫拍卖最高和最低价格
【例7】突出显示销售量增长最多的销售员
1.4 数据透视表中的项目排序和筛选设 置
• 在数据透视表中自动排序 • 在数据透视表中自定义排序
• 在数据透视表中自动筛选
• 使用切片器进行筛选
在数据透视表中自动排序
【例8】工资与销售数据自动排序
• 按行字段排序
• 按分类汇总值排序
1.7 创建多重合并区域数据透视表
1.1 数据透视表基础
• 什么是数据透视表
• 为什么应该使用数据透视表
• 数据透视表的结构
• 数据透视表的幕后 • 为数据透视表准备数据
什么是数据透视表
• 数据透视表是一种能够对大量数据快速进行汇总的 交互式制表工具。 • 它如同一个对准数据集的万花筒，通过旋转万花筒， 可以从不同的角度透视查看数据。 • 数据透视表的强大之处在于，每一次改变布局时， 系统会立即按照新的布局重新计算数据从而得到新 的汇总表。
1.2 创建和使用基本数据透视表
• 创建基本数据透视表
• 增加数据透视表层次
• 使用字段列表与经典布局
• 改变报表布局样式
• 显示或取消分类汇总
• 显示或取消行列总计
创建基本数据透视表
【例3】销售数据透视分析 • 插入/表格/数据透视表 • 选择数据集 • 选择放置数据透视表的位置 • 设置表格布局
• 数据透视表工具/选项/活动字段/字段设置
• 在“值字段设置”对话框中单击“数字格式”按钮
• 在打开的“设置单元格格式”对话框中设置
设置零值与错误值的显示方式
• 在数据透视表选项对话框中设置
– 将零值显示为空值
– 将错误值显示为“-”
【例4】零值与错误值的显示方式设置
运用条件格式显示特殊数据
【例5】利用图标集分级显示销售数据
• 通过定义名称创建动态数据透视表
– OFFSET函数 – COUNTA函数
1.7 创建多重合并区域数据透视表
• 创建单个页字段的多重区域数据透视表
• 创建多个页字段的多重区域数据透视表
• 创建动态多重合并计算区域数据透视表
创建单个页字段的多重区域数据透视表
【例19】单页字段的数据透视表 • 数据透视表和数据透视图命令
增加数据透视表层次
使用字段列表与经典布局
改变报表布局样式
• 数据透视表工具/设计/布局/报表布局
– 以压缩形式显示
– 以大纲形式显示
– 以表格形式显示
显示或取消分类汇总
显示或取消行列总计
数据透视表的其他常用操作
• 移动数据透视表
• 复制数据透视表
• 删除数据透视表
• 显示/隐藏数据透视表字段列表
为什么应该使用数据透视表
• 没有数据透视表的日子
– 使用函数制作汇总表 – 使用模拟分析工具制作汇总表 – 若希望改变汇总表的布局，可能一切工作都得 从头再来。
【例1】使用函数汇总数据
何时使用数据透视表
• 有大量交易数据，难以通过其他方式汇总和分析
• 需要找出数据内部的关系并分组 • 需要找出数据中某一字段的一些列特定值 • 需要利用各种时间周期找出数据的变化趋势 • 预计需要频繁地查询数据并分析其变化情况
【例16】值显示方式设置
插入计算字段与计算项
【例17】计算字段与计算项
• 计算各商品的加权平均单价
• 计算销售额的年增长率
1.6 更新数据透视表的数据源
• 推迟布局更新
• 自动刷新数据
• 创建动态数据透视表
推迟布局更新
• 基于大型数据源建立数据透视表时，每次向数 据透视表区域添加字段时，Excel都会对数据进
• 按多个行字段排序
在数据透视表中自定义排序
【例9】销售数据手动排序
【例10】拍品价格局部排序
【例11】不合格产品按行排序
【例12】按职称自定义排序
在数据透视表中自动筛选
【例13】销售数据自动筛选
• 筛选高于某个数值的销售额
• 筛选高于平均值的销售额
• 筛选前n个最大值 • 筛选包含特定文本的数据
• 改变字段列表布局
1.3 自定义数据透视表
• 数据透视表样式设置
• 数字显示格式设置
• 设置零值与错误值的显示方式
• 运用条件格式显示特殊数据
数据透视表样式设置
• 数据透视表工具/设计/数据透视表样式
• 84种样式
– 浅色 – 中等深色 – 深色
数字显示格式设置
• 在数据透视表中选择要设置数字格式的字段
• 常常需要创建包含新增部分数据的分类汇总表
• 需要将数据组织成易于制成图表的格式
数据透视表的结构
数据透视表的幕后
• 开始创建数据透视表时，Excel会拍下数据集的快照， 并把它存放在数据透视表缓存中。 • 数据透视表缓存是一个特殊的存储子系统，可以把 数据源复制到该系统以便快速存取。 • 从独立的数据源创建的每个数据透视表都会创建它 自己的数据透视表缓存，而这会增加内存的使用量 以及文件的大小。 • 数据透视表只从缓存中获取其需要的数据，与数据 源之间没有连接。
2.12 各分公司利润预算目标完成情况分析图 2.13 项目作业计划进程图 2.14 任意两家分公司销售数据对比动态分析图 2.15 任选期数现金余额变动情况动态追踪分析图
2.16 预算目标执行情况监控仪表盘
2.1 销售额变动趋势分析图
• 【例21】
2.2 员工人数与销售额变动趋势分析图
• 【例22】
2.4 各分公司近3年收入对比分析图 2.5 市场占有率和市场增长率分析图 2.6 各月工资变动情况分析图 2.7 两年收入对比分析
2.8 本期材料收发存情况分析图
第二部分 经营与财务分析图表制作
2.9 期末材料库存量及预警分析图 2.10 各产品产量预算目标完成情况分析图
2.11 各产品销售预算目标完成情况分析图
使用切片器进行筛选
【例14】筛选某业务员的销售业绩
• 插入切片器
• 切片器字体与样式设置
• 是否显示切片器页眉设置
• 隐藏与显示切片器设置
• 删除切片器
1.5 数据透视表中的项目分组和计算设 置
• 数据透视表项目分组设置
• 值汇总方式与显示方式设置
• 插入计算字段与计算项
数据透视表项目分组设置
行处理，从而需要等待一定的时间。
• 解决这一问题的方法是：选择数据透视表字段
列表下边的“推迟布局更新”命令。
• 待设置好数据透视表布局之后，再单击“更新”
命令。
自动刷新数据
• 数据透视表工具/选项/数据/刷新
• 或者：右键快捷菜单/刷新
创建动态数据透视表
【例18】基于动态数据源的数据透视表
• 利用表功能创建动态数据透视表
为数据透视表准备数据
• 有效数据源的特点
– 第一行是列标题 – 每一列表示唯一类别的数据 – 每一行表示各列中的单独一项 – 不存在空行、空列或空单元格 – 不存在合并单元格 – 日期型数据应该是合法日期 – 需进行数学运算的数据不应是文本型数字
不规范数据表示例
规范整理数据
【例2】快速整理数据表 • 规范整理日期数据 • 规范整理文本数字 • 数据分列 • 快速删除空行 • 高效填充数据
2.3 各产品季度与年度收入对比分析图
• 【例23】
2.4 各分公司近3年收入对比分析图
• 【例24】
2.5 市场占有率和市场增长率分析图
• 【例25】
2.6 各月工资变动情况分析图
• 【例26】
2.7 各产品两年收入对比分析图
• 【例27】
2.8 本期材料收发存情况分析图
• 【例28】
2.15 任选期数的现金余额变动情况 动态追踪分析图
• 【例35】
2.16 收入预算执行情况监控仪表盘
• 【例36】
经营与财务数据透视分析 及图表制作
课程提纲
1 经营与财务数据透视分析
2 经营与财务分析图表制作
1 经营与财务数据透视分析
1.1 数据透视表基础 1.2 创建和使用基本数据透视表
1.3 自定义数据透视表
1.4 数据透视表中的项目排序和筛选设置
1.5 数据透视表中的项目分组和计算设置
1.6 更新数据透视表的数据源
2.9 期末材料库存量及预警分析图
• 【例29】
2.10 各产品产量预算完成情况分析图
• 【例30】
2.11 各产品销售预算目标完成情况分析图
• 【例31】
2.12 各分公司目标利润完成情况分析图
• 【例32】
2.13 项目作业计划进程图
• 【例33】
2.14 任意两家分公司销售数据对比 动态分析图
– 在快速访问工具栏上添加按钮
– 【Alt+D+P】组合键
• 请指定所需的页字段数目
– 创建单页字段
– 自定义页字段
• ቤተ መጻሕፍቲ ባይዱ改字段名称
创建多个页字段的多重区域数据透视表
• 【例20】
2 经营与财务分析图表制作
2.1 销售额变动趋势分析图 2.2 员工人数与销售额变动趋势分析图
2.3 各产品季度与年度收入对比分析图
【例15】数据透视表项目分组
• 按年、季、月对销售数据分组
• 按周对产量数据分组
• 手动分组与取消组合
值汇总方式与显示方式设置
• 值汇总方式
– 求和、计数、平均值、最大值、最小值……
• 值显示方式
– 列汇总的百分比、行汇总的百分比 – 总计的百分比、定基百分比 – 差异、差异百分比 – 按某一字段汇总
【例6】突出显示动漫拍卖最高和最低价格
【例7】突出显示销售量增长最多的销售员
1.4 数据透视表中的项目排序和筛选设 置
• 在数据透视表中自动排序 • 在数据透视表中自定义排序
• 在数据透视表中自动筛选
• 使用切片器进行筛选
在数据透视表中自动排序
【例8】工资与销售数据自动排序
• 按行字段排序
• 按分类汇总值排序
1.7 创建多重合并区域数据透视表
1.1 数据透视表基础
• 什么是数据透视表
• 为什么应该使用数据透视表
• 数据透视表的结构
• 数据透视表的幕后 • 为数据透视表准备数据
什么是数据透视表
• 数据透视表是一种能够对大量数据快速进行汇总的 交互式制表工具。 • 它如同一个对准数据集的万花筒，通过旋转万花筒， 可以从不同的角度透视查看数据。 • 数据透视表的强大之处在于，每一次改变布局时， 系统会立即按照新的布局重新计算数据从而得到新 的汇总表。
1.2 创建和使用基本数据透视表
• 创建基本数据透视表
• 增加数据透视表层次
• 使用字段列表与经典布局
• 改变报表布局样式
• 显示或取消分类汇总
• 显示或取消行列总计
创建基本数据透视表
【例3】销售数据透视分析 • 插入/表格/数据透视表 • 选择数据集 • 选择放置数据透视表的位置 • 设置表格布局
• 数据透视表工具/选项/活动字段/字段设置
• 在“值字段设置”对话框中单击“数字格式”按钮
• 在打开的“设置单元格格式”对话框中设置
设置零值与错误值的显示方式
• 在数据透视表选项对话框中设置
– 将零值显示为空值
– 将错误值显示为“-”
【例4】零值与错误值的显示方式设置
运用条件格式显示特殊数据
【例5】利用图标集分级显示销售数据
• 通过定义名称创建动态数据透视表
– OFFSET函数 – COUNTA函数
1.7 创建多重合并区域数据透视表
• 创建单个页字段的多重区域数据透视表
• 创建多个页字段的多重区域数据透视表
• 创建动态多重合并计算区域数据透视表
创建单个页字段的多重区域数据透视表
【例19】单页字段的数据透视表 • 数据透视表和数据透视图命令
增加数据透视表层次
使用字段列表与经典布局
改变报表布局样式
• 数据透视表工具/设计/布局/报表布局
– 以压缩形式显示
– 以大纲形式显示
– 以表格形式显示
显示或取消分类汇总
显示或取消行列总计
数据透视表的其他常用操作
• 移动数据透视表
• 复制数据透视表
• 删除数据透视表
• 显示/隐藏数据透视表字段列表
为什么应该使用数据透视表
• 没有数据透视表的日子
– 使用函数制作汇总表 – 使用模拟分析工具制作汇总表 – 若希望改变汇总表的布局，可能一切工作都得 从头再来。
【例1】使用函数汇总数据
何时使用数据透视表
• 有大量交易数据，难以通过其他方式汇总和分析
• 需要找出数据内部的关系并分组 • 需要找出数据中某一字段的一些列特定值 • 需要利用各种时间周期找出数据的变化趋势 • 预计需要频繁地查询数据并分析其变化情况
【例16】值显示方式设置
插入计算字段与计算项
【例17】计算字段与计算项
• 计算各商品的加权平均单价
• 计算销售额的年增长率
1.6 更新数据透视表的数据源
• 推迟布局更新
• 自动刷新数据
• 创建动态数据透视表
推迟布局更新
• 基于大型数据源建立数据透视表时，每次向数 据透视表区域添加字段时，Excel都会对数据进
• 按多个行字段排序
在数据透视表中自定义排序
【例9】销售数据手动排序
【例10】拍品价格局部排序
【例11】不合格产品按行排序
【例12】按职称自定义排序
在数据透视表中自动筛选
【例13】销售数据自动筛选
• 筛选高于某个数值的销售额
• 筛选高于平均值的销售额
• 筛选前n个最大值 • 筛选包含特定文本的数据
• 改变字段列表布局
1.3 自定义数据透视表
• 数据透视表样式设置
• 数字显示格式设置
• 设置零值与错误值的显示方式
• 运用条件格式显示特殊数据
数据透视表样式设置
• 数据透视表工具/设计/数据透视表样式
• 84种样式
– 浅色 – 中等深色 – 深色
数字显示格式设置
• 在数据透视表中选择要设置数字格式的字段
• 常常需要创建包含新增部分数据的分类汇总表
• 需要将数据组织成易于制成图表的格式
数据透视表的结构
数据透视表的幕后
• 开始创建数据透视表时，Excel会拍下数据集的快照， 并把它存放在数据透视表缓存中。 • 数据透视表缓存是一个特殊的存储子系统，可以把 数据源复制到该系统以便快速存取。 • 从独立的数据源创建的每个数据透视表都会创建它 自己的数据透视表缓存，而这会增加内存的使用量 以及文件的大小。 • 数据透视表只从缓存中获取其需要的数据，与数据 源之间没有连接。
2.12 各分公司利润预算目标完成情况分析图 2.13 项目作业计划进程图 2.14 任意两家分公司销售数据对比动态分析图 2.15 任选期数现金余额变动情况动态追踪分析图
2.16 预算目标执行情况监控仪表盘
2.1 销售额变动趋势分析图
• 【例21】
2.2 员工人数与销售额变动趋势分析图
• 【例22】
2.4 各分公司近3年收入对比分析图 2.5 市场占有率和市场增长率分析图 2.6 各月工资变动情况分析图 2.7 两年收入对比分析
2.8 本期材料收发存情况分析图
第二部分 经营与财务分析图表制作
2.9 期末材料库存量及预警分析图 2.10 各产品产量预算目标完成情况分析图
2.11 各产品销售预算目标完成情况分析图
使用切片器进行筛选
【例14】筛选某业务员的销售业绩
• 插入切片器
• 切片器字体与样式设置
• 是否显示切片器页眉设置
• 隐藏与显示切片器设置
• 删除切片器
1.5 数据透视表中的项目分组和计算设 置
• 数据透视表项目分组设置
• 值汇总方式与显示方式设置
• 插入计算字段与计算项
数据透视表项目分组设置
行处理，从而需要等待一定的时间。
• 解决这一问题的方法是：选择数据透视表字段
列表下边的“推迟布局更新”命令。
• 待设置好数据透视表布局之后，再单击“更新”
命令。
自动刷新数据
• 数据透视表工具/选项/数据/刷新
• 或者：右键快捷菜单/刷新
创建动态数据透视表
【例18】基于动态数据源的数据透视表
• 利用表功能创建动态数据透视表
为数据透视表准备数据
• 有效数据源的特点
– 第一行是列标题 – 每一列表示唯一类别的数据 – 每一行表示各列中的单独一项 – 不存在空行、空列或空单元格 – 不存在合并单元格 – 日期型数据应该是合法日期 – 需进行数学运算的数据不应是文本型数字
不规范数据表示例
规范整理数据
【例2】快速整理数据表 • 规范整理日期数据 • 规范整理文本数字 • 数据分列 • 快速删除空行 • 高效填充数据
2.3 各产品季度与年度收入对比分析图
• 【例23】
2.4 各分公司近3年收入对比分析图
• 【例24】
2.5 市场占有率和市场增长率分析图
• 【例25】
2.6 各月工资变动情况分析图
• 【例26】
2.7 各产品两年收入对比分析图
• 【例27】
2.8 本期材料收发存情况分析图
• 【例28】