借助函數(shù)實(shí)現(xiàn)條件查詢

王志軍

實(shí)際工作中，我們經(jīng)常會(huì)涉及條件查詢的問(wèn)題，在創(chuàng)建公式時(shí)，可以借助LOOKuP、FREQuENCY函數(shù)實(shí)現(xiàn)，這里舉兩個(gè)例子進(jìn)行介紹：

實(shí)例1：利用LOOKUP函數(shù)實(shí)現(xiàn)區(qū)間查詢

例如圖1所示的工作表，A1：B6區(qū)域是一張成績(jī)?cè)u(píng)價(jià)標(biāo)準(zhǔn)表，小于60不及格，大于等于60小于80為及格，大于等于80小于90為良好…，依此類(lèi)推?，F(xiàn)在需要在G列對(duì)某位同學(xué)的成績(jī)進(jìn)行評(píng)判。

選擇G2單元格，在編輯欄輸入公式“=L00KuP（F2，A$2：B$5）”，LOOKuP函數(shù)的查詢思路是“LOOKuP（查找值，查找區(qū)域）”，注意查找區(qū)域的首列必須按照升序排列，本例的查詢區(qū)域是A$2：B$5，公式執(zhí)行之后向下拖曳或雙擊填充柄，很快就可以得到如圖2所示的評(píng)判結(jié)果。

實(shí)例2：利用FREQUENCY函數(shù)實(shí)現(xiàn)接近查詢

如圖3所示，數(shù)據(jù)與例一相仿，但評(píng)判標(biāo)準(zhǔn)有變化，要求查找最接近的值，然后得出評(píng)判結(jié)果。此時(shí)我們需要利用FREQuENCY函數(shù)實(shí)現(xiàn)接近查詢的要求，在G2單元格輸入如下公式：=LOOKuP（1，0/FREQUENCY（O，ABS（A$2：A$6-F2）），B$2：B$6）

上述公式的“ABS（A$2：A$6-F2）”部分，可以計(jì)算得出A2：A6區(qū)域和F2之間差的絕對(duì)值（正數(shù)和零），得到一個(gè)內(nèi)存數(shù)組：{13;3;2;12;17）;FREQuENCY函數(shù)以ABS函數(shù)的計(jì)算結(jié)果為分段區(qū)間，對(duì)0進(jìn)行計(jì)頻，由于FREQuENCY函數(shù)只在分段點(diǎn)首次出現(xiàn)時(shí)統(tǒng)計(jì)頻數(shù)，且統(tǒng)計(jì)小于等于此分段點(diǎn)、大于上一分段點(diǎn)的頻數(shù)，所以0所返回的計(jì)頻位置，總是處于最接近0的那個(gè)分段點(diǎn)。本例中這個(gè)分段點(diǎn)是2，計(jì)數(shù)為1，其余分段點(diǎn)，計(jì)數(shù)為0，計(jì)算之后依然得到一個(gè)內(nèi)存數(shù)組：{0;0;1;0;0;0）。

最后再利用LOOKUP進(jìn)行查詢，“O/FREQUENCY（0，ABS（A$2：A$6-F2））”構(gòu)建一個(gè)由O和錯(cuò)誤值#DIV/O！組成的數(shù)組，再用永遠(yuǎn)大于0的1作為L(zhǎng)OOKUP的查找值，即可快速得出O所對(duì)應(yīng)的目標(biāo)區(qū)域結(jié)果，此處的LOOKUP目標(biāo)區(qū)域?yàn)锽$2：B$6，因此得分13，返回15所對(duì)應(yīng)的B4的值C。

需要指出的是，F(xiàn)REQUENCY函數(shù)支持分段區(qū)間亂序，所以并不需要得分區(qū)域必須升序排列，最終結(jié)果如圖4所示。