【函中201301班】第九讲作业A组上交贴

ws278106483

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

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

石岗

【函中201301班】- A23-石岗
第一题：=MID(SUBSTITUTE(NUMBERSTRING("5"&B94,3),"○","零"),2,7)：
第二题：{=MID(MATCH(MID(SUBSTITUTE("一"&B100,"零","○"),1,4),NUMBERSTRING(ROW($1:$20000),3),)&MATCH(MID(SUBSTITUTE("一"&B100,"零","○"),5,4),NUMBERSTRING(ROW($1:$20000),3),),2,7)}；
第三题：=NUMBERSTRING(ROUND(C107,0),3)：
第四题：{=MATCH(IF(MID(C115,1,1)="十","一"&C115,C115),NUMBERSTRING(ROW($1:$20000),1),)}；
第五题：=MIDB("五"&B124,SEARCHB("?","五"&B124),LEN("五"&B124)*2-LENB("五"&B124))；
第六题：=MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))。
请老师指正，本周有点忙，做题的时间少。

石岗

【函中201301班】- A23-石岗
第一题：=MID(SUBSTITUTE(NUMBERSTRING("5"&B94,3),"○","零"),2,7)：
第二题：{=MID(MATCH(MID(SUBSTITUTE("一"&B100,"零","○"),1,4),NUMBERSTRING(ROW($1:$20000),3),)&MATCH(MID(SUBSTITUTE("一"&B100,"零","○"),5,4),NUMBERSTRING(ROW($1:$20000),3),),2,7)}；
第三题：=NUMBERSTRING(ROUND(C107,0),3)：
第四题：{=MATCH(IF(MID(C115,1,1)="十","一"&C115,C115),NUMBERSTRING(ROW($1:$20000),1),)}；
第五题：=MIDB("五"&B124,SEARCHB("?","五"&B124),LEN("五"&B124)*2-LENB("五"&B124))；
第六题：=MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))。
请老师指正，本周有点忙，做题的时间少。

rlz680926

【函中201301班】-A24
           第一题：=IF(LEN(B94)=LEN(SUBSTITUTE(NUMBERSTRING(B94,3),"○","零")),SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"),"零"&SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"))
           第二题：=RIGHT(MATCH(LEFT(SUBSTITUTE("一"&B100,"零","○"),4),NUMBERSTRING(ROW($1:$9999),3),0)&MATCH(RIGHT(SUBSTITUTE("一"&B100,"零","○"),4),NUMBERSTRING(ROW($1:$9999),3),0),7)数组公式
  第三题：=NUMBERSTRING(ROUND(C107,0),3)
  第四题：=MATCH(SUBSTITUTE(SUBSTITUTE(IF(LEFT(C115,1)="十",SUBSTITUTE(C115,"十","一"),IF(RIGHT(C115,1)="十",SUBSTITUTE(C115,"十","○"),IF(RIGHT(C115,1)="百",SUBSTITUTE(C115,"百","○○"),IF(RIGHT(C115,1)="千",SUBSTITUTE(C115,"千","○○○"),C115)))),"百",),"十",),NUMBERSTRING(ROW($1:$1000),3),0)数组公式
第五题：=LEFT(B124,LEN(B124)*2-LENB(B124))
第六题：=MID(LEFT(B131,SEARCHB("?",B131)/2),FIND("市",LEFT(B131,SEARCHB("?",B131)/2))+1,LEN(LEFT(B131,SEARCHB("?",B131)/2)))

dididing

【函中201301班】- A18-dididing

第1题 ：

1. =SUBSTITUTE(RIGHT(NUMBERSTRING(1&B94,3),LEN(B94)),"○","零")

复制代码

第2题：

1. =LEFT(IF(LEFT(B100)="零",0,"")&SUM((FIND(MID(B100,ROW($1:$15),1),"零一二三四五六七八九")-1)*10^(15-ROW($1:$15))),LEN(B100))

复制代码

第3题：

1. =NUMBERSTRING(ROUND(C107,0),3)

复制代码

第4题：

1. =+MATCH("*"&C115,NUMBERSTRING(ROW($1:999),1),)

复制代码

第5题：

1. =MIDB(B124,SEARCHB("?",B124),LEN(B124)*2-LENB(B124))

复制代码

第6题：

1. =MID(B131,FIND("市",B131)+1,MATCH(1,LENB(MID(B131,ROW($1:$50),1)),)-FIND("市",B131)-1)

复制代码

myszcpf

【函中201301班】-A15-MYSZCPF
第1题：

1. =IF(LEFT(B94,1)<>"0",SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"),"零"&SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"))

复制代码

第2题：

1. {=IF(LEFT(B100,1)>"零",MATCH(LEFT(SUBSTITUTE(B100,"零","○"),3),NUMBERSTRING(ROW($1:$9999),3),0)&MATCH(RIGHT(SUBSTITUTE(B100,"零","○"),4),NUMBERSTRING(ROW($1:$9999),3),0),"0"&MATCH(MID(SUBSTITUTE(B100,"零","○"),2,2),NUMBERSTRING(ROW($1:$9999),3),0)&MATCH(RIGHT(SUBSTITUTE(B100,"零","○"),4),NUMBERSTRING(ROW($1:$9999),3),0))}

复制代码

第3题：

1. =NUMBERSTRING(ROUND(C107,0),3)

复制代码

第4题：

1. {=IF(LEFT(C115,1)="十",MATCH(C115,TEXT(ROW($1:$1000),"[dbnum1][$-411]G/通用格式"),),MATCH(C115,TEXT(ROW($1:$1000),"[dbnum1][$-404]G/通用格式"),))}

复制代码

第5题：

1. =LEFT(B124,2*LEN(B124)-LENB(B124))

复制代码

第6题：

1. {=MID(B131:G135,SEARCH("市",B131:G135)+1,SEARCH({"a";"b";"b";"a";"c"},B131:G135)-SEARCH("市",B131:G135)-1)}

复制代码

蓝火雨

【函中201301班】- A21-蓝火雨
1、=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F94,"萬",""),"百",""),"千",""),"○","零"),"十","")
2、在F2单元格输入：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B100,"一","1"),"二","2"),"三","3"),"四","4"),"五","5"),"六","6"),"七","7"),"八","8")，在G2单元格输入：=SUBSTITUTE(SUBSTITUTE(F100,"九","9"),"零",0)
3、=NUMBERSTRING(C107,3)
4、=MATCH(C115,SUBSTITUTE(SUBSTITUTE(TEXT(ROW($1:$1000),"[DBNum1]"),"一十","十"),"○","零"),)三键回车
5、=LEFT(B124,LEN(B124)*2-LENB(B124))
6、=MIDB(B131,FIND("市",B131)*2,SEARCHB("?",B131)-FIND("市",B131)*2)

风林火山

1         =RIGHT(SUBSTITUTE(NUMBERSTRING(1&B95,3),"○","零"),7)                                                       
2         =MID(SUM(IFERROR(FIND(MID("一"&B100,ROW($1:8),1),"一二三四五六七八九"),)*10^(8-ROW($1:8))),2,7)                                                       
3         =NUMBERSTRING(ROUND(C107,),3)                                                       
4         =MATCH(C115,TEXT(ROW($1:$1000),"[<20][dbnum1]d;[dbnum1]"),)                                                       
5         =LEFT(B124,LEN(B124)*2-LENB(B124))                                                       
6          =REPLACEB(LEFTB(B131,SEARCHB("?",B131)-1),1,SEARCHB("市",B131),"")                                                       

as0810114

【函中201301班】-A12-as0810114
1.   =RIGHT(SUBSTITUTE(NUMBERSTRING(1&B94,3),"○","零"),7)
2.   =RIGHT(SUM((FIND(MID("一"&B100,ROW($1:$8),1),"零一二三四五六七八九")-1)*10^(8-ROW($1:$8))),7)
3.   =NUMBERSTRING(ROUND(C107,),3)
4.  取巧的方法     =IFERROR(MATCH(C115,NUMBERSTRING(ROW($1:999),1),),18)
5.    =LEFT(B124,2*LEN(B124)-LENB(B124))
6.   =MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))

十五的月亮

【函中201301班】- A14-十五的月亮
一题

1. =REPT("零",7-LEN(NUMBERSTRING(B94,3)))&SUBSTITUTE(NUMBERSTRING(B94,3),NUMBERSTRING(0,3),"零")

复制代码

二题：

1. =TEXT(SUM(--IFERROR(FIND(MID(B100,ROW($1:$7),1),"一二三四五六七八九○"),)*10^(7-ROW($1:$7))),"0000000")

复制代码

三题：

1. =NUMBERSTRING(ROUND(C107,),3)

复制代码

四题：

1. =MATCH(C115,IF(ROW($1:$1000)<20,TEXT(ROW($1:$1000),"[DBNUM1][$-411]G/通用格式"),TEXT(ROW($1:$1000),"[DBNUM1][$-404]G/通用格式")),)

复制代码

五题：

1. =MIDB(B124,SEARCHB("?",B124),LEN(B124)*2-LENB(B124))

复制代码

六题：

1. =MIDB(B131,SEARCHB("市",B131)+2,SEARCHB("?",B131)-SEARCHB("市",B131)-2)

复制代码

4题用[<20]不知道什么原因做不出，开贴后再看吧，辛苦