EXCEL函数公式：如何根据公民身份号码中的出生年份生成农历属相

假设身份证号码在A1，可以在B1输入公式
=LOOKUP(MOD(IF(LEN(A1)=18,MID(A1,7,4),"19"&MID(A1,7,2)),12),{0,1,2,3,4,5,6,7,8,9,10,11},{"猴","鸡","狗","猪","鼠","牛","虎","兔","龙","蛇","马","羊"})

如果已知B列的出生年月日
可以在C列的C1单元格输入公式
=LOOKUP(MOD(C1,12),{0,1,2,3,4,5,6,7,8,9,10,11},{"猴","鸡","狗","猪","鼠","牛","虎","兔","龙","蛇","马","羊"})

B1求生日公式
=TEXT(MID(A1,7,8),"0000-00-00")

C1求属相公式
=MID("猴鸡狗猪鼠牛虎免龙蛇马羊",MOD(YEAR(B1),12)+1,1)
注：因使用阳历生日，而属相使用阴历，所以在年头时可能不对

精髓，解题思路：1、如果出生月份数<今天月份数,且日期数<今天日期数；显示几个假设，假设一，A1是身份证号，且是18位假设二你要按阳历生日计算那

在B1输入=(RIGHT(LEFT(A1,10),4)&-RIGHT(LEFT(A1,12),2)&-RIGHT(LEFT(A1,14),2))
在C1输入=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B1)-4,12)+1,1)

B列输入 =--TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")

C列输入
=CHOOSE(MOD(TEXT(B1,"[$-130000]e"),12)+1,"猴","鸡","狗","猪","鼠","牛","虎","兔","龙","蛇","马","羊")

说明: 公式仅适合excel2007以上版本(算农历), 身份证可为15位或18位