《Excel统计分析与决策（第2版）》第11章 多元回归分析

Excel统计分析与决策第11章多元回归分析主要内容11.1多元回归分析的基本内容11.2非线性回归分析11.3品质变量回归分析多元回归分析的基本内容多元回归模型与回归方程参数估计回归方程的似合优度显著性检验11.1多元回归分析的基本内容销售额与广告媒体的关系例11.1哪种广告渠道更有效。选择“VCD”工作表。在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框。在“数据分析”对话框中，于“分析工具”列表中选择“回归”选项，单击“确定”按钮进入“回归”对话框。11.1多元回归分析的基本内容在“Y值输入区域”中输入A1：A21单元格，它代表销售额的数据范围。在“X值输入区域”中输入B1：C21单元格，这里包括“广播”与“电视”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。选中标志。选择95%的置信度。11.1多元回归分析的基本内容在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如图所示。11.1多元回归分析的基本内容回归输出结果解释回归统计表方差分析表回归参数表

11.1多元回归分析的基本内容非线性关系的线性化的基本内容多项式模型对数模型幂函数指数模型11.2非线性回归分析成本产量多项式模型拟合例11.2

拟合二项成本函数。选择“多项式”工作表。绘制成本产量散点图。11.2非线性回归分析在B栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在A、B两列之间增加一列，成本数据将被移到C列。新列必须放在“产量”列旁边，Excel要求自变量是相邻的。在单元格B1中输入“产量平方”。在单元格B2中输入公式“=A2^2”以计算A2单元格数值的平方，并复制到B3：B51区域中的各个单元格中。11.2非线性回归分析在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框。在“数据分析”对话框中，于“分析工具”列表中选择“回归”选项，单击“确定”按钮,进入“回归”对话框。在“Y值输入区域”中输入C1：C51，它代表成本的数据范围。在“X值输入区域”中输入A1：B51，这里包括“产量”与“产量平方”两个自变量，回归工具要求自变量之间必须是相邻的，不能隔开。11.2非线性回归分析选中标志。选择95%的置信度。在“输出区域”中输入D1单元格，表示输出结果的起点。单击“确定”按钮。得多元回归计算结果如图所示。11.2非线性回归分析产量收益对数模型拟合例11.3建立模型说明产量收益之间的关系。打开“对数”工作表。制作产量收益散点图，看出二者呈现出较好的对数形式。11.2非线性回归分析利用回归分析工具得到分析结果如图所示。11.2非线性回归分析生产函数（幂函数）模型拟合例11.4

试对某省近年工业产值、固定资产投资和职工工资资料拟合适当模型加以分析。生产函数是典型的多元幂函数，其形式为：Y=AKαLβ。对幂函数公式两边同时取对数：

Ln(Y)＝Ln(A)＋α*Ln(K)＋β*Ln(L)用Ln(y)作为标准线性回归的因变量，Ln(K)、Ln(L)为自变量进行拟合。11.2非线性回归分析详细过程参照成本产量多项式模型，结果如图：11.2非线性回归分析国内生产总值指数模型拟合例11.5拟合国内生产总值的指数增长模型。详细过程参照成本产量多项式模型，下图是回归分析结果：11.2非线性回归分析两种表现的品质变量回归分析可以将品质变量指定0值或1值，从而使其在回归模型中作为自变量。这些0/1变量有时也称为虚拟变量或哑变量。与在多元回归分析中使用数量变量一样，使用这些哑变量也可建立模型。哑变量只有两个值0或1，当哑变量用于表示某单一特征时，具有此特征的变量等于1，不具有此特征的变量表示为0。11.3品质变量回归分析例11.6使用计算机学生的统计成绩高于不使用计算机学生的统计成绩？选择“计算机”工作表。回归分析工具要求所有的自变量数据是邻近的，所以需移开C列数据，建立哑变量。11.3品质变量回归分析在C栏中任选一个单元格，打开“插入”菜单，选择“列”选项，则在B、C两列之间增加一列，“是否使用计算机”数据将被移到D列。C列用于放置哑变量。在单元格C1中输入“计算机”。在单元格C2中输入条件函数IF的表达式“=IF(D2=”是“,1,0)”，式中表示如果真则为1，如果不真则为0。单元格C2显示值1，将其复制到C3：C21各单元格中。C列中的数值便是哑变量的值，作为自变量使用。11.3品质变量回归分析在“工具”菜单中选择“数据分析”选项，打开“数据分析”对话框。在“数据分析”对话框中，于“分析工具”列表中选择“回归”选项，单击“确定”按钮，进入“回归”对话框。在“Y值输入区域”输入地址为A1：A21。在“X值输入区域”输入地址为B1：C21。11.3品质变量回归分析选择“标志”和95%的置信度。在“输出区域”中输入E2，单击“确定”按钮，得计算结果如图所示。11.3品质变量回归分析多种表现的品质变量回归分析例11.7拟合售价的预测模型。选择“房地产”工作表。11.3品质变量回归分析在单元格E1、F1和G1中分别输入“高”、“中”和“低”。在单元格E2中输入公式：＝IF(D2＝“高”，1，0)。在单元格F2中输入公式IF(D2＝“中”，1，0)。在单元格G2中输入公式：IF＝(D2＝“低”，1，0)。如果三个公式输入正确，单元格E2：G2的内容是0，0，1。将单元格E2：G2的内容复制到E3：G16区域中的各单元格。11.3品质变量回归分析由于回归分析工具要求变量区域必须邻接，故需对各列位置进行调整。选中D列，点击鼠标右键，在弹出的工具条中选择“剪切”；再选中H列，点击鼠标右键，在弹出的工具条中选择“插入剪切单元格”，则调整后的工作表如图所示。11.3品质变量回归分析打开“数据分析”对话框。在“数据分析”对话框中，于“分析工具”列表中选择“回归”选项，单击“确定”按钮,进入“回归”对话框。1