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

hwc2ycy

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

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

云影

【函中201301班】-B15-云影
1.

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

复制代码

2.

1. =MONTH(B313&1)

复制代码

3.

1. =DATE(YEAR(MID(C322,FIND("/",C322,4)+1,4)&"-1-1"),-LOOKUP(,-MID(C322,FIND("/",C322)+1,{1;2})),-LOOKUP(,-LEFT(C322,{1;2})))

复制代码

4.

1. =C337+VLOOKUP(MOD(C337,7),{0,1;2,-1;1,0;5,3;3,-2;4,-3;6,2},2,)

复制代码

5.

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

复制代码

6.

1. =TEXT(DATE(C$360,1,D$360*7)-(13-ROW(A1)),"M/D")

复制代码

opqazxc

【函中201301班】-B18-opqazxc

1

1. =TEXT(SUM(--TRIM(MID(SUBSTITUTE(B303," ",REPT(" ",50)),{1,51},{50,999}))),"e年mm月dd日hh时mm分ss秒")

复制代码

2

1. =MONTH(B313&1)

复制代码

3数组公式

1. =TEXT(--VLOOKUP("*-*",SUBSTITUTE(C322,"/"&ROW($1:$12)&"/",TEXT(ROW($1:$12)&"-1","-mmm")),1,),"e-m-d")

复制代码

4原来想的没保存    又从第6题得到个思路

1. =TEXT(ROUND((C337-1)/7,)*7+1,"e-m-d")

复制代码

5

1. =MONTH(--TEXT(--SUBSTITUTE(C346,".","-"),"[<10000]0-00;0"))

复制代码

6原来的忘记保存了
看云影的公式想到    03版本的整列3100年以内的还是可以用的

1. =TEXT(LOOKUP(DATE(C$360,1,D$360*7),1+ROW($1:6000)*7)-7+ROW(A1),"m/d")

复制代码

然后再想到一个思路   晚上再看下对不对好了    不对再改

1. =TEXT(INT(DATE(C360,1,D360*7)/7)*7-6+ROW(A1),"m/d")

复制代码

zx_wl

【函中201301班】-B05-zx_wl
1、

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

复制代码

2、

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

复制代码

3、

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

复制代码

4、

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

复制代码

5、

1. =TEXT(--IF(--SUBSTITUTE(C346,".","-")<9999,TEXT(C346,"0!-00"),SUBSTITUTE(C346,".","-")),"m")

复制代码

6、

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

复制代码

lyf7276

1、=TEXT(TRIM(LEFT(SUBSTITUTE(B303," ",REPT(" ",20)),20)),"yyyy年mm月dd日")&TEXT(TRIM(RIGHT(SUBSTITUTE(B303," ",REPT(" ",20)),20)),"上午/下午"&"hh时mm分ss秒")
2、MONTH(1&B313)
3、TEXT(INDEX(TRIM(MID(SUBSTITUTE(C322,"/",REPT(" ",20)),{40,20,1},10)),1)&"-"&INDEX(TRIM(MID(SUBSTITUTE(C322,"/",REPT(" ",20)),{40,20,1},10)),2)&"-"&INDEX(TRIM(MID(SUBSTITUTE(C322,"/",REPT(" ",20)),{40,20,1},10)),3),"yyyy-m-d")
4、C337+IF(WEEKDAY(C337,2)<4,-WEEKDAY(C337,2),7-WEEKDAY(C337,2))
5、IF(C346>4000,TEXT(C346,"m"),IFERROR(LEFT(C346,FIND(".",C346)-1),LEFT(C346,LEN(C346)-2)))
6、TEXT(--((($C$360&"-1-1")+($D$360<>1)*(($D$360-2)*7+7-WEEKDAY(--($C$360&"-1-1"),3))))+(ROW(A1)-1),"m/d")

stone1969

【函数中级201301】B16-stone1969.rar (17.01 KB, 下载次数: 7)

2013-7-18 18:36 上传

点击文件名下载附件 第一题 =TEXT(MID(B3,1,FIND(" ",B3)-1)+TRIM(MID(B3,FIND(" ",B3)+1,15)),"yyyy年m月d日hh时mm分ss秒")
第二题 =TEXT(B13&1,"m")
第三题 =--TEXT(--SUBSTITUTE(RIGHT(C22,2),"/","0")&"-"&MID(SUBSTITUTE(C22,"/","-",2),FIND("/",C22)+1,FIND("-",SUBSTITUTE(C22,"/","-",2))-FIND("/",C22)-1)&"-"&LEFT(C22,FIND("/",C22)-1),"e-m-d")
第四题 =TEXT(IF(WEEKDAY(C37,2)>3,C37+7-WEEKDAY(C37,2),C37-WEEKDAY(C37,2)),"e-M-d")
第五题 =--IF(--SUBSTITUTE(C46,".","-")<40000,LEFT(TEXT(C46,"0000"),2),TEXT(--SUBSTITUTE(C46,".","-"),"m"))

gaawx

4    =7-WEEKDAY(C337,2)+C337
6    =DATE(C360,1,1)+D360*7-WEEKDAY(DATE(C360,1,1),2)+ROW(1:7)

gaawx

【函中201301班】-B19-GAAWX

ykfexcel

函中201301班】-B20-ykfexcel
1,=TEXT(LEFT(B303,FIND(" ",B303)-1),"e年mm月dd日")&TEXT(SUBSTITUTE(MID(B303,FIND(" ",B303),20)," ",""),"上午/下午hh时mm分ss秒")
2,=MONTH(B313&1)
3,=TEXT(MID(C322,FIND("/",C322,FIND("/",C322)+1)+1,4)&"/"&MID(C322,FIND("/",C322)+1,FIND("/",C322,FIND("/",C322)+1)-FIND("/",C322)-1)&"/"&LEFT(C322,FIND("/",C322)-1),"e/m/d")
4,=TEXT(IF(7-WEEKDAY(C337,2)<4,C337+7-WEEKDAY(C337,2),C337-WEEKDAY(C337,2)),"e-m-d")
5,=MONTH(SUBSTITUTE(C346,".","-"))      [其中有一行单独 =MONTH(TEXT(C355,"0-00"))才行]
6,=MONTH(DATE($C$360,1,($D$360-2)*7+ROW(A1)+8-WEEKDAY(DATE($C$360,1,1),2)))&"/"&DAY(DATE($C$360,1,($D$360-2)*7)+ROW(A1)+8-WEEKDAY(DATE($C$360,1,1),2))

11.rar (59.96 KB, 下载次数: 10)

2013-7-19 16:37 上传

点击文件名下载附件

wangjianff

【函中201301班】-B12-wangjianff
第一题：

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

复制代码

第二题：

1. {=MATCH(B313,TEXT(DATE(,ROW($1:$12),1),"mmm"),)}

复制代码

第三题：

1. =DATE(SUBSTITUTE(RIGHT(C322,2),"/","")+2000,MID(C322,FIND("/",C322)+1,FIND("/",C322,FIND("/",C322)+1)-FIND("/",C322)-1),LEFT(C322,FIND("/",C322)-1))

复制代码

第四题：

1. =C337+(WEEKDAY(C337,2)>3)*7-WEEKDAY(C337,2)

复制代码

第五题：

1. =MONTH(TEXT(--SUBSTITUTE(C346,".","-"),"[<9999]0-00;0"))

复制代码

第六题：

1. {=IFERROR(TEXT(SMALL(IF(WEEKNUM(DATE(C360,1,)+ROW(INDIRECT("1:"&(DATE(C360+1,1,)-DATE(C360,1,)))),2)=D360,DATE(C360,1,)+ROW(INDIRECT("1:"&(DATE(C360+1,1,)-DATE(C360,1,))))),ROW($1:$7)),"M/D"),"")}

复制代码