|
額，這個(gè)問(wèn)題在問(wèn)答里已經(jīng)出現(xiàn)了幾次,，奈何真的沒(méi)幾個(gè)人會(huì)，可能是真的比較復(fù)雜，所以沒(méi)什么愿意去寫(xiě)吧！我很好奇為什么非要用Excel去做紀(jì)年的轉(zhuǎn)換呢…… 今天我正好有空,，我就給大家簡(jiǎn)單寫(xiě)一下,。 首先，有兩種方式能實(shí)現(xiàn)公歷紀(jì)年和農(nóng)歷紀(jì)年的轉(zhuǎn)換,，在Excel默認(rèn)的序列填充內(nèi)容里并不能自動(dòng)生出農(nóng)歷紀(jì)年，我們能實(shí)現(xiàn)的僅僅是轉(zhuǎn)換,，而不是自動(dòng)出結(jié)果,！ 至于兩種方式嘛，大家都知道,，一種用公式做轉(zhuǎn)換,，另一種用VBA寫(xiě)自定義函數(shù)。 1.公式法：
公式一：如果公歷日期在A1單元格,，那么我們?cè)貰1單元格做轉(zhuǎn)換,，B1輸入 =TEXT(A1,'[$-130000]yyyy年m月'&IF(LEN(--TEXT(A1,'[$-130000]dd'))=1,'初','')&'d'&'日') 公式得到結(jié)果如下： 
這種方式轉(zhuǎn)換的農(nóng)歷日期不太完美，僅能保證農(nóng)歷日期是對(duì)的,，但是顯示方式和公歷的顯示方法差別不大,，但是公式簡(jiǎn)單，有點(diǎn)基礎(chǔ)的小伙伴肯定能看懂,。 公式二：如果公歷日期在A1單元格,，那么我們?cè)貰1單元格做轉(zhuǎn)換，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日') 公式得到結(jié)果如下： 
這種方式轉(zhuǎn)換的農(nóng)歷日期似乎已經(jīng)能滿足大多數(shù)要求了,，及包含里天干地支紀(jì)年法,，也將數(shù)字的日期轉(zhuǎn)換為中文了，但相對(duì)來(lái)說(shuō)公式較為復(fù)雜,，需要點(diǎn)技巧才能讀懂,，不過(guò)還好，我?guī)痛蠹覍?xiě)好了,，復(fù)制過(guò)去就能用了,。弊端是有時(shí)候遇到農(nóng)歷閏月，這個(gè)公式就不靈了,，其他還好,！ 最后就是大招了，這個(gè)大招呢,，把公歷日期完美轉(zhuǎn)換,，但也是最復(fù)雜的。如果你用的辦公軟件是WPS的話,，那對(duì)不起了,，你可能用不了這個(gè)方法。 2.自定義函數(shù)法： 鑒于很多小伙伴可能不懂,，那么我就細(xì)致一點(diǎn)的說(shuō),，自定義函數(shù)就是,，我們自己根據(jù)需求去創(chuàng)建一個(gè)新的函數(shù)，以供我們計(jì)算使用,。 步驟一：打開(kāi)VBA,，如果你的Excel功能區(qū)中沒(méi)有【開(kāi)發(fā)工具】，那么你需要在【文件】→【選項(xiàng)】→【自定義功能區(qū)】中勾選開(kāi)發(fā)工具即可,。 也可用快捷鍵Alt F11,，打開(kāi)VBA編輯器 
步驟二：我們?cè)偕蠄D所標(biāo)識(shí)的區(qū)域內(nèi)鼠標(biāo)右鍵→插入→模塊，得到模塊1 
步驟三：在【模塊1】中錄入我們的VBA代碼,。代碼很長(zhǎng),，我就不截長(zhǎng)圖了。這里我們就定義好了一個(gè)叫做NongLi的函數(shù),。 
步驟四：代碼也寫(xiě)好了,，那么就去用起來(lái)吧！??！關(guān)閉VBA編輯器，回到Excel,，如果公歷日期在A1單元格,，那么還在再B1單元格做轉(zhuǎn)換。 
這個(gè)神奇的方法會(huì)讓你的公式很簡(jiǎn)單哦,，=NongLi（A1）即可,，這樣的方式轉(zhuǎn)換出的農(nóng)歷日期可以顯示農(nóng)歷、天干地支紀(jì)年,、生肖,、農(nóng)歷閏月等等，是不是完美,？,？？ 你一定會(huì)問(wèn),，那特么代碼你也不貼出來(lái),，我怎么知道怎么寫(xiě)？我這就貼,，這就貼,。 代碼如下： 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 '獲取當(dāng)前系統(tǒng)時(shí)間 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) = '豬' '農(nóng)歷日期名 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) = '三十' '農(nóng)歷月份名 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) = '臘' '公歷每月前面的天數(shù) 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 '農(nóng)歷數(shù)據(jù) 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 '生成當(dāng)前公歷年、月,、日 ==> 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 '計(jì)算到初始時(shí)間1921年2月8日的天數(shù)：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 '計(jì)算農(nóng)歷天干,、地支、月,、日 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個(gè)二進(jì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 '生成農(nóng)歷天干,、地支,、屬相 ==> NongliStr NongliStr = '農(nóng)歷' & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & '年' NongliStr = NongliStr & '(' & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ')' '生成農(nóng)歷月、日 ==> 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
代碼這么長(zhǎng),，你們復(fù)制拿去用就好了,，看不看的我也不在意了。 最好,，提醒一下,，運(yùn)用自定義函數(shù)的方法，你的Excel文件保存時(shí)需要保存為*****.xlsm,，這點(diǎn)很重要，千萬(wàn)注意,。,。。不然一切功虧一簣?。,。?/p> 
好了,，我寫(xiě)完了,。。,。 我就知道會(huì)有一萬(wàn)個(gè)人贊我?。?！
|
