当前位置:首页 > 礼品 > 正文内容

如何用EXCEL自动生成农历记年?

醉死的梦3年前 (2021-10-15)礼品3376
印刷厂直印●彩页1000张只需要69元●名片5元每盒-更多报价➦联系电话:138-1621-1622(微信同号)

额,这个问题在问答里已经出现了几次,奈何真的没几个人会,可能是真的比较复杂,所以没什么愿意去写吧!我很好奇为什么非要用Excel去做纪年的转换呢……

今天我正好有空,我就给大家简单写一下。

首先,有两种方式能实现公历纪年和农历纪年的转换,在Excel默认的序列填充内容里并不能自动生出农历纪年,我们能实现的仅仅是转换,而不是自动出结果!

至于两种方式嘛,大家都知道,一种用公式做转换,另一种用VBA写自定义函数。

1.公式法:

公式一:如果公历日期在A1单元格,那么我们再B1单元格做转换,B1输入=TEXT(A1,"[$-130000]yyyy年m月"IF(LEN(--TEXT(A1,"[$-130000]dd"))=1,"初","")"d""日")公式得到结果如下:

这种方式转换的农历日期不太完美,仅能保证农历日期是对的,但是显示方式和公历的显示方法差别不大,但是公式简单,有点基础的小伙伴肯定能看懂。

公式二:如果公历日期在A1单元格,那么我们再B1单元格做转换,B1输入=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A1,"[$-130000]e")-4,10)+1,1)MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A1,"[$-130000]e")-4,12)+1,1)"年"TEXT(A1,"[$-130000][DBNum1]m月d日")公式得到结果如下:

这种方式转换的农历日期似乎已经能满足大多数要求了,及包含里天干地支纪年法,也将数字的日期转换为中文了,但相对来说公式较为复杂,需要点技巧才能读懂,不过还好,我帮大家写好了,复制过去就能用了。弊端是有时候遇到农历闰月,这个公式就不灵了,其他还好!

最后就是大招了,这个大招呢,把公历日期完美转换,但也是最复杂的。如果你用的办公软件是WPS的话,那对不起了,你可能用不了这个方法。

2.自定义函数法:

鉴于很多小伙伴可能不懂,那么我就细致一点的说,自定义函数就是,我们自己根据需求去创建一个新的函数,以供我们计算使用。

步骤一:打开VBA,如果你的Excel功能区中没有【开发工具】,那么你需要在【文件】→【选项】→【自定义功能区】中勾选开发工具即可。

也可用快捷键Alt+F11,打开VBA编辑器

步骤二:我们再上图所标识的区域内鼠标右键→插入→模块,得到模块1

步骤三:在【模块1】中录入我们的VBA代码。代码很长,我就不截长图了。这里我们就定义好了一个叫做NongLi的函数。

步骤四:代码也写好了,那么就去用起来吧!!!关闭VBA编辑器,回到Excel,如果公历日期在A1单元格,那么还在再B1单元格做转换。

这个神奇的方法会让你的公式很简单哦,=NongLi(A1)即可,这样的方式转换出的农历日期可以显示农历、天干地支纪年、生肖、农历闰月等等,是不是完美???

你一定会问,那特么代码你也不贴出来,我怎么知道怎么写?我这就贴,这就贴。

代码如下:

Public Function NongLi(Optional XX_DATE As Date)

Dim MonthAdd(11), NongliData(99), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), MonName(12)

Dim curTime, curYear, curMonth, curDay

Dim GongliStr, NongliStr, NongliDayStr

Dim i, m, n, k, isEnd, bit, TheDate

'获取当前系统时间

curTime = XX_DATE

'天干名称

TianGan(0) = "甲"

TianGan(1) = "乙"

TianGan(2) = "丙"

TianGan(3) = "丁"

TianGan(4) = "戊"

TianGan(5) = "己"

TianGan(6) = "庚"

TianGan(7) = "辛"

TianGan(8) = "壬"

TianGan(9) = "癸"

'地支名称

DiZhi(0) = "子"

DiZhi(1) = "丑"

DiZhi(2) = "寅"

DiZhi(3) = "卯"

DiZhi(4) = "辰"

DiZhi(5) = "巳"

DiZhi(6) = "午"

DiZhi(7) = "未"

DiZhi(8) = "申"

DiZhi(9) = "酉"

DiZhi(10) = "戌"

DiZhi(11) = "亥"

'属相名称

ShuXiang(0) = "鼠"

ShuXiang(1) = "牛"

ShuXiang(2) = "虎"

ShuXiang(3) = "兔"

ShuXiang(4) = "龙"

ShuXiang(5) = "蛇"

ShuXiang(6) = "马"

ShuXiang(7) = "羊"

ShuXiang(8) = "猴"

ShuXiang(9) = "鸡"

ShuXiang(10) = "狗"

ShuXiang(11) = "猪"

'农历日期名

DayName(0) = "*"

DayName(1) = "初一"

DayName(2) = "初二"

DayName(3) = "初三"

DayName(4) = "初四"

DayName(5) = "初五"

DayName(6) = "初六"

DayName(7) = "初七"

DayName(8) = "初八"

DayName(9) = "初九"

DayName(10) = "初十"

DayName(11) = "十一"

DayName(12) = "十二"

DayName(13) = "十三"

DayName(14) = "十四"

DayName(15) = "十五"

DayName(16) = "十六"

DayName(17) = "十七"

DayName(18) = "十八"

DayName(19) = "十九"

DayName(20) = "二十"

DayName(21) = "廿一"

DayName(22) = "廿二"

DayName(23) = "廿三"

DayName(24) = "廿四"

DayName(25) = "廿五"

DayName(26) = "廿六"

DayName(27) = "廿七"

DayName(28) = "廿八"

DayName(29) = "廿九"

DayName(30) = "三十"

'农历月份名

MonName(0) = "*"

MonName(1) = "正"

MonName(2) = "二"

MonName(3) = "三"

MonName(4) = "四"

MonName(5) = "五"

MonName(6) = "六"

MonName(7) = "七"

MonName(8) = "八"

MonName(9) = "九"

MonName(10) = "十"

MonName(11) = "十一"

MonName(12) = "腊"

'公历每月前面的天数

MonthAdd(0) = 0

MonthAdd(1) = 31

MonthAdd(2) = 59

MonthAdd(3) = 90

MonthAdd(4) = 120

MonthAdd(5) = 151

MonthAdd(6) = 181

MonthAdd(7) = 212

MonthAdd(8) = 243

MonthAdd(9) = 273

MonthAdd(10) = 304

MonthAdd(11) = 334

'农历数据

NongliData(0) = 2635

NongliData(1) = 333387

NongliData(2) = 1701

NongliData(3) = 1748

NongliData(4) = 267701

NongliData(5) = 694

NongliData(6) = 2391

NongliData(7) = 133423

NongliData(8) = 1175

NongliData(9) = 396438

NongliData(10) = 3402

NongliData(11) = 3749

NongliData(12) = 331177

NongliData(13) = 1453

NongliData(14) = 694

NongliData(15) = 201326

NongliData(16) = 2350

NongliData(17) = 465197

NongliData(18) = 3221

NongliData(19) = 3402

NongliData(20) = 400202

NongliData(21) = 2901

NongliData(22) = 1386

NongliData(23) = 267611

NongliData(24) = 605

NongliData(25) = 2349

NongliData(26) = 137515

NongliData(27) = 2709

NongliData(28) = 464533

NongliData(29) = 1738

NongliData(30) = 2901

NongliData(31) = 330421

NongliData(32) = 1242

NongliData(33) = 2651

NongliData(34) = 199255

NongliData(35) = 1323

NongliData(36) = 529706

NongliData(37) = 3733

NongliData(38) = 1706

NongliData(39) = 398762

NongliData(40) = 2741

NongliData(41) = 1206

NongliData(42) = 267438

NongliData(43) = 2647

NongliData(44) = 1318

NongliData(45) = 204070

NongliData(46) = 3477

NongliData(47) = 461653

NongliData(48) = 1386

NongliData(49) = 2413

NongliData(50) = 330077

NongliData(51) = 1197

NongliData(52) = 2637

NongliData(53) = 268877

NongliData(54) = 3365

NongliData(55) = 531109

NongliData(56) = 2900

NongliData(57) = 2922

NongliData(58) = 398042

NongliData(59) = 2395

NongliData(60) = 1179

NongliData(61) = 267415

NongliData(62) = 2635

NongliData(63) = 661067

NongliData(64) = 1701

NongliData(65) = 1748

NongliData(66) = 398772

NongliData(67) = 2742

NongliData(68) = 2391

NongliData(69) = 330031

NongliData(70) = 1175

NongliData(71) = 1611

NongliData(72) = 200010

NongliData(73) = 3749

NongliData(74) = 527717

NongliData(75) = 1452

NongliData(76) = 2742

NongliData(77) = 332397

NongliData(78) = 2350

NongliData(79) = 3222

NongliData(80) = 268949

NongliData(81) = 3402

NongliData(82) = 3493

NongliData(83) = 133973

NongliData(84) = 1386

NongliData(85) = 464219

NongliData(86) = 605

NongliData(87) = 2349

NongliData(88) = 334123

NongliData(89) = 2709

NongliData(90) = 2890

NongliData(91) = 267946

NongliData(92) = 2773

NongliData(93) = 592565

NongliData(94) = 1210

NongliData(95) = 2651

NongliData(96) = 395863

NongliData(97) = 1323

NongliData(98) = 2707

NongliData(99) = 265877

'生成当前公历年、月、日 == GongliStr

curYear = Year(curTime)

curMonth = Month(curTime)

curDay = Day(curTime)

GongliStr = curYear "年"

If (curMonth 10) Then

GongliStr = GongliStr "0" curMonth "月"

Else

GongliStr = GongliStr curMonth "月"

End If

If (curDay 10) Then

GongliStr = GongliStr "0" curDay "日"

Else

GongliStr = GongliStr curDay "日"

End If

'计算到初始时间1921年2月8日的天数:1921-2-8(正月初一)

TheDate = (curYear - 1921) * 365 + Int((curYear - 1921) / 4) + curDay + MonthAdd(curMonth - 1) - 38

If ((curYear Mod 4) = 0 And curMonth 2) Then

TheDate = TheDate + 1

End If

'计算农历天干、地支、月、日

isEnd = 0

m = 0

Do

If (NongliData(m) 4095) Then

k = 11

Else

k = 12

End If

n = k

Do

If (n 0) Then

Exit Do

End If

'获取NongliData(m)的第n个二进制位的值

bit = NongliData(m)

For i = 1 To n Step 1

bit = Int(bit / 2)

Next

bit = bit Mod 2

If (TheDate = 29 + bit) Then

isEnd = 1

Exit Do

End If

TheDate = TheDate - 29 - bit

n = n - 1

Loop

If (isEnd = 1) Then

Exit Do

End If

m = m + 1

Loop

curYear = 1921 + m

curMonth = k - n + 1

curDay = TheDate

If (k = 12) Then

If (curMonth = (Int(NongliData(m) / 65536) + 1)) Then

curMonth = 1 - curMonth

ElseIf (curMonth (Int(NongliData(m) / 65536) + 1)) Then

curMonth = curMonth - 1

End If

End If

'生成农历天干、地支、属相 == NongliStr

NongliStr = "农历" TianGan(((curYear - 4) Mod 60) Mod 10) DiZhi(((curYear - 4) Mod 60) Mod 12) "年"

NongliStr = NongliStr "(" ShuXiang(((curYear - 4) Mod 60) Mod 12) ")"

'生成农历月、日 == NongliDayStr

If (curMonth 1) Then

NongliDayStr = "闰" MonName(-1 * curMonth)

Else

NongliDayStr = MonName(curMonth)

End If

NongliDayStr = NongliDayStr "月"

NongliDayStr = NongliDayStr DayName(curDay)

NongLi = NongliStr NongliDayStr

End Function

代码这么长,你们复制拿去用就好了,看不看的我也不在意了。

最好,提醒一下,运用自定义函数的方法,你的Excel文件保存时需要保存为*****.xlsm,这点很重要,千万注意。。。不然一切功亏一篑!!!

好了,我写完了。。。

我就知道会有一万个人赞我!!!

收藏0

发表评论

访客

看不清,换一张

◎欢迎参与讨论,请在这里发表您的看法和观点。