excel数据深度挖掘和分析课程.ppt

Excel数据深度挖掘和分析培训师：张剑悦打印版讲义说明：本讲义是课程知识点，并不是讲课 顺序本次培训的目的是为了配合实际工作中 的应用，为了提高课程针对性，因此课程中会 根据大家的实际需求随时添加知识点，或删除 一些暂时用不到的内容，请大家理解数据量不大的数据信息汇=MIF($A$4:$A$15,F4,$D$4:$D$15)相对地址和绝对地址引用举例公式 =C5 / M ( $C$4 : $C$7) 中C5使用了相对地址，所以在 计算其他部门时，可自动进行调整；而求和区域使用了绝对地址“$C$4 : $C$7”，所以在计算时无论 哪个部门都会与相同的区域和进行相除为数据分析做准备• 字段表和二维表 • 什么是细分管理 • 合并的单元格要不要？ • 怎么调整不合理的数据源？管理数据的方法1．用什么样的表来管理数据 2．用字段表如何管理数据用正确的方式来管理数据1．字段表的制作 2．字段表的作用把不规范的表调整为规范的表12Ctrl + Enter3公式和函数应用技巧•在公式中尽量使用引用而少输入数值•可用鼠标点击单元格来输入引用，而不必手工输入•适当使用括号或空格以易于阅读和理解•当公式较长时，可用 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的 单元格区域M(OFFSET(A1:C1,2,1,2,3))=M(OFFSET(B2,1,0,2,3))=M(OFFSET(A1,2,1,2,3))=M(OFFSET(C1:E1,2,-1,2,3))=M(OFFSET(A1,3,1,-2,3))循环引用循环引用和迭代运算图表分析• 预算区间标识 • 大小值自动分析 • 预算和销售直观对比分析 • 数据不同数量级的分析预算区间标识及均线折线图和散点图散点图的误差线不同数量级的图表分析柱图和折线图双轴图表复合饼图动态图表=Offset案例.xls!销售额=OFFSET(Offset与动态图表!$C$3,1,0,COUNTA(Offset与动态图表!$C:$C)-1,1)销售额用数据透视表进行数据分析•对数据表生成数据透视表•对数据字段进行分类•对数据字段进行汇•对分类字段进行多级分类•制作二维分类表•隐藏汇行•对多个数据进行汇•对分类字段进行分组•对数据透视表添加计算字段或计算项字段数据表生成数据透视表数据透视表分析举例。