如何在excel中實(shí)現(xiàn)陽(yáng)歷轉(zhuǎn)化陰歷

1、步驟一，在Excel工作表界面下按VALT+F11組合鍵打開(kāi)VBA窗口，進(jìn)入VBA編輯環(huán)境，在其窗口的菜單欄上依次單擊插入一模塊”可在當(dāng)前VBA工程中插入模塊，并直接進(jìn)入此模塊的代碼編輯區(qū)域?？稍诖司庉媴^(qū)域中輸入自定義函數(shù)的程序代碼,如圖：PublicFunctionNongLi(OptionalXX_DATEAsDate)II.A12=IlI-.-.-E-DimMonthAdd(11),NongliData(99),TianGan(9),DiZhi(11),ShuXiang(11),DayName(30),MonName(12)iihii山+也DimcurTime,curYear,curMo

2、nth,curDayDimGongliStr,NongliStr,NongliDayStrIDIIII:Dimi,m,n,k,isEnd,bit,TheDate獲取當(dāng)前系統(tǒng)時(shí)間1curTime=Xx_DATETianGan(0)=呷TianGan(1)=乙TianGan(2)=丙TianGan(3)=丁TianGan(4)=戊TianGan(5)=己IJ，MLTianGan(6)=庚bTianGan(7)=辛TianGan(8)=壬TianGan(9)=癸地支名稱DiZhi(O)=子DiZhi(1)=丑DiZhi(2)=寅DiZhi(3)=卯DiZhi(4)=辰DiZhi(5)=巳DiZhi(

3、6)=午DiZhi(7)=未DiZhi(8)=申DiZhi(9)=酉DiZhi(IO)=戌1DiZhi(11)=亥屬相名稱ShuXiang(O)=鼠ShuXiang(1)=牛ShuXiang(2)=虎ShuXiang(3)=兔ShuXiang(4)=龍ShuXiang(5)=蛇ShuXiang(6)=馬ShuXiang(7)=羊ShuXiang(8)=猴ShuXiang(9)=雞ShuXiang(10)=狗ShuXiang(11)=豬農(nóng)歷日期名DayName(O)=*DayName(1)=初一DayName(2)=初二DayName(3)=初三DayName(4)=初四DayName(5)=初

4、五DayName(6)=初六DayName(7)=初七DayName(8)=初八DayName(9)=初九DayName(IO)=初十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(2

5、7)=廿七DayName(28)=廿八DayName(29)=廿九DayName(30)=三十農(nóng)歷月份名|MonName(O)=*MonName(1)=正MonName(2)=二MonName(3)=三MonName(4)=四MonName(5)=五MonName(6)=六MonName(7)=七M(jìn)onName(8)=八MonName(9)=九MonName(10)=十MonName(11)=十,VIMonName(12)=臘公歷每月前面的天數(shù)MonthAdd(O)=0MonthAdd(1)=31MonthAdd(2)=59MonthAdd(3)=90MonthAdd(4)=120MonthA

6、dd(5)=151MonthAdd(6)=181MonthAdd(7)=212MonthAdd(8)=243MonthAdd(9)=273MonthAdd(10)=304MonthAdd(11)=334農(nóng)歷數(shù)據(jù)NongliData(O)=2635NongliData(1)=333387NongliData(2)=1701NongliData(3)=1748NongliData(4)=267701NongliData(5)=694NongliData(6)=2391NongliData(7)=133423NongliData(8)=1175NongliData(9)=396438NongliDa

7、ta(10)=3402NongliData(11)=3749NongliData(12)=331177NongliData(13)=1453NongliData(14)=694NongliData(15)=201326NongliData(16)=2350NongliData(17)=465197NongliData(18)=3221NongliData(19)=3402NongliData(20)=400202NongliData(21)=2901NongliData(22)=1386NongliData(23)=267611NongliData(24)=605NongliData(25)=

8、2349NongliData(26)=137515NongliData(27)=2709NongliData(28)=464533NongliData(29)=1738NongliData(30)=2901NongliData(31)=330421NongliData(32)=1242NongliData(33)=2651NongliData(34)=199255NongliData(35)=1323NongliData(36)=529706NongliData(37)=3733NongliData(38)=1706NongliData(39)=398762NongliData(40)=274

9、1NongliData(41)=1206NongliData(42)=267438NongliData(43)=2647NongliData(44)=1318NongliData(45)=204070NongliData(90)=2890NongliData(91)=267946NongliData(92)=2773NongliData(93)=592565NongliData(94)=1210NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)NongliData(51)NongliData(52)Non

10、gliData(53)NongliData(54)NongliData(55)NongliData(56)NongliData(57)NongliData(58)NongliData(59)NongliData(60)NongliData(61)NongliData(62)NongliData(63)NongliData(64)NongliData(65)NongliData(66)NongliData(67)NongliData(68)NongliData(69)NongliData(70)NongliData(71)NongliData(72)NongliData(73)NongliDat

11、a(74)NongliData(75)NongliData(76)NongliData(77)NongliData(78)NongliData(79)NongliData(80)NongliData(81)NongliData(82)NongliData(83)NongliData(84)NongliData(85)NongliData(86)NongliData(87)NongliData(88)347746165313862413330077119726372688773365531109290029223980422395117926741526356610671701174839877

12、227422391330031117516112000103749527717145227423323972350322226894934023493133973138646421960523493341232709NongliData(89)NongliData(95)=2651NongliData(96)=395863NongliData(97)=1323NongliData(98)=2707NongliData(99)=265877生成當(dāng)前公歷年、月、日=GongliStrcurYear=Year(curTime)curMonth=Month(curTime)curDay=Day(cur

13、Time)GongliStr=curYear&”年If(curMonth10)ThenGongliStr=GongliStr&0&curMonth&月ElseGongliStr=GongliStr&curMonth&月EndIfIf(curDay2)ThenTheDate=TheDate+1EndIf計(jì)算農(nóng)歷天干、地支、月、日isEnd=0m=0DoIf(NongliData(m)4095)Thenk=11Elsek=12EndIfn=kDoIf(n0)ThenExitDoNongliData(46)NongliData(47)NongliData(48)NongliData(49)Nongl

14、iData(50)EndIf獲取NongliData(m)的第n個(gè)二進(jìn)制位的值bit=NongliData(m)Fori=1TonStep1bit=Int(bit/2)Nextbit=bitMod2If(TheDate(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(cur

15、Year-4)Mod60)Mod12)&)生成農(nóng)歷月、日=NongliDayStrIf(curMonth1)ThenNongliDayStr=閏&MonName(-1*curMonth)ElseNongliDayStr=MonName(curMonth)EndIfNongliDayStr=NongliDayStr&月NongliDayStr=NongliDayStr&DayName(curDay)NongLi=NongliStr&NongliDayStrEndFunction步驟2,單擊窗口右上角的關(guān)閉按鈕關(guān)閉VBA編輯窗口，返回到工作表窗口中。此時(shí)即可在當(dāng)前工作簿中使用剛剛創(chuàng)建的自定義函數(shù)，

16、并且可以在函數(shù)列表的用戶定義類別中找到此自定義函數(shù)。如圖NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)步驟4,保存后需要在加載項(xiàng)中添加加載宏。單擊按鈕一選項(xiàng)”打開(kāi)選項(xiàng)對(duì)話框，在左側(cè)類別中選擇加載項(xiàng)”然后在右側(cè)下方的管理下拉列表中選擇加載項(xiàng)”再單擊轉(zhuǎn)到按鈕，打開(kāi)加載宏對(duì)話框，在其中勾選之前所保存的包含自定義函數(shù)的加載宏前面的復(fù)選框，最后單擊確定即可完成加載項(xiàng)的添加。NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)然寸亙二臣妻件輛良雅弔翌輛專叢洽沁，時(shí)曲W五議+41晁：J込出崔冏三洩NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)NongliData(46)NongliData(47)NongliData(48)NongliData(49)NongliData(50)*倔門迫,J目詼憩旖醸商E翻翳錚堿T$隘EBTB豎晡|珂穌畫(huà)肅絶維