羅小成， 蔡 雨， 王晨霖， 艾正菊， 馮 薇， 陳虎學(xué)鷹， 陳 湘

(內(nèi)江師范學(xué)院 物理與電子信息工程學(xué)院， 四川 內(nèi)江 641100)

0 引言

在物理實(shí)驗(yàn)中，以誤差理論為基礎(chǔ)的數(shù)據(jù)處理是實(shí)驗(yàn)結(jié)果分析的重要依據(jù).一般來(lái)說(shuō)，越是復(fù)雜的實(shí)驗(yàn)，對(duì)其數(shù)據(jù)處理的過(guò)程就越復(fù)雜.如果通過(guò)手工對(duì)其進(jìn)行計(jì)算或是作圖處理，非常容易出錯(cuò).Excel作為一種最常見(jiàn)的數(shù)據(jù)分析表格，自身攜帶有函數(shù)、格式化操作、圖表自動(dòng)生成、宏語(yǔ)言、數(shù)據(jù)管理等功能；同時(shí)，Excel同Origin軟件具有數(shù)據(jù)對(duì)接功能.相比而言，Origin僅需根據(jù)菜單欄命令即可得出所需實(shí)驗(yàn)數(shù)據(jù)處理結(jié)果，無(wú)須學(xué)習(xí)者去深入理解數(shù)據(jù)處理的相關(guān)知識(shí)，因此Origin更適合于高級(jí)階段數(shù)據(jù)處理.而Excel在應(yīng)用過(guò)程中則更注重理論公式的計(jì)算機(jī)語(yǔ)言編寫，有助于初學(xué)者深入理解公式的物理意義，并為將來(lái)的高階學(xué)習(xí)打下基礎(chǔ).目前，關(guān)于Excel在物理實(shí)驗(yàn)數(shù)據(jù)處理中的應(yīng)用已有大量研究[1-8]，但這些研究基本都是對(duì)某一個(gè)具體的物理實(shí)驗(yàn)數(shù)據(jù)處理方法進(jìn)行研究，并不全面和系統(tǒng).

本文結(jié)合實(shí)驗(yàn)誤差理論，對(duì)直接測(cè)量、間接測(cè)量數(shù)據(jù)處理中的諸多參數(shù)進(jìn)行計(jì)算，以及最小二乘法、逐差法等常見(jiàn)的數(shù)據(jù)處理方法進(jìn)行程序編寫，以及在物理學(xué)專業(yè)實(shí)驗(yàn)和非物理專業(yè)的大學(xué)物理實(shí)驗(yàn)中，對(duì)學(xué)生和教師處理數(shù)據(jù)具有參考價(jià)值.學(xué)生在實(shí)驗(yàn)中獲得原始數(shù)據(jù)后，通過(guò)這些程序可以立刻知道實(shí)驗(yàn)結(jié)果的好壞，并對(duì)實(shí)驗(yàn)方法、操作、儀器、過(guò)程等做出相應(yīng)的分析，進(jìn)而提升實(shí)驗(yàn)的質(zhì)量，提高課程的效率.

1 常用函數(shù)用法

在用Excel對(duì)物理實(shí)驗(yàn)數(shù)據(jù)進(jìn)行處理時(shí)需要編寫相關(guān)公式的計(jì)算機(jī)程序，這要求研究者對(duì)于相應(yīng)知識(shí)點(diǎn)有透徹理解，也需要研究者對(duì)Excel常用函數(shù)的用法熟練掌握.表1列出一些常用函數(shù)的用法及其功能.

2 直接測(cè)量與間接測(cè)量的數(shù)據(jù)處理

2.1 直接測(cè)量數(shù)據(jù)的重要參數(shù)計(jì)算

表2中計(jì)算結(jié)果，即C列的具體計(jì)算表達(dá)式及注解如表3所示，其中*號(hào)部分為注解.表3中m的不確定度計(jì)算程序及注解如表4所示.

表1 Excel用于物理實(shí)驗(yàn)數(shù)據(jù)分析函數(shù)及函數(shù)注解

表2 Excel中質(zhì)量m多次直接測(cè)量原始數(shù)據(jù)與計(jì)算結(jié)果

表3 質(zhì)量m多次直接測(cè)量計(jì)算過(guò)程注解

表4 質(zhì)量m四舍六入五湊偶的程序

2.2 直接測(cè)量的B類不確定度計(jì)算

測(cè)量m，用到天平、砝碼、游碼等，那么它們的最大示值誤差、誤差極限系數(shù)等參數(shù)，以及因儀器而定的置信概率和置信系數(shù)等也需根據(jù)實(shí)際情況而定，這些參數(shù)需手動(dòng)輸入.

表5是Excel表中關(guān)于測(cè)量所使用的儀器及相關(guān)參數(shù)，以及所計(jì)算的B類不確定度，計(jì)算結(jié)果分別放在I列.對(duì)應(yīng)計(jì)算公式如F1*G1/H1.

表5 Excel中質(zhì)量m直接測(cè)量中各儀器的不確定度計(jì)算

2.3 基于多個(gè)直接測(cè)量結(jié)果的間接測(cè)量計(jì)算

表6 Excel中密度ρ間接測(cè)量的計(jì)算及注解

3 線性函數(shù)的最小二乘法處理

3.1 相關(guān)參數(shù)的計(jì)算

(1)

將式(1)看成關(guān)于a和b的函數(shù)，然后求a和b的一階導(dǎo)數(shù)，并令其等于零，從而建立兩個(gè)等式，然后解出a和b的表達(dá)式，具體如下：

(2)

(3)

根據(jù)誤差傳遞的理論，可以求出a和b的不確定度，具體表述如下：

(4)

(5)

式中γ為相關(guān)系數(shù).

以上討論的是在已知函數(shù)形式下的情形，通過(guò)實(shí)驗(yàn)測(cè)量的數(shù)據(jù)求出回歸方程.因此，在確定函數(shù)形式后，用回歸法處理數(shù)據(jù)的結(jié)果是唯一的，不會(huì)因人而異.可見(jiàn)回歸法處理問(wèn)題的關(guān)鍵是函數(shù)形式的選擇.但是當(dāng)函數(shù)形式不明確時(shí)，需要用測(cè)量值來(lái)探索經(jīng)驗(yàn)公式，這時(shí)就只能通過(guò)數(shù)據(jù)趨勢(shì)來(lái)推測(cè).對(duì)于同一組數(shù)據(jù)，不同的人會(huì)采取不一樣的函數(shù)形式，得出不同的結(jié)果.為了判斷眾多研究者的實(shí)驗(yàn)結(jié)果是否合理，就有了用相關(guān)系數(shù)r來(lái)評(píng)判的方法.對(duì)一元線性回歸，r定義為：

(6)

相關(guān)系數(shù)r的數(shù)值大小反映了相關(guān)程度的好壞，即所得結(jié)果的合理性.|r|的值介于0到1之間，|r|的值越接近于1，說(shuō)明實(shí)驗(yàn)數(shù)據(jù)是緊密聚集在所求直線的附近.x、y之間存在線性關(guān)系，用線性函數(shù)進(jìn)行回歸比較合理.反之，則說(shuō)明實(shí)驗(yàn)數(shù)據(jù)遠(yuǎn)離所求直線且分散，x、y之間不存在線性關(guān)系，需要重新用其他函數(shù)試探.表7給出了Excel中線性函數(shù)最小二乘法擬合計(jì)算實(shí)例，其中A列數(shù)據(jù)為x，B類數(shù)據(jù)為y，D列為計(jì)算參數(shù)，E列為計(jì)算結(jié)果，F(xiàn)列為有效位數(shù)，G列為修約結(jié)果，H列為實(shí)驗(yàn)結(jié)果.表7中E、F、G列的對(duì)應(yīng)計(jì)算程序如表8-10所示.同時(shí)，表11給出了截距平均值計(jì)算程序注解.

表7 線性函數(shù)y=a+bx最小二乘法擬合情況

表8 線性函數(shù)最小二乘法各參數(shù)計(jì)算程序(表7的E列)

續(xù)表

表9 線性函數(shù)最小二乘法各參數(shù)保留有效位數(shù)計(jì)算程序(表7的F列)

表10 線性函數(shù)最小二乘法各參數(shù)修約計(jì)算程序(表7的G列)

表11 線性函數(shù)最小二乘法中截距平均值計(jì)算程序注解

3.2 Excel計(jì)算與Origin軟件擬合結(jié)果對(duì)比

將表7中的原始數(shù)據(jù)導(dǎo)入Origin數(shù)據(jù)表中，采用散點(diǎn)形式作圖后，對(duì)圖形進(jìn)行“Linear fitting”命令，結(jié)果如圖1所示.

采用Excel和Origin軟件計(jì)算的結(jié)果比較,結(jié)果如表12所示.由表12可見(jiàn),采用兩種方法計(jì)算的斜率、截距、相關(guān)系數(shù)是一致的，且Excel表格中可根據(jù)有效位數(shù)保留規(guī)則來(lái)表示計(jì)算結(jié)果.

表12 Origin擬合結(jié)果與Excel計(jì)算結(jié)果比較

圖1 Origin軟件中線性擬合圖示

4 Excel應(yīng)用與物理實(shí)驗(yàn)誤差分析中的常見(jiàn)問(wèn)題處理

4.1 最后一位為零的數(shù)值顯示處理

在運(yùn)算中有可能遇到最后一個(gè)數(shù)字是0的情況，在Excel中將不會(huì)顯示，如某單元格(D4)中數(shù)字最后一位的0沒(méi)有顯示.此時(shí)可以對(duì)該數(shù)據(jù)進(jìn)行完整顯示,只需在另外的單元格中輸入如下公式即可.

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

4.2 程序的保護(hù)和原始數(shù)據(jù)的輸入

在輸入原始數(shù)據(jù)時(shí)，難免因操作失誤將數(shù)據(jù)輸入到非法位置，因此需對(duì)非原始數(shù)據(jù)輸入?yún)^(qū)域進(jìn)行限定設(shè)置.在Excel中,可以通過(guò)菜單欄中的“審閱→保護(hù)工作表”來(lái)實(shí)現(xiàn)對(duì)工作表的輸入保護(hù)，然后進(jìn)入“審閱”→“更改”模塊，點(diǎn)擊“允許用戶編輯”會(huì)出現(xiàn)圖示(圖2).

圖2 原始數(shù)據(jù)輸入?yún)^(qū)域設(shè)置和非輸入?yún)^(qū)域保護(hù)操作界面

點(diǎn)擊“新建”，并將鼠標(biāo)置于圖(2)“引用單元格”處，在Excel中選擇要輸入數(shù)據(jù)的區(qū)域，如“F10:F16”，點(diǎn)擊確定，則F10:F16區(qū)域就是可輸入?yún)^(qū)域.同理,要設(shè)置多個(gè)可輸入?yún)^(qū)域，則重復(fù)上述操作即可.反之,對(duì)非輸入?yún)^(qū)域的程序進(jìn)行修改，點(diǎn)擊“審閱”中“更改”模塊的“撤銷工作表保護(hù)”即可.

5 結(jié)論

利用Excel自帶函數(shù)，如STDEV、SIGN、REPT、ROUND、SUMPRODUCT、INDIRECT等，編寫了多次直接測(cè)量和間接測(cè)量物理量的誤差參數(shù)計(jì)算程序，如數(shù)據(jù)個(gè)數(shù)統(tǒng)計(jì)、平均值、A類不確定度、B類不確定度、合成不確定度、對(duì)數(shù)法誤差傳遞系數(shù)，以及四舍六入五取偶的修約程序等.對(duì)最小二乘法、逐差法等相關(guān)參數(shù)進(jìn)行了計(jì)算程序編寫.同時(shí)對(duì)界面進(jìn)行優(yōu)化，使實(shí)驗(yàn)者輸入原始數(shù)據(jù)后，就可以直接獲得實(shí)驗(yàn)數(shù)據(jù)相關(guān)結(jié)果，提高實(shí)驗(yàn)效率.