伊莉討論區

標題: 【已解決】Excel關於如何把同一格的日期和非日期資料自動徘序？ [打印本頁]
作者: wind571    時間: 2020-2-21 11:41 PM     標題: 【已解決】Excel關於如何把同一格的日期和非日期資料自動徘序？

本帖最後由 wind571 於 2020-2-29 11:12 PM 編輯



請教大家，我想弄一個表格如上，工作天是星期一到六，整個月的。
日期第一格是自己輸入的，然後斷行加上農曆日期，旁邊跟著自動抓星期的公式。
（↑儲存格格式是通用型）

再沒加上那農曆時，我整個月分都可以自動套公式出來，加上農曆後一切就毀了……
未加上農曆的A2我是用這公式「=A2+IF(WEEKDAY(A2)=7,2,1)」，
後來加上農曆想用「=LEFT(A2,5)」這個拆出日期數字加減，但遲遲無法成功……
網路上找來找去也聯想不出其他應用方式…OTL

想請教有沒有 沒那麼複雜、又可以解決的方式？
謝謝><

作者: davidshu    時間: 2020-2-29 10:52 AM

日曆製作可以詳細參照以下網站,
kknews.cc/zh-tw/education/nppp9a8.html

簡述如下,
自己動手用EXCEL製作日曆
網上有現成的日曆模板，一般是整年的，帶農曆、節氣、節日
我們今天做一個簡易的日曆，填寫年份、月份，就生成帶有農曆和星期的單月日曆

這個日曆可以查看從1900年1月1日開始的每個月的日曆。

做這個日曆的第一步是要先做一個公曆日曆來做輔助表
這是一個7*6的矩陣，只需要一個數組公式填充即可

{=IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))MONTH(DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),,DATE(YEAR(B2),MONTH(B2),1)-(WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

選中7*6這42個單元格，填入公式，按下Ctrl+Shift+Enter三個鍵，生成日曆。
這個公式引用的是單元格B2的內容，只要改變B2中的年月日，就會自動生成這個月的日曆。
帶有農曆的日曆，需要84個單元格：
1、3、5、7、9、11行用來顯示公曆，直接引用輔助表里的公曆日曆；
2、4、6、8、10、12行用來顯示農曆，用公曆值計算農曆，公式：=IFERROR(TEXT(E5,IF(--TEXT(E5,[$-130000]d)=10,初十,IF(--TEXT(E5,[$-130000]d)10,[$-130000][DBNum1]d,初[$-130000][DBNum1]d))),)。
其實這個函數就是設置顯示格式的函數，從excel2007開始，就已經支持農曆轉換，就是用[$-130000]d來轉換，其他的IF語句都是為了修飾「初一」到「初十」，和「十一」到「三十」的。
農曆年是用這個公式：=MID(甲乙丙丁戊己庚辛壬癸,MOD(TEXT(B3,[$-130000]e)-4,10)+1,1)MID(子丑寅卯辰巳午未申酉戌亥,MOD(TEXT(B3,[$-130000]e)-4,12)+1,1)年
農曆月用這個公式：=TEXT(B3,[$-130000][DBNum1]m月)
這裡引用的B3單元格的內容不顯示：=B2-C2
單元格B2是要顯示的年份
單元格C2是要顯示的月份
只要修改這兩個單元格的內容，就會自動生成含有農曆的日曆了。
作者: davidshu    時間: 2020-2-29 10:54 AM

另一方式可以透過VBA的程式來實作,可以參考看看

建立並開啟Excel  =>  開發人員  =>  Visual Basic  =>  插入 模組   =>  輸入以下資料 :
Excel公式 =NongLi(A1)
產生的結果範例: 2017/12/14 => 農曆丁酉年(雞)十一月二八日
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

'獲取當前系統時間
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) = "豬"
'農曆日期名
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) = "三十"
'農曆月份名
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) = "臘"
'國曆每月前面的天數
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
'農曆數據
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
'生成當前國曆年、月、日 ==> 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
'計算到初始時間1921年2月8日的天數：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
'計算農曆天干、地支、月、日
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?二進位的值
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
'生成農曆天干、地支、生肖 ==> NongliStr
NongliStr = "農曆" & TianGan(((curYear - 4) Mod 60) Mod 10) & DiZhi(((curYear - 4) Mod 60) Mod 12) & "年"
NongliStr = NongliStr & "(" & ShuXiang(((curYear - 4) Mod 60) Mod 12) & ")"
'生成農曆月、日 ==> 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



歡迎光臨 伊莉討論區 (http://a401.file-static.com/) Powered by Discuz!