lena和shane,家庭日志,xulavigne,Lena&Shane

如何实现

巧用函数检查,事半功倍,轻松愉快

2015-03-22 ID:cfoclass财务第一教室 彭怀文 Lena Jhang

春节一过,很多企业就进入了招聘高峰。个人所得税全员申报申报系统要求必须录入每一个人的身份证号码、出生日期、性别等,当企业招聘进来几十上百的人员时是一件很麻烦的事情。虽然系统支持从EXCEL表格中直接导入,可惜很多人事MM天生对数据不敏感,经常把人员的身份证号码、出生日期甚至性别整错,导致导入出现大量错误,费时费工。

如何检查并防范这些错误,请看一个财务人员的小技巧。

其实,我国的居民身份证号码中包含有每个人的出生日期和性别等信息。但是,面对大批量的居民身份证号码如何快速、准确地生成并判断呢?现举例说明如下:


 

一、检查身份证号码的位数是否正确

现在我国的居民身份证号码都是18位数,但是人事MM经常会出现要么位数不够、要么位数多了。

其实,我们可以利用人事MM提供的EXCEL表格用一些函数就能很快检查(详见上图,下同)。

“位数判断”是D列,先在D2输入如下的公式:

=IF(LEN(C2)=18,"OK","NO!!!")

然后选中D2向下批量复制。

解释1:

函数LEN就是判断指定字符串的字符数的;IF是条件函数,等于18位数就OK,否则就是错的。

二、检查身份证号码上的性别与人事部提供的性别是否一致

我国的居民身份证18位数号码中的第17位数就是性别标识:奇数为男性,偶数为女性。

因此,只要在E2输入公式:

=IF((MID(C2,17,1)/2-INT(MID(C2,17,1)/2))=0,"女","男")

这样就可以得到身份证号码中性别的信息了。

然后选中E2向下批量复制。

解释2:

函数MID就是从指定字符串的左边开始取数,因为我们要取第17位数,故公式中有17;但是我们只取1位数,所以还有1。

函数INT是取整数函数,所有带小数的数也只取整数。偶数除以2取整数与偶数除以2相等,自然判定是“女”,否则就是“男”。

上面的方法是把身份证号码中的性别信息提取出来了,可是还没有判定是否与人事部提供的性别一致。其实,只要在G2输入公式:

=IF(IF(E2="男",1,0)-IF(F2="男",1,0)=0,"OK","NO!!!")

然后选中G2向下批量复制。这样就可以很清楚看出是否一致了。

三、检查身份证号码上的出生日期与人事部提供的出生日期是否一致

我国的居民身份证号码从第7位数开始就是出生日期,因此我们可以继续使用函数MID。在H2中输入公式:

=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"

然后选中H2向下批量复制。

接着在J2中输入公式:

=IF(H2-I2=0,"OK","NO!!!")

然后选中J2向下批量复制。

说明:上述所有公式中“OK”代表正确,“NO!!!”代表错误。

最后声明一点:本人不是电脑达人,也不是EXCEL高手,经常是需要使用函数的时候才临时去查询或翻书;只是我认为一个合格的财务人员必须掌握一点EXCEL函数,需要知道怎么能事半功倍而轻松愉快的工作。我的上述方法,我从来都不是自己悄悄使用,我会向所有与该工作相关的人员传授,尤其是人事部的MM们,她们从开始就检查了错误,我们财务人员岂不是更加快捷了呢。


如果觉得文章还不错,请点个赞吧


8月 30 2020
太过信任,你或许会受骗,然而终日怀疑,你会饱受痛苦。

弗兰克•克莱恩 (Frank Crane 美国演员,导演)