excel表格_excel函数公式大全_execl从入门到精通
使用excel，通过身份证号码找出归属地、属相等信息，公式大公开2024-02-23 10:36:30
1、归属地归属地查询需要提前准备好地区编码对照表，不过网上下载的地区编码表似乎都不全，我做好的示例文档中的对照表基本是全的。
建议新建一个工作表保存地区对照表。编码放在A列，使用文本格式；地区放在B列。身份证信息查询表使用结构化表格，俗称超级表或动态表。这种表格以后添加数据时会自动更新表中的公式及单元格格式。
序号使用这个公式：
=IF([@身份证号码]="","",ROW()-2)
使用这个公式，添加或减少数据，可以自动更新序号。
归属地的查询公式如下：
=IF([@身份证号码]="","",VLOOKUP(--MID([@身份证号码],1,6),地区编码对照表!A:B,2,0))
2、出生日期
公式如下：
=IF([@身份证号码]="","",TEXT(MID([@身份证号码],7,8),"0年00月00日"))
3、年龄
公式如下：
=IF([@身份证号码]="","",DATEDIF(TEXT(MID([@身份证号码],7,8),"0000-00-00"),TODAY(),"Y"))
4、性别
公式如下：
=IF([@身份证号码]="","",TEXT(-1^MID([@身份证号码],9,9),"女;男"))
5、生日
公式如下：
=IF([@身份证号码]="","",TEXT(MID([@身份证号码],11,4),"0月00日"))
6、属相
公式如下：
=IF([@身份证号码]="","",MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID([@身份证号码],7,4)-4,12)+1,1))
7、星座
公式如下：
=IF([@身份证号码]="","",LOOKUP(--TEXT([@出生日期],"mdd"),{101,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";621,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"}))
8、退休日期（按男60岁，女55岁计算）
如果需要按其他年龄计算，仅修改一下公式中的数字60或55即可。
公式如下：
=IF([@身份证号码]="","",EDATE([@出生日期],IF([@性别]="男",60,55)*12))
9、身份证号码真伪验证
公式如下：
=IF([@身份证号码]="","",IF(RIGHT([@身份证号码])=MID("10X98765432",MOD(SUM(MID([@身份证号码],ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假"))
附：示例文档下载地址（收费）
https://mianbaoduo.com/o/chensir18/work
标签: excel表格中如何筛选出地区