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

1、经营与财务数据透视分析经营与财务数据透视分析及图表制作及图表制作课程提纲课程提纲1 经营与财务数据透视经营与财务数据透视分析分析2 经营与财务分析图表制作经营与财务分析图表制作1 经营与财务数据透视分析经营与财务数据透视分析1.1 数据透视表基础数据透视表基础1.2 创建和使用基本数据透视表创建和使用基本数据透视表1.3 自定义数据透视表自定义数据透视表1.4 数据透视表中的项目排序和筛选设置数据透视表中的项目排序和筛选设置1.5 数据透视表中的项目分组和计算设置数据透视表中的项目分组和计算设置1.6 更新数据透视表的数据源更新数据透视表的数据源1.7 创建多重合并区域数据透视表创建多重合并区

2、域数据透视表1.1 数据透视表基础数据透视表基础 什么是数据透视表什么是数据透视表 为什么应该使用数据透视表为什么应该使用数据透视表 数据透视表的结构数据透视表的结构 数据透视表的幕后数据透视表的幕后 为数据透视表准备数据为数据透视表准备数据什么是数据透视表什么是数据透视表 数据透视数据透视表是表是一一种种能够对大量数据快速进行汇总的能够对大量数据快速进行汇总的交互式交互式制表工具制表工具。它如同一个对准数据集的万花筒，通过旋转万花筒，它如同一个对准数据集的万花筒，通过旋转万花筒，可以从不同的角度透视查看数据。可以从不同的角度透视查看数据。数据透视表数据透视表的强大之处在于，的强大之处在于，每

3、每一次一次改变改变布局时布局时，系统系统会会立即按照新立即按照新的的布局布局重新重新计算计算数据数据从而得到新从而得到新的汇总表的汇总表。为什么应该使用数据透视表为什么应该使用数据透视表 没有数据透视表的日子没有数据透视表的日子 使用函数制作汇总表使用函数制作汇总表 使用模拟分析工具制作汇总表使用模拟分析工具制作汇总表 若希望改变汇总表的布局，可能一切工作都得若希望改变汇总表的布局，可能一切工作都得从头再来。从头再来。【例例1】使用函数汇总数据使用函数汇总数据何时使用数据透视表何时使用数据透视表 有大量交易数据，难以通过其他方式汇总和分析有大量交易数据，难以通过其他方式汇总和分析 需要找出数据

4、内部的关系并分组需要找出数据内部的关系并分组 需要找出数据中某一字段的一些列特定值需要找出数据中某一字段的一些列特定值 需要利用各种时间周期找出数据的变化趋势需要利用各种时间周期找出数据的变化趋势 预计需要频繁地查询数据并分析其变化情况预计需要频繁地查询数据并分析其变化情况 常常需要创建包含新增部分数据的分类汇总表常常需要创建包含新增部分数据的分类汇总表 需要将数据组织成易于制成图表的格式需要将数据组织成易于制成图表的格式数据透视表的结构数据透视表的结构数据透视表的幕后数据透视表的幕后 开始创建数据透视表时，开始创建数据透视表时，Excel会拍下数据集的快照，会拍下数据集的快照，并把它存放在数

5、据透视表缓存中。并把它存放在数据透视表缓存中。数据透视表数据透视表缓存是一个特殊的存储子系统，可以把缓存是一个特殊的存储子系统，可以把数据源复制到该系统以便快速存取。数据源复制到该系统以便快速存取。从独立的数据源创建的每个数据透视表都会创建它从独立的数据源创建的每个数据透视表都会创建它自己的数据透视表缓存，而这会增加内存的使用量自己的数据透视表缓存，而这会增加内存的使用量以及文件的大小。以及文件的大小。数据透视数据透视表只从缓存中获取其需要的数据，与数据表只从缓存中获取其需要的数据，与数据源之间没有连接。源之间没有连接。为数据透视表准备数据为数据透视表准备数据 有效数据源的特点有效数据源的特点

6、 第第一行是列标题一行是列标题 每一每一列表示唯一类别的数据列表示唯一类别的数据 每一行表示各列中的单独一项每一行表示各列中的单独一项 不存在空行、空列或空单元格不存在空行、空列或空单元格 不存在合并单元格不存在合并单元格 日期日期型数据应该是合法日期型数据应该是合法日期 需进行数学运算的数据不应是文本型数字需进行数学运算的数据不应是文本型数字不规范数据表示例不规范数据表示例规范整理数据规范整理数据【例例2】快速整理数据表快速整理数据表 规范整理日期数据规范整理日期数据 规范整理文本数字规范整理文本数字 数据分列数据分列 快速删除空行快速删除空行 高效填充高效填充数据数据1.2 创建和使用基本

7、数据透视表创建和使用基本数据透视表 创建基本数据透视表创建基本数据透视表 增加数据透视表层次增加数据透视表层次 使用字段列表与经典布局使用字段列表与经典布局 改变报表布局样式改变报表布局样式 显示或取消分类汇总显示或取消分类汇总 显示或取消显示或取消行列总计行列总计创建基本数据透视表创建基本数据透视表【例例3】销售销售数据透视分析数据透视分析 插入插入/表格表格/数据透视数据透视表表 选择数据集选择数据集 选择放置数据透视表的选择放置数据透视表的位置位置 设置表格布局设置表格布局增加数据透视表层次增加数据透视表层次使用字段列表与经典布局使用字段列表与经典布局改变报表布局样式改变报表布局样式 数

8、据透视表工具数据透视表工具/设计设计/布局布局/报表布局报表布局 以压缩形式显示以压缩形式显示 以大纲形式显示以大纲形式显示 以表格形式以表格形式显示显示显示或取消分类汇总显示或取消分类汇总显示或取消行列总计显示或取消行列总计数据透视表的其他常用操作数据透视表的其他常用操作 移动数据透视表移动数据透视表 复制数据复制数据透视透视表表 删除数据删除数据透视透视表表 显示显示/隐藏数据透视表字段列表隐藏数据透视表字段列表 改变字段列表布局改变字段列表布局1.3 自定义数据透视表自定义数据透视表 数据透视表样式设置数据透视表样式设置 数字显示格式设置数字显示格式设置 设置零值与错误值的显示方式设置零

9、值与错误值的显示方式 运用条件格式显示特殊数据运用条件格式显示特殊数据数据透视表样式设置数据透视表样式设置 数据透视表工具数据透视表工具/设计设计/数据透视表样式数据透视表样式 84种样式种样式 浅色浅色 中等深色中等深色 深色深色数字显示格式设置数字显示格式设置 在数据透视表中选择要设置数字格式的字段在数据透视表中选择要设置数字格式的字段 数据透视表工具数据透视表工具/选项选项/活动字段活动字段/字段设置字段设置 在在“值字段设置值字段设置”对话框中单击对话框中单击“数字格式数字格式”按钮按钮 在打开的在打开的“设置单元格格式设置单元格格式”对话框对话框中设置中设置设置零值与错误值的显示方式

10、设置零值与错误值的显示方式 在数据透视表选项对话框中设置在数据透视表选项对话框中设置 将零值显示为空值将零值显示为空值 将错误值显示为将错误值显示为“-”【例例4】零值与错误值的零值与错误值的显示方式设置显示方式设置运用条件格式显示特殊数据运用条件格式显示特殊数据【例例5】利用图标集分级显示销售数据利用图标集分级显示销售数据【例例6】突出显示突出显示动漫动漫拍卖最高和最低价格拍卖最高和最低价格【例例7】突出显示销售量增长最多的销售员突出显示销售量增长最多的销售员1.4 数据透视表中的项目排序和筛选设数据透视表中的项目排序和筛选设置置 在数据透视表中自动排序在数据透视表中自动排序 在数据透视表中

11、自定义排序在数据透视表中自定义排序 在数据透视表中自动筛选在数据透视表中自动筛选 使用切片器进行筛选使用切片器进行筛选在数据透视表中自动排序在数据透视表中自动排序【例例8】工资与销售数据自动排序工资与销售数据自动排序 按行字段排序按行字段排序 按分类汇总值排序按分类汇总值排序 按多个行字段排序按多个行字段排序在数据透视表中自定义排序在数据透视表中自定义排序【例例9】销售数据手动排序销售数据手动排序【例例10】拍品价格局部拍品价格局部排序排序【例例11】不合格不合格产品按行产品按行排序排序【例例12】按职称自定义排序按职称自定义排序在数据透视表中自动筛选在数据透视表中自动筛选【例例13】销售数据

12、自动筛选销售数据自动筛选 筛选高于某个数值的销售额筛选高于某个数值的销售额 筛选高于平均值的销售额筛选高于平均值的销售额 筛选前筛选前n个最大值个最大值 筛选包含特定文本的数据筛选包含特定文本的数据使用切片器进行筛选使用切片器进行筛选【例例14】筛选某业务员的销售业绩筛选某业务员的销售业绩 插入切片器插入切片器 切片切片器字体与样式设置器字体与样式设置 是否显示切片器页眉设置是否显示切片器页眉设置 隐藏与显示切片器设置隐藏与显示切片器设置 删除切片器删除切片器1.5 数据透视表中的项目分组和计算设数据透视表中的项目分组和计算设置置 数据透视表项目分组设置数据透视表项目分组设置 值汇总方式与显示

13、方式设置值汇总方式与显示方式设置 插入计算字段与计算项插入计算字段与计算项数据透视表项目分组设置数据透视表项目分组设置【例例15】数据透视表项目分组数据透视表项目分组 按年、季、月对销售数据分组按年、季、月对销售数据分组 按周对产量数据分组按周对产量数据分组 手动分组与取消组合手动分组与取消组合值汇总方式与显示方式设置值汇总方式与显示方式设置 值汇总方式值汇总方式 求和、计数、平均值、最大值、最小值求和、计数、平均值、最大值、最小值 值显示方式值显示方式 列汇总的百分比、行汇总列汇总的百分比、行汇总的百分比的百分比 总计的百分比、定基百分比总计的百分比、定基百分比 差异、差异百分比差异、差异百

14、分比 按某一字段汇总按某一字段汇总【例例16】值显示方式设置值显示方式设置插入计算字段与计算项插入计算字段与计算项【例例17】计算字段与计算项计算字段与计算项 计算各商品的加权平均单价计算各商品的加权平均单价 计算销售额的年增长率计算销售额的年增长率1.6 更新数据透视表的数据源更新数据透视表的数据源 推迟布局更新推迟布局更新 自动刷新数据自动刷新数据 创建动态数据透视表创建动态数据透视表推迟布局更新推迟布局更新 基于大型数据源建立数据透视表时，每次向数基于大型数据源建立数据透视表时，每次向数据透视表区域添加字段时，据透视表区域添加字段时，Excel都会对数据进都会对数据进行处理，从而需要等待

15、一定的时间。行处理，从而需要等待一定的时间。解决这一问题的方法是：选择数据透视表字段解决这一问题的方法是：选择数据透视表字段列表下边的列表下边的“推迟布局更新推迟布局更新”命令。命令。待设置好数据透视表布局之后，再单击待设置好数据透视表布局之后，再单击“更新更新”命令。命令。自动刷新数据自动刷新数据 数据透视表工具数据透视表工具/选项选项/数据数据/刷新刷新 或者：右键快捷菜单或者：右键快捷菜单/刷新刷新创建动态数据透视表创建动态数据透视表【例例18】基于动态数据源的数据透视表基于动态数据源的数据透视表 利用表功能创建动态数据透视表利用表功能创建动态数据透视表 通过定义名称创建通过定义名称创建

16、动态数据透视动态数据透视表表 OFFSET函数函数 COUNTA函数函数1.7 创建多重合并区域数据透视表创建多重合并区域数据透视表 创建单个页字段的多重区域数据透视表创建单个页字段的多重区域数据透视表 创建多个页字段的多重区域数据透视表创建多个页字段的多重区域数据透视表 创建动态多重合并计算区域数据透视表创建动态多重合并计算区域数据透视表创建单个页字段的多重区域数据透视表创建单个页字段的多重区域数据透视表【例例19】单页字段的数据透视表单页字段的数据透视表 数据透视表和数据透视图命令数据透视表和数据透视图命令 在快速访问工具栏上在快速访问工具栏上添加按钮添加按钮【Alt+D+P】组合键组合键

17、 请指定所需的页字段数目请指定所需的页字段数目 创建单页字段创建单页字段 自定义页字段自定义页字段 修改字段名称修改字段名称创建多个页字段的多重区域数据透视表创建多个页字段的多重区域数据透视表【例例20】2 经营与财务分析图表制作经营与财务分析图表制作2.1 销售额变动趋势分析图销售额变动趋势分析图2.2 员工人数与销售额变动趋势分析图员工人数与销售额变动趋势分析图2.3 各产品季度与年度收入对比分析图各产品季度与年度收入对比分析图2.4 各分公司近各分公司近3年收入对比分析图年收入对比分析图2.5 市场占有率和市场增长率分析图市场占有率和市场增长率分析图2.6 各月工资变动情况分析图各月工资

18、变动情况分析图2.7 两年收入对比分析两年收入对比分析2.8 本期材料收发存情况分析图本期材料收发存情况分析图第二部分第二部分 经营与财务分析图表制作经营与财务分析图表制作2.9 期末材料库存量及预警分析图期末材料库存量及预警分析图2.10 各产品产量预算目标完成情况分析图各产品产量预算目标完成情况分析图2.11 各产品销售预算目标完成情况分析图各产品销售预算目标完成情况分析图2.12 各分公司利润预算目标完成情况分析图各分公司利润预算目标完成情况分析图2.13 项目作业计划进程图项目作业计划进程图2.14 任意两家分公司销售数据对比动态分析图任意两家分公司销售数据对比动态分析图2.15 任选

19、期数现金余额变动情况动态追踪分析图任选期数现金余额变动情况动态追踪分析图2.16 预算目标执行情况监控仪表盘预算目标执行情况监控仪表盘2.1 销售额变动趋势分析图销售额变动趋势分析图【例例21】2.2 员工人数与销售额变动趋势分析图员工人数与销售额变动趋势分析图【例例22】2.3 各产品季度与年度收入对比分析图各产品季度与年度收入对比分析图【例例23】2.4 各分公司近各分公司近3年收入对比分析图年收入对比分析图【例例24】2.5 市场占有率和市场增长率分析图市场占有率和市场增长率分析图【例例25】2.6 各月工资变动情况分析图各月工资变动情况分析图【例例26】2.7 各产品两年收入对比分析图

20、各产品两年收入对比分析图【例例27】2.8 本期材料收发存情况分析图本期材料收发存情况分析图【例例28】2.9 期末材料库存量及预警分析图期末材料库存量及预警分析图【例例29】2.10 各产品产量预算完成情况分析图各产品产量预算完成情况分析图【例例30】2.11 各产品销售预算目标完成情况分析图各产品销售预算目标完成情况分析图【例例31】2.12 各分公司目标利润完成情况分析图各分公司目标利润完成情况分析图【例例32】2.13 项目作业计划进程图项目作业计划进程图【例例33】2.14 任意两家分公司销售数据对比任意两家分公司销售数据对比动态分析图动态分析图【例例34】2.15 任选期数的现金余额变动情况任选期数的现金余额变动情况动态追踪分析图动态追踪分析图【例例35】2.16 收入预算执行情况监控仪表盘收入预算执行情况监控仪表盘【例例36】