Excel共享 | 軟件資訊

分享兩個(gè)EXCEL函數(shù)與數(shù)據(jù)區(qū)間查詢有關(guān)的公式套路，學(xué)會(huì)了你也是高手

SEP技術(shù)支持 | 2020/5/22 0:00:00

推薦使用：Excel協(xié)作編輯軟件(免費(fèi)下載，像Excel一樣極速上手，可靈活自定義的企業(yè)管理軟件)

今天給大家分享的EXCEL知識(shí)是兩個(gè)函數(shù)式的套路，它們與數(shù)據(jù)區(qū)間查詢有關(guān)。
查找函數(shù)
第一個(gè)函數(shù)是熟悉的LOOKUP。
例如，如下圖所示，區(qū)域A1:B6是一個(gè)績效評(píng)估標(biāo)準(zhǔn)表格，不及格少于60分，及格大于或等于60分且少于80分，如果大于或等于80分且少于90分則為優(yōu)秀，依此類推。

現(xiàn)在我們要評(píng)判一個(gè)同學(xué)的表現(xiàn)。
G2 單元格輸入以下公式并填寫：
=LOOKUP(F2，A$2:B$6)
假設(shè)這位同學(xué)是女漢子EH在線培訓(xùn)課程《[圖表之美》的大紅花，她的圖表分?jǐn)?shù)為98，那么她的最終評(píng)價(jià)是：優(yōu)秀。
假設(shè)洪華在某一天因缺人而考了68分，公式返回的結(jié)果是：通過考試。
應(yīng)該注意的是，這個(gè)查找例程，即
檢查
搜索區(qū)域中的第一列數(shù)據(jù)需要按升序排列，否則公式可能會(huì)得到錯(cuò)誤的結(jié)果。
FREQUENCY函數(shù)?
第二個(gè)功能——Frequency——很少在微信上介紹給每個(gè)人。
在EXCEL函數(shù)中，F(xiàn)REQUENCY是一個(gè)運(yùn)算效率高、功能極其強(qiáng)大的函數(shù)。它在條件計(jì)數(shù)、查詢、排序等方面有許多奇妙的用途。然而，它也是一個(gè)困難的功能，所以很少有人使用它。到什么程度？晚上，呼吸著純凈的煙霧，你可以抬頭數(shù)天上的星星。
今天，讓我們簡單介紹一下Frequency間隔查詢的一個(gè)例程。有興趣的家長可以去ExcelHome論壇為這個(gè)功能充電。
讓我們?cè)倥e一個(gè)例子。

如上圖所示，單元格區(qū)域A1:B6仍然是性能評(píng)估標(biāo)準(zhǔn)表。
F2:F3區(qū)域仍然是兩個(gè)大紅花的記錄?，F(xiàn)在，她仍然需要兩個(gè)分?jǐn)?shù)。
看起來和上面的例子沒有什么不同，但是需要解釋的是評(píng)分標(biāo)準(zhǔn)。
得分標(biāo)準(zhǔn)不是甲0-9，乙10-14.等等，但是要找到最接近的值，然后得到結(jié)果。
例如，《大紅花》的第一個(gè)分?jǐn)?shù)是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í)。
簡單地說，誰靠近你誰就屬于你?！钦嬲膯渭?xì)胞生物女漢子。
G2輸入公式并向下填充：
=LOOKUP(1,0/FREQUENCY(0,ABS(A$2:A$6-F2)),B$2:B$6)
簡要說明這個(gè)公式的操作過程。

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ū)間，計(jì)算Frequency為0。
由于Frequency函數(shù)僅在段點(diǎn)第一次出現(xiàn)時(shí)計(jì)算Frequency，并且統(tǒng)計(jì)值小于或等于該段點(diǎn)且大于前一段點(diǎn)的Frequency，因此由0返回的Frequency計(jì)算位置總是在最接近0的段點(diǎn)。在本例中，該線段點(diǎn)為2，計(jì)數(shù)為1，剩余的線段點(diǎn)計(jì)數(shù)為0。
仍然得到一個(gè)存儲(chǔ)陣列：
{0;0;1;0;0;0}
對(duì)我那些不知道Frequency的朋友來說，上面這段話就像一本天書。那么，F(xiàn)RANCITY函數(shù)在最接近它的值處拋出0。從那時(shí)起，0將屬于該值。它值0，等于1。從那以后，他們過著幸福的生活。其他人什么也沒有得到，所以他們都返回0并且玩光棍——。嗯，事情就是這樣。
最后，還有另一個(gè)查找例程：
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所對(duì)應(yīng)的目標(biāo)區(qū)域結(jié)果。
此處的LOOKUP目標(biāo)區(qū)域?yàn)锽$2:B$6，因此得分13，返回15所對(duì)應(yīng)的B4的值C。
此外，應(yīng)該注意的是，F(xiàn)REQUENCY函數(shù)支持無序分割間隔，因此評(píng)分區(qū)域不必按升序排列。
結(jié)束語
這是今天的兩個(gè)與間隔查詢相關(guān)的函數(shù)例程分享。至于Frequency部分，如果你理解它，你就能理解它。即使你不明白，這些慣例也會(huì)在關(guān)鍵時(shí)刻被應(yīng)用。如果你不理解它，那也沒什么。

如有侵權(quán)請(qǐng)聯(lián)系我們 370

上一篇：分享兩個(gè)常用的Excel函數(shù)，一個(gè)是LOOKUP函數(shù)，另外一個(gè)是SUMPRODUCT函數(shù)，不僅實(shí)用，效率也十分高下一篇：「伙伴云」伙伴云免費(fèi)版和標(biāo)準(zhǔn)版功能有哪些區(qū)別？

相關(guān)主題