| 楼主 apolloh |
Q：如何计算农历？ A：使用函数直接计算农历并不十分方便，但可以通过辅助数据来简化计算公式。参见下图：  G2单元格的公式为：
 计算农历.rar |
| 2楼 wshcw |
再补充农历转公历的计算方法: =SUMPRODUCT((YEAR(A2:A2491)=2009)*(B2:B2491="二月")*A2:A2491)+MATCH(RIGHT(G2,2),D2:D31,)-1 公式中"二月"也可以改成引用,则公式变为: =SUMPRODUCT((YEAR(A2:A2491)=2009)*(B2:B2491=LEFT(G2,LEN(G2)-2))*A2:A2491)+MATCH(RIGHT(G2,2),D2:D31,)-1 注:公式中的LEN(G2)-2是考虑闰月的字符数是5. 农历转公历的计算方法.png  |
| 3楼 gvntw |
LOOKUP(F2,A:A,B:B)，可直接写成：LOOKUP(F2,A:B) |
| 4楼 cnetkevin |
同一公历年中如果有两个腊月初一会使SUMPRODUCT计算的数组有两个不为0的项，因此出错 |
| 5楼 cnetkevin |
同一公历年中如果有两个腊月初一会使SUMPRODUCT计算的数组有两个不为0的项，因此出错 |
| 6楼 lxmihui |
谢谢了 学习 |
| 7楼 Mainy |
此为公式，可以实现公历转农历，公式在B2,A2转入年月日=CHOOSE(MOD(YEAR(LEFT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,10)+1,"庚","辛","壬","癸","甲","乙","丙","丁","戊","己")&CHOOSE(MOD(YEAR(LEFT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),4)&"-"&MID(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),6,2)&"-"&RIGHT(TEXT(A2,"[$-130000]yyyy年mm月"&IF(LEN(--TEXT(A2,"[$-130000]dd"))=1,"初","")&"dd"),2))-1900,12)+1,"子","丑","寅","卯","辰","巳","午","未","申","酉","戌","亥")&TEXT(A2,"[dbnum1][$-130000]年m月"&IF(--TEXT(A2,"[$-130000]d")<11,"初","")&TEXT(A2,"[dbnum1][$-130000]d日")) |
| 8楼 Mainy |
这是别人设置出来，实在是太佩服了，这个可以算出农历 |
| 9楼 liusir6548 |
7楼的公式有问题，2012-9-22日显示为农历九月初七，实际是八月初七 |
| 10楼 1042363772 |
学习学习 |
| 11楼 古豆 |
7楼公式仅是把“2014-2-14”的日期变成农历写法嘛……哦，原来在2007中有改变，不过仍然不准确 |