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

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

0 引言

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

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

1 常用函數(shù)用法

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

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

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

表2中計算結果，即C列的具體計算表達式及注解如表3所示，其中*號部分為注解.表3中m的不確定度計算程序及注解如表4所示.

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

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

表3 質量m多次直接測量計算過程注解

表4 質量m四舍六入五湊偶的程序

2.2 直接測量的B類不確定度計算

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

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

表5 Excel中質量m直接測量中各儀器的不確定度計算

2.3 基于多個直接測量結果的間接測量計算

表6 Excel中密度ρ間接測量的計算及注解

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

3.1 相關參數(shù)的計算

(1)

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

(2)

(3)

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

(4)

(5)

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

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

(6)

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

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

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

續(xù)表

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

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

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

3.2 Excel計算與Origin軟件擬合結果對比

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

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

表12 Origin擬合結果與Excel計算結果比較

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

4 Excel應用與物理實驗誤差分析中的常見問題處理

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

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

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

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

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

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

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

5 結論

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