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

tough2010

【函中201301班]- B8113-tough2010
第一题：=LEN(A3&A4&A5&A6&A7)-LEN(SUBSTITUTE(A3&A4&A5&A6&A7,"小妖","g"))
第二题：=MID(MID(A13&"##",FIND("#",A13&"##")+1,88),1,FIND("#",MID(A13&"##",FIND("#",A13&"##")+1,88))-1)

stone1969

【函中201301班]- B16：stone1969(1109590007)
第一题
=(LEN(A3)-LEN(SUBSTITUTE(A3,"小妖",0)))+(LEN(A4)-LEN(SUBSTITUTE(A4,"小妖",0)))+(LEN(A5)-LEN(SUBSTITUTE(A5,"小妖",0)))+(LEN(A6)-LEN(SUBSTITUTE(A6,"小妖",0)))+(LEN(A7)-LEN(SUBSTITUTE(A7,"小妖",0)))+(LEN(B3)-LEN(SUBSTITUTE(B3,"小妖",0)))
第二种方法
=LEN(PHONETIC(A3:B7))-LEN(SUBSTITUTE(PHONETIC(A3:B7),"小妖",0))
第二题
=SUBSTITUTE(MID(SUBSTITUTE(A14,"#","#")&"#",FIND("#",SUBSTITUTE(A14,"#","#")&"#")+1,99),"#",)按住左键向下拖
=SUBSTITUTE(MID(SUBSTITUTE(A14,"#","#")&"#",FIND("#",D14)+1,99),"#",)

=SUBSTITUTE(MID(SUBSTITUTE(A15,"#","#")&"#",FIND("#",D15)+1,99),"#",)

=SUBSTITUTE(MID(SUBSTITUTE(A16,"#","#")&"#",FIND("#",D16)+1,99),"#",)

=SUBSTITUTE(MID(SUBSTITUTE(A17,"#","#")&"#",FIND("#",D17)+1,99),"#",)

=SUBSTITUTE(MID(SUBSTITUTE(A18,"#","#")&"#",FIND("#",D18)+1,99),"#",)

第三题

=SUBSTITUTE(SUBSTITUTE(REPLACE(MID(TEXT(ROUND(A23,1)+0.01,"[DBNum2]"),1,1+FIND(".",TEXT(ROUND(A23,1)+0.01,"[DBNum2]"))),FIND(".",TEXT(ROUND(A23,1)+0.01,"[DBNum2]"))+2,,"角"),"零角","整"),".","元")按住左键向下拖

月魄

=LEN(PHONETIC(A3:A7))-LEN(SUBSTITUTE(PHONETIC(A3:A7),"小妖",1))
=MID(MID(A13,FIND("#",A13&"#")+1,99),1,FIND("#",MID(A13,FIND("#",A13&"#")+1,99)&"#")-1)
【函中201301班]- B17-月魄.rar (4.95 KB, 下载次数: 1)

2013-5-2 13:48 上传

点击文件名下载附件

xzsychenwei

【函中201301班]- B04-xzsychenwei

第1题：

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

复制代码

第2题：

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

复制代码

第3题：

1. 不做了。

复制代码

wk0316

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

复制代码

1. =IF(IFERROR(FIND("#",A13)+1,0)=0,"",MID(A13,IFERROR(FIND("#",A13)+1,0),IFERROR(FIND("#",A13,IFERROR(FIND("#",A13)+1,0)),LEN(A13)+1)-IFERROR(FIND("#",A13)+1,0)))

复制代码

1. =IF(SUBSTITUTE(REPLACE(SUBSTITUTE(TEXT(ROUND(A23,1)+0.001,"[DBNum2][$-804]G/通用格式"),".","元"),FIND(".",TEXT(ROUND(A23,1)+0.001,"[DBNum2][$-804]G/通用格式"))+2,2,"角"),"零角","整")<>"零元整",SUBSTITUTE(SUBSTITUTE(REPLACE(SUBSTITUTE(TEXT(ROUND(A23,1)+0.001,"[DBNum2][$-804]G/通用格式"),".","元"),FIND(".",TEXT(ROUND(A23,1)+0.001,"[DBNum2][$-804]G/通用格式"))+2,2,"角"),"零角","整"),"零元",""),SUBSTITUTE(REPLACE(SUBSTITUTE(TEXT(ROUND(A23,1)+0.001,"[DBNum2][$-804]G/通用格式"),".","元"),FIND(".",TEXT(ROUND(A23,1)+0.001,"[DBNum2][$-804]G/通用格式"))+2,2,"角"),"零角","整"))

复制代码

【函中201301班]- B14-wk0316

MYHERE131

函中201301班】-B02-MYHERE131
练习一：=SUM((LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",)))/LEN("小妖"))     三键返回
练习二：=IF(ISERR(FIND("#",A13)),"",IF(LEN(A13)-LEN(SUBSTITUTE(A13,"#",))=1,RIGHT(A13,LEN(A13)-FIND("#",A13)),MID(A13,FIND("#",A13)+1,FIND("#",A13,FIND("#",A13)+1)-FIND("#",A13)-1)))
练习三：=IF(IF(INT(ROUND(A23,1))=ROUND(A23,1),TEXT(ROUND(A23,1),"[DBNUM2]")&"元整",TEXT(INT(ROUND(A23,1)),"[DBNUM2]")&"元"&TEXT(RIGHT(ROUND(A23,1),1),"[DBNUM2]")&"角")="零元整","零元整",SUBSTITUTE(IF(INT(ROUND(A23,1))=ROUND(A23,1),TEXT(ROUND(A23,1),"[DBNUM2]")&"元整",TEXT(INT(ROUND(A23,1)),"[DBNUM2]")&"元"&TEXT(RIGHT(ROUND(A23,1),1),"[DBNUM2]")&"角"),"零元",))

gaawx

【函中201301班]- B19GAAWX作业
1、=(LEN(A3&A4&A5&A6&A7)-LEN(SUBSTITUTE(A3&A4&A5&A6&A7,"小妖","1")))
2、=LEFT(MID(A13,FIND("#",A13)+1,100),FIND("#",MID(A13,FIND("#",A13)+1,100))-1)

深蓝色的海洋

【函中201301班]- B06-深蓝色的海洋
一、
辅助列
=LEN(A3)-LEN(SUBSTITUTE(A3,"小妖",0))
求和
=SUM(B3:B7)

二、
=IF(COUNTIF(A13,"*#*")=1,MID(A13,FIND("#",A13)+1,LEN(A13)-FIND("#",A13)+1))

=IF(COUNTIF(A13,"*#*#*")=1,MID(A13,FIND("#",A13)+1,FIND("#",A13,FIND("#",A13)+1)-FIND("#",A13)-1))

=IF(COUNTIF(A13,"*#*")=1,MID(A13,FIND("#",A13)+1,LEN(A13)-FIND("#",A13)+1),IF(COUNTIF(A13,"*#*#*")=1,MID(A13,FIND("#",A13)+1,FIND("#",A13,FIND("#",A13)+1)-FIND("#",A13)-1),""))

三、

ykfexcel

ykfexcel 发表于 2013-4-29 14:56
【函中201301班】-B20-ykfexcel上交作业：
1、=LEN(A3)+LEN(A4)+LEN(A5)+LEN(A6)+LEN(A7)-LEN(SUBSTITUTE( ...

谢谢，辛苦了，第一题原不知有PHONETIC函数，故只好笨做了

gdsgzhrsh

【函中201301班]- B08-gdsgzhrsh
1.=SUMPRODUCT((LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖","")))/2)
2.=IF(ISERROR(FIND("#",A13)),"",IF(LEN(A13)-LEN(SUBSTITUTE(A13,"#",""))<2,MID(A13,FIND("#",A13)+1,99),MID(A13,FIND("#",A13)+1,FIND("#",MID(A13,FIND("#",A13)+1,99))-1)))
3.=IF(ISERR(FIND(".",ROUND(A23,1))),NUMBERSTRING(A23,2)&"元整",IF(INT(A23),SUBSTITUTE(TEXT(ROUND(A23,1),"[DBNum2][$-804]G/通用格式"),".","元")&"角",SUBSTITUTE(TEXT(ROUND(A23,1),"[DBNum2][$-804]G/通用格式"),"零.",)&"角"))