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