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

ws278106483

1、本贴已设置回贴仅作者可见，直接跟贴回复即可；
2、上交时直接贴公式，只上传附件的不予批改；
3、答题时必须注明【函中201301班】-组号-论坛ID,如【函中201301班】-A99-ws278106483；
4、截止时间2013年7月19日17:00；
5、请误重复占楼，非本组同学跟贴回复者扣分。

下附课程相关链接:
函中201301班第十一讲课件

蓝火雨

【函中201301班】-A21-蓝火雨
1、=TEXT(MID(B303,1,FIND(" ",B303,1)),"yyyy""年""mm""月""dd""日""")&TEXT(SUBSTITUTE(MID(B303,FIND(" ",B303,1),100)," ",""),"上午/下午hh""时""mm""分""ss""秒"";@")
2、=MONTH(B313&"-2013")
3、=TEXT(MID(C322,FIND("/",C322,FIND("/",C322,1)+1)+1,4)&"-"&MID(C322,FIND("/",C322,1)+1,FIND("/",C322,FIND("/",C322,1)+1)-FIND("/",C322,1)-1)&"-"&MID(C322,1,FIND("/",C322,1)-1),"e-m-d")
4、=IF(DATEDIF(C337-WEEKDAY(C337,2),C337,"d")>DATEDIF(C337,C337-WEEKDAY(C337)+8,"d"),F337,IF(DATEDIF(C337-WEEKDAY(C337,2),C337,"d")=DATEDIF(C337,C337-WEEKDAY(C337)+8,"d"),C337,C337-WEEKDAY(C337,2)))
5、=--IF(C346=40480,MONTH(TEXT(SUBSTITUTE(C346,".","-"),"mm-dd")),IF(ISTEXT(C346),MONTH(TEXT(SUBSTITUTE(C346,".","-"),"mm-dd")),IF(--SUBSTITUTE(SUBSTITUTE(LEFT(C346,2),"-",""),".","")<=12,SUBSTITUTE(SUBSTITUTE(LEFT(C346,2),"-",""),".",""),LEFT(C346,1))))
6、=INDEX($G$360:$G$428,LARGE(($J$360:$J$428=$D$360)*(ROW($A$360:$A$428)-359),{7;6;5;4;3;2;1}))

augustzhu1983

【函中201301班】-A08-augustzhu1983
第一题

1. =TEXT(LEFT(B303,FIND(" ",B303))+TRIM(MID(B303,FIND(" ",B303),99)),"e年mm月dd日上午/下午hh时mm分ss秒")

复制代码

第二题

1. =TEXT(B313&"-1","m")

复制代码

第三题

1. =--TEXT(SUM(MID(SUBSTITUTE(C322,"/",REPT(" ",9)),{1,9,18},9)*10^{0,2,4}),"0-00-00")

复制代码

第四题

1. =C337+(WEEKDAY(C337)>4)*7-WEEKDAY(C337)+1

复制代码

第五题

1. =TEXT(TEXT(SUBSTITUTE(C346,".","-"),"[>1231]0;0-00"),"m")

复制代码

第六题

1. =DATE(C$360,1,D$360*7+ROW(A1)-WEEKDAY(DATE(C$360,1,1),2)-6)

复制代码

fcyxiaoyuer

1. =TEXT(SUM(--LEFT(B303,FIND(" ",B303)),(--SUBSTITUTE(MID(B303,FIND(" ",B303),15)," ",""))),"yyyy年m月d日上午/下午h时mm分ss秒")

复制代码

1. =TEXT(MONTH("1-"&B313),"0")

复制代码

1. =SUBSTITUTE(IF(LEN(TEXT(MID(C322,FIND("/",C322,4)+1,4),"000"))=4,TEXT(MID(C322,FIND("/",C322,4)+1,4),"000"),"2"&TEXT(MID(C322,FIND("/",C322,4)+1,4),"000"))&"-"&MID(C322,FIND("/",C322)+1,2)&"-"&LEFT(C322,2),"/","")

复制代码

1. =TEXT(IF(--TEXT(7-WEEKDAY(C337,2),"0")<=3,7-WEEKDAY(C337,2),-WEEKDAY(C337,2))+C337,"yyyy-m-d")

复制代码

1. =IF(LEN(TRUNC(C346))=5,MONTH(C346),IF(--LEFT(TRUNC(C346,),2)>12,LEFT(TRUNC(C346),1),LEFT(TRUNC(C346),2)))

复制代码

1. =TEXT(($C$360&"-1-1")+7*($D$360-1)-WEEKDAY("1956-1-1",2)+1,"m-d")

复制代码

rlz680926

【函中201301班】-A24
      第一题:方法1:=TEXT(LEFT(B303,SEARCH(" ",B303)-1)+TRIM(RIGHT(B303,LEN(B303)-SEARCH(" ",B303))),"yyyy年mm月dd日"&"上午/下午hh""时""mm""分""ss""秒""")下拉
           方法2:=TEXT(TEXT(LEFT(B303,SEARCH(" ",B303)-1),"e-mm-dd"),"yyyy年mm月dd日")&TEXT(TRIM(RIGHT(B303,LEN(B303)-SEARCH(" ",B303))),"上午/下午hh""时""mm""分""ss""秒""")下拉
    第二题:方法1:=MID(TEXT(TEXT((1&B313&2010),"dd-mmm-yyyy"),"dd-m-yyyy"),FIND("-",TEXT(TEXT((1&B313&2010),"dd-mmm-yyyy"),"dd-m-yyyy"))+1,FIND("-",TEXT(TEXT((1&B313&2010),"dd-mmm-yyyy"),"dd-m-yyyy"),4)-FIND("-",TEXT(TEXT((1&B313&2010),"dd-mmm-yyyy"),"dd-m-yyyy"))-1)下拉
          方法2:=MID(TEXT(TEXT((1&B313&2010),"dd-mmm-yyyy"),"dd-m-yyyy"),4,IF(LEN(TEXT(TEXT((1&B313&2010),"dd-mmm-yyyy"),"dd-m-yyyy"))=9,1,2))下拉
第三题:=TEXT(TEXT(RIGHT(TEXT(RIGHT(C322,LEN(C322)-FIND("/",C322,FIND("/",C322)+1)),2&"000"),4)&TEXT(MID(C322,FIND("/",C322)+1,FIND("/",C322,FIND("/",C322)+1)-FIND("/",C322)-1),"00")&TEXT(LEFT(C322,FIND("/",C322)-1),"00"),"#-00-00"),"e-m-d")下拉
第四题:=IF(WEEKDAY(C337,2)>=4,C337+(7-WEEKDAY(C337,2)),C337-WEEKDAY(C337,2))下拉
第五题：=IF(--SUBSTITUTE(C346,".","-")<40000,LEFT(--SUBSTITUTE(C346,".","-"),LEN(--SUBSTITUTE(C346,".","-"))-2),TEXT(--SUBSTITUTE(C346,".","-"),"m"))

007feiren

【函中201301班】-A22-007feiren:
第1题：

1. =LEFT(B303,FIND(" ",B303))+TRIM(MID(B303,FIND(" ",B303),15))

复制代码

第2题：

1. =MONTH(--(B313&1))

复制代码

第3题：

1. =TEXT(MID(C322,FIND("/",C322,4)+1,4)&"-"&MID(C322,FIND("/",C322)+1,FIND("/",C322,4)-(FIND("/",C322)+1))&"-"&LEFT(C322,FIND("/",C322)-1),"yyyy-m-d")

复制代码

第4题：

1. =IF((7-WEEKDAY(C337,2))>=4,C337-WEEKDAY(C337,2),C337+(7-WEEKDAY(C337,2)))

复制代码

第5题：

1. =IF(--C346>4000,TEXT(C346,"m"),IF(--C346>1000,LEFT(C346,2),IF(--C346>500,LEFT(--C346,1),TEXT(SUBSTITUTE(C346,".","-"),"m"))))

复制代码

第6题：

1. =DATE($C$360,1,(($D$360-1)*7-WEEKDAY($C$360&"-1-1",2))+ROW(A2))

复制代码

daichaodaichao

【函中201301班】-A09-daichaodaichao

1. 1.=TEXT(LEFT(B303,FIND(" ",B303)-1)+TRIM(RIGHT(B303,LEN(B303)-FIND(" ",B303))),"yyyy年mm月dd日上午/下午hh时mm分ss秒")
   
2. 2.=MONTH(B313&1)
   
3. 3.技巧分列-下一步-下一步-日期（ymd）-完成
   
4. 4.=IF(WEEKDAY(C337,2)<4,C337-WEEKDAY((C337),2),C337-WEEKDAY(C337,2)+7)
   
5. 5.=TEXT(TEXT(SUBSTITUTE(C346,".","-"),"[>1231]0;0-00"),"m")

复制代码

zjyxp

【函中201301班】-A10:zjyxp先上交作业，请学委批改指点，单位没网太担心了，先上次，有补充的话在关贴前补上，谢谢
第一题
1、=TEXT(--LEFT(B303,FIND(" ",B303)-1),"yyyy年mm月dd日")&TEXT(TRIM(MID(B303,FIND(" ",B303)+1,99)),"上午/下午hh时mm分ss秒")
2、=TEXT(--LEFT(B303,FIND(" ",B303)-1)+TIMEVALUE(MID(B303,FIND(" ",B303)+1,99)),"yyyy年mm月dd日上午/下午hh时mm分ss秒")
两个笨解
第二题
=TEXT(B313&1,"m")
第三题
=--TEXT(--MID(SUBSTITUTE(C322,"/",REPT(" ",9)),20,9)&-MID(SUBSTITUTE(C322,"/",REPT(" ",9)),10,9)&-MID(SUBSTITUTE(C322,"/",REPT(" ",9)),1,9),"yyyy-mm-dd")
第四题
=C337+LOOKUP(WEEKDAY(C337,2),ROW($1:$7),{-1;-2;-3;3;2;1;0})
第五题
=MONTH(TEXT(SUBSTITUTE(C346,".","-"),"[<10000]0-00")*1)
第六题
=TEXT(DATE(C$360,1,1)+ROW(A64)-WEEKDAY(DATE(C$360,1,1),2),"m/d")

hjcotton

函中201301班】-A25-hjcotton
1.TEXT(--LEFT(B303,FIND(" ",B303)),"yyyy年mm月dd日")&TEXT(TIMEVALUE(MID(B303,FIND(" ",B303)+1,LEN(B303))),"上午/下午h时mm分ss秒;@")
2.MONTH(B313&1)
3.TEXT((MID(C322,FIND("?",SUBSTITUTE(C322,"/","?",2))+1,99)&"/"&MID(C322,FIND("/",C322)+1,FIND("?",SUBSTITUTE(C322,"/","?",2))-FIND("/",C322)-1)&"/"&LEFT(C322,FIND("/",C322)-1)),"yyyy/m/d")
4.TEXT(IF(WEEKDAY(C337,2)<=3,C337-WEEKDAY(C337,2),C337+7-WEEKDAY(C337,2)),"yyyy-m-d")
5.MONTH(IF(ISNUMBER(--SUBSTITUTE(SUBSTITUTE(REPLACE(C346,LEN(C346)-1,,"."),".","-"),"--","-")),--SUBSTITUTE(SUBSTITUTE(REPLACE(C346,LEN(C346)-1,,"."),".","-"),"--","-"),--SUBSTITUTE(SUBSTITUTE(REPLACE(C346,LEN(C346)-1,,"."),".","-"),"-","")))
6.TEXT(DATE(C360,1,1)+(D360-1)*7-7+MOD(DATE(C360,1,1),7),"m/d")

漫天独舞

【函中201301班】-A04-漫天独舞

1. 1、=TEXT(LEFT(B303,FIND(" ",B303)-1),"yyyy年mm月dd日")&TEXT(SUBSTITUTE(MID(B303,FIND(" ",B303),50)," ",""),"hh时mm分ss秒;@")
   
2. 2、不会
   
3. 3、=TEXT(--MID(C322,FIND("/",C322,FIND("/",C322)+1)+1,6)&"-"&MID(C322,FIND("/",C322)+1,FIND("/",C322,FIND("/",C322)+1)-FIND("/",C322)-1)&"-"&LEFT(C322,FIND("/",C322)-1),"yyyy-mm-dd")
   
4. 4、=IF(WEEKDAY(C337,2)>3,C337+7-WEEKDAY(C337,2),C337-WEEK
   
5. DAY(C337,2))
   
6. 5、不会
   
7. 6、=(C360&"-1"&"-1")+7*8+8-WEEKDAY(--(C360&"-1"&"-1"),2)

复制代码