在散点图上求插值点、交点、特征点.docVIP

问题1：在EXCEL绘制的如下散点图中，给定任一条曲线上X点，求该曲线上对应Y值。 问题2：在EXCEL绘制的如下散点图中，求“污染曲线”和“损失曲线”的交点。 问题3：在EXCEL绘制的如下散点图中，求“错配物曲线”的最低点，即Y值最小时的点。 目的：会编简单的VBA函数，掌握使用网上提供的VBA函数，掌握规划求解。 一、如何编写自己的函数，并在EXCEL中调用 1、打开VB编辑器。按Alt＋F11，或“工具”－＞“宏”－＞“Visual Basic编辑器” 2、在Visual Basic编辑器中插入“模块”，在模块中建立VBA函数。 3、在EXCEL中按格式调用。 例：编写自己的线性插值函数。 ①、在模块中写入如下VBA代码 线性插值 Option Base 1 Function Liner(Xarr As Range, Yarr As Range, X As Single) As Single Liner = (Yarr(2) - Yarr(1)) * (X - Xarr(1)) / (Xarr(2) - Xarr(1)) + Yarr(1) End Function ②、在待求单元格中输入线性插值函数公式：“=Liner(B2:B3,C2:C3,3)” 注意调用函数时与定义时参数要一致。 作业：编写一个三点抛物线插值函数，并调用。 二、会用互联网上EXCEL画平滑曲线散点图的算法BezierFit函数，并使用该函数查找散点图上任意点的坐标（函数值）。 1、贝塞尔插值函数说明： ①、函数原型：Function BezierFit(known_x, known_y As Range, known_value, Optional StartKnot As Long = 1, Optional known_value_type As Variant = x) As Variant ②、函数功能：查找散点图曲线上任意点的坐标(函数值) ③、传入参数：至少要输入三个参数，第一个是X坐标系列，然后是Y坐标系列，第三个是待查数值 第四个参数是从哪一段曲线开始查找，如果曲线可以返回多个值，那么分别指定起始节点就可以找出全部合要求的点 第五个参数是待查数值的类型,x代表输入x坐标求对应y坐标，y则相反,t是直接输入贝塞尔插值多项式的参数 ④、返回值：包含六个元素的数组，分别是满足条件的三个点的X,Y坐标 2、与INDEX函数例：假设根据 a1:a4的数值作为X值,b2:b4的数值作为Y值，画了一个平滑线散点图。想查找c1的数值是不是在这条曲线上，输入如下公式： =Index( BezierFit(a1:a4,b1:b4,c1) ,1,1) 得到曲线上第一个 X值=C1数值的点的X坐标 =Index( BezierFit(a1:a4,b1:b4,c1) ,1,2) 得到曲线上第一个 X值=C1数值的点的Y坐标 =Index( BezierFit(a1:a4,b1:b4,c1) ,1,3) 得到曲线上第二个 X值=C1数值的点的X坐标 =Index( BezierFit(a1:a4,b1:b4,c1) ,1,4) 得到曲线上第二个 X值=C1数值的点的Y坐标 =Index( BezierFit(a1:a4,b1:b4,c1) ,1,5) 得到曲线上第三个 X值=C1数值的点的X坐标 =Index( BezierFit(a1:a4,b1:b4,c1) ,1,6) 得到曲线上第三个 X值=C1数值的点的Y坐标 3、互联网原文：/viewthread.php?tid=135621 4、例：见EXCEL文件。 三、利用BezierFit函数及规划求解求解曲线交点、最低点（Y最小点）、最高点（Y最大点）。 （一）、求交点。 用规划求解。给一X值，用BezierFit函数分别求出“污染曲线”上对应Y值（Y1），“损失曲线”上对应Y值（Y2），则相交的条件为Y1＝Y2。见EXCEL例。 1、以B34单元格作为X值（可变），在C34单元格输入公式“=INDEX(BezierFit(A4:A10,B4:B10,B34,1,x),1,2)”，求污染曲线对应Y值；在C35单元格输入公式“=INDEX(BezierFit(A4:A10,C4:C10,B34,1,x),1,2)”，求损失曲线对应Y值。 2、打开规划求解对话框。 ①、设置可变单元格为B34。 ②、添加约束条件。如图所示前两个约束条件为限制可变单元格B34的取值范围在曲线X的取值范围内。③、单击“求解”，若两直线有相交，则会在B34单元格处显示相交点的X值。 （二）、求最低点（曲线上Y最小点） 用规划求解。给一X值，求该曲线上对应Y值，直到对所X求出的Y值最小。 1、以B33单元格作为