EXCELl数据处理及分析

补充知识：相关分析-相关系数
相关分析方法：计算相关系数和绘制统计图形 相关系数评判标准： 0.3以下——不相关 0.3-0.5——低度相关 0.5-0.8——显著相关 0.8以上——高度相关 相关系数求法：利用Excel中的CORREL函数。 运用Excel的分析工具库中的“协方差”和“相关系数 ”两个工具进行相关分析非常简单。
EXCEL在数据分析中的应用 24
补充知识：回归分析-回归系数
回归系数与相关系数的符号相同。
即正相关时，回归系数大于 0 ；
百度文库
负相关时，回归系数小于 0 。

回归系数的大小受计量单位的影响。
补充知识：回归分析-判定系数
回归平方和总离均差平方和之比称为判定系数
或决定系数。记为 R 2 ，其取值在 0 和 1 之间，判
7.2 利用单变量求解实现指标值分析 方法
见 “ 分析工具 ” 工作薄 “ 单变量求解 ” 工作表。 （ 1 ） D3 初始数值任意，如为 1 ，在 E2 输入公式： =(A3+B3+C3+D3)*6.65 （ 2 ）光标定于 E3 ，数据 — 假设分析 — 单变量求解， 输入： 目标单元格： E3 目标值： 800000 可变单元格： $D$3 。 结论：第 4 季度完成销售量 46318 件才能实现产品最大利 润额 80 万元。
7.4 分析工具库简介 （ 1 ）使用分析工具库求相关系数的方法 数据 — 数据分析 — 相关系数 — 确定，回答对话框。
7.4 分析工具库简介 （ 2 ）使用分析工具库进行回归分析的方法
数据 — 数据分析 — 回归 — 确定，回答对话框。
补充知识：相关分析
成年子女的身高与父亲的身高有关系么？ 一个企业的年利润与费用之间有怎样的关系， 是不是费用越多，年利润会越大呢？ 在数理统计中，经常需要研究两组数据之间的相 关程度，这就是相关性分析。相关性分析在回归 分析中意义重大。其一，相关系数说明了回归直 线及其预测值的准确程度；其二，在进行回归分 析前，如果发现两组数据相关性不大，则不必进 行回归分析。
7.2 利用单变量求解实现指标值分析 7.3 利用数据表实现数据敏感度分析
7.4 利用方案管理器实现多因素分析
7.5 分析工具简介
7.1 安装与卸载 “分析工具库” EXCEL 提供了一组强大的数据分析工具， 称为 “ 分析工具库 ” 。当需 要进行复杂的统计或 工程分析时，可以使用分析工具库节省步骤和 时间（在较为简单的数据处理中很少涉及）。 相对于使用函数输入公式求解复杂问题，分析 工具库简易方便。
7.4 分析工具库简介
2 、分析工具库应用举例
部分分析结果如下 ： — 某省 2010 年储蓄市场预测
求生产总值与储蓄的相关系数 生产总值(X) 生产总值(X) 1 储蓄(Y) 0.990223 回归分析统计值的计算 SUMMARY OUTPUT 回归统计 Multiple R R Square Adjusted R Square 标准误差 观测值 0.990223 0.980541 0.978379 6563.763 11
7.4 分析工具库简介
2、分析工具库应用举例
任务4 使用工具进行回归分析—某省2010年储蓄市场预测
通过回归函数和分析工具两种方法进行分析。数据如下 ：
2010年储蓄市场预测
序号
生产总值(X) 储蓄(Y)
R
0.9902
1
2 3 4 5 6 7 8 9 10 11 12
1999年
2000年 2001年 2002年 2003年 2004年 2005年 2006年 2007年 2008年 2009年 2010年
7.2利用单变量求解实现指标值分析
1 、概述 单变量求解就是求解一个变量的方程，它通过 调整单元格中的数值，使其按照给定的公式满足 目标单元格中的目标值。利用单变量求解有助于 解决一些计划管理工作中的问题（如指标值分 析）。 任务 1 （进行销售量预测） 已知某产品利润为 6.65 元 / 件，目前已统计了 前 3 个季度的销售量，第 4 季度完成多少销售量才 能实现产品最大利润额 80 万元。
7.3 利用数据表实现数据敏感度分析
任务 3 （双变量数据表运算） 见 “ 分析工具 ” 工作薄 “ 模拟运算表（双变量） ” 工作表。 根据产品销售额和奖金提成率，计算员工的业 绩奖金。 方法 在 B4 输入 公式并复制到 B7 （公式要完全一 样）； 选中 A7 ： E12- 数据 - 假设分析 - 数据表 - 引用行 的单元格为 $B$2 ，引用列的单元格是 $B$2 ，即 求出 B8 至 E12 中的值。 B3
1997-2007年收入与储蓄数据图 200000 180000 160000 140000 120000 100000 80000 60000 40000 20000 0
1997年 1999年 2001年 2003年 2005年 2007年
收入(X) 储蓄(Y)
补充知识：回归分析
用量化的方法研究两个或多个变量之间存在的 关系，最终根据变量的观测值建立表达变量之 间关系的曲线方程，这就是所谓的曲线拟合问 题。 运用统计学的理论和方法近似地建立变量之间 的数学方程式，检验和比较一个或者一组变量 对所关注变量的影响程度，进而用一个或者一 组变量的变化解释并预测所关注变量的变化， 这就是回归分析。其中所关注的变量称因为变 量，而影响因变量变化的那些变量称为自变量。
78973.03
84402.28 89677.05 99214.55 109655.17 120332.69 135822.76 159878.34 183217.40 211923.50 249529.90 283669.03
46279.80
53407.47 59621.80
R2
截距a 斜率b
0.9805
方案摘要 当前值: X1 X2 X3
可变单元格: $C$37
$C$39
56
17%
50
15%
56
17%
62
22%
$C$40
结果单元格: $C$41
3900
5620
3800
3700
3900
5620
4100
9540
7.5 分析工具库简介
1 、简介 可以使用 EXCEL 的 “ 分析工具库 ” 中的回归分 析、移动平均、指数平滑、方差分析、相关系数、 协方 差等工具进行数据分析和预测。各类工具 的使用方法大同小异，关键是要学会根据实际工 作选取合适的工具，并能正确解释结果，统计基 础知识是非常必要的。
补充知识：回归分析
根据自变量的个数，可以把回归分析分为简单回 归（一元回归）和多元回归，两者分析的原理相似。 按变量之间关系的形式，回归分析可分为线性回归和 非线性回归。 以一元线性回归方法为例：
ˆ a bx y
a、b为待定参数，b为回归系数（表示直线斜 率），它表明自变量每变动一个单位，因变量平 均变动b个单位；a表示截距，即回归线与Y交点的 纵坐标。
定系数越接近于 1 ，说明回归直线的拟合程度
越高，回归的效果越好；越接近于 0 说明回归 直线的拟合程度越差。
r
2

ˆ y y
y y
2
2
1
y y
ˆ y y
2
2
7.4 分析工具库简介
3 、 使用 TREND 函数预测数据
任务 5 ： 通过前期销售额来预测未来一期 的销售额。
-10177.98 0.78 6563.76 18 28.3946 806.2519 212382 39848
64332.40 标准误差 73762.40 自由度N 86910.60 103617.30 141051.00 161587.30 172534.19 T检验 F检验 增长
119555.40 预测值
储蓄(Y)
1
7.4 分析工具库简介
2 、分析工具库应用举例 — 某省 2010 年储蓄市场预测 回归统计表部分分析结果解释 ： （ 1 ） MULTIPLE R ：复相关系数 R ，是 R 2 的平方根，又称 相关系数。用来衡量 Y 与 X 之间的相关程度， 0.99022 3 表 示二者的关系是高度正相关 . 说明生产总值对储蓄有直接 影响； （ 2 ） R SQUARE ：复测定系数 R 2 ，说明用自变量解释因变 量变差的程度，以测定因变量 Y 的拟合效果，本例 0.9805412 说明用自变量可解释因变量的变差的程度 98.05% 。 （ 3 ） ADJUSTED R SQUARE ：调整复测定系数 R 2 ，仅用 于多元回归。可衡量加入独立变量后模型的拟合程度。 （ 4 ）标准误差：用来衡量拟合程度的大小 ，越小说明拟合 程度越好。 （ 5 ）观测值，用于估计回归方程数据的观测值个数。
说明：默认情况， EXCEL 执行 100 次求解，当与目标值 的差在 0.001 时停止计算，可通过 “ 工具 ”—“ 选项 ”— — 设置 “ 最多次数 ” 和 “ 最大误差 ” 。
7.3 利用数据表实现数据敏感度分析
数据表有单变量数据表和双变量数据表两种。
数据敏感度分析是数据分析中常用的一种方法。它主 要用于确定当一个参数变化时，由它连带的中间变量和 引起的最终结果如何变化。 单因素敏感度分析，是假定其余各量均不变化，只模 拟某一参数对最终结果的影响 ，可通过单变量数据表进 行单因素敏感度分析。（对一个变量输入不同的值（即 更改一个单元格的数值），看它对表中一个或多个公式 结果的影响。 ） 双因素敏感度分析，如需查看两个变量对公式计算的 影响 ，需通过双变量数据表进行双因素敏感度分析。
7.1 安装与卸载 “分析工具库”
1 、安装 OFFICE 按钮 — EXCEL 选项 — 加载项 — 选择 “ 分析工具库 ”— 确定 — 弹出提示安装加载宏， 回答 “ 是 ” 。 安装后，在 “ 数据 ” 选项卡下会出现 “ 分析 ” 组中。 2 、卸载 OFFICE 按钮 — EXCEL 选项 — 加载项 — 取消选 择 “ 分析工具库 ”— 确定。
补充知识：相关分析-图表绘制
图表是使用 EXCEL 进行各种运算分析时一个 非常有用的工具，特别是通过图表，可以更加 直观地提示两组数据之间的内在关系和变化趋 势。 Excel中提供了散点图、折线图、条形图等图表 类型，可以结合相关系数来展示数据间的相关关 系。
下面的实例可以说明。
补充知识：相关分析-图表绘制
7.4 利用方案管理器实现多因素分析
前面介绍的单变量求解只解决了目标确定的情况下单 个影响变量的取值问题；而数据表也最多只能解决两个 变量的变动对于计算结果的影响。对于 一些更为复杂的， 涉及的影响因素较多的决策问题，可使用 EXCEL 的 “ 方 案管理器 ” 。 方案是已命名的一组输入值，对于同一方案的模型， 可以创建多组不同参数值，得到各组不同的结果，每 组 参数和结论都 是一个方案。 示例：见 “ 分析工具 ” 工作薄 “ 其他例题 ” 工作表 “ 方案 管理器 ” ，数据为：
知识点：
（ 1 ）建立工作表 “ 统计函数预测销售额 ” ；
7.3 利用数据表实现数据敏感度分析
任务 2 （单变量数据表运算） 见 “ 分析工具 ” 工作薄 “ 模拟运算表（单变量） ” 工 作表。 根据产品销售额和奖金提成率，计算员工的业绩 奖金。
方法 在 B4 输入 公式并复制到 B7 ； 选中 A7 ： B12- 数据 - 假设分析 - 数据表 - 引用行的 单元格为空，引用列的单元格是 $B$2 ，即求 出 B8 至 B12 中的值。
成本 销售数量 加价百分比 销售费用 毛利 56 1000 17% 3900 5620
7.4 利用方案管理器实现多因素分析
其中毛利处为公式 = 销售数量 * 成本 * 加价百分比 - 销售 费用，添加了 X1,X2,X3 三个方案后，生成的方案报告 如下。方案报告可能是摘要，也可能是数据透祯视表。
Excel2013高级教程
数据统计与处理分析
1
7 Excel的数据分析工具简介
学习目标 1 、学会 安装与卸载 “ 分析工具库 ” 。 2 、初步掌握通过函数进行相关性分析、 回归分析的方法。 3 、初步掌握分析工具库的使用方法。
7 Excel的数据分析工具简介
7.1 安装与卸载 “ 分析工具库 ”