如何用Excel计算平面度？

有时候我们已经知道了零件表面上多个点的点坐标，在没有三坐标软件的情况下，怎样计算平面度呢？

比如，已知平板（见图1）上五个点（P1-P5）的点坐标，基于这5个点，如何计算上表面的平面度呢？见图1。

图1 平板上的点坐标和点

图1中，很多小伙伴会认为，在现实中没那么复杂，用高度尺测量每个点的高度（或者直接用Z坐标），高度差就是平面度。这样正确吗？

事实上，用高度差是错误的计算方法！平面度的本质是关心被测平面上各被测点之间的相互差异，并不关心各被测点和其他特征之间的差异。如果用高度差算，这个高度差会把上表面与下底面的平行误差也引进来了，而这个平行误差原本和上表面的平面度误差无关。

那么正确的方法怎么做呢？本文将详细介绍这个问题的处理办法。比如如何用最小二乘法处理点坐标数据，以及如何计算平面度。

本篇文章将分成3个部分来讲解：

1. 平面度计算原理

2. 用Excel拟合出最小二乘平面

3. 平面度计算的具体实现

想知道平面度误差计算原理和具体实现方法的小伙伴请耐心看下去。

一．平面度计算原理

   见图2，理论上讲，平面度的正确计算方法应该是，先将在被测表面上采集的点组用切比雪夫法拟合成一个平面，这个平面就叫切比雪夫平面，见图2中绿色平面。再用与切比雪夫平面平行的两个平面（图2中红色平面）去“夹”采集的点组，这时由两红色面夹出来的区域就是所谓的最小区域，该区域的宽度D就是被测零件平面度的几何误差，即平面度的实测值。

图2 平面度的正确评价方法

以上概念在本公众微信号德辉学堂的另外一篇文章《切比雪夫法与测量的本质》中有详细介绍，有兴趣的小伙伴可以点击文章最后的链接学习。

   而本篇文章基于Excel的特点（Excel中没有对应的切比雪夫算法），将采用最小二乘法代替切比雪夫法，基于上述的同样原理来计算平面度（所以本文的计算方法是一种近似的计算方法）。用最小二乘平面代替切比雪夫面（图3中绿色面），用平行于最小二乘平面的两个平面（图3中红色平面）去夹被测要素（5个点），夹出来的宽度D1则为平面度的几何误差（平面度的实际测量值）。

图3 最小二乘法计算平面度原理

那么这个夹出来区域的宽度D1应该怎么计算呢？见图3中的H1和H2，它们分别是最小二乘平面两侧的最远的采集点到最小二乘平面的距离，显然有：

D1 = H1 + H2

也就是说，我们知道了H1和H2，这个宽度D1就计算出来了。

这个宽度D1就是实际的平面度测量值。

所以计算平面度的步骤主要分三步：

1. 拟合出最小二乘平面；

2. 找到最小二乘平面两侧的最远采集点到该最小二乘平面的距离H1,H2；

3.  通过计算出来最远点的距离H1和H2相加得到宽度D1，即平面度的实测值；

二．用Excel拟合出最小二乘平面

首先我们来了解一下什么是“拟合”。

图4 拟合的过程

见图4，已知有一堆散点，我们通过一定的游戏规则把他拟合成一条直线y=mx+k，比如说最小二乘法，这个游戏规则则是“平方和最小”，通过最小二乘法可以将这些散点拟合成一条直线方程y=mx+k，它是一条在坐标系中具体的一条直线，也就是该直线方程中的两个参数m和k是明确的。

在拟合之前，我们并不知道m和k是多少，通过拟合后，我们就确定了m和k的具体值。

所以，本质上讲，直线方程的拟合过程，就是在满足一定游戏规则的前提下，求直线方程两个参m和k的过程！

本公众微信号德辉学堂另外一篇文章《传说中的最小二乘法》有详细介绍，有兴趣的小伙伴可以点击文章最后的链接。

再说一遍，拟合就是计算目标函数（如刚刚提到的直线）的参数m和k的过程！

回到主题，那么用Excel怎么实现所谓的“拟合”呢（别告诉我你电脑上没有Excel）？

Excel里有个很有用的函数Linest(), 这个函数就是用最小二乘法来拟合线性函数的，比如说直线y=mx+k，它采用最小二乘法对一堆散点进行拟合。比如采用Linest()函数对图4中的散点进行拟合，Linest()返回的值就是图4中m和k的具体值。

看到没？高逼格的最小二乘法被Excel的一个函数就轻松搞定！

怎么用这个最小二乘法拟合函数Linest()呢？

这个不能唬弄过去，我们还是静下来对linest()函数来个仔细研究吧，因为后边会用到。

Linest()函数的完整格式是：

LINEST(known_y's, [known_x's], [const], [stats])

括号里第一个参数known_y’s是已知的一堆y值，比如说图4中所有散点的y坐标，第二个参数know_x’s是y值对应的x值，比如图4中所有散点的x坐标。Const 和Stats是函数返回值的设置，我们设置Const为true, 设置Stats为false，该函数就能返回m和k值。

   废话少说，直接上一个图演示一下吧：

图5 Excel最小二乘法拟合直线

图5中，红框部分就是在A11和B11中输入的公式（注意，这里是输入数组公式，不是单个框格的公式，所以要同时选中A11和B11，再输入公式，最后还得再来一个骚操作，Ctrl+Shift+Enter，就可以搞定）。而虚线椭圆部分则是该公式返回的结果（5，3），即通过最小二乘法对点P1-P5进行拟合，拟合出来的直线是：

y=5x+3

大家看到了？拟合过程就是在求目标函数的参数m和k的过程！而Excel的函数Linest()通过最小二乘法拟合后，就是返回给我们想要的参数！

上面讲的是直线拟合（可以用来计算直线度），可是现在我们真正需要的是面的拟合，怎么办呢？

幸运的是，linest()能拟合的函数，不仅仅可以采用最小二乘法拟合直线，还可以采用最小二乘法拟合多维的线性函数，即拟合成多元一次方程。比如：

y=m1*x1 + m2*x2 + … + mn*xn + k

上公式中，y为因变量，x1,x2,…xn为自变量（表示有n个自变量），m1,m2…mn,k为函数的参数。

公式中的维数我们取n=2, 即上面公式则为：

y=m1*x1 + m2*x2 + k

将此公式做一些名字上的改变，将因变量y名字改为z，自变量x1名字改为x，自变量x2为y, 则上面的公式可以写成：

z=m1*x + m2*y + k

这个公式是不是有点熟悉？没错，它就是平面方程！也就是说那个牛逼的函数Linest()也能拟合出平面来。

我们知道了5个点的三维坐标P(X,Y,Z), 然后通过函数Linest（）拟合可以得到最小二乘平面，即我们可以得到三个参数m1, m2, k。

   还是直接上图吧：

图6 Excel用最小二乘法拟合平面

同样，红框部分是在A11,B11,C11中输入的公式（注意，同时选中A11,B11,C11三个框格，再输入红框处公式，骚操作Ctrl+Shift+Enter）, 而该公式在A11,B11,C11中返回的计算的结果，即m2,m1,k的具体值。

   也就是说，最小二乘平面就这样给拟合出来了！见图7。

图7 Excel拟合最小二乘法平面

拟合出来后又怎么办呢？

算出每个点到该平面的距离，然后就可以计算平面度了。继续看第三章吧。

三．平面度计算的具体实现

接下来我们来看看如何用Excel具体实现。

第一步：首先输入已知点P1,P2,P3,P4,P5的坐标，然后利用Linest()拟合成平面，得到平面方程，即得到平面方程的参数m1,m2,K. 见下图

图8 用已知点拟合出平面方程

因为拟合出来的方程格式是：

z=m1*x+m2*y+k             (3-1)

这个方程格式很不爽，需要将该平面方程(3-1) 转化成标准的一般平面方程 Ax + By + Cz + D = 0

为什么呢？转化后的好处在于，后边计算每个点到该平面距离的时候可以直接套用标准公式。

转化后的一般平面方程为：

m1*x + m2*y + (-1)z + k = 0

即有：A=m1, B=m2, C=-1，D=k    (3-2)

第二步，计算每个采样点到最小二乘平面的距离di：

   空间中任意一个点P（x0,y0,z0）到平面Ax + By + Cz + D = 0的距离公式为：

上式中的A,B,C,D用（3-2）中的等式代替即可。注意这个距离是个矢量，所以有正负号（正好表示与平面的法向同向，负号则表示相反），见图9示意和图10计算。

图9 计算每个点到最小二乘平面距离di

图10 Excel计算每个点到最小二乘平面距离

   第三步，计算平面度。平面度的几何误差实际上就是用和最小二乘平面平行的两个平面去夹5个点，夹出来的宽度D1即为实测平面度。具体见图11。

图11 平面度计算的几何含义

   又因为第二步计算出来的距离有方向，所以直接采用最大的距离减去最小的距离（含方向）即可以得到图11中的区域宽度D1（D1=0.077）. 具体计算见图12。

图12 Excel实现平面度计算

平面度几何误差的计算就这么被搞定!

好了，本期的文章就到这里，希望对你有帮助。有不想自己动手的小伙伴，可以扫描文章最后二维码加入QQ群可下载Excel原文件。

本文小结：

本文主要讲述了在已知被测平面上几个点P的具体坐标的前提下，如何用Excel计算该面的平面度误差的操作原理和方法。本文分为三个部分进行讲解，第一章主要讲解计算平面度的基本原理，首先将已知点拟合成一个平面，再计算每个点到该平面的距离，距离的总宽度即表示平面度。第二章讲解了如何利用Excel的函数Linest()拟合出最小二乘平面以及具体的参数设置。第三章讲解了用Excel具体的计算过程和实现办法。

再次申明，本文采用的是最小二乘法来代替切比雪夫法，是一种近似替代（要是Excel有切比雪夫法的拟合函数就好了）。因为采用切比雪夫拟合出平面后，夹出的的区域宽度D一定是最小的（所以采用切比雪夫法拟合后得到区域的方法，又叫最小区域法），把这个宽度D作为平面度的几何误差也是标准规范的。所以本文中计算出来的平面度误差D1会比规范的计算值D会大一点点。

即：D1≥D,

有兴趣的小伙伴可以用三坐标对比Excel计算试试看。很多三坐标比如PC-DMIS，Polyworks等，他们评价平面度的方法就是规范的最小区域法。

也就是说，采用本方法算出来的平面度误差如果是合格的，那么按照完全标准的的方法计算出来的平面度一定是合格的（有误废的可能性）。

最后，现在很多测量设备已经非常先进，比如三坐标软件都能轻易的用标准的计算方法算出平面度误差。就现实来说，本文提供的计算方法的实用意义不会太大，但是可以帮助有兴趣的发烧友们了解评价几何误差时的计算原理和计算过程。

加入文章后边的QQ群可以下载本文的Excel表格哦。

觉得本文不错？转发个朋友圈呗！

   关注德辉学堂，一直都在进步哦。

关注公众号

相关链接：