【函中201301班】第一讲B组作业上交帖

feedyu

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

lyf7276

第一题目答案：=SUM(IF(MID(PHONETIC(A3:A7),ROW(1:50),2)<>"小妖","",1))
第二题目答案：=REPLACE(REPLACE(A13,1,IFERROR(FIND("#",A13),100),""),IFERROR(FIND("#",REPLACE(A13,1,IFERROR(FIND("#",A13),100),"")),100),100,"")
第三题目答案：=IFERROR(IF(FIND(".",IF(LEN(TEXT(A24,"#.#"))=1,0,TEXT(A24,"#.#")))=LEN(IF(LEN(TEXT(A24,"#.#"))=1,0,TEXT(A24,"#.#"))),SUBSTITUTE(IF(LEN(TEXT(A24,"#.#"))=1,0,TEXT(A24,"#.#")),".","元整"),IF(FIND(".",IF(LEN(TEXT(A24,"#.#"))=1,0,TEXT(A24,"#.#")))=1,(IF(LEN(TEXT(A24,"#.#"))=1,0,TEXT(A24,"#.#"))*10)&"角",SUBSTITUTE(IF(LEN(TEXT(A24,"#.#"))=1,0,TEXT(A24,"#.#")),".","元")&"角")),0&"元")。化为大写的代码不知道。
函中201301班】-Ｂ01-lyf7276

云影

【函中201301班]- B15-云影
练1：

1. =SUMPRODUCT(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,A6,1)))

复制代码

练2：

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

复制代码

例3:（选做题）

1. =IF(A23+0.05<0.1,"零元整",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(TEXT(A23+0.05,"[DBNum2]"),FIND(".",TEXT(A23+0.05,"[DBNum2]"))+1)&"角",".","元"),"零元",),"零角","整"))

复制代码

opqazxc

【函中201301班]- B18-opqazxc
第一题

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

复制代码

第二题

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

复制代码

第三题

1. =IF(ROUND(A23,1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A23+5.1%,"[DBNum2]"),".","元"),"零元",),RIGHT(TEXT(A23+5.1%,"[DBNum2]"),2),"角"),"零角","整"),"零元整")

复制代码

第一题搞个数组公式

1. =SUM(N(MID(A3:A7,COLUMN(A:Z),2)="小妖"))

复制代码

木果果木

【函中201301班]- B11-木果果木
第1题：
（1）非数组：

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

复制代码

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

复制代码

（2）数组：

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

复制代码

第2题：

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

复制代码

第3题：

1. =IF(ISERR(FIND(".",ROUND(A23,1))),TEXT(ROUND(A23,1),"[DBNum2]")&"元整",IF(TRUNC(ROUND(A23,1))=0,TEXT(MID(ROUND(A23,1),3,99),"[DBNum2]")&"角",SUBSTITUTE(TEXT(ROUND(A23,1),"[DBNum2]")&"角",".","元")))

复制代码

wangjianff

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

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

复制代码

第二题

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

复制代码

第三题

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

复制代码

风花雪月8888

【函中201301班]- B07-风花雪月8888
=SUMPRODUCT((LEN(A3:A7))-LEN(SUBSTITUTE(A3:A7,"小妖",1)))

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

=SUBSTITUTE(IF(ROUND(A42,1)<1,NUMBERSTRING(INT((ROUND(A42,1)-INT(ROUND(A42,1)))*10),2)&"角",SUBSTITUTE(SUBSTITUTE(REPLACE(TEXT(ROUND(A42,1)+0.001,"[DBNum2]"),FIND(".",TEXT(H42+0.001,"[DBNum2]"))+2,,"角"),"零角","整"),".","元")),"零壹",)

ldxhzy

【函中201301班]- B22-ldxhzy
练1：A8（数组公式） ：=SUM(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",1)))
练2：B13公式：=LEFT(MID(A13&"##",FIND("#",A13 & "##")+1,99),FIND("#",MID(A13&"##",FIND("#",A13 & "##")+1,99))-1)


练3：B23公式：=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(NUMBERSTRING(LEFT(TEXT(A23,"#0.0"),FIND(".",TEXT(A23,"#0.0"))-1),2)&"元"&NUMBERSTRING(RIGHT(TEXT(A23,"#0.0"),1),2) & "角","零元零角","零"),"零角","整"),"零元","")

zx_wl

【函中201301班]- B05-zx_wl
1

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

复制代码

2

1. 1、=IFERROR(MID(A13,FIND("#",A13)+1,FIND("#",A13&"#",FIND("#",A13)+1)-FIND("#",A13)-1),"")
   
2. 2、=REPLACE(MID(A13,FIND("#",A13&"#")+1,99)&"#",FIND("#",MID(A13,FIND("#",A13&"#")+1,99)&"#"),99,)

复制代码

3、

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

复制代码

ykfexcel

函中201301班作业1.rar (4.75 KB, 下载次数: 1)

2013-4-29 14:57 上传

点击文件名下载附件 【函中201301班】-B20-ykfexcel上交作业：
1、=LEN(A3)+LEN(A4)+LEN(A5)+LEN(A6)+LEN(A7)-LEN(SUBSTITUTE(A3,"小妖",1))-LEN(SUBSTITUTE(A4,"小妖",1))-LEN(SUBSTITUTE(A5,"小妖",1))-LEN(SUBSTITUTE(A6,"小妖",1))-LEN(SUBSTITUTE(A7,"小妖",1))

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

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