《生物统计附试验设计》课程教学资源（教研论文）MSExcel在畜牧兽医统计中的应用（三）直线回归和直线相关分析

广东畜牧兽医科技2007年（第32卷）第1期 畜牧技术·27· MS Excel在畜牧兽医统计中的应用（三） 直线回归和直线相关分析 吕敏芝艺 (广东佛山科学技术学院，广东佛山528231) 中图分类号：S81-0 文献标识码：A 文章编号：1005-8567(2007)01-0027-03 回归分regression analysis)是研究呈 1.2操作步骤①输入试验数据如图1。②执行 因果关系的相关变量间的数量关系，建立它们之 菜单工具”下的数据分析”选项，出现数据分 间的回归方程，并利用所建立的回归方程，由自变 析对话框。③在数据分析对话框中选中“回归” 量原因)来预测、控制依变量结果)。回归分析 (图2)，点出确定”弹出回归对话框。④填写Y 包括一元回归分析和多元回归分析。一元回归分 值输入区域：$B$2:$B$13:X值输入区域：$A 析是研究一个自变量与一个依变量的回归关系， $2:$A$13。勾选置信度、残差拟合图等项图 即一因一果”：多元回归分析是研究多个自变量 3),在输出选项”中默认选择的是新工作表 与一个依变量的回归关系，即多因一果”。一元 组”。这里我们自定选择输出区域”的左上起点 回归分析又分为直线回归分析和曲线回归分析两 为A15单元格，输入完成后点击确定”按钮。则 种，多元回归分析又分为多元线性回归分析和多 可得到图4~图6所示的结果。 元非线性回归分析两种。 第重 ■70日龄重 相关分柝correlation analysis).是研究 80 235 呈平行关系的相关变量之间的关系。对两个变量间 2400 272 的直线关系进行相关分析称为简单相关分析（也 6 120 叫直线相关分析)：研究一个变量与多个变量间的 102 95 线性相关称为复相关分析。这里重点介绍利用 10 Excel对两变量进行直线回归与直线相关分析。 105 12 110 2960 1直线回归分析 13 100 2860 1.1例题在四川白鹅的生产性能研究中，得到 图1 如下一组关于雏鹅虱(g)与70日龄重g)的数 数椒分折 分工具初 据，试建立70日龄重y)与雏鹅重x)的直线回 归方程。 表1四川白鹅雏重与70日龄重测定结果 编号 雏鹅重(x) 70日龄重(y) 80 2350 2 86 2400 图2 98 2720 4 90 2500 1.3结果分析图4是一张回归摘要分析表，包 5 120 3150 6 括三部分：回归统计、回归关系的方差分析和直线 102 2680 95 2630 回归的区间估计。表中第一部分显示拟合度达 83 2400 9 113 3080 0.9553,表明建立的回归方程估测的可靠程度较 10 105 2920 高：第二部分显示回归的F统计值为213.81，F的 11 110 2960 12 100 2860 显著值为4.47E-084.47×108),表示回归关系 收稿日期：2006-09-22 C 1994-2011 China Academic Journal Electronic Publishing House.All rights reserved.http://www.cnki.net

MS Excel 在畜牧兽医统计中的应用（ 三） ——直线回归和直线相关分析 吕敏芝 （ 广东佛山科学技术学院，广东 佛山 528231） 中图分类号: S81-0 文献标识码: A 文章编号: 1005-8567(2007)01-0027-03 收稿日期: 2006-09-22 回归分析（ regression analysis）是研究呈 因果关系的相关变量间的数量关系，建立它们之 间的回归方程，并利用所建立的回归方程，由自变 量（ 原因）来预测、控制依变量（ 结果）。回归分析 包括一元回归分析和多元回归分析。一元回归分 析是研究一个自变量与一个依变量的回归关系， 即“ 一因一果”；多元回归分析是研究多个自变量 与一个依变量的回归关系，即“ 多因一果”。一元 回归分析又分为直线回归分析和曲线回归分析两 种，多元回归分析又分为多元线性回归分析和多 元非线性回归分析两种。 相关分析（ correlation analysis）是研究 呈平行关系的相关变量之间的关系。对两个变量间 的直线关系进行相关分析称为简单相关分析（ 也 叫直线相关分析）；研究一个变量与多个变量间的 线性相关称为复相关分析。这里重点介绍利用 Excel 对两变量进行直线回归与直线相关分析。 1 直线回归分析 1.1 例题 在四川白鹅的生产性能研究中，得到 如下一组关于雏鹅重（ g）与 70 日龄重（ g）的数 据，试建立 70 日龄重（ y）与雏鹅重（ x）的直线回 归方程。 表 1 四川白鹅雏重与 70 日龄重测定结果 1.2 操作步骤 ①输入试验数据如图 1。②执行 菜单“ 工具”下的“ 数据分析”选项，出现数据分 析对话框。③在数据分析对话框中选中“ 回归” （ 图 2），点击“ 确定”弹出回归对话框。④填写 Y 值输入区域：＄B＄2：＄B＄13；X 值输入区域：＄A ＄2：＄A＄13。勾选置信度、残差拟合图等项（ 图 3），在“ 输出选项”中默认选择的是“ 新工作表 组”。这里我们自定选择“ 输出区域”的左上起点 为 A15 单元格，输入完成后点击“ 确定”按钮。则 可得到图 4～图 6 所示的结果。 图 1 图 2 1.3 结果分析 图 4 是一张回归摘要分析表，包 括三部分：回归统计、回归关系的方差分析和直线 回归的区间估计。表中第一部分显示拟合度 R2 达 0.9553，表明建立的回归方程估测的可靠程度较 高；第二部分显示回归的 F 统计值为 213.81，F 的 显著值为 4.47E-0（8 4.47×10-8 ），表示回归关系 编号 雏鹅重(x) 70日龄重(y) 1 80 2350 2 86 2400 3 98 2720 4 90 2500 5 120 3150 6 102 2680 7 95 2630 8 83 2400 9 113 3080 10 105 2920 11 110 2960 12 100 2860 广东畜牧兽医科技 2007 年(第 32 卷)第 1 期 畜牧技术 ·27·

·28·畜牧技术 MS Excel在畜牧兽医统计中应用（三）一吕敏艺 极显著，即四川白鹅70日龄重与雏鹅重存在显著 X Variable 1 Line Fit Plot 的直线关系。在实际生产中，可以通过四川白鹅的 4000 雏鹅重对70日龄重做出预测或控制。表中第三部 3000 分给出了回归系数b的值为21.71，其标准误为 -2000 顶测Y 1000 1.485,b的t统计值为14.622，显著值为 0 4.47E-08,95%的置信区间为[18.404,25.021]。回 50 100 150 归截距a的值为582.2，其标准误为147.315，a X Variable 1 的t统计值为3.952，显著值为0.0027,95%的置 图6 信区间为[253.945,910.424]。据此可列出四川白 X Variable 1 Residual Plot 鹅70日龄重与雏鹅重的直线回归方程为：)= 200 21.71x+582.2。图5列出了的预测值及残蕰 100 一y),图6是线性拟合图，可见y的实测值在预 100 测值附近波动，表明拟合的回归方程较合理。 200 X Variable 1 如将图3输出选项的其它各项选上，则显示 残差图图7)及正态概率图图8)。 图7 臣 宝门 Norual Probability Plot T面紫人区糖 段耐35可 1面前大区城： 家3 4000 广常数为年3 和的 2000 出选项 合精出区城西) 0 20 406080 100 120 个新工作表相使 Sample Percentile C新工作用 图8 2 直线相关分析 以1.1的四川白鹅为例，其操作步骤：①输入 图3 试验数据如图1。②执行菜单工具”下的数据 分析”选项，出现数据分析对话框。③在数据分析 对话框中选中相关系数"”（图9），点击确定” 弹出相关系数对话框。④填写输入区域：$A$1: 20方分整 $B$13,分组方式选逐列”，勾选标志位于第一 尔若输入区域填$A$2:$B$3,则不必勾选 标志位于第一行)，在“输出选项”中默认选择的 是新工作表组”，这里我们自定选择输出区 图4 域”的左上起点为F1单元格图10)，输入完成 观值 预测Y 残差 后点出确定”按钮。则可得到图11所示的结果。 2319.15825930.84174085 2 2449.431256-49.4312562 数据分析 ?区 3 2709.9772510.02274975 分折工具 2536.279921-36.2799209 轴足 6 3187.644906 -37.644906 取消 2796.825915-116.825915 7 2644.840752-14.8407517 帮助0 2384.29475815.70524233 9 3035.65974344.34025717 双样本方要 10 2861.96241358.03758655 分 方 11 2970.523244-10.5232443 12 2753.401583106.5984174 图9 图5 C 1994-2011 China Academic Journal Electronic Publishing House.All rights reserved.http://www.cnki.net

极显著，即四川白鹅 70 日龄重与雏鹅重存在显著 的直线关系。在实际生产中，可以通过四川白鹅的 雏鹅重对 70 日龄重做出预测或控制。表中第三部 分给出了回归系数 b 的值为 21.71，其标准误为 1.485，b 的 t 统 计 值 为 14.622， 显 著 值 为 4.47E-08，95%的置信区间为[18.404，25.021]。回 归截距 a 的值为 582.2，其标准误为 147.315，a 的 t 统计值为 3.952，显著值为 0.0027，95%的置 信区间为[253.945，910.424]。据此可列出四川白 鹅 70 日龄重与雏鹅重的直线回归方程为： ＝ 21.71x＋582.2。图 5 列出了的预测值及残差（ －y），图 6 是线性拟合图，可见 y 的实测值在预 测值附近波动，表明拟合的回归方程较合理。 如将图 3 输出选项的其它各项选上，则显示 残差图（ 图 7）及正态概率图（ 图 8）。 图 3 图 4 图 5 图 6 图 7 图 8 2 直线相关分析 以 1.1 的四川白鹅为例，其操作步骤：①输入 试验数据如图 1。②执行菜单“ 工具”下的“ 数据 分析”选项，出现数据分析对话框。③在数据分析 对话框中选中“ 相关系数”（ 图 9），点击“ 确定” 弹出相关系数对话框。④填写输入区域：＄A＄1： ＄B＄13，分组方式选“ 逐列”，勾选标志位于第一 行（ 若输入区域填＄A＄2：＄B＄13，则不必勾选 标志位于第一行），在“ 输出选项”中默认选择的 是“ 新工作表组”，这里我们自定选择“ 输出区 域”的左上起点为 F1 单元格（ 图 10），输入完成 后点击“ 确定”按钮。则可得到图 11 所示的结果。 图 9 · 28 · 畜牧技术 MS Excel 在畜牧兽医统计中应用（ 三）—吕敏芝

MS Excel在畜牧兽医统计中应用（三）一吕敏芝 畜牧技术·29· 定 G H 雏重70日龄重 分植方式 遥行围 形助心 维重 1 口际达位于单一可 70日龄重0.977404 输出选项 年辅出区城@) 图11 C新工作表但优) 广新工作博) 图11给出了四川白鹅70日龄重与雏鹅重的 图10 相关系数为0.9774，表明70日龄重与雏重两者 呈强的正相关。 也也你也尔地你也不心你也不也不地不地不地不地不地标地标地宗心不地不也你也你也你业业你心不地不心你心不心不也你也你也不业不心综必不也不也你业不也不也不也不也不心不也你心你也不世不心标 (上接18页) [14]Matsuzaki T,Tanaka N,Irahara M.Activin and follic- [3]0'Bryan MK,Sebire KL,Gerdprasert 0,et al.Cloning and ular maturation[J].Nippon Rinsho,2006,4:291-6. regulation of the rat activin beta E subunit[I].Jour- [15]Hasegawa Y.Roles of inhibins,activins and related nal of Molecular Endocrinology,2000,24(3):409-18. proteins in reproduction[J].Nippon Rinsho,2006,4: [4]Kretzschmar M,Liu F,Hata A,et al.The TGF-beta fami- 277-83. ly mediator Smadl is phosphorylated directly and [16]Lovell TM,Gladwell RT,Groome NP,et al.Ovarian foll- activated functionally by the BMP receptor kinase icle development in the laying hen is accompanied [J].Genes Development,1997,11(8):984-95. by divergent changes in inhibin A,inhibin B,activin [5]Kretzschmar M,Massague J.SMADs:mediators and regu- A and follistatin production in granulosa and the- lators of TGF-beta signaling[J].Current Opinion Ge- ca layers[J].Journal of Endocrinology,2003,177(1): netic Development,1998,8(1):103-11. 45-55. [6]Shoji H,Tsuchida K,Kishi H,et al.Identification and [17]Nishimori K,Matzuk MM.Transgenic mice in the anal- characterization of a PDZ protein that interacts ysis of reproductive development and function[J]. with activin type II receptors[J].The Journal of Reviews of Reproduction,1996,1:203-12. Biological Chemistry,2000,275(8):5485-92. [18]Eramaa M,Hilden K,Tuuri T,et al.Regulation of in- [7]Sugino H,Sugino K,Hashimoto 0,et al.Follistatin and hibin/activin subunit messenger ribonucleic acids its role as an activin-binding protein[J].The Jou- mRNAs)by activin A and expression of activin re- rnal of Medical Investigation,1997,44(1-2):1-14. ceptor mRNAs in cultured human granulosa-luteal [8]Muttukrishna S,Tannetta D,Groome N,et al.Activin cells[J].Endocrinology.1995,136:4382-9. and follistatin in female reproduction[J].Molecular [19]Hillier SG.Regulatory functions for inhibin and Ce11 Endocrinology,.2004,225(1-2):45-56. activin in human ovaries[J].Journal of Endocrinol- [9]Bussmann UA,Lanuza GM,Bussmann LE.Activin and fol- 0gy.1991,131:171-5. listatin in rat mammary gland[J].Molecular Cell En- [20]Alak BM,Coskun S,Friedman CI,et al.Activin A stim- docrinology..2004,221(1-2):9-19. ulates meiotic maturation of human oocytes and [10]de Winter JP,ten Dijke P,de Vries CJ,et al.Follis- modulates granulosa cell steroidogenesis in vitro tatins neutralize activin bioactivity by inhibi- [J].Fertility and sterility,1998,70:1126-30. tion of activin binding to its type II receptors [21]Silva CC,Knight PG.Modulatory actions of activin-A [J].Molecular and Cellular Endocrinology.1996,116 and follistatin on the developmental competence of (1):105-14. in vitro-matured bovine oocytes[J].Biology of Re- [1l]Phillips DJ,de Kretser DM.Follistatin:a multifun- production,1998,58:558-65. ctional regulatory protein[J].Frontiers in Neuroe- [22]Roberts VJ,Barth SL.Expression of messenger ri- ndocrino1ogy,1998,19(4):287-322. bonucleic acids encoding the inhibin/activin sys- [12]Mather JP.Follistatins and alpha 2-macroglobulin tem during mid-and late-gestation rat embryogene- are soluble binding proteins for inhibin and ac- sis[J].Endocrinology,1994,134(2):914-23. tivin[J].Hormone Research,1996,45(3-5):207-10. [23]Cataldo NA,Fujimoto VY,Jaffe RB.Interferon-gamma [13]Hindley S,Juurlink BH,Gysbers JW,et al.Nitric ox- and activin A promote insulin-like growth factor ide donors enhance neurotrophin-induced neurite binding protein-2 and -4 accumulation by human outgrowth through a cGMP-dependent mechanism[J]. luteinizing granulosa cells,and interferon-gamma Nature,1995,374:354. promotes their apoptosis[J].Journal of Clinical Endocrinology Metabolism,1998,83(1):179-86. C1994-2011 China Academic Journal Electronic Publishing House.All rights reserved.http://www.cnki.net

(上接 18 页) [3] O'Bryan MK,Sebire KL,Gerdprasert O,et al.Cloning and regulation of the rat activin beta E subunit[J].Jour￾nal of Molecular Endocrinology,2000,24(3):409-18. [4] Kretzschmar M,Liu F,Hata A,et al.The TGF-beta fami￾ly mediator Smad1 is phosphorylated directly and activated functionally by the BMP receptor kinase [J].Genes & Development,1997,11(8):984-95. [5] Kretzschmar M,Massague J.SMADs:mediators and regu￾lators of TGF-beta signaling[J].Current Opinion Ge￾netic Development,1998,8(1):103-11. [6] Shoji H,Tsuchida K,Kishi H,et al.Identification and characterization of a PDZ protein that interacts with activin type II receptors[J].The Journal of Biological Chemistry,2000,275(8): 5485-92. [7] Sugino H,Sugino K,Hashimoto O,et al.Follistatin and its role as an activin-binding protein[J].The Jou￾rnal of Medical Investigation,1997,44(1-2):1-14. [8] Muttukrishna S,Tannetta D,Groome N,et al.Activin and follistatin in female reproduction[J].Molecular Cell Endocrinology,2004,225(1-2): 45-56. [9] Bussmann UA,Lanuza GM,Bussmann LE.Activin and fol￾listatin in rat mammary gland[J].Molecular Cell En￾docrinology.2004,221(1-2):9-19. [10] de Winter JP,ten Dijke P,de Vries CJ,et al.Follis￾tatins neutralize activin bioactivity by inhibi￾tion of activin binding to its type II receptors [J].Molecular and Cellular Endocrinology.1996,116 (1):105-14. [11] Phillips DJ,de Kretser DM. Follistatin:a multifun￾ctional regulatory protein[J].Frontiers in Neuroe￾ndocrinology,1998,19(4):287-322. [12] Mather JP.Follistatins and alpha 2-macroglobulin are soluble binding proteins for inhibin and ac￾tivin[J].Hormone Research,1996,45(3-5): 207-10. [13] Hindley S,Juurlink BH,Gysbers JW,et al.Nitric ox￾ide donors enhance neurotrophin-induced neurite outgrowth through a cGMP-dependent mechanism[J]. Nature,1995,374:354. [14] Matsuzaki T,Tanaka N,Irahara M.Activin and follic￾ular maturation[J].Nippon Rinsho,2006,4:291-6. [15] Hasegawa Y.Roles of inhibins,activins and related proteins in reproduction[J].Nippon Rinsho,2006,4: 277-83. [16] Lovell TM,Gladwell RT,Groome NP,et al.Ovarian foll￾icle development in the laying hen is accompanied by divergent changes in inhibin A,inhibin B,activin A and follistatin production in granulosa and the￾ca layers[J].Journal of Endocrinology,2003,177(1): 45-55. [17] Nishimori K,Matzuk MM.Transgenic mice in the anal￾ysis of reproductive development and function[J]. Reviews of Reproduction,1996,1:203-12. [18] Eramaa M,Hilden K,Tuuri T,et al.Regulation of in￾hibin/activin subunit messenger ribonucleic acids （ mRNAs）by activin A and expression of activin re￾ceptor mRNAs in cultured human granulosa-luteal cells[J].Endocrinology.1995,136:4382-9. [19] Hillier SG.Regulatory functions for inhibin and activin in human ovaries[J].Journal of Endocrinol￾ogy.1991,131:171-5. [20] Alak BM,Coskun S,Friedman CI,et al.Activin A stim￾ulates meiotic maturation of human oocytes and modulates granulosa cell steroidogenesis in vitro [J].Fertility and sterility,1998,70:1126-30. [21] Silva CC,Knight PG.Modulatory actions of activin-A and follistatin on the developmental competence of in vitro-matured bovine oocytes[J].Biology of Re￾production,1998,58:558-65. [22] Roberts VJ,Barth SL.Expression of messenger ri￾bonucleic acids encoding the inhibin/activin sys￾tem during mid- and late-gestation rat embryogene￾sis[J].Endocrinology,1994,134(2):914-23. [23] Cataldo NA,Fujimoto VY,Jaffe RB. Interferon-gamma and activin A promote insulin-like growth factor￾binding protein-2 and -4 accumulation by human luteinizing granulosa cells,and interferon-gamma promotes their apoptosis[J].Journal of Clinical Endocrinology Metabolism,1998,83(1):179-86. 图 10 图 11 图 11 给出了四川白鹅 70 日龄重与雏鹅重的 相关系数为 0.9774，表明 70 日龄重与雏重两者 呈强的正相关。 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! MS Excel 在畜牧兽医统计中应用（ 三）—吕敏芝 畜牧技术 ·29·