感谢 CONCAT ,你拯救了 Excel 的文本处理能力
消息来源:baojiabao.com 作者: 发布时间:2024-05-18
《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),作者:小花
2023-02-12 19:17:41相关文章
- 美国法院裁定阿里须为Squishmallows玩具侵权案答辩
2023-12-28 19:59:34
- 小米汽车传员工3700人 雷军称小米汽车不可能卖9万9
2023-12-28 19:41:57
- 国家新闻出版署:认真研究《网络游戏管理办法(草桉徵求意见稿)》关切 实行前进一步完善
2023-12-28 19:14:56
- 天猫新规可以无条件申请“仅退款”了?淘宝天猫又离狗多多零元购近了一步
2023-12-28 18:57:55
- 印度以打击金融犯罪为由逮捕了两名 vivo 高管
2023-12-26 16:49:01
- 在国外微信收不到国内信息?微信和WeChat将被拆分
2023-12-15 10:40:15
- 苹果iPhone15 系列手机发布最新消息 预计上市发布时间9月
2023-08-06 23:21:02
- 华为将发布鸿蒙HarmonyOS4操作系统 功能五大升级支持设备清单
2023-08-06 23:17:37
- 整治自媒体网红账号 400万粉丝网红发布擦边视频被无限期封禁
2023-07-12 09:56:09
- 网传微信文件传输助手是真人是真的吗?微信官方回应
2023-06-27 15:53:32
- 电信移动送手机成了“信用购”?你上了运营商的贷款套路了吗?
2023-06-12 17:18:55
- 中国电信广东地区崩了无信号 客服回应已在核实处理
2023-06-08 15:39:04
- 消息称小米新能源汽车价格表正讨论定价区间:双版本不同配置,高配或超 35 万元
2023-03-06 12:56:03
- 华为因制裁被传或分拆剥离手机业务? 内部人士回应:可能性不大.
2023-03-05 23:26:41
- OPPO正式发布安第斯智能云,让终端更智能
2023-02-24 16:02:27
- 华为与OPPO签订全球专利交叉许可协议 包括5G蜂窝通信专利
2023-02-24 16:02:26
- 老蛙将推MINI镜头新品:目前未知具体规格 官宣将于12月20日发布
2023-02-24 16:02:26
- 首发全新35mm定制光学系统 努比亚Z50性能同样强悍
2023-02-24 16:02:25
- Redmi K60屏幕细节曝光:全系标配2K护眼柔性直屏+5000mAh大容量电池
2023-02-24 16:02:25
- OPPO Find N2今天发 合金金属折叠屏更轻了
2023-02-24 16:02:24