和身份证有关的5个Excel函数公式

和身份证有关的5个Excel函数公式

以下是一组和身份证有关的函数公式,如果你是财务人员或是一名HR,那就一起来看一下啦:

1、查找重复身份证号码:
=IF(COUNTIF(A:A,A2&"*")>1,"重复","")

要点:
在COUNTIF函数的第二参数后加上一个星号 &"*"。
COUNTIF函数在计算文本型数字时,会默认按数值型进行处理,但是Excel中的数字精度只有15位,并且身份证号码是18位,因此会把前15位相同的身份证全部识别为相同的内容。在第二参数后加上一个星号 &"*",就是告诉Excel,要查找包含A2单元格内容的文本,通过这样变通的手段,COUTNIF函数就听话啦。

2、用出生年月来计算年龄:
=DATEDIF(A4,TODAY(),"y")

要点:
DATEDIF函数用于计算两个日期之间的间隔。用法是:
=DATEDIF(开始日期,结束日期,指定要返回的类型)
第三参数是Y,表示年,是M,就表示月。
TODAY函数返回系统当前的日期。
DATEDIF函数是隐藏函数,输入时没有屏幕提示,需要手工录入哦。

3、根据身份证号计算出生年月:
=--TEXT(MID(A2,7,8),"0!/00!/00")

要点:
身份证号码中的第8位到15位是出生年月信息。
首先使用MID函数从A2单元格的第7位开始,提取出8个字符,得到类似 19840727的结果。
再使用TEXT函数将这个字符串变成日期样式的文本,得到类似1984/07/27的结果。
由于TEXT函数的结果是文本型的内容,所以加上两个小减号 -- 就是计算负数的负数,通过四则运算,变成真正的日期序列。
公式也可以使用=--TEXT(MID(A2,7,8),"0-00-00"),原理是一样的。

4、根据身份证号码提取性别:
=IF(MOD(MID(A2,15,3),2),"男","女")

要点:
身份证号码中的第17位是性别信息,单数表示男,双数表示女。
首先使用MID函数从A2单元格的第15位开始,提取出3个字符。
再使用MOD函数计算这个字符与2相除的余数。
最后使用IF函数判断,如果余数大于0,返回男,如果余数是0,则返回女。
这里使用MID(A2,15,3),是为了兼容15位的旧身份证,这部分公式也可以使用MID(A2,17,1)。

5、根据身份证号计算退休时间:
=EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)

要点:
先用TEXT(MID(A2,7,8),"0!/00!/00"),计算出出生年月信息。
再使用MOD(MID(A2,15,3),2)计算性别码和2相除的余数,结果是1或是0。
如果性别码和2相除的余数是1,MOD(MID(A2,15,3),2)*120+600部分就相当于1*120+600,结果是720。
否则就是0*120+600,结果是600。
EDATE函数返回一个日期经过指定月份后的日期,也就是以出生年月为准,男性要经过720个月(60年),女性要经过600个月(50年)就是退休年龄了

原文地址:https://www.cnblogs.com/medik/p/11026410.html

时间: 06-15

和身份证有关的5个Excel函数公式的相关文章

工作中最常用的Excel函数公式大全

电脑那些事儿2016-05-18 22:23:02微软 公式 工作阅读(22574)评论(1) 声明:本文由入驻搜狐公众平台的作者撰写,除搜狐官方账号外,观点仅代表作者本人,不代表搜狐立场.举报 Word.Excel.PPT是我们日常生活中,最长用到的三款工具.每一种工具在使用过程中都有一些知识与技巧,今天"电脑那些事儿"小编给大家带来的是Excel中函数公式汇总,尤其是文员或者学习会计的小伙伴赶快收藏,一起来涨知识吧! 一.数字处理 1.取绝对值 =ABS(数字) 2.取整 =INT

Excel函数公式 (日期函数)

1.=TODAY() 取当前的系统日期 2.=NOW() 取当前系统日期和时间 3.= NOW()-TODAY() 计算当前是几点几分.也可以用=MOD(NOW(),1)计算. 4.=YEAR(TODAY()) 取当前日期的年份 5.=MONTH(TODAY()) 取当前日期的月份 6.=DAY(TODAY()) 计算当前日期是几号. 7.=WEEKDAY(TODAY(),2) 计算今天是星期几. 第二参数是2,表示将星期一计算为1,这样比较符合国人的习惯.也可以写成=TEXT(TODAY(),

利用Excel函数多角度计算个人所得税

新个税征收方法已与2011年9月1日起施行,但计算方法变更改后,有关这方面的计算方法在网络论坛上讨论较多,但在新旧交替时期正误混杂,新人难以分辨.在现实中根本就找不到这方面的经典计算方法,ExcelHome论坛超级版主.微软最有价值专家MVP黄成武归纳总结一下计算方法,最终目的是给大家拓展函数使用的技巧和思路,在陶醉于函数奇妙.经典的运用中,感叹Excel博大精深,同时又是加强练习Excel函数的绝佳机会. 一.个税计算最新税率表 级数 全月应纳税所得额 税率 扣除数 1       不超过15

【228】Excel 函数说明

官方帮助:Excel 函数(按字母顺序列出) 官方帮助:Excel 函数(按类别列出) 参考:EXCEL常用函数大全 IF 函数:此函数用于在条件为真时返回一个值,条件为假时返回另一个值. LEFT.LEFTB 函数:返回文本值中最左边的字符 LEN.LENB 函数:返回文本字符串中的字符个数 MID.MIDB 函数:从文本字符串中的指定位置起返回特定个数的字符 FIND.FINDB 函数:在一个文本值中查找另一个文本值(区分大小写)

Excel函数大全

我们在使用Excel制作表格整理数据的时候,经常要用到它的函数功能来自己主动统计处理表格中的数据.这里整理了Excel中使用频率最高的函数的功能.用法,以及这些函数在实际应用中的实例剖析,并配有具体的介绍. 1.ABS函数 函数名称:ABS  主要功能:求出对应数字的绝对值.  使用格式:ABS(number) 參数说明:number代表须要求绝对值的数值或引用的单元格.  应用举例:假设在B2单元格中输入公式:=ABS(A2),则在A2单元格中不管输入正数(如100)还是负数(如-100),B

Excel函数应用教程 Excel VBA基础教程 WPS2013表格教程

热门推荐电脑办公计算机基础知识教程 Excel2010基础教程 Word2010基础教程 PPT2010基础教程 五笔打字视频教程 Excel函数应用教程 Excel VBA基础教程 WPS2013表格教程 更多>平面设计PhotoshopCS5教程 CorelDRAW X5视频教程 Photoshop商业修图教程 Illustrator CS6视频教程 更多>室内设计3Dsmax2012教程 效果图实例提高教程 室内设计实战教程 欧式效果图制作实例教程 AutoCAD2014室内设计 Aut

Excel函数学习随笔

2017/7/9 学习心得: vlookup函数: VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊查找) 查找目标要在区域的第一列:查找范围要用绝对引用($A$3:$F$19):返回值列数以引用范围第一列为始:一般均使用精确查找(false/0) 查找目标的模糊搜索方法:"*" & 字符 & "*"(即搜索包含该字符内容的所有目标): 数字区间查找(模糊查找的应用):使用前提:引用数字区域必须是从小到大排列.使用效果:给一定数,函数会

Excel 函数

Execel  Concatenate("'",A2,",'")   S1234567        ->              "S1234567D",

Excel表格公式大全[转]

Excel技巧网_官方微博 作者: Excel技巧网_官方微博 2016-09-23 14:05:20 举报 阅读数:21219 ​1.查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复",""). 2.用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0). 3.从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),&qu

如何使用函数公式来查找图片

先看效果: 是不是有点意思啊.好,咱们看看如何实现这样的效果. 步骤一: 准备好图片资料,话说巧媳妇难做无米之炊,没有资料,如何查询?如下图所示:A列是产品名称,B列是对应的图片.注意图片的大小要在单元格的边框以内,不要过界. 步骤二:建立一个图片查询工作表,在A2单元格设置数据有效性,在数据有效性的允许序列中添加图片资料库的产品名称单元格区域: 按Ctrl+F3调出名称管理器,点击[新建]按钮,新建一个名称"图片",在引用位置编辑框内输入以下公式: =OFFSET(图片资料库!$B$