【函中20130301】第十讲D组作业上交帖（已经开贴）

顺⑦.zì繎。

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

笨笨四

【函中201301班】-D09-笨笨四
1.=SUM(--TEXT({1;"A";2;10;15.76;"B";21},"[>20]!0;[>=10]G/通用格式;!0;!0"))
2.=SUBSTITUTE(TEXT(C157,"0"&REPT("!,00",INT((LEN(C157)-1)/2))),",0",",")
3.=PRODUCT(-TEXT(MID(SUBSTITUTE(C167,"*",REPT(" ",99)),{1,100},99),"G/通用格式;-G/通用格式;0;!1"))
  =PRODUCT(-(MID(SUBSTITUTE(C164&"*1","*",REPT(" ",99)),{1,100},99)))
4.=TEXT(C173,"[dbnum1]0")
5.=MID(C181,MAX((MID(C181,COLUMN(1:1),3)=TEXT(row(1:366),"MMM"))*COLUMN(1:1)),3)

林玲玲

【函中201301班】-D11-林玲玲
1、=SUM(--TEXT(TEXT({1;"A";2;10;15.76;"B";21},"[>=10];!0;!0;!0"),"[<=20];!0;!0;!0"))
2、=SUBSTITUTE((TEXT(C157,"#"&REPT("!,00",INT(LEN(C157)/2.1)))),",0",",")
3、=PRODUCT(--TEXT(MID(SUBSTITUTE(C164&"*","*",REPT(" ",15)),{1,16},15),"G/通用格式;;;!1"))
4、=(TEXT(C173,"[DBNUM1]#"))
5、=MID(C181,SMALL(FIND(TEXT("2001-"&ROW($1:$12)&"-1","mmm"),C181&TEXT("2001-"&ROW($1:$12)&"-1","mmm")),1),3)
5、=TEXT(--MID(C181,FIND("/",C181)-2,5),"mmm;")

liziyuliziyu7

【函中201301班】-d04-liziyuliziyu7
=SUM(--TEXT({1;"A";2;10;15.76;"B";21},"[<10]!0;[>20]!0;0.00;\0"))
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(TEXT(C157,REPT("00 ",(LEN(C157)+1)/2)))," ",","),"0,","c"),0,),"c","0,")
=IF(ISNUMBER(0/C164),--C164,LEFT(C164,FIND("*",C164)-1)*MID(C164,FIND("*",C164)+1,9))
=TEXT(C173,"[dbnum1]0")
=TEXT(MID(C181,FIND("/",C181)-2,2)*30,"mmm")

岁月静好

函中201301班】-D24-岁月静好
1、
2、=SUBSTITUTE(TEXT(C157,"#"&REPT("!,00",INT(LEN(C157)/2.1))),",0",",")
3、
4、
5、

1982zyh

【函中201301班】-D16-1982zyh
1.=SUMPRODUCT((N({1;"A";2;10;15.76;"B";21}>=10)*N({1;"A";2;10;15.76;"B";21}<=20))*N({1;"A";2;10;15.76;"B";21}))
2.=MID(SUBSTITUTE(TEXT(C157,REPT("!,00",INT(LEN(C157)/2.1)+1)),",0",","),2,99)
3.=IF(FIND("*",C164&"*",1)<LEN(C164),LEFT(SUBSTITUTE(C164,"*",REPT(" ",10)),FIND("*",C164,1)-1)*RIGHT(SUBSTITUTE(C164,"*",REPT(" ",10)),LEN(C164)-FIND("*",C164,1)),C164)
4.=TEXT((TEXT(C173,"G/通用格式")-1900)*365+INT((TEXT(C173,"G/通用格式")-1900))/4+2,"[DBNum1][$-804]yyyy")
5.=MID(C181,FIND("/",C181,1)-9,3)
第三题不知道怎么用TEXT，作业都没怎么用TEXT

cg372101

【函中201301班】-D18-cg372101  第10讲作业：
题1（数组公式）：

1. =SUM(--TEXT({1;"A";2;10;15.76;"B";21},"[<10]!0;[>20]!0;#.00;!0"))

复制代码

题2：

1. =SUBSTITUTE(TEXT(C157,"#"&REPT("!,00",INT(LEN(C157)/2.1))),",0",",")

复制代码

题3：

1. =PRODUCT(--TEXT(MID(SUBSTITUTE(C164,"*",REPT(" ",15)),{0,1}*15+1,15),"G/通用格式;G/通用格式;G/通用格式;!1"))

复制代码

如果可以不用TEXT，则可以简化些：

1. =PRODUCT(--MID(SUBSTITUTE(C164&"*1","*",REPT(" ",15)),{0,1}*15+1,15))

复制代码

题4：

1. =TEXT(C173,"0[DBNum1]")

复制代码

题5（数组公式）：

1. =MID(C181,MIN(FIND(TEXT(ROW($1:$12)*30,"mmm"),C181&TEXT(ROW($1:$12)&-1,"mmm"))),3)

复制代码

Shelvee

【函中201301班】-D15-Shelvee
2. =TEXT(C157,"#"&REPT("!,00",INT(LEN(C157)/2.1)))

3. =IFERROR((LEFT(C164,FIND("*",C164&"*1")-1))*(MID(C164,FIND("*",C164&"*1")+1,7)),--C164)

5. =MID(C181,FIND("Fut",C181)+4,3)

veryloveep

【函中201301班】- D22-veryloveep
1.=SUM(--TEXT((TEXT({1;"A";2;10;15.76;"B";21},"0.00;0.00;;!0")),"[<10]!0;[>20]!0;0.00"))
2.=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(C157,REPT(" ,00",5)),",00",),",0",","),",",,1)
3.=--LEFT(C164&"*",SEARCH("~*",C164&"*")-1)*TEXT(--TEXT(SUBSTITUTE(--SUBSTITUTE(SUBSTITUTE(C164&"*",--LEFT(C164&"*",SEARCH("~*",C164&"*")-1)&"*",1)&"*","*",)*100,1,,1),"0!.00"),"0.00;0.00;1")
4.=TEXT(C173,"[dbnum1]0")
5.=MID(C181,FIND("13",C181)-4,3)

yunqingzi

1、{=SUM(1*(0&TEXT(TEXT({1;"A";2;10;15.76;"B";21},"[>=10]G/通用格式;"),"[<=20]G/通用格式;;;")))}
2、=TEXT(C157,"#"&REPT("!,??",INT(LEN(C157)/2.1)))
3、
4、=TEXT(NUMBERSTRING(C173,3),"@")
5、=MID(C181,FIND("Fut",C181)+3,4)