从身份证号码提取归属地等相关信息,你还在纠结用什么公式?真的out了!

关于Excel内将身份证号码提取归属地、生日、性别等,有以下两个方法:

一、公式法

获取全国身份证归属地信息表,然后用公式直接读取,方法比较简单,如下所示:

1、身份证归属地获取

可以在网上搜索,其实很多地方都能找到相关的信息,最好是直接的Excel表格式的,如果需要,也可以从文末分享的配套数据链接中下载。如下图所示:

 2、公式读取归属地相关信息

有了这个基础的数据表后,根据身份证号码提取户籍地就很简单了,公式如下(假设身份证号码在A2单元格):

=VLOOKUP(LEFT(A2,6),'全国身份证号码属地(无港澳台)'!A:C,2,0)

如下图所示:

二、网络直查法

上面的公式法虽然简单,但是有一个问题,就是获得的归属地基础信息表无法更新,而且大部分现在能找到的版本都不包含“撤县变市”的情况,所以,有些身份证号码无法识别。因此,个人更加推荐使用Excel2016的新功能Power Query(Excel2010或Excel2013可到微软官方下相应的插件)进行网络直查,操作简单,而且信息全面。具体方法如下:

输入链接(红色部分为身份证号码,按实际需要修改),如下:

http://qq.ip138.com/idsearch/index.asp?action=idcard&userid=440924198810032838

确定后就得到相应的信息:

通过简单的编辑,就可以将数据返回Excel里了,如下图所示:

当然,如果希望能直接输入,然后刷新出结果,那么需要再学习一些Power Query功能的知识,入门系列请看《新整理Power Query入门操作基础20篇【含配套材料下载】》。

以上是个人总结的提取身份证户籍地的两个方法,公式法比较简单,但可能会出现信息更新不及时的情况,使用Excel新功能Power Query进行网络直接查询的方法稍为复杂一点儿,但是也不难,而且信息更加完整,并能保持与网络最新信息的同步。

因此,个人推荐使用Power Query网络直查的方法。

(0)

相关推荐

  • 你会从身份证号码算生日吗?

    点击上方蓝字关注 Excel应用大全 置顶公众号或设为星标,避免收不到文章 每天分享Excel应用技巧,让你不仅用得上,还用的爽! 微信公众号:Excel应用大全(ExcelApp520) 个人微信号 ...

  • 1个公式搞定根据身份证号码判断性别

    个人微信号 | (ID:LiRuiExcel520) 微信服务号 | 跟李锐学Excel(ID:LiRuiExcel) 微信公众号 | Excel函数与公式(ID:ExcelLiRui) vlooku ...

  • POWER QUERY--从列数据中提取字符

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 从列数据中提取字符,使用的是"提取"命令.该命令出现在 ...

  • 1个公式搞定根据身份证号码查询所属星座

    跟李锐学Excel, 高效工作,快乐生活. 1个公式搞定根据身份证号码查询所属星座 身份证号码里包含很多信息,比如年龄.性别.生日等. 今天我来结合一个实际案例,介绍1个公式从身份证号码查询所属星座. ...

  • 表格中身份证号码提取出年龄

    如何从表格中的身份证号码提取出年龄呢?下面是具体的操作方法. 打开一个WPS excel"年龄计算表". 输入姓名和身份证号码. 在年龄列下单元格输入公式"=DATEDI ...

  • 从身份证号码提取生日

    版权所有 转载须经Excel技巧网/Office学吧允许 [ Excel ]:快速隐藏并保护公式

  • Excel技巧连载35和36:身份证号码提取生肖的两种公式写法

    一个身份证号码包含了我们的出生日期.性别.籍贯等信息. 昨天有小伙伴在微信群私聊雅雯,询问同样的一个公式,为何有些单元格提取生肖是错误的,可以先从数据源开始检查是否是数据源格式.录入等等有错误. 通过 ...

  • 1个公式从身份证号码提取生日

    跟李锐学Excel, 高效工作,快乐生活. 1个公式从身份证号码提取生日 身份证号码里包含很多信息,比如年龄.性别.生日等. 今天我来结合一个实际案例,介绍1个公式从身份证号码提取出生日期. 方法1: ...

  • 1个公式搞定根据身份证号码提取所属省市地址

    跟李锐学Excel, 高效工作,快乐生活. 1个公式搞定根据身份证号码提取所属省市地址 身份证号码里包含很多信息,比如年龄.性别.所属省市地址等. 今天我来结合一个实际案例,介绍1个公式搞定根据身份证 ...

  • Excel教程:身份证号码提取性别,你还不会吗?

    Excel身份证提取性别主要使用到IF函数.MOD函数.MID函数来判断性别,从而实现快速从身份证号码来筛选男女. 第一步:了解身份证号码规则 学习excel身份证提取性别技巧之前,我们需要先了解身份 ...

  • 一张图识别身份证号码归属地级市

    一张图识别身份证号码归属地级市

  • 通过Excel可以提取身份证号码哪些信息?看完才知道有多重要

    前面说了关于身份证号码录入的各种问题,今天说说可以从身份证里面提取哪些信息,包括出生地所在省市区.生日.年龄.性别.生肖.星座等等.看看身份证号码包含了多少信息,所以平时要注意保护好我们的身份证哦. ...

  • 从身份证号码中提取数据

    利用函数,从身份证18号码中提取出生日期: Mid函数是一个字符串函数,作用是从一个字符串中截取出指定数量的字符.Mid函数的语法格式是:MID(文本,开始位置,字符数).我个人比较喜欢这个函数,因为 ...