《excel在统计中的应用教学课件作者肖文博第7章相关与回归分析》由会员分享，可在线阅读，更多相关《excel在统计中的应用教学课件作者肖文博第7章相关与回归分析（36页珍藏版）》请在金锄头文库上搜索。
1、第7章 相关与回归分析,7. 1相关分析 7. 2一元线性回归分析 7. 3多元线性回归分析,返回,7. 1 相关分析,7. 1. 1定性判断相关关系 定性判断是依据专业理论知识和实践经验，对客观现象之间是否存在相关关系以及有何种相关关系做出判断，并可在定性判断的基础上编制相关表、绘制散点图，以便直观地判断现象之间相关的方向、形态及密切程度。 1.相关表 相关表是一种统计表。它是直接根据现象之间的原始资料，将因变量的变量值按自变量从小到大的顺序排列形成的统计表，如图7一1所示。 【例1】喜人公司在全国有许多代理商，为研究其产品的广告投入与销售额的关系，统计人员随机选择了10家代理商进行观察，搜
2、集到年广告投入费和月平均销售额的数据，按图7 -2形式输入Excel。,下一页,返回,7. 1 相关分析,2.散点图 散点图又称相关图，它是用直角坐标系的、轴代表自变量，y轴代表因变量，将两个变量间相对应的变量值用坐标点的形式描绘出来，用以表明相关点分布状况的图形，根据散点图中数据点的形状和整体走向来判断现象之间相关关系的种类和密切程度。 7.1.2定量分析相关系数 相关表和散点图可反映两个变量之间的相互关系及其相关方向，但无法确切地表明两个变量之间相关的程度相关系数是用以反映两个变量之间线性相关关系密切程度的统计指标，相关系数的计算公式为:,上一页,下一页,返回,7. 1 相关分析,1.用常
3、规方法计算相关系数 【例3】以图7 -4中A1 ; C13中的数据为例。 (1)新建一工作表，在A1 ; B3 , D1 ; F1输入如图7一4所示的相应区域数据。 (2)在D2中输入“= B2 * B2 “，按回车键，在E2中输入“= C2 * C2 按回车键;在F2中输入“= B2 * C2”，按回车键。 (3)选中D2;F2,鼠标指针移至F2单元格右下角，待鼠标指针变为“+” 时，按住鼠标左键向下拖至F13，计算出D2;F13数据 (4)在A14中输入“合计”，鼠标单击B14，单击“自动求和”图标，按回车键，计算出B2;B13数据的合计为“1025 ” ;鼠标移至B14单元格右下角，待鼠
4、标变为“+”时，按住鼠标左键向下拖至F14，计算出C14;F14数据。,上一页,下一页,返回,7. 1 相关分析,(5)根据式(7一1)，计算式(7一1)分子部分，譬如在H2中输入“=12* F14一B14 * C14“，按回车键，显示72103;在H3中输入“=SQRT (12*D14-B14 * B14)*(12 * E14一C14 * C14)”，按回车键，显示783s3. 031，得出式(7一1)分母部分。 2.用函数CORREL)计算相关系数 【例4】接【例3】以图7一4的A1;C13中的数据为例 (1)单击一空f1单元格，譬如C16单元格 (2)单击菜单选项【插入(I)，在弹出的下
5、拉菜单中选择【fx函数(D】，显示插入函数对话框;单击“或选择类别(C);”文本框右侧的“，”，显示函数类别，按右侧滚动条，选择“统计”函数，找到“CORREL”，单击“确定”按钮，显示如图7一5所示。,上一页,下一页,返回,7. 1 相关分析,(3)将光标置于Array1右侧文本框中，选择B2;B13单元格区域;再将光标置于Array2右侧文本框中，选择C2;C13单元格区域，单击“确定”，得出相关系数为0. 920 232 3.用“相关系数”分析工具计算相关系数 用常规方法与相关系数函数CORREL()计算相关系数，一次只能计算一个相关系数，用“相关系数”分析工具一次可以计算出多个相关系数
6、，操作步骤如下，仍以图7 -4所示的数据为例。 (1)单击菜单选项【工具(T)】，在弹出的下拉菜单中选择数据分析工具(D);在弹出的“数据分析”对话框中选择“相关系数”，单击“确定”。弹出如图7 -6所示的“相关系数”对话框。,上一页,下一页,返回,7. 1 相关分析,(2)单击“输入区域(I) ;”左侧文本框，将光标置于其中，然后选择B1; C13单元格区域。 (3)选中复选框“标志位于第一行(L)”左侧的口(注意:如果在“偷入区 域(I) ;”的文本框中偷入的单元格区域为B2;C13，则该项不用选)。 (4)单击“输出区域(n):”左侧单选框，然后单击“输出区域(o): 右侧文本框，将光标
7、于其中，再单击工作表一空白单元格，譬如E18，最后单击“确定”，则得计算结果，如图7 -7所示。 7.1.3相关系数检验 为判断样本相关系数能否代表总体相关系数，需对求得的相关系数进行显著性检验首先按式(7一2)求t统计量:,上一页,返回,7. 2 一元线性回归分析,7. 2. 1用常规方法建立一元线性回归方程 对于具有线性相关关系的两个变量，一元线性回归方程为: 式中: 表示因变量的估计值(回归理论值);a, b是待定参数，其中a是 回归直线的起始值(截距)，即x为0时的值，从数学意义上理解，它表示在没有自变量x的影响时，其他各种因素对因变量)的平均影响;b是回归系数(直线的斜率)，表示自变
8、量x每变动一个单位，因变量y平均变动b个单位我们可通过样本观察值计算a, b，进而求样本回归方程，用它对总体线性回归方程进行估计。 一元线性回归方程中的待定参数是根据数据资料求出的，其计算公式为:,下一页,返回,7. 2 一元线性回归分析,7. 2. 2用INTERCEPT()和SLOPE()建立一元线性回归方程 在Excel中，有一个求截距的函数INTERCEPT()和一个求斜率的函数 SLOPE ()，还有一个既能用于一元线性回归又能用于多元线性回归和自回归的函数LINEST下面仍以【例3】表资料为例，分别讲述其操作方法。 【例7】用INTERCEPT()和SLOPE()建立一元线性回归方
9、程，以【例3】资料为例 (1)单击一空白单元格，譬如H15,上一页,下一页,返回,7. 2 一元线性回归分析,(2)单击菜单选项【插入(I)】，在弹出的下拉菜单中选择【fx函数 (F)】,显示插入函数对话框;单击“或选择类别(C);”文本框右侧的 “，”，显示函数类别，按右侧滚动条，选择“统计”函数类别，选择“INTER-CEPT()”，单击“确定”，则弹出“函数参数”对话框，如图7一8所示。 (3)单击Known ys右侧文本框，将光标置于其中，然后选中C2;C13单元格区域;再单击Known xs右侧文本框，将光标置于其中，然后选中B2;B13单元格区域;最后单击“确定”，得出截距。=12
10、4. 15 (4)单击一空白单元格，譬如单击H16，然后插入统计函数“SLOPE()”，单击“确定”，出现“函数参数”对话框，如图7一8所示重复计算截距的函数INTERCEP()的操作步骤，可得斜率b = 0. 420 683。,上一页,下一页,返回,7. 2 一元线性回归分析,7. 2. 3用LINEST()函数建立一元线性回归方程 【例8】用LINEST()函数建立一元线性回归方程，以【例3】资料为例。 (1)首先选定放置计算结果的单元格区域，本例可选定J2 ; K6单元格区域(2列5行共10个单元格区域)，然后单击“常用”工具栏中艺甲右侧黑色“”，然后选择“其他函数”，打开“插入函数”对
11、话框，在右侧选择“统计”，选择“LINEST”，单击“确定”，进入LINEST的“函数参数”对话框，如图7一9所示。,上一页,下一页,返回,7. 2 一元线性回归分析,(2)用鼠标单击Known ys右侧文本框，将光标置于其中，然后选中C2;C13单元格区域;单击Known xs右侧文本框，将光标置于其中，然后选中B2;B13单元格区域;鼠标单击Stats右侧文本框，将光标置于其中，输入0，按“ shift + ctrol”组合键，单击“确定”，得出截距a =124. 15 , h = 0. 420 683 。 若在Stats右侧文本框，将光标置于其中，输入1，按“Ctrl + Shift+回
12、车”组合键，则运算结果如图7一10所示。 7. 2. 4通过“添加趋势线”建立一元线性回归方程 【例9通过“添加趋势线”方法建立一元线性回归方程，操作步骤(1)一(3)例2】的操作步骤(1)一(3)相同。,上一页,下一页,返回,7. 2 一元线性回归分析,(4)单击散点图，当散点图四周出现8个黑色.，说明图表被选中，此时菜单选项【数据(D)变为【图表(C)】 (5)单击菜单选项【图表(C)】，在弹出的下拉菜单中选择添加趋势线 (R).。若在弹出的下拉菜单中没有显示出【添加趋势线(R)】，则单击下拉菜单下方的等就会显示添加趋势线(R).，然后单击选中，就会弹出“添加趋势线”对话框，如图7一11所
13、示。根据散点图中点的走向和趋势来确定添加趋势线类型，黑色表示被选中。本例中选择“线性(L)。 (6)单击“选项”选项卜，在显示的对话框中选择“显示公式(E)”和 “显示R平方值(R)”使前面的方框中出现绿色的“V”，最后单击“确定”其显示结果如图7一12所示。,上一页,下一页,返回,7. 2 一元线性回归分析,7. 2. 5通过“回归”分析工具建立一元线性回归方程 【例10】通过“回归”分析工具建立一元线性回归方程，以例1J中图7一1的数据为例。 (1)单击一空白单元格，选中菜单选项【工具(T)】，在弹出的下拉菜单中选择【数据分析工具(D).】，在弹出的“数据分析”对话框中选择“回归”，单击“
14、确定”，弹出“回归”对话框，如图7一13所示。 (2)单击“Y值输入区域(Y):”右侧文本框，将光标置于其中，然后选择 B1;B11单元格区域。,上一页,下一页,返回,7. 2 一元线性回归分析,(3)在“输出选项”下，选定单选框，单击“输出区域(n):”右侧文本 框，将光标置于其中，单击一空白单元格，最后单击“确定”。 单击“确定”之后会出现如图7一14，7-15，图7一16所示的运算结果(注意:图7一14至图7一16在经过调整单元格式之后可得到)。 7. 2. 6通过一元线性回归方程进行预测 建立回归方程的重要日的是进行预测。如果是进行点预测，只需把需要预测的x的值代人回归方程进行计算即可
15、。如果像在参数估计中可以采用区间估计一样，在预测时一也可以计算预测值的置信区间。在小样本情况下，根据置信水平1一a,给定的x0,置信区间的上下限为:,上一页,返回,7. 3 多元线性回归分析,社会经济现象变动的原因十分复杂，一种事物的变动往往受多方面因素的影响一个因变量的变动往往是由多个自变量变动引起的，因变量与多个自变量之间有线性相关关系，为此就需要建立多元线性回归方程。多元回归分析的思想与一元回归分析类似，只是相关计算要复杂一些，通常多元线性回归方程的一般表达式为: 在Excel中进行多元直线线性回归分析，最合适的工具是“回归分析”下具，现说明其操作方法并对运算结果进行说明。 【例12】兴
16、达公司在10个地区设立了经销分公司，管理部门认为销售额与地区的人口数和年均收入有关，希望建立销售额、地区人口数、年均收入之间的数量关系式，用于预测销售额现搜集到有关数据如图7一17所示(a=0.05)。,下一页,返回,7. 3 多元线性回归分析,(1)如图 7一17所示，输入数据 (2)单击一空白单元格，单击菜单选项【工具(T)，在弹出的下拉菜单中选择【数据分析工具(D).】，在弹出的“数据分析”对话框中选择“回归”，然后单击“确定”，出现“回归”对话框，如图7一13所示。 (3)单击“Y值输入区域(Y)”右侧文本框，将光标置于其中，然后选择 B2; B12单元格区域单击“X值输入区域(X)”右侧的文本框，将光标置于其中，然后选择C2;D12单元格区域选择B1;D1单元格，单击选中“标志”，同时单击选中“置信度95 % “(注意:千万不能选择“常数为0”的复选框)。 (4)在“输出选项”下，选定单选框，单击“输出区域(0)”右侧文本 框，将光标置于其中，单击一空白单元格最后单击“确定”，则会出现如图7-18，7-19，图7 - 20所示的运
