陈建锋

(深圳天祥质量技术服务有限公司广州分公司，广州 510663)

0　引言

目前市场上有专门的软件可以帮助计算直线度和平面度，甚至有检测系统或仪器可以直接给出结果，但是这并不利于初学者对计算的理解。而具有强大功能的EXCEL办公软件使用起来方便灵活，能个性化设计和编辑。本文介绍使用EXCEL办公软件自动计算直线度和平面度，其中包括最小二乘法、最小条件法还有其他EXCEL内置函数。文中介绍的技巧可以自动计算直线度、平板平面度和等级，生成2D或3D图表以供阅读者更直观的理解。经使用，所作出的模板方便简单，平常可用于原始数据记录等多项工作。

1　直线度的计算

1.1　最小二乘法

最小二乘法(又称最小平方法)是一种数学优化技术。它通过最小化误差的平方和寻找数据的最佳函数匹配，利用最小二乘法可以简便地求得未知的数据，并使得这些求得的数据与实际数据之间误差的平方和为最小。

例：用分度值为d=0.01mm/m的电子水平仪校准1000mm平尺，桥板跨距L=100mm，原始数据及计算结果如表1 所示。

表1原始数据及计算结果

原始数据(B列)：每个跨距水平仪读出的数据，第一个读数填在B3上，在B2里填“—”；

累计读数(C列)：在C2里输入0，作为原始0点的Y坐标。依次是各个测量点对零点的偏差(在C3填入=B3+C2，然后往下拉自动填充到C12)；

最小二乘法拟合曲线(D列)：用EXCEL自带函数trend()来编辑，它是返回线性回归拟合线的一组纵坐标值(y值)，在D2填上=TREND(C$(此符号对吗？)2:C$12,A$2:A$12,A2)，然后往下拉自动填充到D12，在D2到D12就会出现最小二乘拟合曲线的y值；

最小二乘法直线度偏差(E列)：累计读数减去拟合曲线y值，即为各测量点对拟合y值的偏差(在E2填入=C2-D2，然后往下拉自动填充到E12)。

在E15处填入公式：=MAX(E2:E12)-MIN(E2:E12)，会自动得出最小二乘直线度。

根据公式，直线度F=d×L×Δ=(0.01×100×42.3)μm=42.3μm。

1.2　最小条件法

以包容实际直线且距离为最小的两平行直线或两平行平面间的距离为其直线度。

同样以表1为例子，“最小区域法拟合曲线”(F列)就是以最小条件法计算出来的y值坐标(在F2填入=$H$2*A2，往下拉到自动填充到F12)；

“最小区域直线度偏差”(G列)就是各测量点对最小条件拟合曲线y值的偏差(在G2填入=C2-F2，往下拉到自动填充到G12)；

“最小区域斜率”列就是相对应的斜率(表1中是H2格)。

设最小条件的拟合曲线为y=kx+b，然后倒过来看看最小条件法的数学模型其实是minf()=min (maxΔy-minΔy)，式中，Δy为各点与最小条件拟合曲线的偏差。所以在表1的G15中应填入=max (G2:G12)-min(G2:G12)，这个格得出的数就是最后所求的结果。

拟合曲线的截距b可以任取一个值，这个对计算没有影响。在例中b=0。

这里的关键计算点就是如何计算出两条平行线的最佳斜率k。这里用到EXCEL2003版中的“规划求解”功能，这个选项能在菜单“工具”中找到。如果在工具菜单中没有找到“规划求解”，则先点“工具”，再点“加载宏”，勾选“规划求解”，确定之后“规划求解”就能在工具菜单中显示了。“规划求解”就是通过迭代来算出一组有关联的数据的最佳值，然后依次填入数据，如图1。在“设置目标单元格”选中G15，“等于”选择“最小值”，“可变单元”选中H2，因为H2就是需要求的数值并且能显示，“约束”就是计算条件，这里我选择 -1000 ≥H2≤1000，基本已经涵盖所有斜率了。最后点击求解，经过自动计算，H2格显示8.7(参见表1)，这就是需要求的斜率；同时G15格显示36.6，这就是最小条件所求的结果，同时在F列和G列显示出正确的数据。

图1

按直线度公式F=d×L×Δ=(0.01×100×36.6)μm=36.6μm算出了最终的结果。

2　平面度的计算

2.1　对角线求法

这里以JJG 117—2005附录上的例子，其对角线平面度偏差如表2所示。

在表2上A列和1行分别是Y坐标值和X坐标值。该平面的对角线平面度为7.3μm。

2.2　最小条件法

对于平板最小条件原则的评定，是以包容平板实际工作面且距离为最小的两平行平面间的距离为平板工作面平面度。该平面度可按对角线评定的测量结果进行“基面转换”求得。经基面转换后，平板工作面上最高点数值与最低点数值之差作为测量结果。

从表2中可以看出，经过对角线法求出的平面跟最小条件的理想平面实际上还有一定旋转量偏差，需要沿X轴和Y轴旋转，X轴和Y轴均为假想轴。

表2　对角线平面度偏差

假设横向为X轴，纵向为Y轴。先旋转X轴，再旋转Y轴。这里与直线度的最小条件求法相同，不同的是二维数据，所以同样可用“规划求解”来实现旋转。由于现在所用“规划求解”是一维的，如果有二维以上的“规划求解”的软件，当然计算起来就比较方便了。现在用一维来解决二维问题，对X轴和Y轴分别求“规划求解”也能得到最小区域平面度，但是多数情况是需要人为参与，使平面度逐步减少达到最小区域平面度。

拟合的最小区域平面的坐标值如表3所示。表4的数据是由表2的数据减去表3的数据得来的。F最小=2.35-(-4.2)=6.55μm。与检定规程JJG 117—2005的结果完全一致。

表3　拟合的最小区域平面的坐标值

在表3 I2栏内写入=H$2*A2+H$1*B$1，在J2栏内写入=H$2*A2+H$1*C$1…直至在M栏内写入=H$2*A2+H$1*F$1。这是将第一行的X坐标值和Y坐标值乘以X方向和Y方向的旋转斜率，构成拟合平面第一行的坐标值。再将I列至M列向下拉，至第6行，就可以得到拟合平面所有各点的坐标值。如果X、Y的斜率满足最小条件，该平面就是最小区域平面。在O2栏内写入=B2-I2，并从O2拉至S6，就可以得到该平面对最小区域平面的偏差值。在H3栏内写入=max(O2:S6)-min(O2:S6)。先将X、Y的斜率置零，然后再旋转X轴，用X得出初步结果列在表5上。图2的可变单元为H1，即X方向斜率。从表5中可以看出已经有两个等值最大点，同时在两个最高点连线两侧存在两个不等的极小值点，要是这两个极小值点数值趋于相等，要使Y轴斜率为负值。平面度也由7.3μm下降到7.0μm。

表4　平面对最小区域平面的偏差值

图2　X方向“规划求解”

表5　X方向初步旋转结果

在H2栏内写入-0.4，并再次使用X方向的“规划求解”，得到表6。这时两个极小值点数值更趋于接近。平面度也下降到6.6μm。当Y轴斜率为-0.45时，再次使用X方向的“规划求解”，就得到表3和表4 的结果，得到最小区域平面度为6.55μm。这样计算比起检定规程介绍的方法已经简便多了。当然“规划求解”能升级到二维或多维，计算最小区域平面度方便多了。

表6　X方向再次旋转结果

3　结语

本文探讨利用EXCEL解决直线度、平面度的计算问题，直线度问题解决得比较好。在平面度方面还需要人为参与，但在计算上已经改进不少，或许有不严谨和不准确之处，请大家指出。在上述例子中，特别值得注意的是利用“规划求解”求平面度的时候，从左到右为X轴，从上至下为Y轴来进行旋转得出最终结果属于极少的特例。在实际求解中，绝大多数是不能得出最终结果。需要人为参与才能得出最终结果。所以用“规划求解”得出最终结果还有一定局限性和约束性。主要是现在的“规划求解”是一维的，如何升级到二维或多维“规划求解”，这是今后继续研究和开发的方向和目标。

在实际工作和学习中，EXCEL的确给我们带来了方便，我们需要多多利用新式软件或程式，提高工作学习效率。在上述求平板平面度的例子中，可以完全自动生成数据和自动判别等级，利用vlookup()、lookup()和offset等函数来实现，但由于篇幅限制和比较繁琐，未一一列举。

[1]费业泰，主编. 误差理论与数据处理. 北京：机械工业出版社，2004

[2]国防科工委科技与质量司. 几何量计量. 北京：原子能出版社，2002

[3]平板检定规程. JJG 117—2005. 北京：中国计量出版社，2005

[4]平尺校准规范. JJF 1097—2003. 北京：中国计量出版社，2003

[5]周庆麟, 王建发，等. EXCEL应用大全. 北京：人民邮电出版社，2008

[6]濮良贵, 纪名刚,等. 机械设计. 北京：高等教育出版社,2006

[7]岳武陵 杨杰 朱志松 空间直线度误差的快速评定.计量技术，2001(3)