excel模糊查找二分法詳細(xì)解析(版權(quán)寥若_晨星2013)

1、二分法的概念數(shù)學(xué)方面牛頓二分法一般地，對(duì)于函數(shù)f(x),如果存在實(shí)數(shù)c,當(dāng)x=c時(shí)，若f(c)=0,那么把x=c叫做函數(shù)f(x)的零點(diǎn)。解方程即要求f(x)的所有零點(diǎn)。假定f(x)在區(qū)間（x，y）上連續(xù)先找到a、b屬于區(qū)間（x，y），使f(a)，f(b)異號(hào)，說(shuō)明在區(qū)間(a,b)內(nèi)一定有零點(diǎn)，然后求f(a+b)/2,現(xiàn)在假設(shè)f(a)<0,f(b)>0,a<b如果f(a+b)/2=0，該點(diǎn)就是零點(diǎn)，如果f(a+b)/2<0,則在區(qū)間（(a+b)/2，b)內(nèi)有零點(diǎn)，(a+b)/2賦給a，從開(kāi)始繼續(xù)使用中點(diǎn)函數(shù)值判斷。如果f(a+b)/2>0，則在區(qū)間(a,(a+b)/

2、2)內(nèi)有零點(diǎn)，(a+b)/2賦給b，從開(kāi)始繼續(xù)使用中點(diǎn)函數(shù)值判斷。這樣就可以不斷接近零點(diǎn)。當(dāng)區(qū)間小于一定值時(shí)，結(jié)束迭代過(guò)程。通過(guò)每次把f(x)的零點(diǎn)所在小區(qū)間收縮一半的方法，使區(qū)間的兩個(gè)端點(diǎn)逐步迫近函數(shù)的零點(diǎn)，以求得零點(diǎn)的近似值，這種方法叫做二分法。從以上可以看出，每次運(yùn)算后，區(qū)間長(zhǎng)度減少一半，是線性收斂。另外，二分法不能計(jì)算復(fù)根和重根。Excel中二分法的應(yīng)用明白了上面二分法的意思，那運(yùn)用在excel中match、lookup等的模糊查找就好理解了。但在excel中的應(yīng)用又和數(shù)學(xué)方面有些不同。一、EXCEL和數(shù)學(xué)方面二分法的不同1、這第一點(diǎn)的不同是在excel中所有能模糊查找的函數(shù)的第二個(gè)參

3、數(shù)找不到零點(diǎn)，所以用中間值代替。中間值等于數(shù)組最中間那個(gè)值。偶數(shù)數(shù)組的中間值=數(shù)據(jù)個(gè)數(shù)/2 的值=counta(數(shù)組)/2的值。奇數(shù)數(shù)組的中間值=(數(shù)據(jù)個(gè)數(shù)+1)/2的值= counta(數(shù)組)/2+1/2的值。2、這第二點(diǎn)的不同是excel中數(shù)組中包含文本值、邏輯值、錯(cuò)誤值。這些在模糊查找時(shí)會(huì)被忽略。3、如果不規(guī)定取數(shù)精度的話，數(shù)學(xué)方面的二分法是永遠(yuǎn)查找不到值的，只能無(wú)限循環(huán)下去，有些專(zhuān)家就在說(shuō)二分法是錯(cuò)誤的（這里就不做討論），在excel里面就沒(méi)有這方面的考慮。二、Excel中二分法的具體算法(這里就不做流程圖了)Exce中的二分法也和數(shù)學(xué)牛頓二分法的查找方式一樣（線性迭代），用excel

4、的查找值（函數(shù)第一個(gè)參數(shù)）與目標(biāo)值（中間值）進(jìn)行比較，這里就涉及到4種情況。情況1：查找值和中間目標(biāo)值相等這時(shí)會(huì)依次從中間值向右判斷是否有連續(xù)和中間值相等的最后一個(gè)值。例：情況2：查找值小于中間目標(biāo)值 這時(shí)會(huì)以數(shù)組第一個(gè)值與中間置為區(qū)域進(jìn)行查找。也就是以中間置為邊界向左查找。例:=lookup(1,1,2,1,1,2,3)=1 第一次查找中間值為2,查找值1<2,以2為邊界向左查找 第二次查找 就相當(dāng)于=lookup(1,1,2,1,2) 這時(shí)的中間值為1,等于查找值1. 第三次查找 這時(shí)就和情況1相同了,依次向右判斷時(shí)候存在連續(xù)的1，這題不存在，返回結(jié)果1=lookup(,1,2,1,

5、1,2,3)=#N/A第一個(gè)參數(shù)省略，默認(rèn)為0 第一次查找中間值為2,查找值0<2,以2為邊界向左查找 第二次查找相當(dāng)于=lookup(,1,2,1,2)，中間值1，查找值0<1,以1為邊界向左查找，沒(méi)數(shù)值，查找不到，出錯(cuò)。情況3：查找值大于中間目標(biāo)值 這時(shí)會(huì)以數(shù)組中間置和數(shù)組最后一個(gè)值為區(qū)域進(jìn)行查找。也就是以中間置為邊界向右查找。和情況2相反。情況4：存在文本值、邏輯值、真空、假空和錯(cuò)誤值的情況（以下描述成非純數(shù)值型）。這里的區(qū)別是：查找的中間值如果為文本值、真空、假空、邏輯值和錯(cuò)誤值,中間值自動(dòng)向右變動(dòng)，直到中間目標(biāo)值為純數(shù)字為止，這時(shí)以這個(gè)數(shù)字為中間值和查找值進(jìn)行判斷。例：

6、C1=LOOKUP(17,A1:A19,B1:B19) F1、H1、J1、M1、P1公式同理。如上圖：文本值、真空、假空、邏輯值、錯(cuò)誤值的結(jié)果一致，原理一致。 第一次查找 中間值為第10行的值（依次為：a、 、”、ture、#DIV/0!）,中間值不為純數(shù)字，向右找取第一個(gè)純數(shù)字為13，這時(shí)以13作為中間值。查找值17>13,屬于情況3，以13為邊界向右查找。PS: I、J列單獨(dú)剔出來(lái)講 第二次查找1313a14a15a16a171818a19以上圖作為區(qū)域進(jìn)行第二次查找，中間值為16對(duì)應(yīng)的a，不為純數(shù)字，中間值向右變動(dòng)找到純數(shù)字18，以18作為中間值，查找值17<18,此時(shí)關(guān)鍵點(diǎn)

7、注意。由于非純數(shù)值型全部被忽略，所以相當(dāng)于就剩下13,18了。所以最后結(jié)果返回13對(duì)應(yīng)的值。關(guān)鍵點(diǎn)：在查找中間值的前，非純數(shù)值型是已經(jīng)被忽略的了（原因好像是excel所有函數(shù)是先處理參數(shù)，然后再處理參數(shù)之間的運(yùn)算的，這是我的理解，不知道對(duì)不對(duì)），但數(shù)組的個(gè)數(shù)是沒(méi)有被忽略的，所以中間值還是要從數(shù)組最中間那個(gè)值判斷起的，而且中間值必須要是一個(gè)純數(shù)字才能進(jìn)行判斷。IJ列的情況： 第1次查找中間值為第10行的0，查找值17>0,以中間值0為邊界向右查找 第2次查找01001101213130140150160171818019中間值為14對(duì)應(yīng)的0，查找值17>0, 以中間值0為邊界繼續(xù)向右查找 第3次查找0140150160171818019中間值為16對(duì)應(yīng)的0，查找值17>0, 以中間值0為邊界繼續(xù)向右查找 第4次查找0160171818019中間值為17對(duì)應(yīng)的0，查找值17>0, 以中間值