在两万条数据中找特定的手机号，第2种方法居然只用1秒……这个神器太牛X！

点击蓝字【秋叶 Excel】👆

发送【福利】

免费领 Excel 插件&工具，看精华文章！

本文作者：农夫、小爽

本文审核：小爽

本文编辑：雅梨子、竺兰

小伙伴们，大家好，我是农夫，专治疑难「数」的农夫；她是小爽，注定要站在 Excel 顶端的大佬~

这里有一份包含两万条手机号的数据，我们想从这些手机号里筛选出包含 AAAABB 型的靓号（从第 2~6 位开始），分析一下这些靓号的消费习惯等特征，方便后面进行精准推广，这应该怎么做呢？

之前的文章中我们讲过其中两种方法（余数判断法和拆分判断法），忘了的同学点这里复习↓↓↓

今天我们就来聊聊另外两种做法！

❶ M 函数法

❷ 正则表达式法

首先，我们来介绍 M 函数的做法，这个方法涉及的 M 函数比较多，我们重在理解一下分析思路。

PS.文末可获取本文配套练习文件；第 2 种方法更简单哦~

M 函数法

既然是 AAAABB 的模式，那我们就把所有可能出现的结果的都逐一列示出来，然后判断后筛选一下不就行嘛！

这里我们需要使用的是工具是 PowerQuery。（需要 Excel 2016 以及以上版本。）

❶ AAAABB 所有可能的结果

首先我们来思考一下：

AAAA 的模式有 9 种可能结果，也就是 1111,2222,3333……8888，9999。

BB 的模式也有 9 种可能结果，也就是 11,22,33……88，99。

然后我们把它们排列组合起来，一共 9*9=81 种结果。

👉 具体操作：

① 选择表格区域，选择【数据】-「来自表格/区域」-【确定】。

② 新建一个【空查询】，输入 M 函数-将名称修改为可能的结果。

输入公式如下：

= List.TransformMany(List.Transform({1..9},each _*1111),each List.Transform({1..9},each _*11),(x,y)=>Text.From(x)&Text.From(y))

▲ 左右滑动查看

此时就做出逐一列示了 81 种结果。

简单解析一下：

= List.TransformMany(List.Transform({1..9},each _*1111),each List.Transform({1..9},each _*11),(x,y)=>Text.From(x)&Text.From(y))

▲ 左右滑动查看

List.Transform 是一个循环函数，意思是将列表中的每个值，进行循环处理。

其中：

List.Transform({1..9},each _*1111)，目的是生成 1111~9999
List.Transform({1..9},each _*11)，目的是生成 11~99

▲ 左右滑动查看

然后用 List.TransformMany 函数进行排列重组。

公式套路：

=List.TransformMany(列表 1,each 列表 2,(x,y)=>返回方式)

▲ 左右滑动查看

意思是将列表 1 中的每一个值，与第二参数的每一个值进行排列重组。

比如：

=List.TransformMany({1..3},each {3},(x,y)=>x*y)

▲ 左右滑动查看

结果为{3,6,9}，就是将列表{1,2,3}中的每一个元素，跟{3}进行相乘。

Text.From(x)&Text.From(y))

由于 M 函数对于数据类别有严格的限制，所以我们需要利用 Text.From 将数值转换为文本，再进行合并连接。

最后套上公式：

= List.TransformMany(列表 1,each 列表 2,(x,y)=>返回方式)
= List.TransformMany( List.Transform({1..9},each _*1111),each List.Transform({1..9},each _*11),(x,y)=>Text.From(x)&Text.From(y))

▲ 左右滑动查看

❷ 判断符合条件的手机号

上面我们已经逐一列示出所有的可能的结果了，下面我们就要进一步判断，符合条件的手机号有哪些了。

由于 AAAABB 是从第二位手机号开始的，所以我们要提取第二位到最后一位的数据。

返回之前导入数据的表。

点击【添加列】-【自定义列】-新列名为：结果。

自定义列公式输入：

Text.Middle(Text.From([电话]),1)

Text.Middle 函数和工作表函数 MID 函数有点类似，都是用来提取指定位数的。

所以我们利用 Text.Middle 函数提取第二位到最后一位。

Text.Middle(Text.From([电话]),1)，

PS.M 语言中索引值（即用数字来表示数值所在的位置）是从 0 开始的，所以第二参数为 1，相当于从第二位开始提取，最后一个参数忽略，表示提取到最后的值。

此时公式显示为：

=Table.AddColumn(源,"结果",each Text.Middle(Text.From([电话]),1))

▲ 左右滑动查看

下面我们就继续修改公式：

= Table.AddColumn(源, "结果", each List.MatchesAny(可能的结果,(x)=> Text.Contains(Text.Middle(Text.From([电话]),1),x)))

▲ 左右滑动查看

添加公式中划红色区域的地方：

我们已经有去掉第一位的手机号了，也有「可能的结果」的列表了，那下面就是判断是否存在，所以我们用到了 Text.Contains 函数。

基本语法：

Text.Contains(文本,字符串)

如果文本中包含指定的字符串，则返回 TRUE，否则返回 FALSE。

List.MatchesAny 是一个列表匹配判断的函数。

它是指将列表中的每一个值，循环进行判断，结果只要有一个为 TRUE 的则返回 TRUE。

公式套路：

=List.MatchesAny（列表，(x)=> 判断条件）

▲ 左右滑动查看

下面就是判断手机号是否存在可能的结果，只要包含一个值，则返回 TRUE。

=List.MatchesAny(可能的结果,(x)=>判断条件)

▲ 左右滑动查看

判断条件：

Text.Contains(Text.Middle(Text.From(【电话】),1),x) ——x 代表每一个「可能的结果」中列表的值。
Text.Contains(去掉第一位的手机号,x)  ——去掉第一位的手机号是否包含可能的结果中的一个，如果其中有一个为 TRUE，由于 List.MatchesAny 只要存在一个，就返回 TRUE。

最后公式整合一下:

=List.MatchesAny(可能的结果,(x)=>判断条件)
=List.MatchesAny(可能的结果,(x)=>Text.Contains(Text.Middle(Text.From([电话]),1),x))

▲ 左右滑动查看

= Table.AddColumn(源, "结果", each List.MatchesAny(可能的结果,(x)=> Text.Contains(Text.Middle(Text.From([电话]),1),x)))

▲ 左右滑动查看

Table.AddColumn 是指增加一列，名称为结果。

❸ 筛选符合条件的部分

前面我们已经判断出来了，是否包含 AAAABB，下面我们直接将 TRUE 的结果筛选出来就可以。

最后将结果加载出来就搞定啦！

用 PowerQuery 来解决这个问题是很常见的思路，我们先把可能出现的结果都逐一列示出来，最后再来判断筛选。

当然还不止这种思路，别忘了，还有我们强大的正则表达式~

前面用了 N 步，对吧？

下面的介绍的方法，只需要一个函数公式，立马搞定！

下面，我们来看看吧~

正则表达式

PS.这里使用的是拉登老师自创的公式 plus 函数中的一个，获取方式见文末~

我们在 B2 单元格中输入公式：

（需要提前安装公式 plus。）

=P_REG_FIND(A2,"(\d)\1{2}(\d)\2{1}")

▲ 左右滑动查看

下拉填充，一下子就搞定！

上面的公式使用了公式 plus 中的 P_REG_FIND 函数，函数语法如下：

=P_REG_FIND(目标单元格，"正则表达式")

▲ 左右滑动查看

然而，正则表达式又是啥？

其实，就相当于通配符的升华。

通俗理解是文本数据的超级匹配模式，用更简洁的方法来指定所需的文本。

类似于一对多模式。

其中，一就是由元字符组成的通用模式，多则是文本中符合该模式的文本数据。

看起来好复杂的样子，不过它真的超级强大！

下面我们来简单的解释一下这个神奇的函数公式：

=P_REG_FIND(A2,"(\d)\1{3}(\d)\2{1}") 

▲ 左右滑动查看

最主要的是第二参数，正则表达式的语法：

"(\d)\1{3}(\d)\2{1}"

▲ 左右滑动查看

\d 代表的是数字，等价于 0-9。——相当于 0 到 9 的任意一个数字 ()代表一个子表达式的开始和结束位置，即将匹配到的数字单独分为一组，以便后面直接调用，有几个小括号就分为几组；——(\d)\1{3}(\d)\2{1}，这里有两组 \n 代表将第 n 组的匹配模式进行复制，如\1 就指复制第一组的匹配模式；——(\d)\1{3}(\d)\2{1}，\1 复制前面的一组（第 1 组），也就是前面的(\d)

{n}代表前面最近匹配到的数据必须有 n 个，如，2{3}指数字 222 才会匹配上，而 2 和 22 都不会匹配。

拆开 "(\d)\1{3}(\d)\2{1}" 进行理解，我们会更加清楚正则的语法规则~

(\d)——可以是 0 到 9 的任意一个数字。
\1 ——复制前面一组，(\d)，可以是 0 到 9 的任意一个数字。
{3} ——将前面的\1 的数字重复三次，加上前面一个就是连续的 4 位数字，比如上一个是 2，再重复 3 次 222。
(\d)——可以是 0 到 9 的任意一个数字。
\2{1}——复制前面一组，(\d)，可以是 0 到 9 的任意一个数字，然后再重复 1 次，比如上一个是 1，再重复 1 次 1，与上一个连起来就是 11。

综上所述：

(\d)\1{3}指\1 将匹配\d 所匹配到的数字，而{3}则会将\1 所匹配到的数字重复 3 遍，即匹配 4 个相同的数字；(\d)\2{1}也是这样，只不边是匹配 2 个相同的数字。

正则表达式方法就到这里讲解完了~

需要注意的是，你的电脑上，默认是没有 P_REG_FIND 函数的，这是函数公式插件「公式 plus」中特有的函数。

关注公众号【秋叶 Excel】

后台回复：【PLUS】

即可免费下载，使用 P_REG_FIND 函数~‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

↓↓↓

关于更多正则表达式的介绍，可以戳：

知识回顾

逐一筛选法：

❶ 利用 List.TransformMany 遍历出所有可能的结果

❷ 利用 Text.Contains 进行判断是否包含

❸ 筛选出符合条件的结果

正则表达式：

❶ 利用公式 plus P_REG_FIND 函数引用正则表达式进行查找

❷ 正则表达式的基本语法 

练习文件：

关注公众号【秋叶 Excel】

后台回复：【0608】

即可免费获取本文配套练习文件~

↓↓↓

好了，今天的文章就到这里了，喜欢的小伙伴就动动小手点赞和分享吧~

如果你有任何疑问，欢迎给我们留言哦~

我是农夫，专治疑难杂「数」的农夫~

她是小爽，注定要成为站在 Excel 顶端的大佬~

欢迎加入秋叶Excel专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

看完本文，是不是直呼「涨见识了！！！」

如果你还想学更多打印技巧，小 E 推荐大家入手《和秋叶一起学 Excel》图书哦~

全书扫码即可获取视频教学，别让 Excel 成为你按时下班的拦路虎！

原价 99 元，现在仅售 59.4 元！

点击下方图片立抢买书超值优惠价！↓↓↓

↓↓↓

遇到有价值的文章

不放过 !

👇👇👇

动动小手

分享给朋友~

👇👇👇