【社区答疑】帮公安干警提取车牌号信息

张俊

WPS函数专家

要想下班早,表格必须用的好!

大家好,我是张俊。

场景

前段时间在社区看到@清华学弟任泽岩大佬(泽岩师兄)发布动态数组行业案例:帮公安干警提取车牌的帖子,让我学习到了很多知识,如函数用法、问题分析、逻辑思路和场景应用等,在此特别感谢@清华学弟任泽岩大佬(泽岩师兄)和@汪道长汪英伦老师。具体如下图所示:

需求方汪道长汪英伦老师的需求

泽岩师兄解答结果

但是一直感觉泽岩师兄需要借助辅助表(各省级车牌号简称)来查找,我也一直想跳过辅助表看是否可以完成。另也发现具体问题如下:

1)通过一长串函数计算处理的结果与数据源顺序不一致

2)通过FIND函数只能查找第一次匹配的位置,如果出现了同省两个车辆信息,则会出现错误

3)当出现不符合的标点符号也许会被计算出来

数据源简单改变后,出现的问题

也许是长时间思考这个问题吧,也许真的是“日有所思夜有所梦”,就在昨天晚上梦到了需求人@汪道长汪英伦老师,在另外一个微信群里联系我,让我解决这个问题。也许真的是冥冥之中的安排,我今天醒来迫不及待打开与汪老师的对话,发现我们竟然没有沟通过,也有且只有一个微信群(去年WPS软件技能大赛决赛群),然后就重新打开了这个题目,果然思绪瞬间涌上心头,有了很多种解决方法。

  1. 问题分析

1.1 车牌号格式

我们先来了解下常见的车牌号(比较特殊的暂时不考虑,只考虑最常见的车牌号)格式,各地省份简称+大写字母+5-6位大写字母和数字混合,具体如下:

还是老规矩,我们重新来分析问题,我们需要判断是否为车票号的要素并且需要根据ASCII码来判断,具体如下:

1)车牌号第1个字符为汉字(ASCII码大于122

2)车牌号第2个字符为大写字母(ASCII码大于123在65到90之间

3)车牌号第3-7/8位应为大写字母或0-9数字混合(ASCII码大于123在48到90之间,且不包含58至64

1.2 ASCII码是啥?

ASCII(American Standard Code for Information Interchange)码是一种常用的字符编码标准,最初由美国国家标准学会(ANSI)于1963年发布,后来被国际标准化组织(ISO)接受为国际标准ISO 646。大家如果感兴趣的话,可以在网络上搜索和详细了解,在此就不一一赘述。

1.3 ASCII码与WPS表格函数

说了这么多ASCII码,那与WPS表格函数有啥关系了呢?当然有了,我们可以通过CHAR函数将数字转化为对应的字符(从左到右),也可以通过CODE函数将字符转化为数字(从右到左),具体如下图所示:

通过上图可以看出,对我们当前最常用的就是48至122之间,如果只考虑大写字母不考虑小写字母的话,就是48到90之间,且不考虑58至64。

因此如果我们使用常规的函数来处理的话,就需要通过CODE函数来判断字符是否满足。

  1. JS宏自定义函数

首先想到的方法就是通过正则表达式提取,而WPS表格默认自带的JS宏就支持自定义函数,代码具体如下:

function REG(rng,str1,str2){

//入参1:需要处理的单元格区域

//入参2:需要编写的正则表达式字符串

//入参3:如匹配多个数据,之间的连接符

return rng.Value2.match(eval(str1)).join(str2)

}

具体公式如下:

=reg(A2,"/\W[A-Z]\w{5,6}/g","、")

实现效果如下图所示:

  1. 内测REGEXP函数

同理通过WPS表格内测版本的REGEXP函数(正则表达式)配合TEXTJOIN函数也可以完成,具体公式如下:

=TEXTJOIN("、",,REGEXP(A2,"\W[A-Z]\w{5,6}"))

具体效果如下图所示:

📢

注意:

REGEXP函数(正则表达式)目前还是内测函数,预计在2024年4月底或5月初正式上线,如果大家无法内测的话,建议大家耐心等待一下。

  1. 正式版已支持函数

能否能用现有可以使用的正式版支持呢?答案是肯定的,只不过公式写的稍微过长,具体公式如下:

=TEXTJOIN("、",,TOROW(IF((CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))))),1))>122)*IFERROR(CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58))))))+1,1))>64,0)*IFERROR(CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58))))))+1,1))<91,0)*IFERROR(CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58))))))+6,1))>47,0)*IFERROR(CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58))))))+6,1))<91,0),MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))))),IF(IFERROR(CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58))))))+7,1))>47,0)*IFERROR(CODE(MID(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),SEQUENCE(LEN(CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58))))))+7,1))<91,0),8,7)),""),3))

具体函数公式简单解析如下图所示:

具体效果如下图所示:

  1. 通过LET函数简化公式

发现上面的公式太长了,能否通过现有的LET函数简化下呢?那是必须的,通过LET函数简化后的公式来了,具体如下:

=LET(a,CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(7,,58)))),b,SEQUENCE(LEN(a)),TEXTJOIN("、",,TOROW(IF((CODE(MID(a,b,1))>122)*IFERROR(CODE(MID(a,b+1,1))>64,0)*IFERROR(CODE(MID(a,b+1,1))<91,0)*IFERROR(CODE(MID(a,b+6,1))>47,0)*IFERROR(CODE(MID(a,b+6,1))<91,0),MID(a,b,IF(IFERROR(CODE(MID(a,b+7,1))>47,0)*IFERROR(CODE(MID(a,b+7,1))<91,0),8,7)),""),3)))

具体函数公式简单解析如下图所示:

具体效果如下图所示:

  1. 方法总结

本次分享了4种方法分别是JS宏自定义函数、内测REGEXP函数、现有支持函数和通过LET函数简化,都是可以不依赖辅助表,都可以严格规避无效字符,也避免了前面所提及的问题,这4种方法各有优劣,具体如下:

1)JS宏自定义函数,对WPS表格的支持的JS宏版本的函数都适用,只是不管是书写代码还是自定义函数的公式,都是相对比较简单的;但是对于初学者不太友好,只能“依葫芦画瓢”。

2)内测REGEXP函数对于即将发布的新版本来说,是相对更加简单通俗易懂;但是也需要掌握正则表达式的专业知识,才能写出更加符合自己习惯的函数公式,也需要等待支持REGEXP函数的版本发布后才能使用。

3)现有支持的函数公式,虽然现在最新版都已经可以使用;但是逻辑复杂,书写更加费劲,需要较强的思维逻辑和函数公式的调试能力。

4)通过LET函数简化后,虽然看起来更加简单了,但是理解难度大大增加。

以上4种方法建议大家根据自己的需求选择适合自己的方法,也欢迎大佬们有更好的方法可以具体说明。

都看到这里了,记得得❤️❤️❤️点赞(红心❤️❤️❤️收藏(五角星并在评论区评论“我学会了!”,您的❤️❤️❤️点赞(红心❤️❤️❤️收藏(五角星和评论是对我最大的支持!

🚩

练习文件:👉【社区答疑】帮公安干警提取车牌号信息👈


个人往期帖子合集:【帖子合集】个人往期帖子合集来了!

相关帖子链接:动态数组行业案例:帮公安干警提取车牌

你学会了吗?
学会了!
3 (75%)
太难了!
1 (25%)
4人参与 投票已截止
上海
浏览 1212
1
19
分享
19 +1
26
1 +1
全部评论 26
 
· 贵州省
1
回复
 
厉害了
· 浙江省
1
回复
 
厉害的俊哥
· 江苏省
1
回复
张俊

WPS函数专家

加油加油,期待旋哥的分享!
· 上海
1
回复
 
清华学弟任泽岩

创作者俱乐部成员

我才明白啥叫ASCII!我之前一直以为是ASC2码
· 辽宁省
2
回复
张俊

WPS函数专家

哈哈,加油,奥利给!坐等泽岩师兄分享更加优秀的作品,再次在此特别感谢泽岩师兄的分享!
· 上海
1
回复
 
优秀如斯,JSA自定义函数+正则,威武霸气
· 吉林省
1
回复
wps新路

WPS函数专家

大神你好
· 重庆
1
回复
 
清华学弟任泽岩

创作者俱乐部成员

手动at汪老师
· 辽宁省
3
回复
张俊

WPS函数专家

期待汪老师能看到!
· 上海
1
回复
 
=LET(文本,B2,数组,VSTACK(CHAR(SEQUENCE(26,,65)),SEQUENCE(10,,0)),车牌号,TEXTSPLIT(文本,TEXTSPLIT(文本,数组,,1),,1),车牌地区,MID(文本,FIND(车牌号,文本)-1,1),组合车牌,车牌地区&车牌号,合并车牌,TEXTJOIN(",",,组合车牌),合并车牌)
· 江西省
2
回复
有个意外情况 例如: 放在A13街道的黑黑色川A528D2 就要加个判断 =LET(文本,B5,数组,VSTACK(CHAR(SEQUENCE(26,,65)),SEQUENCE(10,,0)),车牌,TEXTSPLIT(文本,TEXTSPLIT(文本,数组,,1),,1),车牌号,IF(LEN(车牌)>5,车牌,""),车牌地区,IFERROR(MID(文本,FIND(车牌号,文本)-1,1),""),组合车牌,车牌地区&车牌号,合并车牌,TEXTJOIN(",",,组合车牌),合并车牌)
· 江西省
2
回复
 
懒得批爆

创作者俱乐部成员

学习了下,发觉这可不容易,国内的车牌类型太多了,是个大型工程。 据我所知,公安系统有这么一套系统,兼容的车牌几乎包括所有的社会车辆和制式车辆,部分内部车辆还是不兼容。这么一套函数公式就能全部兼容的话,公安部会给你颁奖的。 社会车辆就是京A12345和京AD12345这类的油车和电车车牌、教练车(京A1234学)、使123456、挂车车牌(京A1234挂),厂区车牌(场内A12345,这种一般在厂区、码头、库区用的比较多,也能短暂上社会道路) 制式车辆包括,军警车牌(KA12345,军A12345、WJ1212345【WJ后面两个12是右下角小标,后面是正常数字】,警车牌京A1234警等等),民航车牌(民航A1234,好像还看到过机场B1234这种车牌) 网上搜了下,发现500字不够描述这么多个种类。 网上一搜一大堆https://auto.ifeng.com/qichezixun/20200818/1465300.shtml
· 四川省
1
回复
清华学弟任泽岩

创作者俱乐部成员

你500字又不够了
· 辽宁省
1
回复
 
/(京|沪|津|渝|冀|豫|云|辽|黑|湘|皖|鲁|新|苏|浙|赣|鄂|桂|甘|晋|蒙|陕|吉|贵|粤|青|藏|川|宁|琼)\w(\d|\w){5,6}/g 用正则规则可以提取出来,如果有特殊字符,可以提前过滤后再进行提取
· 广东省
2
回复
张俊

WPS函数专家

666厉害了
· 上海
1
回复
 
6
· 山东省
回复
 
方盛

创作者俱乐部成员

挺好的,主要是用不上,看不懂
· 湖北省
1
回复
张俊

WPS函数专家

哈哈,谢谢认可,不过主要是可以学习对应的思路和解析方法也很不错!
· 上海
1
回复