|
我们昨天学了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去掉多余的空格。 如果对你有所帮助或启发，请打赏或分享一下，你的支持就是我最大的动力！ |
|
|
