《人事工作EXCEL常用函数》.docVIP

1.查看重复 =IF (COUNTIF(A$1:A1,A1)1,重复,不重复) 查看重复数据 同列 =IF(COUNTIF(A:A,B1)0,重复,*) 查看重复数据 不同列 假设A列，选中A列 --格式--条件格式-- 选择公式--等于 右边输入 =countif(A:A,A1)1 格式---图案--选择 红色 ， 重复的将显示红. 2.LEFT函数 函数名称：LEFT 主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。 使用格式：LEFT(text,num_chars) 参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。 应用举例：假定A38单元格中保存了“我喜欢天极网”的字符串，我们在C38单元格中输入公式：=LEFT(A38,3)，确认后即显示出“我喜欢”的字符。 特别提醒：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。 3.身份证中提取出生年月 =IF(A1=,,TEXT(IF(LEN(A1)=15,19 MID(A1,7,2)-MID(A1,9,2)-MID(A1,11,2),MID(A1,7,4)-MID(A1,11,2)-MID(A1,13,2)),yyyy-mm-dd)) 4、18位身份证号码校验函数公式： =IF(LEN(B2)=18,IF(MID(10MOD(SUM(MID(LEFT(B2,17),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(B2,1),通过,校验未通过),) 备注：输入函数后，按ctrl+shift+回车 5、15位身份证号码升18位函数公式： =IF(LEN(B2)=15,REPLACE(B2,7,,19)MID(10MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1),) 6、同时完成15位升位或18位校验的函数公式： =IF(LEN(B2)=18,IF(MID(10MOD(SUM(MID(LEFT(B2,17),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(B2,1),通过,校验未通过),IF(LEN(B2)=15,REPLACE(B2,7,,19)MID(10MOD(SUM(MID(REPLACE(B2,7,,19),ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1),)) 7、必备函数-VLOOKUP 用途：引用与筛选，根据已知条件查找数据源中符合条件的 示例：根据人员编号（必须是没有重复的）来查找数据源中对应的学历 已知条件如下： 数据源如下： （3）使用公式如下： （4）字段解释如下： LOOKUP_VALUE:已知条件所在的起始单元格 TABLE_ARRAY:数据源 Col_index_num:目标值所在的列数 Range_lookup:精确匹配（TRUE）还是大致匹配（FALSE），一般填写FALSE或0 （5）最后结果如下： 位数判断”是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向下批量复