乐于分享
好东西不私藏

EXCEL篇-全称匹配简称,XLOOKP+正则函数

EXCEL篇-全称匹配简称,XLOOKP+正则函数
场景:左边 A‑B 列是【简称 + 余额】对照表;D 列是系统导出的银行完整全称,需要根据长长的全称,自动匹配出对应余额,填充到 E 列

一、公式思路

在E2单元格输入公式:

=XLOOKUP(TRUE,MAP(A$2:A$5,LAMBDA(x,REGEXP(D2,TEXTJOIN(".*?",,MID(x,SEQUENCE(LEN(x)),1)))<>"")),B$2:B$5,"无匹配")

① 用MID+SEQUENCE,把简称拆成单个字

② 用TEXTJOIN,把每个字之间插入".*?",拼接成正则表达式

③ 用REGEXP,拿正则去全称里匹配,看能不能对上

④ 用MAP+LAMBDA,对每个简称重复上述操作,得到一组TRUE/FALSE

⑤ 用XLOOKUP,找到第一个TRUE,返回对应的余额

二、公式拆解

以D2="中国工商银行股份有限公司"为例,逐层拆解:

步骤
函数/表达式
计算结果
说明
①
MID(x,SEQUENCE(LEN(x)),1)
{"工","行"}
把简称"工行"拆成单个字,SEQUENCE(2)生成{1,2},逐个取字
②
TEXTJOIN(".*?",,①)
"工.*?行"
在每个字之间插入".*?",拼接成正则表达式
③
REGEXP(D2,②)
"工商银行"
用正则"工.*?行"去全称匹配,"工"和"行"之间有任意字符,匹配成功返回匹配内容
④
③<>""
TRUE
匹配结果不为空,说明全称中包含该简称的字符组合
⑤
MAP(A$2:A$5,LAMBDA(x,①~④))
{TRUE,FALSE,FALSE,FALSE}
对每个简称重复上述操作,"工行"匹配成功TRUE,"农商银行""招行""建行"匹配失败FALSE
⑥
XLOOKUP(TRUE,⑤,B$2:B$5,"无匹配")
5
找到第一个TRUE的位置,返回对应的余额5

三、正则表达式".*?"是什么意思?

以"工行"为例:

  • MID拆字:{"工","行"}
  • TEXTJOIN拼接:"工.*?行"
  • 正则含义:匹配"工"开头,"行"结尾,中间可以有任意字符

四、函数语法  []内为可选填参数,剩余参数为必选项

1. MID(文本, 起始位置, 提取字符数)

作用:从文本中指定位置提取指定数量的字符

参数
参数说明
本例参数
文本
要提取的文本
x(每个简称)
起始位置
从第几个字符开始
SEQUENCE(LEN(x))(生成1,2,3…的序列)
提取字符数
每次取几个字
1(每次取1个字)

2. TEXTJOIN(分隔符, 忽略空值, 文本1, [文本2], ...)

作用:用指定分隔符将多个文本拼接成一个

参数
参数说明
本例参数
分隔符
文本之间插入的内容
".*?"
忽略空值
TRUE=跳过空值
省略(默认TRUE)
文本
要拼接的文本
MID拆出的单个字数组

3. REGEXP(原始字符串, 正则表达式, [匹配模式], [替换内容])

作用:用正则表达式在文本中查找匹配内容

参数
参数说明
本例参数
原始字符串
要搜索的文本
D2(银行全称)
正则表达式
匹配规则
TEXTJOIN拼接出的正则
匹配模式
0=提取(默认)
省略(默认0)
替换内容
替换匹配到的内容
省略

4. MAP(数组, LAMBDA(变量, 表达式))

作用:对数组中的每个元素,执行LAMBDA定义的运算,返回一组结果

参数
参数说明
本例参数
数组
要逐个处理的区域
A$2:A$5(简称列表)
LAMBDA
对每个元素执行的操作
判断全称是否匹配该简称

5. XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式], [搜索模式])

作用:按条件查找并返回对应结果

参数
参数说明
本例参数
查找值
要找什么
TRUE
查找数组
在哪里找
MAP返回的TRUE/FALSE数组
返回数组
找到后返回什么
B$2:B$5(余额)
未找到值
找不到时返回
"无匹配"

XLOOKUP函数的详细用法可以参考这篇文章Excel篇-XLOOKUP vs FILTER:Excel 一对一 / 一对多查找完整教程,做表必备


👉 收藏 + 在看,用的时候不迷路 

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料