感谢 CONCAT ,你拯救了 Excel 的文本处理能力 |
您所在的位置:网站首页 › excel如何取交集 › 感谢 CONCAT ,你拯救了 Excel 的文本处理能力 |
《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之家所有文章均包含本声明。 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |