夜雨聆风
【WPS表格函数】身份证号码深度应用指南
身份证号码具有唯一性，在办公自动化、数据库管理和日常业务中经常使用，具有核心作用。
一、日常工作中的作用
1.身份识别与核对。作为员工表、客户表、会员表的唯一标识，避免同名混淆。
2.人事或会员管理。自动提取出生日期、性别、年龄，计算退休时间、生日提醒等。
3.财务与税务。个税申报、工资发放、银行开户等场景需关联身份证号，确保资金与个人一一对应。
4.统计与报表。通过地址码提取户籍地，分析人员地域分布；通过出生日期分析年龄结构、出生率等。
5.业务办理。入职、工会会员管理等流程必填项，用于身份验证和档案建立等。
二、在数据库管理中的作用
1.唯一主键。设为主键或唯一索引，防止重复录入，确保记录唯一性。
2.数据关联。关联人事、工资、考勤等多表，实现跨表查询与汇总。
3.数据清洗与去重。利用唯一性快速识别重复或错误数据，提升数据质量。
4.安全与权限控制。作为敏感数据访问的身份凭证，用于权限验证和日志记录。
三、注意事项
1.存储格式。身份证所在单元格必须设为文本格式，或在号码前加英文单引号‘，防止科学计数法导致精度丢失。
2.隐私保护。共享或展示时应脱敏处理（如只显示前6位和后4位）。
3.验证准确性。录入时校验长度、格式、校验码，确保数据有效。
4.兼容性：有15位旧版身份证号码时，应先进行数据清洗转为二代身份号码。
四、身份证号码编码规则
五、典型场景
假定：李乙的二代身份证号码11010119850315123X在C9单元格。有的公式很复杂，复制使用公式时，注意据实调整引用单元格位置。
场景1.提取性别
=IF(MOD(–MID(C9,17,1),2)=1,”男”,”女”)
结果：男
场景2.提取生肖
=CHOOSE(MOD(MID(C9,7,4)-1900,12)+1,”鼠”,”牛”,”虎”,”兔”,”龙”,”蛇”,”马”,”羊”,”猴”,”鸡”,”狗”,”猪”)
结果：牛
场景3.提取星座
=LOOKUP(–TEXT(DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2)),”mdd”),{120,219,321,420,521,622,723,823,923,1024,1123,1222},{“水瓶座”,”双鱼座”,”白羊座”,”金牛座”,”双子座”,”巨蟹座”,”狮子座”,”处女座”,”天秤座”,”天蝎座”,”射手座”,”摩羯座”})
结果：双鱼座
场景4.提取出生日期（yyyy/mm/dd）
=DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2))
结果：1985/3/15
场景5.提取出生日期（yyyy-m-d）
=MID(C9,7,4)&”-“&VALUE(MID(C9,11,2))&”-“&VALUE(MID(C9,13,2))
结果：1985-3-15
场景6.提取出生日期（yyyy-mm-dd）
=MID(C9,7,4)&”-“&MID(C9,11,2)&”-“&MID(C9,13,2)
结果：1985-03-15
场景7、提取出生日期（yyyy年mm月dd日，TEXT格式）
=TEXT(MID(C9,7,8),”0000年00月00日”)
结果：1985年03月15日
场景8、提取出生日期（yyyy年mm月dd日，拼接格式）
=MID(C9,7,4)&”年”&MID(C9,11,2)&”月”&MID(C9,13,2)&”日”
结果：1985年03月15日
场景9、出生日期有效性验证
=IF(AND(LEN(C9)=18,–MID(C9,11,2)>=1,–MID(C9,11,2)<=12,–MID(C9,13,2)>=1,–MID(C9,13,2)<=DAY(DATE(–MID(C9,7,4),–MID(C9,11,2)+1,0))),”出生日期有效”,”出生日期无效”)
结果：出生日期有效
场景10、提取出生年（数字）
=MID(C9,7,4)
结果：1985
场景11、出生年（带单位）
=MID(C9,7,4)&”年”
结果：1985年
场景12、提取出生月（两位数字）
=MID(C9,11,2)
结果：03
场景13、提取出生月（带单位）
=MID(C9,11,2)&”月”
结果：03月
场景14、提取出生日（两位数字）
=MID(C9,13,2)
结果：15
场景15、提取出生日（带单位）
=MID(C9,13,2)&”日”
结果：15日
场景16、提取出生年代
=LEFT(MID(C9,7,4),3)&”0年代”
结果：1980年代
场景17、提取出生年月
=MID(C9,7,4)&”年”&MID(C9,11,2)&”月”
结果：1985年03月
场景18、提取出生季节
=LOOKUP(VALUE(MID(C9,11,2)),{1,3,6,9,12},{“冬季”,”春季”,”夏季”,”秋季”,”冬季”})
结果：春季
场景19、提取年龄（虚岁）
=YEAR(TODAY())-MID(C9,7,4)+1
结果：42
场景20、提取年龄（周岁）
=DATEDIF(DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2)),TODAY(),”Y”)
结果：40
场景21、提取生日季度
=”第”&ROUNDUP(VALUE(MID(C9,11,2))/3,0)&”季度”
结果：第1季度
场景22、提取生日星期几
=TEXT(DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2)),”aaaa”)
结果：星期五
场景23、是否已过生日（当年）
=IF(TODAY()>=DATE(YEAR(TODAY()),VALUE(MID(C9,11,2)),VALUE(MID(C9,13,2))),”是”,”否”)
结果：否
场景24、距离下次生日天数
=IF(TODAY()<=DATE(YEAR(TODAY()),VALUE(MID(C9,11,2)),VALUE(MID(C9,13,2))),DATE(YEAR(TODAY()),VALUE(MID(C9,11,2)),VALUE(MID(C9,13,2)))-TODAY(),DATE(YEAR(TODAY())+1,VALUE(MID(C9,11,2)),VALUE(MID(C9,13,2)))-TODAY())&”天”
结果：14天
场景25、提取省份名称
=VLOOKUP(VALUE(LEFT(C9,2)),身份证号码行政区划表!$B$4:$C$37,2,FALSE)
结果：北京市
场景26、是否成年（周岁≥18岁）
=IF(DATEDIF(DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2)),TODAY(),”Y”)>=18,”是”,”否”)
结果：是
场景27、是否退休（男60，女55）
=IF(OR(AND(MOD(MID(C9,17,1),2)=1,DATEDIF(DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2)),TODAY(),”Y”)>=60),AND(MOD(MID(C9,17,1),2)=0,DATEDIF(DATE(MID(C9,7,4),MID(C9,11,2),MID(C9,13,2)),TODAY(),”Y”)>=55)),”是”,”否”)
结果：否
场景28、提取顺序码
=MID(C9,15,3)
结果：123
场景29、提取校验码
=RIGHT(C9,1)
结果：X
场景30、身份证格式验证（18位）
=IF(AND(LEN(C9)=18,ISNUMBER(–LEFT(C9,17)),OR(ISNUMBER(–RIGHT(C9,1)),RIGHT(C9,1)=”X”),–MID(C9,11,2)>=1,–MID(C9,11,2)<=12,–MID(C9,13,2)>=1,–MID(C9,13,2)<=DAY(DATE(–MID(C9,7,4),–MID(C9,11,2)+1,0))),”有效”,”无效”)
结果：有效
场景31、身份证号脱敏（改变位数）
=LEFT(C9,6)&”****”&RIGHT(C9,4)
结果：110101****123X
场景32、身份证号脱敏（不改变位数）
=REPLACE(C9,7,8,”********”)
结果：110101********123X
关注长伴不迷航，更新迭出见真意。
