研生試劑-液壓千斤頂校準(zhǔn)方程用Excel進(jìn)行數(shù)據(jù)處理的方法簡(jiǎn)介

千斤頂校準(zhǔn)方程(即線性回歸方程)是根據(jù)數(shù)理統(tǒng)計(jì)理論計(jì)算，以力值為自變量，以千斤頂指示器示值為函數(shù)的擬合方程(實(shí)際中力值與千斤頂指示器示值自變量與函數(shù)選擇可任意)，設(shè)為y=bx+a，各系數(shù)截距a、斜率b、相關(guān)系數(shù)r線性回歸分析式為

 
       其中
      

Excel在線性回歸方程計(jì)算中有多種數(shù)據(jù)處理方法。本文利用Excel提供的統(tǒng)計(jì)函數(shù)和回歸分析工具進(jìn)行線性回歸分析，并利用Excel的插入圖表功能作線性回歸擬合圖。另外介紹根據(jù)原始記錄里各項(xiàng)規(guī)程中規(guī)定的指標(biāo)由函數(shù)自動(dòng)計(jì)算出檢定結(jié)果方法，并利用映射功能自動(dòng)生成檢定證書。

現(xiàn)舉一實(shí)例說明該方法的具體應(yīng)用。對(duì)某一額定負(fù)荷為2000kN的液壓千斤頂進(jìn)行6組試驗(yàn)，原始數(shù)據(jù)如表1所示。求解線性回歸方程，過程如表2所示。

<CTSM>表1千斤頂檢定原始數(shù)據(jù)</CTSM>

具體操作步驟:

一、實(shí)測(cè)數(shù)據(jù)表格制作(見表1)

實(shí)際中把此表格作為原始記錄的相關(guān)部分填寫。表1中，I、II、III分別是檢定中的第i個(gè)檢定點(diǎn)的3次實(shí)測(cè)數(shù)據(jù)。E列是第i個(gè)檢定點(diǎn)3次平均值，在E3單元格中輸入“=AVERAGE(B3:D3)”(選擇區(qū)域時(shí)也可用鼠標(biāo)拖動(dòng)選中)。然后，選中E3單元，待鼠標(biāo)變成“+”，拖動(dòng)鼠標(biāo)復(fù)制格式，下邊的單元自動(dòng)得出第i個(gè)檢定點(diǎn)的平均值。

二、千斤頂校準(zhǔn)方程進(jìn)行線性回歸分析的計(jì)算

利用Excel提供的統(tǒng)計(jì)函數(shù)快速計(jì)算。工作表數(shù)據(jù)模型如表2所示，其中的y_i由表1的平均值一欄得到。

<CTSM>表2千斤頂校準(zhǔn)方程系數(shù)求解計(jì)算過程</CTSM>

1.計(jì)算斜率b

選中C8單元格，點(diǎn)擊“插入”菜單→選“函數(shù)”→選“統(tǒng)計(jì)”→在“或選擇類別”中選擇“統(tǒng)計(jì)”→選“SLOPE”→點(diǎn)“確定”→彈出對(duì)話框中，*數(shù)據(jù)區(qū)域按提示選擇y_i列(即C2:C7)，第二數(shù)據(jù)區(qū)域按提示選擇x_i列(即B2:B7)→點(diǎn)“確定”，即得b=30.0136。

2.計(jì)算截距a

選中C9單元格，點(diǎn)擊“插入”菜單→選“函數(shù)”→選“統(tǒng)計(jì)”→在“或選擇類別”中選擇“統(tǒng)計(jì)”→選“INTERCEPT”→點(diǎn)“確定”→彈出對(duì)話框中，*數(shù)據(jù)區(qū)域y_i列(即C2:C7)，第二數(shù)據(jù)區(qū)域選擇x_i(即B2:B7)，與第1點(diǎn)同→點(diǎn)“確定”，即得a=-1.5733。

3.計(jì)算相關(guān)系數(shù)r

選中C10單元格，點(diǎn)擊“插入”菜單→選“函數(shù)”→選“統(tǒng)計(jì)”→在“或選擇類別”中選擇“統(tǒng)計(jì)”→選“CORREL”→點(diǎn)“確定”→彈出對(duì)話框中，*數(shù)據(jù)區(qū)域y_i列(即C2:C7)，第二數(shù)據(jù)區(qū)域選擇x_i(即B2:B7)，與第1點(diǎn)同→點(diǎn)“確定”，即得r=0.999866。

三、校準(zhǔn)方程直線圖作法

如表3所示，假如把鼠標(biāo)放在B8單元格上(覺得方便放任一單元格均可)，點(diǎn)擊菜單“插入”→“圖表”→“XY散點(diǎn)圖”→下一步→“數(shù)據(jù)區(qū)域”對(duì)話框中點(diǎn)zui右選擇按鍵，用鼠標(biāo)拖動(dòng)數(shù)據(jù)區(qū)域?yàn)?B$2:$C$7→下一步→下一步→點(diǎn)完成，在彈出圖表的6個(gè)數(shù)據(jù)點(diǎn)中的任意一個(gè)上點(diǎn)擊右鍵→添加趨勢(shì)曲線→對(duì)話框中點(diǎn)“選項(xiàng)”→趨勢(shì)線名稱處點(diǎn)“自定義”，輸入“千斤頂校準(zhǔn)方程”；勾選“顯示公式、顯示R平方值”→點(diǎn)“確定”。作出如表3中所示圖形。若與本圖有些出入，把圖片拉大就能顯示一致(本文中圖片是基于Excel2003作的)。

<CTSM>表3</CTSM>

四、采用映射關(guān)系，由原始記錄自動(dòng)生成證書

1.原始記錄中示值重復(fù)性、負(fù)載率、內(nèi)插誤差計(jì)算公式計(jì)算

建立原始記錄表格部分截圖，如表4所示。

<CTSM>表4液壓千斤頂原始記錄</CTSM>

利用Excel中引用函數(shù)公式，讓需求值的單元格公式等于JJG621-2005《液壓千斤頂》檢定規(guī)程第6.2.7.6條的各種指標(biāo)定義式即可。

(1)示值重復(fù)性R為原始記錄中第i個(gè)檢定點(diǎn)負(fù)荷力值3次讀數(shù)zui大值減去zui小值，再除以3次示值的平均值，計(jì)算:即在F4單元格中輸入引用公式“=ROUND(100*(MAX(B4:D4)-MIN(B4:D4))/E4，2)”，其他用光標(biāo)變成“+”復(fù)制得到。

(2)負(fù)載效率η計(jì)算

千斤頂輸出力理論值是千斤頂活塞有效面積乘以第i個(gè)檢定點(diǎn)的油壓表示值。計(jì)算:在I4單元格輸入“=A4*B1*1000”，其他用光標(biāo)變成“+”復(fù)制得到。

η計(jì)算:在J4單元格輸入“=E4/I4”，即各檢定點(diǎn)的測(cè)量平均值除以千斤頂理論輸出力值。其他用光標(biāo)變成“+”復(fù)制得到。

(3)表格中計(jì)算值一欄G4:G9為按擬合生成的校準(zhǔn)方程y=30.0136x-1.5733計(jì)算得到的力值。

(4)內(nèi)插誤差I計(jì)算:在H4單元格中輸入“=(G4-E4)/E4”，即計(jì)算值減平均值再除以平均值。其他用光標(biāo)變成“+”復(fù)制得到。

2.由映射功能自動(dòng)生成證書

原始記錄與證書在一個(gè)工作簿中平行排列。需要引用原始記錄數(shù)據(jù)的單元格就如圖在單元格中輸入“=原始記錄!單元格編號(hào)”，即可得到有對(duì)應(yīng)關(guān)系的檢定證書。檢定員不必再在檢定證書上錄入一遍數(shù)據(jù)。