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

Shelvee

【201301函中1班】—D15—Shelvee

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

=IFERROR(REPLACE(MID(A13,FIND("#",A13)+1,20),FIND("#",MID(A13,FIND("#",A13)+1,20)),10,),"")

=IF(INT(ROUND(A23,1))<1,NUMBERSTRING(INT(ROUND(A23,1)*10),2)&"角",IF(LEN(SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1),"[dbnum2]")&".",FIND(".",TEXT(ROUND(A23,1),"[dbnum2]")&"."),1,"元"),".","角"))=FIND("元",SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1),"[dbnum2]")&".",FIND(".",TEXT(ROUND(A23,1),"[dbnum2]")&"."),1,"元"),".","角")),SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1),"[dbnum2]")&".",FIND(".",TEXT(ROUND(A23,1),"[dbnum2]")&"."),1,"元"),".","角")&"整",SUBSTITUTE(REPLACE(TEXT(ROUND(A23,1),"[dbnum2]")&".",FIND(".",TEXT(ROUND(A23,1),"[dbnum2]")&"."),1,"元"),".","角")))

cbg2008

练一
方法1 =SUM(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",1)))
方法2 =SUM(N(MID(A3:A7,COLUMN(A:Z),2)="小妖"))
练二
方法1 =TRIM(MID(SUBSTITUTE(A13&"##","#",REPT(" ",50)),51,50))
方法2 =MID(A13,FIND("#",A13&"#")+1,FIND("*",SUBSTITUTE(A13&"##","#","*",2))-FIND("#",A13&"#")-1)
练三
  =TEXT(ROUND(A23,1),"[>=1]"&NUMBERSTRING(INT(ROUND(A23,1)),2)&"元"&IF(RIGHT(ROUND(A23*10,),1)="0","整",NUMBERSTRING(RIGHT(ROUND(A23,1)*10,1),2)&"角")&";[=0]零元整;"&NUMBERSTRING(RIGHT(ROUND(A23*10,),1),2)&"角")

xcnclzy

1. =LEN(PHONETIC(A3:B7))-LEN(SUBSTITUTE(PHONETIC(A3:B7),"小妖",1))
   
2. =SUBSTITUTE(LEFT(MID(A13,FIND("#",A13&"#")+1,99)&"#",FIND("#",MID(A13,FIND("#",A13&"#")+1,99)&"#")),"#",)
   
3. =IF(ROUND(A23,1)>=1,NUMBERSTRING(INT(ROUND(A23,1)),2)&"元"&IF(ROUND(A23,1)=INT(ROUND(A23,1)),"整",IF(--RIGHT(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*100),1)>0,NUMBERSTRING(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*10)+1,2)&"角",NUMBERSTRING(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*10),2)&"角")),IF(--RIGHT(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*100),1)>0,NUMBERSTRING(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*10)+1,2)&"角",IF(--RIGHT(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*10),1)>0,NUMBERSTRING(INT((ROUND(A23,1)-INT(ROUND(A23,1)))*10),2)&"角","零元整")))
   
4. =IF(ROUND(A23,1)<0.1,"零元整",SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(INT(ROUND(A23,1)),2)&"元"&NUMBERSTRING((ROUND(A23,1)-INT(ROUND(A23,1)))*10,2)&"角","零角","整"),"零元",""))
   

复制代码

【函中201301班]- D19-xcnclzy

veryloveep

【函中201301班]- D22-veryloveep
1.

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

复制代码

2.

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

复制代码

3.

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

复制代码

zac0226

【函中201301班】-D02-zac0226 落后了，交作业，公式写得老长，没能领会好老师的讲课精神。惭愧！

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

复制代码

1. 2、=CHOOSE(LEN(A13)-LEN(SUBSTITUTE(A13,"#",))+1,"",MID(A13,FIND("#",A13)+1,99),MID(A13,FIND("#",A13)+1,FIND("#",A13,FIND("#",A13)+1)-(FIND("#",A13)+1)))

复制代码

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

复制代码

yunqingzi

[函中201301班]-D13.yunqingzi
1、{=(SUM(LEN(A3:A7))-SUM(LEN(SUBSTITUTE(A3:A7,"小妖",""))))/2}
2、=IFERROR(MID(SUBSTITUTE(A13,"#","*",1)&"#",FIND("*",SUBSTITUTE(A13,"#","*",1)&"#")+1,FIND("#",SUBSTITUTE(A13,"#","*",1)&"#")-(FIND("*",G13)+1)),"")
3、=NUMBERSTRING(INT(A23),2)&"元"&IF(A23=INT(A23),"整",NUMBERSTRING(INT((A23-INT(A23))*10),2)&"角"&NUMBERSTRING(RIGHT(A23*100),2)&"分")

xxm893032160

【函中201301班]- D17-xxm893032160
1.=SUMPRODUCT(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"小妖",1)))
2. =A13&"#"    =FIND("#",C13)
=MID(C13,D13+1,99)&"#"     =FIND("#",E13)   =LEFT(E13,F13-1)   
最后的公式是=LEFT(MID(A13&"#",FIND("#",A13&"#")+1,99)&"#",FIND("#",MID(A13&"#",FIND("#",A13&"#")+1,99)&"#")-1)
3.不会做。。

1982zyh

                                       

zxf2012

【函中201301班]- D07-excel学习
第一题：

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

复制代码

第二题：

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

复制代码

第三题：

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

复制代码

lucking551

【函中201301班】- D08-lucking551

1、答案：

1. <FONT size=3>=(LEN(PHONETIC(A3:A7))-LEN(SUBSTITUTE(PHONETIC(A3:A7),"小妖",0)))</FONT>

复制代码

2、答案：

1. =IF(ISERROR(FIND("#",A13)),"",IF(LEN(A13)-LEN(SUBSTITUTE(A13,"#",""))=1,RIGHT(A13,LEN(A13)-FIND("#",A13)),LEFT(RIGHT(A13,LEN(A13)-FIND("#",A13)),FIND("#",RIGHT(A13,LEN(A13)-FIND("#",A13)))-1)))

复制代码

3、答案：

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

复制代码