1 / 28

相关分析与回归分析的 Excel 应用

相关分析与回归分析的 Excel 应用. 制作人：胡继国. 一、实验目的. 1. 用 Excel 计算相关系数 2. 用 Excel 进行一元线性回归分析. 二、实验内容. ㈠ 用数据分析工具计算相关系数及 t 检验 ㈡ 用一般方法计算相关系数及 t 检验 ㈢ 线性回归方程参数的计算 ㈣ 线性回归方程的假设检验. 案例 2. 案例 1. 结束. 计算相关系数 方法 1. 1. 输入数据到工作表 Sheet1 的 A1:C8 。 2. 单击菜单“工具”→“数据分析”，在弹 出的数据分析对话框中选择“相关系数”，按“确定”按钮 。

luana
Télécharger la présentation

相关分析与回归分析的 Excel 应用

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 相关分析与回归分析的Excel应用 制作人：胡继国

  2. 一、实验目的 1. 用Excel计算相关系数 2. 用Excel进行一元线性回归分析

  3. 二、实验内容 ㈠ 用数据分析工具计算相关系数及t检验 ㈡ 用一般方法计算相关系数及t检验 ㈢ 线性回归方程参数的计算 ㈣ 线性回归方程的假设检验 案例2 案例1 结束

  4. 计算相关系数方法1 1. 输入数据到工作表Sheet1的A1:C8。 2. 单击菜单“工具”→“数据分析”，在弹出的数据分析对话框中选择“相关系数”，按“确定”按钮 。 3. 在相关系数对话框中输入样本值区域$B$2:$C$8，分组方式选“逐列”，标志位于第一行前的复选框选中，输出区域$A$10, 按“确定”按钮 。 4. 计算相关系数假设检验的统计量t。 5. 查临界 （α为0.05或0.01）。 相关公式 返回

  5. 相关分析案例 案例1对一项药物有效期的研究，在生产后的不同时期从3批药品中各取一片，重复三次测量其主要成分的含量。生产后不同时期与其重复测量结果的均数见下表 试计算存放时间x 与主要成分含量y的相关系数 返回

  6. 计算相关系数方法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. 查临界值 相关公式 返回

  7. 方法 1-1 1. 输入数据到工作表Sheet1的A1:C8。 存放数量和测量结果相关系数计算表 返回

  8. 方法 1-2 2.单击菜单“工具”→“数据分析”，在弹出的数据分析对话框中选择“相关系数”，按“确定”按钮后出现下列对话框： 相关系数对话框 返回

  9. 方法 1-3 3. 在相关系数对话框中输入样本值区域$B$2:$C$8，分组方式选“逐列”，标志位于第一行前的复选框选中，输出区域$A$10, 按“确定”按钮后出现下列结果： 相关系数计算结果 返回

  10. 方法 1-4 4.　相关系数假设检验的统计量t 计算统计量t, 在A14单元格输入=B12*SQRT((A8-2)/(1-B12*B12)) 得-8.981。 查临界值 在B14单元格输入=TINV(0.05,4)得2.776。 返回

  11. 方法2 -1 • 输入数据到工作表Sheet1的A1:C8。 存放数量和测量结果相关系数计算表 返回

  12. 方法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的计算。 返回

  13. 方法2-3 返回

  14. 方法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）。 返回

  15. 方法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。 返回

  16. 线性回归方程参数的计算 1. 计算b的值 在D13单元格输入=F11/B11得到-0.324。 2. 计算a的值 在B13单元格输入=C10-D13*B10得到52.095。 3. 建立回归方程 Y=a+bx y=52.095-0.324x 返回

  17. 线性回归方程的假设检验 方法一 方差分析统计量F值的计算 方法二 用数据分析工具计算统计量 回归公式 返回

  18. 方法一 方差分析统计量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）

  19. 方法一 方差分析统计量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。 相关公式

  20. 回归分析案例 案例2某药物分析实验室测得砷含量（ ）与吸光度值见下表，试建立砷含量对吸光度的回归直线。 返回

  21. F值计算表 返回

  22. 方法二 用数据分析工具计算统计量 1 1. 输入数据到工作表Sheet1的A1:C8。如图10-3。

  23. 方法二 用数据分析工具计算统计量 2 2. 单击菜单“工具”→“数据分析”，在弹出的数据分析对话框中选择“回归”，按“确定”按钮后出现下列对话框：

  24. 方法二 用数据分析工具计算统计量 3 3. 在“回归”对话框中Y值输入区域输入$C$2:$C$8，X值输入区域输入$B$2:$B$8，标志前的复选框选中，置信度95%，输出区域$A$10, 按“确定”按钮后出现下列结果：

  25. 方法二 用数据分析工具计算统计量 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之间存在回归关系 返回

  26. 相关分析统计量计算公式 返回方法1 返回方法2

  27. 线性回归分析统计量计算公式 返回

  28. 努力获得成功！

More Related