陳建鋒

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

0　引言

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

1　直線度的計(jì)算

1.1　最小二乘法

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

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

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

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

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

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

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

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

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

1.2　最小條件法

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

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

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

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

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

擬合曲線的截距b可以任取一個(gè)值，這個(gè)對(duì)計(jì)算沒(méi)有影響。在例中b=0。

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

圖1

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

2　平面度的計(jì)算

2.1　對(duì)角線求法

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

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

2.2　最小條件法

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

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

表2　對(duì)角線平面度偏差

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

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

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

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

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

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

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

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

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

3　結(jié)語(yǔ)

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

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

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

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

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

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

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

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

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