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

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