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

hwc2ycy

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

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

lyf7276

1、SUM(--TEXT({1;"a";2;10;15.76;"B";21},"[<10]!0;[>20]!0;0.00;!0"))
2、SUBSTITUTE(TEXT(C157,"#"&REPT("!,00",INT(LEN(C157)/2.1))),",0",",")
3、PRODUCT(--TEXT(MID(SUBSTITUTE(C164,"*",REPT(" ",9)),{1,9},9),"0.00;;;!1"))
4、TEXT(C173,"[dbnum1]0")
5、RIGHT(TEXT(MID(C182,FIND("/",C182)-2,2)&"/1","d-mmm;"),3)

wangjianff

第一题：

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

复制代码

第二题：

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

复制代码

第三题：

1. {=PRODUCT(--TRIM(MID(SUBSTITUTE(C164&"*1","*",REPT(" ",99)),99*{1,2}-98,99)))}

复制代码

第四题：

1. =TEXT(C173&"-1","[dbnum1]e")

复制代码

第五题：

1. {=TEXT(DATE(2003,MID(C181,SEARCH("??/??/????",C181),2),1),"MMM")}

复制代码

【函中201301班】-B12-wangjianff

ldxhzy

【函中201301班】-B22-ldxhzy
1、
2、
3、E164公式 =TEXT(TEXT(MID(SUBSTITUTE(C164&"*","*",REPT(" ",15)&0),1,15),"0.00;-0;0;"),"0.00;-0;0;")*MAX(1,TEXT(MID(SUBSTITUTE(C164&"*","*",REPT(" ",15)&0),16,15),"0.00;-0;0;"))
4、
5、

zx_wl

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

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

复制代码

2、

1. =SUBSTITUTE(TEXT(C157,REPT("##!,",(LEN(C157)-1)/2)&"##"),",0",",")

复制代码

3、

1. =MID(SUBSTITUTE(C164,"*",REPT(" ",9)),1,9)*TEXT(MID(SUBSTITUTE(C164&"*1","*",REPT(" ",9)),10,9),"G/通用格式")

复制代码

4、

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

复制代码

5、

1. =TEXT(SUBSTITUTE(MID(C181,FIND(13,C181),8)," ","/"),"mmm")

复制代码

云影

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

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

复制代码

2.

1. =SUBSTITUTE(SUBSTITUTE(TEXT(C157,REPT("0!,0",INT(LEN(C157)/2))&0),",0",","),"0,4",4)

复制代码

3.

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

复制代码

4.

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

复制代码

5.

1. =MID(C181,MATCH("-*",-(MID(C181,ROW($1:99),3)&"-1")&"",),3)

复制代码

stone1969

第一题=SUM(--TEXT({1;"A";2;10;15.76;"B";21},"[<10]!0;[>20]!0;0%;!0"))
          =SUM(--TEXT(N({1;"A";2;10;15.76;"B";21}),"[>20]!0;[<10]!0;.00"))
第二题=SUBSTITUTE(TEXT(C7,"#"&REPT("!,00",INT(LEN(C7)/2.1))),",0",",")
第三题=TRIM(MID(SUBSTITUTE(C14&"*","*",REPT(" ",99)),1,99))*TEXT(TRIM(MID(SUBSTITUTE(C14&"*","*",REPT(" ",99)),100,99)),"g/通用格式;;;!1")
第四题=TEXT(NUMBERSTRING(C23,3),)
            =TEXT(C23&"-1","[dbnum1]e")
              =TEXT(C23,"[dbnum1]0")        
第五题 =MID(C31,FIND("13",C31)-4,3)

ykfexcel

【函中201301班】-B20-ykfexcel
第一题和第二题还没想出好的解决办法，只好把能做的贴出来，请学委指点
1、=SUM(TEXT(TEXT({1;"A";2;10;15.76;"B";21},"[<=20]0.00;;;"),"[>=10]0.00;;;"))
2、=TEXT(C157,"#"&REPT("!,00",INT(LEN(C157)/2.1)))
3、=TEXT(MID(SUBSTITUTE(C164&"*1","*",REPT(" ",15)),1,15),"0.00")*TEXT(MID(SUBSTITUTE(C164&"*1","*",REPT(" ",15)),15,15),"0.00")
4、=TEXT(NUMBERSTRING(C173,3),"0")
5、=MID(SUBSTITUTE(C181," ",),SEARCH("Fut",SUBSTITUTE(C181," ",))+3,3)

opqazxc

【函中201301班】-B18-opqazxc
``都是直接回车后下拉即可
1

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

复制代码

2

1. =SUBSTITUTE(TEXT(C157,0&REPT("!,00",LEN(C157)/2-0.5)),",0",",")

复制代码

3

1. =PRODUCT(-TEXT(--(0&TRIM(MID(SUBSTITUTE(C164,"*",REPT(" ",20)),{1,21},20))),"[=0]1;0.00"))

复制代码

4

1. =TEXT(C173&"-1","[DBNum1]e")

复制代码

5

1. =MID(C181,LOOKUP(99,FIND(TEXT(ROW($1:12)&"-1","mmm"),C181)),3)

复制代码

MYHERE131

【函中201301班】- B02-MYHERE131
第一题：=SUM(({1;"A";2;10;15.76;"B";21}>=10)*({1;"A";2;10;15.76;"B";21}<=20)*(IF(ISNUMBER({1;"A";2;10;15.76;"B";21})=TRUE(),{1;"A";2;10;15.76;"B";21},0)))
第四题：=NUMBERSTRING(C173,3)