怎么在excel表格中找到天干

1.在电子表格中把公历日期转换成农历与天干地支

这个需要VBA了.按ALT+F11 再 插入 模块把下面的代码制复制到模块中，然后 在B1中输入：=calendar(A1)假设日期在A1Option ExplicitDim WeekName(7), MonthAdd(11), NongliData(120), TianGan(9), DiZhi(11), ShuXiang(11), DayName(30), MonName(12)Dim curTime, curYear, curMonth, curDay, curWeekdayDim GongliStr, WeekdayStr, NongliStr, NongliDayStrDim i, m, n, k, isEnd, bit, TheDate, num1, num2Dim year, month, day, grid, grid_dateFunction calendar(grid)grid_date = gridyear = Mid(grid_date, 1, 4)num1 = InStr(grid_date, "-")num2 = InStr(6, grid_date, "-")If (num2 - num1) = 3 Thenmonth = Mid(grid_date, 6, 2)day = Mid(grid_date, 9, 2)Elsemonth = Mid(grid_date, 6, 1)day = Mid(grid_date, 8, 2)End If'星期名WeekName(0) = " * "WeekName(1) = "星期日"WeekName(2) = "星期一"WeekName(3) = "星期二"WeekName(4) = "星期三"WeekName(5) = "星期四"WeekName(6) = "星期五"WeekName(7) = "星期六"'天干名称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) = 0MonthAdd(1) = 31MonthAdd(2) = 59MonthAdd(3) = 90MonthAdd(4) = 120MonthAdd(5) = 151MonthAdd(6) = 181MonthAdd(7) = 212MonthAdd(8) = 243MonthAdd(9) = 273MonthAdd(10) = 304MonthAdd(11) = 334'农历数据NongliData(0) = 2635NongliData(1) = NongliData(2) = 1701NongliData(3) = 1748NongliData(4) = NongliData(5) = 694NongliData(6) = 2391NongliData(7) = NongliData(8) = 1175NongliData(9) = NongliData(10) = 3402NongliData(11) = 3749NongliData(12) = NongliData(13) = 1453NongliData(14) = 694NongliData(15) = NongliData(16) = 2350NongliData(17) = NongliData(18) = 3221NongliData(19) = 3402NongliData(20) = NongliData(21) = 2901NongliData(22) = 1386NongliData(23) = NongliData(24) = 605NongliData(25) = 2349NongliData(26) = NongliData(27) = 2709NongliData(28) = NongliData(29) = 1738NongliData(30) = 2901NongliData(31) = NongliData(32) = 1242NongliData(33) = 2651NongliData(34) = NongliData(35) = 1323NongliData(36) = NongliData(37) = 3733NongliData(38) = 1706NongliData(39) = NongliData(40) = 2741NongliData(41) = 1206NongliData(42) = NongliData(43) = 2647NongliData(44) = 1318NongliData(45) = NongliData(46) = 3477NongliData(47) = NongliData(48) = 1386NongliData(49) = 2413NongliData(50) = NongliData(51) = 1197NongliData(52) = 2637NongliData(53) = NongliData(54) = 3365NongliData(55) = NongliData(56) = 2900NongliData(57) = 2922NongliData(58) = NongliData(59) = 2395NongliData(60) = 1179NongliData(61) = NongliData(62) = 2635NongliData(63) = NongliData(64) = 1701NongliData(65) = 1748NongliData(66) = NongliData(67) = 2742NongliData(68) = 2391NongliData(69) = NongliData(70) = 1175NongliData(71) = 1611NongliData(72) = NongliData(73) = 37。

2.如何用excel将12位的数字转化为60甲子天干地支

转换成一个天干地支可以这样：

=LOOKUP(MOD(A1-1,10),{0,1,2,3,4,5,6,7,8,9}，{"甲"，"乙"，"丙"，"丁"，"戊"，"己"，"庚"，"辛"，"壬"，"癸"}）&LOOKUP(MOD(A1-1,12),{0,1,2,3,4,5,6,7,8,9,10,11}，{"子"，"丑"，"寅"，"卯"，"辰"，"巳"，"午"，"未"，"申"，"酉"，"戌"，"亥"}）

如果要达到你的要求，那公式就太长了。

建议一：分两步，首先将6组数字提取到其他单元格，再针对这些单元格进行转换。

建议二：用宏来做。