應(yīng)用技巧九：妙用LOOKUP函數(shù)升序與亂序查找

LOOKUP函數(shù)具有向量和數(shù)組兩種語法形式。

LOOKUP(lookup_value,lookup_vector,result_vector)

LOOKUP(lookup_value,array)

向量語法是在由單行或單列（也就是“向量”）構(gòu)成的第二參數(shù)中查找第一參數(shù)，并返回第三參數(shù)中對應(yīng)位置的值咒劲。數(shù)組語法的第二參數(shù)可以使單行或單列油狂，也可以是多行或多列忧风，此時LOOKUP函數(shù)會根據(jù)第二參數(shù)的尺寸執(zhí)行類似VLOOKUP函數(shù)或HLOOKUP函數(shù)升序查找的功能勒魔。

LOOKUP函數(shù)要求第二參數(shù)（如為數(shù)組語法，則是第二參數(shù)的首行或首列）按升序排列朝聋，并與小于或等于查找值的最大值匹配。

一囤躁、升序查找多個值

例：如圖1所示冀痕，“表1”中加油站名稱按升序排列，要求計算“表2”中3個加油站的銷售業(yè)績之和狸演。

F7單元格的計算公式如下：

{=SUM(LOOKUP(E3:E5,A3:C8))}

思路解析：

該公式要求A列加油站名稱已按升序排序言蛇，LOOKUP函數(shù)公式查找出E3:E5數(shù)據(jù)區(qū)域中3個加油站的業(yè)績?yōu)閧1295;1110;1355}，再進行求和宵距，得到結(jié)果為3760腊尚。

二、多區(qū)間判斷數(shù)值等級

例：如圖2所示满哪，要求根據(jù)《加油站等級標準》婿斥，按年銷售量對D2:G10單元格區(qū)域中的加油站進行等級評定。

根據(jù)年銷售量與加油站等級的對應(yīng)關(guān)系翩瓜，使用常量數(shù)組{0;2000;4000;6000;8000;10000}分別表示從0至2000噸（不含2000噸）受扳、2000噸至4000噸（不含4000噸）……10000噸以上，并以此對應(yīng)B3:B8的加油站等級兔跌。

在G2單元格中輸入如下公式勘高，并將公式向下復(fù)制填充至G10單元格：

=LOOKUP(F2,{0;2000;4000;6000;8000;10000},$B$3:$B$B8)

如果判斷等級的數(shù)據(jù)區(qū)間為向上包含，例如(0,2000）坟桅、(2000,4000)等华望，則可以將第二參數(shù)加上一個小于F列數(shù)值最小當(dāng)量的值（比如F列均為整數(shù)，則0.1不會影響其標準大薪雠摇）來作為等級劃分赖舟，例如如下公式：

=LOOKUP(F2,{0;2000;4000;6000;8000;10000}+0.1,$B$3:$B$B8)

思路解析：

通過兩個公式對比可以看出：利用LOOKUP函數(shù)升序查找并返回小于等于查找值對應(yīng)結(jié)果的原理，當(dāng)查找值為4000時夸楣，第1個公式的常量數(shù)值中小于等于查找值的是第3個元素4000宾抓，因此返回第三參數(shù)的第3個元素即B5的“三級站”子漩；而第2個公式由于加了0.1，則小于等于查找值的最大值為2000石洗，因此返回結(jié)果為B4的“四級站”幢泼。

此類利用LOOKUP函數(shù)進行多個連續(xù)數(shù)值區(qū)間判斷的方法，可以替代IF函數(shù)的復(fù)雜多層嵌套解法讲衫。

三缕棵、亂序查找最后一個滿足條件的記錄

按照LOOKUP函數(shù)的要求，第二參數(shù)的首列（行）必須升序排列涉兽，并且具有“如果LOOKUP找不到lookup_value招驴，則它與lookup_vector中小于或等于lookup_value的最大值匹配”的特性。當(dāng)所要查找的值大于被查找區(qū)域的所有同類型數(shù)據(jù)時枷畏，LOOKUP函數(shù)將返回最后一個與第一參數(shù)類型相匹配的值别厘。

例：如圖3所示，A列中由數(shù)值矿辽、空單元格丹允、錯誤值、文本等多種數(shù)據(jù)組成袋倔，要求取出最后一個文本雕蔽、最后一個數(shù)值和最后一條記錄。

分別在C1:C3單元格中輸入以下公式：

公式1? =LOOKUP(“々”,A:A)

公式2? =LOOKUP(9E+307,A:A)

公式3? =LOOKUP(1,0/(A2:A10<>””),A2:A10)

思路解析：

公式1使用符號” 々”最為查找值宾娜，一般可以滿足查找最后一個文本記錄的需求批狐，但當(dāng)數(shù)據(jù)中有以” 々”字開頭的字符串時，可以使用如下公式：

=LOOKUP(PEPT(“々”,9),A:A)

也就是可以查找以9個連續(xù)的“々”字開頭的文本（一般不會有這樣的記錄）前塔。

公式2使用一個接近Excel規(guī)范與限制允許鍵入最大數(shù)值的數(shù)嚣艇，即9*10^307作為查找值，可滿足查找最后一個數(shù)值（含日期华弓、時間）記錄的需要食零。

公式3則以0/(A2:A10<>””)構(gòu)建一個0、#DIV/0!組成的數(shù)組寂屏，再用大于第二參數(shù)中所有數(shù)值的1（已經(jīng)足夠大）最為查找值贰谣，即可滿足查找最后一個滿足A2:A10不為空單元格條件的記錄。

可以歸納為如下內(nèi)容：

=LOOKUP(1,0/(條件),目標區(qū)域或數(shù)組)

其中迁霎，條件可以是多個邏輯判斷相乘組成的多條件數(shù)組吱抚。

四、填補合并單元格的空缺

例：如圖4所示考廉，A列的片區(qū)采用合并單元格方式秘豹，要求統(tǒng)計“東區(qū)”的加油站個數(shù)。

在A13單元格中輸入片區(qū)名稱昌粤，在B14單元格中輸入數(shù)組公式既绕，即可得到加油站個數(shù)：

{=SUM(--(LOOKUP(ROW(2:10),IF(A2:A10<>””,ROW(2:10)),A2:A10)=A13))}

思路解析：

在此例中啄刹，A列合并單元格實際只有A2、A6岸更、A9這3個單元格有數(shù)據(jù)（示意如D列）鸵膏，其他單元格都是空單元格膊升。因此不能直接用COUNTIF函數(shù)統(tǒng)計“東區(qū)”的個數(shù)怎炊，而需要將A3:A5、A7:A8廓译、A10單元格分別填補數(shù)據(jù)后再統(tǒng)計评肆。

在公式中，利用IF函數(shù)判斷非区，返回A列非空單元格的行號與FALSE瓜挽，再利用LOOKUP函數(shù)查找ROW(2:10)構(gòu)成的行號數(shù)組{2;3;4;5;6;7;8;9;10}，根據(jù)“如果LOOKUP找不到lookup_value征绸，則它與lookup_vector中小于或等于lookup_value的最大值匹配”的特性久橙，例如查找行號4，則與之匹配的小于等于4的最大值為2管怠，由此返回對應(yīng)的A2單元格的“東區(qū)”淆衷，其他行同理類推。從而渤弛，LOOKUP函數(shù)構(gòu)建了一個9行的數(shù)組祝拯，效果如圖中F列所示。最后她肯，再使用SUM函數(shù)統(tǒng)計該數(shù)組中等于A13單元格值的個數(shù)佳头。