Excel大擂臺:CHOOSE函數(shù)巔峰對決IF函數(shù),你投誰的票?
?
作者:小花來源:部落窩教育發(fā)布時間:2018-09-20 14:14:23點(diǎn)擊:6357
編按:
喜歡函數(shù)的朋友有福了,部落窩教育將推出“Excel大擂臺”系列,讓工作中常用的函數(shù)進(jìn)行PK,讓大家能更透徹的、更靈活的運(yùn)用函數(shù),完成工作。當(dāng)前是首篇,出場角色是CHOOSE函數(shù)和IF函數(shù)。這兩個函數(shù)的功能存在很大類似,也有各自的擅長和絕技?,F(xiàn)在就來看看,歡迎投票——
如果Excel函數(shù)圈也有江湖,那CHOOSE函數(shù)絕對算得上掃地僧。它不如IF函數(shù)那般威震江湖,但它的本領(lǐng)卻更勝一籌。今天小花就帶大家好好見識一下被大多數(shù)人冷遇的CHOOSE函數(shù)!
一、了解一下CHOOSE函數(shù)基本語句
CHOOSE函數(shù)使用 index_num 返回數(shù)值參數(shù)列表中的數(shù)值。使用 CHOOSE 可以根據(jù)索引號從最多 254 個數(shù)值中選擇一個。它的基本語句是:
=CHOOSE(index_num,value1,value2,...)
index_num:指定索引號的參數(shù),它是1-254之間的任意整數(shù),CHOOSE根據(jù)該值從參數(shù)列表value1到value254中選取對應(yīng)的參數(shù)。index_num可以是數(shù)字、公式或單元格的引用。設(shè)置該參數(shù)時需注意以下兩點(diǎn):
①如果 index_num 小于 1 或大于列表中最后一個值的索引號,則 CHOOSE 返回 #VALUE! 錯誤值。例如CHOOSE(3,1,2),由于索引參數(shù)為3,但參數(shù)列表只有兩個值,所以返回#VALUE!(錯誤類型:找不到該值。)
②如果index_num 為小數(shù),則在使用前將被截尾取整。例如CHOOSE(1.99,1,2),1.99截尾取整為1,則從參數(shù)列表{1,2}中選取第一個參數(shù)值1作為公式返回值。
value1-value254:參數(shù)列表中至少包含一個value參數(shù),即value1是必須的,且參數(shù)列表中的value個數(shù)必須大于或等于 index_num的最大可能值。value可以是數(shù)字、單元格引用、定義的名稱、公式、函數(shù)或文本。
二、單一邏輯判斷能力,CHOOSE須遜IF一段香
看了上述CHOOSE函數(shù)的語句及解釋,不難發(fā)現(xiàn),CHOOSE函數(shù)兼具了IF函數(shù)的功能。
IF的基本語句是IF(邏輯判斷,邏輯正確時返回值,邏輯錯誤時返回值),加之TRUE對應(yīng)數(shù)值1,FALSE對應(yīng)數(shù)值0,于是我們可以把IF函數(shù)語句翻譯成CHOOSE函數(shù)語句,即CHOOSE(2-邏輯判斷值,邏輯正確時返回值,邏輯錯誤時返回值)。
當(dāng)邏輯判斷結(jié)果為TRUE,2-TRUE=2-1=1,CHOOSE函數(shù)選取value1作為邏輯正確返回值;
當(dāng)邏輯判斷結(jié)果為FALSE,2-FALSE=2-0=2,CHOOSE函數(shù)選取value2作為邏輯錯誤返回值。
案例:
分別使用CHOOSE函數(shù)和IF函數(shù)來判斷成績是否合格,CHOOSE函數(shù)需使用2-邏輯值來將邏輯值轉(zhuǎn)化成索引號,略顯復(fù)雜!
三、多重條件判斷能力,CHOOSE更勝一籌
對于多重條件判斷,IF函數(shù)的忠實粉們會使用多重嵌套的方式來處理。但這樣做的結(jié)果是函數(shù)公式冗長且繁瑣,難以解讀。在嵌套過程中,我們需要多次使用IF函數(shù)。而使用CHOOSE函數(shù)來完成多重條件判斷,則較為簡潔,但需理解并掌握索引參數(shù)index_num的設(shè)置原理。接下來我們結(jié)合實例來講解下CHOOSE函數(shù)的多重條件判斷公式原理。
案例:
將下圖里的考核等級轉(zhuǎn)化為對應(yīng)的級別,每一個人的考核等級唯一。
此時如果用IF函數(shù)我們需要三重嵌套,這還是IF函數(shù)前套中比較簡單的情景,當(dāng)條件數(shù)量增加時,IF函數(shù)嵌套公式的復(fù)雜程度也會隨之增大。而CHOOSE函數(shù)公式則無需嵌套,只需將index_num寫成1+邏輯判斷1*1+邏輯判斷2*2+......+邏輯判斷n*n的形式,將value 1設(shè)置為全部條件都不滿足時的“待改進(jìn)”,其他value值與邏輯判斷條件依次對應(yīng)排列即可。
IF函數(shù)公式如下:
=IF(I4="S","優(yōu)秀",IF(I4="A","良好",IF(I4="B","一般","待改進(jìn)")))
CHOOSE函數(shù)公式如下:
=CHOOSE(1+(B4="S")*1+(B4="A")*2+(B4="B")*3,"待改進(jìn)","優(yōu)秀","良好","一般")
公式說明:
CHOOSE函數(shù)的第一個參數(shù)index_num表示的是選取參數(shù)列表的索引號,當(dāng)所有條件都不滿足時,所有邏輯條件均返回FALSE,1+∑邏輯條件n*n=1+0=1,選取value 1作為公式的最終返回值,因此value 1應(yīng)當(dāng)填入所有條件均不滿足時的目標(biāo)結(jié)果,本例中應(yīng)為“待改進(jìn)”;
當(dāng)?shù)谝粋€條件滿足時,其他條件都不滿足,1+∑邏輯條件n*n=1+1*1+0=2,選取value 2即“優(yōu)秀”作為返回值;
當(dāng)?shù)诙€條件滿足時,其他條件都不滿足,1+∑邏輯條件n*n=1+0*1+1*2+0=3,選取value 3即“良好”作為返回值;
以此類推。
因此,當(dāng)各個邏輯條件彼此不包含時,CHOOSE函數(shù)的第一個參數(shù)應(yīng)表示為1+∑邏輯條件n*n的形式,其余參數(shù)順序為value all false,value if logical 1 true,value if logical 2 true......
相反的,如果各個邏輯條件間相互包含,則CHOOSE函數(shù)的第一個參數(shù)index_num應(yīng)該寫成1+邏輯判斷1+邏輯判斷2+......+邏輯判斷n的形式,即1+∑邏輯條件n。這是因為,當(dāng)邏輯n滿足時,邏輯n-1一定也滿足,所以滿足的條件個數(shù)再加1即為選取參數(shù)列表的索引號,無需運(yùn)用*n的形式進(jìn)行轉(zhuǎn)化。典型的問題是舊個稅時的勞務(wù)報酬收入計稅。譬如工資4500元,則其既大于4000,也大于800,把它們邏輯值相加再加1,得3,個稅就采用公式中Value 3即A2*0.8*0.2計算,如下:
=CHOOSE(((A2>800)+(A2>4000)+(A2>25000)+(A2>62500)+1),0,A2-800)*0.2,A2*0.8*0.2,A2*0.8*0.3-
2000,A2*0.8*0.4-7000)
四、建立反向查找區(qū)域能力,CHOOSE全面占優(yōu)
在運(yùn)用VLOOKUP函數(shù)進(jìn)行反向查找時,我們會使用IF{1,0}結(jié)構(gòu)來完成表格數(shù)據(jù)列的重構(gòu),從而使VLOOKUP的目標(biāo)查詢值出現(xiàn)在查詢范圍的第一列。例如下圖,由于數(shù)據(jù)源區(qū)域中,姓名一列在年級列的右側(cè),我們無法直接使用VLOOKUP進(jìn)行查詢,于是我們用IF{1,0}將A列和C列數(shù)據(jù)重排順序,當(dāng)判斷為真(1),輸出$C$10:$C$14列數(shù)據(jù),判斷為假(0)輸出$A$10:$A$14列數(shù)據(jù),從而新構(gòu)建了以$C$10:$C$14為首列,$A$10:$A$14為第二列的數(shù)列作為查找區(qū)域,使VLOOKUP函數(shù)可以順利查詢到目標(biāo)結(jié)果。
于是,問題來了。IF{1,0}結(jié)構(gòu)僅能指定兩列數(shù)據(jù)的順序,無法指定多列數(shù)據(jù)的順序,來組合成新的查詢區(qū)域,這使得我們經(jīng)常需要為相同查詢邏輯不同查詢列的多個單元格單獨(dú)設(shè)置公式,無法拖動填充公式匹配列查找。譬如當(dāng)前就不能將B2公式拖動填充到C2中。IF{1,0}結(jié)構(gòu)的這一缺陷,使得它在與CHOOSE的對比中一敗涂地!
下面是CHOOSE出手,直接一次性把3列數(shù)據(jù)重新排序構(gòu)建出統(tǒng)一的查詢區(qū)域,公式可以直接從B2拖動填充到C2中:
=VLOOKUP($A2,CHOOSE({1,2,3},$C$10:$C$14,$A$10:$A$14,$D$10:$D$14),COLUMN(),0)
公式說明:
該公式的重點(diǎn)是我們運(yùn)用CHOOSE{1,2,3}結(jié)構(gòu)將表中三列數(shù)據(jù)A10:A14、C10:C14、D10:$D14重新按C10:C14排第1列,A10:A14排第2列,D10:$D14排第3列的順序組成一個新的數(shù)據(jù)區(qū)域用作Vlookup的查找區(qū)域。再借由COLUMN()返回公式所在單元格的列數(shù),確定VLOOKUP查詢返回的列數(shù)。CHOOSE函數(shù)的該用法大大突破了IF{1,0}結(jié)構(gòu)只能將兩列數(shù)據(jù)交換位置進(jìn)行重建的限制,可以說是后者的威力加強(qiáng)版!
本文,小花通過CHOOSE函數(shù)與IF函數(shù)的橫向?qū)Ρ龋v解了CHOOSE的幾個實戰(zhàn)用法,這些用法你學(xué)會了嗎?你還知道哪些與CHOOSE函數(shù)有關(guān)的技能?別忘了留言與小花交流分享哦!
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
《技術(shù)講座:Excel中IF函數(shù)嵌套運(yùn)用》
《Excel教程:sumifs函數(shù)常量數(shù)組簡化公式》
《countif函數(shù)的使用方法以及countif函數(shù)查重復(fù)等5個案例分享》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!