基于Excel的直線擬合問題不確定度評(píng)定研究

趙云飛，邱成鋒，賈小文，張金鳳，范海英

(陸軍軍事交通學(xué)院 基礎(chǔ)部，天津 300161)

大學(xué)物理實(shí)驗(yàn)經(jīng)常會(huì)遇到直線擬合問題，逐差法和最小二乘法是解決這一問題的常用方法。這兩種方法的優(yōu)劣是大學(xué)物理實(shí)驗(yàn)爭論已久的問題。而Excel又是一款常用的軟件，是學(xué)生通過全國計(jì)算機(jī)等級(jí)一級(jí)考試時(shí)的必考內(nèi)容。本文以Excel 2016為計(jì)算工具，分別用逐差法和最小二乘法處理“拉伸法測金屬絲的楊氏模量”實(shí)驗(yàn)數(shù)據(jù)，通過評(píng)定測量結(jié)果的不確定度對(duì)這兩種數(shù)據(jù)處理方法分析比較。

1 金屬絲楊氏模量的測量

1.1 實(shí)驗(yàn)原理

一根長為L，截面積為S的均勻金屬絲，在沿長度方向外力F的作用下，其伸長為δl，設(shè)金屬絲的直徑為d，則金屬絲楊氏模量E為：

(1)

式(1)中，F(xiàn)、L、d比較容易測量，微小變化量δl測量困難。下面利用CCD成像系統(tǒng)來測量δl。如圖1所示，在懸垂的金屬絲下端連著十字叉絲和砝碼盤，當(dāng)盤中加上質(zhì)量M的砝碼時(shí)，金屬絲受力增加了F=Mg，g為當(dāng)?shù)氐闹亓铀俣龋缣旖虻貐^(qū)約為9.801 m/s2。十字叉絲隨著金屬絲的伸長下降。叉絲板通過顯微鏡的物鏡成像在最小分度為0.05 mm的分劃板上后被目鏡放大，通過監(jiān)視器觀察，從而實(shí)現(xiàn)對(duì)δl進(jìn)行測量[1]。

圖1 測量原理圖

1.2 數(shù)據(jù)測量

表1 金屬絲直徑的測量

表2 金屬絲伸縮變化測量

圖2 砝碼質(zhì)量與伸縮變化量關(guān)系圖

2 直接測量量的標(biāo)準(zhǔn)不確定度評(píng)定

表3 直接測量量標(biāo)準(zhǔn)不確定度

金屬絲直徑d共測量6次，以平均值作為最佳估計(jì)值，需考慮測量重復(fù)性帶來的標(biāo)準(zhǔn)不確定度分量u2(d)，用函數(shù)“=STDEVA(B11：G11)/SQRT(6)”計(jì)算，結(jié)果為：u2(d)=0.000 577 mm。

因此測量量d的合成標(biāo)準(zhǔn)不確定度為：

因此測量量l的合成標(biāo)準(zhǔn)不確定度為：

圖3 Excel數(shù)據(jù)處理示例

3 求解金屬絲的楊氏模量并評(píng)定δl的標(biāo)準(zhǔn)不確定度

3.1 利用逐差法求解

(2)

利用式(1)并編寫Excel計(jì)算公式“=4*9.801*F12*10^4/(PI()*H11^2*E4)”可得金屬絲的楊氏模量E(公式中的E4為(2)式的計(jì)算結(jié)果)：

(3)

表4 逐差法求解伸縮變化測量

由表4可知

所以利用不確定度傳播律，可得：

(3)

(4)

3.2 利用最小二乘法求解

圖4 “l(fā)inest”函數(shù)使用示例

利用“l(fā)inest”函數(shù)求得斜率k=-0.001 229 46，再利用函數(shù)“=ABS(I16)”取絕對(duì)值，即可計(jì)算出每增加1 g質(zhì)量金屬絲的伸長量δl=0.001 229 mm。δl的實(shí)驗(yàn)標(biāo)準(zhǔn)偏差也可利用函數(shù)“l(fā)inest”求解出來，計(jì)算結(jié)果參見圖4Excel表“I17”。

uc(δl)=s(δl)=6.97×10-6mm

(5)

由此可以計(jì)算出金屬絲的楊氏模量為：

(6)

4 結(jié) 語

4.1 評(píng)定用逐差法求伸長量后E的不確定度

由不確定度傳播律可得：

(7)

代入數(shù)據(jù)并Excel軟件編寫公式可以計(jì)算得出(g=9.801 m/s2)：

=-187 154 995 g·m·cm/(s2·mm4)

=-15756394983 g·m·cm/(s2·mm4)

將uc(L)、uc(d)和uc(δl)的值分別代入后，可得：

=1.16×1011Pa

取包含因子k=2，則被測金屬絲楊氏模量E的擴(kuò)展不確定度：

U=2uc=2.4×1011Pa

(8)

因此，E=(1.9±2.4)Pa，用逐差法求解δl后E的擴(kuò)展不確定度太大，明顯不合理。

4.2 評(píng)定用最小二乘法求伸長量后E的不確定度

同理，可求出利用最小二乘法求解δl后

=-186237568.6 g·m·cm/(s2·mm4)

=-15602299137 g·m·cm/(s2·mm4)

將uc(L)、uc(d)和uc(δl)的值分別代入，可得

=0.046×1011Pa

取包含因子k=2，則被測金屬絲楊氏模量的擴(kuò)展不確定度：

U=2Uc=0.09×1011Pa

因此，E=(1.92±0.09)Pa，比較而言，實(shí)驗(yàn)中用最小二乘法求解δl更加合理，逐差法只適合計(jì)算條件不方便時(shí)估算測量結(jié)果。