哇,找到一組讓效率翻倍的套路

小夥伴們好啊,今天咱們說說函數公式的內容。

單個的函數作用和功能都是比較單一的,在解決實際問題時,往往需要多個函數嵌套使用,今天就和大家分享幾個常用的函數嵌套組合。熟悉這些嵌套函數的使用,提高效率一丟丟還是有可能的。

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折


推薦閱讀:

方天戟套路練法
牽女生手的套路! 很容易
你有哪些連環套路問題?
套路不全,就隨時有可能被彈劾
使用反膠乒乓球愛好者如何打長膠(高膠),三種套路克制長膠打法

TAG:套路 | 效率 | 找到 |