设为首页收藏本站
开启辅助访问

Excel精英培训网

 找回密码
 注册
Excel精英培训网»论坛 › Excel 培训班 › Excel 函数中级班 › 【开贴啦】【函中 201401】练习10-日期函数（2）
数据透视表40+个常用小技巧，让你一次学会！
1234下一页
返回列表 发新帖
查看: 7133|回复: 32

[习题] 【开贴啦】【函中 201401】练习10-日期函数（2）

[复制链接]
jio1ye
发表于 2014-8-1 21:43 | 显示全部楼层 |阅读模式
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
本帖最后由 jio1ye 于 2014-8-6 20:06 编辑

我自己觉得挺有趣的日期练习，当然不是我原创，分享给大家做做哈。

开贴时间8月6日 习题10-日期函数（2）.rar (11.09 KB, 下载次数: 229)

评分

参与人数 3 +23 金币 +40 收起 理由
26759761@qq.com + 20 + 20 辛苦组长了
air05 + 3 大爱A组长，赞一个!
水上漂123 + 20 赞一个!

查看全部评分

回复

使用道具 举报

工作中最常用的函数公式，全印在一张超大的鼠标垫上
jio1ye
 楼主| 发表于 2014-8-1 21:43 | 显示全部楼层
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
本帖最后由 jio1ye 于 2014-8-6 20:56 编辑

这楼是答案汇总，大家自己慢慢看吧

1.png

2.png 第一题
人员公式公式长备注
蝶舞=TEXT(NOW()-MOD(TODAY()+2,7),"m月d日~")&TEXT(CEILING(NOW()+3,7)-3,"m月d日")答案错
E06=TEXT(INT((TODAY()+2)/7)*7-2,"m月d日")&"~"&TEXT(INT((TODAY()+2)/7)*7+4,"m月d日")76
E15=TEXT(TODAY()-MOD(TODAY()+2,7),"m月d日")&TEXT(TODAY()-MOD(TODAY()+3,-7),"~m月d日")78
D01=TEXT(NOW()-WEEKDAY(NOW(),14)+1,"m月d日")&"~"&TEXT(NOW()-WEEKDAY(NOW(),14)+7,"m月d日")82
D组长=TEXT(NOW()-WEEKDAY(NOW(),14)+1,"m月d日")&"~"&TEXT(NOW()-WEEKDAY(NOW(),14)+7,"m月d日")82
A组长=TEXT(TODAY()-WEEKDAY(TODAY(),14)+1,"m月d日")&"~"&TEXT(TODAY()-WEEKDAY(TODAY(),14)+7,"m月d日")90
E13=TEXT(TODAY()-WEEKDAY(TODAY(),14)+1,"m月d日")&"~"&TEXT(TODAY()-WEEKDAY(TODAY(),14)+7,"m月d日")90
C10=TEXT(TODAY()-WEEKDAY(TODAY(),14)+1,"m月d日")&"~"&TEXT(TODAY()-WEEKDAY(TODAY(),14)+7,"m月d日")90
A05=TEXT(TODAY()-WEEKDAY(TODAY(),14)+1,"m月d日")&"~"&TEXT(TODAY()+(7-WEEKDAY(TODAY(),14)),"m月d日")92
E08=TEXT(TODAY()-MOD(WEEKDAY(TODAY(),15),7),"m月d日")&"~"&TEXT(TODAY()+6-MOD(WEEKDAY(TODAY(),15),7),"m月d日")102
B13=IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),14),TEXT(TODAY()-5,"M月D日")&"～"&TEXT(TODAY()+1,"M月D日"))114
A09=TEXT(LOOKUP(WEEKDAY(TODAY(),14),ROW(1:7),TODAY()-ROW(1:7)+1),"M月D日")&"-"&TEXT(LOOKUP(WEEKDAY(TODAY(),14),ROW(1:7),TODAY()+7-ROW(1:7)),"M月D日")142
B05=IF(WEEKDAY(NOW(),14)=1,TEXT(NOW(),"M月D日")&"~"&TEXT(NOW()+6,"M月D日"),TEXT(NOW()+1-WEEKDAY(NOW(),14),"M月D日")&"~"&TEXT(NOW()+1-WEEKDAY(NOW(),14)+6,"M月D日"))152
E组长=TEXT(TODAY()-IF(WEEKDAY(TODAY(),2)<4,3+WEEKDAY(TODAY(),2),WEEKDAY(TODAY(),2)-4),"m月d日")&"~"&TEXT(TODAY()-IF(WEEKDAY(TODAY(),2)<4,3+WEEKDAY(TODAY(),2),WEEKDAY(TODAY(),2)-4)+6,"m月d日")182
E03=TEXT(TODAY()-IF(WEEKDAY(TODAY(),2)>=4,WEEKDAY(TODAY(),2)-4,3+WEEKDAY(TODAY(),2)),"m月d日")&"~"&TEXT(TODAY()+6-IF(WEEKDAY(TODAY(),2)>=4,WEEKDAY(TODAY(),2)-4,3+WEEKDAY(TODAY(),2)),"m月d日")184
B03=TEXT(TODAY()+IF(WEEKDAY(TODAY(),2)>=4,4-WEEKDAY(TODAY(),2),-WEEKDAY(TODAY(),2)-3),"M月DD日")&"~"&TEXT(TODAY()+IF(WEEKDAY(TODAY(),2)>=4,4-WEEKDAY(TODAY(),2),-WEEKDAY(TODAY(),2)-3)+7,"M月DD日")188答案接近正确
A13=IF(WEEKDAY(TODAY())>=5,TEXT(TODAY()-(WEEKDAY(TODAY())-5),"MM月DD日")&"~"&TEXT(TODAY()-(WEEKDAY(TODAY())-5)+6,"MM月DD日"),TEXT(TODAY()+(4-WEEKDAY(TODAY()))-6,"MM月DD日")&"~"&TEXT(TODAY()+(4-WEEKDAY(TODAY())),"MM月DD日"))212
E02=IF(WEEKDAY(TODAY())>=5,TEXT(TODAY()-(WEEKDAY(TODAY())-5),"m月d日"),TEXT(TODAY()-(WEEKDAY(TODAY())+2),"m月d日"))  & "~" &  IF(WEEKDAY(TODAY())<=4,TEXT(TODAY()+(4-WEEKDAY(TODAY())),"m月d日"),TEXT(TODAY()+(11-WEEKDAY(TODAY())),"m月d日"))225
E01=CHOOSE(WEEKDAY(TODAY(),14),TEXT(TODAY(),"M月d日")&"~"&TEXT(TODAY()+6,"M月d日"),TEXT(TODAY()-1,"M月d日")&"~"&TEXT(TODAY()+5,"M月d日"),TEXT(TODAY()-2,"M月d日")&"~"&TEXT(TODAY()+4,"M月d日"),TEXT(TODAY()-3,"M月d日")&"~"&TEXT(TODAY()+3,"M月d日"),TEXT(TODAY()-4,"M月d日")&"~"&TEXT(TODAY()+2,"M月d日"),TEXT(TODAY()-5,"M月d日")&"~"&TEXT(TODAY()+1,"M月d日"),TEXT(TODAY()-6,"M月d日")&"~"&TEXT(TODAY(),"M月d日"))374
A04=TEXT(TODAY()-1,"m月d日")&"~"&TEXT(TODAY()+5,"m月d日")答案错误
D04=TEXT(TODAY()-WEEKDAY(TODAY(),2),"mm月dd日")&"~"&TEXT(TODAY()+7-WEEKDAY(TODAY(),2),"mm月dd日")答案错误
A12=IF((TODAY()-WEEKDAY(TODAY(),2)+3)<TODAY(),(TEXT((TODAY()-WEEKDAY(TODAY(),2)+3),"m月d日"))&"~"&(TEXT((TODAY()-WEEKDAY(TODAY(),2)-3),"m月d日")),(TEXT((TODAY()-WEEKDAY(TODAY(),2)+4),"m月d日"))&"~"&(TEXT((TODAY()-WEEKDAY(TODAY(),2)+10),"m月d日")))答案错误

第二题
人员题目公式公式长备注
A04日历=IF(ROW(A1)*7-6+COLUMN(A7)-1<=DAY(EOMONTH(DATE($A$15,$C$15,1),0)),ROW(A1)*7-6+COLUMN(A7)-1,"")94
A04随机年=RANDBETWEEN(2001,2021)23
A04随机月=RANDBETWEEN(1,12)18
A04条件格式年月=AND($A$15=2014,$C$15=8)24
A04条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),2)>=636
A05日历=IF(ROW(A1)*7-7+COLUMN()>DAY(EOMONTH(DATE($A$15,$C$15,1),0)),"",ROW(A1)*7-7+COLUMN())85
A05随机年=RANDBETWEEN(2001,2021)23
A05随机月=RANDBETWEEN(1,12)18
A05条件格式年月=AND($A$15=YEAR(NOW()),$C$15=MONTH(NOW()))42
A05条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16-1))>535
A09日历=IF(COLUMN(A1)+7*(ROW(A1)-1)>DAY(DATE($A$15,$C$15+1,1)-1),"",COLUMN(A1)+7*(ROW(A1)-1))86
A09随机年=RANDBETWEEN(2001,2021)23
A09随机月=RANDBETWEEN(1,12)18
A09条件格式年月=($A$15=YEAR(TODAY()))*($C$15=MONTH(TODAY()))45
A09条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),2)>535
A12日历=IF(DAY(DATE($A$15,$C$15+1,0))>=(ROW()-ROW($A$16))*7+COLUMN()-COLUMN($A$16)+1,(ROW()-ROW($A$16))*7+COLUMN()-COLUMN($A$16)+1,"")127
A12随机年=RANDBETWEEN(2001,2021)23
A12随机月=RANDBETWEEN(1,12)18
A12条件格式年月=YEAR(TODAY())&MONTH(TODAY())=$A$15&$C$1541
A12条件格式周末-没写
A13日历=IF(COLUMN(A1)+(ROW(A1)-1)*7>DAY(EOMONTH(DATE(INDIRECT("$A$15"),INDIRECT("$C$15"),1),0)),"",TEXT(DATE(INDIRECT("$A$15"),INDIRECT("$C$15"),DAY(COLUMN(A1)+(ROW(A1)-1)*7)),"d"))174
A13随机年=RANDBETWEEN(2001,2021)23
A13随机月=RANDBETWEEN(1,12)18
A13条件格式年月=AND($A$15=YEAR(TODAY()),$C$15=MONTH(TODAY()))46
A13条件格式周末=OR(WEEKDAY(A16)=2,WEEKDAY(A16)=3)公式错误
A组长日历=IF(ROW(A1)*7+COLUMN(A1)-7>DAY(DATE($C$51,$E$51+1,)),"",ROW(A1)*7+COLUMN(A1)-7)79
A组长随机年=RANDBETWEEN(2001,2021)23
A组长随机月=RANDBETWEEN(1,12)18
A组长条件格式年月=AND(MONTH(NOW())=$C$15,YEAR(NOW())=$A$15)42
A组长条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),2)>535
B03日历=IF(ROW(A1)*7-7+COLUMN(A1)>$F$22,"",ROW(A1)*7-7+COLUMN(A1))59
B03随机年-没写
B03随机月-没写
B03条件格式年月-写错了
B03条件格式周末=OR(WEEKDAY(DATE($A$23,$C$23,A24),2)=6,WEEKDAY(DATE($A$23,$C$23,A24),2)=7)74
B05日历=IF(COLUMN(A1)+(ROW($A1)-1)*7>DAY(DATE($A$15,$C$15+1,1)-1),"",COLUMN(A1)+(ROW($A1)-1)*7)88
B05随机年=INT(RAND()*20)+200120
B05随机月=INT(RAND()*11)+117
B05条件格式年月=AND($A$15=YEAR(NOW()),$C$15=MONTH(NOW()))42
B05条件格式周末=AND(WEEKDAY(DATE($A$15,$C$15,A16),11)>5)41
B13日历=IF(ROW(A1)*7+COLUMN(A1)-7>30,"",ROW(A1)*7+COLUMN(A1)-7)56
B13随机年=RANDBETWEEN(2001,2021)23
B13随机月=RANDBETWEEN(1,12)18
B13条件格式年月-没写
B13条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),1)>=636
C10日历=IF(DATE($A$15,$C$15,ROW(A1)*7+COLUMN()-7)>DATE($A$15,$C$15+1,0),"",DATE($A$15,$C$15,ROW(A1)*7+COLUMN()-7))107
C10随机年=RANDBETWEEN(2001,2021)23
C10随机月=RANDBETWEEN(1,12)18
C10条件格式年月=AND($A$15=YEAR(TODAY()),$C$15=MONTH(TODAY()))46
C10条件格式周末=IFERROR(WEEKDAY(A16,2)>5,)27
D01日历=IF((ROW()-16)*7+COLUMN(A:G)>DAY(EOMONTH(DATE($A$15,$C$15,1),0)),"",(ROW()-16)*7+COLUMN(A:A))93
D01随机年=RANDBETWEEN(2001,2021)23
D01随机月=RANDBETWEEN(1,12)18
D01条件格式年月=$A$15=YEAR(NOW())/=$C$15=MONTH(NOW())同时满足年月才可以
D01条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),1)=735
D04日历=IF(COLUMN(A1)+ROW(A1)*7-7<=DAY(DATE($A$15,$C$15+1,0)),COLUMN(A1)+ROW(A1)*7-7,"")81
D04随机年=RANDBETWEEN(2001,2021)23
D04随机月=RANDBETWEEN(1,12)18
D04条件格式年月-没写
D04条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),2)>535
D组长日历=IF(ROW()*7+COLUMN()-112>DAY(EOMONTH($A$15&$C$15,0)),"",ROW()*7+COLUMN()-112)77
D组长随机年=YEAR(RANDBETWEEN(36892,44561))31
D组长随机月=MONTH(RANDBETWEEN(1,366))26
D组长条件格式年月=$A$15=YEAR(NOW())/=$C$15=MONTH(NOW())同时满足年月才可以
D组长条件格式周末=WEEKDAY(A16,2)>5答案错误
E02日历=IF(MONTH(DATE($A$15,$C$15,COLUMN()+(ROW()-16)*7))=$C$15,DAY(DATE($A$15,$C$15,COLUMN()+(ROW()-16)*7)),"")105
E02随机年=RANDBETWEEN(2001,2021)23
E02随机月=RANDBETWEEN(1,12)18
E02条件格式年月=AND($A$15=YEAR(TODAY()),$C$15=MONTH(TODAY()))46
E02条件格式周末=OR(WEEKDAY(DATE($A$15,$C$15,A16))=1,WEEKDAY(DATE($A$15,$C$15,A16))=7)70
E03日历=IF(MONTH(DATE($A$15,$C$15,ROW(A1)*7-7+COLUMN(A1)))>$C$15,"",DAY(DATE($A$15,$C$15,ROW(A1)*7-7+COLUMN(A1))))107
E03随机年没有写
E03随机月没有写
E03条件格式年月=$A$15=YEAR(NOW())/=$C$15=MONTH(NOW())同时满足年月才可以
E03条件格式周末=OR(WEEKDAY(DATE($A$15,$C$15,A16),2)=7,WEEKDAY(DATE($A$15,$C$15,A16),2)=6)74
E06日历=IF(DATE($A$15,$C$15,0)+(ROW()-16)*7+COLUMN()>DATE($A$15,$C$15+1,0),"",DATE($A$15,$C$15,0)+(ROW()-16)*7+COLUMN())113
E06随机年=RANDBETWEEN(2001,2021)23
E06随机月=RANDBETWEEN(1,12)18
E06条件格式年月=AND(YEAR(TODAY())=$A$15,MONTH(TODAY())=$C$15)46
E06条件格式周末=OR(TEXT(A16,"aaa")="六",TEXT(A16,"aaa")="日")44
E08日历=TEXT(A15&B15&C15&D15&7*ROW(1:5)-{6,5,4,3,2,1,0}&"日","d;;;")60用的区域数组
E08随机年=RANDBETWEEN(2001,2021)23
E08随机月=INT(RAND()*12+1)17
E08条件格式年=AND($A$15=YEAR(NOW()),$C$15=MONTH(NOW()))42
E08条件格式月=AND($A$15=YEAR(NOW()),$C$15=MONTH(NOW()))42
E08条件格式周末=OR(MOD($A$15&$B$15&$C$15&$D$15&A$16&"日",7)=ROW($1:$2))55
E13日历=IF($C$15=MONTH(DATE($A$15,$C$15,ROW(A1)*7+COLUMN(A1)-7)),TEXT(DATE($A$15,$C$15,ROW(A1)*7+COLUMN(A1)-7),"d"),"")112
E13随机年没有写
E13随机月没有写
E13条件格式年月=AND($A$15=YEAR(NOW()),$C$15=MONTH(NOW()))42
E13条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),2)>535
E15日历=IF(MONTH(DATE($A$15,$C$15,COLUMN(A1)+(ROW(A1)-1)*7))<>$C$15,"",DAY(DATE($A$15,$C$15,COLUMN(A1)+(ROW(A1)-1)*7)))112
E15随机年=RANDBETWEEN(2001,2021)23
E15随机月=RANDBETWEEN(1,12)18
E15条件格式年月-马马虎虎
E15条件格式周末-马马虎虎
E组长日历=IF(ISERR(-($A$15&"-"&$C$15&"-"&COLUMN(A1)+(ROW(A1)-1)*7)),"",COLUMN(A1)+(ROW(A1)-1)*7)87
E组长随机年=RANDBETWEEN(2001,2021)23
E组长随机月=RANDBETWEEN(1,12)18
E组长条件格式年月=$A$15=YEAR(NOW())/=$C$15=MONTH(NOW())同时满足年月才可以
E组长条件格式周末=WEEKDAY(DATE($A$15,$C$15,A16),2)>535
蝶舞日历=TEXT($A$15&-$C$15&-ROW(A1)*7-COLUMN(A1)+7,"d;;;")50
蝶舞随机年=RANDBETWEEN(2001,2021)23
蝶舞随机月=RANDBETWEEN(1,12)18
蝶舞条件格式年月=$A$15&-$C$15=TEXT(NOW(),"e-m")31
蝶舞条件格式周末=MOD($A$15&-$C$15&-A16,7)<227

评分

参与人数 1 +18 收起 理由
xuyouxm + 18 牛B

查看全部评分

回复

使用道具 举报

发表于 2014-8-1 22:56 | 显示全部楼层
工作中最常用的Excel函数公式，全印在一张鼠标垫上
本帖最后由 滴答滴 于 2014-8-2 18:51 编辑

练习10

习题10-E08-滴答滴.zip

12.99 KB, 下载次数: 13

点评

jio1ye
Good~  发表于 2014-8-3 11:09
jio1ye
Halou~做到结果不对哦，我这边看到第一题返回的结果是#NUM。第二题如果年月是14年8月，单元格不会变为黄色  发表于 2014-8-2 15:47

评分

参与人数 2 +19 收起 理由
as0810114 + 10 神马都是浮云
jio1ye + 9

查看全部评分

回复

使用道具 举报

hsl215
发表于 2014-8-1 23:06 | 显示全部楼层
本帖最后由 hsl215 于 2014-8-6 19:30 编辑

习题10-E13-hsl215.rar (12.92 KB, 下载次数: 2)

点评

jio1ye
条件格式思路很好，充分运用Weekday的功能，但是年月用的数据有效性，我希望的是随机函数  发表于 2014-8-3 11:19

评分

参与人数 1 +7 收起 理由
jio1ye + 7 很给力!

查看全部评分

回复

使用道具 举报

发表于 2014-8-1 23:18 | 显示全部楼层
  1. =TEXT(A5-IF(WEEKDAY(A5,2)<4,3+WEEKDAY(A5,2),WEEKDAY(A5,2)-4),"m月d日")&"~"&TEXT(A5-IF(WEEKDAY(A5,2)<4,3+WEEKDAY(A5,2),WEEKDAY(A5,2)-4)+6,"m月d日")
复制代码
  1. =RANDBETWEEN(2001,2012)
复制代码
  1. =RANDBETWEEN(1,12)
复制代码
  1. =IF(ISERR(-($$A$$15&"-"&$$C$$15&"-"&COLUMN(A1)+(ROW(A1)-1)*7)),"",COLUMN(A1)+(ROW(A1)-1)*7)
复制代码
  1. =WEEKDAY(DATE($$A$$15,$$C$$15,A16),2)>5
复制代码

点评

jio1ye
第一题我希望是Today（）对应，答案思路是对的 第二题年月数据条件没设 你的单元格用两个$$Excel能够识别出来吗？  发表于 2014-8-3 11:31

评分

参与人数 1 +5 收起 理由
jio1ye + 5 赞一个!

查看全部评分

回复

使用道具 举报

妹纸
发表于 2014-8-1 23:44 | 显示全部楼层
{:091:}也不知道对不对  组长给我改吧，第一个公式是硬凑的。

习题10-A04-妹纸.zip

12.42 KB, 下载次数: 2

点评

jio1ye
第一题确实是不对的。用Weekday函数思考一下吧~ 第二题很棒  发表于 2014-8-3 12:12

评分

参与人数 1 +9 收起 理由
jio1ye + 9

查看全部评分

回复

使用道具 举报

箫风
发表于 2014-8-2 15:10 | 显示全部楼层
谢谢老师！

练习10-B05-箫风.rar

12.79 KB, 下载次数: 3

点评

jio1ye
Good~  发表于 2014-8-3 12:24

评分

参与人数 1 +9 收起 理由
jio1ye + 9 赞一个!

查看全部评分

回复

使用道具 举报

发表于 2014-8-2 16:05 | 显示全部楼层
1、=TEXT(NOW()-MOD(TODAY()+2,7),"m月d日~")&TEXT(CEILING(NOW()+3,7)-3,"m月d日")
2、选中A15,C15，条件格式公式=$A$15&-$C$15=TEXT(NOW(),"e-m")
3、日历=TEXT($A$15&-$C$15&-ROW(A1)*7-COLUMN(A1)+7,"d;;;")
4、周末条件格式=MOD($A$15&-$C$15&-A16,7)<2

点评

jio1ye
大神，发现你第一题公式不对。。。T_T  发表于 2014-8-6 20:54
jio1ye
给力都对了哦，思路好好。可是你貌似不是班级成员哦。  发表于 2014-8-3 12:31

评分

参与人数 2 +21 收起 理由
jio1ye + 9 赞一个!
huangcaiguang + 12 赞一个!

查看全部评分

回复

使用道具 举报

lmze2000
发表于 2014-8-2 16:58 | 显示全部楼层
习题10-日期函数 D01-lmze2000.zip (13.44 KB, 下载次数: 4)

点评

jio1ye
第一题很棒 第二题： 1）题目希望是今年今月才变黄色 2）日历周六日分别用了两个公式，其实可以考虑用一个啦  发表于 2014-8-3 12:40

评分

参与人数 1 +7 收起 理由
jio1ye + 7 赞一个!

查看全部评分

回复

使用道具 举报

发表于 2014-8-2 20:03 | 显示全部楼层

习题10-日期函数（2）-木偶fenzi.zip

12.48 KB, 下载次数: 3

点评

jio1ye
第一题，对了很给力 第二题，周六日单元格条件格式公式错误  发表于 2014-8-3 12:53

评分

参与人数 1 +7 收起 理由
jio1ye + 7 赞一个!

查看全部评分

回复

使用道具 举报

下一页 »
1234下一页
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-4-29 16:53 , Processed in 0.371045 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表