|
我們昨天學了2個文本連接函數，今天來學一個功能更強大的文本連接函數——textjoin。此函數也是新增函數，要office2016版以上才可能有這個函數，它的功能是使用分隔符連接文本字符串區域。 -01- 函數說明 TEXTJOIN函數將多個區域和/或字符串的文本組合起來，并包括你在要組合的各文本值之間指定的分隔符。它的語法結構如下。 TEXTJOIN(分隔符, ignore_empty, text1, [text2], …) 分隔符 必須 文本字符串 (空) 或一個或多個用雙引號括起來的字符, 或對有效文本字符串的引用。如果提供了一個數字, 它將被視為文本。 ignore_empty 必須 忽略空值的意思。如果為TRUE[默認]，則忽略空白單元格。如果為FALSE，則不忽略空白單元格。 text1 必須 要連接的文本項。可以是文本字符串或字符串數組, 例如單元格區域。 text2 可選 要連接的其他文本項。文本項目最多可以包含252個文本參數, 包括text1。每個都可以是文本字符串或字符串數組, 例如單元格區域。 如果結果字符串超過32767個字符 (單元格限制), TEXTJOIN 將返回 #VALUE!。 -02- 示例解釋 在C1單元格中輸入公式=TEXTJOIN(",",TRUE,A1:A4)，結果如下。第1參數為逗號","，也就是分隔符是逗號；第2參數為TRUE，也就是忽略空值；第3參數為A1:A4，也就是要連接的文本是一個單元格區域。它不僅把單元格區域的每個字符串連接起來，而且用分隔符將每個字符串隔開，而且如果單元格區域有空單元格它也會忽略。 在C1單元格中輸入公式=TEXTJOIN(",",FALSE,A1:A4)，結果如下。此時不忽略空值，可以看到在"愛"和"你"中間有2個逗號","。說明不忽略空值時，空值也要用分隔符隔開，也就是空值上要顯示出分隔符。 其實公式還可以簡寫，如下圖所示。第2參數不寫，但用逗號把它的位置留出來，相當于TRUE。你可以看它的結果，已經把空值忽略了。 第1，第2參數都可以不寫，但要用逗號留出它們的位置，如下圖所示。第1參數不寫，就相當于分隔符為空""，或者可以看作沒有分隔符；第2參數不寫相當于TRUE。 在C1單元格輸入如下公式，第3參數是單元格區域，第4參數為數組。但結果還是1個值，而不是數組。有點像concat。而且連接的方式是第1個參數的每個元素依次連接，再連接第2個參數的每個元素，...直到連接完最后一個參數的每個元素。 在C1單元格中輸入如下公式，結果如下。此時第1參數也是一個數組，但是它的結果還是1個值，而不是一個數組。說明第1參數是數組時，也會像第3，第4參數...那樣一一連接起來，直到連接完。只不過連接方式是循壞連接。如下圖所示"+","-"一直在循環。 當第2參數也是數組時，情況就有點不同了。選中公式按F9可以看到，它是一個數組，有2個元素，如下第2圖，而且2個元素的值也不同，一個忽略空值，一個不忽略空值。說明只有當第2參數是數組時，才會形成數組。 你可能會問寫這么多“沒用的”有什么用呢？其實是有用的，寫這么多示例情況，就是為了搞清楚這個函數的運行原理，以防我們在工作中踩坑。 -03- 具體應用 1.提取數字并用分隔符隔開 昨天文章的最后留了個思考題，不僅將數字提取出來，而且要在數字之間連接分隔符。用今天的textjoin函數來完成就比較簡單，在B8單元格中輸入公式=TEXTJOIN("-",,TEXT(MID(A8,ROW($1:$20),1),"0;;0;"))，按ctrl+shift+enter三鍵，向下填充。 思路還是一樣的，先用mid函數將單元格中的每個字符提取出來；然后用text函數將數字顯示出來，將文本顯示為空；最后用textjoin函數將其連接起來，可以添加分隔符，重要的是可以忽略空值。 用concat函數也能完成，不過步驟要多一些。在C8單元格中輸入公式=SUBSTITUTE(TRIM(CONCAT(TEXT(MID(A8,ROW($1:$20),1),"0;;0;")&" "))," ","-")，按ctrl+shift+enter三鍵，向下填充。 TEXT(MID(A8,ROW($1:$20),1),"0;;0;")這部分都是一樣的，將每個字符提取出來，數字顯示為數字，文本顯示為空。然后在其后面連接個空格" "，再用concat連接起來，就是下圖1的效果。在編輯欄中選中公式按F9查看結果，如下圖2的結果，發現有很多空格。
所以用trim函數將多余的空格去掉，就是下圖的結果；最后用substitute將空格替換為短線，就是我們要的結果。
2.將相同部門的員工合并 將左表變成右表的形式，也就是將相同部門的員工合并在一個單元格中。在E14單元格中輸入公式=TEXTJOIN("、",,IF(A$14:A$22=D14,B$14:B$22,""))，按ctrl+shift+enter三鍵，向下填充。
textjoin的第1參數是頓號，第2參數不寫用逗號留出位置就是忽略空值，關鍵的就是第3參數，第3參數是個if函數。 IF(A$14:A$22=D14,B$14:B$22,"")意思是如果A列的值等于“技術部”，那么就返回B列中對應的員工名字，否則就返回空""，這樣就形成一個數組。用textjoin連接起來就完成了。 3.將銀行卡號分段顯示 如下圖所示要將銀行卡號每隔4位添加一個空格，實現分段顯示，這樣看起來比較好看。之前用text函數結合left和right來完成，今天用textjoin和concat來分別完成。在B36單元格中輸入公式=TEXTJOIN(" ",,MID(A36,ROW($1:$9)*4-3,4))，按ctrl+shift+enter三鍵，向下填充。
思路是這樣的，用mid函數從第1位提取4位，就是6217；從第5位提取4位，就是0071，···以此類推，提取完成。再用textjoin將其連接起來，用空格隔開。 ROW($1:$9)*4-3這部分就是構建一個以1開始，步長為4的等差數列。也就是1，5，9，13，17，21···。其實這里到17就可以了，因為銀行卡號一共是19位。 用concat來完成，在C36單元格中輸入公式=TRIM(CONCAT(MID(A36,ROW($1:$9)*4-3,4)&" "))，按ctrl+shift+enter三鍵，向下填充。
MID(A36,ROW($1:$9)*4-3,4)這部分和上面一樣，得到這樣一個數組{"6217";"0071";"4001";"3073";"428";"";"";"";""}。后面連接個空格，得到這樣一個數組{"6217 ";"0071 ";"4001 ";"3073 ";"428 ";" ";" ";" ";" "}，然后用concat連接起來就是"6217 0071 4001 3073 428 "，尾部有多余的空格，用trim去掉多余的空格。 如果對你有所幫助或啟發，請打賞或分享一下，你的支持就是我最大的動力！ |
|
|
