韓老師今天就來講三個文本連接函數:CONCATENATE、CONCAT、TEXTJOIN。這三個函數可以實現添加固定文本、帶格式內容、區域合併、固定間隔符合並等各種連接。
CONCATENATE函數
一、函數簡介
功能:將兩個或多個文本字符串聯接為一個字符串。
語法:
CONCATENATE(text1, [text2], ...)
text1(必需):要聯接的第一個項目。項目可以是文本值、數字或單元格引用;Text2, ... (可選):要聯接的其他文本項目。最多可以有 255 個項目,總共最多支持 8,192 個字符。二、用法舉例
CONCATENATE和文本連接符的用法基本一樣,所以不再展開講述,只把結果寫到此處,詳細介紹可參考昨天的文章。
1、基本用法:
直接合並多個單元格內容,使用公式:
=CONCATENATE(A1,B1,C1,D1,E1,F1)。
如下圖:
如果需要連接進固定文本內容,使用公式:
=CONCATENATE("敬請關注:",A3,B3,C3,D3,E3,F3)。
如下圖:
2、合併後換行:
公式:
=CONCATENATE("敬請關注:",CHAR(10),A1,B1,C1)
結果如下圖:
3、合併帶格式的內容:
公式:
=CONCATENATE(A2,TEXT(B2,"e年m月"),$C$1,TEXT(C2,"#,##0.00"),$D$1,TEXT(D2,"0.0%"))
實現“日期”、“千分位分隔符”“百分比”等格式文本的鏈接。
結果如下圖:
4、合併列實現多條件查找:
在G2單元格輸入公式:
=SUMPRODUCT((CONCATENATE(A2:A13,B2:B13)=CONCATENATE(E2,F2))*C2:C13)
即可完成查找。
但,在實際多條件查詢應用中,一般選擇用文本連接符&。
&與CONCATENATE函數的弊端:
不管是文本連接符,還是CONCATENATE函數,在連接數據時,參數都必須是單元格,而不能是區域,所以,如果連接內容較多時,公式會非常長,寫起來很麻煩!
比如:
=A2&B2&C2&D2&E2&F2&G2&H2&I2&J2;
=CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2)
因此,新版EXCEL2016出現了可以合併區域的文本連接函數:CONCAT和TEXTJOIN
以上兩個麻煩的公式可以簡化為:=CONCAT(A1:J1)或=TEXTJOIN("",,A1:J1),是不是簡單了許多?
CONCAT函數
一、函數簡介
功能:將區域文本(或單個文本)聯接為一個字符串。
語法:
CONCAT (文本 1,[文本 2],...)
文本 1 (必需):要合併的文本項。字符串或字符串數組,如單元格的區域中。[文本 2,...] (可選):要聯接的額外的文本項。可以有 253 文本項的文本參數的最大值。二、用法舉例
1、合併區域:
同行合併公式:=CONCAT(A1:L1)。
結果如下圖:
行列區域合併公式:=CONCAT(A1:L4).
結果如下圖:
2、加分隔符合並區域:
公式:=CONCAT(A1:F1&" "),(此處加空白分隔符)
結果如下圖:
特別注意:此公式是數組公式,需要按<ctrl>組合鍵結束。/<ctrl>
3、條件篩選區域合併:
CONCAT函數還可以用於符合某些條件的數據合併,不如以下動圖,篩選的是各個部門考核優秀的員工名單,而且,如果源數據有變化,結果也會跟著更新:
公式:
{=CONCAT(IF((A2:A16=E2)*(C2:C16="優秀"),B2:B16&",",""))},(按<ctrl>組合鍵結束)/<ctrl>
該公式的含義是:凡是滿足條件:部門列中等於E2部門、考核結果列中等於優秀的就合併。
TEXTJOIN函數
CONCAT函數能完成的功能,TEXTJOIN 函數都能完成,並且TEXTJOIN 函數在忽略空白單元格、合理應用分隔符方面更勝一籌。
一、函數簡介
功能:將從多個區域和/或字符串文本結合在一起,包括您指定將結合每個文本值之間的分隔符。
語法:
TEXTJOIN (分隔符, 是否忽略空白單元格,文本 1,[文本 2],...)
分隔符 (必需):文本字符串,或者為空,或用雙引號引起來的一個或多個字符,或對有效文本字符串的引用。如果提供一個數字,則將被視為文本。是否忽略空白單元格二、用法舉例
1、合併區域:
同行合併公式:=TEXTJOIN("、",1,A1:F1)。
含義是:忽略空白單元格合併,並用“、’”分隔。
結果如下圖:
行列區域合併公式:=TEXTJOIN("",1,A1:L4)。
結果如下圖:
2、條件篩選區域合併:
TEXTJOIN函數還可以用於合併滿足某一條件的單元格合併,比如下面動圖,展示的是同一部門員工的合併,而且,隨著員工部門調動,結果也隨之改變:
公式:
{=TEXTJOIN("、",1,IF($A$2:$A$16=D2,$B$2:$B$16,""))},(按<ctrl>組合鍵結束)/<ctrl>
該公式的含義是:合併部門列同一部門的員工。
備註
CONCAT和TEXTJOIN函數是EXCEL2016特有的,而且,並不是每一個2016版本都有。