国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

打開APP
userphoto
未登錄

開通VIP,暢享免費(fèi)電子書等14項(xiàng)超值服

開通VIP
日歷干支換算(年月日)

日歷干支換算(年月日)  

2014-04-04 18:57:52|  分類: 一技之長 |  標(biāo)簽:干支經(jīng)年算法  |舉報(bào)|字號 訂閱

 先補(bǔ)充點(diǎn)小知識:
古時以干支表示時辰,一天合12個時辰,1個時辰相當(dāng)于現(xiàn)在的2小時。
子 23~1  丑 1~3 寅 3~5 卯 5~7 辰 7~9 巳  9~1  午 11~13  未 13~15  申 15~17  酉 17~19  戌 19~21  亥 21~23

我們知道,農(nóng)村人忌諱,無論干什么事,先看老黃歷。陰歷仿佛真是趨吉避兇的參照。

那么陰歷的很多數(shù)據(jù)是怎么算來的呢?
先說年干。
大家知道,今年是馬年,又說甲午年。

年干支算法:
①推算天干:公元年末位數(shù)-3=年干(適用于任何年代)。
②推算地支:公元年末二位數(shù)+9=年支(適用于十九世紀(jì),即1800—1899 年); 
公元年末二位數(shù)十1=年支(適用于二十世紀(jì),即1900—1999 年);  
公元年末二位數(shù)+5=年支(適用于廿一世紀(jì),即2000—2099 年);  
說明: ①年干按1(甲).2(乙)、3(丙)、4(丁)、5(戊)、6(己)、7(庚)、8(辛)、9(壬)、10(癸)順序;  
年支按1(子)、2(丑)、3(寅)、4(卯)、5(辰)、6(巳)、7(午)、8(未)、9(申)、10(酉)、11(戌)、12(亥)順序。  
②求年干,不夠減則借10;求年支,超過12要遞減12,取余數(shù)。  
③求年干之公式,適用于任何年代;求年支之公式,不同公式只適用于某一世紀(jì)“00年)。 

我們打開EXCEL,根據(jù)上面的條件,列出公式:
 系統(tǒng)時間如果無誤,我們分別填入公式:
k1=today()
提取當(dāng)前系統(tǒng)日期
j2=year(k1)
提取k1的年份數(shù)
l2=month(k1)
提取k1的月份數(shù)
n2=day(k1)
提取k1的日期數(shù)

年干算法:
年的末尾
k4=--RIGHT(J2,1)
--表示把序數(shù)由文本轉(zhuǎn)換為數(shù)字
天干數(shù)
m4=IF(IF(K4>2,K4-3,K4+7)=0,10,IF(K4>2,K4-3,K4+7))
天干
o4=INDEX(B3:B12,MATCH(K5,C3:C12),1,1)

年支算法:
 
年的末尾兩位數(shù)
k4=--RIGHT(J2,2)
應(yīng)加數(shù)
m5=IF(J2>2099,"不知道",IF(J2>=2000,5,IF(J2>=1900,1,
IF(J2>=1800,9,"不知道"))))
地支數(shù)
p5=MOD(K5+M5,12)
地支
=INDEX(E3:E14,MATCH(P5,F3:F14),1,1)

月干算法:
甲已之年丙作首,乙庚之歲戊為頭,
 丙辛之歲尋庚上,丁壬壬寅順行流,
戊癸之年何方發(fā),甲寅之上好追求。

月支順序是固定的:
對應(yīng)
正月二月三月四月五月六月
七月八月九月十月十一月十二月

在EXCEL中,先作源表:如右圖.
上面我們已經(jīng)求過年的天干了,直接拿來就用.
所以有:
設(shè)Q12為年干,L4為月份:
 
年干位置:
K7=MATCH(Q12,B21:B31,0)
月份位置:
K6=MATCH(L4,B21:N21,0)
所以月干支為:
=INDEX(B21:N31,K7,K6)


日干支算法

根據(jù)公歷日期計(jì)算當(dāng)日干支口訣,經(jīng)過校正后,應(yīng)為:

  乘5除4,9加日,
  雙月間隔30天。
  一二自加整少1,
  0三四1,六2, 七,

  3,4,5八十尾順。
計(jì)算基本公式是:(××年×5+××年÷4+9+日+修正值)÷60=商……余數(shù)
日干確定:取余數(shù)的個位為天干序數(shù),對應(yīng)天干(余數(shù)為0,對應(yīng)天干是癸)。
日支確定:余數(shù)÷12=商……[余數(shù)],取[余數(shù)]為地支序數(shù),對應(yīng)地支([余數(shù)]為0,對應(yīng)地支為亥)
(逢閏年 一二月不加,平年加1,加2)  一月加修正值1,二月加修正值2,三月不加修正值,四、五月加1,六、七月加2,八月加3,九、十月加4,十一、十二月加5。
具體怎么算呢? 

 如右圖:設(shè)b2為年份,b4 為日期,b5為年末尾兩位數(shù)
第一步:確定被判斷年份是否大于2000,
先判斷是否閏年.
b9=IF(MOD(B2,4)<>0,"",IF(MOD(B2,100)<>0,"閏年",IF(MOD(B2,400)<>0,"","閏年")))
根據(jù)判斷,確定修正月格式
b10=B9&B3
根據(jù)修正月,查詢是修正值
b11=INDEX(數(shù)據(jù)表!AC4:AC27,MATCH(B10,數(shù)據(jù)表!AA4:AA27,0))
第二步:判斷是否雙月,并確定附加值
b8=IF(MOD(B3,2)=0,30,0)
第三步:
判斷是否大于等于2000,并確認(rèn)附加值
b6=IF(B2>=2000,100,0)
第三步:確認(rèn)日天干數(shù):m1=MOD((B6+B5)*5+QUOTIENT((B5+B6),4)
+9+B4+B11+B8,60)
L1=--RIGHT(M1,1)
第四步:確認(rèn)日地支數(shù):
I2=IF(MOD(M1,12)=0,10,MOD(M1,12))

(相信到這步大家應(yīng)該會了.)
第五步:根據(jù)天干數(shù),地支數(shù),確定天干,地支
=INDEX(數(shù)據(jù)表!B2:B11,L1)
=INDEX(數(shù)據(jù)表!D2:D13,參數(shù)表!L2)

回顧一下:我們查出了年干支數(shù),月干支數(shù),日干支數(shù).
其中,我們需要提供的僅僅是公歷的年月日,陰歷的月份.
陽歷的年月日可以自動從系統(tǒng)提取,但是陰歷的月份日期怎么來呢?我們不知道???
我們可以自定義一個函數(shù):
ALT+F11,打開"Visual Basic"編輯器,新建模塊,復(fù)制下面的代碼:

'公歷轉(zhuǎn)農(nóng)歷模塊'原創(chuàng):互聯(lián)網(wǎng)'修正:犟神 2005/1/12'// 農(nóng)歷數(shù)據(jù)定義 //'先以 H2B 函數(shù)還原成長度為 18 的字符串,其定義如下:'前12個字節(jié)代表1-12月:1為大月,0為小月;壓縮成十六進(jìn)制(1-3位)'第13位為閏月的情況,1為大月30天,0為小月29天;(4位)'第14位為閏月的月份,如果不是閏月為0,否則給出月份(5位)'最后4位為當(dāng)年農(nóng)歷新年的公歷日期,如0131代表1月31日;當(dāng)作數(shù)值轉(zhuǎn)十六進(jìn)制(6-7位)'農(nóng)歷常量(1899~2100,共202年)Private Const ylData = "AB500D2,4BD0883," _ & "4AE00DB,A5700D0,54D0581,D2600D8,D9500CC,655147D,56A00D5,9AD00CA,55D027A,4AE00D2," _ & "A5B0682,A4D00DA,D2500CE,D25157E,B5500D6,56A00CC,ADA027B,95B00D3,49717C9,49B00DC," _ & "A4B00D0,B4B0580,6A500D8,6D400CD,AB5147C,2B600D5,95700CA,52F027B,49700D2,6560682," _ & "D4A00D9,EA500CE,6A9157E,5AD00D6,2B600CC,86E137C,92E00D3,C8D1783,C9500DB,D4A00D0," _ & "D8A167F,B5500D7,56A00CD,A5B147D,25D00D5,92D00CA,D2B027A,A9500D2,B550781,6CA00D9," _ & "B5500CE,535157F,4DA00D6,A5B00CB,457037C,52B00D4,A9A0883,E9500DA,6AA00D0,AEA0680," _ & "AB500D7,4B600CD,AAE047D,A5700D5,52600CA,F260379,D9500D1,5B50782,56A00D9,96D00CE," _ & "4DD057F,4AD00D7,A4D00CB,D4D047B,D2500D3,D550883,B5400DA,B6A00CF,95A1680,95B00D8," _ & "49B00CD,A97047D,A4B00D5,B270ACA,6A500DC,6D400D1,AF40681,AB600D9,93700CE,4AF057F," _ & "49700D7,64B00CC,74A037B,EA500D2,6B50883,5AC00DB,AB600CF,96D0580,92E00D8,C9600CD," _ & "D95047C,D4A00D4,DA500C9,755027A,56A00D1,ABB0781,25D00DA,92D00CF,CAB057E,A9500D6," _ & "B4A00CB,BAA047B,B5500D2,55D0983,4BA00DB,A5B00D0,5171680,52B00D8,A9300CD,795047D," _ & "6AA00D4,AD500C9,5B5027A,4B600D2,96E0681,A4E00D9,D2600CE,EA6057E,D5300D5,5AA00CB," _ & "76A037B,96D00D3,4AB0B83,4AD00DB,A4D00D0,D0B1680,D2500D7,D5200CC,DD4057C,B5A00D4," _ & "56D00C9,55B027A,49B00D2,A570782,A4B00D9,AA500CE,B25157E,6D200D6,ADA00CA,4B6137B," _ & "93700D3,49F08C9,49700DB,64B00D0,68A1680,EA500D7,6AA00CC,A6C147C,AAE00D4,92E00CA," _ & "D2E0379,C9600D1,D550781,D4A00D9,DA400CD,5D5057E,56A00D6,A6C00CB,55D047B,52D00D3," _ & "A9B0883,A9500DB,B4A00CF,B6A067F,AD500D7,55A00CD,ABA047C,A5A00D4,52B00CA,B27037A," _ & "69300D1,7330781,6AA00D9,AD500CE,4B5157E,4B600D6,A5700CB,54E047C,D1600D2,E960882," _ & "D5200DA,DAA00CF,6AA167F,56D00D7,4AE00CD,A9D047D,A2D00D4,D1500C9,F250279,D5200D1"Private Const ylMd0 = "初一初二初三初四初五初六初七初八初九初十十一十二十三十四十五" _ & "十六十七十八十九二十廿一廿二廿三廿四廿五廿六廿七廿八廿九三十 "Private Const ylMn0 = "正二三四五六七八九十冬臘"Private Const ylTianGan0 = "甲乙丙丁戊己庚辛壬癸"Private Const ylDiZhi0 = "子丑寅卯辰巳午未申酉戌亥"Private Const ylShu0 = "鼠牛虎兔龍蛇馬羊猴雞狗豬"'公歷日期轉(zhuǎn)農(nóng)歷Function GetYLDate(ByVal strDate As String) As StringOn Error GoTo aErr If Not IsDate(strDate) Then Exit Function Dim setDate As Date, tYear As Integer, tMonth As Integer, tDay As Integer setDate = CDate(strDate) tYear = Year(setDate): tMonth = Month(setDate): tDay = Day(setDate) '如果不是有效有日期,退出 If tYear > 2100 Or tYear < 1900 Then Exit Function Dim daList() As String * 18, conDate As Date, thisMonths As String Dim AddYear As Integer, AddMonth As Integer, AddDay As Integer, getDay As Integer Dim YLyear As String, YLShuXing As String Dim dd0 As String, mm0 As String, ganzhi(0 To 59) As String * 2 Dim RunYue As Boolean, RunYue1 As Integer, mDays As Integer, i As Integer '加載2年內(nèi)的農(nóng)歷數(shù)據(jù) ReDim daList(tYear - 1 To tYear) daList(tYear - 1) = H2B(Mid(ylData, (tYear - 1900) * 8 + 1, 7)) daList(tYear) = H2B(Mid(ylData, (tYear - 1900 + 1) * 8 + 1, 7)) AddYear = tYearinitYL: AddMonth = CInt(Mid(daList(AddYear), 15, 2)) AddDay = CInt(Mid(daList(AddYear), 17, 2)) conDate = DateSerial(AddYear, AddMonth, AddDay) '農(nóng)歷新年日期 getDay = DateDiff("d", conDate, setDate) + 1 '相差天數(shù) If getDay < 1 Then AddYear = AddYear - 1: GoTo initYL thisMonths = Left(daList(AddYear), 14) RunYue1 = Val("&H" & Right(thisMonths, 1)) '閏月月份 If RunYue1 > 0 Then '有閏月 thisMonths = Left(thisMonths, RunYue1) & Mid(thisMonths, 13, 1) & Mid(thisMonths, RunYue1 + 1) End If thisMonths = Left(thisMonths, 13) For i = 1 To 13 '計(jì)算天數(shù) mDays = 29 + CInt(Mid(thisMonths, i, 1)) If getDay > mDays Then getDay = getDay - mDays Else If RunYue1 > 0 Then If i = RunYue1 + 1 Then RunYue = True If i > RunYue1 Then i = i - 1 End If AddMonth = i AddDay = getDay Exit For End If Next dd0 = Mid(ylMd0, (AddDay - 1) * 2 + 1, 2) mm0 = Mid(ylMn0, AddMonth, 1) + "月" For i = 0 To 59 ganzhi(i) = Mid(ylTianGan0, (i Mod 10) + 1, 1) + Mid(ylDiZhi0, (i Mod 12) + 1, 1) Next i YLyear = ganzhi((AddYear - 4) Mod 60) YLShuXing = Mid(ylShu0, ((AddYear - 4) Mod 12) + 1, 1) If RunYue Then mm0 = "閏" & mm0 GetYLDate = "農(nóng)歷" & YLyear & "(" & YLShuXing & ")年" & mm0 & dd0aErr: End Function'農(nóng)歷轉(zhuǎn)公歷日期'secondMonth 為真,則天示當(dāng) tMonth 是閏月時,取第二個月Function GetDate(ByVal tYear As Integer, tMonth As Integer, tDay As Integer, Optional secondMonth As Boolean = False) As StringOn Error GoTo aErr If tYear > 2100 Or tYear < 1899 Or tMonth > 12 Or tMonth < 1 Or tDay > 30 Or tDay < 1 Then Exit Function Dim thisMonths As String, ylNewYear As Date, toMonth As Integer Dim mDays As Integer, RunYue1 As Integer, i As Integer thisMonths = H2B(Mid(ylData, (tYear - 1899) * 8 + 1, 7)) If tDay > 29 + CInt(Mid(thisMonths, tMonth, 1)) Then Exit Function ylNewYear = DateSerial(tYear, CInt(Mid(thisMonths, 15, 2)), CInt(Mid(thisMonths, 17, 2))) '農(nóng)歷新年日期 thisMonths = Left(thisMonths, 14) RunYue1 = Val("&H" & Right(thisMonths, 1)) '閏月月份 toMonth = tMonth - 1 If RunYue1 > 0 Then '有閏月 thisMonths = Left(thisMonths, RunYue1) & Mid(thisMonths, 13, 1) & Mid(thisMonths, RunYue1 + 1) If tMonth > RunYue1 Or (secondMonth And tMonth = RunYue1) Then toMonth = tMonth End If thisMonths = Left(thisMonths, 13) mDays = 0 For i = 1 To toMonth mDays = mDays + 29 + CInt(Mid(thisMonths, i, 1)) Next mDays = mDays + tDay GetDate = ylNewYear + mDays - 1aErr: End Function'將壓縮的陰歷字符還原Private Function H2B(ByVal strHex As String) As String Dim i As Integer, i1 As Integer, tmpV As String Const hStr = "0123456789ABCDEF" Const bStr = "0000000100100011010001010110011110001001101010111100110111101111" tmpV = UCase(Left(strHex, 3)) '十六進(jìn)制轉(zhuǎn)二進(jìn)制 For i = 1 To Len(tmpV) i1 = InStr(hStr, Mid(tmpV, i, 1)) H2B = H2B & Mid(bStr, (i1 - 1) * 4 + 1, 4) Next H2B = H2B & Mid(strHex, 4, 2) '十六進(jìn)制轉(zhuǎn)十進(jìn)制 H2B = H2B & "0" & CStr(Val("&H" & Right(strHex, 2)))End Function

保存模塊,返回表格,我們使用用戶自定義函數(shù),獲得陰歷:
(注:上述自定義函數(shù)只能計(jì)算1899-2100年日期)
R17=GetYLDate(today())
先發(fā)現(xiàn)月字在提取的日期中的位置:
R18=FIND("月",R17)
R19=MID(R17,R18-1,2)
將右圖對應(yīng)表格的十一月改為冬月,十二月改為臘月.

好了....沒啥講的了.
順便說一句,這些公式算起來,只是大概不會錯.
但實(shí)際上,干支計(jì)算還有諸多因素要考慮進(jìn)去,所以有些結(jié)果,你會和老黃歷對應(yīng)不上,在此,我們不過是為了練習(xí)幾個函數(shù)的用法罷了,不當(dāng)之處,還請一笑了之...

本站僅提供存儲服務(wù),所有內(nèi)容均由用戶發(fā)布,如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請點(diǎn)擊舉報(bào)
打開APP,閱讀全文并永久保存 查看更多類似文章
猜你喜歡
類似文章
四柱換算
公歷日辰的干支速算心法
年、月、日、時干支計(jì)算(又準(zhǔn)又快的方法)
根據(jù)【陽歷日期】計(jì)算【陰歷日干】
怎樣排四柱(年、月、日、時柱)
不看萬年歷起日柱的簡便方法
更多類似文章 >>
生活服務(wù)
分享 收藏 導(dǎo)長圖 關(guān)注 下載文章
綁定賬號成功
后續(xù)可登錄賬號暢享VIP特權(quán)!
如果VIP功能使用有故障,
可點(diǎn)擊這里聯(lián)系客服!

聯(lián)系客服