Excel教程回归分析.ppt

第8章回归分析 8 1线性回归分析的基本原理8 2图表分析与回归函数分析8 3Excel回归分析工具8 4多元回归分析8 5非线性回归分析 本章学习目标 u回归分析的基本思想u利用Excel图表进行线性回归分析u利用Excel回归分析工作表函数进行线性回归分析u利用Excel回归分析工具进行一元及多元线性回归分析u非线性回归分析的基本思路 8 1线性回归分析的基本原理 8 1 1回归分析的概念8 1 2回归分析的主要内容 返回首页 8 1 1回归分析的概念 首先要区分两种主要类型的变量 一种变量相当于通常函数关系中的自变量 对这样的变量能够赋予一个需要的值 如室内的温度 施肥量 或者能够取到一个可观测但不能人为控制的值 如室外的温度 这样的变量称为自变量 自变量的变化能引起另一些变量 如水稻亩产量 的变化 这样的变量称为因变量 由一个或一组非随机变量来估计或预测某一个随机变量的观测值时 所建立的数学模型及所进行的统计分析 称为回归分析 因此 回归分析是研究随机变量与非随机变量之间的数量关系的一种数学方法 如果所建立的模型是线性的就称为线性回归分析 线性回归分析不仅告诉我们怎样建立变量间的数学表达式 即经验公式 而且还利用概率统计知识进行分析讨论 判断出所建立的经验公式的有效性 从而可以进行预测或估计 返回本节 8 1 2回归分析的主要内容 回归分析的内容包括如何确定因变量与自变量之间的回归模型 如何根据样本观测数据 估计并检验回归模型及未知参数 在众多的自变量中 判断哪些变量对因变量的影响是显著的 哪些变量的影响是不显著的 根据自变量的已知值或给定值来估计和预测因变量的值 Excel提供了许多回归分析的方法与工具 它们可用于不同的分析目的 返回本节 8 2图表分析与回归函数分析 8 2 1利用图表进行分析8 2 2Excel中的回归分析工作表函数8 2 3利用工作表函数进行回归分析 返回首页 8 2 1利用图表进行分析 例8 1某种合成纤维的强度与其拉伸倍数之间存在一定关系 图8 1所示 线性回归分析 工作表 是实测12个纤维样品的强度y与相应的拉伸倍数x的数据记录 试求出它们之间的关系 1 打开 线性回归分析 工作表 2 在工具栏上选择 图表向导 按钮 单击打开图表向导对话框 如图8 2所示 在 图表类型 列表框中选择 XY散点图 单击 下一步 按钮进入图表向导步骤2 3 在图表向导步骤2对话框的 数据区域 中输入 B2 C13 选择 系列产生在 为 列 如图8 3所示 单击 下一步 按钮进入步骤3 4 在图表向导步骤3的对话框中 打开 图例 页面 取消 显示图例 省略标题 如图8 4所示 5 单击 完成 按钮 得到XY散点图如图8 5所示 6 在散点图中 把鼠标放在任一数据点上 右击 在快捷菜单中选择 添加趋势线 打开趋势线对话框 7 在 添加趋势线 对话框中打开 类型 页面 选择 线性 选项 在 选项 页面中选择 显示公式 和 显示R平方 选项 单击 确定 按钮 得到趋势回归图 如图8 6所示 图8 1 线性回归分析 xls 工作表 图8 2图表向导 步骤1 图8 3图表向导 步骤2 图8 4图表向导 步骤3 图8 5XY散点图 图8 6趋势回归直线 返回本节 8 2 2Excel中的回归分析工作表函数 Excel提供的回归分析工作表函数主要有以下几个 1 截距函数 2 斜率函数 3 测定系数函数 4 估计标准误差函数 1 截距函数 其功能是利用现有的x值与y值计算直线与y轴的截距 截距为穿过已知的known x s和known y s数据点的线性回归线与y轴的交点 当自变量为0时 使用INTERCEPT函数可以决定因变量的值 例如 当所有的数据点都是在室温或更高的温度下取得的 可以用INTERCEPT函数预测在0 C时金属的电阻 语法 INTERCEPT known y s known x s 图8 7x y数据 图8 8计算截距 2 斜率函数 该函数返回根据known y s和known x s中的数据点拟合的线性回归直线的斜率 斜率为直线上任意两点的垂直距离与水平距离的比值 也就是回归直线的变化率 语法 SLOPE known y s known x s 其中 Known y s为数字型因变量数据点数组或单元格区域 Known x s为自变量数据点集合 3 测定系数函数 3 测定系数函数 该函数返回根据known y s和known x s中数据点计算得出的乘积矩相关系数的平方 R平方值可以解释为y方差与x方差的比例 语法 RSQ known y s known x s 回归直线的斜率计算公式如下 图8 9计算斜率 4 估计标准误差函数 该函数返回通过线性回归法计算每个x的y预测值时所产生的标准误差 标准误差用来度量根据单个x变量计算出的y预测值的误差量 语法 STEYX known y s known x s 其中 Known y s为因变量数据点数组或区域 Known x s为自变量数据点数组或区域 预测值y的标准误差计算公式如下 返回本节 8 2 3利用工作表函数进行回归分析 例8 4在某大学一年级新生体检表中随机抽取10张 得到10名大学生的身高 x 和体重 y 的数据 如图8 10 身高体重 工作表 所示 用Excel提供的工作表函数进行相关计算 1 在单元格A12 A15中分别输入 截距 斜率 测定系数 估计标准误差 2 在单元格B12中输入公式 INTERCEPT C2 C11 B2 B11 回车后显示 79 42015 3 在单元格B13中输入公式 SLOPE C2 C11 B2 B11 回车后显示0 8041825 4 在单元格B14中输入公式 RSQ C2 C11 B2 B11 回车后显示0 6817018 5 在单元格B15中输入公式 STEYX C2 C11 B2 B11 回车后显示2 8180738 计算结果如图8 8所示 图8 10 身高体重 工作表 图8 11 身高体重 回归计算结果 返回本节 8 3Excel回归分析工具 8 3 1回归分析工具的主要内容8 3 2回归分析工具的应用8 3 3回归分析工具的输出解释 返回首页 8 3 1回归分析工具的主要内容 回归分析工具是通过对一组观察值使用 最小平方法 进行直线拟合 以分析一个或多个自变量对单个因变量的影响方向与影响程度的方法 它是Excel中数据分析工具的一个内容 在 工具 菜单中选择 数据分析 选项 会出现 数据分析 对话框 在分析工具中选择 回归 单击 确定 按钮就会进入 回归 对话框 如图8 12所示 在此对话框中主要包括以下内容 Y值输入区域 X值输入区域 标志 置信度 常数为零 输出区域 新工作表组 新工作簿 残差 标准残差 残差图 线形拟合图 正态概率图 返回本节 8 3 2回归分析工具的应用 例8 5以例8 4资料为例 利用回归分析工具进行回归分析 1 打开 身高体重 工作表 2 在 工具 菜单中选择 数据分析 选项 在 分析工具 列表中选择 回归 单击 确定 按钮 打开 回归 对话框 3 在 Y值输入区域 中输入 C 1 C 11 在 X值输入区域 中输入 B 1 B 11 选择 标志 置信度默认 在 输出选项 中选择 输出区域 在其右边输入 D 1 如图8 13所示 单击 确定 按钮输出结果 如图8 14所示 图8 13 回归 对话框 图8 14回归分析结果 返回本节 8 3 3回归分析工具的输出解释 Excel回归分析工具的输出结果包括3个部分 1 回归统计表2 方差分析表3 回归参数表 回归统计表包括以下几部分内容 1 MultipleR 复相关系数R 2 RSquare 复测定系数R2 3 AdjustedRSquare 调整复测定系数R2 4 标准误差 5 观测值 返回本节 8 4多元回归分析 例8 6有一个工厂会计部门在估计每月管理费y时 用工人的劳动日数x1与机器的开工台数x2作自变量 现将当年10个月的数据搜集起来 如图8 15 多元回归分析 工作表 所示 估计y对x1与x2的线性回归方程 0 05 返回首页 1 在 工具 菜单中选择 数据分析 选项 在 分析工具 列表中选择 回归 单击 确定 按钮 打开 回归 对话框 2 在 Y值输入区域 中输入 D1 D11 在 X值输入区域 中输入 B1 C11 选择 标志 置信度默认 在 输出选项 中选择 输出区域 在其右边输入 A12 单击 确定 按钮输出结果 如图8 16所示 图8 15 多元回归分析 工作表 图8 16二元线性回归分析计算结果 返回本节 8 5非线性回归分析 以最小平方法分析非线性关系资料在数量变化上的规律叫做非线性回归分析 从非线性回归的角度看 线性回归仅是其中的一个特例 一个恰当的非线性回归方程的确定不是很容易的 一般要经过变量转换 将非线性问题转化为线性问题解决 下面讨论几种非线性方程线性化的情况 返回首页 1 1 添加趋势线 2 利用回归分析工具 表8 1微量元素超标量与患病人数 图8 17添加对数趋势线结果 图8 18 回归 工具获得的对数曲线模型拟合结果 2 表8 2氰化物浓度数据 图8 19添加指数趋势线结果 8 20 回归 工具获得的指数曲线模型拟合结果 3 返回本节