根据身份证号计算年龄(可实现对15位和18位身份证号判断)
=IF(TRIM(B2)="","",IF(LEN(TRIM(B2))=18,IF(TODAY()>=DATEVALUE(TEXT(TODAY(),"yyyy")&"-"&MID(TRIM(B2), 11, 2)&"-"&MID(TRIM(B2), 13, 2)),YEAR(TODAY())-MID(TRIM(B2), 7, 4),YEAR(TODAY())-MID(TRIM(B2), 7, 4)-1),
IF(LEN(TRIM(B2))=15,
IF(TODAY()>=DATEVALUE(TEXT(TODAY(),"yyyy")&"-"&MID(TRIM(B2), 9, 2)&"-"&MID(TRIM(B2), 11, 2)),
YEAR(TODAY())-("19"&MID(TRIM(B2),7,2)),
YEAR(TODAY())-("19"&MID(TRIM(B2), 7, 2))-1),
"无效身份证号")))
根据身份证号计算性别(可实现对15位和18位身份证号判断)
=IF(TRIM(B2)="", "", IF(LEN(TRIM(B2))=18, IF(MOD(MID(TRIM(B2),17,1),2)=1,"男","女"),
IF(LEN(TRIM(B2))=15, IF(MOD(MID(TRIM(B2),15,1),2)=1,"男","女"), "无效身份证号")))