小伙伴們好啊,單個的函數(shù)作用和功能都是比較單一的,在解決實際問題時,往往需要多個函數(shù)嵌套使用,今天就和大家分享幾個常用的函數(shù)嵌套組合。熟悉這些嵌套函數(shù)的使用,提高效率一丟丟還是有可能的。 組合一:INDEX+MATCH 出鏡率最高的查詢類函數(shù)之一。 用MATCH函數(shù)來定位查詢值的位置,再用INDEX函數(shù)返回指定區(qū)域中指定位置的內(nèi)容,二者結(jié)合,可以實現(xiàn)上下左右全方位的查詢。 應(yīng)用實例: 如下圖所示,根據(jù)姓名查詢部門和職務(wù),也就是傳說中的逆向查詢。 F3單元格公式為: =INDEX(A:A,MATCH($E3,$C:$C,)) MATCH找到E3單元格在C列的精確位置:老IN啊,紅紅在第6間屋呢。 接下來INDEX根據(jù)MATCH提供的線索,從A列找到第6個單元格:歐了,這就是紅紅家的窗戶,下手—— 組合二:MIN+IF 用于計算指定條件的最小值。 如下圖所示,要計算生產(chǎn)部的最低分?jǐn)?shù)。 G3單元格可以使用數(shù)組公式: =MIN(IF(A2:A9=F3,D2:D9)) 先用IF函數(shù)判斷A列的部門是否等于F3指定的部門,如果條件成立,則返回D列對應(yīng)的分?jǐn)?shù),否則返回邏輯值FALSE: {FALSE;45;FALSE;FALSE;FALSE;66;FALSE;72} 接下來再使用MIN函數(shù)計算出其中的最小值。 MIN函數(shù)有一個特性,就是可以自動忽略邏輯值,所以只會對數(shù)值部分計算,最終得到指定部門的最低分?jǐn)?shù)。 注意,由于執(zhí)行了多項計算,所以在輸入公式時,要按Shift+ctrl+Enter鍵哦。 組合三:VLOOKUP+MATCH 常用于不確定列數(shù)的數(shù)據(jù)查詢。 如下圖所示,要根據(jù)B13單元格的姓名,在數(shù)據(jù)表中查詢對應(yīng)的項目。 C13單元格公式為: =VLOOKUP(B13,A1:G9,MATCH(C12,1:1,),0) 如果數(shù)據(jù)表的列數(shù)非常多,在使用VLOOKUP函數(shù)時,還需要掰手指頭算算查詢的項目在數(shù)據(jù)表中是第幾列,真是麻煩的很。 現(xiàn)在好了,先用MATCH函數(shù)來查詢項目所在是第幾列,然后VLOOKUP函數(shù)就根據(jù)MATCH函數(shù)提供的情報,返回對應(yīng)列的內(nèi)容。 組合四:LEN+SUBSTITUTE 計算一個單元格內(nèi)有幾個項目。 如下圖所示,要計算每個部門的人數(shù)。 這種表格想必大家不會陌生吧,多個姓名在一個單元格內(nèi),每個姓名之間用頓號隔開。 C2單元格公式為: =LEN(B2)-LEN(SUBSTITUTE(B2,'、',))+1 先用LEN函數(shù)計算出B列單元格的字符長度,然后再用SUBSTITUTE函數(shù)將頓號全部替換掉之后,計算替換后的字符長度。 用字符長度減去替換后的字符長度,就是單元格內(nèi)頓號的個數(shù)。 接下來,加1即是實際的人數(shù),你懂得。 組合五:TEXT+MID 常用于日期字符串的提取和轉(zhuǎn)換。 如下圖所示,要根據(jù)B列身份證號碼提取出生年月。 C2單元格公式為: =--TEXT(MID(B2,7,8),'0-00-00') MID函數(shù)用于從字符串的指定位置開始,提取特定數(shù)目的字符串。 MID(B2,7,8)就是從B2單元格的第7位開始,提取8位數(shù)字,結(jié)果為: 19751226 再使用TEXT函數(shù),將這個字符串變成'0-00-00'的樣式,結(jié)果為'1975-12-26'。 這個時候,已經(jīng)有了日期的模樣,但是本身還是文本型的,所以再加上兩個負(fù)號,也就是計算負(fù)數(shù)的負(fù)數(shù),通過這么一折騰,就變成真正的日期序列了。 Excel中的函數(shù)就像是一個萬花筒,千變?nèi)f化的組合還有太多太多,今天就和大家分享這些,咱們一起加油吧~~ 圖文作者:祝洪忠 2月6/8日 20:00免費公開課 課程介紹: 第一節(jié) 掀起你的蓋頭來——揭開vba神秘的面紗 報名請入QQ群:246666937,掃描二維碼輕松入群 |
|