用Excel实现实验数据的计算机处理
翳羲羹纛olT技术论坛o
2锻孥蘩23蘩
用Excel实现实验数据的计算机处理
王劲松
(潍坊学院物理与电子科学系
山东潍坊261061)
i接要】本文简要撂讨了Excd敞件在耪理实验数搬处理申的应弼。并媛海令等通_耱理实赣为铡,务绍了用Excel进学实验数据的镁蓬诗
葬、数据纂逸、残槛耘舍等操嚣姆悬黪和援巧。透过酝e旌酌各静函数瑷及绘朝蓬表等痨毙酌灵活燕薅,可双为实验蜜茨稃学霹霓捷篌一静更
方便、更快捷、更可靠的数耱处理方浚。
【关键词】融cd;误差计算;敷瓣处理;最小二乘法
●
THE
EX瞪R删N1.AL
DATA
P】刚)I国璐SINGⅥ唧EXCEL
instanee,e麟c越e落牡ti镰,d如selecli鳓,linear
wangj细学哺黼嚷
国印射鼬蛐£0f鳓蚓随囊nd鄹ec|咖l越csc蛔招鹣Wel细喙U嫩ve蝴ly,W础l嘴渤糊d锄g26l嘶l,幽ina)
【轰|捧昀c|l拍e印plie醛旺《E麓藤波遗ee曲r鸵蠢即e鹅si珏g西弘iesex滞痰撙e珏|a董妇ah鹅瓿雌discH瓣d至珏穗isl赫i8,§越n《瘙。娃g瓤毪矬d
ski珏s
i珏胛e8siHgexp两蹴ntal
data
d8慷byt五lxc越isintl静dncedby
two
phy《cs糕pe矗ments,lbf
re伊嘲i∞鲫d∞on.11lepr∞e鞘iIlg《lab鲫|tory觚d∞ientific
sqllare
r;eseamh耐ll
be“m
reliable,quickef粕ddirectorbyapplyingdi鳓rentkind¥
offumtiom黝dcharting0fExcel,e懈.
1Keyw0州s】Excel;E珊rcalalllate;Datap麒瑚8ing;least
methodMarco
{。翦赛
,
在物理实验中往往要测量大薰的实验数据.而对这热数据的计算及处理既费时叉费力。如果手工计算的话。还很容易出错。常用方法是通过计算机的编程来进行计算,但熙要编写一个此类的通用计算程序十分困难。经过一段时间的实践和探索。发现对于常见的数据计算和缝理,鲡:诗算警壹每篷、掾握穰差、求辩搴、截题、相关系数、进行妻线羧合、衷解楚萃方羧、逐差法、囊夺二黎法、回努法等蘩嚣xcel孛翡数蕹计算功能来解决即方便又快速。
Excel是一种非编程数据处理工舆软件,它的工作麟础是标有行、列标记的表格。称为“工作表”。表巾的每个格子可用行橼和列标来标记,称为“单元格”。“单元辂”中可以填写文字。也可以填譬数据、函数和表达式。它除了具备通掌豹数据艨功能驻终,还能避褥数值计算、统诗势羲、鑫饕绘裁嚣表等。E”el嫒越方便,是较为褒怒瓣~静数据薤理工具。下面分剃以“用游标卡足溯麟柱体的体积”稷“验诞测俸的转动定律”两个实瓣为例。介绍如何用Excel来实现实披散据的计算机处理。
露1条律穰炎
2.4求圆柱体的体积与标准偏差
将A16和B16单张格合并,并输入公式“±3.142+B13^2#C13,4”.
将A17和B17单元格合并,输入公式“=C16}SQRT((24815,B13心+《c15舷13)垡”疆车就会擞零其体积豹标准偏熬的大小。
菇羚,蓊采发褒嚣鲶羧据臻入镶误或薅黉菱竣,霹壹接在稷壤攀元楱壤写薪的数值,Exoel将自动更正计算旗暴。如果想察看或修改公式和数据范围,可双击棚应单元格,Excel将艘示公式并可对其进行编辑修改。
襄1溯脚拄体的直径、肖度数据表
舂
l23
4
2.误差计算与数据筛选
下面以“用游标卡尺测圆柱体的俸积”为例(见表1)。分绍如何用诤算鬟遴舞溪熬瓣谤箕纛数据薅逡。
2。l求溅爨翊鳕平鹭篷
选取B14单元格后单击工具横上粘贴公式的图标£。并选取常用函数中的Avl£RAGE。然后确定计算范围“B3:B12”。单击确定后,Excel自动将圆柱体的直径平均值熙永在B13单元格中。也可在B13单元格中直接辘入“三avem癣e(B3:B12)“后回车。对于高度平均值的计算可淡翻曩Exeel鹣是动填充功貔来究竣。
2.2求测纛溺豹蠡准穰差与算零擎穗篷熬标建穰藏选取B14攀元格,并输入公式“=s1:DEV(B2:B12)”聪翻车。将自费计算出直径测徽捌的标准偏差;j巅取B15单元格,输入公式“=B14,SQRHlo)”后嘲率,将自动计算出崴径测量列的算术平均值的标准偏差。对于高度测黧列的标准偏差与算术平均值的标准偏麓的计算可以
耘e
蘑辩拣卡疋涮簿棱俸的俸积
测惫次数
l23霹567S910
直经D(∞畸
17.4417.4217鼻217.毒2
高h(mm)
32.1832.1832。20322032.2232.1632.1832,2032.1832.2032.190O.017O.O跫瓣
56789lOll1213‘14151617
.
17舶
17.44
利用&el的巍旗填充功能来完成。
213冥豢数鬃懿潮豫
17舶17射
17.4617.4417.440O.016
剔除灏羹弼巾异常数据的标穗露多种。有3矾准弼、薅维准瓣、格罗布斯判据(Gmbbs)等。下面以常朋的格罗布斯判据为倒来说明。按此判据给出一个和数据个数n相联系的系数G(n=10,Gn=2.18)m,当
值xi的范围为(葶蜮・扩)≤麓《<舯仅・矿),著不在此蔻蹦,剃xi为异常
已知数据个散n、算术平均值和测最捌标准偏差仃。则可以保留的测量
串均值栝壤偏差口
数囊,痉誊台去。:羧定B3一B12单元耩,箨萃壹“接式”蔡纂夺豹。条箨接式”,在条佟格式瓣话框中第一捧瓣溅今选择框蠢辕入耨纛翡篷。并在。格式”按钮中设慧数字的显示格式为“红色斜体”(见瞬1)。田单击“硫定”后.长度测黧中的异常数据将自动以红色的斜体字照泳出来.从而可以很方便地进行下一步的处理。
冀拳平玛撼豹掾准{叠差覃《x)
娜拄俸的俸积(I—夸
聊柱体的标准偏差
囊∞52
奢鲫融S98
4.7
3.数据处理
万方数据
6l
科技信息
oIT技术论坛o
2008年第23期
数据处理的方法较多,本文只对“最小二乘法”作以简要的讨论。3.1最小二乘法
最小二乘法线性拟合是物理实验数据处理的基本方法之一。在物理实验中,经常要观测两个有函数关系的物理量.根据两个量的许多组观测数据来确定它们的函数关系曲线。要借助线性或曲线方程的参数来求出这些物理量之间的关系。嗍最小二乘法以误差理论为依据,在诸多数据处理方法中.误差最小.精确性最好。然而在实际教学过程中因其计算比较繁杂。学生很少采用这一方法。影响了学生运用最小二乘法进行数据处理能力的培养。
随着计算机的普及。运用最小二乘法进行数据处理有了有利的工具,然而采用编写程序的方法处理数据学生仍感到并不简便。寻找简便易学、容易掌握的计算方法是解决学生掌握最小二乘法进行数据处理的关键。而用Excel进行最小二乘法计算时不需要编写程序.处理数据非常简便,容易掌握:
3.2用Excel实现最小二乘法的计算机处理
下面以“验证刚体的转动定律”实验为例,介绍如何用计算机实现“最小二乘法”处理实验数据,以及用“最小二乘法”进行直线拟合。
在“验证刚体的转动定律”实验嗍中,如图2所示。刚体转动定律的数学表达式:
(2)用“最小二乘法”进行线性拟合操作步骤如下:
①单击“插入”菜单中的“图表”,在“图表向导”对话框中,图表类
型选则“XY散点图”,子图表类型选散点图,按“下一步”按扭:
②添加图表数据源系列名称为“m—t_2关系图,x值为=¥F¥3:¥F¥9.
Y值为=¥A¥3:¥A¥9.按“下一步”按扭:
③定义图表选项x轴标题为“t4(s,”,Y轴标题为“m∞”,图例位置为下方,按“完成”按扭,插入了一张图表;
④双击绘图区,定义绘图区无边框,无底纹;
(9双击Y轴,定义图案为实线,刻度最小值为O,最大值为O.04,主要刻度单位0.005,次要刻度单位0.00l,与X轴交叉于O:类似定义X轴:
⑥双击网格线,定义图案线条样式为虚线,刻度根据坐标轴刻度
定义;
⑦双击数据系列标志,定义数据系列图案线型为无.数据标记为
“?”。这样,对应砝码质量(m)和砝码下落时间平方的倒数(t。2)各组数据的散点图就绘制完成,而要拟合出关系直线.则可以用Excel的图表菜单的添加趋势线子菜单即可。
⑧选中所作的图表,在工具栏单击“图表”中的“添加趋势线”。在弹出的对话框选项中“类型”选“线性(L)”;“选项”选中“显示公式”和“显示R平方值”的复选框,便可得到拟合线方程和相关系数的平方。所得结果如图3所示,拟合线方程为y:o.659)【+0.002,相关系数的平方R2-0.998。在散点图和趋势线绘制好后,只要改变数据源内的数据。则散点图和趋势线也跟着变化。
y=0.659x+0.∞2
∑脚(1)
即刚体转动角加速度与刚体受合外力矩成正比.J为转动惯量。设刚体的摩擦力矩为M7,h、t、a分为砝码下落的高度和下落时间及下落加速度,r为绕线塔轮半径.则刚体转动定律数学表达式:
m(g_。)r-膨可》
,懈—肘可!=:;}(3)
一
.(2)
图2
实验装置图
0.0400.0350.0300.0250.0200.0150.0lO0.005
g为重力加速度,在实际实验过程中a<<g,则(2)可简化成:
n。
保持r、hJ不变,改变m测出相应的砝码下落时间t。则m与t之间关系为:
m:型鞲+塑一=b与把(4)r
gn‘
gr
如果在直角坐标系中作m一产图,且数据点分布在一条直线上.则验证(1)式成立,即验证了刚体转动定律。由斜率b和截距a可以求出转动惯量J和摩擦力矩M7。
(1)运用Excel中的“函数”进行计算
Excel有许多函数,其中线性回归拟合线方程的斜率函数SLOPE、
0.0∞
0.0∞
0.010
0.020
0.030
0.040
0.050
0.060
图3
4.结语
m—P关系图
线性回归拟合线方程的截距函数IM陋RCE盯以及相关系数函数
CORREL,可用来确定线性方程忙6石+o的口、6两个系数和计算相关系数R以判别线性回归是否合理。BlO单元格中输入“=SLOPEfA3:A9.
用手工处理物理实验数据,误差较大,且计算、作图步骤烦琐.很容易出错,且同一组实验数据,不同的实验者数据处理的结果还不一样;而用计算机编程处理物理实验数据,误差较小.但需繁杂的编程过程,要求使用人员具备一定的计算机语言能力及数理统计知识。因此.寻找一种更方便、更快捷、更可靠的数据处理方法是有意义的。
笔者在运用Excd进行数据处理及数据分析时,针对不同的实验先制作单个实验的处理模板或编写宏.对大量数据进行分析时只需进行复制、批量处理。这样处理的速度及可靠性是显而易见的。同时.大量的实验储存数据也有助于对实验仪器稳定性及实验方法可靠性的分析。Excel处理方法所得实验误差小。而且对同一组数据结果是唯一的,因此Excel可以替代手工对实验数据进行的数据处理.该方法操作非常简便,容易掌握,无须进行烦琐的编程,随着电脑和Excel软件
F3:聃)”,DlO单元格中输入“=IN7瓶RCEPT(A3:A9,F3:F9)”,F10单元格
中输入“=CORIu见(F3:F9,A3:A9)”,即可求出斜率b=o.659、截距a=O.002以及相关系数R=0.999,R值接近l。说明数据点密集地分布在所求得的直线的近旁,故用线性函数进行回归是合适的.即m与tt线性关系成立。并由斜率b和截距a可以求出转动惯量J和摩擦力矩M7。见表2
表2验证刚体转动定律数据表
A
l23456789lO
B
C
D
E
F
验证刚体转动定律
的普及,本方法具备了推广和应用的可能。l黔
l(s)
14.25‘9.007.106.025.474.854.43
m(kg)
0.0(巧0.0100.015O.020O.0250.0300.035
tl(B)
14.288.9l
t2(s)
14.259.037.125.975.504.784.42
b(B)
14.229.067.096.095.384.894.340.002
产(s’
O.005O.012O.020O.0280.033O.043
【参考文献】
[1]杨述武.普通物理实验(一、力学及热学部分1.|E京:高等教育出版社.2000.10.
[2]马哲.中文Excel2000中级教程.北京:机械工业出版社.1999.134—135.
7.∞
6.005.534.884.530.659
[3]朱俊孔,张山彪等.普通物理实验脐南:山东大学出版社.2001.17.
[4]林抒,龚镇友.普通物理实验.北京:高等教育出版社.1981.96.
作者简介:王劲松,1968年出生,男.汉,讲师,潍坊学院物理与电子科学系。
[责任编辑:田瑞▲】
0.051
斜率b截距a
相关系数R
O.鲫19
万方数据
用Excel实现实验数据的计算机处理
作者:作者单位:刊名:英文刊名:年,卷(期):被引用次数:
王劲松, wang jing-song
潍坊学院物理与电子科学系,山东,潍坊,261061科技信息
SCIENCE & TECHNOLOGY INFORMATION2008(23)2次
参考文献(4条)
1. 杨述武 普通物理实验(一、力学及热学部分) 20002. 马哲 中文Excel2000中级教程 19993. 朱俊孔;张山彪 普通物理实验 20014. 林抒;龚镇友 普通物理实验 1981
本文读者也读过(10条)
1. 谢同祥. 汤云兴 EXcel在物理实验数据处理环节中的应用[期刊论文]-九江师专学报2004,23(5)
2. 兰燕娜. Lan Yanna Excel在大学物理实验数据处理中的应用研究[期刊论文]-赤峰学院学报(自然科学版)2010,26(12)
3. 张佩英. ZHANG pei-ying 自编函数处理Excel中复杂数据[期刊论文]-实验室研究与探索2007,26(8)4. 王波 Excel在误差计算及实验数据处理中的应用[期刊论文]-大学物理实验2003,16(1)
5. 姚进斌. 徐业. 辛瑜. 王亮 浅析Excel在物理实验数据处理中的应用[期刊论文]-科技信息2010(30)
6. 陈玉林. 丁留贯. 张磊. Chen Yulin. Ding Liuguan. Zhang Lei 基于Excel的物理实验数据处理[期刊论文]-大学物理实验2007,20(2)
7. 王建华. WANG Jian-hua Excel处理物理实验数据的应用研究[期刊论文]-忻州师范学院学报2006,22(2)8. 汪建军. WANG Jian-jun 浅析Excel软件在大学物理实验数据处理中的应用技巧[期刊论文]-江西科学2005,23(5)9. 高海林. GAO Hai-lin Excel在误差计算及实验数据处理中的应用[期刊论文]-实验室科学2007(3)10. 田红梅. TIAN Hongmei Excel在物理实验数据处理中的应用研究[期刊论文]-深圳信息职业技术学院学报2008,6(2)
引证文献(2条)
1. 吴姜 岩石变形特性参数测数数据处理在Excel中的应用[期刊论文]-管理学家 2012(3)2. 张若林. 刘民 阻抗测量中的一种复数计算工具[期刊论文]-宇航计测技术 2013(1)
引用本文格式:王劲松. wang jing-song 用Excel实现实验数据的计算机处理[期刊论文]-科技信息 2008(23)