《用excel进行线性回归分析.pdf》由会员分享，可在线阅读，更多相关《用excel进行线性回归分析.pdf（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第 2 2卷，第 6期 2 0 0 5年 1 1月 光谱实验室 Ch i n e s e,J o u r n a l o f S p e c t r o s c o p y La b o r a t o r y Vo 1 2 2，No6 No v e mb e r，2005 用 E X C E L进行线性 回归分析 及测量不确定度的计算 董 夫 银 (深圳出入境检验检疫局深圳市福田区福强路 1 o i I号大厦 1 5 楼5 1 8 0 4 5)摘 要 用 E XC E L提供 的函数 L I NE S T和 回归 分析工具进行 线性 回归分析，并利用 E X C E L的插入图表功 能做线性
2、 回归拟合 图。也示例说明了线性回归产生的测量不确定度的计算。关 键 词E X C E L，L I N E S T，回归，线性拟合图，测量不确定度 中图分类号：06 5 2；T P 3 文献标识码：B 文章编号：1 0 0 4 8 1 3 8(2 0 0 5)0 6 1 2 3 4 0 5 l 前言 在 日常检验中，尤其是化学分析中，经常要用到一元线性回归方程。这是由于被分析物的浓度 与仪器响应值之间通常是一个线性关系。仪器分析就是利用这个特性，先测得一组已知浓度的标准 溶液的仪器响应值，然后求 出该标准溶液浓度与仪器响应值之间的线性关系，即一元线性回归方 程，利用这个方程就可通过待测溶液的仪
3、器响应值求得待测溶液的浓度。上述线性回归过程尽管复 杂，但在现代仪器分析中，都不要 自己计算，仪器已自动完成。只不过仪器计算完后给出的有关该一 元线性回归方程的信息较少，给计算测量不确定度以及其他一些需要用到一元线性回归特性参数 的场合带来很大的不便。尽管市面上有许多专门的统计计算软件，但对于简单的统计计算都不如 E X C E I 经济、易得和实用。在 E X C E I 中集成很 多统计计算功能，它的回归计算功能很强，不但可 以进行一元线性回归计算，而且可以进行多重线性回归计算，也能画线性拟合图。因此非常有用。下 面以一元线性回归为例，介绍具体的计算过程，该计算过程也同样适用于 多重线性回
4、归。2 测量方法 假设，为了得到一元线性回归方程，用已知浓度 c，的不同标准溶液进行 n次测量，得到 n 个仪 器响应值，然后再按最小二乘法就可求得的一元线性回归方程为 A-C B。+B。，其中 为仪器 响应值；c：待测溶液的浓度；B ：斜率；B。：截距。由于这是一条拟合直线，按照该线性回归方程求得的待测浓度就不可避免地带来测量不确定 度。该测量不确定度按下式进行计算 1 ：厅丁 L)：=瓦，十i十 ：_ 其中：(c)待测溶液浓度 c的测量不确定度；P 重复测试的次数。和 值由下式求得：联 系人，电话 t(0 7 5 5)8 3 3 9 0 2 9 3；(0 7 5 5)8 3 8 8 6 1
5、 6 3；手机 t 0 1 3 6 0 0 1 9 1 9 6 4；传真：(0 7 5 5)8 3 3 9 6 4 5 5；E ma i l d o n 8 6 8 C y a h o o c o m c n 作者简介：董夫银(1 9 6 4 一)，男，浙江省泰顺县人，高级工程师，从事化学及分析化学 中测量不确定度的评估 的研 究。收稿 日期：2 0 0 5 0 7 0 5 维普资讯 http:/ 第 6期 董夫银：用 E X CE L进行线性回归分析及 测量不确定度的计算。A，一(B。+B c，)82=型 一一 1 S 一(C，-O)J=1 ：不同标准溶液浓度的平均值(共测量 次)。下面将测
6、量分成单次测量与重复测量两种情况分别进行介绍。2 1 单次测 量 对于单次测量即每个标准溶液浓度 只测量一次的情形。例如，校准标准溶液 的浓度与仪器的响应值见表 1。表 1 单次测量 标准溶液浓度0 0 0 1 0 0 2 0 0 5 0 0 1 0 0 0 仪器响应值 l 1 2 5 2 4 6 6 1 9 1 2 5 0 2 1 1 用 E XC E I 求一元 线性 回归 方程及其 参数 打 开 E XC E I ，在 E X C E I 的 和 B列分别输入标准溶液浓度和仪器 响应值(见图 1)。在 E X C E I 中有两种方法求一元线性回归方程，即 L I NE S T函数法和回
7、归法。(1)I I NE S T 函数法 先选一个放计算结果的区间，如 c 1：D5，然后输入一I I NE S T，会出现 I I NE S T(k n o wn y s，kn o wnx S，c o n s t，s t a t s)，设 浓度 为 X轴，仪器 响应值 为 Y 轴，而 c o n s t 和 s t a t s 均用 t r u e 代 入，则在 括号内分别输入(B 1：B 5，A1：A5，t r u e，t r u e)，然后同时按 C t r l+S h i f t+E n t e r，则出现下图 1。上，V，川L J i a$v J，J”蔫；慧 兹 篝 蓑 篓 蔫 篓
8、嚣 蔓 馨 誉 塞 篓 篓 篓 警 蕃 -!ll：iiiiiiiii n n n 1 I 1 2 q 9 1 4 1 荔 鬻囊 簿 l 2 搿 1 0 O 1 2 5 辩 囊 夔 i ：；U U 2 4 6 蘸 囊 l 誊；5 O 0 6 1 9 攀 簸 錾 。：0 1 I l II I 1 2 5 0 囊 鹾 熬缝i 1 L i n e s t珐 计 算 不意 图 在 c 1：D5区域就出现 了一元线性回归计算 的结果，按照 E X C E I 的规定，C1 单元格为斜率；D1单元格为截距；C2 单元格为斜率的标准差；D2 单元格为截距的标准差；C 3单元格为 R z 值；D3 单元格为仪器
9、响应值 A的标准差,C 4单元格为 F统计值，D 4单元格为自由度，C 5 单元格为回归平 方和，D5 单元格为残差平方和。所 以，从 图 1 可 得斜 率 B =1 2 4 9 1 4 1；截距 B。一-1 4 9 0 8，一 元线性 回归 方程 为：A 一 1 24 91 41 C-1 4 9 0 8 (2)用“数 据分 析”中的“回归”工具 在“工具”菜单上，单击“数据分析”。如果没有“数据分析”，则要加载“分析工具库，加载宏。具 体 做法 如下：在“工具”菜单上，单击“加载宏”。在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。单击“数据分析”后，在所出现的分析工具列表中选
10、 中“回归”，单击确定后出现图 2。在输入 X 维普资讯 http:/ 光谱实验室 第 2 2卷 值区域时，可先将“值输入区域”的框清空，然后 在 框上 点上 鼠标指 针，涂 黑放 数 值 的区域即 A1：A5。Y也按同样的方式输入。然后在需要显示 的内容前打上相应的勾后，按确定，得 图 3的数据(仅列出所产生的部分数据，没有列出所产生的线 性拟 合 图)。在表 中，每个数据代表什么，一 清二 楚，不像 L I NE S T函数计算的结果，需要记住 E X C E I 的规 定才能知道每个数据的含义。另外，在图 2的列表 中有很 多选项，可以做线性 拟合 图、正态概 率图 等。利用所给出的 F
11、值、t 值及置信概率，还可以对 线性回归方程进行显著性检验。图 4的数据表明，F值 大于 查表 所得 的 F 、。(O 0 5)一1 0 1 3或 t 值大 图 2 数 据分析 法中的回归工具 示意 图 于查表所得的 t。(0 0 2 5)一3 1 8 2 4，因此 与 y之间的线性相关关系显著。图 3 利用 数 据 分析 法 中 的回 归工 具 的 计 算 不 意 图 2 1 2 画线性 拟合 图 上述所介绍的“数据分析”中的“回归”工具的功能较多，有画线性回归拟合图的功能。除此之 外，线性拟 合 图还可 以通过 以 下方式 获得：在“插入”菜单上，单击“图表”，在“标准类型”列表上选 中“
12、X Y散点图”，点击“下一步”，在“系 列”列表上分别输入 x和 y的数值的区域(与图 2回归图表中的 和 y值的区域一样)。或在“数 据区域”列表上输入 x与 y的总的数值区域即 A1：B 5，并在“系列产生在”上选中“列”。点击“下一 步”在“标题”列表上在“图表标题”中填人“线性回归拟合图”或认为合适的其他名称；在数值()和 数值(y)上分别填人“X”与“y”。点击“下一步”，然后在出现的列表中选 中“作为其 中的对象插入”，点击“完成”，则会看到一个线性回归图。点击该图表的任何位置，则在工具栏中会出现“图表”菜单，单击“添加趋势线”，在“类型”列表中选择“线性”，点击“确定”就得到图
13、4。维普资讯 http:/ 第 6期 董夫银：用 E X C E L进行线性回归分析及 测量不确定度的计算 2 1 3测量 不确 定度 的计 算 计 算测量不确定度(c)时，可利用上述计 算所得的 B1结果，即图 1中 c 1单元格的数值。对于 5，可直接利用 D3单元格的标准差数据，也可利用图 1 D 5单元格的残差 平 方 和 数 值，这 是 由于 S 一残 差 平 方 和(n 一 2)，故 S一 ，一3 4 7 9 4 1 7。对于，则需要利用 E X C E I 的标准偏差函数 s t d e v，因为 S 等于标准偏差的平方乘以 图 4线性 回归拟合图 x 浓度：g L；y仪 器响应
14、值。(一1)。为求标准偏差，任选一个单元格 F 1，输入=s t d e v，在出现的括号内输入标准溶液浓度的区 间 A1：A5，得 一s t d e v(A1：A5)，按 回车 键，得标 准 偏 差值 为 4 0 3 7 3 2 6，故 S 一(4 0 3 7 3 2 6)4 6 5 2。待测浓度 C可通过待测物的仪器响应值，再按上述所求得的回归方程求出浓度即可(仪器一 般自动 产生)，设所求得的待测浓度 c一2 0 6，并假设测量次数为一次，即 尸一1，本例中标准溶液测 量 5次故 n 一5，用 AVE R AG E函数计算，结果为 3 6 0，将上述参数代入“(c)的计算公式则得“(c)
15、一0 0 3 0 9 7 2，该测量不确定度计算过程也可通过 E X C E I 来进行，具体见图 5第一行 框中所 列 出的式子。“_ v “i 、-；-“1 1 2 4 9 1 4 1 -1 4 9 0 8 标准偏差 4 0 3 7 3 2 6 、J _ ，iii2-i!i：-ii!i 1 0 0 1 2 5 0 4 3 0 9 0 6 2 1 9 7 1 9 9 平均值 3 6 0 2 0 0 2 4 6 0 9 9 9 9 6 4 3 4 7 9 4 1 7 s 值 3 4 7 9 4 1 7 ：；5 O 0 6 1 9 8 4 0 3 4 5 2 3 S x x 6 5 2 5：1
16、0 0 0 1 2 5 0 1 0 1 7 3 5 0 3 6 3 1 9 0 2 难 2 0 6 i i 略：鬈 u(C)值 1 0 0 3 0 9 7 2 1 图 5 测量不确定度的计算示意 图 2 2 重 复测 量 对于重复测量即每个标准浓度测量多次的情形，见表 2。表 2 重复测量 标 准溶液浓度(g L)仪器 响应值 l 仪器响应值 2 仪器响应值 3 01 0 3 0 5 0 7 0 9 0 0 2 8 0 0 8 4 0 1 3 5 0、l 8 0 0 2 1 5 0 0 2 9 0 08 3 0 1 3l 0 1 8l 0 2 3 0 0 0 2 9 0 0 8l 0 1 3
17、3 O 1 8 3 (本例选目参考文献 L l P 7 5页的例子)用 L I NE S T进行计算时，要注意标准溶液的浓度的数量为 1 5 个而不是 5 个。结果如下：从图 6可得 0 0 0 5 4 8 6；斜率 B 一0 2 4 1；斜率的标准差为 0 0 0 5 0 0 8；截距 B。：0 00 8 7；截 距 的标准差为 0 0 0 2 8 7 7；尺 0 9 9 4 4 1 8(即 尺一0 9 9 7 2 0 5)；另按上述 2 1 3的步骤可计算得 一 1 2(要按 1 5个浓度计算)。这些数值与参考文献给出的数值完全一致。另外，上述 2 1 1(2)所介绍的“回归”工具不适合于
18、重复NN_的情况。对于上述 2 1 2 所介绍 的画线性拟合图，除了能添加趋势线外，也能进行拟图。对于本例 要将数据分成三个系列，即 A列 对 D列，B列对 E列，C列对 F列，然后按照上述方法进行即可。维普资讯 http:/ 光谱实验室 第 2 2卷 一一 篓 -L I N E S (D I F 5，A I：C 5，T R U E J T R U E)j j=I 0 1 0 1 0 1 0 0 2 8 0 0 29 0 0 2 9 _ 0 3 0 3 0 3 0 0 8 4 0 0 8 3 0 0 8 1 ，。_ 0 5 0 5 0 5 0 1 3 5 0 1 3 1 0 1 3 3 ：誓
19、一 0 7 0 7 0 7 0 1 8 0 i 8 1 0 1 8 3 0 0 9 0 9 0 9 2 1 5 0 2 3 0 2 1 6 l l 。一 参考文献 图 6 重复测量 的计算示意 图 1 中国实验室认可委 员会 化 学分析 中不确定度 的评估指 南 M 北京：中国计量出版社出版，2 0 0 2 7 8 Li ne a r Re g r e s s i o n An a l y s i s a nd Ca l c u l a t i o n o f Unc e r t a i n t y wi t h EXCEL S o f t wa r e DONG Fu Yi n (z J I
20、 En t r y-Exit I n s p e c t io n a n d Qu a r a n t i n e Bu r e a u，S h e n z h e n，Gu a n gd ong 5 1 8 0 4 5，P R Ch in 口)Ab s t r a c t The LI NEST t e c hn i q u e，t h e r e gr e s s i o n t o o l a n d c h a r t t oo l bu i l t i n t h e EXCEI we r e u s e d t(、g e n e r a t e a b e s t f i t l
21、 i n e a n d c a l c u l a t e r e l e v a n t p a r a me t e r s a s We l l a s u n c e r t a i n t y Ke y wo r d s EXCEI ，I I NES T，Re gr e s s i o n，I i n e a r Fi t Cha r t，Unc e r t a i n t y Ca l c u 1 a t i 0 n 光谱实验室 2 0 0 5年售价实际继续下 降 由于 光谱实验室 投稿数量 不断增加，为了保 证 出版 周期，从 2 0 0 5 年 第 1 期开始，在 2 0 0 4年的基础上每 册正文增加页码 1 6 页，而售价保持不变：2 0 0 3年售价：2 O 元 册，页码为 1 6 0页 册，平均 0 1 2 5元 页；2 0 0 4 年售价：2 5 元 册，页码为 2 0 8 页 册，平均 0 1 2 0元 页。2 0 0 5 年售价：2 5元 册，页 码为 2 2 4 页 册，平均 0 1 1 2 元 页。因此，售价实际继续卞降。光谱实验 室 编辑部 维普资讯 http:/