快捷搜索:   服务器  PHP  安全  IIS  linux 安全

excel函数计算农历(考虑闰月)Vlookup Text EDATE应用

excel中计算农历的公式应该是有不少人也会写,使用TEXT的隐藏技能:

TEXT(日期,"[$-130000]yyyy-mm-dd")

关于这个用法详细说明可以见:https://www.bnxb.com/uploads/allimg/1802/124T52B3-0.jpg


但是如果只是简单使用这个公式,遇到闰月就不行了。农历有润月情况,比如2014年闰九月,即有2个九月。下面是1949年~2020年的润月表。

年份

润月

1949

7

1952

5

1955

3

1957

8

1960

6

1963

4

1966

3

1968

7

1971

5

1974

4

1976

8

1979

6

1982

4

1984

10

1987

6

1990

5

1993

3

1995

8

1998

5

2001

4

2004

2

2006

7

2009

5

2012

4

2014

9

2017

6

2020

4


用text函数计算的农历日期没考虑到闰月,那怎么把闰月因素也加上呢?


【例】如下图所示,要求根据B2的阳历在B3单元格中返回对应的农历日期。

blob.png

原理是这样的

用B2的年份 (第2个函数)

Year(B2)

从D和E列查找对应的闰月(第3个函数)

VLOOKUP(YEAR(B2),D:E,2,0)

查出出来后和B2的月份进行比较(第4个函数出现)

VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2)

如果闰月大于B2的日期,不需要对日期处理,否则需要处理,为了方便处理,在表达式前添加 - 号,TRUE遇到-变成 -1 ,FALSE变成0

-(VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2))


因为有的年份没有闰月,VLOOKUP查不到会返回错误值,所以需要用IFERROR函数把错误值转换为0 (第5个函数出马)

IFERROR(-(VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2)),0)


最后如果是润月后的日期,TEXT计算后的月份再 - 1 ,完成这个功能的函数是EDATE函数(第6个函数),最终的计算农历的函数也已完成!


=EDATE(TEXT(B2,"[$-130000]yyyy-mm-dd"),IFERROR(-(VLOOKUP(YEAR(B2),D:E,2,0)<MONTH(B2)),0))


另外附送一个我更早之前设置过,使用辅助列方式计算的表格附件,结果如下

blob.png


附件如下:

新历农历转换含闰月.zip


您可能还会对下面的文章感兴趣: