Excel在曲线拟合中的应用

苏 州 大 学 学 报 ( 医 学 版 )2008 ；wenku.baidu.com8(5)
761
与孵育时间的关系不可能是直线关系，而是曲线关 系。 以 X 的自然对数与 Y 的自然对数作散点图有直 线趋势(图 3)。
表 3 细胞培养液中 SOD 活性与孵育时间的关系
X2
4
8
12
18 24 48
Y 7.9 6.5 5.09 4.16 3.64 3.3 2.35
Excel 操作方式 由于 Y 的自然对数与 X 作散点图有直线趋势， 故拟合指数曲线方程操作步骤如下： 方法一：①在 Excel 中，选择用来制图的数据所 在的单元格区域的 X 和 Y 数据；②单击工具栏中的 图表向导按钮,选择散点图,按图表向导一步步操作 可得到原始数据的散点图；③从图表栏中选择添加 趋势线或在绘图区中鼠标指向某一数据点来选择 添加趋势线；④在添加趋势线的操作中，在类型项 上的趋势预测/回归分析类型中，选择指数（X）；⑤在 选项上选择显示公式和显示 R 平方值，确定即可得 到指数曲线方程为：Y=92.393e-0.5062X 及决定系数 R2= 0.9926。 方法二：①在 Excel 中，对原始数据 Y 作数据变 换， 取原始数据 Y 的自然对数作为新的 Y 数据, 选 择用来制图的数据所在的单元格区域的 X 和自然 对数 Y 数据；步骤②、③同方法一。 ④在添加趋势线 的操作中， 在类型项上的趋势预测/回归分析类型 中 ， 选 择 线 性 （L）；⑤ 在 选 项 中 选 择 显 示 公 式 和 显 示 R 平方值，确定即可得到方程为：y=-0.5062x+4.5261
Key words:Excel; curve fitting; application
在医学科研时，常遇到两个变量间的关系不是 呈直线而呈某种曲线关系，或在某范围内呈曲线关 系[1]。 如服药后的血药浓度－时间关系、暴露于 X 线 下的莫沙奇病毒计数－暴露时间关系、 细胞培养液 中的 SOD 活性－孵育时间关系或毒理学动物实验中 的动物死亡率与给药剂量的关系， 就非直线形式， 而是某种形式的曲线形式，通过作散点图，大体上 就可判断因变量 Y 和自变量 X 的关系是线性趋势 还是非线性趋势。 对于曲线趋势，可通过曲线拟合
SOD 活性的自然对数 酶活性
2.5 2.0 1.5 1.0 0.5
0
1
2
3
4
5
孵育时间的自然对数
图 3 SOD 活性的自然对数与孵育时间的自然对数关系
Excel 操作方式 由于 X 的自然对数与 Y 的自然对数作散点图 有直线趋势，故拟合幂函数曲线方程操作步骤如下： 方法一：步骤①、②、③同“指数曲线拟合”中的 方法一；④在添加趋势线的操作中，在类型项上的趋 势 预 测 /回 归 分 析 类 型 中 ， 选 择 乘 幂 (W)；⑤ 在 选 项 上 选择显示公式和显示 R 平方值，确定即可得到幂函 数 曲 线 方 程 为 ：y =10.786x -0.3809 及 决 定 系 数 R2 = 0.9925。 方法二：步骤①②③④同“指数曲线拟合”中的 方法二； ⑤在选项中选择显示公式和显示 R 平方 值， 确定即可得到方程为：y=-0.3809x+2.3782 及决 定系数 R2=0.9925。 这里的 x 是原始数据 X 的自然 对数， 这里的 y 是原始数据 Y 的自然对数 ， 而 ln (10.786)＝2.3782， 因此方法一与方法二拟合得到的 曲线方程是一致的。 1.4 多项式曲线拟合 实例 4: 研究某 氧 化 酶 活 性 与 pH 值 之 间 的 关 系，结果列于表 4[4]。 其原始数据的散点图如图 4 所 示。 从原始数据散点图可以看出，某氧化酶活性与 pH 值之间的关系有抛物线趋势。 Excel 操作方式: 在 Excel 中，根据图 4, 图表栏中选择添加趋势 线或在绘图区中鼠标指向某一数据点来选择添加趋 势线，在添加趋势线的操作中，在类型项上的趋势预 测/回归分析类型中，选择多项式，在选项中选择显
收稿日期:2008-04-01 作者简介：艾自胜（1966-），男，安徽巢湖人,讲师，在读医学博士，研究方向为骨科流行病学与统计学方法的应用。 通讯 作者：余金明
760
SUZHOU UNIVERSITY JOURNAL OF MEDICAL SCIENCE 2008；28(5)
复查阳性率自然对数 火箭高度
来描述自变量 X 和因变量 Y 之间数量上的依从关 系。 曲线拟合就是选择适当类型的曲线来拟合这些 实测数据，并用拟合的曲线方程来分析两变量之间 的关系。 医学上曲线拟合的常见类型分两类：（1）频 数分布拟合 （如正态分布曲线拟合，Poisson 分布曲 线 拟 合 等 ）； （2） 回 归 曲 线 拟 合 （ 如 对 数 曲 线 拟 合 、 指 数曲线拟合、 幂函数曲线拟合及多项式曲线拟合 等）。 本研究探讨 Excel 在回归曲线拟合中的应用。
及决定系数 R2=0.9926。 这里的 y 是原始数据的自然 对 数 值 ，而 e4.5261=92.397，因 此 方 法 一 与 方 法 二 拟 合 得到的曲线方程是一致的。 1.2 对数曲线拟合
实 例 2： 用 已 知 浓 度 的 免 疫 球 蛋 白 A（IgA,μg/ ml）做火箭电泳，测得火箭高度（cm）如表 2 所示。 试 采用恰当的回归方程描述火箭高度 Y 与 IgA 浓度 X 之间的关系[3]。
从原始数据散点图(图略)可以看出，IgA 浓度与 火箭高度之间的关系不可能是直线关系， 而是曲线 关系。 以 X 的自然对数与 Y 作散点图有直线趋势 （图 2）。
表 2 火箭高度(Y)与 IgA 浓度(X) X 0.2 0.4 0.6 0.8 1 1.2 1.4 1.6 Y 7.6 12.3 15.7 18.2 18.7 21.4 22.6 23.8
关键词: Excel; 曲线拟合; 应用 中图分类号: R195.1 文献标识码: A 文章编号:1673-0399(2008)05-0759-04
The Application of Excel in Curve Fitting
AI Zi-sheng1, ZHANG Chang-qing2, SHAN Lian-cheng2, WANG Gang2, YU Jin-ming3 (1.Dept of Preventive Medicine, Medical College, Tongji University, Shanghai 200092, China; 2. Dept of Orthopaedics, Shanghai Sixth People’s Hospital, Shanghai Jiaotong University, Shanghai 200233, China; 3. School of Public Health, Fudan University, Shanghai 200032, China)
1 Excel 在曲线拟合中的实例操作
1.1 指数曲线拟合 实例 1： 某疾病防治站重复治疗钩虫病患者的
次数（X）与复查阳性率（Y）资料列于表 1[2]。 从原始数据散点图(图略)可以看出，钩虫治疗
次数与复查阳性率之间的关系不可能是直线关系， 而是曲线关系。 以 Y 的自然对数与 X 作散点图有直 线趋势(图 1)。
表 1 某疾病防治站重复治疗钩虫病患者的次数与
复查阳性率资料
X1
23 45 6 7
8
Y 63.9 36 17.1 10.5 7.3 4.5 2.8 1.7
4.5 4.0 3.5 3.0 2.5 2.0 1.5 1.0 0.5
0 123 4 567 8 治疗次数
图 1 钩虫治疗次数与复查阳性率对数的关系
摘要:目的 探讨 Excel 在曲线拟合中的应用。 方法 通过利用 Excel 可绘制散点图的功能，了解原始数据自变 量 X 和因变量 Y 间的线性与非线性关系，并由自变量 X 和（或）因变量 Y 的自然对数转换后的散点图的情况，决定 采用何种曲线拟合。 结果 X 取自然对数与 Y 作散点图有直线趋势，拟合对数曲线方程；Y 取自然对数与 X 作散点 图有直线趋势，拟合指数曲线方程；X 取自然对数与 Y 取自然对数作散点图有直线趋势，拟合幂函数曲线方程；原始 数据 X 和 Y 成抛物线关系，拟合抛物线方程。 结论 Excel 拟合曲线回归简单易行。
25 20 15 10 5 0
-2 -1.5 -1 -0.5 0 0.5 1 IgA 浓度的自然对数
图 2 IgA 浓度自然对数与火箭高度的关系
Excel 操作方式 由于 X 的自然对数与 Y 作散点图有直线趋势， 故拟合对数曲线方程操作步骤如下： 方法一：步骤①②③同“指数曲线拟合”中的方 法一；④在添加趋势线的操作中，在类型项上的趋势 预 测/回 归 分 析 类 型 中 ， 选 择 对 数 (O)；⑤ 在 选 项 上 选 择显示公式和显示 R 平方值，确定即可得到对数曲 线 方 程 ：y =7.7771 ln x +19.745 及 决 定 系 数 R2 = 0.9922。 方法二：步骤①②③④同“指数曲线拟合”中的 方法二； ⑤在选项中选择显示公式和显示 R 平方 值， 确定即可得到方程为：y =7.7771x+19.745 及决 定系数 R2=0.9922。 这里的 x 是原始数据 X 的自然 对数。 1.3 幂函数曲线拟合 实例 3: 某实验室观察了细胞培养液中 SOD 活 性 （Y）与 孵 育 时 间 （X）的 关 系 ，测 定 结 果 如 表 3 所 示[4]。 从原始数据散点图（图略）可以看出，SOD 活性
Abstract：Objective To explore the application of Excel in curve fitting. Methods At first, the relationship of linear or non -linear between dependent X and independent Y in original data can be distinguished by scatter diagram which can be made through Excel software. The scatter diagram of logarithmic transformation between dependent X and ／ or independent Y decides which curve to be fitted. Results There was a linear trend between logarithmic X and Y, the equation of logarithmic curve was able to be fitted, There was a linear trend between logarithmic Y and X, the equation of exponential curve was able to be fitted, There was a linear trend between logarithmic X and logarithmic Y, the equation of power curve can be fitted. There is a parabola pattern between X and Y, the equation of parabola was able to be fitted. Conclusion It is simple and feasible in curve fitting through Excel.
苏 州 大 学 学 报 ( 医 学 版 )2008 ；28(5)
759
Excel 在曲线拟合中的应用
艾自胜 1, 张长青 2, 单连成 2, 王 刚 2, 余金明 3
(1 同济大学医学院 预防医学教研室, 上海 200092; 2. 上海交通大学附属第六人民医院骨科, 上海 200233; 3 复旦大学公共卫生学院, 上海 200032）
示公式和显示 R 平方值，确定即可得到多项式曲线 方 程 为 ：y=-1228.2x2+17672x-59836 及 决 定 系 数 R2 =0.952。
表 4 某氧化酶活性与 pH 值之间的关系 X 6 6.3 6.6 6.9 7.2 7.5 7.8 8.1 8.4 Y 2 015 2 520 3 498 3 675 3 85 3 642 3 165 2 516 2 128