研生試劑-液壓千斤頂校準(zhǔn)方程用Excel進(jìn)行數(shù)據(jù)處理的方法簡介
千斤頂校準(zhǔn)方程(即線性回歸方程)是根據(jù)數(shù)理統(tǒng)計理論計算，以力值為自變量，以千斤頂指示器示值為函數(shù)的擬合方程(實(shí)際中力值與千斤頂指示器示值自變量與函數(shù)選擇可任意)，設(shè)為y=bx+a，各系數(shù)截距a、斜率b、相關(guān)系數(shù)r線性回歸分析式為
其中
Excel在線性回歸方程計算中有多種數(shù)據(jù)處理方法。本文利用Excel提供的統(tǒng)計函數(shù)和回歸分析工具進(jìn)行線性回歸分析，并利用Excel的插入圖表功能作線性回歸擬合圖。另外介紹根據(jù)原始記錄里各項規(guī)程中規(guī)定的指標(biāo)由函數(shù)自動計算出檢定結(jié)果方法，并利用映射功能自動生成檢定證書。
現(xiàn)舉一實(shí)例說明該方法的具體應(yīng)用。對某一額定負(fù)荷為2000kN的液壓千斤頂進(jìn)行6組試驗(yàn)，原始數(shù)據(jù)如表1所示。求解線性回歸方程，過程如表2所示。
<CTSM>表1千斤頂檢定原始數(shù)據(jù)</CTSM>
具體操作步驟:
一、實(shí)測數(shù)據(jù)表格制作(見表1)
實(shí)際中把此表格作為原始記錄的相關(guān)部分填寫。表1中，I、II、III分別是檢定中的第i個檢定點(diǎn)的3次實(shí)測數(shù)據(jù)。E列是第i個檢定點(diǎn)3次平均值，在E3單元格中輸入“=AVERAGE(B3:D3)”(選擇區(qū)域時也可用鼠標(biāo)拖動選中)。然后，選中E3單元，待鼠標(biāo)變成“+”，拖動鼠標(biāo)復(fù)制格式，下邊的單元自動得出第i個檢定點(diǎn)的平均值。
二、千斤頂校準(zhǔn)方程進(jìn)行線性回歸分析的計算
利用Excel提供的統(tǒng)計函數(shù)快速計算。工作表數(shù)據(jù)模型如表2所示，其中的yi由表1的平均值一欄得到。
<CTSM>表2千斤頂校準(zhǔn)方程系數(shù)求解計算過程</CTSM>
1.計算斜率b
選中C8單元格，點(diǎn)擊“插入”菜單→選“函數(shù)”→選“統(tǒng)計”→在“或選擇類別”中選擇“統(tǒng)計”→選“SLOPE”→點(diǎn)“確定”→彈出對話框中，*數(shù)據(jù)區(qū)域按提示選擇yi列(即C2:C7)，第二數(shù)據(jù)區(qū)域按提示選擇xi列(即B2:B7)→點(diǎn)“確定”，即得b=30.0136。
2.計算截距a
選中C9單元格，點(diǎn)擊“插入”菜單→選“函數(shù)”→選“統(tǒng)計”→在“或選擇類別”中選擇“統(tǒng)計”→選“INTERCEPT”→點(diǎn)“確定”→彈出對話框中，*數(shù)據(jù)區(qū)域yi列(即C2:C7)，第二數(shù)據(jù)區(qū)域選擇xi(即B2:B7)，與第1點(diǎn)同→點(diǎn)“確定”，即得a=-1.5733。
3.計算相關(guān)系數(shù)r
選中C10單元格，點(diǎn)擊“插入”菜單→選“函數(shù)”→選“統(tǒng)計”→在“或選擇類別”中選擇“統(tǒng)計”→選“CORREL”→點(diǎn)“確定”→彈出對話框中，*數(shù)據(jù)區(qū)域yi列(即C2:C7)，第二數(shù)據(jù)區(qū)域選擇xi(即B2:B7)，與第1點(diǎn)同→點(diǎn)“確定”，即得r=0.999866。
三、校準(zhǔn)方程直線圖作法
如表3所示，假如把鼠標(biāo)放在B8單元格上(覺得方便放任一單元格均可)，點(diǎn)擊菜單“插入”→“圖表”→“XY散點(diǎn)圖”→下一步→“數(shù)據(jù)區(qū)域”對話框中點(diǎn)zui右選擇按鍵，用鼠標(biāo)拖動數(shù)據(jù)區(qū)域?yàn)?B$2:$C$7→下一步→下一步→點(diǎn)完成，在彈出圖表的6個數(shù)據(jù)點(diǎn)中的任意一個上點(diǎn)擊右鍵→添加趨勢曲線→對話框中點(diǎn)“選項”→趨勢線名稱處點(diǎn)“自定義”，輸入“千斤頂校準(zhǔn)方程”；勾選“顯示公式、顯示R平方值”→點(diǎn)“確定”。作出如表3中所示圖形。若與本圖有些出入，把圖片拉大就能顯示一致(本文中圖片是基于Excel2003作的)。
<CTSM>表3</CTSM>
四、采用映射關(guān)系，由原始記錄自動生成證書
1.原始記錄中示值重復(fù)性、負(fù)載率、內(nèi)插誤差計算公式計算
建立原始記錄表格部分截圖，如表4所示。
<CTSM>表4液壓千斤頂原始記錄</CTSM>
利用Excel中引用函數(shù)公式，讓需求值的單元格公式等于JJG621-2005《液壓千斤頂》檢定規(guī)程第6.2.7.6條的各種指標(biāo)定義式即可。
(1)示值重復(fù)性R為原始記錄中第i個檢定點(diǎn)負(fù)荷力值3次讀數(shù)zui大值減去zui小值，再除以3次示值的平均值，計算:即在F4單元格中輸入引用公式“=ROUND(100*(MAX(B4:D4)-MIN(B4:D4))/E4，2)”，其他用光標(biāo)變成“+”復(fù)制得到。
(2)負(fù)載效率η計算
千斤頂輸出力理論值是千斤頂活塞有效面積乘以第i個檢定點(diǎn)的油壓表示值。計算:在I4單元格輸入“=A4*B1*1000”，其他用光標(biāo)變成“+”復(fù)制得到。
η計算:在J4單元格輸入“=E4/I4”，即各檢定點(diǎn)的測量平均值除以千斤頂理論輸出力值。其他用光標(biāo)變成“+”復(fù)制得到。
(3)表格中計算值一欄G4:G9為按擬合生成的校準(zhǔn)方程y=30.0136x-1.5733計算得到的力值。
(4)內(nèi)插誤差I計算:在H4單元格中輸入“=(G4-E4)/E4”，即計算值減平均值再除以平均值。其他用光標(biāo)變成“+”復(fù)制得到。
2.由映射功能自動生成證書
原始記錄與證書在一個工作簿中平行排列。需要引用原始記錄數(shù)據(jù)的單元格就如圖在單元格中輸入“=原始記錄!單元格編號”，即可得到有對應(yīng)關(guān)系的檢定證書。檢定員不必再在檢定證書上錄入一遍數(shù)據(jù)。
在線客服
