學(xué)習(xí)教程：在excel中如何進(jìn)行區(qū)間查詢

LOOKUP函數(shù)

第一個(gè)函數(shù)是大家耳熟能詳?shù)腖OOKUP。

舉個(gè)例子，如下圖，A1:B6區(qū)域是一張成績(jī)?cè)u(píng)價(jià)標(biāo)準(zhǔn)表，小于60不及格，大于等于60小于80為及格，大于等于80小于90為優(yōu)良……依次類推。

現(xiàn)在我們要對(duì)某個(gè)同學(xué)的成績(jī)做出評(píng)判。

G2單元格輸入下面的公式，并向下填充：

=LOOKUP(F2,A$2:B$6)

假設(shè)這位同學(xué)大紅花，她圖表成績(jī)得了98分，那么她的最終評(píng)價(jià)是：優(yōu)異。

假設(shè)某天發(fā)揮嚴(yán)重失常，只考了68分，那么該公式返回的結(jié)果為：及格。

需要說明的是，LOOKUP的這個(gè)查詢套路，即

LOOKUP（查找值,查找區(qū)域）

要求查找區(qū)域的首列數(shù)據(jù)升序排列，否則公式可能得出錯(cuò)誤的結(jié)果。

FREQUENCY函數(shù)

第二個(gè)函數(shù)微信里平時(shí)很少給大家介紹，是FREQUENCY。

在EXCEL函數(shù)里，F(xiàn)REQUENCY是一個(gè)運(yùn)算效率很高、功能異常強(qiáng)大的函數(shù)，在條件計(jì)數(shù)、查詢、排序等方面都有很多妙用，但它又是一個(gè)難點(diǎn)函數(shù)，以至于用的人很少，少到什么地步？夜晚的時(shí)候，呼吸著純凈的霧霾，你抬頭數(shù)數(shù)天上的星星就知道咧。

今天先簡(jiǎn)單介紹一個(gè)FREQUENCY區(qū)間查詢的套路，感興趣的親們可以到ExcelHome論壇對(duì)這個(gè)函數(shù)自我充電下。

依然舉個(gè)例子。

如上圖，A1:B6單元格區(qū)域依然是一份成績(jī)?cè)u(píng)價(jià)標(biāo)準(zhǔn)表。

F2:F3區(qū)域依然是大紅花同學(xué)的兩次得分記錄，現(xiàn)在，依然需要對(duì)她的兩次得分進(jìn)行評(píng)級(jí)。

看起來似乎和上面的例題并無區(qū)別，但需要說明的是評(píng)分標(biāo)準(zhǔn)。

評(píng)分標(biāo)準(zhǔn)并不是0-9得A，10-14得B……諸如此類，而是尋找最接近的值，進(jìn)而得出結(jié)果。

比如大紅花的首次得分為13，13距離10相差3，距離15相差2，結(jié)論，13更靠近15，所以，大紅花的評(píng)級(jí)結(jié)果為15所對(duì)應(yīng)的C級(jí)，而非10所對(duì)應(yīng)的B級(jí)。

簡(jiǎn)單的說，就是靠近誰，就屬于誰——真是一個(gè)單細(xì)胞的女漢子啊。

G2輸入公式，并向下填充：

=LOOKUP(1,0/FREQUENCY(0,ABS(A$2:A$6-F2)),B$2:B$6)

簡(jiǎn)單說明一下這個(gè)公式的運(yùn)算過程。

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。

依然得到一個(gè)內(nèi)存數(shù)組：

{0;0;1;0;0;0}

上面那段話對(duì)于不懂FREQUENCY的小伙伴們而言簡(jiǎn)直如同天書，好吧，F(xiàn)REQUENCY函數(shù)將0扔到了最接近它的那個(gè)值身上，從此以后0就歸那個(gè)值了，那個(gè)值得到了一個(gè)0，計(jì)數(shù)為1，從此過上了幸福的生活，其他人啥都沒得到，所以都返回0，打了光棍——嗯，就是這么回事。

最后又是一個(gè)LOOKUP的查詢套路：

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

0/FREQUENCY(0,ABS(A$2:A$6-F2))，構(gòu)建一個(gè)由0和錯(cuò)誤值#DIV/0!組成的數(shù)組，再用永遠(yuǎn)大于0的1作為L(zhǎng)OOKUP的查找值，即可快速得出0所對(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ū)域必須升序排列。

更多相關(guān)閱讀