位置:首頁 > 軟件操作教程 > 辦公軟件 > Excel > 問題詳情

怎么使用Excel函數(shù)實現(xiàn)Excel數(shù)據(jù)分段區(qū)間查找?

提問人:周麗平發(fā)布時間:2021-07-19

LOOKUP函數(shù)

image.png

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

舉個例子,如下圖,A1:B6區(qū)域是一張成績評價標(biāo)準(zhǔn)表。用我拿手的中文翻譯一下,小于60不及格,大于等于60小于80為及格,大于等于80小于90為優(yōu)良……依次類推。

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

G2單元格輸入下面的公式,并向下填充:

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

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

假設(shè)某天大紅花因為思念男友某人,導(dǎo)致發(fā)揮嚴重失常,只考了68分,那么該公式返回的結(jié)果為:及格。

需要說明的是,LOOKUP函數(shù)的這個查詢套路,即

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

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

Frequency函數(shù)

image.png

第二個函數(shù)微信里平時很少給大家介紹,是Frequency。

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

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

依然舉個例子。

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

F2:F3區(qū)域依然是EH培訓(xùn)班圖表之美的高材生大紅花同學(xué)的兩次得分記錄,現(xiàn)在,依然需要對她的兩次得分進行評級。

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

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

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

簡單的說,就是靠近誰,就屬于誰——真是一個單細胞的女漢子啊。

G2輸入公式,并向下填充。

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

簡單說明一下這個公式的運算過程。

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

{13;3;2;12;17}

Frequency函數(shù),以ABS函數(shù)的計算結(jié)果為分段區(qū)間,對0進行計頻。由于frequency函數(shù)只在分段點首次出現(xiàn)時統(tǒng)計頻數(shù),且統(tǒng)計小于等于此分段點,大于上一分段點的頻數(shù),所以0所返回的計頻位置,總是處于最接近0的那個分段點,本例中這個分段點是2,計數(shù)為1,其余分段點,計數(shù)為0。依然得到一個內(nèi)存數(shù)組:

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

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

最后又是一個LOOKUP的查詢套路:

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

0/FREQUENCY(0,ABS(A$2:A$6-F2)),構(gòu)建一個由0和錯誤值#DIV/0!組成的數(shù)組,再用永遠大于0的1作為LOOKUP的查找值,即可快速得出0所對應(yīng)的目標(biāo)區(qū)域結(jié)果,此處的LOOKUP目標(biāo)區(qū)域為B$2:B$6,因此得分13,返回15所對應(yīng)的B4的值C。

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

繼續(xù)查找其他問題的答案?

回復(fù)(0)
返回頂部