excel表格星座计算公式输入生日计算出星座

{"1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座")。
比如下面公式
=IF(ISNA(VLOOKUP(B5,sheet1!$B$2:$E$179,2,FALSE)),"",VLOOKUP(B5,sheet1!$B$2:$E$179,2,FALSE))
X=VLOOKUP(B5,sheet1!$B$2:$E$179,2,FALSE) -->找到一样条件的，取它的数值。整个就是找的到取数值，找不到，用用空白代替NA，公式ISNA和ISERROR类似，不过ISERROR出错的时候，也会显示空白，用ISNA，出错就显示出错，NA才会显示空白。

A1引用样式
默认情况下，Excel 使用 A1 引用样式，此样式引用字母标识列（从 A 到 IV，共 256 列，备注：版本不同最大列数也不同），引用数字标识行（从 1 到 65,536）。这些字母和数字称为行号和列标。若要引用某个单元格，请输入列标和行号。例如，B2 引用列 B 和行 2 交叉处的单元格。

如果A1单元格写入18位身份证号
则B1单元格写入公式
=IF(A1="","",LOOKUP(--TEXT(MID(A1,11,4),"0-00"),--{"1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座"))

如果A1单元格写入的是EXCEL认可的日期格式,
则B1单元格写入公式
=IF(A1="","",LOOKUP(--TEXT(A1,"m/d"),--{"1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座"))

=INDEX({"山羊座","人马座","天蝎座","天秤座","处女座","狮子座","巨蟹座","双子座","金牛座","白羊座","双鱼座","水瓶座","山羊座"},MATCH(DATE(,MONTH(A1),DAY(A1)),{366,356,327,297,267,236,204,173,142,111,80,49,20},-1))

公式里这个部分： DATE(,MONTH(A1),DAY(A1)) 是把你输入的日期直接进行查找

=INDEX({"山羊座","人马座","天蝎座","天秤座","处女座","狮子座","巨蟹座","双子座","金牛座","白羊座","双鱼座","水瓶座","山羊座"},MATCH(DATE(,MID(A1,INDEX({9,11},MATCH(LEN(A1),{15,18},0)),2),MID(A1,INDEX({9,11},MATCH(LEN(A1),{15,18},0))+2,2)),{366,356,327,297,267,236,204,173,142,111,80,49,20},-1))

这个是兼容15位和18位的身位证的算法

=INDEX({"山羊座","人马座","天蝎座","天秤座","处女座","狮子座","巨蟹座","双子座","金牛座","白羊座","双鱼座","水瓶座","山羊座"},MATCH(IF(LEN(A1)>10,DATE(,MID(A1,INDEX({9,11},MATCH(LEN(A1),{15,18},0)),2),MID(A1,INDEX({9,11},MATCH(LEN(A1),{15,18},0))+2,2)), DATE(,MONTH(A1),DAY(A1))),{366,356,327,297,267,236,204,173,142,111,80,49,20},-1))

这个是允许输入生日，15/18位身份证的算法

=IF(IF(LEN(A1)=15,DATE(1900,MID(A1,9,2),MID(A1,11,2)),DATE(1900,MID(A1,11,2),MID(A1,13,2)))<21,"魔羯座",INDEX({"水瓶座","双鱼座","牧羊座","金牛座","双子座","巨蟹座","狮子座","处女座","天秤座","天蝎座","射手座","魔羯座"},MATCH(IF(LEN(A1)=15,DATE(1900,MID(A1,9,2),MID(A1,11,2)),DATE(1900,MID(A1,11,2),MID(A1,13,2))),{21,51,81,112,143,174,206,237,268,297,328,357},1)))

以上假设身份证在单元格A1，公式太长了，可以考虑在B1输入 =IF(LEN(A1)=15,DATE(1900,MID(A1,9,2),MID(A1,11,2)),DATE(1900,MID(A1,11,2),MID(A1,13,2)))
再C1输入 =IF(B1<21,"魔羯座",INDEX({"水瓶座","双鱼座","牧羊座","金牛座","双子座","巨蟹座","狮子座","处女座","天秤座","天蝎座","射手座","魔羯座"},MATCH(B1,{21,51,81,112,143,174,206,237,268,297,328,357},1)))
结果在C1

举例使用身份证号码来算吧
在A1中输入身份证号码，在其他单元格中执行公式
=LOOKUP(--TEXT(MID(A1,LEN(A1)*62%,4),"1900-00-00"),{1,"摩羯座";20,"水瓶座";50,"双鱼座";81,"白羊座";112,"金牛座";142,"双子座";174,"巨蟹座";205,"狮子座";236,"处女座";267,"天秤座";297,"天蝎座";327,"射手座";357,"摩羯座"})

注：星座起始日可能不太一致，自己进行修改

上面那个函数有一丢丢漏洞哈，摩羯座不能被完全跑出来。
=IFNA((IF(【生日单元格】="","",LOOKUP(--TEXT(【生日单元格】,"m/d"),--{"1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座"))),"摩羯座")
可以用这个试试！

如果A1单元格写入的是EXCEL认可的日期格式,
则B1单元格写入公式
=IF(A1="","",LOOKUP(--TEXT(A1,"m/d"),--{"1/1","1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"摩羯","水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座"))