内插值计算

内插值计算在工程中有广泛的应用，在其他行业也有应用，如财务管理中的利率查表等。为理解内插值计算的含义，举个简单例子：下表中，B值是与A值对应的，如当A值＝10时，B值＝239.41,问当A=12时，B=?表一如果只是查A列中已有的值（如A=10和A=15)，用VLOOKUP函数就可简单解决，而对于A=12则要进行插值计算AB0211.992218.47插值的方法是：找到A列中与12最接近的两个值：10和15,先计算：15-10=53221.5再找到B列中对应的两个值：239.41和249.73，再计算：249.73-239.41=10.325227.16把上面两个结果相除，表示在这个范围内，A每增长1个单位，B增长10.32/5=2.0648234.78综合上述，完整公式就是：10239.41当A=12时，B=239.41+(249.73-239.41)/(15-10)*(12-10)=243.53815249.73我们在这里的思路是－－20258.79把整个数据表分成若干段，每段内进行线性插值的计算，得到结果25266.85为什么要分段？因为整体数据并不是线性的。为了说明这点，我们把左表画成图（选用平滑线散点图）：30274.0835280.64图一40286.7445292.3750297.760307.370316.0380323.89图中可见：整个数据区连起来后是一条曲线，但每两个点之间都可以近似看作一段直线，误差很小（如果连这样的误差也需要考虑减小的话，看本文的后面其他更进一步的办法）按照这个思路，我们就可以直接转换为用公式计算的方法：12B=243.538（注：这里的计算范围为=0，80)公式为：=LOOKUP(B37,A8:B24)+(INDEX($A$8:$B$24,W+1,2)-LOOKUP(B37,A8:B24))/(INDEX(A8:B24,W+1,1)-INDEX(A8:A24,W,1))*(B37-INDEX(A8:A24,W,1))其中定义名称W=MATCH(内插值计算!$B$37,内插值计算!$A$8:$A$24,1)这是一个最“原始”的算法，完全模拟了手工计算。当然还有其他的公式写法，但都离不开模拟手工计算实际上，EXCEL已经提供了计算线性插值的函数。这就是TREND()内插值计算手工计算的思路：计算方法1050100150200250300350020格式为：TREND(known_y's,known_x's,new_x's,const)其中第一和第二参数为已知y值和已知x值，分别对应于上表中的B列和A列而第三参数则为需要求插值的数值，对应于上面的A=12函数更详细的说明见帮助文件。使用这个函数时，我们要注意：如果我们按帮助文件中的举例来作，把公式写成：TREND(B8:B24,A8:A24,12)将得不到正确的值原因在于：TREND是求一条线性回归拟合线的值，而我们表一并不是线性关系，图一也不是一条直线所以我们必须仍然按照上面的思路把曲线分割成若干直线段来计算B=243.538公式：=TREND(INDIRECT(B&W+7&:&B&W+8),INDIRECT(A&W+7&:&A&W+8),B37)这是不是简单多了？上面说到，由于图一不是直线，所以我们采用分段计算。如果是直线，怎么样？看下例：表二在表二中，要求计算：温度=-196度时，电阻值=?温度电阻值-196-20018.49电阻值＝20.2008-19022.767公式：=TREND(B62:B65,A62:A65,C61)-18027.044这个公式这么简单，原因是表二的数据是完全线性的，看图二：-17031.321顺便说说：如果真是完全的线性，上面的公式2a还可以计算超出表二范围的数据如温度值为－100度，则电阻值＝61.26这就是“外插值”，也叫“外推”。条件是外推区间关系不变。所以在应用TREND函数时，应注意：当数据是线性相关时，可以取整个数据区，否则应分段计算我们注意到图二是一条直线，那么我们自然就想到：能不能写出这条直线的解析式？有了解析式，问题肯定简单得多！我们知道，直线的解析式是y=ax+b，怎么求a，b？这就要用到excel函数的另一个函数：LINEST()格式为：LINEST(known_y's,known_x's,const,stats)其中后两个参数省略的情况下，即可返回直线方程的斜率和截距表三-19022.767-18923.1947对于表二的数据，我们有以下的计算：-18823.6224-18724.0501ab-18624.47780.4277104.03-18524.9055这样，直线方程就是：y＝0.4277x＋104.03-18425.3332-18325.7609有这样一个温度－电阻的表达式，计算不就太简单了吗！见左表三计算方法2计算方法2a计算方法3LINEST函数TREND函数-18226.1886-18126.6163-18027.044那么，对于不是直线的表一、图一，还能用类似办法吗？能！我们用表一数据来说明：图三第三步：点击“趋势线格式”中的“选项”，勾选其中的“显示公式”、“显示R平方值”确定。第四步，我们看到在图三中出现一个公式和一个R值由于R值1,我们重新回到“趋势线格式”的“类型”对话框重新选取阶数,使得R2尽量接近1当选到阶数＝6时，我们得到图四：图四计算方法4050100150200250300350020406080100350表四计算结果系数AB上面的这个图表中给出的公式，即可用来计算插值0211.99212.022218.47218.46-8.78744E-103221.5221.472.47137E-075227.16227.12-2.86078E-058234.78234.780.001830558其中的系数也可以用公式法来求，这样计算方便，精度也得以保证10239.41239.43-0.07852947215249.73249.803.368981529公式及计算结果见左侧表四。20258.79258.79212.024903425266.85266.79根据这个公式，可求得（见C172）30274.08274.04当A＝12时，B=243.7735280.64280.6640286.74286.7645292.37292.4150297.7297.6760307.3307.2870316.03316.0580323.89323.8912243.77上面的做法归纳起来就是：根据数据表作出趋势线，并令R2→1，再用公式求出系数，从而得到一个计算插值的完整公式实际上，上述方法已经不是“插值”了，它是求出源数据组的拟合函数，用这个函数直接求出对应值。理论上，如果拟合程度很好的话，它比任何插值方法都有更高的准确度。原表y=-0.00000000088x6+0.00000024714x5-0.00002860783x4+0.00183055817x3-0.07852947195x2+3.36898154985x+212.02490298744R²=0.9999990717405010015020025030035001020304050607080==============================================除此之外，excel还提供了很多类似用途的函数，如SLOPE斜率INTERCEPT截距FORECAST线性回归CORREL求相关系数GROWTH指数拟合LOGEST指数回归PEARSONpearson乘积矩相关系数RSQpearson乘积矩相关系数的平方灵活运用这些函数，可使插值问题得到很好的解决。多说一句：上面的讨论只是局限于excel范围，但实际上excel并不能完全、全面地解决插值问题。要想更好地计算插值，应该使用专用工具。好了，就说到这里吧。再向下说，就进入“回归分析”的范围了。===============================================点击查看相关帖子：wenou的还有welcpa的《多元线性回归》homerzhang的牛顿插值法第一个自然是：chenjun的《图表趋势线公式系数的计算公式》内插值计算在工程中有广泛的应用，在其他行业也有应用，如财务管理中的利率查表等。下表中，B值是与A值对应的，如当A值＝10时，B值＝239.41,问当A=12时，B=?如果只是查A列中已有的值（如A=10和A=15)，用VLOOKUP函数就可简单解决，而对于A=12则要进行插值计算插值的方法是：找到A列中与12最接近的两个值：10和15,先计算：15-10=5再找到B列中对应的两个值：239.41和249.73，再计算：249.73-239.41=10.32把上面两个结果相除，表示在这个范围内，A每增长1个单位，B增长10.32/5=2.064当A=12时，B=239.41+(249.73-239.41)/(15-10)*(12-10)=243.538把整个数据表分成若干段，每段内进行线性插值的计算，得到结果为什么要分段？因为整体数据并不是线性的。为了说明这点，我们把左表画成图（选用平滑线散点图）：图中可见：整个数据区连起来后是一条曲线，但每两个点之间都可以近似看作一段直线，误差很小（如果连这样的误差也需要考虑减小的话，看本文的后面其他更进一步的办法）（注：这里的计算范围为=0，80)=LOOKUP(B37,A8:B24)+(INDEX($A$8:$B$24,W+1,2)-LOOKUP(B37,A8:B24))/(INDEX(A8:B24,W+1,1)-INDEX(A8:A24,W,1))*(B37-INDEX(A8:A24,W,1))=MATCH(内插值计算!$B$37,内插值计算!$A$8:$A$24,1)这是一个最“原始”的算法，完全模拟了手工计算。当然还有其他的公式写法，但都离不开模拟手工计算实际上，EXCEL已经提供了计算线性插值的函数。这就是TREND()406080100TREND(known_y's,known_x's,new_x's,const)其中第一和第二参数为已知y值和已知x值，分别对应于上表中的B列和A列如果我们按帮助文件中的举例来作，把公式写成：TREND(B8:B24,A8:A24,12)将得不到正确的值原因在于：TREND是求一条线性回归拟合线的值，而我们表一并不是线性关系，图一也不是一条直线所以我们必须仍然按照上面的思路把曲线分割成若干直线段来计算=TREND(INDIRECT(B&W+7&:&B&W+8),INDIRECT(A&W+7&:&A&W+8),B37)上面说到，由于图一不是直线，所以我们采用分段计算。如果是直线，怎么样？看下例：图二=TREND(B62:B65,A62:A65,C61)这个公式这么简单，原因是表二的数据是完全线性的，看图二：顺便说说：如果真是完全的线性，上面的公式2a还可以计算超出表二范围的数据这就是“外插值”，也叫“外推”。条件是外推区间关系不变。我们注意到图二是一条直线，那么我们自然就想到：能不能写出这条直线的解析式？（其中a为斜率，b为截距）LINEST(known_y's,known_x's,const,stats)其中后两个参数省略的情况下，即可返回直线方程的斜率和截距有这样一个温度－电阻的表达式，计算不就太简单了吗！见左表三05101520253035-210-200-190-180-170-160第一步，按上面的办法作出图（图三）第二步，在图中的曲线上点击右键选择“添加趋势线”类型为多项式，阶数选2这里，R平方值称为“决定系数”表示趋势线与原数据的拟合程度R2=1,表示完全拟合。越接近于1,表示拟合程度越好我们可以通过改变曲线类型、阶数来提高拟合的程度上面的这个图表中给出的公式，即可用来计算插值其中的系数也可以用公式法来求，这样计算方便，精度也得以保证公式及计算结果见左侧表四。根据这个公式，可求得（见C1