Excel中函數公式的威力大家都知道有多強大, 但很多同學卻難以駕馭!
大家的同感是:在工作中很多具體問題用普通公式都無法解決,
比如遇到多條件判斷、多條件統計和極值計算、文本合併、資料按分隔符合並連接等需求時, 不但要使用複雜的陣列公式, 有時甚至還要用到VBA程式設計。
但是所有這一切, 都隨著Excel 2016的到來改變了!很多以前很棘手的問題, 現在在新版本中都可以迎刃而解, 用到的利器就是今天的幾位主角。
1、IFS函數
2、MAXIFS函數
3、MINIFS函數
4、CONCAT函數
5、TEXTJOIN函數
(注意, 要能使用上述函數, Office的版本很重要, 並不是所有的Office 2016能使用。 建議
在以下位址申請試用Office 365, 安裝最新版的Office 2016)
h t t p s: / / p r o d u c t s . o f f i c e .c o m / z h -C N/ t r y
一、IFS函數
IFS 函數檢查是否滿足一個或多個條件, 且是否返回與第一個 TRUE 條件對應的值。 IFS 可以輕鬆取代複雜的多層嵌套 IF 語句。
用一句話說清楚它的語法:
=IFS(條件1判斷語句, 條件1結果, 條件2判斷語句, 條件2結果, 條件3判斷語句, 條件3結果, ......)
說明:IFS 函數允許測試最多 127 個不同的條件。
光說不練假把式, 咱來個實際問題讓IFS露露臉
按照成績劃分所屬等級
規則如下:
100分:滿分
大於等於90分:優秀
大於等於80分:良好
大於等於60分:及格
小於60分:不及格
要在C列輸入公式, 根據以上規則判斷等級, 以往常用的IF公式如下:
=IF(B2=100,"滿分",IF(B2>=90,"優秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格"))))
看到這一群括弧, 80%的人已經暈了
下面IF可以下場休息了, 換IFS上場
=IFS(B2=100,"滿分",B2>=90,"優秀",B2>=80,"良好",B2>=60,"及格",B2
這種公式寫起來是不是簡單多了?
條件越多, 用這個公式的優勢就更加明顯, 你懂的!
如果你以為IFS就這點本事就小瞧他啦, 當IFS與MAX合體時, 會發生什麼?往下看吧~~
二、MAXIFS函數
MAXIFS 函數返回一組給定條件或標準指定的儲存格中的最大值。
用一句話說清楚它的語法:
=MAXIFS(結果所在區域, 條件區域1, 條件1, 條件區域2, 條件2, ......)
結合一個實際案例來加深理解。
在下圖所示的表格中, 要同時滿足3個條件統計最高銷量
1、第一個條件是產品為C
2、第二個條件是管道為外銷
3、第三個條件是滿足前兩條下銷量最高
以前遇到這種情況, 需要用到以下這個陣列公式, 而且要按輸入才能返回正確結果。
=MAX(IF((C2:C15="C")*(B2:B15="外銷"),D2:D15))
現在有了MAXIFS, 一切都變的超簡單!
在F2儲存格輸入以下公式即可
=MAXIFS(D:D,C:C,"C",B:B,"外銷")
有了Excel 2016, 就連Excel小白也可以輕鬆搞定這類複雜的多條件統計問題了!
MAXIFS還有個好兄弟, 下面輪到他出場。
三、MINIFS函數
MINIFS 函數返回一組給定條件或標準指定的儲存格中的最小值。
他的語法結構跟MAXIFS一致, 不再贅述, 直接上案例。
在F2儲存格輸入以下公式即可
=MINIFS(D:D,B:B,"女",C:C,"1組")
一個公式輕鬆搞定,小白也毫無壓力!
除了多條件判斷、多條件統計極值,在文本合併方面Excel 2016也有驚喜帶給你,往下看~
四、CONCAT函數
下表中的多列資料需要合併,即需要把A2:J2的資料合併放置在K2儲存格,依此類推。
在老版本中,常見的合併做法是以下這兩種公式。
老方法1:
=A2&B2&C2&D2&E2&F2&G2&H2&I2&J2
老方法2:
=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2)
當需要合併的資料更多時,公式長度繼續增加,很機械很重複的碼公式是個苦差事!
用CONCAT函數就簡單了,以K2儲存格為例公式如下
=CONCAT(A2:J2)
CONCAT不但可以橫向合併,還可以縱向合併
比如要把A列資料合併,只需輸入以下公式。
=CONCAT(A2:A7)
除了橫向合併,縱向合併外,CONCAT還支援區域內資料合併
以下這個很簡短的公式,可以幫你把整個區域的資料都合併在一起
=CONCAT(A2:J7)
這些問題在Excel 2016出現以前,處理方法非常麻煩,要麼是手動輸入長長的公式,要麼是需要使用陣列公式甚至VBA程式設計,現在一個CONCAT統統搞定。
其實學會這麼多你已經可以在工作中搞定80%以上的問題啦,但今天我再多送你點乾貨,讓你賺個盆滿缽滿,覺得有用就去底部點贊吧!
有時候,工作需要讓合併資料的時候中間加個分隔符號,這個,可以滿足你一下!
這裡輸入的是一個陣列公式,需要按複合鍵輸入以下公式。
=CONCAT(A2:C4&" ")
公式中引號裡面是個空格,這樣就在合併資料的時候用空格間隔每個資料了,貼心吧!
不但如此,CONCAT還支援條件篩選後的資料合併,看看下面這個動態演示
點擊gif可見演示過程
F2用的是以下這個陣列公式,需要按輸入
=CONCAT(IF((B2:B13=E2)*(C2:C13="是"),A2:A13&"、",""))
它可以幫你做到條件篩選後的資料合併,而且即時動態更新結果哦!
你見識了CONCAT的強大,我悄悄告訴你下面要講的那個函數可以替代CONCAT的功能,而且還可以完成更多,比如這個案例中返回結果中不想顯示最後那個頓號
五、TEXTJOIN函數
這個函數從名字一看就是專業幹文本連接的對吧,呵呵~
用一句話說明他的語法就是
=TEXTJOIN(間隔符,1,需要合併的資料或區域)
看個案例加深理解
要想把下圖左側的表格,按照所屬部門將人員姓名列示在一起,並以頓號間隔,你該怎麼做呢?
E2輸入陣列公式後,按複合鍵輸入,將公式向下填充。
=TEXTJOIN("、",1,IF(B$2:B$15=D2,A$2:A$15,""))
效果演示如下,點擊gif可見動態演示過程。
講了這麼多給力的函數,你不是大開眼界呢?原來這麼多好用的函數都藏在2016版裡!
在F2儲存格輸入以下公式即可
=MINIFS(D:D,B:B,"女",C:C,"1組")
一個公式輕鬆搞定,小白也毫無壓力!
除了多條件判斷、多條件統計極值,在文本合併方面Excel 2016也有驚喜帶給你,往下看~
四、CONCAT函數
下表中的多列資料需要合併,即需要把A2:J2的資料合併放置在K2儲存格,依此類推。
在老版本中,常見的合併做法是以下這兩種公式。
老方法1:
=A2&B2&C2&D2&E2&F2&G2&H2&I2&J2
老方法2:
=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2)
當需要合併的資料更多時,公式長度繼續增加,很機械很重複的碼公式是個苦差事!
用CONCAT函數就簡單了,以K2儲存格為例公式如下
=CONCAT(A2:J2)
CONCAT不但可以橫向合併,還可以縱向合併
比如要把A列資料合併,只需輸入以下公式。
=CONCAT(A2:A7)
除了橫向合併,縱向合併外,CONCAT還支援區域內資料合併
以下這個很簡短的公式,可以幫你把整個區域的資料都合併在一起
=CONCAT(A2:J7)
這些問題在Excel 2016出現以前,處理方法非常麻煩,要麼是手動輸入長長的公式,要麼是需要使用陣列公式甚至VBA程式設計,現在一個CONCAT統統搞定。
其實學會這麼多你已經可以在工作中搞定80%以上的問題啦,但今天我再多送你點乾貨,讓你賺個盆滿缽滿,覺得有用就去底部點贊吧!
有時候,工作需要讓合併資料的時候中間加個分隔符號,這個,可以滿足你一下!
這裡輸入的是一個陣列公式,需要按複合鍵輸入以下公式。
=CONCAT(A2:C4&" ")
公式中引號裡面是個空格,這樣就在合併資料的時候用空格間隔每個資料了,貼心吧!
不但如此,CONCAT還支援條件篩選後的資料合併,看看下面這個動態演示
點擊gif可見演示過程
F2用的是以下這個陣列公式,需要按輸入
=CONCAT(IF((B2:B13=E2)*(C2:C13="是"),A2:A13&"、",""))
它可以幫你做到條件篩選後的資料合併,而且即時動態更新結果哦!
你見識了CONCAT的強大,我悄悄告訴你下面要講的那個函數可以替代CONCAT的功能,而且還可以完成更多,比如這個案例中返回結果中不想顯示最後那個頓號
五、TEXTJOIN函數
這個函數從名字一看就是專業幹文本連接的對吧,呵呵~
用一句話說明他的語法就是
=TEXTJOIN(間隔符,1,需要合併的資料或區域)
看個案例加深理解
要想把下圖左側的表格,按照所屬部門將人員姓名列示在一起,並以頓號間隔,你該怎麼做呢?
E2輸入陣列公式後,按複合鍵輸入,將公式向下填充。
=TEXTJOIN("、",1,IF(B$2:B$15=D2,A$2:A$15,""))
效果演示如下,點擊gif可見動態演示過程。
講了這麼多給力的函數,你不是大開眼界呢?原來這麼多好用的函數都藏在2016版裡!