Excel培训数据透视表技术报告.doc

数据透视表的应用 销售数据的分析 制作数据透视表的基本方法 路径:[数据]-[数据透视表和数据透视图] 合理设置数据透视表的布局 相关概念 -页字段（销售日期） -行字段（销售地点） -列字段（商品名称） -数据字段（销售量、销售收入） 数据透视表的编辑与更新方法 修改字段名称（编辑栏直接删除“求和项：”） 设置字段的数字格式（点任意单元格-右键单击-字段设置-数字-数字格式） 取消数据透视表行总计或列总计（点任意单元格-右键单击-表格选项-格式选项-取消“列总计”、“行总计”） 取消字段的分类汇总（双击字段名称-修改字段名、取消字段的分类汇总） 合并数据标志（数据字段拉至商品名称后并排，点任意单元格-右键单击-表格选项-格式选项-勾选“合并标志”） 设置错误值或空值的显示方式（数据字段拉至商品名称后并排，随机销售数量求方差，点任意单元格-右键单击-表格选项-格式选项-勾选“对于错误值，显示”，“对于空单元格，显示”） 更新数据透视表的数据（数据-刷新数据或工具栏感叹号） 运用数据透视表多角度分析财务数据 修改数据透视表布局 显示明细数据（双击任意数据-生成形成汇总数据的清单） 制作与使用数据透视图 在数据清单基础上制作数据透视图 在数据透视表基础上制作数据透视图 数据透视表的高级应用 通过组合字段创建多维数据汇总表 按不同字段分组查看数据 -按日期分组（点选“销售日期”字段-右键单击-组及显示明细数据-组合-步长（月）） -按地区分组（点选需组合地区名-右键单击-组及显示明细数据-组合） -按商品类别分组（点选需组合商品名-右键单击-组及显示明细数据-组合） 通过设置字段显示方式创建各种比较分析表 差异或差异百分比（点选任意单元格-单击右键-选项-字段选项-名称（销售收入）、数据显示方式（差异、差异百分比）、基本字段（商品名称）、基本项（冰箱）） 占同行或同列百分比（需勾选行汇总、列汇总后再设置） 占总合百分比 累计值显示（按日期累计） 纵向差异比较（再拉入三次销售数据，列字段分2008年和2009年） 当月销售额 当年累计销售额——字段设置-选项-按某一字段汇总-销售日期 环比增长额——字段设置-差异-销售日期-上一个 环比增长率——字段设置-差异百分比-销售日期-上一个 通过自定义设置灵活编制销售数据汇总表 添加自定义计算字段（行字段-商品名称，列字段-销售地点，数据字段-销售收入、销售量；插入销售单价。 点选数据区域任意单元格-工具栏-数据透视表-公式-计算字段-名称（单价）-输入公式“销售收入/销售数量” 添加自定义计算项（点选任意字段名-工具栏-数据透视表-公式-计算项（新地区）） 高效汇总多重区域的销售数据 [例4-2]高效汇总多重区域的销售数据（插入多重合并区域数据透视表，修改字段名称） 无论拟汇总的数据区域结构是否相同均适用 使用函数汇总销售额 [例4-3]按客户汇总销售数据 SUMIF函数-条件求和（组成汇总数据区域-透视客户名称-设置公式SUMIF($B$1:$B$26,$B32,C$1:C$26) 分析图表的制作 组合图表的制作 例[费用预算执行情况分析图] 插入柱形图 清除网格线、背景颜色 选中预算系列—右键图表类型—更改为面积图 选中实际数系列—右键图表类型—更改为数据点折线图 数据系列格式的设置（刻度移动，对齐—偏移量设置） 双坐标轴图表的制作 例[组合销售图表] 插入柱形图 清除网格线、背景颜色 选中累计销售额系列—右键图表类型—更改为面积图 选中环比增长率系列—右键图表类型—更改为数据点折线图 选中环比增长率系列—右键数据系列格式—坐标轴页签—次坐标轴 数据系列格式的设置（次坐标轴改为百分比，移动图例位置并去边框） 双系列重叠图表的制作 例[销售预算完成进度图表] 插入条形图 横坐标轴刻度最大值更改为100% 网格线、背景颜色、边框清除 点选实际数系列—坐标轴—次坐标轴 上坐标轴清除 数据系列格式的设置（实际系列数据标志值） 动态销售图表的制作 例[动态销售图表] 建立绘图数据区域 索引数据函数OFFSET—以指定的引用为参照系，通过给定偏移量返回新的引用 OFFSET(原点单元格绝对引用,控件链接单元格绝对引用,COLUMN(需要得到其列标的单元格) 如：OFFSET($A$1,$A$11,COLUMN(A$1)-1) 插入柱形图 插入分组框（视图-工具-窗体-画分组框-画选项按钮） 链接返回数据单元格（右键选项按钮框-设置控键格式-控制页签-链接单元格） 高效整理数据表 整理银行对账单 数据分列（空格分隔符号、日期列数据格式） 发生额分列（插入列，负号分隔符号） 快速整理从ERP中导入的数据表 B、C列合并单元格取消合并 选择A列-编辑-定位空值-删除整行 发生额分列 其他功能的应用 合并