Excel案例分享:利用函数公式来检验身份证号码的真假

星萱君_4883

星萱君_4883

2022-09-13

19825人浏览

转载

在之前的文章《excel案例分享:5个只靠“拖拉”就实现的高效技巧》中,我们学习了5个excel表格只靠“拖拉”就实现的高效技巧。而今天我们聊聊excel检验身份证号码真假的函数公式,快来看看!

说起假疫苗、毒奶粉这些骇人听闻的事情,真的是愤怒加无奈,对于这类事情,作为普通人的我们,只能希望有关部门能够加大管理检查力度,杜绝再次发生这样的恶性事件。同时对于自己的工作也要更加细心,在很多容易出现错误的环节反复核查,避免产生一些不良的后果,比如员工档案中的身份证号码就是一个非常容易发生问题的重灾区!

今天我们就来和大家分享一个核查身份证号码是否有错误的公式。

一般来说,身份证号码会出现两类错误,长度错误和内容错误。

出现错误的原因大多数都输入过程中发生的,对于长度错误来说,目前都是18位的号码,如果长度出现错误,比较容易发现,但是如果其中的某一个数字出现错误,那就非常难以发现,以后就有可能会造成一些严重的后果,因此,今天重点就是核查内容的正确性。

一、身份证号组成原理

首先需要了解一点关于身份证号码的编码规则:

1.png

18位身份证号码组成:ddddddyyyymmddxxsp共18位,其中:1-6位是地区编码,7-14为出生日期,15-17这三位是性别代码,最后一位是根据前面的17个数字得到的校验位。

校验位的计算规则比较复杂:

(1)前十七位数字本体码加权求和公式

S = Sum(Ai * Wi), i = 0, ... , 16 ,先对前17位数字的权求和

Ai:表示第i位置上的身份证号码数字值

Wi:表示第i位置上的加权因子

Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

(2)计算模

Y = mod(S, 11)

(3)通过模得到对应的校验码

Y: 0 1 2 3 4 5 6 7 8 9 10

校验码: 1 0 X 9 8 7 6 5 4 3 2

要检验一个身份证号码是否有误,原理就是按照这个规则计算出校验码,再与最后一位数字去比较,如果不一致就是有问题了。对于这个规则理解起来还是比较费劲的,我们举个例子来看看:

1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为:

7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2。

Excel公式工具(专业版)
Excel公式工具(专业版)

从自然语言描述生成Excel公式,诊断表格错误,支持VLOOKUP、条件求和等常用函数。

下载

2、将这17位数字和系数相乘的结果相加。

3、用加出来和除以11,看余数是多少?

4、余数只可能有0-1-2-3-4-5-6-7-8-9-10这11个数字。其分别对应的最后一位身份证的号码为1-0-X -9-8-7-6-5-4-3-2。

5、通过校验码对比得知如果余数是3,就会在身份证的第18位数字上出现的是9。如果对应的数字是10,身份证的最后一位号码就是罗马数字2。

例如:某男性的身份证号码为【53010219200508011x】, 我们看看这个身份证是不是合法的身份证。

首先我们得出前17位的乘积和

【(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+(2*4)+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8*5)+(0*8)+(1*4)+(1*2)】

是189,然后用189除以11得出的结果是189/11=17----2,也就是说其余数是2。最后通过对应规则就可以知道余数2对应的检验码是X,所以,可以判定这是一个正确的身份证号码。

二、Excel检验身份证号码真假

原理明白了,现在的问题就是如何将这个计算过程用Excel的公式来表示出来,在这里将公式分享给大家:

=IF(RIGHT(A2)=MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假")

注意这是个数组公式,输入或者粘贴公式后,需要同时按住Ctrl和shift键再回车。

2.jpg

公式看上去非常复杂,使用的时候只需要把两个A2修改为自己表格中身份证号对应的位置即可。

简单来说一下公式的思路吧,核心部分就是

MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)

这一串,作用是将前面的17位数字按照校验码的规则计算出来,再与RIGHT(A2)做比较,RIGHT(A2)的作用就是从A2中提取最右边的一位出来,根据比较结果用if函数来进行判断。

我们可以再用这个公式来检验一下文章开头图片中的身份证号码是否正确:

3.jpg

结果是假。

不妨用这个公式去检验一下你的表格中是否有错误的身份证号码吧,对于判定为错误的号码及时进行更正,将问题扼杀在萌芽阶段!

相关学习推荐:excel教程

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Excel 教程
Excel 教程

共162课时 | 45.9万人学习

成为PHP架构师-自制PHP框架
成为PHP架构师-自制PHP框架

共28课时 | 3.7万人学习