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