|
很多时候我们都会用文本连接符&进行连接运算，今天就说2个关于文本连接的函数，一个是concatenate，一个是concat。虽然它们写法上有点相似，但是差别还是挺大的。concatenate和&的作用一样，concat是新增函数。 -01- concatenate函数 1.函数说明 这个函数是将两个或多个文本字符串连接为一个字符串，相当于&。函数结构如下，可以有255个参数。 CONCATENATE(text1, [text2], ...) text1 必需，是要合并的文本字符串，可以是字符串、数字或单元格引用。其实也可以是数组。 text2，...可选 是其他要合并的文本字符串。 2.示例解释 在D1单元中输入如下图所示公式，结果为"我1爱2你3"。就把它们连起来放在一个单元格中。请注意，这里有6个参数，每个参数都是1个单元格，是一个一个输入的，比较烦琐，我能不能直接选一个区域呢？ 在D1单元格输入如下公式，结果显示为错误值。此时只有一个参数，并且是一个区域。在单元格中选中公式按F9，发现是一个数组，就是选定区域对应的数组。如下第2图所示。说明直接选择一个区域，它是不会给连接起来的。可以说这一点就是它的劣势，因为有时，我们需要将一个区域或者数组的内容连接起来。 在D1单元格输入如下公式，结果显示为错误值。此时有2个参数，第1个参数是一个区域，第2个参数是0。在单元格中选中公式按F9，发现是一个数组，如下第2图所示，相当于A1:B3&0，就是第1个参数的每一个单元格和第2个参数的0连接，形成一个新的数组。也就是说，如果它的参数是数组，那么它会按数组的对应关系运算，形成新的数组。 3.具体应用 a.制作九九乘法表 在A7单元格中输入公式=IF(ROW(1:1)<COLUMN(A:A),"",CONCATENATE(COLUMN(A:A),"*",ROW(1:1),"=",ROW(1:1)*COLUMN(A:A)))，向右向下填充。 concatenate有5个参数代表乘法口诀表达式的5个部分，if判断是为了让右上角的部分显示为空。判断条件是在九九乘法表这个区域中，行号小于列号的就为空。比如B7单元格，它在九九乘法表这个区域中是第1行第2列，行号小于列号，所以为空。 -02- concat函数 1.函数说明 concat函数是将多个区域和/或字符串的文本组合起来，但不提供分隔符或 IgnoreEmpty 参数。分隔符和IgnoreEmpty是另一个强大的文本连接函数textjoin的参数，这个函数改天讲。它的语法结构如下， CONCAT(text1, [text2],…) text1 必需 要连接的文本项。可以是字符串或字符串数组，如单元格区域。 text2，... 可选 要联接的其他文本项。文本项最多可以有 253 个文本参数。每个参数可以是一个字符串或字符串数组，如单元格区域。 如果结果字符串超过 32767 个字符（单元格限制），则 CONCAT 返回 #VALUE! 错误。 2.示例解释 在D1单元格中输入如下公式，结果为"我1爱2你3"。此时只有1个参数，而且是一个区域。当我把公式改为下面第2图的时候，结果还是一样的。此时也是只有1个参数，只是参数是一个数组，而不是一个区域。说明当参数是区域或者数组的时候，它是可以把它们连在一起的。而不像concatenate那样。有了这个功能就能做很多事情了。 在D1单元格中输入如下公式，结果为"我1爱2你30"。此时有2个参数，第1个参数是区域，第2个参数为0。它是将所有参数的每个元素一一连接起来，最后只返回1个值，而不是一个数组，有点像sum函数。注意和concatenate的区别。 它还可以按列的方向连接，如下图公式所示。 3.具体应用 a.将字符反转 如下图所示，A列是一些字符串，要求将其反转成B列的效果。在B20单元格中输入公式=CONCAT(MID(A20,10-ROW($1:$9),1))，按ctrl+shift+enter三键，向下填充。
思路是这样的，从右边第1个字符提取1位，第2个字符提取1位，。。。直到提到左边第1个为止。最后用concat连接起来就好了。 ROW($1:$9)是从1到9的一个数组，10-ROW($1:$9)就是从9到1的一个数组。MID(A20,10-ROW($1:$9),1)就是从第9位提1个，从第8位提1个，。。。从第1位提1个。也就是从大到小的提取。最后用concat连起来就好了。 b.提取数字和汉字 如下图所示，A列是随意写的一些字符串，要求将数字和汉字分别提取出来。在B26单元格中输入公式=CONCAT(TEXT(MID(A26,ROW($1:$20),1),"0;;0;"))，按ctrl+shift+enter三键，向下填充，这样就将数字提取出来。
说下思路，先用mid将字符串的每个字符提取出来，形成一个数组。然后用text函数将数组中的数字显示为原来的值，文本显示为空。最后用concat将数组中的元素连接起来。 MID(A26,ROW($1:$20),1)这部分就不说了，相信你已经很明白了。说下text的第2参数"0;;0;"，3个分号分成4部分。第1部分0，将正数显示为本身；第2部分什么也不写，将负数显示为空；第3部分0，将0显示为0；第4部分什么也不写，将文本显示为空。这样就把数字显示为本身，文本显示为空。 在C26单元格中输入公式=CONCAT(IFERROR(IF(CODE(MID(A26,ROW($1:$20),1))>256,MID(A26,ROW($1:$20),1),""),""))，按ctrl+shift+enter三键，向下填充。这样就将汉字提取出来，公式有点长，就不详细说明了。 如果只需提取文本，在D26单元格中输入公式=CONCAT(TEXT(MID(A26,ROW($1:$20),1),";;;@"))，按ctrl+shift+enter三键，向下填充。 c.按次数重复记录 有个朋友的问题是怎么将左表变成右表，比如在左表中销售部次数是2，那么在右表中销售部的记录就得有2条。这个题用累计数加lookup可以解决，但今天用concat来解决一下。
在D31单元格中输入公式=CONCAT(REPT(A32:A34&CHAR(10),B32:B34))，按ctrl+shift+enter三键。如下图所示的结果。这里用的rept函数，将A列的部门重复B列的次数，但是重复之前要连接个换行符CHAR(10)。
我点下自动换行看下它的效果，此时所有记录是出来了，但是还在一个单元格中。
先将公式复制到另一个单元格粘贴为值，如D33单元格。然后双击D33，选中全部内容，剪切，粘贴到其他单元格就ok了。
思考题： 如何将数字提取出来，并且在数字中间加个分隔符，像下图一样。
如果对你有所帮助或启发，请打赏或分享一下，你的支持就是我最大的动力！ 关注解锁更多函数的用法 |
|
|
