在Excel中,如何根据公历日期返回农历生肖
首先根据公历出生日期求出农历的年份,然后把年份除以12求出余数,即确定该年份在十二生肖中的排位,最后使用MID函数从生肖字符串中截取对应的生肖。
→ 操作方法
选择C3:C12单元格区域,输入下列公式,然后按组合键结束。
=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(TEXT(B3,"[$-130000]e")-4,12)+1,1)
→ 原理分析
根据公历生日查询农历生肖
1.使用TEXT(B3,"[$-130000]e")将B3单元格中公历的出生日期转换为农历年份,返回的结果是"1977",其中数字13也可以是0E、11、12。
2.使用MOD函数根据年份取得12个生肖中的位置...全部
首先根据公历出生日期求出农历的年份,然后把年份除以12求出余数,即确定该年份在十二生肖中的排位,最后使用MID函数从生肖字符串中截取对应的生肖。
→ 操作方法
选择C3:C12单元格区域,输入下列公式,然后按组合键结束。
=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(TEXT(B3,"[$-130000]e")-4,12)+1,1)
→ 原理分析
根据公历生日查询农历生肖
1.使用TEXT(B3,"[$-130000]e")将B3单元格中公历的出生日期转换为农历年份,返回的结果是"1977",其中数字13也可以是0E、11、12。
2.使用MOD函数根据年份取得12个生肖中的位置。因为Excel中时间系统是1900-1-1开始算起,而1900年是鼠年,将1900对12求余数得到4,因此用TEXT函数求出农历年份后还要减去4,以便对应生肖字符串中生肖的正确排位。
由于生肖是12年一循环,所以要对农历年份减去4后的数据除以12求余数,然后加上1,即可得出该生肖在生肖字符串中的字符位置。如MOD(TEXT(B3,"[$-130000]e")-4,12)+1返回的结果是6。
3.最后使用MID函数从生肖字符串中截取对应的生肖。
关于使用数字格式换算阴历,请参阅:第1章第2节疑难21。
注意:本例实际是计算阴历年份而非农历年份,根据核查,使用Excel推算1960年至2050年阴历1月1日与农历正月初一基本吻合,个别年份相差1天。
阴历日期中可能出现13月,表示该年有“闰月”,但不能体现某月为闰月。
→ 知识扩展
1.如果把生肖字符串中的生肖重新排列,即把“鼠”排在第五位,其它顺移,则公式中求得的农历年份不需再减4,公式如下:
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(TEXT(B3,"[$-130000]e"),12)+1,1)
2.本例中也可以使用CHOOSE函数提取生肖,公式如下:
=CHOOSE(MOD(TEXT(B3,"[$-130000]e")-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
3.如果图
181‑1中B列日期为农历日期,则上述公式就不必用TEXT函数求取农历年份,直接用YEAR函数提取年份参与下一步计算即可,公式如下:
=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B3)-4,12)+1,1)
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(B3),12)+1,1)
=CHOOSE(MOD(YEAR(B3)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
。收起