【函中201301班】第一讲C组作业上交帖【已评分完成】

CheryBTL

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

kouhogliang

【函中201301班】-c08-kouhogliang
1.
a8

1. =(LEN(A3)-LEN(SUBSTITUTE(A3,"小妖","")))/2+(LEN(A4)-LEN(SUBSTITUTE(A4,"小妖","")))/2+(LEN(A5)-LEN(SUBSTITUTE(A5,"小妖","")))/2+(LEN(A6)-LEN(SUBSTITUTE(A6,"小妖","")))/2+(LEN(A7)-LEN(SUBSTITUTE(A7,"小妖","")))/2

复制代码

2.
b13

1. =LEFT(MID(A13&"##",FIND("#",A13&"##")+1,99),FIND("#",MID(A13&"##",FIND("#",A13&"##")+1,99))-1)

复制代码

下拉到b18

3.
b23

1. =IF(INT(ROUND(A23,1))=0,"",NUMBERSTRING(INT(ROUND(A23,1)),2)&"元")&IF((ROUND(A23,1)-INT(ROUND(A23,1)))*10=0,IF(INT(ROUND(A23,1))=0,"零元整","整"),NUMBERSTRING((ROUND(A23,1)-INT(ROUND(A23,1)))*10,2)&"角")

复制代码

下拉到b38

lisa19860622

【函中201301班】-c19-lisa19860622
第一题:

1. =(LEN(PHONETIC(A3:A7))-LEN(SUBSTITUTE(PHONETIC(A3:A7),"小妖","")))/2

复制代码

第二题:
法1:

1. =MID(A13,FIND("#",A13&"#")+1,FIND("#",A13&"##",FIND("#",A13&"#")+1)-FIND("#",A13&"#")-1)

复制代码

88个字符,
法2:

1. =MID(A13,FIND("#",A13&"#")+1,FIND("#",SUBSTITUTE(A13&"##","#","@",1))-FIND("#",A13&"#")-1)

复制代码

90个字符.
第三题完整版(5.1终于想出来了,只是公式有点长,237个字符.):

1. =IF(ROUND(A23,1)>=1,TEXT(INT(ROUND(A23,1)),"[dbnum2]")&"元"&IF(ROUND(A23,1)=INT(ROUND(A23,1)),"整",TEXT(INT(MID(ROUND(A23,1),FIND(".",ROUND(A23,1)&".")+1,1)),"[dbnum2]")&"角"),IF(ROUND(A23,1)*10=0,"零元整",TEXT(ROUND(A23,1)*10,"[dbnum2]")&"角"))

复制代码

雨后的风

【函中201301班】-C10-雨后的风
第一题：
数组公式

1. =SUM(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",1)))

复制代码

1. =LEN(PHONETIC(A3:A7))-LEN(SUBSTITUTE(PHONETIC(A3:A7),"小妖",0))

复制代码

第二题：

1. =SUBSTITUTE(MID(SUBSTITUTE(A13,"#",REPT(" ",99)),99,99)," ",)

复制代码

1. =MID(A13,FIND("#",A13&"#")+1,FIND("#",REPLACE(A13,FIND("#",A13&"#"),1,)&"#")-FIND("#",A13&"#"))

复制代码

第三题：

1. =IF(ROUND(A23,1),SUBSTITUTE(SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1),"[DBNUM2]"),FIND(".",TEXT(ROUND(A23,1),"[DBNUM2]")&"."),1,"元")&"角","元角","元整"),"零元",),"零元整")

复制代码

yjeeee

【函中201301班】-C07-yjeeee
请学委点评

1. 练习1
   
2. 数组=SUM(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",)))/2
   
3. 
   
4. =SUMPRODUCT({1;1;1;1;1},LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",)))/2

复制代码

1. 练习2
   
2. =IF(ISERR(FIND("#",A13)),"",IF(ISERR(FIND("#",MID(A13,FIND("#",A13)+1,10))),MID(A13,FIND("#",A13)+1,10),MID(MID(A13,FIND("#",A13)+1,10),1,FIND("#",MID(A13,FIND("#",A13)+1,10))-1)))

复制代码

1. 练习3
   
2. =IF(ROUND(A23,1)=0,"零元",IF(ROUND(A23,1)<1,,NUMBERSTRING(ROUND(A23,1),2)&"元"))&IF(OR(ROUND(A23-INT(A23),1)>0.9,ROUND(A23-INT(A23),1)=0),"整",NUMBERSTRING(ROUND(A23-INT(A23),1)*10,2)&"角")

复制代码

csmccdh

【函中201301班]- C01-csmccdh上交作业：

第一题：
方法1：
=(LEN(PHONETIC(A3:A7))-LEN(SUBSTITUTE(PHONETIC(A3:A7),"小妖",)))/2
方法2（数组公式）：
=SUM(N(MID(A3:A7,COLUMN(A:CU),2)="小妖"))
方法3（数组公式）：
=SUM(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",)))/2

第二题：
方法1：
=TRIM(MID(SUBSTITUTE(A13,"#",REPT(" ",99)),99,99))
方法2：
=MID(A13,FIND("#",A13&"#")+1,FIND("#",A13&"##",FIND("#",A13&"#")+1)-FIND("#",A13&"#")-1)

第三题：
方法1：
=TEXT(INT(ROUND(A23,1)),"[DBNum2]G/通用格式元;;")&TEXT(ROUND(A23,1),";;零元")&TEXT(MOD(ROUND(A23,1),1)*10,"[DBNum2]0角;;整")
方法2：
=IF(MOD(ROUND(ROUND(ROUND(A23,1),1),1),1),IF(INT(ROUND(ROUND(ROUND(A23,1),1),1)),SUBSTITUTE(TEXT(ROUND(ROUND(ROUND(A23,1),1),1),"[DBNum2]")&"角",".","元"),SUBSTITUTE(TEXT(ROUND(ROUND(ROUND(A23,1),1),1),"[DBNum2]")&"角","零.",)),TEXT(ROUND(ROUND(ROUND(A23,1),1),1),"[DBNum2]")&"元整")

云龙

题一

1. =SUM((LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",1))))

复制代码

1. =LEN(PHONETIC(A3:A7))-LEN(SUBSTITUTE(PHONETIC(A3:A7),"小妖",1))

复制代码

题二

1. =TRIM(MID(SUBSTITUTE(A13,"#",REPT(" ",9)),9,9))

复制代码

1. =MID(A13,FIND("#",A13&"#")+1,FIND("#",A13&"##",FIND("#",A13&"#")+1)-FIND("#",A13&"#")-1)

复制代码

题三

1. =SUBSTITUTE(TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元"&TEXT(RIGHT(ROUND(A23*10,)),"[DBNum2]0角;;整"),IF(ROUND(A23,1),"零元",""),)

复制代码

xuyouxm

1. =(LEN(A3&A4&A5&A6&A7)-LEN(SUBSTITUTE(CONCATENATE(A3,A4,A5,A6,A7),"小妖",0)))

复制代码

1. =MID(MID(A13,FIND("#",A13&"#")+1,99),1,FIND("#",MID(A13,FIND("#",A13&"#")+1,99)&"#")-1)

复制代码

【函中201301班】-C21-xuyouxm

选做题：

1. =IF(LEN(SUBSTITUTE(IF(ROUND(A23,1)=INT(ROUND(A23,1)),TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元整",TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元"),"零元","")&IF(((ROUND(A23,1)-INT(ROUND(A23,1)))*10)=0,"",NUMBERSTRING(((ROUND(A23,1)-INT(ROUND(A23,1)))*10),2)&"角"))=1,"零元"&SUBSTITUTE(IF(ROUND(A23,1)=INT(ROUND(A23,1)),TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元整",TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元"),"零元","")&IF(((ROUND(A23,1)-INT(ROUND(A23,1)))*10)=0,"",NUMBERSTRING(((ROUND(A23,1)-INT(ROUND(A23,1)))*10),2)&"角"),SUBSTITUTE(IF(ROUND(A23,1)=INT(ROUND(A23,1)),TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元整",TEXT(INT(ROUND(A23,1)),"[DBNum2]")&"元"),"零元","")&IF(((ROUND(A23,1)-INT(ROUND(A23,1)))*10)=0,"",NUMBERSTRING(((ROUND(A23,1)-INT(ROUND(A23,1)))*10),2)&"角"))

复制代码

孤单无所谓

【函中201301班】-C04孤单无所谓
1

1. =COUNT(IF(FIND("小妖",PHONETIC(A3:A7),ROW(1:99))=ROW(1:99),))

复制代码

2

1. =TRIM(LEFT(SUBSTITUTE(MID(A13&"#",FIND("#",A13&"#")+1,99),"#",REPT(" ",99)),99))

复制代码

3

1. =IF(AND(INT(ROUND(A23,1))=0,INT(ROUND(A23,1))<>ROUND(A23,1)),"",NUMBERSTRING(INT(ROUND(A23,1)),2)&"元")&IF(INT(ROUND(A23,1))=ROUND(A23,1),"整",NUMBERSTRING(ROUND((ROUND(A23,1)-INT(ROUND(A23,1)))*10,1),2)&"角")

复制代码

cir.s

【函中201301班]- C12-cir.s
第一题：   =SUM(LEN(A3:A7))-SUM(LEN(SUBSTITUTE(A3:A7,"小妖","0")))   三键回车
第二题：   =IFERROR(MID(A13,FIND("#",A13)+1,FIND("#",A13&"#1",FIND("#",A13)+1)-1-FIND("#",A13)),"")
第三题：   =IF(SUBSTITUTE(SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1)+0.001,"[DBNum2]"),FIND(".",TEXT(ROUND(A23,1)+0.001,"[DBNum2]"))+2,2,"角"),"零角","整"),".","元")="零元整","零元整",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1)+0.001,"[DBNum2]"),FIND(".",TEXT(ROUND(A23,1)+0.001,"[DBNum2]"))+2,2,"角"),"零角","整"),".","元"),"零元",""))