Excel表格在物理实验数据处理中的应用

罗小成， 蔡 雨， 王晨霖， 艾正菊， 冯 薇， 陈虎学鹰， 陈 湘

(内江师范学院 物理与电子信息工程学院， 四川 内江 641100)

0 引言

在物理实验中，以误差理论为基础的数据处理是实验结果分析的重要依据.一般来说，越是复杂的实验，对其数据处理的过程就越复杂.如果通过手工对其进行计算或是作图处理，非常容易出错.Excel作为一种最常见的数据分析表格，自身携带有函数、格式化操作、图表自动生成、宏语言、数据管理等功能；同时，Excel同Origin软件具有数据对接功能.相比而言，Origin仅需根据菜单栏命令即可得出所需实验数据处理结果，无须学习者去深入理解数据处理的相关知识，因此Origin更适合于高级阶段数据处理.而Excel在应用过程中则更注重理论公式的计算机语言编写，有助于初学者深入理解公式的物理意义，并为将来的高阶学习打下基础.目前，关于Excel在物理实验数据处理中的应用已有大量研究[1-8]，但这些研究基本都是对某一个具体的物理实验数据处理方法进行研究，并不全面和系统.

本文结合实验误差理论，对直接测量、间接测量数据处理中的诸多参数进行计算，以及最小二乘法、逐差法等常见的数据处理方法进行程序编写，以及在物理学专业实验和非物理专业的大学物理实验中，对学生和教师处理数据具有参考价值.学生在实验中获得原始数据后，通过这些程序可以立刻知道实验结果的好坏，并对实验方法、操作、仪器、过程等做出相应的分析，进而提升实验的质量，提高课程的效率.

1 常用函数用法

在用Excel对物理实验数据进行处理时需要编写相关公式的计算机程序，这要求研究者对于相应知识点有透彻理解，也需要研究者对Excel常用函数的用法熟练掌握.表1列出一些常用函数的用法及其功能.

2 直接测量与间接测量的数据处理

2.1 直接测量数据的重要参数计算

表2中计算结果，即C列的具体计算表达式及注解如表3所示，其中*号部分为注解.表3中m的不确定度计算程序及注解如表4所示.

表1 Excel用于物理实验数据分析函数及函数注解

表2 Excel中质量m多次直接测量原始数据与计算结果

表3 质量m多次直接测量计算过程注解

表4 质量m四舍六入五凑偶的程序

2.2 直接测量的B类不确定度计算

测量m，用到天平、砝码、游码等，那么它们的最大示值误差、误差极限系数等参数，以及因仪器而定的置信概率和置信系数等也需根据实际情况而定，这些参数需手动输入.

表5是Excel表中关于测量所使用的仪器及相关参数，以及所计算的B类不确定度，计算结果分别放在I列.对应计算公式如F1*G1/H1.

表5 Excel中质量m直接测量中各仪器的不确定度计算

2.3 基于多个直接测量结果的间接测量计算

表6 Excel中密度ρ间接测量的计算及注解

3 线性函数的最小二乘法处理

3.1 相关参数的计算

(1)

将式(1)看成关于a和b的函数，然后求a和b的一阶导数，并令其等于零，从而建立两个等式，然后解出a和b的表达式，具体如下：

(2)

(3)

根据误差传递的理论，可以求出a和b的不确定度，具体表述如下：

(4)

(5)

式中γ为相关系数.

以上讨论的是在已知函数形式下的情形，通过实验测量的数据求出回归方程.因此，在确定函数形式后，用回归法处理数据的结果是唯一的，不会因人而异.可见回归法处理问题的关键是函数形式的选择.但是当函数形式不明确时，需要用测量值来探索经验公式，这时就只能通过数据趋势来推测.对于同一组数据，不同的人会采取不一样的函数形式，得出不同的结果.为了判断众多研究者的实验结果是否合理，就有了用相关系数r来评判的方法.对一元线性回归，r定义为：

(6)

相关系数r的数值大小反映了相关程度的好坏，即所得结果的合理性.|r|的值介于0到1之间，|r|的值越接近于1，说明实验数据是紧密聚集在所求直线的附近.x、y之间存在线性关系，用线性函数进行回归比较合理.反之，则说明实验数据远离所求直线且分散，x、y之间不存在线性关系，需要重新用其他函数试探.表7给出了Excel中线性函数最小二乘法拟合计算实例，其中A列数据为x，B类数据为y，D列为计算参数，E列为计算结果，F列为有效位数，G列为修约结果，H列为实验结果.表7中E、F、G列的对应计算程序如表8-10所示.同时，表11给出了截距平均值计算程序注解.

表7 线性函数y=a+bx最小二乘法拟合情况

表8 线性函数最小二乘法各参数计算程序(表7的E列)

续表

表9 线性函数最小二乘法各参数保留有效位数计算程序(表7的F列)

表10 线性函数最小二乘法各参数修约计算程序(表7的G列)

表11 线性函数最小二乘法中截距平均值计算程序注解

3.2 Excel计算与Origin软件拟合结果对比

将表7中的原始数据导入Origin数据表中，采用散点形式作图后，对图形进行“Linear fitting”命令，结果如图1所示.

采用Excel和Origin软件计算的结果比较,结果如表12所示.由表12可见,采用两种方法计算的斜率、截距、相关系数是一致的，且Excel表格中可根据有效位数保留规则来表示计算结果.

表12 Origin拟合结果与Excel计算结果比较

图1 Origin软件中线性拟合图示

4 Excel应用与物理实验误差分析中的常见问题处理

4.1 最后一位为零的数值显示处理

在运算中有可能遇到最后一个数字是0的情况，在Excel中将不会显示，如某单元格(D4)中数字最后一位的0没有显示.此时可以对该数据进行完整显示,只需在另外的单元格中输入如下公式即可.

=IF(IF(ISERROR(FIND(".",ABS(D4))),LEN(ABS(D4)),LEN(ABS(D4))-1)

4.2 程序的保护和原始数据的输入

在输入原始数据时，难免因操作失误将数据输入到非法位置，因此需对非原始数据输入区域进行限定设置.在Excel中,可以通过菜单栏中的“审阅→保护工作表”来实现对工作表的输入保护，然后进入“审阅”→“更改”模块，点击“允许用户编辑”会出现图示(图2).

图2 原始数据输入区域设置和非输入区域保护操作界面

点击“新建”，并将鼠标置于图(2)“引用单元格”处，在Excel中选择要输入数据的区域，如“F10:F16”，点击确定，则F10:F16区域就是可输入区域.同理,要设置多个可输入区域，则重复上述操作即可.反之,对非输入区域的程序进行修改，点击“审阅”中“更改”模块的“撤销工作表保护”即可.

5 结论

利用Excel自带函数，如STDEV、SIGN、REPT、ROUND、SUMPRODUCT、INDIRECT等，编写了多次直接测量和间接测量物理量的误差参数计算程序，如数据个数统计、平均值、A类不确定度、B类不确定度、合成不确定度、对数法误差传递系数，以及四舍六入五取偶的修约程序等.对最小二乘法、逐差法等相关参数进行了计算程序编写.同时对界面进行优化，使实验者输入原始数据后，就可以直接获得实验数据相关结果，提高实验效率.