关注并星标★Excel表哥,不错过每一次推送~
▲星标Excel表哥★,不错过模板升级更新
最近解放军在台湾省的环岛军演看的大家热血沸腾！
还记得之前看了一个新闻说自2021年10月1日起，持台湾居民居住证的台胞就可以参加北京市城乡居民基本养老保险了[参考新闻1]。
新闻中提到的台湾居民居住证和我们平常使用的身份证看上去外观几乎一样，那么问题来了：
请问台湾省的身份证号码是以什么数字开头呢？
实际上台湾在我们的身份证编码里是有预留编号的，台湾省的身份证ID编码以83开头。例如我随意在查询网站上"杜撰"了一个身份证号，查询到的归属地信息如下：
▍注：数据来源查询网站http://www.51zzl.com/shenfenzheng/
动手能力强的读者朋友可能这会儿自己也去试了，但别着急，我随意输入一串数字可以查到，你随便输十有八九会是这样：
这是因为你输入的身份证号码不符合编码规则！接下来我们借助Excel来给大家解析一下身份证号码中都藏着哪些秘密吧。
01 身份证号码编码规则
在我国现行的身份证系统中共有15位和18位两种身份证号码，第一代身份证大多为15位号码，由于15 位身份证只能为1900.01.01到1999.12.31出生的人编码(千年虫问题)，所以后来逐步替换为18位的身份证号码。
我们现在使用的18位身份证编码规则如下：
DDDDDD YYYYMMDD XXG Y
各组成部分说明：
其中第18位，校验位的计算规则如下：
- 将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的权重系数分别为：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。
- 将这17位数字和系数相乘的结果相加。
- 用加出来和除以11，看余数是多少。
- 余数只可能有0－1－2－3－4－5－6－7－8－9－10这11个数字。其分别对应的最后一位身份证的号码为1－0－X －9－8－7－6－5－4－3－2。(即余数0对应1，余数1对应0，余数2对应X...)
02 Excel计算身份证号校验位
根据上面的计算步骤，我们就可以使用Excel来计算和验算身份证号的校验位：
▍过程一：提取身份证号的每一位为
了计算每个身份证号码的第18位校验位，一种可行的办法是按照标题1中的步骤首先提取出身份证号码的第1~17位，使用到的函数如下：例如，C4单元格的公式为：
=--MID($B4,COLUMN(A$1),1) 从C4单元格向右向下填充公式既可得到C~S列的所有17位身份证数字。
COLUMN(A$1)函数的运算结果表示A$1的数字列标1，随着公式向右拖动填充一列，COLUMN(A$1)则自动填充为COLUMN(B$1)，返回B$1的数字列标2。同理COLUMN(Q$1)=17。对$符号表示的单元格格绝对引用方式不熟悉的读者，请参考下文：
MID函数用于从字符串$B4中分别提取从第1-17位开始的1位数据，由于提取出来的内容为文本字符格式，则通过--操作强制将其转换为数字格式以方便后续计算。
▍过程二：计算系数加权和
依照标题1中的步骤1，应首先计算提取出的身份证号前17位数字与第三列权重系数的数字加权和，在Excel中有一个函数SUMPRODUCT专门用来做这个事情，于是：
SUMPRODUCT函数用来计算相应的数组和区域的和，同样的可以参考如下例如文章：
SUMPRODUCT函数详解
▍过程三：计算加权和除11的余数
接下来只需要使用MOD函数计算过程二中的加权和除以11得到第一节中步骤3中的余数。
▍过程四：根据余数校验位表查询校验位
接下来需要首先建立一个余数与校验位的对照表，如下：
然后我们使用经典的VLOOKUP函数根据过程三中计算出来的余数查找对应的校验位即可。我们把上面的过程一~过程四合并起来写成一个包含几个函数嵌套的总公式：
F8单元格综合公式为：
=VLOOKUP(MOD(SUMPRODUCT(C4:S4,$C$3:$S$3),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0) 公式中{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2}，{}括起来的表示为一个数组，即过程四中的校验位表，其中","表示同一行不同单元格之间的分隔(数值位于不同列)，";"则表示不同行之间的分隔。这个{}中的数字只需要在选中公式中的区域引用(数组)，按F9进行手动计算即可得到：
03 更加集成的公式
有的朋友还希望公式更加集成，甚至不希望单独提取身份证号中的每一位数字，其实在Excel中也是比较容易实现的，只是公式就会更长一些了。
例如下面不需要任何计算过程辅助列的一个公式：
写出这样长的公式也没什么好自豪的，不信你看着，时间长了连自己也忘记了，可维护性实在太差。新手朋友还是老老实实使用上文介绍的辅助列方法吧。
▍参考新闻1
标题：10月1日起，台胞在北京重要政策生效
链接：https://www.163.com/dy/article/GG0AKU160550LMMF.html
今天的分享至此结束，恭喜读者朋友们又获得一项新技能！
作者：Excel表哥
由于公众号更改了推送规则，建议读者朋友将Excel表哥微信公众号设置星标★，这样就不会错过最新的模板升级更新和文章干货推送啦！
我们的推文时间是中午12:16,请记得抽空来看哦~
Excel表哥的项目管理模板已获2300+付费阅览↓↓↓
带日历交互功能的To-Do List模板
另外一个非常强大的文件管理工具↓↓↓
往期推荐
▌带万年历版Excel To-Do List模板V4.0
▌项目管理模板V4.0重大升级
▌全网最好用的Excel文件管理神器V4.0
注：本平台所载原创文章均为作者辛苦创作，转载请联系作者并标明出处。处处留心皆学问，建议大家可以将这篇推文收藏，以备不时之需。
上一篇：领势路由器怎样啊？
下一篇：去冰岛留学的方式？ |
窥视卡
雷达卡
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜