腦洞大開(kāi)!7種另類(lèi)的區(qū)間取值的方法,打開(kāi)你新世界的大門(mén)!
?
作者:E圖表述來(lái)源:部落窩教育發(fā)布時(shí)間:2019-12-16 17:30:21點(diǎn)擊:2516
編按:
哈嘍,大家好!在前面的文章中,給大家分享了9種常規(guī)的區(qū)間查詢(xún)的方法,不知道大家還有印象嗎?今天我們又來(lái)說(shuō)區(qū)間查詢(xún),不過(guò)這次我們要分享的可不是常規(guī)的方法,是7種另類(lèi)的區(qū)間查詢(xún)的方法,保證小伙伴們是聞所未聞見(jiàn)所未見(jiàn),如同打開(kāi)新世界大門(mén)一般!趕緊來(lái)看看吧!
【前言】
我們之前說(shuō)過(guò)“等級(jí)評(píng)定、區(qū)間取值”的話(huà)題,記得當(dāng)時(shí)給出了三大類(lèi),共九種函數(shù)的操作方法:《老是加班還沒(méi)加班費(fèi)?誰(shuí)讓你不會(huì)excel區(qū)間查詢(xún)的三大套路!》,今天我們接著聊區(qū)間查詢(xún)。只不過(guò)作者E圖表述希望通過(guò)本篇閱讀,可以讓同學(xué)們學(xué)到一些少數(shù)人才會(huì)的“另類(lèi)”操作。
【正文】
既然之前和大家說(shuō)過(guò)關(guān)于區(qū)間取值的方法,那么今天就給大家換換“口味”,不再引用數(shù)字或者文本,本次案例我們來(lái)引用“非字體字符”,是什么呢?見(jiàn)下圖:
“滿(mǎn)天都是小星星”,這樣評(píng)定等級(jí)的方式應(yīng)該不陌生吧,很多的企業(yè)都會(huì)這樣要求,它區(qū)別于“ABCD”式或者百分?jǐn)?shù)形式的評(píng)級(jí),優(yōu)點(diǎn)就在于可以很直觀的看出“孰強(qiáng)孰弱”。當(dāng)然同學(xué)們也可以使用“微圖表”操作,效果是一樣的,只是我們通過(guò)本例,可以了解并練習(xí)一些函數(shù)。話(huà)不多言,直接開(kāi)始。
方法一
G2單元格輸入函數(shù):
=LOOKUP(B3,{0,60,70,80,90,100},$F$3:$F$8)
下拉填充柄將函數(shù)填充至下方單元格,完成。
這是LOOKUP函數(shù)的標(biāo)準(zhǔn)用法,通過(guò)常量數(shù)組{0,60,70,80,90,100}和單元格區(qū)域F3:F8的匹配,達(dá)到區(qū)間取值的效果。只不過(guò),我們這里引出的是“圖形字符”。
方法二
是不是覺(jué)得方法一比較常規(guī)了,那再看看方法二:
G2單元格輸入函數(shù):
=INDEX($F$3:$F$8,MATCH(B3,{0,60,70,80,90,100}))
下拉填充柄將函數(shù)填充至下方單元格,完成。
INDEX+MATCH函數(shù)的經(jīng)典用法,通過(guò)MATCH(B3,{0,60,70,80,90,100})確定“得分”在常量數(shù)組中的序號(hào),作為INDEX被索引的序號(hào),索引出單元格區(qū)域F3:F8中對(duì)應(yīng)的圖形字符。
方法三
上面的兩個(gè)方法,是不是要被有的同學(xué) “吐槽”,沒(méi)有什么新意???其實(shí)基礎(chǔ)的內(nèi)容還是有必要掌握的,不積硅步無(wú)以至千里,下面就應(yīng)同學(xué)們的要求,請(qǐng)看“方法三”:
G2單元格輸入函數(shù):
=TEXT(TEXT(TEXT(B3,"[>=100]★★★★★;[>=90]★★★★☆;0"),"[>=80]★★★☆☆;[>=70]★★☆☆☆;0"),"[>=60]★☆☆☆☆;☆☆☆☆☆")
下拉填充柄將函數(shù)填充至下方單元格,完成。
這個(gè)函數(shù)的用法就不多見(jiàn)了吧。TEXT函數(shù)對(duì)于“單元格值的格式”來(lái)說(shuō)是一個(gè)萬(wàn)能函數(shù),我們選中某個(gè)單元格,按CTRL+1快捷鍵,彈出“設(shè)置單元格格式”窗口。
這里涉及的所有分類(lèi)中,只有一種效果是TEXT函數(shù)實(shí)現(xiàn)不了的,其他的都可以用TEXT函數(shù)實(shí)現(xiàn)。關(guān)于TEXT函數(shù)的話(huà)題比較大,在之前的文章《Excel教程:最魔性的TEXT函數(shù),看一眼就心動(dòng)~》中,我們也介紹過(guò),這里就不展開(kāi)了,下次再給它開(kāi)一個(gè)專(zhuān)題來(lái)討論。
“方法三”是典型的用TEXT函數(shù)代替IF函數(shù)的用法(在之前的文章《如果函數(shù)有職業(yè),TEXT絕對(duì)是變裝女皇!》中有介紹過(guò)),TEXT函數(shù)代替條件函數(shù)用法的語(yǔ)法:
TEXT(數(shù)值,"[條件1]顯示格式;[條件2]顯示格式;否則顯示格式;文本顯示格式"),我們可以看到TEXT函數(shù)的第二參數(shù)有四個(gè)部分組成,其中第四個(gè)格式是“文本格式”,因?yàn)槲覀儽景咐褂玫氖菍?duì)數(shù)字的判斷,所以這個(gè)格式可以省略,變成下面的語(yǔ)法:
TEXT(數(shù)值,"[條件1]顯示格式;[條件2]顯示格式;否則顯示格式"),大家通過(guò)語(yǔ)法可以看出,前兩個(gè)是判斷條件的,當(dāng)滿(mǎn)足時(shí)返回需要的“小星星格式”,如果不滿(mǎn)足的時(shí)候,我們規(guī)定了一個(gè)“0”的格式。在TEXT函數(shù)中這個(gè)“0”不是數(shù)字,而是一個(gè)“占位符”,返回的依然是數(shù)值本身。看到這里,我們需要記住一個(gè)規(guī)則:TEXT函數(shù)作為判斷條件使用的時(shí)候,最多只能判斷兩個(gè)條件。當(dāng)有第三個(gè)、第四個(gè)條件時(shí),我們需要再使用一個(gè)TEXT函數(shù)來(lái)判斷,還有更多的條件,以此類(lèi)推。
再回到我們“方法三”給出的函數(shù),一共6個(gè)條件,所以我們使用了3個(gè)TEXT函數(shù)嵌套判斷,完成了效果。
方法四
跳過(guò)剛剛“燒腦”的方法三,讓我們來(lái)看看輕松一些的方法四。
G2單元格輸入函數(shù):
=MID("★★★★★☆☆☆☆☆",7-MATCH(B3,{0,60,70,80,90,100}),5)
下拉填充柄將函數(shù)填充至下方單元格,完成。
函數(shù)解析:我們列出★★★★★☆☆☆☆☆這樣的一個(gè)圖形字符串,通過(guò)MID函數(shù)“斷位取值”的思路,得到我們的需求。
方法五
在方法四中,我們利用了數(shù)學(xué)的思路,得到了結(jié)果。在方法五中同樣也適用,我們利用OFFSET函數(shù)來(lái)解決這個(gè)需求。
G2單元格輸入函數(shù):
=OFFSET($F$2,MAX(1,INT((B3-60)/10+2)),,,)
下拉填充柄將函數(shù)填充至下方單元格,完成。
INT((B3-60)/10+2)的作用,就是為了確定OFFSET函數(shù)的第二參數(shù)向下移動(dòng)的行數(shù),列表如下:
這樣OFFSET就可以根據(jù)分?jǐn)?shù),確定從基礎(chǔ)單元格F2開(kāi)始,下移的行數(shù),第三參數(shù)為空,默認(rèn)為0;第四、五參數(shù)為空,默認(rèn)為1,這樣就形成了我們上面的函數(shù),得到需求的效果。
方法六
G2單元格輸入函數(shù):
=INDEX(★☆,MAX(1,INT((B3-60)/10+2)))
下拉填充柄將函數(shù)填充至下方單元格,完成。
“方法六”的做法,和“方法五”類(lèi)似,寫(xiě)出這個(gè)案例,主要是為了讓同學(xué)們能夠?qū)W會(huì)一個(gè)“看”函數(shù)的習(xí)慣。在這個(gè)函數(shù)中,有一個(gè)★☆圖形字符串,可是這個(gè)字符串既不是“數(shù)字”,也沒(méi)有加英文狀態(tài)的雙引號(hào),作者E圖表述教給大家一個(gè)經(jīng)驗(yàn):在公式函數(shù)中,如果看到一個(gè)沒(méi)有加英文狀態(tài)雙引號(hào)的“字符串”時(shí),十有八九這是一個(gè)“自定義名稱(chēng)”。我們本方法就是用了這個(gè)技巧。
按CTRL+F3,彈出“名稱(chēng)管理器”窗口,點(diǎn)擊“新建”按鍵,按下圖設(shè)置名稱(chēng)即可,再用MAX+INT的方式確定索引號(hào)(同方法五),再用INDEX索引出對(duì)應(yīng)值即可。
方法七
G2單元格輸入函數(shù):
=REPT("★",MAX(0,(B3-60)/10+1))&REPT("☆",5-INT(MAX(0,(B3-60)/10+1)))
下拉填充柄將函數(shù)填充至下方單元格,完成。
這個(gè)就是今天重點(diǎn)想給大家介紹的一個(gè)函數(shù)——REPT函數(shù),這個(gè)函數(shù)只有兩個(gè)參數(shù),其功能是根據(jù)指定次數(shù)重復(fù)文本錄入。
語(yǔ)法:REPT(TEXT值,重復(fù)的次數(shù))
這里需要注意一點(diǎn):重復(fù)的次數(shù),可以通過(guò)計(jì)算得到,如果得到的是小數(shù),REPT函數(shù)默認(rèn)只取整數(shù)部分,即ROUNDDOWN函數(shù)的效果,例如:
根據(jù)這個(gè)特性,我們上面公式中計(jì)算重復(fù)次數(shù)的方式,才有意義。這里面的“數(shù)學(xué)思維”你能看懂嗎?
【編后語(yǔ)】
EXCEL是統(tǒng)計(jì)、是數(shù)據(jù)分析、是“算數(shù)”的軟件,每一個(gè)EXCELER的操作都應(yīng)該和數(shù)學(xué)有關(guān),即便我們處理文本字符串,這其中有的時(shí)候同樣離不開(kāi)“數(shù)學(xué)的思維”。這個(gè)思路希望可以給各位同學(xué),在工作上帶來(lái)一些新的創(chuàng)意,可以讓工作更輕松,更有“樂(lè)趣”。
本文配套的練習(xí)課件請(qǐng)加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
區(qū)間查找①《老是加班還沒(méi)加班費(fèi)?誰(shuí)讓你不會(huì)excel區(qū)間查詢(xún)的三大套路!》
區(qū)間查找②《比VLOOKUP重要,更容易讓你晉升高手的函數(shù),就包含在這三大經(jīng)典嵌套公式中》
區(qū)間查找③《看看excel絕頂高手用的這些壓箱底查找公式,其實(shí)都是你熟悉但從沒(méi)深入用過(guò)的簡(jiǎn)單東西……》
最熱教程
- 像綠皮火車(chē)一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱(chēng)完成目錄制作
- 延遲退休落地,快速查詢(xún)你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂(yōu) !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)