基于excel的实验数据最小二乘法计算探讨.pdf
基于excel的实验数据最小二乘法计算探讨.pdf
第 16 卷 第4期 大 2003 年 12 月出版 学 物 理 实 Vol116 No14 验 Dec1 PHYSICAL EXPERIMENT OF COLLEGE 2003 文章编号 :1007 - 2934 (2003) 04 - 0043 - 03 基于 Excel 的实验数据最小二乘法计算探讨 唐曙光 ( 南京工程学院 ,南京 ,210013) 摘 要 最小二乘法是实验数据处理中最重要的方法之一 ,但因其计算比较繁杂 ,教学 中使用较困难 。本文对 Excel 中三种计算方法进行比较 ,探讨最小二乘法计算的简便方 法。 关键词 最小二乘法 ; 数据处理 ; Excel 中图分类号 :O24115 1 文献标识码 :A 引言 最小二乘法是以误差理论为依据 ,在诸数据处理方法 中 ,误差最小 ,精确性最好 。然 而在实际教学过程中因其计算比较繁杂 ,学生很少采用这一方法 ,影响了学生运用最小二 乘法进行数据处理能力的培养 。 随着计算机的普及 ,运用最小二乘法进行数据处理有了有力的工具 ,然而采用编写程 序的方法处理数据学生仍感到并不简便 。寻找简便易学 、 容易掌握的计算方法是解决学 生掌握最小二乘法进行数据处理的关键 。笔者认为运用最常见的学生也比较了解的软件 Excel 进行最小二乘法的计算 ,其过程简便而且容易掌握 。 2 运用 Excel 进行最小二乘法的计算 Excel 中有多种工具可用于最小二乘法的计算 ,其中的“函数”、 “图表向导”、 “数据分 析” 在处理数据时各有特点 ,用于最小二乘法计算时不需要编写程序 ,处理数据非常简便 。 例 : 温度变化时 ,测得某铜线的电阻 ,数据记录在 Excel 中如表 1 ,求在 0 ℃时铜线的电 阻及其温度系数 。 表 1 实验数据记录表 A B C D E F G H I J K 1 t/ ℃ 2510 3010 3510 4010 4510 5010 5510 6010 6510 7010 2 R/ Ω 11579 11611 11639 11670 11698 11727 11758 11787 11814 11846 这一问题可以用 Excel 通过三种不同的方法进行最小二乘法计算 。 收稿日期 :2002 - 12 - 20 — 43 © 1995-2006 Tsinghua Tongfang Optical Disc Co., Ltd. All rights reserved. — 211 运用 Excel 中的“函数” 进行计算 Excel 中有各类函数三百余个 ,分别用于各种不同的计算 。其中的线性回归拟合线方 程的斜率函数 SLOPE 、 线性回归拟合线方程的截距函数 INTERCEPT 以及相关系数函数 CORREL 可用来确定线性方程 y = ax + b 的 a 、 b 两个系数和计算相关系数以判别线性回归 是否合理 。 如在上例中 ,在空白的单元格单击“插入” 菜单中的“fx 函数”,在弹出的对话框中分别 选中函数名为“SLOPE”“ 、INTERCEPT”、 “CORREL ”的函数 , 在各自的对话框中输入存放数 据的单元格区域 B2 : K2 和 B1 : K1 便可获得斜率 a =αR0 = 0. 00589 ; 截距 b = R0 = 1 . 433 和 相关系数 R = 0 . 9999 的结果 。由此可得在 0 ℃时铜线的电阻为 11433Ω , 温度系数为 α = αR0 / R0 = 4. 108 ×10 - 3 ℃- 1 。 212 运用 Excel 中的“图表向导”进行 计算 “图表向导”是 Excel 中绘制图表 的工具 , 提供有十 多 种“图 表 类 型”。 其中的“XY 散点图”可用来进行回归 分析 ,在生成一张数据分析图时 , 并能 方便地得到拟合线方程和相关系数的 平方 。 单击“插入”菜单中的“图表”, 选 中“XY 散点图”; 在对话框“步骤之二” 的“数据区域” 中输入存放 y 轴数据的 单元格区域 B 2 : K2 ; 在“系列”选项的 图 1 电阻温度系数的测定 “ X 值 ( X ) ”中输入存放 x 轴数据的单 元格区域 B 1 : K1 ; 在对话框“步骤之三” 中确定图形的名称 、 坐标轴的标题以及网格线 , 在 确定图表的插入位置后就完成实验数据分布图 。选中所作的图表 , 在工具栏单击“图表” 中的“添加趋势线”, 在弹出的对话框选项中“类型”选“线性”;“选项”选中“显示公式”和 “显示 R 平方值” 的复选框 , 便可得到拟合线方程和相关系数的平方 。如图 1 所示 , 拟合 线方程为 y = 0 . 0059 x + 1 . 4333 及相关系数的平方 R2 = 0 . 9999 , 由此也可得在 0 ℃时铜线 的电阻为 11433Ω ,温度系数α为 4. 108 ×10 - 3 ℃- 1 。 213 运用 Excel 中的“数据分析” 进行计算 “数据分析” 是 Excel 中为了进行复杂统计或工程分析时节省步骤的一个专用工具 。 使用时单击“工具” 菜单中的“数据分析”命令 。如果“工具”菜单中没有“数据分析”命令 , 则需要安装“分析工具库”。( 在“工具”菜单中 ,单击“加载宏”命令 ,在“加载宏”对话框中 ) 在弹出的“数据分析” 选中“分析工具库”。 对话框中选中“回归”,此工具可通过对一组观 察值使用“最小二乘法” 直线拟合 ,进行线形回归分析 。在弹出的“回归” 对话框“Y 值输入 区域”“ 、X 值输入区域” 中分别输入存放数据的单元格区域 , 选择“输出区域”单选按钮并 输入要显示结果的单元格 , 若选中“线性拟合图”的复选框则可同时生成图表 。单击“确 定” 就完成了所有计算和作图工作 。 “数据分析” 的结果有许多线性回归分析的计算数值 。在本例中不但计算出关系数 R — 44 — © 1995-2006 Tsinghua Tongfang Optical Disc Co., Ltd. All rights reserved. = 0. 9999 ; 斜率 a = 0100589 ; 截距 b = 1 . 433 , 同时在“标准误差” 行中显示测量值 yi 的标准 偏差 s ( y ) = 010016 , 在“标准误差” 列中显示斜率 a 的标准偏差 s ( a ) = 2155 ×10 - 5 和截距 b 的标准偏差 s ( b) = 0100126 等等分析数据 。 214 各运算方法的特点比较 从上面 Excel 中三种计算方法中可看出 : 利用“函数”运算方法简单 ,但需要记住函数 名称 ,缺点是没有图表显示 ; 利用“图表向导” 运算根据对话框 ,所见即所得操作简单 ,数据 和图表都能显示 ,缺点是运算步骤较多 ; 利用“数据分析”运算过程简单 ,运算结果和图表 可一并获得 ,获得的数据分析结果比前两种方法要多而全 ,而过程则简便得多 ,其缺点正 是得到的分析数据太多 ,许多数据是初步进行最小二乘法计算时所不需要的 ,要能够对太 多的数据进行取舍 。对它们各自特点对比如表 2 。 表2 Excel 中三种最小二乘法计算比较表 方法 所需步骤 获得信息 函数 2 步/ 函数 截距 、 斜率 、 相关系数 图表向导 共5步 图表 、 拟合线方程 、 相关系数的平方 数据分析 共2步 截距 、 斜率 、 相关系数 、 标准偏差 ( s ( y) 、 s ( a) 、 s ( b) ) 、 图表 、…等 根据上面三种计算方法的特点 ,笔者认为在运用最小二乘法处理实验数据时以 Excel 中“数据分析” 最为方便又易于掌握 ,获得的信息也最多 。 3 结束语 在众多的最小二乘法计算方法中 ,利用 Excel 来处理 ,不用编写程序 ,简便易学 ,容易 掌握 ,是解决学生掌握最小二乘法进行数据处理的很好工具 ,在培养学生数据处理能力方 面很有帮助 。 参 考 文 献 [1 ] 马哲 1 中文 Excel 2000 中级教程 [M]1 北京 1 机械工业出版社 ,1999 [2 ] 李平 1 大学物理实验教程 [M]1 北京 : 机械工业出版社 ,2001 STUDY ON HOW TO USE THE LEAST SQUARE METHOD IN THE ANALYSIS OF EXPERIMENTAL DATA Tang Shu - guang (Nanjing Institute of Technology ,Nanjing ,Jiangsu ,210013) Abstract :The least square method is one of the most important methods that can be used to analyze experimental da2 ta1But owing to its complicated calculation ,the method is difficult to use in our teaching practice1This paper has made comparision between three calculation methods in Excel to find out the easiest way in calculation by the least square method1 Key words :least sqhare method ;data processing ;excel — 45 © 1995-2006 Tsinghua Tongfang Optical Disc Co., Ltd. All rights reserved. —