【201502函初】必交练习二(已开贴)

xk465592765

【201502函初】必交练习二.rar (11.23 KB, 下载次数: 61)

2015-11-20 22:09 上传

点击文件名下载附件
注意：
1、本次作业按代码方式提交公式即可，注明学号以及ID；
2、不接受附件上传， 回帖方式不正确不评分或者扣分；
3、回复不要重复占楼，修改请在原楼层，本帖已设置回复仅作者可见；
4、非【201502函初】学员勿跟帖，或者扣10经验10金币！
5、练习截止时间：11月23日18:00前

天天EP

C05 天天EP，上交练习二

1. 1、=TEXT(INDEX(SMALL(IF(ISODD(ROW($1:$1000000))*((MOD(ROW($1:$1000000),7)=2)+(MOD(ROW($1:$1000000),7)=4)+(MOD(ROW($1:$1000000),7)=6)),ROW($1:$1000000)),ROW(INDIRECT("1:"&COUNT(0/(ISODD(ROW($1:$1000000))*((MOD(ROW($1:$1000000),7)=2)+(MOD(ROW($1:$1000000),7)=4)+(MOD(ROW($1:$1000000),7)=6))))))),RANDBETWEEN(1,COUNT(0/(ISODD(ROW($1:$1000000))*((MOD(ROW($1:$1000000),7)=2)+(MOD(ROW($1:$1000000),7)=4)+(MOD(ROW($1:$1000000),7)=6)))))),{"e-m-d","周aaa"})      理论上可行的
   
2. 2、=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$-130000]e")-4,12)+1,1)&"年 "&TEXT(A13,"[$-130000][DBNum1]m月d日")‘源于百度
   
3. 3、=IF(AND(MONTH($H$25-7+COLUMN(A1)+(ROW(A1)-1)*7)=MONTH($H$25),MOD(DAY($H$25-7+COLUMN(A1)+(ROW(A1)-1)*7),3)<>0),DAY($H$25-7+COLUMN(A1)+(ROW(A1)-1)*7),"")

复制代码

vicky4896

姓名：C03:vicky4896

第一题：

1. =CHOOSE(COLUMN(A1),DATE(RANDBETWEEN(2000,2015),RANDBETWEEN(1,6)*2-1,RANDBETWEEN(1,15)*2-1),TEXT($$A3,"aaaa"))

复制代码

第二题：

1. =MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$$-130000]e")-4,12)+1,1)&"年"&" "&TEXT(A13,"[$$-130000][DBNum1]m月d日")

复制代码

第三题：

1. =IF(ROW(A1)=1,CHOOSE(ROW(A1)*COLUMN(A1),"一","二","三","四","五","六","日"),IF(MOD(ROW(A1)*7-20+COLUMN(A1),3),TEXT(0&ROW(A1)*7-20+COLUMN(A1),"[<30];;;")," "))

复制代码

回答完毕！

suzhouy2003

201502函初：A10
第一题 A3单元格 右拉下拉

1. =IF(A$2="日期",DATE(RANDBETWEEN(1900,2015),RANDBETWEEN(1,12),2*RANDBETWEEN(1,16)-1),TEXT($A3,"[$-804]aaaa;@"))

复制代码

第二题 B13单元格 下拉

1. =MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$-130000]e")-4,12)+1,1)&"年"&TEXT(A13,"[$-130000][DBNum1]m月d日")

复制代码

第三题 H27 右拉下拉

1. =IF(7*ROW(A1)+COLUMN(A1)-20<-6,TEXT(COLUMN(A$1)+1,"[$-804]aaa;@"),IF(7*ROW(A1)+COLUMN(A1)-20<1,"",IF(MOD(7*ROW(A1)+COLUMN(A1)-20,3)=0,"",IF(7*ROW(A1)+COLUMN(A1)-20>29,"",7*ROW(A1)+COLUMN(A1)-20))))

复制代码

rlzcjrrmy

A03 rlzcjrrmy

1. =IF(COLUMN()=1,DATE(YEAR(NOW()),MONTH(NOW()),RANDBETWEEN(1,15)*2-1),TEXT(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),RANDBETWEEN(1,15)*2-1),2),"g/通用格式"))

复制代码

1. <div class="blockcode"><blockquote>=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$-130000]e")-4,12)+1,1)&"年"&"  "&TEXT(A13,"[$-130000][DBNum1]m月")&TEXT(A13,IF(--TEXT(A13,"[$-130000]d")=10,"初十",IF(--TEXT(A13,"[$-130000]d")>10,"[$-130000][DBNum1]d","初[$-130000][DBNum1]d")))

复制代码

1. =IF(MOD(COLUMN(A1)+ROW(A1)*7-10,3),IF(OR((COLUMN(A1)+ROW(A1)*7-10)<1,(COLUMN(A1)+ROW(A1)*7-10)>31),"",COLUMN(A1)+ROW(A1)*7-10),"")

复制代码

liangj

B02:liangj
第一题：

1. =TEXT(RANDBETWEEN(1990,2020)&"-"&RANDBETWEEN(1,12)&"-"&TRUNC(RANDBETWEEN(1,15))*2+1,"yyyy-mm-dd aaaa")

复制代码

第二题：

1. =MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-1984,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$-130000]e")-1984,12)+1,1)&"年"&TEXT(A13," [$-130000][DBNum1]m月d日")

复制代码

第三题：

1. =IF(MOD((7*ROW(A1)-12+COLUMN(A1)-1),3)=0,"",TEXT(7*ROW(A1)-12+COLUMN(A1)-1,"[<0] ;[<30]0;"))

复制代码

第三题放到第一个空的单元格上，谢谢！

绵花糖

A组长：绵花糖

1. 第一题：
   
2. 前言：日期是一种特殊的数字，EXCEL能识别的最小日期是1900/1/1,对应的数字是1，最大日期是9999/12/31对应的数字是2958466，本题求奇数日期就要是求对应的数字为奇所以在=2*RANDBETWEEN(1,1479233.5)-1这个范围随机即可，第一题公式为
   
3. =CHOOSE(COLUMN(A1),2*RANDBETWEEN(1,1479233.5)-1,WEEKDAY(2*RANDBETWEEN(1,1479233.5)-1,2))右拉下拉
   
4. 第二题=MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$-130000]e")-4,12)+1,1)&"年"&TEXT(A13,"[$-130000][DBNum1]m月")&TEXT(A13,IF(--TEXT(A13,"[$-130000]d")=10,"初十",IF(--TEXT(A13,"[$-130000]d")>10,"[$-130000][DBNum1]d","初[$-130000][DBNum1]d")))
   
5. 第三题
   
6. =IF(MOD(IF(MONTH($H$25-WEEKDAY($H$25,3)+COLUMN(A:A)-1+ROW(1:1)*7-7)=MONTH($H$25),DAY($H$25-WEEKDAY($H$25,3)+COLUMN(A:A)-1+ROW(1:1)*7-7)),3),IF(MONTH($H$25-WEEKDAY($H$25,3)+COLUMN(A:A)-1+ROW(1:1)*7-7)=MONTH($H$25),DAY($H$25-WEEKDAY($H$25,3)+COLUMN(A:A)-1+ROW(1:1)*7-7)),"")

复制代码

qiaoqiaodl_001

201502函初A07

1. =ODD(RANDBETWEEN(1,42330))

复制代码

1. =TEXT(A3,"aaaa")

复制代码

1. =MID(" 甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-4,10)+2,1)&MID("子丑寅卯辰巳午未申酉 戌亥",MOD(TEXT(A13,"[$-130000]e")-4,12)+1,1)&"年"&TEXT(A13," [$-130000][DBNum1]m月d日")

复制代码

1. =IF(7*ROW(A1)+COLUMN(A1)-20<-5,TEXT(2015-9-31+COLUMN(A1),"aaa"),IF(7*ROW(A1)+COLUMN(A1)-20<1,"",IF(7*ROW(A1)+COLUMN(A1)-20>29,"",IF(MOD(7*ROW(A1)+COLUMN(A1)-20,3)=0,"",7*ROW(A1)+COLUMN(A1)-20))))

复制代码

H明悠

C08：H明悠
第一题

1. =IF(COLUMN()=1,RANDBETWEEN(1,10000)*2+1,TEXT(RANDBETWEEN(1,100)*2+1,"aaaa"))

复制代码

第二题

1. 
   

复制代码

第三题

1. =IF(OR((7*ROW(A1)+COLUMN(A1)-13)<0,(7*ROW(A1)+COLUMN(A1)-13)>30,MOD((7*ROW(A1)+COLUMN(A1)-13),3)=0),"",(7*ROW(A1)+COLUMN(A1)-13))

复制代码

红旗雪

B13:红旗雪
第一题

1. =IF(COLUMN(A1)=1,DATE(RANDBETWEEN(2000,2020),RANDBETWEEN(1,12),RANDBETWEEN(1,10)*2-1),INDEX("星期"&{"一","三","五"},RANDBETWEEN(1,3)))

复制代码

第二题

1. =MID("甲乙丙丁戊己庚辛壬癸",MOD(TEXT(A13,"[$-130000]e")-4,10)+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(TEXT(A13,"[$-130000]e")-4,12)+1,1)&"年"&" "&TEXT(TEXT(A13,"[$-130000]M-D"),"[DBNum1]M月D日")

复制代码

第三题

1. =IF(ROW(A1)=1,MID("一二三四五六日",COLUMN(A1),1),IF(OR(MONTH($H$25)<>MONTH(DATE(YEAR($H$25),MONTH($H$25),1)-(WEEKDAY(DATE(YEAR($H$25),MONTH($H$25),1),2))+ROW(A1)*7-14+COLUMN(A1)),MOD(DAY(DATE(YEAR($H$25),MONTH($H$25),1)-(WEEKDAY(DATE(YEAR($H$25),MONTH($H$25),1))-1)+ROW(A1)*7-14+COLUMN(A1)),3)=0),"",DAY(DATE(YEAR($H$25),MONTH($H$25),1)-(WEEKDAY(DATE(YEAR($H$25),MONTH($H$25),1))-1)+ROW(A1)*7-14+COLUMN(A1))))

复制代码