1樓：匿名使用者
=lookup(--text(c3,"mdd"),)
excel**星座計算公式（輸入生日計算出星座）
2樓：諾諾百科
,&"座")。比如下面公式
=if(isna(vlookup(b5,sheet1!$b$2:$e$179,2,false)),"",vlookup(b5,sheet1!
$b$2:$e$179,2,false))
x=vlookup(b5,sheet1!$b$2:$e$179,2,false) -->找到一樣條件的，取它的數值。
整個就是找的到取數值，找不到，用用空白代替na，公式isna和iserror類似，不過iserror出錯的時候，也會顯示空白，用isna，出錯就顯示出錯，na才會顯示空白。
a1引用樣式
預設情況下，excel 使用 a1 引用樣式，此樣式引用字母標識列（從 a 到 iv，共 256 列，備註：版本不同最大列數也不同），引用數字標識行（從 1 到 65,536）。這些字母和數字稱為行號和列標。
若要引用某個單元格，請輸入列標和行號。例如，b2 引用列 b 和行 2 交叉處的單元格。
3樓：匿名使用者
上面那個函式有一丟丟漏洞哈，摩羯座不能被完全跑出來。
=ifna((if(【生日單元格】="","",lookup(--text(【生日單元格】,"m/d"),--,&"座"))),"摩羯座")
可以用這個試試！
4樓：匿名使用者
舉例使用身份證號碼來算吧
在a1中輸入身份證號碼，在其他單元格中執行公式=lookup(--text(mid(a1,len(a1)*62%,4),"1900-00-00"),)
注：星座起始日可能不太一致，自己進行修改
5樓：菸灰
如果a1單元格寫入的是excel認可的日期格式,
則b1單元格寫入公式
=if(a1="","",lookup(--text(a1,"m/d"),--,&"座"))
6樓：匿名使用者
如果a1單元格寫入18位身份證號
則b1單元格寫入公式
=if(a1="","",lookup(--text(mid(a1,11,4),"0-00"),--,&"座"))
如果a1單元格寫入的是excel認可的日期格式,則b1單元格寫入公式
=if(a1="","",lookup(--text(a1,"m/d"),--,&"座"))
excel 中根據生日計算星座
7樓：匿名使用者
你不感覺特別麻煩麼？
12個月12個星座。每月19-23號是分界線。
分界線附近的當然要用軟體查的。
有時候一天能分出2個太陽星座的。
如何在excel表中將陰曆生日批量轉換成陽曆？ 5
8樓：期待你的愛吧
將下面面這段**儲存在模組中，就可以用公式 =nongli(a1) 呼叫了。
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
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
希望能幫助你
如何在jsp頁面中使用freemarker標籤和struts2標籤
struts cleanup org.apache.struts2.dispatcher.actioncontextcleanup sitemesh freemarker org.apache.struts2.sitemesh.freemarkerpagefilter default encodin...
如何在中使用電子簽名
教你在word中製作電子簽名 1 首先，我們手動在一張白紙上寫下我們的簽名，用手機拍下我們的簽名，然後傳送到電腦上。2 接下來，我們開啟計算機上的word軟體，並輸入需要新增到簽名服務中的word文件。3 輸入文件後，我們在軟體選單欄中找到了 插入 選單，並繼續在 插入 選單下找到 插圖 選項卡。4...
在excel中,使用條件格式將 採購數量 列中數量大於100的單元格中字型顏色設定為紅色，粗體顯示。怎麼做啊
篩選 然後找出大於100的 設定 紅色 粗體 在excel中,使用條件格式將 採購數量 列中數量大於100的單元格中字型顏色設定為紅色，加粗顯示。怎麼做啊？選中列 條件格式 突出顯示單元格規則 大於 填寫100 設定為 自定義格式 選定格式 確定即可。excel怎麼將符合條件的單元格變成紅色 可以用...