二維碼 購物車
部落窩在線教育歡迎您!

處理條件判斷的函數(shù)除了IF函數(shù)還有CHOOSE、TEXT 、IFS等六個函數(shù)

?

作者:E圖表述來源:部落窩教育發(fā)布時間:2021-04-07 10:47:03點擊:5991

分享到:
0
收藏    收藏人氣:0人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

編按:

作為EXCEL函數(shù)大家庭中的絕對大佬之一,IF函數(shù)在眾多場合中都有出色的表現(xiàn)。每次遇到需要進行條件判斷的情況時,它總能一馬當(dāng)先,手到擒來!但是,也有小弟不服!比如CHOOSE、TEXT、IFS等,都在挑戰(zhàn)它在“條件判斷”領(lǐng)域中的地位……

 

前言:

如果問任何一位EXCELER,在EXCEL函數(shù)的日常使用中,哪些是你認為的常用函數(shù)呢?筆者相信,無論說出哪些函數(shù)名稱,想必一定會包含IF函數(shù)。別不承認,IF函數(shù)絕對是使用率最高的一個函數(shù)了,甚至超過SUM函數(shù)和VLOOKUP函數(shù)。

但是今天,大家就來看一組實際的案例,本來應(yīng)該是IF出場的,居然被一堆函數(shù)搶了風(fēng)頭。

 

正文:

今天的案例是筆者E圖表述的一個做物料控制部門工作的朋友提供的素材,真實性和實用性還是比較高的,案例如下:

 

 

問題:根據(jù)計劃數(shù)量求實際數(shù)量,以便采購原材料。

計算規(guī)則為:

1.如果“計劃數(shù)量”小于50時,“實際數(shù)量”按最小采購量50記入D列;

2.如果“計劃數(shù)量”大于500時,“實際數(shù)量”按最大采購量500記入D列;

3.如果“計劃數(shù)量”在50500之間,則“實際數(shù)量”按照“計劃數(shù)量”實際值記入D列。

 

【解法一:常規(guī)解法】

看到這樣的問題,大家的第一反應(yīng)應(yīng)該就是使用IF函數(shù)做“多級判斷”處理。沒有任何問題,這就是一個典型的IF函數(shù)案例,答案如下:

 

 

D2單元格輸入函數(shù):=IF(C2>=500,500,IF(C2<=50,50,C2))

這段函數(shù)就不再贅述了,如果大家在部落窩大家庭中,還沒有學(xué)會兩級IF函數(shù)的話,那就報一個系統(tǒng)學(xué)習(xí)的課程吧。

 

接下來,開始真正的表演吧??!

【解法二:最簡解法】

如果說“讓一個數(shù)字和另一個數(shù)字比較后取最大值或者最小值”,對于EXCEL函數(shù)高手來說,往往會采用MIN函數(shù)或者MAX函數(shù),來替代多層IF的寫法,答案如下:

 

 

D2單元格輸入函數(shù):=MAX(50,MIN(C2,500))

 

函數(shù)解析:

MIN(C2,500),在C2單元格值和500之間取最小值,滿足了實際數(shù)量不超過500的取值規(guī)則;再用=MAX(50,X),在50MIN函數(shù)的返回值之間取最大值,得到不小于50的數(shù)值,及此完成需求操作。

 

【解法三:另類解法】

 

 

大家有沒有看“懵”,有沒有內(nèi)心狂吼一句“What is this?”

 

D2單元格輸入函數(shù):=SMALL(CHOOSE({1,2,3},50,500,C2),2)

 

函數(shù)解析:

CHOOSE函數(shù)是在一組數(shù)據(jù)中,指定選擇第幾個的函數(shù),語法如下:

=CHOOSE(index_num,value1,[value2],…)

其中index_num是在value組中指定的序號。

如果大家將index_num的值,換做數(shù)列{1,2,3},那么就意味著,大家要CHOOSE提取value組中的第1、23個值,形成一個新的數(shù)組。

 

D2單元格,就形成了{50,500,408}這樣的一個組列;再使用SMALL函數(shù),提取第2小的值,就取得了≥50 and 500的值。又如D4單元格,CHOOSE函數(shù)返回的數(shù)列是{50,500,754},那么SMAll(數(shù)列,2)就得到了第2小的值500。大家明白了嗎?

 

同理,大家也可以使用LARGE函數(shù)處理,公式如下:=LARGE(CHOOSE({1,2,3},50,500,C2),2),原理都是一樣的,大家可以自己推敲一下。

 

【解法四:創(chuàng)意解法】

 

 

這個公式中雖然也有CHOOSE,但是和上例不一樣,CHOOSE函數(shù)只是常規(guī)用法。重點是利用了LOOKUP函數(shù)的區(qū)間取值的方法。

D2單元格輸入函數(shù):=CHOOSE(LOOKUP(C2,{0,50,500},{1,2,3}),50,C2,500)

(關(guān)于區(qū)間取值的LOOKUP函數(shù)的用法,大家可以查看《老是加班還沒加班費?誰讓你不會excel區(qū)間查詢的三大套路!

 

通過C列單元格的值,在區(qū)間{0,50,500}中的落點,得到{1,2,3}中的某一個值,然后利用CHOOSE函數(shù)根據(jù)這個值在{50,C列的值,500}這三個數(shù)中提取一個滿足規(guī)則的值并記入。

 

【解法五:究極解法】

 

有了解法三和解法四做鋪墊,大家可以總結(jié)出這道題的一個規(guī)律,其實就是在{50,C列的值,500}中找到一個“中間數(shù)”。那么有沒有什么方法可以不用這么繞來繞去,直接一些的嗎?大家再看一組解法:

 

 

哇哦,原來這才是最簡單的方法??!

D2單元格輸入函數(shù):=MEDIAN(50,500,C2)

原來工作表函數(shù)中是有一個專門的函數(shù)是做“中值”的,就是大家看到的MEDIAN函數(shù),有了前面函數(shù)的講解,相信大家也就明白了這個函數(shù)解題的原理了,取三個數(shù)中的“中間數(shù)”。

 

【解法六:變態(tài)解法】

還來?!有了“究極解法”,難道還有更好的解法嗎?

 

為了讓IF函數(shù)退休,筆者也是拼了,不辣、微辣、正常辣、PLUS辣、變態(tài)辣,口味一定要全才能開飯館!

 

 

EXCEL中,TEXT函數(shù)一直就有可以替代IF的用法,本例亦如此。

D2單元格輸入函數(shù):=--TEXT(C2,"[>500]5!0!0;[<50]5!0;0")

 

函數(shù)解析:

TEXT函數(shù)判斷格式的語法為:[條件1]顯示值1;[條件2]顯示值3;都不滿足返回本身

使用TEXT函數(shù),最重要的一點是要將格式部分寫好,這個格式中就有很多需要大家學(xué)習(xí)的地方:

1.TEXT函數(shù)的格式一定要用英文的雙引號括起來;

2.TEXT函數(shù)中的“[]”,中括號里面是寫條件的部分;

3.0”在TEXT函數(shù)的格式中是占位符,如果直接使用,則代表一個有效數(shù)值,如本例,如果要在[>500]的時候顯示500,那么大家不能直接寫500,因為此時的00是占位符,而不是實際的0值。所以如上圖中,大家需要使用“!”,英文狀態(tài)下的感嘆號是“強制符”,強制顯示0值。

及此完成用TEXT函數(shù)解題的過程,再用兩個負號(減負函數(shù)),將文本轉(zhuǎn)換成數(shù)值即可。

 

編后語:

今天的題目寫完了,其實IF函數(shù)是不會退休的,只是大家使用的函數(shù)越多越靈活,就會有更多的積累和沉淀,各種運用思路對大家更快速地完成工作是很有幫助的,有時需要迂回,有時需要直接,例如今天的題,還可以用IF的升級版來解決:

=ifs(C3>500,500,C3<50,50,C3,C3)

 

PS:以上函數(shù)在EXCEL2016及以上的版本中均可使用。函數(shù)和軟件一直都在更新?lián)Q代,大家也要與時俱進啊!

 

本文配套的練習(xí)課件請加入QQ群:902294808下載。

Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!

掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel

IMG_256

相關(guān)推薦:

同事一個函數(shù)都沒用,僅靠小學(xué)算數(shù),分分鐘搞定excel多條件判斷

Excel大擂臺:CHOOSE函數(shù)巔峰對決IF函數(shù),你投誰的票?

請也為Excel中的《無名之輩》choose獻上一份理解!

IF函數(shù):剝洋蔥

版權(quán)申明:

本文作者E圖表述;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。