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