Download
1 / 28
280 likes | 531 Views
相关分析与回归分析的 Excel 应用. 制作人：胡继国. 一、实验目的. 1. 用 Excel 计算相关系数 2. 用 Excel 进行一元线性回归分析. 二、实验内容. ㈠ 用数据分析工具计算相关系数及 t 检验 ㈡ 用一般方法计算相关系数及 t 检验 ㈢ 线性回归方程参数的计算 ㈣ 线性回归方程的假设检验. 案例 2. 案例 1. 结束. 计算相关系数 方法 1. 1. 输入数据到工作表 Sheet1 的 A1:C8 。 2. 单击菜单“工具”→“数据分析”，在弹 出的数据分析对话框中选择“相关系数”，按“确定”按钮 。
E N D
相关分析与回归分析的Excel应用 制作人：胡继国
一、实验目的 1. 用Excel计算相关系数 2. 用Excel进行一元线性回归分析
二、实验内容 ㈠ 用数据分析工具计算相关系数及t检验 ㈡ 用一般方法计算相关系数及t检验 ㈢ 线性回归方程参数的计算 ㈣ 线性回归方程的假设检验 案例2 案例1 结束
计算相关系数方法1 1. 输入数据到工作表Sheet1的A1:C8。 2. 单击菜单“工具”→“数据分析”，在弹出的数据分析对话框中选择“相关系数”，按“确定”按钮 。 3. 在相关系数对话框中输入样本值区域$B$2:$C$8，分组方式选“逐列”，标志位于第一行前的复选框选中，输出区域$A$10, 按“确定”按钮 。 4. 计算相关系数假设检验的统计量t。 5. 查临界 （α为0.05或0.01）。 相关公式 返回
相关分析案例 案例1对一项药物有效期的研究，在生产后的不同时期从3批药品中各取一片，重复三次测量其主要成分的含量。生产后不同时期与其重复测量结果的均数见下表 试计算存放时间x 与主要成分含量y的相关系数 返回
计算相关系数方法2 1. 输入数据到工作表Sheet1的A1:C8 2. 在D2,E2,F2单元格添加X2,Y2,XY列标题；完成ΣX, ΣY,ΣX2, ΣY2, ΣXY的计算 3. 计算X,Y的平均数 4. 计算Lxx,Lyy,Lxy 5. 计算r 6. 计算统计量 t 7. 查临界值 相关公式 返回
方法 1-1 1. 输入数据到工作表Sheet1的A1:C8。 存放数量和测量结果相关系数计算表 返回
方法 1-2 2.单击菜单“工具”→“数据分析”，在弹出的数据分析对话框中选择“相关系数”，按“确定”按钮后出现下列对话框： 相关系数对话框 返回
方法 1-3 3. 在相关系数对话框中输入样本值区域$B$2:$C$8，分组方式选“逐列”，标志位于第一行前的复选框选中，输出区域$A$10, 按“确定”按钮后出现下列结果： 相关系数计算结果 返回
方法 1-4 4. 相关系数假设检验的统计量t 计算统计量t, 在A14单元格输入=B12*SQRT((A8-2)/(1-B12*B12)) 得-8.981。 查临界值 在B14单元格输入=TINV(0.05,4)得2.776。 返回
方法2 -1 • 输入数据到工作表Sheet1的A1:C8。 存放数量和测量结果相关系数计算表 返回
方法2-2 2. 在D2,E2,F2单元格添加X2,Y2,XY列标题；在D3,E3,F3单元格分别输入=B3*B3，=C3*C3，=B3*C3；并将D3,E3,F3中的公式分别复制到D4:D8,E4:E8,F4:F8；完成X2,Y2,XY的计算；选中B3:F8区域，按工具按钮Σ完成ΣX, ΣY,ΣX2, ΣY2, ΣXY的计算。 返回
方法2-3 返回
方法2-4 3. 计算X,Y的平均数 在B10单元格输入=AVERAGE(B3:B8)得到7.5。 在C10单元格输入=AVERAGE(C3:C8)得到49.7。 4. 计算Lxx,Lyy,Lxy 在B11单元格输入=D9-B9*B9/A8得到157.5 （Lxx）。 在D11单元格输入=E9-C9*C9/A8得到17.33（Lyy）。 在F11单元格输入=F9-B9*C9/A8得到-51（Lxy）。 返回
方法2-5 5. 计算相关系数r 在B12单元格输入=F11/SQRT(B11*D11) 得到r为-0.97609。 6. 计算统计量t 在D12单元格输入=B12*SQRT((A8-2)/(1-B12*B12)) 得到t为-8.981。 7. 查临界值 在F12单元格输入=TINV(0.05,4) 得到α为0.05的临界值2.776。 返回
线性回归方程参数的计算 1. 计算b的值 在D13单元格输入=F11/B11得到-0.324。 2. 计算a的值 在B13单元格输入=C10-D13*B10得到52.095。 3. 建立回归方程 Y=a+bx y=52.095-0.324x 返回
线性回归方程的假设检验 方法一 方差分析统计量F值的计算 方法二 用数据分析工具计算统计量 回归公式 返回
方法一 方差分析统计量F值的计算 1 1. 输入数据到工作表Sheet1的A1:C8。 见F值计算表 2. 在D2,E2,F2单元格添加X2,Y2,XY列标题；在D3,E3,F3单元格分别输入=B3*B3，=C3*C3，=B3*C3；并将D3,E3,F3中的公式分别复制到D4:D8,E4:E8,F4:F8；完成X2,Y2,XY的计算；选中B3:F8区域，按工具按钮Σ完成ΣX, ΣY,ΣX2, ΣY2, ΣXY的计算。 3. 计算X,Y的平均数 在B10单元格输入=AVERAGE(B3:B8)得到5 在C10单元格输入=AVERAGE(C3:C8)得到0.242 4. 计算Lxx,Lyy,Lxy 在B11单元格输入=D9-B9*B9/A8，得到70（Lxx） 在D11单元格输入=E9-C9*C9/A8，得到0.1448（Lyy） 在F11单元格输入=F9-B9*C9/A8，得到3.183（Lxy）
方法一 方差分析统计量F值的计算 2 5. 计算b的值 在F12单元格输入=F11/B11得到0.0455。 6. 计算SS回归的值 在F13单元格输入=F12*F11得到0.1447。 7. 计算SS剩余的值 在F13单元格输入=D11-D13得到0.0001。 8. 计算F的值 在B13单元格输入=0.1447/0.0001得到1447。 相关公式
回归分析案例 案例2某药物分析实验室测得砷含量（ ）与吸光度值见下表，试建立砷含量对吸光度的回归直线。 返回
F值计算表 返回
方法二 用数据分析工具计算统计量 1 1. 输入数据到工作表Sheet1的A1:C8。如图10-3。
方法二 用数据分析工具计算统计量 2 2. 单击菜单“工具”→“数据分析”，在弹出的数据分析对话框中选择“回归”，按“确定”按钮后出现下列对话框：
方法二 用数据分析工具计算统计量 3 3. 在“回归”对话框中Y值输入区域输入$C$2:$C$8，X值输入区域输入$B$2:$B$8，标志前的复选框选中，置信度95%，输出区域$A$10, 按“确定”按钮后出现下列结果：
方法二 用数据分析工具计算统计量 4 4. 计算得到： r=B13=0.9761 ， MS回归=16.5143 MS剩余=0.2048 ， F=80.6512 a=52.0952，b=-0.3238。 5. 显示 F0.05(1,4)，F0.01(1,4)，p的值 在C19单元格输入=FINV(0.05,1,4)，得到 7.7086 在E19单元格输入, =FINV(0.01,1,4)，得到 21.1977 在G19单元格输入=FDIST(80.6512,1,4) ，得到 0.0009 认为y与x之间存在回归关系 返回
相关分析统计量计算公式 返回方法1 返回方法2
