Excel回归分析课件.ppt

用EXCEL进行相关与回归分析 水利部灌溉试验总站 用Excel计算描述统计量用Excel进行相关分析用Excel进行回归分析 用Excel进行相关与回归分析 用Excel计算描述统计量 用函数计算描述统计量描述统计菜单项的使用 常用的描述统计量有众数 中位数 算术平均数 调和平均数 几何平均数 极差 四分位差 标准差 方差 标准差系数等 下面介绍如何用函数来计算描述统计量 一 用函数计算描述统计量 用函数运算有两种方法 一是手工输入函数名称及参数 这种输入形式比较简单 快捷 但需要非常熟悉函数名称及其参数的输入形式 所以 只有比较简单的函数才用这种方法输入 二是函数导入法 这是一种最为常用的办法 它适合于所有函数的使用 而且在导入过程中有向导提示 因而非常方便 函数的一般导入过程为 点菜单 插入 找 函数 此时出现一个 插入函数 对话框 在对话框的 选择类别 中确定函数的类别 如常用函数或统计 在 选择函数 内确定欲选的函数名称 如SUM MODE等 点 确定 后即可出现该函数运算的对话框向导 再按向导的提示往下运行即可 1 众数 例 为了分析小麦的分蘖情况 进行了10株小麦的调查 如下图所示 1 手工输入函数名称及参数 单击任一单元格 输入 MODE B2 B11 回车后即可得众数为14 2 函数导入法 点菜单 插入 找 函数 此时出现一个 插入函数 对话框 在对话框的 选择类别 中确定函数的类别 统计 在 选择函数 内确定欲选的函数名称 MODE 点 确定 后即可出现该函数运算的对话框向导 在Number1处输入B2 B11或选择Excel中的B2 B11区域 按 确定 在Excel中即得到众数14 2 中位数 单击任一空白单元格 输入 MEDIAN B2 B11 回车后得中位数为14 3 算术平均数 单击任一空白单元格 输入 AVERAGE B2 B11 回车后得算术平均数为14 1 4 几何平均数 单击任一空白单元格 输入 GEOMEAN B2 B11 回车后得几何平均数为14 02 5 调和平均数 单击任一空白单元格 输入 HARMEAN B2 B11 回车后得调和平均数为1 422 6 截尾平均数 将数据按由小到大顺序排列后 因数据两端值不够稳定 按一定比例去掉头尾两端一定数量的观察值 然后再求平均 这样得到的平均数就是截尾平均数 如果按2 10 即从30个数据中去掉最大的一个值和最小的一个值 再求平均数 单击任一空白单元格 输入 TRIMMEAN B2 B11 1 10 回车后得截尾平均数为14 1 7 全距 单击任一空白单元格 输入 MAX B2 B11 MIN B2 B11 回车后得全距为5 8 标准差 估计样本 单击任一空白单元格 输入 STDEV B2 B11 回车后得标准差为1 524 9 标准差系数 单击任一空白单元格 输入 STDEV B2 B11 AVERAGE B2 B11 回车后得标准差系数为0 1080 10 偏度系数 单击任一空白单元格 输入 SKEW B2 B11 回车后得偏度系数为 0 678 11 峰度系数 单击任一空白单元格 输入 KURT B2 B11 回车后得峰度系数为0 6249 常用的统计量函数 由于公式执行后显示的是计算结果 按Ctrl 键 位于键盘左上角 可使公式在显示公式内容与显示公式结果之间切换 二 描述统计菜单项的使用 仍使用上面的例子我们已经把数据输入到B2 B11单元格 然后按以下步骤操作 第一步 在工具菜单中选择数据分析选项 从其对话框中选择描述统计 按确定后打开描述统计对话框 第二步 在输入区域中输入 B 1 B 11 在输出区域中选择 F 1 其他复选框可根据需要选定 选择汇总统计 可给出一系列描述统计量 选择平均数置信度 会给出用样本平均数估计总体平均数的置信区间 第K大值和第K小值会给出样本中第K个大值和第K个小值 第三步 单击确定 可得输出结果 上面的结果中 平均指样本均值 标准误差指样本平均数的标准差 中值即中位数 模式指众数 标准偏差指样本标准差 自由度为n 1 峰值即峰度系数 偏斜度即偏度系数 区域实际上是极差 或全距 可以看出与我们前面用函数计算的结果完全相同 最大值为16 最小值为11 第三个最大值为15 第三个最小值为13 1 利用函数计算相关系数2 用相关系数宏计算相关系数 利用EXCEL计算相关系数 1 利用函数计算相关系数 在Excel中 提供了两个计算两个变量之间相关系数的方法 CORREL函数和PERSON函数 这两个函数是等价的 这里我们介绍用CORREL函数计算相关系数 第一步 单击任一个空白单元格 单击 插入 菜单 选择 函数 打开 插入函数 对话框 在 选择类别 中选择 统计 在 选择函数 中选择 CORREL 单击确定后 出现CORREL对话框 第二步 在array1中输入B3 B10 在array2中输入C3 C10 即可在对话框下方显示出计算结果为0 77723 2 用相关系数宏计算相关系数 第一步 单击 工具 菜单 选择 数据分析 项 在 数据分析 项中选择 相关系数 弹出 相关系数 对话框 第二步 在 输入区域 输入 B 2 C 10 分组方式 选择逐列 选择 标志位于第一行 在 输出区域 中输入 I 3 单击 确定 用Excel进行回归分析 Excel进行回归分析同样分函数和回归分析宏两种形式 其提供了9个函数用于建立回归模型和预测 这9个函数分别是 1 INTERCEPT返回线性回归模型的截距2 SLOPE返回线性回归模型的斜率3 RSQ返回线性回归模型的判定系数4 FORECAST返回一元线性回归模型的预测值5 STEYX计算估计的标准误差6 TREND计算线性回归线的趋势值7 GROWTH返回指数曲线的趋势值8 LINEST返回线性回归模型的参数9 LOGEST返回指数曲线模型的参数用函数进行回归分析比较麻烦 这里介绍使用回归分析宏进行回归分析 第一步 单击 工具 菜单 选择 数据分析 选项 出现 数据分析 对话框 在分析工具中选择 回归 用Excel进行回归分析 第二步 单击 确定 按钮 弹出 回归 对话框 在 Y值输入区域 输入 B 1 B 11 在 X值输入区域 输入 C 1 C 11 在 输出选项 选择 E 1 如下图所示 多元回归分析 用Excel进行回归分析 第三步 单击确定按钮 得回归分析结果如下图所示 用Excel进行回归分析 Excel回归分析工具的输出结果包括3个部分 1 回归统计表回归统计表包括以下几部分内容 MultipleR 复相关系数R 是R2的平方根 又称为相关系数 用来衡量变量x和y之间相关程度的大小 本例中R为0 6313 表示二者之间的关系是正相关 RSquare 复测定系数R2 用来说明自变量解释因变量变差的程度 以测定因变量y的拟合效果 回归分析工具的输出解释 AdjustedRSquare 调整复测定系数R2 仅用于多元回归才有意义 它用于衡量加入独立变量后模型的拟合程度 当有新的独立变量加入后 即使这一变量同因变量之间不相关 未经修正的R2也要增大 修正的R2仅用于比较含有同一个因变量的各种模型 标准误差 用来衡量拟合程度的大小 也用于计算与回归相关的其他统计量 此值越小 说明拟合程度越好 观测值 用于估计回归方程的数据的观测值个数 回归分析工具的输出解释 2 方差分析表方差分析表的主要作用是通过F检验来判断回归模型的回归效果 表中 回归分析 行计算的是估计值同均值之差 的各项指标 残差 行是用于计算每个样本观察值与估计值之差 的各项指标 总计 行用于计算每个值同均值之差 的各项指标 第二列df是自由度 第三列SS是离差的平方和 第四列MS是均方差 它是离差平方和除以自由度 第五列是F统计量 第六列SignificanceF是在显著性水平下的F 的临界值 用Excel进行回归分析 幂函数回归 基本形式 JENSEN模型 水分生产函数 两边同时取对数转化为线性形式 用Excel进行回归分析 用Excel进行回归分析 用Excel进行回归分析 用Excel进行回归分析 指数模型 基本形式