直線度及平面度在Excel中的計算

陳建鋒

(深圳天祥質(zhì)量技術(shù)服務(wù)有限公司廣州分公司，廣州 510663)

0　引言

目前市場上有專門的軟件可以幫助計算直線度和平面度，甚至有檢測系統(tǒng)或儀器可以直接給出結(jié)果，但是這并不利于初學(xué)者對計算的理解。而具有強大功能的EXCEL辦公軟件使用起來方便靈活，能個性化設(shè)計和編輯。本文介紹使用EXCEL辦公軟件自動計算直線度和平面度，其中包括最小二乘法、最小條件法還有其他EXCEL內(nèi)置函數(shù)。文中介紹的技巧可以自動計算直線度、平板平面度和等級，生成2D或3D圖表以供閱讀者更直觀的理解。經(jīng)使用，所作出的模板方便簡單，平?？捎糜谠紨?shù)據(jù)記錄等多項工作。

1　直線度的計算

1.1　最小二乘法

最小二乘法(又稱最小平方法)是一種數(shù)學(xué)優(yōu)化技術(shù)。它通過最小化誤差的平方和尋找數(shù)據(jù)的最佳函數(shù)匹配，利用最小二乘法可以簡便地求得未知的數(shù)據(jù)，并使得這些求得的數(shù)據(jù)與實際數(shù)據(jù)之間誤差的平方和為最小。

例：用分度值為d=0.01mm/m的電子水平儀校準(zhǔn)1000mm平尺，橋板跨距L=100mm，原始數(shù)據(jù)及計算結(jié)果如表1 所示。

表1原始數(shù)據(jù)及計算結(jié)果

原始數(shù)據(jù)(B列)：每個跨距水平儀讀出的數(shù)據(jù)，第一個讀數(shù)填在B3上，在B2里填“—”；

累計讀數(shù)(C列)：在C2里輸入0，作為原始0點的Y坐標(biāo)。依次是各個測量點對零點的偏差(在C3填入=B3+C2，然后往下拉自動填充到C12)；

最小二乘法擬合曲線(D列)：用EXCEL自帶函數(shù)trend()來編輯，它是返回線性回歸擬合線的一組縱坐標(biāo)值(y值)，在D2填上=TREND(C$(此符號對嗎？)2:C$12,A$2:A$12,A2)，然后往下拉自動填充到D12，在D2到D12就會出現(xiàn)最小二乘擬合曲線的y值；

最小二乘法直線度偏差(E列)：累計讀數(shù)減去擬合曲線y值，即為各測量點對擬合y值的偏差(在E2填入=C2-D2，然后往下拉自動填充到E12)。

在E15處填入公式：=MAX(E2:E12)-MIN(E2:E12)，會自動得出最小二乘直線度。

根據(jù)公式，直線度F=d×L×Δ=(0.01×100×42.3)μm=42.3μm。

1.2　最小條件法

以包容實際直線且距離為最小的兩平行直線或兩平行平面間的距離為其直線度。

同樣以表1為例子，“最小區(qū)域法擬合曲線”(F列)就是以最小條件法計算出來的y值坐標(biāo)(在F2填入=$H$2*A2，往下拉到自動填充到F12)；

“最小區(qū)域直線度偏差”(G列)就是各測量點對最小條件擬合曲線y值的偏差(在G2填入=C2-F2，往下拉到自動填充到G12)；

“最小區(qū)域斜率”列就是相對應(yīng)的斜率(表1中是H2格)。

設(shè)最小條件的擬合曲線為y=kx+b，然后倒過來看看最小條件法的數(shù)學(xué)模型其實是minf()=min (maxΔy-minΔy)，式中，Δy為各點與最小條件擬合曲線的偏差。所以在表1的G15中應(yīng)填入=max (G2:G12)-min(G2:G12)，這個格得出的數(shù)就是最后所求的結(jié)果。

擬合曲線的截距b可以任取一個值，這個對計算沒有影響。在例中b=0。

這里的關(guān)鍵計算點就是如何計算出兩條平行線的最佳斜率k。這里用到EXCEL2003版中的“規(guī)劃求解”功能，這個選項能在菜單“工具”中找到。如果在工具菜單中沒有找到“規(guī)劃求解”，則先點“工具”，再點“加載宏”，勾選“規(guī)劃求解”，確定之后“規(guī)劃求解”就能在工具菜單中顯示了?！耙?guī)劃求解”就是通過迭代來算出一組有關(guān)聯(lián)的數(shù)據(jù)的最佳值，然后依次填入數(shù)據(jù)，如圖1。在“設(shè)置目標(biāo)單元格”選中G15，“等于”選擇“最小值”，“可變單元”選中H2，因為H2就是需要求的數(shù)值并且能顯示，“約束”就是計算條件，這里我選擇 -1000 ≥H2≤1000，基本已經(jīng)涵蓋所有斜率了。最后點擊求解，經(jīng)過自動計算，H2格顯示8.7(參見表1)，這就是需要求的斜率；同時G15格顯示36.6，這就是最小條件所求的結(jié)果，同時在F列和G列顯示出正確的數(shù)據(jù)。

圖1

按直線度公式F=d×L×Δ=(0.01×100×36.6)μm=36.6μm算出了最終的結(jié)果。

2　平面度的計算

2.1　對角線求法

這里以JJG 117—2005附錄上的例子，其對角線平面度偏差如表2所示。

在表2上A列和1行分別是Y坐標(biāo)值和X坐標(biāo)值。該平面的對角線平面度為7.3μm。

2.2　最小條件法

對于平板最小條件原則的評定，是以包容平板實際工作面且距離為最小的兩平行平面間的距離為平板工作面平面度。該平面度可按對角線評定的測量結(jié)果進行“基面轉(zhuǎn)換”求得。經(jīng)基面轉(zhuǎn)換后，平板工作面上最高點數(shù)值與最低點數(shù)值之差作為測量結(jié)果。

從表2中可以看出，經(jīng)過對角線法求出的平面跟最小條件的理想平面實際上還有一定旋轉(zhuǎn)量偏差，需要沿X軸和Y軸旋轉(zhuǎn)，X軸和Y軸均為假想軸。

表2　對角線平面度偏差

假設(shè)橫向為X軸，縱向為Y軸。先旋轉(zhuǎn)X軸，再旋轉(zhuǎn)Y軸。這里與直線度的最小條件求法相同，不同的是二維數(shù)據(jù)，所以同樣可用“規(guī)劃求解”來實現(xiàn)旋轉(zhuǎn)。由于現(xiàn)在所用“規(guī)劃求解”是一維的，如果有二維以上的“規(guī)劃求解”的軟件，當(dāng)然計算起來就比較方便了。現(xiàn)在用一維來解決二維問題，對X軸和Y軸分別求“規(guī)劃求解”也能得到最小區(qū)域平面度，但是多數(shù)情況是需要人為參與，使平面度逐步減少達到最小區(qū)域平面度。

擬合的最小區(qū)域平面的坐標(biāo)值如表3所示。表4的數(shù)據(jù)是由表2的數(shù)據(jù)減去表3的數(shù)據(jù)得來的。F最小=2.35-(-4.2)=6.55μm。與檢定規(guī)程JJG 117—2005的結(jié)果完全一致。

表3　擬合的最小區(qū)域平面的坐標(biāo)值

在表3 I2欄內(nèi)寫入=H$2*A2+H$1*B$1，在J2欄內(nèi)寫入=H$2*A2+H$1*C$1…直至在M欄內(nèi)寫入=H$2*A2+H$1*F$1。這是將第一行的X坐標(biāo)值和Y坐標(biāo)值乘以X方向和Y方向的旋轉(zhuǎn)斜率，構(gòu)成擬合平面第一行的坐標(biāo)值。再將I列至M列向下拉，至第6行，就可以得到擬合平面所有各點的坐標(biāo)值。如果X、Y的斜率滿足最小條件，該平面就是最小區(qū)域平面。在O2欄內(nèi)寫入=B2-I2，并從O2拉至S6，就可以得到該平面對最小區(qū)域平面的偏差值。在H3欄內(nèi)寫入=max(O2:S6)-min(O2:S6)。先將X、Y的斜率置零，然后再旋轉(zhuǎn)X軸，用X得出初步結(jié)果列在表5上。圖2的可變單元為H1，即X方向斜率。從表5中可以看出已經(jīng)有兩個等值最大點，同時在兩個最高點連線兩側(cè)存在兩個不等的極小值點，要是這兩個極小值點數(shù)值趨于相等，要使Y軸斜率為負(fù)值。平面度也由7.3μm下降到7.0μm。

表4　平面對最小區(qū)域平面的偏差值

圖2　X方向“規(guī)劃求解”

表5　X方向初步旋轉(zhuǎn)結(jié)果

在H2欄內(nèi)寫入-0.4，并再次使用X方向的“規(guī)劃求解”，得到表6。這時兩個極小值點數(shù)值更趨于接近。平面度也下降到6.6μm。當(dāng)Y軸斜率為-0.45時，再次使用X方向的“規(guī)劃求解”，就得到表3和表4 的結(jié)果，得到最小區(qū)域平面度為6.55μm。這樣計算比起檢定規(guī)程介紹的方法已經(jīng)簡便多了。當(dāng)然“規(guī)劃求解”能升級到二維或多維，計算最小區(qū)域平面度方便多了。

表6　X方向再次旋轉(zhuǎn)結(jié)果

3　結(jié)語

本文探討利用EXCEL解決直線度、平面度的計算問題，直線度問題解決得比較好。在平面度方面還需要人為參與，但在計算上已經(jīng)改進不少，或許有不嚴(yán)謹(jǐn)和不準(zhǔn)確之處，請大家指出。在上述例子中，特別值得注意的是利用“規(guī)劃求解”求平面度的時候，從左到右為X軸，從上至下為Y軸來進行旋轉(zhuǎn)得出最終結(jié)果屬于極少的特例。在實際求解中，絕大多數(shù)是不能得出最終結(jié)果。需要人為參與才能得出最終結(jié)果。所以用“規(guī)劃求解”得出最終結(jié)果還有一定局限性和約束性。主要是現(xiàn)在的“規(guī)劃求解”是一維的，如何升級到二維或多維“規(guī)劃求解”，這是今后繼續(xù)研究和開發(fā)的方向和目標(biāo)。

在實際工作和學(xué)習(xí)中，EXCEL的確給我們帶來了方便，我們需要多多利用新式軟件或程式，提高工作學(xué)習(xí)效率。在上述求平板平面度的例子中，可以完全自動生成數(shù)據(jù)和自動判別等級，利用vlookup()、lookup()和offset等函數(shù)來實現(xiàn)，但由于篇幅限制和比較繁瑣，未一一列舉。

[1]費業(yè)泰，主編. 誤差理論與數(shù)據(jù)處理. 北京：機械工業(yè)出版社，2004

[2]國防科工委科技與質(zhì)量司. 幾何量計量. 北京：原子能出版社，2002

[3]平板檢定規(guī)程. JJG 117—2005. 北京：中國計量出版社，2005

[4]平尺校準(zhǔn)規(guī)范. JJF 1097—2003. 北京：中國計量出版社，2003

[5]周慶麟, 王建發(fā)，等. EXCEL應(yīng)用大全. 北京：人民郵電出版社，2008

[6]濮良貴, 紀(jì)名剛,等. 機械設(shè)計. 北京：高等教育出版社,2006

[7]岳武陵 楊杰 朱志松 空間直線度誤差的快速評定.計量技術(shù)，2001(3)