Excel数据深度挖掘和分析课程.pptVIP

Excel数据深度挖掘和分析 培训师：张剑悦 打印版 讲义说明：本讲义是课程知识点，并不是讲课顺序。本次培训的目的是为了配合实际工作中的应用，为了提高课程针对性，因此课程中会根据大家的实际需求随时添加知识点，或删除一些暂时用不到的内容，请大家理解。 数据量不大的数据信息汇总 =SUMIF($A$4:$A$15,F4,$D$4:$D$15) 相对地址和绝对地址引用举例 公式 =C5 / SUM ( $C$4 : $C$7) 中C5使用了相对地址，所以在计算其他部门时，可自动进行调整； 而求和区域使用了绝对地址“$C$4 : $C$7”，所以在计算时无论哪个部门都会与相同的区域和进行相除。 为数据分析做准备 字段表和二维表 什么是细分管理 合并的单元格要不要？ 怎么调整不合理的数据源？ 管理数据的方法 1．用什么样的表来管理数据 2．用字段表如何管理数据 用正确的方式来管理数据 1．字段表的制作2．字段表的作用 把不规范的表调整为规范的表 1 2 Ctrl + Enter 3 公式和函数应用技巧 在公式中尽量使用引用而少输入数值 可用鼠标点击单元格来输入引用，而不必手工输入 适当使用括号或空格以易于阅读和理解 当公式较长时，可用 Alt+Enter 强行换行，以方便阅读 按F2 或双击单元格可进入公式编辑状态 可按F9 进行公式的重新计算 既可以复制公式，也可以复制公式的计算值 公式错误值的理解 利用函数分析数据逻辑（一） 挖掘数据逻辑 挖掘数据重复频度 频率区间统计 匹配分析 挖掘数据重复频度 =COUNTIF($B$2:B2,B2) 计算一个阶段某个信息重复出现了几次 频率区间统计 理解什么是数组公式 { } 的作用 匹配分析 =VLOOKUP(H5,$D$16:$E$18,2,1) 利用函数分析数据逻辑（二） 数组公式 名称和偏移量 循环引用 Excel中的汇总函数和数组函数 总价汇总 Offset函数的作用 Offset的作用是计算“基准单元格”或“区域”在偏移后的选择区域。 动态计算结果 动态设置有效性 动态实现图表绘制功能 Offset函数及其参数 =Offset（基准单元格，偏移的行，偏移的列，选择的行，选择的列） =Offset（A1，1，2，3，4） 相当于以A1单元格为基准，向下偏移1行，在向右偏移2列，然后选择3行，再选择4列，相当于选中了$C$2:$F$4的单元格区域。 =SUM(OFFSET(A1:C1,2,1,2,3)) =SUM(OFFSET(B2,1,0,2,3)) =SUM(OFFSET(A1,2,1,2,3)) =SUM(OFFSET(C1:E1,2,-1,2,3)) =SUM(OFFSET(A1,3,1,-2,3)) 循环引用 循环引用和迭代运算 图表分析 预算区间标识 大小值自动分析 预算和销售直观对比分析 数据不同数量级的分析 预算区间标识及均线 折线图和散点图 散点图的误差线 不同数量级的图表分析 柱图和折线图 双轴图表 复合饼图 动态图表 =Offset案例.xls!销售额 =OFFSET(Offset与动态图表!$C$3,1,0,COUNTA(Offset与动态图表!$C:$C)-1,1) 销售额 用数据透视表进行数据分析 对数据表生成数据透视表 对数据字段进行分类 对数据字段进行汇总 对分类字段进行多级分类 制作二维分类表 隐藏汇总行 对多个数据进行汇总 对分类字段进行分组 对数据透视表添加计算字段或计算项 字段数据表 生成数据透视表 数据透视表分析举例 * *