|
前面其中的公式使用了MATCH函數(shù)的近似匹配模式，公式是這樣的， =IF( B13="", "", B13-INDEX( B$2:B12, MATCH(10^9,B$2:B12,1) )) 其中，MATCH函數(shù)引用了B2：B12區(qū)域，該區(qū)域是這樣的，
有好幾位朋友通過各種方式跟我討論：MATCH函數(shù)的近似匹配模式不是需要對(duì)查找區(qū)域進(jìn)行排序嗎？為什么這里的源數(shù)據(jù)沒有排序呢？ 這個(gè)問題涉及到近似匹配的原理的細(xì)節(jié)了。好在這個(gè)原理其實(shí)并不復(fù)雜，這里我就借機(jī)解釋一下。 本文的內(nèi)容對(duì)VLOOKUP，MATCH的近似匹配模式，XLOOKUP和XMATCH函數(shù)的二進(jìn)制搜索模式，LOOKUP函數(shù)等都適用。 首先簡單回歸一下近似匹配模式。 假設(shè)我們需要對(duì)一組同學(xué)的成績進(jìn)行評(píng)估，標(biāo)準(zhǔn)如下： 90 <= grade, "A" 80 <= grade < 90, "B" 70 <= grade < 80, "C" grade < 70, "D"
這時(shí)，我們可以使用VLOOKUP函數(shù)的近似匹配直接在B列中得到每一個(gè)人的評(píng)估結(jié)果。 為了簡單起見，我們只要求返回每一個(gè)人的成績對(duì)應(yīng)標(biāo)準(zhǔn)區(qū)間的下邊界。 就可以使用MTACH函數(shù)的近似匹配： =MATCH(A2,$D$2:$D$5,1)
比如A2的成績?yōu)?2，代表低于70分的區(qū)間，這個(gè)區(qū)間的下邊界就是0，所以返回1，即標(biāo)準(zhǔn)中的第一個(gè)值。 我們知道，當(dāng)MATCH的第三個(gè)參數(shù)設(shè)為1時(shí)，代表使用近似匹配，而且返回的是： 比查找值小的所有值中的最大值。 比如，查找值為：85，Excel就在查找區(qū)域：D2:D5 中查找所有小于85的數(shù)，顯然，這包括前面三個(gè)：{0， 70， 80}，其中最大的是第三個(gè)，因此返回值為3。 為什么要求排序呢？ 這里就必須說到近似匹配的原理了。 近似匹配使用的是二分法。 比如，
j假如我們要在綠色的數(shù)據(jù)中為數(shù)字：35 找到一個(gè)位置插進(jìn)去， 很明顯，這個(gè)位置就在30和35之間。 但是計(jì)算機(jī)怎么才能快速找到呢？ 第一步，計(jì)算機(jī)找到最中間的一個(gè)位置，
因?yàn)榭偣?0個(gè)數(shù)，所以中間位置為第10和11個(gè)數(shù)中間，取中間值為47.5，這個(gè)中間值將原來的數(shù)字分為左右兩部分，每部分只要10個(gè)數(shù)。 然后將中間值與要查找的數(shù)：33比較，顯然有：33 < 47.5。 因?yàn)榫G色數(shù)據(jù)是升序排列的。所以，這個(gè)比較說明35這個(gè)數(shù)的位置一定在左邊。 于是，問題就變成了在更小的一組數(shù)據(jù)中定位 35 的位置：
繼續(xù)采用同樣的方法，
這次，每一部分只有5個(gè)數(shù)，中間值為 22.5。 將中間值與 33 比較，得到 22.5 < 33。 這說明，我們要找的數(shù)在右半部分，
繼續(xù)二分，
這次，只有5個(gè)數(shù)，所以中間數(shù)也只有一個(gè)，就是35，與 33 比較，得到： 33 < 35， 所以要找的數(shù)在左邊，
繼續(xù)二分，
這次，很明顯，中間值是 27.5，與 33 比較，得到： 30 < 33， 因此，要找的位置在右邊，
由于右邊只有一個(gè)數(shù)了，所以它就是我們要找到的位置。 從上邊的示例可以看出，之所以要求查找區(qū)域排序，就是為了可以根據(jù)每次的比較結(jié)果判斷要找的位置在左邊還是右邊。 如果沒有排序，就不能得到這個(gè)結(jié)論。如果還是采用同樣的步驟計(jì)算的話，就有可能不能得到正確結(jié)果了。 VLOOKUP，MATCH，LOOKUP函數(shù)中的近似匹配都是采用得這種二分法。所以都要求排序。 從上面的示例可以看出，這種方法查找速度非?？?。所以，我們會(huì)采用這種原理來優(yōu)化公式計(jì)算速度。（詳細(xì)介紹見Excel中的15個(gè)查找公式速度大比拼！是騾子是馬牽出來遛一遛就知道了。） 其實(shí)，在MATCH函數(shù)的近似匹配的介紹中，近似匹配的行為是： 比查找值小的所有值中的最大值。
要實(shí)現(xiàn)這種行為根本不用排序啊。 確實(shí)，所以，在XLOOKUP函數(shù)和XMATCH函數(shù)中，就有不使用二分法也可以實(shí)現(xiàn)近似匹配的現(xiàn)象。 回到本文開頭的問題，為什么我們不再需要排序了呢？ 這是因?yàn)槲覀円鉀Q的問題與上面的問題是不同的。 上面的問題中，我們要找的是一個(gè)數(shù)字的準(zhǔn)確位置。在這里，我們要找的是最后一個(gè)發(fā)生的數(shù)字：
轉(zhuǎn)換為查找問題的話，就是下圖所示，
我們要查找的數(shù)為：10000，（其實(shí)這個(gè)數(shù)具體是多少不重要，只要大于查找區(qū)域所有樹即可） 我們的目標(biāo)是找到這個(gè)數(shù)在綠色數(shù)組中的位置。 使用二分法，首先找到中間位置，
中間值是50，與查找值進(jìn)行比較，可以得到：50 < 10000， 所以，要找的位置在右邊。 這個(gè)過程可以往下繼續(xù)。由于查找值大于原始數(shù)組中的任意值，所以，查找值大于任何中間值，每次都會(huì)判定要找的位置在右邊。
直到如果中間值為空白單元格。 當(dāng)進(jìn)行比較時(shí)，空白單元格大于任意數(shù)值（你可以自己排序看看，空白單元格在升序中一定排在最后），當(dāng)然也就大于查找值。 所以，這次判定為要找的位置在左邊。
左邊只有一個(gè)數(shù)了，查找過程結(jié)束了，這個(gè)位置就是我們要找的最后一個(gè)數(shù)。 從這個(gè)例子可以看出，只要查找值足夠大，就可以使用二分法找到最后一個(gè)有實(shí)際數(shù)值的位置。 |
|
|
