看見星光 Excel之家ExcelHome 1周前

表親們，早上好，很久不見了，像想念靜靜一樣想念你們（此處省略好幾萬字……）。嗯，打個(gè)響指，說正事唄，今天和大家分享的EXCEL知識是兩個(gè)函數(shù)公式套路，有關(guān)數(shù)據(jù)區(qū)間查詢的。

LOOKUP函數(shù) 

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

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

現(xiàn)在我們要對某個(gè)同學(xué)的成績做出評判。

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

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

假設(shè)這位同學(xué)是EH在線培訓(xùn)課程【圖表之美】的女漢子大紅花，她圖表成績得了98分，那么她的最終評價(jià)是：優(yōu)異。

假設(shè)某天大紅花因?yàn)樗寄钅橙?，?dǎo)致發(fā)揮嚴(yán)重失常，只考了68分，那么該公式返回的結(jié)果為：及格。

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

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

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

第二個(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ù)天上的星星就知道咧。

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

依然舉個(gè)例子。

如上圖，A1:B6單元格區(qū)域依然是一份成績評價(jià)標(biāo)準(zhǔn)表。

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

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

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

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

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

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

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

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

ABS(A$2:A$6-F2)部分，計(jì)算得出A2:A6區(qū)域和F2之間差的絕對值（正數(shù)和零），得到一個(gè)內(nèi)存數(shù)組：

{13;3;2;12;17}

Frequency函數(shù)，以ABS函數(shù)的計(jì)算結(jié)果為分段區(qū)間，對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}

上面那段話對于不懂FREQUENCY的小伙伴們而言簡直如同天書，好吧，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作為LOOKUP的查找值，即可快速得出0所對應(yīng)的目標(biāo)區(qū)域結(jié)果。

此處的LOOKUP目標(biāo)區(qū)域?yàn)锽$2:B$6，因此得分13，返回15所對應(yīng)的B4的值C。

此外需要說明的是，F(xiàn)REQUENCY函數(shù)支持分段區(qū)間亂序，所以并不需要得分區(qū)域必須升序排列。

這就是今天和大家分享的兩個(gè)有關(guān)區(qū)間段查詢的函數(shù)套路，關(guān)于FREQUENCY部分，能懂就懂，不懂就算，套路嘛，關(guān)鍵時(shí)刻會套用就好，理不理解其實(shí)也沒啥。

我是男神，我是星光，喜歡我，就伸出大拇指支持我吧！

圖文制作：看見星光