《Excel 版滑板鞋》
有些事我都已忘记。
但我现在还记得,在一个晚上,
我的同事问我,今天怎么特开心?
我说在我的 Excel 中,有一个 CONCAT,
与众不同最时尚,用起来肯定棒!
在小花改编的《Excel 滑板鞋》中,提到了一个函数,CONCAT。
它是 OFFICE 2019 等高级版本中,才破笋而出的「后浪」。
直到落笔前,小花才想到一句话来形容这个「超级新人」:CONCAT 拯救了 Excel 的文本处理能力。
稍加搜索,便发现目前的 Excel 教程对它都是浅尝辄止,而小花一贯毫无保留。
前方烧脑,请自备核桃!
PS:如果觉得文中有些公式太难学不会,会套用就好。
合并再多文本,都只是基操!
让我们先看看,函数胖子 CONCATENATE 是如何完成文本连接的。(名字这么长,不是胖是什么?)
两个字评价,「鸡肋」!难怪 CONCATENATE 函数很快被「&」连接符取而代之。
痛定思痛,疯狂瘦身后,胖子变型男,CONCAT 函数闪亮登场!
它的功能是连接列表或文本字符串区域,只有一个参数,即:=CONCAT(文本区域)
相对于 CONCATENATE 函数或「&」连接符的逐一连接,CONCAT 的效率提升可是指数级别的。看下图:
小花:哎呦,就算你们这么夸我,我也不会开心的!哈哈哈!
粉丝:我们才没夸你,别 YY!
当然,除了合并行列,多行多列区域文本合并,CONCAT 也是轻松搞定!
按条件合并文本,也是妥妥的!
还在使用 OFFICE 2016 版及以下的朋友,按部门合并姓名这类问题,你是如何解决的?
排序 + IF 法,VLOOKUP 法还是 PQ 法……(这些知识点,你会吗?)
CONCAT 为我们提供了全新的便捷思路。公式如下:
{=CONCAT(IF(A2:A7=D2,B2:B7&"、",""))}
公式说明:
使用 IF 函数进行条件判断,对满足「部门」条件的人员,返回其姓名 &"、";不满足条件的,返回空,从而返回 N 个「姓名、」值。
再使用 CONCAT 将这些文本连接起来。
混合文本提取数字,这才是最优解!
没有 CONCAT 函数之前,为了从混合文本中提取数字,特别是不连续数字,各路大神真是绞尽脑汁、肝脑涂地。
什么 SUM+LARGE、什么 LOOKUP+MID,各种神级嵌套、各种数据逻辑。
终于,这一切在 CONCAT 问世后,宣告下架。
提取数字这事儿,CONCAT 才是最优解。公式如下:
{=CONCAT(IFERROR(--MID($A2,ROW($1:$100),1),""))}
公式说明:
使用 MID+ROW 组合,将混合文本中的每一个字符都单独提取出来,然后使用双负号进行运算。
此时,数字返回其本身,非数字返回错误#VALUE!,IFERROR 随即将错误值转化为空。
于是 A2 中数字被保留下来,其余全部被替换为空,CONCAT 将它们连接起来,即可完成对数字的提取。
提取不重复数字难题,照样拿下
Q:语文是数学老师教的,是一种什么体验?
A:「1 去 23 里,烟村 45 家」这句诗里用到几个数字?
从混合文本中识别用到的数字,这是一个充满趣味和挑战的 Excel 问题。
OFFICE 2016 版以前的 Excel 用户中要完成这项任务可谓困难之极,或许仰仗 PQ 之力尚有一线希望。
但在 CONCAT 铁蹄之下,没有征服不了的「文本处理」大山!
{=CONCAT(IF(ISNUMBER(FIND(ROW($1:$10)-1,$A2)),(ROW($1:$10)-1),""))}
公式说明:
❶ ROW ($1:$10)-1 返回从 0-9 这十个阿拉伯数字,再通过 FIND 函数查找这些数字在 B2 中出现的位置。
如果 A2 单元格包含该数字,则返回位置序数值,否则返回#VALUE!。
❷ 利用 ISNUMBER 对 FIND 的返回值进行判断,使得序数值和#VALUE! 的有序数组变为一组逻辑值,逻辑为 TRUE 代表 A2 中包含该数字,FALSE 则相反。
❸ 随后 IF 函数发挥作用,TRUE 返回对应的数字,FALSE 返回空。
❹ 最后 CONCAT 收尾,将这些数字连接起来。
看到这,实战派不禁要问,这个公式在工作中似乎无用武之地?
错!它大有可为。
实战案例:根据部门人员名单和考勤登记名单,自动计算缺勤人员名单。
{=CONCAT(IF(ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2,"、",REPT(" ",99)),ROW($1:$100)*99-98,99)),B2)),"",TRIM(MID(SUBSTITUTE(A2,"、",REPT(" ",99)),ROW($1:$100)*99-98,99))&"、"))}
▲ 左右滑动查看
公式简要说明:
我们通过 SUBSTITUTE 函数将姓名间的顿号替换为 99 个空格,使得 99 个空格和一个姓名形成一组独特的字符串,每组独特字符串仅包含一个姓名。
随后我们像提取不重复数字的做法一样,将 99 个字符作为一个单位并去除空格进行比对,只是返回值做了调换,包含返回空,不包含返回姓名 &"、"。
最后,再使用 CONCAT 将它们连接起来,形成缺勤名单。
其他有趣用法,了解一下!
在尝试使用 CONCAT 完成上述几个高能用法的过程中,小花意外挖掘出了 CONCAT 的两个有趣用法。
难以抑制分享的冲动,权当烧坏各位小伙伴脑瓜的最后一把稻草,不要太感谢我哦!
▋趣味运用 ❶:倒背如流
将文本逐一颠倒过来,倒序排列,这样诗情画意、才气盎然的 CONCAT,你喜欢吗?
{=CONCAT(MID(A2,101-ROW($1:$100),1))}
公式说明:
原理很简单,使用 101-ROW ($1:$100) 构建一个从 100 至 1 的逆序数组,于是 MID 就从最后一个字符开始,逐一提取字符,再由 CONCAT 组合即可。
补充一点,如果 MID 的第二个参数大于文本的长度,MID 截取的结果本身就为空,直接连接即可。
▋趣味运用 ❷:慧眼识珠
小屁孩秀下限造出来的各色错别字,绝对是语文老师和家长的梦魇。
还好有 CONCAT 帮忙纠错,妈妈再也不用担心我的眼睛。
{=CONCAT(IF(MID(B2,ROW($1:$100),1)<>MID(A2,ROW($1:$100),1),MID(B2,ROW($1:$100),1),""))}
▲ 左右滑动查看
公式说明:
使用 MID+ROW 函数分别从 A2 和 B2 中将字符逐一提取出来,一一比对,错误则返回 B2 中对应的字符,正确返回空。
最后由 CONCAT 函数将这些错别字都连接起来即可。
此篇文章,是否刷新了你对 CONCAT 函数的认知?
其实,CONCAT 的孪生加强版函数 TEXTJOIN 也能够完成上述操作,甚至做得更好,学有余力的小伙伴不妨尝试一下!
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小花
广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。