Excel经典嵌套公式(正则REGEXP+FILTER+TEXTJOIN),万能关键字查找!神了!

大家好,这一章给大家分享一个非常经典的案例,你一定会用到这个搭配.

不连续的关键字批量查找.

会要用到的函数:

正则表达函数

TEXTJOIN函数

FILTER函数

NOT函数

这个案例真的你一定遇到过,如下所示:

下方是一份数据明细,我想要用关键字去进行查找,

例如你要查找北京,天津这样的关键词,进行内容的批量调取,这个很简单,我们讲过很多次了.

但是如果你想要如下图:

查找不连续的关键字,北京ABC

这个中间包括类似于:北京天翔ABC有限公司

这个中间是隔开的状态的关键字,如果用常规方法去做,就没法弄了.

我们之前讲过这种不连续的关键字的查找,但是之前讲的是唯一值的查找.

但是这一章我们要做的是批量明细不连续关键字查找,情况就不一样了.

文字解析部分:

(下方视频有详细解析)

1.逐字拆解

使用正则表达式函数:

=REGEXEXTRACT(B1,".",1)

进行拆解每一个字符,有了正则拆解以后彻底告别MID函数逐字拆解.

这个函数的意思是:

把B1单元格的单元格拆解成一个一个单独的文字.

"."的意思是任何单一字符的意思.

2.合并元字符搭配

公式:

=TEXTJOIN(".*",,REGEXEXTRACT(B1,".",1))

意思是:

把刚才的关键字:北京ABC

变成:北.*京.*A.*B.*C

在正则表达式中:

"."代表任何字符的意思,"*"代表0次或以上的次数.

组合在一起就是:任何字符0次或以上.

例如:北京天翔ABC有限公司

满足这个元字符搭配

北(0次文本)京(多次文本)A(0次文本)B(0次文本)C(0次文本)

所以用这个组合的元字符正好是我们需要的.

3.正则表达式融合

公式:

REGEXEXTRACT(明细表!B2:B131,TEXTJOIN(".*",,REGEXEXTRACT(B1,".",1)))

意思是:

正则函数(明细客户列,北(0次文本)京(多次文本)A(0次文本)B(0次文本)C(0次文本))

但凡是满足条件的都会被调取显示部分文字,不满足条件的都会显示错误.

4.判断

公式:

=ISERROR(REGEXEXTRACT(明细表!B2:B131,TEXTJOIN(".*",,REGEXEXTRACT(B1,".",1))))

判断这一列是不是错误,如果是错误就TRUE,反之满足条件的为FALSE

前面加一个NOT把TRUE和FALSE做颠倒处理.

意义在于:

错误让其显示为FALSE,有文本正常显示的转化为TRUE.

因为错误是不满足条件的.

5.结果调取

公式:

=FILTER(明细表!A2:K131,NOT(ISERROR(REGEXEXTRACT(明细表!B2:B131,TEXTJOIN(".*",,REGEXEXTRACT(B1,".",1))))))

把满足条件的用FILTER调取出来.

完美.

这个公式是个万用公式,不但连续也好,不连续也好,这个公式更是一个适合于所有情景的方法.

注:如果你是WPS,方法一模一样,只是公式写法不完全相同,原理一样:

=FILTER(明细表!A2:K131,NOT(ISERROR(REGEXP(明细表!B2:B131,TEXTJOIN(".*",,REGEXP(B1,".",3)),3))))

以上,

如果没有看懂文字解析部分,可以看看下方的视频解析部分.

我演示的素材下载:

复制下方文字,发送公众号信息获取课件:

REGEXP搭配FILTER万用查找

REGEXP搭配FILTER万用查找.xlsx

小贴士:

之前一些课件由于时间太久失效,所以现在的下载素材方式,回复公众号信息获得,如果有一天素材失效,记得给老徐留言,我再补上.

如果想要系统学习Excel,可以看看这个链接: