excel统计分析与决策（第2版）于洪彦 第10章 简单线性回归分析

于洪彦刘金星张洪利Excel统计分析与决策第10章简单线性回归分析主要内容

10.1简单线性回归的主要内容10.2图表分析与回归函数分析10.3归分析的检验10.4回归分析预测10.5Excel回归分析工具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中输入公式“=RSQ(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$3*H10))^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中输入公式“=L39/J39〞并单击回车键。在单元格G2中输入公式“=I40-G3*H40〞并单击回车键。在单元格G5中输入公式“=(L39/SQRT(J39*K39))^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/J39))〞。在单元格O10中输入公式“=IF(Q5<O5,〞拒绝“,〞不能拒绝“)〞。在单元格O11中输入公式“=IF(Q6>O6,"拒绝","不能拒绝")〞。在单元格O16中输入“0.05〞，在单元格O17中输入公式“=FINV(0.05,1,36-2)〞。在单元格Q16中输入公式“=FDIST(Q17,1,36-2)〞。在单元格Q17中输入公式“=G3*L39/(M39/(36-2))〞。在单元格O21中输入公式“=IF(Q16<O16,〞拒绝“,〞不能拒绝“)〞。在单元格O22中输入公式“=IF(Q17>O17,"拒绝","不能拒绝")〞。结果拒绝零假设，回归方程是显著的。10.3回归分析的检验回归分析预测的根本理论点估计利用估计的回归方程，对于x的一个特定值，求出的y平均值的一个点估计，或者对于x的一个给定值，预测y的一个个别值。y平均值的置信区间的估计标准误差：10.4回归分析的预测y的个别值的预测区间10.4回归分析的预测回归分析预测的应用例10.5以95%的置信度预测当产量为58时，总本钱的区间范围。选择“本钱〞工作表。10.4回归分析的预测在单元格S2中输入“58〞。在单元格S3中输入公式“=F2+F3*S2〞。在单元格S4中输入公式“=SQRT(M39/(36-2))*SQRT(1+(1/36)+(S2-H40)^2/(J39))〞。在单元格S5中输入公式“=TINV(0.05,34)〞。在单元格S6中输入公式“=S5*S4〞。在单元格S7中输入公式“=S3+S6〞，在单元格S8中输入公式“=S3-S6〞计算结果如上图所示。10.4回归分析的预测

回归分析工具的主要内容10.5Excel回归分析工具回归分析工具的应用例10.6拟合产量和总本钱的回归方程。选择“本钱〞工作表。在“工具〞菜单中选择“数据分析〞选项，翻开“数据分析〞对话框。在“分析工具〞列表中选择“回归〞选项，单击“确定〞按钮，翻开“回归〞对话框。10.5Excel回归分析工具在Y值输入区域中输入D2：D37,在X值输入区域中输入C2：C37,选择“标志〞，置信度选择95%。在“输出选项〞中选择“输出区域〞，在其右边的位置输入“T1〞，单击“确定〞按钮。输出结果如下图。10.5Excel回归分析工具回归分析工具的输出解释回归统计MultipleR〔复相关系数R〕RSquare(复测定系数R2)AdjustedRSquare(调整复测定系数R2)标准误差观测值10.5Excel回归分析工具方差分析表“回归分析〞行计算的是估计值同均值之差的各项指标“残差〞行是用于计算每个样本观察值y与估计值之差(y-)的各项指标“总计〞行用于计算每个y值同均值之差的各项指标第二列df是自由度第三列SS是离差的平方和第四列MS是均方差，它是离差平方和除以自由度第五列是F统计量第六列(“SignificanceF〞)是在显著水平下的临界值10.5Excel回归分析工具回归参数表第一行是β0(截距)，第二行是β1(斜率)。对于大多数回归分析来讲，关注β1要比β0重要，因此，建立在β1根底上的推断要比对β0的推断更