广东畜牧兽医科技2007年（第32卷）第4期 畜牧技术·13· MS Excel在畜牧兽医统计中应用（六） 拟合直线及常用曲线 吕敏芝 (广东佛山科学技术学院，广东佛山528231) 中图分类号：S81-0 文献标识码：A 文章编号：1005-8567(2007)04-0013-03 畜牧生产、动物防疫等工作实践中，常常要对 “线性”（图4），在“选项”的页框中勾选“显 试验数据进行线性拟合(linear fit),利用MS 示公式”和“显示R平方值”，单击“确定”（图 Excel的作图功能来拟合直线或常用的曲线，非常 5)。这时在原来线图中增加了一条直线方程： 方便快捷，且拟合是否合理一目了然。现以几组数 y=534.07x-210.25,R2=0.9827(图6)。 据来介绍拟合直线或常用曲线的过程及结果。 1.2多项式方程的拟合步骤①④与直线方程 1直线、多项式、指数曲线的拟合 的拟合完全相同。⑤右击线图中的曲线，在弹出的 例题1：在仙湖肉鸭的生产性能研究中0，得到 对话框中，单击“添加趋势线”，在“类型”的页框 如下一组关于周龄与肉鸭体重(g)的数据（见表 中点击“多项式”，此时，阶数变为可用，根据需要 1),试拟合并优选出肉鸭体重增长的线性方程。 可选择2~4，生成的函数方程对应于2次3项式 1.1直线方程的拟合①输入试验数据（图1）： 至4次5项式，在“选项”的页框中勾选“显示公 ②单击“插入”→“图表”→“XY散点图”，并在 式”和“显示R平方值”，单击“确定”。这时在原 “子图表类型”中点击“平滑线散点图”→“下一 来线图中增加了一条多项式曲线，对于此例阶数选 步”（图2）：③在图表源数据对话框中的数据区 3较合适，可得曲线方程y=-16.597x3+189.42x 域中选定A2:B9,“系列产生在”勾选“列”：④根 -28.413x+70.086,R2=0.9993(图7、图8)。 据需要填写图表标题等选项，之后单击“完成”。 1.3指线方程的拟合步骤①~④与直线方程 这时，一张以周龄为X坐标，以体重为Y坐标的线 的拟合完全相同。⑤右击线图中的曲线，在弹出的 图就展现在眼前（图3）：再进行修饰，使之简洁、 对话框中，单击“添加趋势线”，在“类型”的页框 清晰：⑤右击线图中的曲线，在弹出的对话框中， 中点击“指数”，在“选项”的页框中勾选“显示 单击“添加趋势线”，在“类型”的页框中点击 公式”和“显示R平方值”，单击“确定”。这时在 表1仙湖肉鸭周龄与体重关系 周龄 0 2 3 4 5 6 7 体重 60.7 250.2 582.5 1285.3 1920.8 2556.7 3165.2 3450.5 去利与生之1周天美华 A 1周龄 体重 平型位口 2 0 60.7 3 1 250.2 4 2 582.5 5 3 1285.3 1920.8 7 2556.7 8 6 3165.2 面 7 3450.5 ■ 见图低卫■ 图2 收稿日期：2007-06-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)04-0013-03 收稿日期: 2007- 06- 22 畜牧生产、动物防疫等工作实践中，常常要对 试验数据进行线性拟合 (linear fit)，利用 MS Excel 的作图功能来拟合直线或常用的曲线，非常 方便快捷，且拟合是否合理一目了然。现以几组数 据来介绍拟合直线或常用曲线的过程及结果。 1 直线、多项式、指数曲线的拟合 例题 1：在仙湖肉鸭的生产性能研究中[1] ，得到 如下一组关于周龄与肉鸭体重（g）的数据（见表 1），试拟合并优选出肉鸭体重增长的线性方程。 1.1 直线方程的拟合 ①输入试验数据(图 1)； ②单击“插入”→“图表”→“XY 散点图”，并在 “子图表类型”中点击“平滑线散点图”→“下一 步” (图 2) ；③在图表源数据对话框中的数据区 域中选定 A2：B9，“系列产生在”勾选“列”；④根 据需要填写图表标题等选项，之后单击“完成”。 这时，一张以周龄为 X 坐标，以体重为 Y 坐标的线 图就展现在眼前（图 3）；再进行修饰，使之简洁、 清晰；⑤右击线图中的曲线，在弹出的对话框中， 单击“添加趋势线”，在“类型”的页框中点击 “线性”（图 4)，在“选项”的页框中勾选“显 示公式”和“显示 R 平方值”，单击“确定”（图 5)。 这时在原来线图中增加了一条直线方程： y＝534.07x－210.25，R2 ＝0.9827（图 6)。 1.2 多项式方程的拟合 步骤①～④与直线方程 的拟合完全相同。⑤右击线图中的曲线，在弹出的 对话框中，单击“添加趋势线”，在“类型”的页框 中点击“多项式”，此时，阶数变为可用，根据需要 可选择 2～4，生成的函数方程对应于 2 次 3 项式 至 4 次 5 项式，在“选项”的页框中勾选“显示公 式”和“显示 R 平方值”，单击“确定”。这时在原 来线图中增加了一条多项式曲线，对于此例阶数选 3 较合适，可得曲线方程 y＝－16.597x3 ＋189.42x2 －28.413x＋70.086，R2 ＝0.9993（图 7、图 8)。 1.3 指线方程的拟合 步骤①～④与直线方程 的拟合完全相同。⑤右击线图中的曲线，在弹出的 对话框中，单击“添加趋势线”，在“类型”的页框 中点击“指数”，在“选项”的页框中勾选“显示 公式”和“显示 R 平方值”，单击“确定”。这时在 周龄 0 1 2 3 4 5 6 7 体重 60.7 250.2 582.5 1285.3 1920.8 2556.7 3165.2 3450.5 图 1 图 2 表 1 仙湖肉鸭周龄与体重关系 广东畜牧兽医科技 2007 年( 第 32 卷) 第 4 期 畜牧技术 ·13·
·14· 畜牧技术 MS Excel在畜牧兽医统计中应用（六）-拟合直线及常用曲线一吕敏芝 体国 4000 3500 3000 2500 2000 1500 期胞推莱列望 1000 500 周龄 0 1 2 345678 图3仙湖肉鸭周龄与体重关系的平滑线散点图 图4 enn性 4000 体重 3500 3000 2500 2000 y=534.07x-210.25 1500 R=0.9827 列量带业建 己黑1苹方通) 1000 500 0 ■ -5000 2345678 图5 图6仙湖肉鸭周龄与体重关系的拟合直线 海秀其 体重 4000 3500 3000 2500 2000 1600 y=-16.597x3+189.42x2-28.413x 1000 +70.086 500 R2=0.9993 0 口物 0 1 2 图7 图8仙湖肉鸭周龄与体重关系拟合的多项式曲线 原来线图中增加了一条指数曲线，可得指线方程 y=139.38ea55,R2=0.8758(图9)。 4000 体重 由以上可见，对于例1的数据，多项式方程拟 3500 合的效果最优，拟合度达0.9993，拟合曲线几乎与 3000 平滑线散点图重合在一起（图8）。所以，肉鸭体重 2500 y=139.3800. 增长最优线性方程为y=一16.597x3+189.42x2 2000 R2=0.8758 -28.413x+70.086。 1500 1000 2对数曲线、乘幂方程的拟合 500 例题2：在禽多杀性巴氏杆菌感染对麻保沙星 0 周龄 在鸡体内药动学特征的影响研究中，得到一组感 0 1 23456T8 染鸡静注后采血时间与麻保沙星的血药浓度 图9仙湖肉鸭周龄与体重关系拟合的指数曲线 (mg/L)(见表2)，试拟合并优选出血药浓度曲线。 C1994-2011 China Academic Journal Electronic Publishing House.All rights reserved.http://www.cnki.net
原来线图中增加了一条指数曲线，可得指线方程 y＝139.38e0.5454x ，R2 ＝0.8758（图 9)。 由以上可见，对于例 1 的数据，多项式方程拟 合的效果最优，拟合度达 0.9993，拟合曲线几乎与 平滑线散点图重合在一起（图 8）。所以，肉鸭体重 增长最优线性方程为 y＝－16.597 x3 ＋189.42x2 －28.413x＋70.086。 2 对数曲线、乘幂方程的拟合 例题 2：在禽多杀性巴氏杆菌感染对麻保沙星 在鸡体内药动学特征的影响研究中[2] ，得到一组感 染鸡静注后采血时间与麻保沙星的血药浓度 （mg/L）（见表 2），试拟合并优选出血药浓度曲线。 图 3 仙湖肉鸭周龄与体重关系的平滑线散点图 图 4 图 5 图 6 仙湖肉鸭周龄与体重关系的拟合直线 图 7 图 8 仙湖肉鸭周龄与体重关系拟合的多项式曲线 图 9 仙湖肉鸭周龄与体重关系拟合的指数曲线 · 14 · 畜牧技术 MS Excel 在畜牧兽医统计中应用 ( 六) - 拟合直线及常用曲线—吕敏芝
MS Excel在畜牧兽医统计中应用（六）-拟合直线及常用曲线一吕敏艺 畜牧技术·15· 1■时间浓度 2 0.1 4.45 3 0.25 3.39 0.5 2.76 5 0,75 2.39 6 219 7 1.5 1,9 8 2 1,6 摇) 9 12 10 d 1.03 0.7 12 0.44 0.3 0.09 15 6 0.03 图10 图11 血药浓度 12 血药浓度 10 3 y=-0.7611Ln(x）+2.2662 R2=0.9701 y=1.7483x0.17时 R2=0.8471 ◆ 6 10 1520253035 40采血时间 1 0 510152025303540 采血时间 图2感染鸡采血时间与血药浓度关系拟合的对数曲线 图13感染鸡采血时间与血药浓度关系拟合的乘幂曲线 表2感染鸡静注麻保沙星的血药浓度 时间(h)0.10.250.50.7511.523 469122436 浓度mgL)4.453.392.762.392.191.91.61.21.030.710.440.30.090.03 2.1对数曲线的拟合①输入试验数据（图 0.7733,R2=0.8471(图13). 10):②单击“插入”→“图表”→“XY散点图”， 例2如选择直线、多项式、指数曲线拟合其拟 并在“子图表类型”中点击“平滑线散点图”→ 合度分别为0.4745、0.7346、和0.9402，由此可 “下一步”：③在图表源数据对话框中的数据区域 见，血药浓度曲线最佳的拟合曲线是对数曲线，其 中选定A2:B15,“系列产生在”勾选“列”：④根 R2=0.9701,接近1，拟合的效果最优 据需要填写图表标题等选项，之后单击“完成”。 3线性拟合时应注意的事项 这时，一张以采血时间为X坐标，以血药浓度为Y ①必先将实测值用平滑线散点图描出，观察 坐标的线图就展现在眼前。再进行修饰，使之简 其分布趋势，再结合专业知识选取最接近的函数 洁、清晰：⑤右击线图中的曲线，在弹出的对话框 关系式来拟合：②实测值要尽可能的多，至少5对 中，单击“添加趋势线”，在“类型”的页框中点击 以上，同时自变量的取值范围要尽可能大些，才容 “对数”（图11），在“选项”的页框中勾选“显示 易发现两变量间的变化关系：③拟合度2也称为 公式”和“显示R平方值”，单击“确定”。这时 相关指数或决定系数，其大小介于0和1之间，表 在原来线图中增加了一条对数方程：y=一 示了回归方程估测可靠程度的高低，2愈接近1， 0.7611Ln(x)+2.2662,R2=0.9701(图12)。 表示拟合的曲线愈好。 2.2乘幂方程的拟合步骤①~④与对数曲线 的拟合完全相同。⑤右击线图中的曲线，在弹出的 参考文献： 对话框中，单击“添加趋势线”，在“类型”的页框 [1]吕敏芝，杨承忠，黄得纯，等.仙湖肉鸭配套系的选育进展[山刀 中点击“乘幂”，在“选项”的页框中勾选“显示 中国家禽，2006，(15)：16-18. [2]黄显会，陈杖榴，张淑婷，等.禽多杀性巴氏杆菌感染对麻保 公式”和“显示R平方值”，单击“确定”。这时在 沙星在鸡体内药动学特征的影响研究[刀.畜牧兽医学报， 原来线图中增加了一乘幂方程y=1.7483x一 2003.(341):98-102. C1994-2011 China Academic Journal Electronic Publishing House.All rights reserved.http://www.cnki.net
2.1 对 数 曲 线 的 拟 合 ①输入试验数据 (图 10)；②单击“插入”→“图表”→“XY 散点图”， 并在“子图表类型”中点击“平滑线散点图”→ “下一步”；③在图表源数据对话框中的数据区域 中选定 A2：B15，“系列产生在”勾选“列”；④根 据需要填写图表标题等选项，之后单击“完成”。 这时，一张以采血时间为 X 坐标，以血药浓度为 Y 坐标的线图就展现在眼前。再进行修饰，使之简 洁、清晰；⑤右击线图中的曲线，在弹出的对话框 中，单击“添加趋势线”，在“类型”的页框中点击 “对数”（图 11)，在“选项”的页框中勾选“显示 公式”和“显示 R 平方值”，单击“确定”。这时 在 原 来 线 图 中 增 加 了 一 条 对 数 方 程 ：y ＝－ 0.7611Ln(x)＋2.2662，R2 ＝0.9701（图 12)。 2.2 乘幂方程的拟合 步骤①～④与对数曲线 的拟合完全相同。⑤右击线图中的曲线，在弹出的 对话框中，单击“添加趋势线”，在“类型”的页框 中点击“乘幂”，在“选项”的页框中勾选“显示 公式”和“显示 R 平方值”，单击“确定”。这时在 原来线图中增加了一乘幂方程 y＝1.7483x－ 0.7733，R2 ＝0.8471（图 13)。 例 2 如选择直线、多项式、指数曲线拟合其拟 合度分别为 0.4745、0.7346、和 0.9402，由此可 见，血药浓度曲线最佳的拟合曲线是对数曲线，其 R2 ＝0.9701，接近 1，拟合的效果最优。 3 线性拟合时应注意的事项 ①必先将实测值用平滑线散点图描出，观察 其分布趋势，再结合专业知识选取最接近的函数 关系式来拟合；②实测值要尽可能的多，至少 5 对 以上，同时自变量的取值范围要尽可能大些，才容 易发现两变量间的变化关系；③拟合度 R2 也称为 相关指数或决定系数，其大小介于 0 和 1 之间，表 示了回归方程估测可靠程度的高低，R2 愈接近 1， 表示拟合的曲线愈好。 参考文献: [1] 吕敏芝,杨承忠,黄得纯,等.仙湖肉鸭配套系的选育进展[J]. 中国家禽,2006,(15):16-18. [2] 黄显会,陈杖榴,张淑婷,等.禽多杀性巴氏杆菌感染对麻保 沙星在鸡体内药动学特征的影响研究[J].畜牧兽医学报, 2003,(341):98-102. 图 10 图 11 图 12 感染鸡采血时间与血药浓度关系拟合的对数曲线 图 13 感染鸡采血时间与血药浓度关系拟合的乘幂曲线 时间(h) 0.1 0.25 0.5 0.75 1 1.5 2 3 4 6 9 12 24 36 浓度(mg/L) 4.45 3.39 2.76 2.39 2.19 1.9 1.6 1.2 1.03 0.71 0.44 0.3 0.09 0.03 表 2 感染鸡静注麻保沙星的血药浓度 MS Excel 在畜牧兽医统计中应用 ( 六) - 拟合直线及常用曲线—吕敏芝 畜牧技术 ·15·