如何在excel中實現(xiàn)陽歷轉(zhuǎn)化陰歷

1、步驟一，在Excel工作表界面下按<ALT+F11>組合鍵打開VBA窗口，進入VBA編輯環(huán)境，在其窗口的菜單欄上依次單擊插入”一模塊”，可在當前VBA工程中插入模塊，并直接進入此模塊的代碼編輯區(qū)域?？稍诖司庉媴^(qū)域中輸入自定義函數(shù)的程序代碼，如圖: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,獲取當前系統(tǒng)時間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)="豬"'農(nóng)歷日期名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)="三十"'農(nóng)歷月份名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)="臘"'公歷每月前面的天數(shù)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'農(nóng)歷數(shù)據(jù)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日的天數(shù)：1921-2-8(正月初一)TheDate=(curYear-1921)*365+Int(curYear-1921)/4)+curDay+MonthAdd(curMonth-1)-38If(curYearMod4)=0AndcurMonth>2)Then|TheDate=TheDate+1EndIf計算農(nóng)歷天干、地支、月、日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'生成農(nóng)歷天干、地支、屬相=>NongliStrNongliStr="農(nóng)歷"&TianGan(curYear-4)Mod60)Mod10)&DiZhi(curYear-4)Mod60)Mod12)&"年"NongliStr=NongliStr&"("&ShuXiang(curYear-4)Mod60)Mod12)&"

21、)"'生成農(nóng)歷月、日=>NongliDayStrIf(curMonth<1)Then|NongliDayStr="閏"&MonName(-1*curMonth)ElseNongliDayStr=MonName(curMonth)EndIfNongliDayStr=NongliDayStr&"月"NongliDayStr=NongliDayStr&DayName(curDay)NongLi=NongliStr&NongliDayStr步驟2,單擊窗口右上角的關(guān)閉”按鈕關(guān)閉VBA編輯窗口，返回到工作表窗口中。此時即可在當前工作簿中使用剛剛創(chuàng)建的自定義函數(shù)，并且可以在函數(shù)列表的用戶定義”類別中找到此自定義函數(shù)。如圖步驟3,要使自定義函數(shù)能夠應(yīng)用在其他工作簿中，需要將這個包含自定義函數(shù)的工作簿另存為加載宏”。依次單擊“Office按鈕”-另存為其他格式工打開另存為”對話框，在保存類型”下拉列表中選擇“Excel加載宏”，然后為此加載宏命名后單擊保存”按鈕進行保存。步驟4,保存后需要在加載項中添加加載宏。單擊“Office按鈕”一“Excel選項”，打開“Excel選項”對話框，在左側(cè)類別