陳國(guó)強(qiáng) 黃俊杰

河南理工大學(xué) 河南焦作 454003

數(shù)據(jù)處理軟件在誤差理論教學(xué)中的運(yùn)用

陳國(guó)強(qiáng) 黃俊杰

河南理工大學(xué) 河南焦作 454003

數(shù)據(jù)和計(jì)算量大是誤差理論與數(shù)據(jù)處理課程教學(xué)中存在的主要問(wèn)題之一。微軟的Excel具有易學(xué)易用、數(shù)據(jù)處理功能豐富的特點(diǎn)，引入Excel可以提高課程教學(xué)的質(zhì)量。本文簡(jiǎn)述了用Excel進(jìn)行數(shù)據(jù)處理時(shí)常用的功能，并給出了3個(gè)課堂實(shí)例及相關(guān)的公式與操作方法。實(shí)例表明，用Excel輔助誤差理論教學(xué)使學(xué)生從繁雜的計(jì)算中解脫出來(lái)，將注意力集中于數(shù)據(jù)處理的理論與方法。

誤差理論；數(shù)據(jù)處理；Excel；課程教學(xué)

數(shù)據(jù)處理、曲線繪制、表格制作等是工程技術(shù)人員和科研工作者進(jìn)行科學(xué)研究和學(xué)術(shù)交流必備的技能。隨著計(jì)算機(jī)技術(shù)和數(shù)據(jù)處理軟件的飛速發(fā)展，數(shù)據(jù)處理和繪圖手段發(fā)生了巨大的變化，手工處理已經(jīng)漸漸被計(jì)算機(jī)所代替。計(jì)算機(jī)進(jìn)行數(shù)據(jù)處理具有速度快、精度高等優(yōu)點(diǎn)，絕大部分學(xué)術(shù)期刊都要求投稿時(shí)采用計(jì)算機(jī)繪圖。目前，數(shù)據(jù)處理軟件很多，常用的有Matlab、Excel、Original、Maple等，這些軟件各有優(yōu)缺點(diǎn)。其中Excel作為MS Office的一部分，具有操作簡(jiǎn)單、容易掌握、不需要編程等優(yōu)點(diǎn)，被廣泛采用。與數(shù)據(jù)處理技能相對(duì)應(yīng)的高等院校課程是誤差理論與數(shù)據(jù)處理及相關(guān)課程。由于課程涉及的數(shù)據(jù)及計(jì)算量都較大，傳統(tǒng)的黑板板書(shū)教學(xué)面臨的弊端就愈顯突出。計(jì)算機(jī)技術(shù)與數(shù)據(jù)處理軟件的發(fā)展、多媒體教學(xué)的運(yùn)用提高了課程的教學(xué)效果，也為數(shù)據(jù)處理軟件在課程中的運(yùn)用開(kāi)辟了廣闊的天地?；诖?，本文以幾個(gè)實(shí)例為基礎(chǔ)，給出了Excel在數(shù)據(jù)處理教學(xué)中的應(yīng)用方法。

一、相關(guān)的操作

1.公式

Excel具有強(qiáng)大的公式計(jì)算功能，一個(gè)公式可以是簡(jiǎn)單的兩個(gè)數(shù)加減運(yùn)算、也可以是包含關(guān)系等運(yùn)算的復(fù)雜運(yùn)算。運(yùn)算符可以是：算術(shù)運(yùn)算符[+(加)、－(減)、×(乘)、÷(除)]、關(guān)系運(yùn)算符[=(等于)、＞(大于)、＜(小于)、≥ (大于等于)、≤(小于等于)、＜＞(不等于)]和字符運(yùn)算符“&”。在數(shù)據(jù)處理中，很多復(fù)雜的計(jì)算都可以用一個(gè)或若干個(gè)公式完成。數(shù)據(jù)處理中很多計(jì)算都可以直接采用公式完成，而不需要編程。如圖1中C、D兩列求殘余誤差和殘差平方都是簡(jiǎn)單的公式，圖6中最小二乘法進(jìn)行線性回歸則為多個(gè)公式的組合運(yùn)用。

2.函數(shù)

一些常用的或較為繁瑣的公式都有相應(yīng)的函數(shù)。Excel中提供了大量的函數(shù)，在誤差數(shù)據(jù)處理中常用的函數(shù)為數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)函數(shù)、邏輯函數(shù)等。如圖1中B17和D18分別調(diào)用了求平均值和開(kāi)平方的函數(shù)。

3.單元格的引用

單元格的引用是把單元格的地址作為參數(shù)。如圖1中C列求殘余誤差時(shí)要用到算術(shù)平均值B17。單元格C2～C16公式的形式都是一樣，都是測(cè)得值減去算術(shù)平均值B17，為了數(shù)據(jù)處理時(shí)使用單元格填充公式，對(duì)算術(shù)平均值B17的引用需要使用絕對(duì)引用$B$17。如果采用相對(duì)引用，則公式填充時(shí)，單元格的地址就會(huì)發(fā)生變化。如果將絕對(duì)引用$B$17改為混合引用B$17，則效果與絕對(duì)引用相同，這是因?yàn)樵贑列進(jìn)行公式填充時(shí)，B、C兩列的相對(duì)位置沒(méi)有發(fā)生變化。

二、課堂教學(xué)實(shí)例

1.粗大誤差的判別

粗大誤差的判別是數(shù)據(jù)處理中經(jīng)常用到的，粗大誤差判別常用的準(zhǔn)則有萊以特準(zhǔn)則、羅曼諾夫斯基淮則、格羅布斯準(zhǔn)則和狄克松準(zhǔn)則。

下面以文獻(xiàn)[5]P44的表2-11中數(shù)據(jù)為例用萊以特準(zhǔn)則判別粗大誤差，處理的過(guò)程及相關(guān)公式如圖1所示。在處理時(shí)，用到了Excel中最基本的數(shù)據(jù)處理功能：求和、平均值、開(kāi)平方。本例中直接用公式進(jìn)行計(jì)算，也可以調(diào)用Excel自帶的函數(shù)。在本例中，絕對(duì)引用和相對(duì)引用要結(jié)合使用，如求殘余誤差時(shí)，平均值的引用必須采用絕對(duì)引用，而當(dāng)前數(shù)值采用的是相對(duì)引用，這為復(fù)制公式提供了便利。根據(jù)原始數(shù)據(jù)計(jì)算得到的3倍標(biāo)準(zhǔn)差為0.0981，則第8個(gè)數(shù)據(jù)20.30含有粗大誤差，剔除之后，則剩下的數(shù)據(jù)不含粗大誤差。

2.隨機(jī)誤差分布規(guī)律的模擬

Crystal Ball是一款基于Excel的仿真軟件包，可以進(jìn)行隨機(jī)誤差合成、分配等的仿真。在誤差理論教學(xué)中，所涉及的隨機(jī)誤差大都服從正態(tài)分布，但是還存在著諸如均勻分布、三角分布等形式。這些不同的分布之間可能存在著某些聯(lián)系，通過(guò)軟件仿真可以加深學(xué)生對(duì)它們之間關(guān)系的了解與掌握。Crystal Ball中提供的隨機(jī)分布如圖2所示。

“兩個(gè)服從相同的均勻分布的相互獨(dú)立的隨機(jī)變量之和仍為隨機(jī)變量，且服從三角分布”。如x和y是相互獨(dú)立、在[-0.1,0.1]上服從均勻分布的兩隨機(jī)誤差，則z=x+y服從三角分布。在EXCEL/Crystal Ball試用版中設(shè)定“Maximum Number of Trials”為60000、“Sampling Methods”為Monte Carlo，某次仿真結(jié)果如圖3所示。從仿真結(jié)果可以看出，z在[-0.2,0.2]上服從三角分布，標(biāo)準(zhǔn)差為0.0814,均值為0.0004，這與理論計(jì)算的結(jié)果基本一致。

圖3 兩個(gè)同分布的均勻分布合成仿真結(jié)果

“對(duì)兩個(gè)誤差限為不相等的均勻分布隨機(jī)誤差求和時(shí),則其和的分布不再是三角分布而是梯形分布”。如x和y是相互獨(dú)立、分別在[-0.1,0.1]和[-0.3，0.3]為均勻分布的兩隨機(jī)誤差,則z=x+y服從梯形分布。與上面的設(shè)置相同,則仿真結(jié)果如圖4所示。

對(duì)于4個(gè)相互獨(dú)立服從均勻分布的隨機(jī)誤差，其和接近正態(tài)分布，圖5給出的是相互獨(dú)立、在[-0.1,0.1]上服從均勻分布的4個(gè)隨機(jī)誤差之和的仿真結(jié)果。

圖5 四個(gè)同分布的均勻分布合成仿真結(jié)果

3.最小二乘法的求解

回歸方程為：

則矩陣形式的結(jié)果為：

回歸平方和為：

殘余平方和為：

離差平方和為：

式(6)和(7)本質(zhì)上是一樣的。參考文獻(xiàn)[1～3]討論了用Excel計(jì)算回歸方程的方法并給出了應(yīng)用實(shí)例。[1]給出了在Excel中用函數(shù)、圖表向?qū)Ш蛿?shù)據(jù)分析三種不同的方法進(jìn)行最小二乘法計(jì)算；[2]給出了用工作表函數(shù)、分析工具及規(guī)劃求解工具進(jìn)行多元線性回歸分析的方法。其中討論的方法不用編寫(xiě)程序、不涉及最小二乘法的具體計(jì)算公式，便于實(shí)際運(yùn)用。但是在課程教學(xué)中,要讓學(xué)生掌握最小二乘法的原理和計(jì)算過(guò)程,就需要運(yùn)用式(6)～(10)直接計(jì)算。

在直線y=3x+2附近隨機(jī)生成一系列數(shù)據(jù)，并進(jìn)行回歸分析。生成的10個(gè)數(shù)據(jù)如圖6的B、C兩列所示。采用的是公式(6)、(8)～(10)進(jìn)行計(jì)算，Excel中相關(guān)的公式如圖6中標(biāo)注所示。

圖6 最小二乘法的求解過(guò)程

圖7 回歸對(duì)話(huà)框設(shè)置

在Excel中如果采用數(shù)據(jù)分析工具中的回歸進(jìn)行求解，對(duì)話(huà)框設(shè)置如圖7所示，則求解結(jié)果如圖8所示，與圖6中的結(jié)果一致。

三、結(jié)束語(yǔ)

1.公式、函數(shù)、單元格引用是用Excel進(jìn)行誤差與數(shù)據(jù)處理時(shí)最常用的功能

特別是單元格引用的方式影響到數(shù)據(jù)處理的效率和靈活程度，巧妙地使用混合引用和單元格公式填充功能，可以實(shí)現(xiàn)諸如循環(huán)等復(fù)雜的數(shù)據(jù)處理功能。

2.本文以課堂教學(xué)中3個(gè)實(shí)例簡(jiǎn)述了Excel在誤差理論與數(shù)據(jù)處理教學(xué)中的應(yīng)用

從最小二乘法求解的實(shí)例可以看出，Excel中已經(jīng)包含了很多數(shù)據(jù)處理的功能，用戶(hù)可以直接進(jìn)行調(diào)用，只需簡(jiǎn)單的設(shè)置即可。隨著版本的升級(jí)，軟件的功能將更加強(qiáng)大。但是，課堂教學(xué)中不但要讓學(xué)生學(xué)會(huì)怎么調(diào)用，而且要讓學(xué)生學(xué)會(huì)怎么運(yùn)用基本的公式進(jìn)行計(jì)算，讓學(xué)生掌握數(shù)據(jù)處理的理論和方法。

3.任何軟件都不是萬(wàn)能的，Excel也如此

對(duì)于一些復(fù)雜的、經(jīng)常使用、重復(fù)性的計(jì)算可以用VBA進(jìn)行二次開(kāi)發(fā)。在課程教學(xué)中，可以設(shè)置一些這方面的實(shí)驗(yàn)，培養(yǎng)學(xué)生編程進(jìn)行數(shù)據(jù)處理的能力。

4.在課程教學(xué)中，可以將數(shù)據(jù)處理過(guò)程轉(zhuǎn)化為Excel的函數(shù)、公式進(jìn)行求解

這可以使學(xué)生將注意力集中于數(shù)據(jù)處理的理論、方法，而不被繁雜的數(shù)據(jù)和計(jì)算所困擾，有利于提高教學(xué)效果。

[1]唐曙光.基于Excel的實(shí)驗(yàn)數(shù)據(jù)最小二乘法計(jì)算探討[J].大學(xué)物理實(shí)驗(yàn),2003，16(4)：41～43

[2]王中華，山桂云，林英武.Excel多元線性回歸及在化學(xué)中的應(yīng)用[J].計(jì)算機(jī)與應(yīng)用化學(xué)，2005，22(9)：817～820

[3]趙延齡，溫曉光，范福海.用Excel求取化工數(shù)據(jù)組回歸方程[J].計(jì)算機(jī)與應(yīng)用化學(xué)，2004，21(4)：650～654

[4]彭小平，顏清.萃取計(jì)算的Excel VBA圖解方法[J].計(jì)算機(jī)與應(yīng)用化學(xué)，2008，25(11)：1412～1416

[5]費(fèi)業(yè)泰.誤差理論與數(shù)據(jù)處理[M].北京：機(jī)械工業(yè)出版社，2000

[6]李金海.誤差理論與測(cè)量不確定度評(píng)定[M].北京：中國(guó)計(jì)量出版社，2003

[7]陳國(guó)強(qiáng)，尚顯光，趙俊偉.含誤差的數(shù)據(jù)序列生成方法及實(shí)現(xiàn)[J].河南理工大學(xué)學(xué)報(bào)(自然科學(xué)版)，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

陳國(guó)強(qiáng)，碩士，講師。