必知的技术知识:Excel通过身份证获取出生年月,性别,年龄,生肖,星座,省份等信息总结归纳

简介: 必知的技术知识:Excel通过身份证获取出生年月,性别,年龄,生肖,星座,省份等信息总结归纳

Excel通过身份证获取出生年月,性别,年龄,生肖,星座,省份等信息总结归纳


早期的身份证号码为15位数字,现在使用的身份证号码为18位数字,它//代码效果参考:http://www.lyjsj.net.cn/wz/art_24161.html

们的含义如下:

(1)15位:1-6位为地区代码,7-8位为出生年份(2位),9-10位为出生月份,11-12位为出生日期,第13-15位为顺序号,这3位奇数为男,偶数为女。


(2)18位:1-6位为地区代码,7-10位为出生年份(4位),11-12位为出生月份,13-14位为出生日期,第15-17位为顺序号,同上,第18位为效验位。


在工作时,有时希望通过身份证号码,自动提取出出生日期、性别、年龄等信息。


现在我们就通过几个公式,来得到这些信息。


1、出生日期


=IF(LEN(A2)=15,CONCATENATE("19",MID(A2,7,2),"/",MID(A2,9,2),"/",MID(A2,11,2)),CONCATENATE(MID(A2,7,4),"/",MID(A2,11,2),"/",MID(A2,13,2)))


LEN函数是检测这个身份证号码的数字位数,然后通过IF判断函数对15位或18位的情况做出不同处理。


CONCATENATE函数将提取出的分别代表“年”、“月”、“日”的信息,以及分隔符“/”等内容,合并成一个文本字符串。


MID函数的做用是从身份证的某个位置开始,取出若干字符。比如MID(A2,7,2),就是将身份证号码(放在A2单元格中),从左边数第七个字符开始,取出2个字符。


2、性别


=IF(LEN(A2)=15,IF(VALUE(RIGHT(A2,3))/2=INT(VALUE(RIGHT(A2,3))/2),"女","男"),IF(VALUE(MID(A2,15,3))/2=INT(VALUE(MID(A2,15,3))/2),"女","男"))


由于代表性别的数字中,偶数为“女”,奇数为“男”,所以在这个公式中用VALUE函数将取出的字符串变成数字,再除以2,看是否能整除,因为奇数是不能整除的。


这里的INT函数是“取整”的意思,把上面除2后的结果用它取整//代码效果参考:http://www.lyjsj.net.cn/wx/art_24159.html

,让IF函数判断一下,取整前后的结果是否相同,相同为偶数,不相同为奇数。

公式中的RIGHT函数是从字串的右边(即字符串最后面)取若干符,注意与MID函数的区别。


3、年龄:(根据得到的出生年月日计算得)


=CONCATENATE(DATEDIF(B2,TODAY(),"y"),"岁",DATEDIF(B2,TODAY(),"ym"),"个月")


=DATEDIF(B2,TODAY(),"y"),"岁"


由于我们用上面的公式,将“出生日期”提取到B2单元格,所以这里的公式是通过操作B2单元格的数据来得到年龄的。


函数DATEDIF是计算两个日期或时间的差值,通过第三个参数来确定所要的结果,比如“y”会得到两个日期相差的年;“ym”会得到除年外,所余的月数。


TODAY()函数的作用是得到当前系统日期,即电脑上今天的日期。


最终结果见下图:


4,省份


//代码效果参考:http://www.lyjsj.net.cn/wz/art_24157.html

前2位代表省份。然后用VLOOKUP函数引用,就可以自动得到各员工的居住城市信息了

身份证号与省份的关系


11 北京市;12 天津市;13 河北省;14 山西省;15 内蒙古自治区;21 辽宁省;22 吉林省;23 黑龙江省;31 上海市;32 江苏省;33 浙江省;34 安徽省;35 福建省;36 江西省;37 山东省;41 河南省;42 湖北省;43 湖南省;44 广东省;45 广西壮族自治区;46 海南省;50 重庆市;51 四川省;52 贵州省;53 云南省;54 西藏自治区;61 陕西省;62 甘肃省;63 青海省;64 宁夏回族自治区;65 新疆维吾尔自治区;71 台湾省;81 香港特别行政区;82 澳门特别行政区


5,生肖:(根据得到的出生年月日计算得)


=CHOOSE(MOD(YEAR(W2)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")


6,星座:(根据得到的出生年月日计算得)


=LOOKUP(--TEXT(C3,"mdd"),{101,"摩羯";120,"水瓶";219,"双鱼";321,"白羊";420,"金牛";521,"双子";621,"巨蟹";723,"狮子";823,"处女";923,"天秤";1023,"天蝎";1122,"射手";1222,"摩羯"})&"座"


转载于:


即使只是凡世中一颗小小的尘埃,命运也要由自己主宰,像向日葵般,迎向阳光、勇敢盛开

相关文章
|
4月前
|
数据库 C语言 索引
必知的技术知识:excel函数
必知的技术知识:excel函数
32 0
|
23天前
|
存储 数据挖掘 测试技术
Python接口自动化中操作Excel文件的技术方法
通过上述方法和库,Python接口自动化中的Excel操作变得既简单又高效,有助于提升自动化测试的整体质量和效率。
21 0
|
5月前
|
Python
pythonSpider_urllib获取豆瓣电影top250信息写入excel
pythonSpider_urllib获取豆瓣电影top250信息写入excel
51 0
|
2月前
|
C# 开发者 Windows
WPF遇上Office:一场关于Word与Excel自动化操作的技术盛宴,从环境搭建到代码实战,看WPF如何玩转文档处理的那些事儿
【8月更文挑战第31天】Windows Presentation Foundation (WPF) 是 .NET Framework 的重要组件,以其强大的图形界面和灵活的数据绑定功能著称。本文通过具体示例代码,介绍如何在 WPF 应用中实现 Word 和 Excel 文档的自动化操作,包括文档的读取、编辑和保存等。首先创建 WPF 项目并设计用户界面,然后在 `MainWindow.xaml.cs` 中编写逻辑代码,利用 `Microsoft.Office.Interop` 命名空间实现 Office 文档的自动化处理。文章还提供了注意事项,帮助开发者避免常见问题。
128 0
|
5月前
|
文字识别
分享:如何ocr识别身份证复印件并导出至excel表格 ? 图片批量识别导出excel表格应用,图片批量识别转excel表格的方法
该软件是一款OCR身份证识别工具,能批量处理图片,自动提取身份证信息并导出为Excel。支持百度网盘和腾讯云盘下载。用户界面直观,操作简单,适合新手。识别过程包括:打开图片、一键识别、导出结果。特别注意,此程序仅适用于身份证识别,不适用于其他类型的图片识别。
208 1
分享:如何ocr识别身份证复印件并导出至excel表格 ? 图片批量识别导出excel表格应用,图片批量识别转excel表格的方法
|
3月前
|
前端开发 JavaScript PHP
技术心得:前端点击按钮,导入excel文件,上传到后台,excel接收和更新数据
技术心得:前端点击按钮,导入excel文件,上传到后台,excel接收和更新数据
38 0
|
4月前
|
数据格式 Windows
必知的技术知识:Excel公式SWITCH函数你用过吗?多种查找函数介绍
必知的技术知识:Excel公式SWITCH函数你用过吗?多种查找函数介绍
64 0
|
4月前
|
easyexcel Java API
Apache POI与easyExcel:Excel文件导入导出的技术深度分析
Apache POI与easyExcel:Excel文件导入导出的技术深度分析
|
5月前
分享:2秒快速查询40万手机号码归属地,批量手机号码归属地查询可以导出excel表格,WPS表格查询手机号码归属地怎么操作,批量手机号码归属地批量查询软件,批量号码查询按省份和城市分类,按运移动号码电信号码联通号码分类整理
本文介绍了如何批量快速查询手机号码归属地并进行分类。首先,通过提供的百度网盘或腾讯云盘链接下载免费查询软件。其次,开启软件,启用复制粘贴功能,直接粘贴号码列表并选择高速查询。软件能在极短时间内(如1.76秒内)完成40多万个号码的查询,结果包括归属地、运营商、邮箱和区号,且数据准确。之后,可直接导出数据至表格,若数据超过100万,可按省份、城市及运营商分类导出。文章还附带了操作动画演示,展示全程流畅的处理大量手机号码归属地查询的过程。
254 0
分享:2秒快速查询40万手机号码归属地,批量手机号码归属地查询可以导出excel表格,WPS表格查询手机号码归属地怎么操作,批量手机号码归属地批量查询软件,批量号码查询按省份和城市分类,按运移动号码电信号码联通号码分类整理
|
5月前
|
存储 数据采集 数据可视化
Python列表到Excel表格第一列的转换技术详解
Python列表到Excel表格第一列的转换技术详解
81 0