如何在excel中实现阳历转化阴历

1、步骤一，在Excel工作表界面下按<ALT+F11>组合键打开VBA窗口，进入VBA编辑环境，在其窗口的菜单栏上依次单击插入”一模块”，可在当前VBA工程中插入模块，并直接进入此模块的代码编辑区域。可在此编辑区域中输入自定义函数的程序代码，如图:MVicyfJ由台-班12JUfll-19-JT超斜HcL州0I通用Ih'jfrLi?I-%d31Mr!AiPstiJr-hZkun.swim:d一融).ia口处£qirTiff'-wHIicwIrf'3ah|li£lr.Lk<Li5u*匕1，好afdLrEri.b&k3U_hJli

2、l,TtrtDiL”日“1-9。口口口u3.qanu匚ownrpFkLL*LEFkd工lL%，r3nI.J.J.PublicFunctionNongLi(OptionalXX_DATEAsDate)DimMonthAdd(11),NongliData(99),TianGan(9),DiZhi(11),ShuXiang(11),DayName(30),MonName(12)DimcurTime,curYear,curMonth,curDayDimGongliStr,NongliStr,NongliDayStrDimi,m,n,k,isEnd,bit,TheDate,获取当前系统时间curTime

3、=XXDATE天干名称TianGan(0)="甲"TianGan(1)="乙"TianGan(2)="丙"TianGan(3)="丁"TianGan(4)="戊"TianGan(5)="己"TianGan(6)="庚"TianGan(7)="辛"TianGan(8)="壬"TianGan(9)="癸"'地支名称DiZhi(0)="子"DiZhi(1)="丑&q

4、uot;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)=&qu

5、ot;虎"ShuXiang(3)="兔"ShuXiang(4)="龙"ShuXiang(5)="蛇"ShuXiang(6)="马"ShuXiang(7)="羊"ShuXiang(8)="猴"ShuXiang(9)="鸡"ShuXiang(10)="狗"ShuXiang(11)="猪"'农历日期名DayName(0)="*"DayName(1)="初一"DayN

6、ame(2)="初二"DayName(3)="初三"DayName(4)="初四"DayName(5)="初五"DayName(6)="初六"DayName(7)="初七"DayName(8)="初八"DayName(9)="初九"DayName(10)="初十DayName(11)="H"DayName(12)="十二DayName(13)="十三DayName(14)="十

7、四DayName(15)="十五DayName(16)="十六DayName(17)="十七"DayName(18)="十八"DayName(19)="十九"DayName(20)="二十”DayName(21)="廿一"DayName(22)="廿二"DayName(23)="廿三"DayName(24)="廿四"DayName(25)="廿五"DayName(26)="廿六"DayN

8、ame(27)="廿七"DayName(28)="廿八"DayName(29)="廿九"DayName(30)="三十"'农历月份名MonName(0)="*"|MonName(1)="正"MonName(2)="二"MonName(3)="三"MonName(4)="四"MonName(5)="五"MonName(6)="六"MonName(7)="七&qu

9、ot;MonName(8)="八"MonName(9)="九"MonName(10)="十"MonName(11)="十一"MonName(12)="腊"'公历每月前面的天数MonthAdd(0)=0MonthAdd(1)=31MonthAdd(2)=59MonthAdd(3)=90MonthAdd(4)=120MonthAdd(5)=151MonthAdd(6)=181MonthAdd(7)=212MonthAdd(8)=243MonthAdd(9)=273MonthAdd(10)=3

10、04MonthAdd(11)=334'农历数据NongliData(0)=2635NongliData(1)=333387NongliData(2)=1701NongliData(3)=1748NongliData(4)=267701NongliData(5)=694NongliData(6)=2391NongliData(7)=133423NongliData(8)=1175NongliData(9)=396438NongliData(10)=3402NongliData(11)=3749NongliData(12)=331177NongliData(13)=1453NongliDa

11、ta(14)=694NongliData(15)=201326NongliData(16)=2350NongliData(17)=465197NongliData(18)=3221NongliData(19)=3402NongliData(20)=400202NongliData(21)=2901NongliData(22)=1386NongliData(23)=267611NongliData(24)=605NongliData(25)=2349NongliData(26)=137515NongliData(27)=2709NongliData(28)=464533NongliData(29

12、)=1738NongliData(30)=2901NongliData(31)=330421NongliData(32)=1242NongliData(33)=2651NongliData(34)=199255NongliData(35)=1323NongliData(36)=529706NongliData(37)=3733NongliData(38)=1706NongliData(39)=398762NongliData(40)=2741NongliData(41)=1206NongliData(42)=267438NongliData(43)=2647NongliData(44)=131

13、8NongliData(45)=204070NongliData(46)=3477NongliData(47)=461653NongliData(48)=1386NongliData(49)=2413NongliData(50)=330077NongliData(51)=1197NongliData(52)=2637NongliData(53)=268877NongliData(54)=3365NongliData(55)=531109NongliData(56)=2900NongliData(57)=2922NongliData(58)=398042NongliData(59)=2395No

14、ngliData(60)=1179NongliData(61)=267415NongliData(62)=2635NongliData(63)=661067NongliData(64)=1701NongliData(65)=1748NongliData(66)=398772NongliData(67)=2742NongliData(68)=2391NongliData(69)=330031NongliData(70)=1175NongliData(71)=1611NongliData(72)=200010NongliData(73)=3749NongliData(74)=527717Nongl

15、iData(75)=1452NongliData(76)=2742NongliData(77)=332397NongliData(78)=2350NongliData(79)=3222NongliData(80)=268949NongliData(81)=3402NongliData(82)=3493NongliData(83)=133973NongliData(84)=1386NongliData(85)=464219NongliData(86)=605NongliData(87)=2349NongliData(88)=334123NongliData(89)=2709NongliData(

16、90)=2890NongliData(91)=267946NongliData(92)=2773NongliData(93)=592565NongliData(94)=1210NongliData(95)=2651NongliData(96)=395863NongliData(97)=1323NongliData(98)=2707NongliData(99)=265877'生成当前公历年、月、日=>GongliStrcurYear=Year(curTime)curMonth=Month(curTime)curDay=Day(curTime)GongliStr=curYear&am

17、p;"年"If(curMonth<10)ThenGongliStr=GongliStr&"0"&curMonth&"月"ElseGongliStr=GongliStr&curMonth&"月"EndIfIf(curDay<10)ThenGongliStr=GongliStr&"0"&curDay&"日"ElseGongliStr=GongliStr&curDay&"日&quo

18、t;EndIf计算到初始时间1921年2月8日的天数：1921-2-8(正月初一)TheDate=(curYear-1921)*365+Int(curYear-1921)/4)+curDay+MonthAdd(curMonth-1)-38If(curYearMod4)=0AndcurMonth>2)Then|TheDate=TheDate+1EndIf计算农历天干、地支、月、日isEnd=0m=0DoIf(NongliData(m)<4095)Thenk=11Elsek=12EndIfn=kDoIf(n<0)ThenExitDoEndIf,获取NongliData(m)的第n

19、个二进制位的值bit=NongliData(m)Fori=1TonStep1bit=Int(bit/2)Next|bit=bitMod2If(TheDate<=29+bit)ThenisEnd=1ExitDoEndIfTheDate=TheDate-29-bitn=n-1LoopIf(isEnd=1)ThenExitDoEndIfm=m+1LoopcurYear=1921+mcurMonth=k-n+1curDay=TheDateIf(k=12)ThenIf(curMonth=(Int(NongliData(m)/65536)+1)ThencurMonth=1-curMonthElseI

20、f(curMonth>(Int(NongliData(m)/65536)+1)ThencurMonth=curMonth-1EndIfEndIf'生成农历天干、地支、属相=>NongliStrNongliStr="农历"&TianGan(curYear-4)Mod60)Mod10)&DiZhi(curYear-4)Mod60)Mod12)&"年"NongliStr=NongliStr&"("&ShuXiang(curYear-4)Mod60)Mod12)&"

21、)"'生成农历月、日=>NongliDayStrIf(curMonth<1)Then|NongliDayStr="闰"&MonName(-1*curMonth)ElseNongliDayStr=MonName(curMonth)EndIfNongliDayStr=NongliDayStr&"月"NongliDayStr=NongliDayStr&DayName(curDay)NongLi=NongliStr&NongliDayStr步骤2,单击窗口右上角的关闭”按钮关闭VBA编辑窗口，返回到工作表窗口中。此时即可在当前工作簿中使用刚刚创建的自定义函数，并且可以在函数列表的用户定义”类别中找到此自定义函数。如图步骤3,要使自定义函数能够应用在其他工作簿中，需要将这个包含自定义函数的工作簿另存为加载宏”。依次单击“Office按钮”-另存为其他格式工打开另存为”对话框，在保存类型”下拉列表中选择“Excel加载宏”，然后为此加载宏命名后单击保存”按钮进行保存。步骤4,保存后需要在加载项中添加加载宏。单击“Office按钮”一“Excel选项”，打开“Excel选项”对话框，在左侧类别