作者:乔山办公网日期:
返回目录:excel表格制作
设身问份证号在C列C2开始 ,请在答D2输入公式:
=LOOKUP(--TEXT(C2,"mdd"),{101,"摩羯版座权";120,"水平座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";621,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"})。
下拉填充。
你不感觉特别麻烦么?
12个月12个星座。每月19-23号是分界线。
分界线附近的当然要用软件查的。
有时候一天能分出2个太阳星座的。
=LOOKUP(--TEXT(C3,"mdd"),{101,"摩羯座百";120,"水瓶座";219,"双鱼度座";321,"白羊座";420,"金牛座专";521,"双子座属";621,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"})
如果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"},{"水瓶","双鱼","牧羊","金牛","双子zd","巨蟹","狮子","处女","天秤","天蝎内","射手","摩羯"}&"座容"))
如果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"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座"))