用excel进行线性回归分析.pdf

第22卷，第6期光谱实验室Vo1．22，No．6
2005年11月Chinese,JournalofSpectroscopyLaboratoryNovember，2005

用EXCEL进行线性回归分析
及测量不确定度的计算

董夫银①
(深圳出入境检验检疫局深圳市福田区福强路1oiI号大厦15楼518045)

摘要
用EXCEL提供的函数LINEST和回归分析工具进行线性回归分析，并利用EXCEL的插入图表功
能做线性回归拟合图。也示例说明了线性回归产生的测量不确定度的计算。
关键词EXCEL，LINEST，回归，线性拟合图，测量不确定度
中图分类号：0652；TP3文献标识码：B文章编号：1004—8138(2005)06—1234—05

l前言
在日常检验中，尤其是化学分析中，经常要用到一元线性回归方程。这是由于被分析物的浓度
与仪器响应值之间通常是一个线性关系。仪器分析就是利用这个特性，先测得一组已知浓度的标准
溶液的仪器响应值，然后求出该标准溶液浓度与仪器响应值之间的线性关系，即一元线性回归方
程，利用这个方程就可通过待测溶液的仪器响应值求得待测溶液的浓度。上述线性回归过程尽管复
杂，但在现代仪器分析中，都不要自己计算，仪器已自动完成。只不过仪器计算完后给出的有关该一
元线性回归方程的信息较少，给计算测量不确定度以及其他一些需要用到一元线性回归特性参数
的场合带来很大的不便。尽管市面上有许多专门的统计计算软件，但对于简单的统计计算都不如
EXCEI经济、易得和实用。在EXCEI中集成很多统计计算功能，它的回归计算功能很强，不但可
以进行一元线性回归计算，而且可以进行多重线性回归计算，也能画线性拟合图。因此非常有用。下
面以一元线性回归为例，介绍具体的计算过程，该计算过程也同样适用于多重线性回归。
2测量方法
假设，为了得到一元线性回归方程，用已知浓度c，的不同标准溶液进行n次测量，得到n个仪
器响应值，，然后再按最小二乘法就可求得的一元线性回归方程为A--C·B。+B。，其中为仪器
响应值；c：待测溶液的浓度；B：斜率；B。：截距。
由于这是一条拟合直线，按照该线性回归方程求得的待测浓度就不可避免地带来测量不确定
度。该测量不确定度按下式进行计算[1]：

⋯L)厅■丁
：：=瓦，＼／十i十—：_
其中：(c)——待测溶液浓度c的测量不确定度；P——重复测试的次数。
和值由下式求得：

①联系人，电话t(0755)83390293；[phone]；手机t013600191964；传真：[phone]；Emaildon868C~yahoo．com．cn
作者简介：董夫银(1964一)，男，浙江省泰顺县人，高级工程师，从事化学及分析化学中测量不确定度的评估的研究。
收稿日期：2005—07—05

第6期董夫银：用EXCEL进行线性回归分析及测量不确定度的计算

。∑[A，一(B。+B·c，)]
82=型———一一

1
S一(C，-O)
J=1

：不同标准溶液浓度的平均值(共测量次)。
下面将测量分成单次测量与重复测量两种情况分别进行介绍。
2．1单次测量
对于单次测量即每个标准溶液浓度表1单次测量
只测量一次的情形。例如，校准标准溶液标准溶液浓度0．001．002．005．0010．00
的浓度与仪器的响应值见表1。仪器响应值l1252466191250

2．1．1用EXCEI求一元线性回归方程及其参数
打开EXCEI，在EXCEI的和B列分别输入标准溶液浓度和仪器响应值(见图1)。在
EXCEI中有两种方法求一元线性回归方程，即LINEST函数法和回归法。
(1)IINEST函数法
先选一个放计算结果的区间，如c1：D5，然后输入一IINEST，会出现IINEST(knowny's，
knownx’S
—，const，stats)，设浓度为X轴，仪器响应值为Y轴，而const和stats均用true代入，则在
括号内分别输入(B1：B5，A1：A5，true，true)，然后同时按Ctrl+Shift+Enter，则出现下图1。

⋯上⋯，⋯⋯V，川L．Ji．a$⋯v■J，J
”蔫；⋯慧兹篝蓑篓蔫篓嚣蔓馨誉塞篓篓篓警蕃

·-····'．⋯．．⋯
!~ll}：iiiiinnn1I12q．9141荔鬻囊簿l
2搿1．0O125辩囊夔i
：：；．UU246蘸囊l
誊；5．O0619攀簸錾
。：01Il『III1250囊鹾熬缝i

1Linest珐计算不意图
在c1：D5区域就出现了一元线性回归计算的结果，按照EXCEI的规定，C1单元格为斜率；
D1单元格为截距；C2单元格为斜率的标准差；D2单元格为截距的标准差；C3单元格为Rz值；D3
单元格为仪器响应值A的