excel模糊查找二分法详细解析(版权寥若_晨星2013).doc

二分法的概念数学方面牛顿二分法一般地,对于函数 f(x), 如果存在实数 c,当 x=c 时,若 f(c)=0, 那么把 x=c 叫做函数 f(x) 的零点。解方程即要求 f(x) 的所有零点。假定 f(x) 在区间( x,y )上连续先找到 a、b 属于区间( x,y ),使 f(a) , f(b) 异号,说明在区间(a,b) 内一定有零点, 然后求 f[(a+b)/2], 现在假设 f(a)<0,f(b)>0,a<b ①如果 f[(a+b)/2]=0 ,该点就是零点, ②如果 f[(a+b)/2]<0, 则在区间( (a+b)/2 , b) 内有零点, (a+b)/2 赋给 a ,从①开始继续使用中点函数值判断。③如果 f[(a+b)/2]>0 , 则在区间(a,(a+b)/2) 内有零点, (a+b)/2 赋给 b,从①开始继续使用中点函数值判断。这样就可以不断接近零点。当区间小于一定值时,结束迭代过程。通过每次把 f(x) 的零点所在小区间收缩一半的方法, 使区间的两个端点逐步迫近函数的零点,以求得零点的近似值,这种方法叫做二分法。从以上可以看出,每次运算后,区间长度减少一半,是线性收敛。另外,二分法不能计算复根和重根。 E xcel 中二分法的应用明白了上面二分法的意思,那运用在 excel 中 match 、 lookup ……等的模糊查找就好理解了。但在 excel 中的应用又和数学方面有些不同。一、 EXCEL 和数学方面二分法的不同 1、这第一点的不同是在 excel 中所有能模糊查找的函数的第二个参数找不到零点, 所以用中间值代替。中间值等于数组最中间那个值。偶数数组的中间值= 数据个数/2 的值=counta( 数组)/2 的值。奇数数组的中间值=( 数据个数+1)/2 的值= counta( 数组)/2+1/2 的值。 2、这第二点的不同是 excel 中数组中包含文本值、逻辑值、错误值。这些在模糊查找时会被忽略。 3、如果不规定取数精度的话, 数学方面的二分法是永远查找不到值的, 只能无限循环下去, 有些专家就在说二分法是错误的(这里就不做讨论) ,在 excel 里面就没有这方面的考虑。二、 E xcel 中二分法的具体算法(这里就不做流程图了) E xce 中的二分法也和数学牛顿二分法的查找方式一样(线性迭代) ,用 excel 的查找值(函数第一个参数)与目标值(中间值)进行比较,这里就涉及到 4 种情况。情况 1 :查找值和中间目标值相等这时会依次从中间值向右判断是否有连续和中间值相等的最后一个值。例: 情况 2 :查找值小于中间目标值这时会以数组第一个值与中间置为区域进行查找。也就是以中间置为边界向左查找。例: =lookup(1,{1,2,1},{1,2,3})=1 1 第一次查找中间值为 2, 查找值 1<2, 以2 为边界向左查找 2 第二次查找就相当于=lookup(1,{1,2},{1,2}) 这时的中间值为 1, 等于查找值 1. 3 第三次查找这时就和情况 1 相同了, 依次向右判断时候存在连续的 1 ,这题不存在,返回结果 1 =lookup(,{1,2,1},{1,2,3})= #N/A 第一个参数省略,默认为 0 1 第一次查找中间值为 2, 查找值 0<2, 以2 为边界向左查找 2 第二次查