哇,找到一組讓效率翻倍的套路
小夥伴們好啊,今天咱們說說函數公式的內容。
單個的函數作用和功能都是比較單一的,在解決實際問題時,往往需要多個函數嵌套使用,今天就和大家分享幾個常用的函數嵌套組合。熟悉這些嵌套函數的使用,提高效率一丟丟還是有可能的。
1、IF IF 多區間判斷
如下圖,要按照不同的區間範圍,判斷B列的成績是否合格。
低於9分是不合格,達到9分是合格,達到9.5分是優秀。
=IF(B5>=9.5,"優秀",IF(B5>=9,"合格","不合格"))
公式先判斷B5大於9.5的判斷條件是否成立,如果符合條件就返回指定的內容「優秀」。
如果條件不成立,就繼續判斷下一個條件,看看B5>=9的條件是否成立,如果成立就返回指定的內容「合格」。
如果條件仍然不成立,就返回指定的內容「不合格」。
使用IF函數進行多個區間的判斷時,特別要注意一點:可以從最高開始,向最低依次判斷,也就是先判斷如果比最高的條件還要高,是什麼結果。
也可以從最低開始向最高依次判斷,也就是先判斷如果比最低的條件還要低,是什麼結果。
2、IF COUNTIF 判斷數據是否重複
如下圖,要統計B列的姓名是否為重複出現。
C2使用的公式為:
=IF(COUNTIF($B$2:B2,B2)>1,"重複","")
COUNTIF函數使用動態擴展區域$B$2:B2作為統計範圍,計算B列員工姓名在這個區域中出現的次數,如果出現的次數大於1,就是重複。
以B2為例,北原愛子首次出現,C3單元格中的公式為:
=IF(COUNTIF($B$2:B3,B3)>1,"重複","")
結果就是1,也就是不重複了。
而到了C9單元格,公式為:
=IF(COUNTIF($B$2:B9,B9)>1,"重複","")
在$B$2:B9這個區域中,B9單元格的北原愛子出現了兩次。所以$B$2:B3,B3)>1的條件成立,也就是說B9是重複出現的。
3、INDEX MATCH 查詢一貼靈
如下圖所示,根據姓名查詢部門和職務,也就是傳說中的逆向查詢。
F3單元格公式為:
=INDEX(A:A,MATCH($E3,$C:$C,))
用MATCH函數來定位查詢值的位置,再用INDEX函數返回指定區域中指定位置的內容,二者結合,可以實現上下左右全方位的查詢。
MATCH找到E3單元格在C列的精確位置:老IN啊,你要找的那位,在第6間屋呢。
接下來INDEX根據MATCH提供的線索,從A列找到第6個單元格。
4、MIN IF 計算指定條件的最小值
如下圖所示,要計算生產部的最低分數。
G3單元格使用以下數組公式,按Shift ctrl 回車:
=MIN(IF(A2:A9=F3,D2:D9))
先用IF函數判斷A列的部門是否等於F3指定的部門,如果條件成立,則返回D列對應的分數,否則返回邏輯值FALSE:
{FALSE;45;FALSE;FALSE;FALSE;66;FALSE;72}
接下來再使用MIN函數計算出其中的最小值。
MIN函數有一個特性,就是可以自動忽略邏輯值,所以只會對數值部分計算,最終得到指定部門的最低分數。
5、DATEDIF TODAY計算年齡
如下圖所示,要根據C列的出生年月計算年齡。
=DATEDIF(C2,TODAY(),"y")
TODAY()函數返回系統當前日期。
DATEDIF函數第一參數是開始日期,第二參數是結束日期,也就是由TODAY計算出的系統當前日期。
第三參數是返回的數據類型。使用Y,表示返回整年數。使用M,則表示返回整月數。
6、TEXT MID 提取出生年月
如下圖所示,要根據B列身份證號碼提取出生年月。
C2單元格公式為:
=--TEXT(MID(B2,7,8),"0-00-00")
MID函數用於從字元串的指定位置開始,提取特定數目的字元串。
MID(B2,7,8)就是從B2單元格的第7位開始,提取8位數字,結果為:
19751226
再使用TEXT函數,將這個字元串變成"0-00-00"的樣式,結果為"1975-12-26"。
這個時候,已經有了日期的模樣,但是本身還是文本型的,所以再加上兩個負號,也就是計算負數的負數,通過這麼一折騰,就變成真正的日期序列了。
7、LEN LENB 混合內容提取
如下圖,要從A列姓名電話中提取出姓名,除了使用高版本的自動填充功能,通常會使用以下公式完成:
=LEFT(A2,LENB(A2)-LEN(A2))
LENB函數將每個漢字(雙位元組字元)的字元數按2計數,LEN函數則對所有的字元都按1計數。因此「LENB(A2)-LEN(A2)」返回的結果就是文本字元串中的漢字個數。
LEFT函數從文本字元串的第一個字元開始,返回指定個數的字元,最終提取出員工姓名。
老祝說:Excel中的函數就像是一個萬花筒,千變萬化的組合還有太多太多,今天就和大家分享這些,咱們一起加油吧~~
京東限時優惠,滿100-30,折後僅相當於5.5折
推薦閱讀:
※方天戟套路練法
※牽女生手的套路! 很容易
※你有哪些連環套路問題?
※套路不全,就隨時有可能被彈劾
※使用反膠乒乓球愛好者如何打長膠(高膠),三種套路克制長膠打法