很多小伙伴因为不会Excel公式，百度搜索也是乱七八糟，看都看不懂的，导致很多内容都是人工手敲，费时费力，那么本帖收集整理了很多Excel公式组合，实现各种统计与提取功能，将公式中的 [@身份证号] 整体改为身份证号相关单元格即可，其他标记词类似，有需要的公式需求可评论区留言。
1、身份证系列操作
- 身份证号真伪：=IF(LEN([@身份证号])=0,”空”,IF(LEN([@身份证号])=15,”老号”,IF(LEN([@身份证号])<>18,”位数不对”,IF(CHOOSE(MOD(SUM(MID([@身份证号],1,1)*7+MID([@身份证号],2,1)*9+MID([@身份证号],3,1)*10+MID([@身份证号],4,1)*5+MID([@身份证号],5,1)*8+MID([@身份证号],6,1)*4+MID([@身份证号],7,1)*2+MID([@身份证号],8,1)*1+MID([@身份证号],9,1)*6+MID([@身份证号],10,1)*3+MID([@身份证号],11,1)*7+MID([@身份证号],12,1)*9+MID([@身份证号],13,1)*10+MID([@身份证号],14,1)*5+MID([@身份证号],15,1)*8+MID([@身份证号],16,1)*4+MID([@身份证号],17,1)*2),11)+1,1,0,”X”,9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT([@身份证号],1)*1),RIGHT([@身份证号],1)*1,”X”),”正确”,”错误”))))
- 取出出生日期：=TEXT(MID([@身份证号],7,8),”0-00-00″)
- 取出生日：=TEXT(MID([@身份证号],11,4),”00-00″)
- 取出性别：=IF(MOD(MID([@身份证号],17,1),2)=1,”男”,”女”)
- 取出年龄1：=DATEDIF([@出生年月日],TODAY(),”Y”)
- 取出年龄2：=DATEDIF(TEXT(MID([@身份证号],7,8),”0-00-00″),TODAY(),”Y”)
- 取出生肖：=MID(“鼠牛虎兔龙蛇马羊猴鸡狗猪”,MOD(YEAR([@出生年月日])-4,12)+1,1)
- 取出星座：=LOOKUP(–TEXT([@出生年月日],”mdd”),{101,”摩羯座”;120,”水瓶座”;219,”双鱼座”;321,”白羊座”;420,”金牛座”;521,”双子座”;621,”巨蟹座”;723,”狮子座”;823,”处女座”;923,”天秤座”;1023,”天蝎座”;1122,”射手座”;1222,”摩羯座”})
- 取出退休日期（男60女55）：=EDATE(TEXT(MID([@身份证号],7,8),”0000-00-00″),IF(IF(MOD(MID([@身份证号],17,1),2),”男”,”女”)=”男”,60,55)*12)
-
取出户口所在地：=VLOOKUP(–LEFT([@身份证号],6),籍贯编码!A:B,2,0)
籍贯编码为另一个工作表名，可参考下列示例文件
2、日期时间类
- 获取当前详细时间：=TEXT(NOW(),”yyyy-m-d h:mm:ss”)&” “&TEXT(WEEKDAY(NOW(),1),”AAAA”)
- 获取当前详细时间(去除时分秒)：=TEXT(NOW(),”yyyy-m-d”)&” “&TEXT(WEEKDAY(NOW(),1),”AAAA”)
- 通过星期几获取日期：参考详细介绍专贴
- 计算本月月底日期：=TEXT(EOMONTH(TODAY(),0),”yyyy-m-d”)
- 计算本月一共多少天：=DAY(EOMONTH(TODAY(),0))
- 计算本月还剩多少天：=DAY(EOMONTH(TODAY(),0)-TODAY())
- 判断今年是闰年还是平年1：=IF(MOD(IF(MOD(YEAR(TODAY()),100)=0,YEAR(TODAY())/100,YEAR(TODAY())),4)=0,”闰年”,”平年”)
- 判断今年是闰年还是平年2：=IF(COUNT(-“2-29″),”闰年”,”平年”)
- 今年是已经经过多少天：=TEXT(TODAY(),”m-d”)-“1-1″+1
- 今年是还剩多少天：=”12-31″-TEXT(TODAY(),”m-d”)+1
- 计算某日期距今过了多少天：=DATEDIF(TEXT([@目标日期],”yyyy-m-d”),TODAY(),”d”)
- 上个公式推展：TODAY()改为其他日期可以实现任意两个日期相隔的天数，”d”计算天，”m”计算月，”y”计算年
- 计算下一个工作日：=TEXT(WORKDAY(TODAY(),1),”m-d”)
3、手机号查询类
- 查询手机号运营商：=IF(LEFT([@手机号],3)=”134″,”移动”,IF(LEFT([@手机号],3)=”135″,”移动”,IF(LEFT([@手机号],3)=”136″,”移动”,IF(LEFT([@手机号],3)=”137″,”移动”,IF(LEFT([@手机号],3)=”138″,”移动”,IF(LEFT([@手机号],3)=”139″,”移动”,IF(LEFT([@手机号],3)=”147″,”移动”,IF(LEFT([@手机号],3)=”150″,”移动”,IF(LEFT([@手机号],3)=”151″,”移动”,IF(LEFT([@手机号],3)=”152″,”移动”,IF(LEFT([@手机号],3)=”157″,”移动”,IF(LEFT([@手机号],3)=”158″,”移动”,IF(LEFT([@手机号],3)=”159″,”移动”,IF(LEFT([@手机号],3)=”182″,”移动”,IF(LEFT([@手机号],3)=”183″,”移动”,IF(LEFT([@手机号],3)=”187″,”移动”,IF(LEFT([@手机号],3)=”188″,”移动”,IF(LEFT([@手机号],3)=”130″,”联通”,IF(LEFT([@手机号],3)=”131″,”联通”,IF(LEFT([@手机号],3)=”132″,”联通”,IF(LEFT([@手机号],3)=”155″,”联通”,IF(LEFT([@手机号],3)=”156″,”联通”,IF(LEFT([@手机号],3)=”185″,”联通”,IF(LEFT([@手机号],3)=”186″,”联通”,IF(LEFT([@手机号],3)=”145″,”联通”,IF(LEFT([@手机号],3)=”133″,”电信”,IF(LEFT([@手机号],3)=”153″,”电信”,IF(LEFT([@手机号],3)=”180″,”电信”,IF(LEFT([@手机号],3)=”189″,”电信”,IF(LEFT([@手机号],3)=”181″,”电信”,IF(LEFT([@手机号],3)=”177″,”电信”,””)))))))))))))))))))))))))))))))
- 取出手机号后四位：=RIGHT([@手机号],4)
- 判断是否为手机号：=IF(LEN([@手机号])=11,”正确”,”错误”)
- 隐藏手机号中间4位：=REPLACE([@手机号],4,4,”****”)
付费阅读
© 版权声明
THE END
请登录后查看评论内容