Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
12
返回列表 发新帖
楼主: ws278106483

[习题] 【函中20130301】第十一讲A组作业上交帖

[复制链接]
发表于 2013-7-18 21:43 | 显示全部楼层
【函中201301班】-A23-石岗
第一题:=TEXT(TEXT(LEFT(B303,FIND(" ",B303)),"yyyy-mm-dd"),"yyyy年mm月dd日;@")&TEXT(SUBSTITUTE(MID(B303,FIND(" ",B303)+1,20)," ",""),"上午/下午h时mm分ss秒;@");
第二题:=VLOOKUP(B313,{"JAN",1;"FEB",2;"MAR",3;"APR",4;"MAY",5;"JUN",6;"JUL",7;"AUG",8;"SEP",9;"OCT",10;"NOV",11;"DEC",12},2,FALSE);
第三题:=TEXT(SUBSTITUTE(RIGHT(SUBSTITUTE(C322,"/","    "),4)&"-"&MID(SUBSTITUTE(C322,"/","    "),4,7)&"-"&LEFT(SUBSTITUTE(C322,"/","    "),2)," ",""),"yyyy-m-d");
第四题:=IF(WEEKDAY(TEXT(C337,"0"),2)<=3,C337-WEEKDAY(TEXT(C337,"0"),2),IF(WEEKDAY(TEXT(C337,"0"),2)<7,C337+IF(WEEKDAY(TEXT(C337,"0"),2)<=3,WEEKDAY(TEXT(C337,"0"),2)*7,IF(WEEKDAY(TEXT(C337,"0"),2)<7,7-WEEKDAY(TEXT(C337,"0"),2),0)),C337));
第五题:=MONTH(IF(LEN(-SUBSTITUTE(C346,".","-"))=6,SUBSTITUTE(C346,".","-"),TEXT(C346,"0!-00")));
第六题:=TEXT(IF(WEEKDAY(--($C$360&"-1-1")+$D$360*7,2)=1,--($C$360&"-1-1")+($D$360-1)*7,--($C$360&"-1-1")+($D$360-1)*7-WEEKDAY(--($C$360&"-1-1")+$D$360*7,2)+ROW(A1)),"m/d")
请指正。

评分

参与人数 1金币 +9 收起 理由
ws278106483 + 9 第一题稍有瑕疵。

查看全部评分

回复

使用道具 举报

发表于 2013-7-19 11:16 | 显示全部楼层
本帖最后由 dididing 于 2013-7-19 13:02 编辑

【函中201301班】-A18-dididing

因工作出差,所以本次作业只做了三个题。就这样吧,其他的,其实我也不会了。

第一题:
  1. =TEXT(LEFT(SUBSTITUTE(B303," ",REPT(" ",8)),11),"e年mm月dd日")&TEXT(TRIM(RIGHT(SUBSTITUTE(B303," ",REPT(" ",8)),9)),"上午/下午hh时mm分ss秒")
复制代码
第二题 :
  1. =MONTH(B313&-1)
复制代码
第四题:
  1. =CHOOSE(WEEKDAY(C337),0,-1,-2,-3,3,2,1)+C337
复制代码

评分

参与人数 1金币 +5 收起 理由
ws278106483 + 5 赞一个!

查看全部评分

回复

使用道具 举报

发表于 2013-7-19 18:52 | 显示全部楼层
201301函中班-A12-as0810114
1.   =TEXT(LEFT(B303,FIND(" ",B303))+RIGHT(B303,LEN(TRIM(B303))-FIND(" ",B303)),"yyyy年mm月dd日上午/下午hh时mm分ss秒")
2.   =MONTH(B313&1)
3.   =TEXT(TEXT(SUBSTITUTE(C322,20,),"d/m/y"),"yyyy-m-d")
4.  =C337-WEEKDAY(C337)+1+7*(WEEKDAY(C337)>4)
5.  =MONTH(TEXT(SUBSTITUTE(C346,".","-"),"[>1231]0;0-00"))
6.  =TEXT(DATE(C$360,1,ROW(A1)-6-WEEKDAY(DATE(C$360,1,1),2)+7*D$360),"m/d")

评分

参与人数 1金币 +10 收起 理由
ws278106483 + 10 神马都是浮云

查看全部评分

回复

使用道具 举报

发表于 2013-7-19 19:51 | 显示全部楼层
1
  1. =TEXT(SUM(--TRIM(MID(SUBSTITUTE(TRIM(B303)," ",REPT(" ",99)),{1,99},99))),"e年mm月dd日上午/下午hh时mm分ss秒")
复制代码
2
  1. =TEXT(C313&"-1","M")
复制代码
3
  1. =TEXT(--TEXT(SUBSTITUTE(C322,"/20","/"),"d-m-y"),"e-m-d")
复制代码
4
  1. =TEXT(C337-WEEKDAY(C337,2)+(WEEKDAY(C337,2)>3)*7,"e-m-d")
复制代码
5
  1. =MONTH(TEXT(SUBSTITUTE(C346,".","-"),"[<13]0;[>1231]0;0-00;"))
复制代码
6
  1. =TEXT(DATE(C$360,1,(D$360-1)*7-WEEKDAY(C$360&"-1-1")+ROW(A3)),"m/d")
复制代码

评分

参与人数 1金币 +10 收起 理由
ws278106483 + 10 很给力!

查看全部评分

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

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

GMT+8, 2024-11-1 07:05 , Processed in 0.135209 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

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