数据处理软件在误差理论教学中的运用

陈国强 黄俊杰

河南理工大学 河南焦作 454003

数据处理软件在误差理论教学中的运用

陈国强 黄俊杰

河南理工大学 河南焦作 454003

数据和计算量大是误差理论与数据处理课程教学中存在的主要问题之一。微软的Excel具有易学易用、数据处理功能丰富的特点，引入Excel可以提高课程教学的质量。本文简述了用Excel进行数据处理时常用的功能，并给出了3个课堂实例及相关的公式与操作方法。实例表明，用Excel辅助误差理论教学使学生从繁杂的计算中解脱出来，将注意力集中于数据处理的理论与方法。

误差理论；数据处理；Excel；课程教学

数据处理、曲线绘制、表格制作等是工程技术人员和科研工作者进行科学研究和学术交流必备的技能。随着计算机技术和数据处理软件的飞速发展，数据处理和绘图手段发生了巨大的变化，手工处理已经渐渐被计算机所代替。计算机进行数据处理具有速度快、精度高等优点，绝大部分学术期刊都要求投稿时采用计算机绘图。目前，数据处理软件很多，常用的有Matlab、Excel、Original、Maple等，这些软件各有优缺点。其中Excel作为MS Office的一部分，具有操作简单、容易掌握、不需要编程等优点，被广泛采用。与数据处理技能相对应的高等院校课程是误差理论与数据处理及相关课程。由于课程涉及的数据及计算量都较大，传统的黑板板书教学面临的弊端就愈显突出。计算机技术与数据处理软件的发展、多媒体教学的运用提高了课程的教学效果，也为数据处理软件在课程中的运用开辟了广阔的天地。基于此，本文以几个实例为基础，给出了Excel在数据处理教学中的应用方法。

一、相关的操作

1.公式

Excel具有强大的公式计算功能，一个公式可以是简单的两个数加减运算、也可以是包含关系等运算的复杂运算。运算符可以是：算术运算符[+(加)、－(减)、×(乘)、÷(除)]、关系运算符[=(等于)、＞(大于)、＜(小于)、≥ (大于等于)、≤(小于等于)、＜＞(不等于)]和字符运算符“&”。在数据处理中，很多复杂的计算都可以用一个或若干个公式完成。数据处理中很多计算都可以直接采用公式完成，而不需要编程。如图1中C、D两列求残余误差和残差平方都是简单的公式，图6中最小二乘法进行线性回归则为多个公式的组合运用。

2.函数

一些常用的或较为繁琐的公式都有相应的函数。Excel中提供了大量的函数，在误差数据处理中常用的函数为数学与三角函数、统计函数、逻辑函数等。如图1中B17和D18分别调用了求平均值和开平方的函数。

3.单元格的引用

单元格的引用是把单元格的地址作为参数。如图1中C列求残余误差时要用到算术平均值B17。单元格C2～C16公式的形式都是一样，都是测得值减去算术平均值B17，为了数据处理时使用单元格填充公式，对算术平均值B17的引用需要使用绝对引用$B$17。如果采用相对引用，则公式填充时，单元格的地址就会发生变化。如果将绝对引用$B$17改为混合引用B$17，则效果与绝对引用相同，这是因为在C列进行公式填充时，B、C两列的相对位置没有发生变化。

二、课堂教学实例

1.粗大误差的判别

粗大误差的判别是数据处理中经常用到的，粗大误差判别常用的准则有莱以特准则、罗曼诺夫斯基淮则、格罗布斯准则和狄克松准则。

下面以文献[5]P44的表2-11中数据为例用莱以特准则判别粗大误差，处理的过程及相关公式如图1所示。在处理时，用到了Excel中最基本的数据处理功能：求和、平均值、开平方。本例中直接用公式进行计算，也可以调用Excel自带的函数。在本例中，绝对引用和相对引用要结合使用，如求残余误差时，平均值的引用必须采用绝对引用，而当前数值采用的是相对引用，这为复制公式提供了便利。根据原始数据计算得到的3倍标准差为0.0981，则第8个数据20.30含有粗大误差，剔除之后，则剩下的数据不含粗大误差。

2.随机误差分布规律的模拟

Crystal Ball是一款基于Excel的仿真软件包，可以进行随机误差合成、分配等的仿真。在误差理论教学中，所涉及的随机误差大都服从正态分布，但是还存在着诸如均匀分布、三角分布等形式。这些不同的分布之间可能存在着某些联系，通过软件仿真可以加深学生对它们之间关系的了解与掌握。Crystal Ball中提供的随机分布如图2所示。

“两个服从相同的均匀分布的相互独立的随机变量之和仍为随机变量，且服从三角分布”。如x和y是相互独立、在[-0.1,0.1]上服从均匀分布的两随机误差，则z=x+y服从三角分布。在EXCEL/Crystal Ball试用版中设定“Maximum Number of Trials”为60000、“Sampling Methods”为Monte Carlo，某次仿真结果如图3所示。从仿真结果可以看出，z在[-0.2,0.2]上服从三角分布，标准差为0.0814,均值为0.0004，这与理论计算的结果基本一致。

图3 两个同分布的均匀分布合成仿真结果

“对两个误差限为不相等的均匀分布随机误差求和时,则其和的分布不再是三角分布而是梯形分布”。如x和y是相互独立、分别在[-0.1,0.1]和[-0.3，0.3]为均匀分布的两随机误差,则z=x+y服从梯形分布。与上面的设置相同,则仿真结果如图4所示。

对于4个相互独立服从均匀分布的随机误差，其和接近正态分布，图5给出的是相互独立、在[-0.1,0.1]上服从均匀分布的4个随机误差之和的仿真结果。

图5 四个同分布的均匀分布合成仿真结果

3.最小二乘法的求解

回归方程为：

则矩阵形式的结果为：

回归平方和为：

残余平方和为：

离差平方和为：

式(6)和(7)本质上是一样的。参考文献[1～3]讨论了用Excel计算回归方程的方法并给出了应用实例。[1]给出了在Excel中用函数、图表向导和数据分析三种不同的方法进行最小二乘法计算；[2]给出了用工作表函数、分析工具及规划求解工具进行多元线性回归分析的方法。其中讨论的方法不用编写程序、不涉及最小二乘法的具体计算公式，便于实际运用。但是在课程教学中,要让学生掌握最小二乘法的原理和计算过程,就需要运用式(6)～(10)直接计算。

在直线y=3x+2附近随机生成一系列数据，并进行回归分析。生成的10个数据如图6的B、C两列所示。采用的是公式(6)、(8)～(10)进行计算，Excel中相关的公式如图6中标注所示。

图6 最小二乘法的求解过程

图7 回归对话框设置

在Excel中如果采用数据分析工具中的回归进行求解，对话框设置如图7所示，则求解结果如图8所示，与图6中的结果一致。

三、结束语

1.公式、函数、单元格引用是用Excel进行误差与数据处理时最常用的功能

特别是单元格引用的方式影响到数据处理的效率和灵活程度，巧妙地使用混合引用和单元格公式填充功能，可以实现诸如循环等复杂的数据处理功能。

2.本文以课堂教学中3个实例简述了Excel在误差理论与数据处理教学中的应用

从最小二乘法求解的实例可以看出，Excel中已经包含了很多数据处理的功能，用户可以直接进行调用，只需简单的设置即可。随着版本的升级，软件的功能将更加强大。但是，课堂教学中不但要让学生学会怎么调用，而且要让学生学会怎么运用基本的公式进行计算，让学生掌握数据处理的理论和方法。

3.任何软件都不是万能的，Excel也如此

对于一些复杂的、经常使用、重复性的计算可以用VBA进行二次开发。在课程教学中，可以设置一些这方面的实验，培养学生编程进行数据处理的能力。

4.在课程教学中，可以将数据处理过程转化为Excel的函数、公式进行求解

这可以使学生将注意力集中于数据处理的理论、方法，而不被繁杂的数据和计算所困扰，有利于提高教学效果。

[1]唐曙光.基于Excel的实验数据最小二乘法计算探讨[J].大学物理实验,2003，16(4)：41～43

[2]王中华，山桂云，林英武.Excel多元线性回归及在化学中的应用[J].计算机与应用化学，2005，22(9)：817～820

[3]赵延龄，温晓光，范福海.用Excel求取化工数据组回归方程[J].计算机与应用化学，2004，21(4)：650～654

[4]彭小平，颜清.萃取计算的Excel VBA图解方法[J].计算机与应用化学，2008，25(11)：1412～1416

[5]费业泰.误差理论与数据处理[M].北京：机械工业出版社，2000

[6]李金海.误差理论与测量不确定度评定[M].北京：中国计量出版社，2003

[7]陈国强，尚显光，赵俊伟.含误差的数据序列生成方法及实现[J].河南理工大学学报(自然科学版)，2008，27(5)：560～564

Application of data processing software in error theory teaching

Chen Guoqiang, Huang Junjie
He’nan polytechnic university, Jiaozuo,454003, China

Large amount of data and calculation quantity is one of the key problems in error theory and data processing teaching. With excellent property of being easy to learn and use, microsoft excel has powerful data processing function. Introducing Excel in teaching can improve the teaching effect significantly. The paper introduces functions that can be used frequently in data processing. The procedures and operation methods of three typical examples are presented. The examples show that excel-aided education can make students pay more attention to the fundamental and method of the data processing instead of complicated data and computation.

error theory；data processing； Excel；course teaching

2009-10-18

陈国强，硕士，讲师。