Excel 统计分析与决策第10章 简单线性回归分析主要内容 10.1 简单线性回归的主要内容10.2 图表分析与回归函数分析10.3 归分
析的检验10.4 回归分析预测10.5 Excel 回归分析工具10.1 简单线性回归的主要内容回归分析的概念现实世界中大多数现象
表现为相关关系，人们通过大量观察，将现象之间的相关关系抽象概括为函数关系，并用函数形式或模型来描述与推断现象间的具体变动关系，用一
个或一组变量的变化来估计与推算另一个变量的变化。这种分析方法称为回归分析。 简单线性回归分析的主要内容简单线性回归模型的参数估计回
归方程拟合效果评价简单回归参数的推断 10.1 简单线性回归的主要内容10.2 图表分析与回归函数分析利用图表进行回归分
析例10.1 产量与总成本之间的关系。选择“成本”工作表。从“插入”菜单中选择“图表”选项，打开“图表向导步骤1”对话框。在“图表
类型”列表中选择XY散点图，单击“下一步”按钮。在图表向导步骤2对话框中，在数据区域中输入C2：D37，选择“系列产生在”“列”单
击“下一步”按钮。在第3步图表选项中，打开“图例”页面，取消图例，省略标题。单击“完成”按钮，便得到XY散点图。10.2 图表分析
与回归函数分析用鼠标激活散点图，把鼠标放在任一数据点上，单击鼠标右键，打开菜单，在菜单栏里选择“填加趋势线”选项。打开趋势线对话框
。打开“类型”页面，选择“线性”选项，Excel将显示一条拟合数据点的直线。 10.2 图表分析与回归函数分析打开“选项”页面，在
对话框下部选择“显示公式”和“显示R平方根”选项。单击“确定”按钮，便得到趋势回归图。 10.2 图表分析与回归函数分析Excel
中的回归分析工作表函数截距函数INTERCEPT斜率函数SLOPE测定系数函数RSQ估计标准误差函数STEYX10.2 图表分析与
回归函数分析利用工作表函数进行回归分析例10.2 “成本”工作表数据为例，计算以上四个函数。选择“成本”工作表。10.2 图表分
析与回归函数分析在单元格E2:E4中分别输入“截距b0”、“斜率b1”、“估计标准误差”和“回归系数”。在单元格F2中输入公式“=
INTERCEPT(D2:D37,C2:C37)” ，单击回车键。在单元格F3中输入公式“=SLOPE(D2:D37,C2:C37
)”，单击回车键。在单元格F4中输入公式“=STEYX(D2:D37,C2:C37)”，单击回车键。在单元格F5中输入公式“=RS
Q(D2:D37,C2:C37)”，单击回车键。计算结果如上图的右边部分所示。10.2 图表分析与回归函数分析利用Excel加和工
作表函数进行回归分析例10.3 利用Excel加和工作表函数进行回归分析。选择“成本”工作表。在单元格G1中输入“用加和函数计算”
。在单元格H2:M2中分别输入“xi”、“yi”、“(xi-Σx/n)^2”、“(yi-Σy/n)^2”“(xi-Σx/n)(yi
-Σy/n)”和“(I10-($G$2+$G$3H10))^2”，分别表示“产量”“总成本”“LXX”“LYY”“LXY”和“误
差项的平方”。在G39:G40中分别输入“求和”和“均值”。并将C2::D37中的数据复制到H3:I38中。10.2 图表分析与回
归函数分析在单元格H39中输入公式“=SUM(H3:H38)”。在单元格H40中输入公式“=H39/36”。在单元格J3:L3中分
别输入公式“=(H3-$H$40)^2”、“=(I3-$I$40)^2”和“=(H3-$H$40)(I3-$I$40)”将单元格
J3:L3中的公式复制到单元格J4:L37中。将单元格H39:H40中的公式复制到I39:L40中。在单元格G3中输入公式“=L3
9/J39”并单击回车键。在单元格G2中输入公式“=I40-G3H40”并单击回车键。 在单元格G5中输入公式“=(L39/SQ
RT(J39K39))^2”并单击回车键。10.2 图表分析与回归函数分析在单元格M3中输入公式“=(I3-($G$2+$G$3
H3))^2”，单击回车键。将单元格M3中的公式复制到M4:M38中。在单元格M39中输入公式“=SUM(M3:M38)”。在单
元格G4中输入公式“=SQRT(M39/(36-2))”，单击回车键。计算结果如图所示10.2 图表分析与回归函数分析 回归分析检
验的基本内容回归参数的检验置信区间F检验10.3 回归分析的检验回归分析的参数检验和方程的检验例10.4 进行回归分析的参数检验和
方程的检验。选择“成本”工作表。10.3 回归分析的检验在单元格O5中输入“0.05”。在单元格O6中输入公式“=TINV(O5,
36-2)”。在单元格Q5中输入公式“=TDIST(Q6,36-2,2)” 在单元格Q6中输入公式“=SQRT(F3/(F4/J3
9))”。在单元格O10中输入公式“=IF(Q5O6,
"拒绝","不能拒绝")” 。在单元格O16中输入“0.05”，在单元格O17中输入公式“=FINV(0.05,1,36-2)”。
在单元格Q16中输入公式“=FDIST(Q17,1,36-2)” 。在单元格Q17中输入公式“=G3L39/(M39/(36-2
))”。在单元格O21中输入公式“=IF(Q16O
17,"拒绝","不能拒绝")” 。结果拒绝零假设，回归方程是显著的。10.3 回归分析的检验回归分析预测的基本理论点估计利用估计
的回归方程，对于x的一个特定值，求出的y平均值的一个点估计，或者对于x的一个给定值，预测y的一个个别值。y平均值的置信区间
的估计标准误差： 10.4 回归分析的预测y的个别值的预测区间10.4 回归分析的预测回归分析预测的应用例10.5 以95
%的置信度预测当产量为58时，总成本的区间范围。选择“成本”工作表。10.4 回归分析的预测在单元格S2中输入“58”。在单元格S
3中输入公式“=F2+F3S2”。在单元格S4中输入公式“=SQRT(M39/(36-2))SQRT(1+(1/36)+(S2
-H40)^2/(J39))” 。在单元格S5中输入公式“=TINV(0.05,34)”。在单元格S6中输入公式“=S5S4”。
在单元格S7中输入公式“=S3+S6”，在单元格S8中输入公式“=S3-S6” 计算结果如上图所示。10.4 回归分析的预测 回归
分析工具的主要内容10.5 Excel回归分析工具回归分析工具的应用例 10. 6 拟合产量和总成本的回归方程。选择“成本”工作
表。在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框。在“分析工具”列表中选择“回归”选项，单击“确定”按钮，打开“回
归”对话框。10.5 Excel回归分析工具在Y值输入区域中输入D2：D37,在X值输入区域中输入C2：C37,选择“标志”，置信
度选择95%。在“输出选项”中选择“输出区域”，在其右边的位置输入“T1”，单击 “确定”按钮。输出结果如图所示。10.5 Exc
el回归分析工具 回归分析工具的输出解释回归统计Multiple R（复相关系数 R）R Square(复测定系数R2)Adjus
ted R Square (调整复测定系数R2)标准误差观测值10.5 Excel回归分析工具方差分析表“回归分析”行计算的是估计
值同均值之差的各项指标“残差”行是用于计算每个样本观察值y与估计值之差(y-)的各项指标“总计”行用于计算每个y值同均值之差 的各
项指标第二列df是自由度第三列SS是离差的平方和第四列MS是均方差，它是离差平方和除以自由度第五列是F统计量第六列(“Signif
icance F”)是在显著水平下的临界值10.5 Excel回归分析工具回归参数表第一行是β0(截距)，第二行是β1(斜率)。对
于大多数回归分析来讲，关注β1要比β0重要，因此，建立在β1基础上的推断要比对β0的推断更重要。第二列给出了回归系数β0(截距)和β1(斜率)的值。据此可以写出回归方程。第三列给出回归系数的标准误差，回归工具也会计算和显示b0的标准误差，但通常很少使用。第四列给出用于原假设H0：β0 = β1 =0的样本统计量t的值。第五列给出各个回归系数的p值(双侧)，最后是给出(β0和β1)95%的置信区间的上下限。10.5 Excel回归分析工具 |
|
