1樓:
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
'計算到初始時間2023年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
doif (nonglidata(m) < 4095) then
k = 11
else
k = 12
end if
n = k
doif (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
將上面這段**儲存在模組中,就可以用公式
=nongli(a1)
呼叫了。
在excel中如何設定工作表的日期
在工作表中,選擇要填寫日期的單元格,如下圖所示 選中單元格後,右擊滑鼠右鍵,在彈出的右鍵選單選項中,選擇 設定單元格格式 在彈出的 單元格格式 視窗中,依序選擇 日期 2001年3月7日 單元格格式設定好後,再次將該單元格選中,然後選擇函式命令,在彈出的 插入函式 視窗,將函式的類別選擇為 日期函式...
在EXCEL中如何能讓數值為五入
如果是隻要大於某個整數就向上圓整,就不能叫 五入 了,這個是個概念問題,呵呵。這可以直接用函式roundup 如roundup 4.2,0 5 但如果是必須要超出0.1才向上圓整的話,那就可以先把數字減去0.1來實現,也就是公式改為 roundup 4.2 0.1,0 5 這裡公式中是直接給出的數字...
excel中如何統計日期裡面出現的人名次數
這需要看電腦配置了,配置低的話,頂多處理十萬以內單元格。配置高也頂多三十萬個單元格 countifs g g,某某 o o,2014 4 2 countifs g g,某某 o o,2014 4 2 後面的日期是固定的,如果換成動態的就好了 如何統計excel中名字出現的次數 材料 工具 excel...