利用Excel編制測(cè)量不確定度計(jì)算表格的方法與使用

閆立新　呼　和　劉曉飛　孫巖峰

(內(nèi)蒙古自治區(qū)計(jì)量測(cè)試研究院，呼和浩特 010020)

0　引言

在計(jì)量檢測(cè)校準(zhǔn)工作中，大量常見(jiàn)計(jì)量器具的測(cè)量不確定度已被分析得非常透徹，或有經(jīng)典的套路可參考，但是對(duì)每一個(gè)具體的計(jì)量器具，往往最棘手的是測(cè)量不確定度評(píng)定的計(jì)算過(guò)程比較繁瑣。我們?nèi)粘９ぷ髦谐Ｓ玫腅xcel表格軟件，非常適合用于計(jì)量檢測(cè)數(shù)據(jù)的計(jì)算處理。我院在近年的計(jì)量檢測(cè)實(shí)踐中，摸索出了一套符合國(guó)家計(jì)量技術(shù)規(guī)范JJF 1059—2012《測(cè)量不確定度評(píng)定與表示》和各計(jì)量專(zhuān)業(yè)檢定規(guī)程中要求的，用于測(cè)量不確定度評(píng)定計(jì)算的電子表格編制方法，取得了很好的效果。為了使廣大計(jì)量工作者能全面準(zhǔn)確地了解并能熟練使用該方法，本文就該電子表格的編制方法與使用進(jìn)行了詳細(xì)介紹，供廣大計(jì)量工作者參考。

1　測(cè)量不確定度評(píng)定計(jì)算流程

測(cè)量不確定度評(píng)定過(guò)程如下：

1)建模分析。建立不確定度數(shù)學(xué)模型,分析有哪些不確定度分量，各個(gè)不確定度分量構(gòu)成、性質(zhì)(A類(lèi)或B類(lèi)) ，確定靈敏系數(shù)。

2) A類(lèi)、B類(lèi)標(biāo)準(zhǔn)不確定度的評(píng)定計(jì)算。

3)計(jì)算合成標(biāo)準(zhǔn)不確定度、有效自由度、包含因子k、計(jì)算擴(kuò)展不確定度。

4)給出測(cè)量結(jié)果不確定度報(bào)告。

2　用到的Excel中的函數(shù)簡(jiǎn)介

1)SUM (number1,number2, ...)返回某一單元格區(qū)域(1到30個(gè))中所有數(shù)字之和，語(yǔ)法：

= SUM(number1,number2, ...)

2)STDEV(number1,number2,...)估算樣本(1到 30個(gè))的標(biāo)準(zhǔn)偏差(對(duì)應(yīng)于貝塞爾公式)，反映相對(duì)于平均值的離散程度，語(yǔ)法：

=STDEV(number1,number2,...)

3)CUNT(value1,value2,...)返回包含數(shù)字以及包含參數(shù)列表(1到30個(gè))中有數(shù)字的單元格的個(gè)數(shù)，該函數(shù)可以計(jì)算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項(xiàng)個(gè)數(shù),語(yǔ)法:

=COUNT(value1,value2,...)

只有數(shù)字類(lèi)型的數(shù)據(jù)才被計(jì)算(計(jì)數(shù))。

4)TINV(probability,degrees of freedom)作為概率和自由度函數(shù), 返回學(xué)生t分布的t值。probability：對(duì)應(yīng)于雙尾學(xué)生t分布的概率，具體使用時(shí)=(1-置信水準(zhǔn)(p<1))。Degrees of freedom ：分布的自由度。語(yǔ)法：

=TINV(1-置信水準(zhǔn)(p<1),自由度)

5)ABS(number) 返回?cái)?shù)字的絕對(duì)值，語(yǔ)法：

=ABS(number)

6) ROUNDUP(number,num_digits) 離零值，向上舍入數(shù)字，語(yǔ)法：

=ROUNDUP(number,num_digits)

number：為需要向上舍入的任意實(shí)數(shù)。

num_digits：四舍五入后的數(shù)字位數(shù)

7)IF(logical_test,value_if_true,value_if_false) 執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果，可以進(jìn)行七層嵌套，用于對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)，語(yǔ)法(此函數(shù)的用法必須熟練掌握)：

= IF(logical_test,value_if_true,value_if_false)

logical_test ：表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式、單元格引用。

value_if_true：logical_test 為 TRUE 時(shí)返回的值，也可以是其他公式。

value_if_false：logical_test 為 FALSE 時(shí)返回的值，也可以是其他公式、單元格引用、嵌套。

還有一些本文用到的Excel基本函數(shù)和工作表、單元格之間計(jì)算引用、條件格式基本用法、單元格限定數(shù)值列表基本用法等技巧，在進(jìn)行編制測(cè)量不確定度計(jì)算表前，均應(yīng)熟悉并掌握。

3　編制基本過(guò)程

下面以中國(guó)計(jì)量出版社出版的《測(cè)量不確定度評(píng)定與表示指南》第十章測(cè)量不確定度應(yīng)用實(shí)例十一、聲學(xué)測(cè)量中的應(yīng)用(P117)為例，闡述毫瓦級(jí)超聲功率計(jì)示值誤差測(cè)量不確定度計(jì)算電子表格的編制方法與過(guò)程。

3.1　建模分析

根據(jù)具體計(jì)量器具的數(shù)學(xué)模型的測(cè)量不確定度分析，確定各個(gè)不確定度分量構(gòu)成、性質(zhì)(A類(lèi)或B類(lèi))，確定靈敏系數(shù)，此步驟決定計(jì)算表格編制的復(fù)雜程度(一般A類(lèi)1個(gè)，B類(lèi)4個(gè)，可滿足常見(jiàn)計(jì)量器具測(cè)量不確定度分析評(píng)定計(jì)算)。

3.2　標(biāo)準(zhǔn)不確定度的評(píng)定計(jì)算表格編制

按圖1編制電子表格，主要是設(shè)置單元格格式(列寬、行高、格式)，在單元格內(nèi)輸入相應(yīng)項(xiàng)目名稱(chēng)、常用數(shù)據(jù)、函數(shù)公式及引用，單元格的背景顏色可根據(jù)個(gè)人喜好設(shè)定成不同顏色的，每次使用時(shí)需要重新錄入數(shù)據(jù)的，設(shè)定為白色。

圖1　毫瓦級(jí)超聲功率計(jì)示值誤差測(cè)量不確定度計(jì)算表

3.2.1A類(lèi)標(biāo)準(zhǔn)不確定度的計(jì)算表格編制

A類(lèi)標(biāo)準(zhǔn)不確定度的計(jì)算主要是對(duì)測(cè)量結(jié)果數(shù)列進(jìn)行平均值計(jì)算、平均值“修約化整”、實(shí)驗(yàn)標(biāo)準(zhǔn)偏差的計(jì)算、標(biāo)準(zhǔn)不確定度和自由度的計(jì)算，具體見(jiàn)圖1(A1:S4)部分，具體步驟如下：

1)單位引用：在R1中輸入“單位”，在S3中輸入“=R1”，在S6中輸入“=R1”、在S12中輸入“=R1”。

2)自由度：在N3中輸入“=COUNT(D3:M3)-1”。此公式的作用是對(duì)有數(shù)據(jù)的單元格進(jìn)行計(jì)數(shù)，然后再減去1計(jì)算出自由度。

3)平均值：在O3中輸入“=SUM(D3:M3)/(N3+1)”，此公式的作用是對(duì)D3:M3單元格中的測(cè)量數(shù)據(jù)求和再除以(自由度+1)，計(jì)算測(cè)量數(shù)據(jù)列的平均值。

4)化整平均值：在P3中輸入平均值的“修約化整”值，此處可用VBA編制函數(shù)模塊進(jìn)行自動(dòng)計(jì)算[3]。

5)標(biāo)準(zhǔn)偏差：在Q3中輸入“=IF(N3=3,STDEV

(D3:G3),IF(N3=4,STDEV(D3:H3),IF(N3=5,STDEV(D3:I3),IF(N3=6,STDEV(D3:J3),IF(N3=7,STDEV(D3:K3),IF(N3=8,STDEV(D3:L3),IF(N3=9,STDEV(D3:M3),"數(shù)據(jù)必須大于4")))))))”，此條件判斷公式的作用是根據(jù)N3中自由度的值，選擇單元格進(jìn)行實(shí)驗(yàn)標(biāo)準(zhǔn)偏差s(x)的計(jì)算。

6)不確定度：在R3中輸“=Q3/((N3+1)^0.5)”，此公式的作用是根據(jù)對(duì)“自由度+1”計(jì)算數(shù)據(jù)個(gè)數(shù)，再平方根后被標(biāo)準(zhǔn)偏差除，得測(cè)量數(shù)列的標(biāo)準(zhǔn)不確定度。

7)(格式準(zhǔn)則)Gσ×s：在Q4中輸入“=IF(N3=2,"不能判斷",IF(N3=3,Q3*1.463,IF(N3=4,Q3*1.672,IF(N3=5,Q3*1.822,IF(N3=6,Q3*1.938,IF(N3=7,Q3*2.032,IF(N3=8,Q3*2.11,IF(N3=9,Q3*2.176,"超出范圍"))))))))” ，此條件判斷公式的作用是進(jìn)行單元格內(nèi)格拉布斯準(zhǔn)則的臨界值與標(biāo)準(zhǔn)偏差的乘積計(jì)算。

8)在相應(yīng)單元格(D4:M4)中設(shè)置公式和條件格式用于異常數(shù)據(jù)判斷顯示: 在D4中輸入“=IF(D3="","",ABS (D3-$P$3))”，然后選擇D4填充至M4即可，如果D4至M4的10個(gè)單元格中的數(shù)據(jù)異常(≥Q4中的數(shù)據(jù))，在D4至M4的10個(gè)單元格中的背景色顯示紅色，否則顯示綠色。

設(shè)置單元格條件格式方法：?jiǎn)螕暨x定單元格D4→菜單欄“格式(O)”→條件格式(D)→設(shè)置條件1(1)→設(shè)置條件2(2)；單擊條件格式的“格式”按鈕用于設(shè)定條件為真時(shí)，待用格式的形式→圖案→顏色(紅、綠)；然后選擇D4填充至M4。條件格式設(shè)置窗口如圖2所示。

圖2　條件格式設(shè)置窗口

3.2.2B類(lèi)標(biāo)準(zhǔn)不確定度計(jì)算表格編制

B類(lèi)標(biāo)準(zhǔn)不確定度的計(jì)算主要根據(jù)輸入的靈敏系數(shù)ci、不確定度區(qū)間的半寬度a、假設(shè)不確定度的概率分布、置信水平p等，估計(jì)置信因子k和根據(jù)“Δu(xi)/u(xi)”估計(jì)自由度，計(jì)算標(biāo)準(zhǔn)不確定度ub=a/k，見(jiàn)圖1(I8:O12)部分，具體步驟：

1)在單元格內(nèi)輸入常用資料數(shù)據(jù)，用于單元格限定數(shù)值列表，具體見(jiàn)圖1(A5:E12)部分。

2)在編制好的電子表格中設(shè)置“概率分布p”，單元格限定數(shù)值列表和“置信因子k值”條件判斷公式，具體：選擇J8→單擊菜單欄“數(shù)據(jù)(D)”→“有效性(L)” →設(shè)置→允許(A)→序列→勾選忽略空值(B)和提供下拉箭頭(I)→來(lái)源(S)=限定數(shù)值單元格列($A$6:$A$12)；然后在單元格K8內(nèi)輸入“=IF(J8="正態(tài)",1.96,IF(J8="均勻",1.732,IF(J8="三角",2.449,IF(J8="反正弦",1.414,IF(J8="兩點(diǎn)",1,"自己設(shè)置")))))”，選定單元格K8填充至K12。

3)在編制好的表格中設(shè)置“Δu(xi)/u(xi)”單元格限定數(shù)值列表，然后 “估計(jì)自由度”條件判斷公式，在單元格O8內(nèi)輸入“=IF(N8= $D$6,$F$6,IF(N8=$D$7,$F$7,IF(N8=$D$8,$F$8,IF(N8=$D$9,$F$9,IF(N8=$D$10,$F$10,IF(N8=$D$11,$F$11,IF(N8=$D$12,$F$12,A))))))) ”，再填充至O12。

4)不確定度：在M8中輸“=ABS(I8)*L8/K8”，再填充至M12，此公式的作用是計(jì)算B類(lèi)不確定度分量的標(biāo)準(zhǔn)不確定度。

3.2.3擴(kuò)展不確定度計(jì)算表格編制

1)設(shè)置單元格格式(列寬、行高、格式)，在單元格輸入項(xiàng)目名稱(chēng)、公式、引用，具體見(jiàn)圖1(P5:S12)部分。

2)合成不確定度：在R6中按合成不確定度公式(相關(guān)、不相關(guān))，進(jìn)行相應(yīng)單元引用計(jì)算，這里以不相關(guān)為例，在R6中輸入“=(R3^2+(M8)^2+(M9)^2+(M10)^2+(M11)^2+(M12)^2)^0.5”，在S6中輸入“=S4”，進(jìn)行計(jì)量單位引用。

3)有效自由度：在R7中按韋爾奇一薩特思韋特(Welch-Satterthwaite)公式，進(jìn)行相應(yīng)單元引用計(jì)算，在R7中輸入 “=(R6^4)/((R3^4)/N3)+((M8^4)/O8)+((M9^4)/O9)+((M10^4)/O10)+((M11^4)/O11)+((M12^4)/O12)”。

4)取置信水準(zhǔn)p(1>p>0)，設(shè)置R8單元格的格式為數(shù)字百分比。

5)包含因子：在R10中輸入“=IF(R7>10000,ROUND((TINV(1-R8,10000)),S9),ROUND((TINV(1-R8,R7)),S9))”，此公式的作用是當(dāng)有效自由度大于10000，調(diào)用函數(shù)TINV(1-R8,R7)，按10000計(jì)算包含因子k值，并根據(jù)保留位數(shù)S9設(shè)定的值用ROUND對(duì)數(shù)據(jù)4舍5入；當(dāng)有效自由度小于10000，按實(shí)際值計(jì)算包含因子k值，并進(jìn)行數(shù)據(jù)4舍5入。

6)擴(kuò)展不確定度：在R12中輸入“=ROUND UP(R10*R6,S11)”，此公式的作用是用包含因子R10×合成不確定度R6，然后用函數(shù)ROUNDUP對(duì)S11單元格設(shè)定的有效位數(shù)進(jìn)行舍入(只進(jìn)不舍)。

7)相對(duì)擴(kuò)展不確定度：在R13中輸入“=ROUNDUP((R12/O3),2)，此公式的作用是用擴(kuò)展不確定度÷化整平均值P3，然后用函數(shù)ROUNDUP對(duì)設(shè)定的有效位數(shù)進(jìn)行舍入(只進(jìn)不舍)。

3.2.4測(cè)量結(jié)果報(bào)告表格編制

在E13中輸入="顯示值/指示值 = "&Q1&S3&",實(shí)際測(cè)量值 = "&P3&S12&"："&"U ="&R12&S12&"；Ur ="&TEXT(R13,"0.0%")&"；k ="&TEXT(R10," 0.00")&" ，此公式的用途是用“&”連接文本和單元格引用(調(diào)整TEXT內(nèi)的參數(shù)可設(shè)定小數(shù)后的有效位數(shù))，輸入數(shù)據(jù)后自動(dòng)生成測(cè)量結(jié)果不確定度報(bào)告,從而完成整個(gè)測(cè)量不確定度的計(jì)算過(guò)程。

4　使用方法說(shuō)明

掌握Excel的基本操作，熟悉測(cè)量不確定度評(píng)定過(guò)程和涉及的基本公式及定義，就能熟練運(yùn)用本文介紹的方法編制出你想要的測(cè)量不確定度計(jì)算表格。本文給出的編制案例基本可滿足95%以上的常見(jiàn)計(jì)量器具的測(cè)量不確定度的評(píng)定計(jì)算，對(duì)于比較復(fù)雜數(shù)學(xué)模型(有相關(guān)性要求)的測(cè)量不確定的計(jì)算，可直接修改圖1表格的單元格中的公式即可，但要注意相關(guān)單元格中公式的絕對(duì)引用的修改。

5　結(jié)束語(yǔ)

本文給出計(jì)量數(shù)據(jù)測(cè)量不確定度計(jì)算電子表格的編制方法，希望能對(duì)從事計(jì)量檢定、檢測(cè)的同志有所幫助，有不妥之處歡迎廣大同行批評(píng)指正。

[1]JJF 1059—2012測(cè)量不確定度評(píng)定與表示

[2]國(guó)家質(zhì)量技術(shù)監(jiān)督局計(jì)量司主編.測(cè)量不確定度評(píng)定與表示指南.北京：中國(guó)計(jì)量出版社，2005

[3]閆立新,呼和,張建平.測(cè)量數(shù)值修約化整程序的編制方法及使用. 中國(guó)計(jì)量,2013(4)

[4]劉翠萍. 淺談Excel軟件在計(jì)量管理及計(jì)量檢定中的應(yīng)用.計(jì)量技術(shù),2003(7)

[5]李擁軍，李獻(xiàn)波，徐建民.EXCEL模板在數(shù)據(jù)處理中的應(yīng)用.計(jì)量技術(shù),2001(10)

[6]陳德恩，黃太柱，馬冬晨.EXCEL在有效數(shù)字運(yùn)算中的應(yīng)用.計(jì)量技術(shù),2006(11)