1 从身份证号中提取出生日期：
=IF(LEN(e3)=15,TEXT("19"&MID(e3,7,2)&"-"&MID(e3,9,2)&"-"&MID(e3,11,2),"yyyy-mm-dd"),IF(L EN(e3)=18,TEXT(MID(e3,7,4)&"-"&MID(e3,11,2)&"-"&MID(e3,13,2),"yyyy-mm-dd"),"无效"))
2 通过生日计算年龄
=(today()-d2)/365
3 查询重复数据
=IF(SUMPRODUCT(N(F$3:F$46061=F2))>1,"重复","不重复")
=IF(SUMPRODUCT(N(E$2:E$5000=E2))>1,"重复","不重复")
4 通过身份证号确定性别
=IF(E2="","",IF(OR(LEN(E2)={15,18}),(IF(LEN(E2)=15,IF(MOD(RIGHT(E2,1),2)=1,"男","女"),IF(MOD(MID(E2,17,1),2),"男","女"))),"身份证号码长度不对，只有"&LEN(E2)&"位！"))
5 校正日期
=DATEVALUE(H3)
6 计数
=COUNTIF(H$2:H$2300,H2)
7 顺序增加
=J2+IF(I3="1","1","0")
8 日期
=TEXT(MID(L2,1,4)&"-"&MID(L2,5,2)&"-"&MID(L2,7,2),"yyyy-mm-dd")
9=IF(F3=1,D3,G2)。