【函中20130301】第九讲C组作业上交帖【开贴】

CheryBTL

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

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

CheryBTL

占位，中午抽空做了一下：

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

复制代码

谢谢雨后的风的提醒，第一题正确公式应为（上式未考虑首数字为0的情况）：

1. =MID(SUBSTITUTE(NUMBERSTRING(1&B94,3),"○","零"),2,7)

复制代码

1. =MID(SUM((FIND(MID(B100,ROW($1:7),1),"零一二三四五六七八九")-1)/10^ROW($1:7)+1%^7),3,7)

复制代码

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

复制代码

1. =MATCH(C115,SUBSTITUTE(NUMBERSTRING(ROW(A:A),1),"一十","十"),)
   
2. =MATCH("*"&C115,NUMBERSTRING(ROW(A:A),1),)

复制代码

1. =LEFT(B124,2*LEN(B124)-LENB(B124))
   
2. =-LOOKUP(1,-LEFT(B124,ROW(A:A)))

复制代码

1. =MID(LEFTB(B131,SEARCHB("?",B131)-1),FIND("市",B131)+1,9)

复制代码

雨后的风

【函中201301班第九讲作业】-C10-雨后的风
第一题：

1. =MID(SUBSTITUTE(NUMBERSTRING(1&B94,3),NUMBERSTRING(0,3),"零"),2,9)

复制代码

1. =RIGHT(SUBSTITUTE(NUMBERSTRING(10^7+B94,3),NUMBERSTRING(0,3),"零"),7)

复制代码

第二题：

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

复制代码

1. =MID(SUM(IFERROR(FIND(MID("一"&B100,COLUMN(A:H),1),"一二三四五六七八九")*10^(8-COLUMN(A:H)),)),2,7)

复制代码

1. =TEXT(SUM(IFERROR(FIND(MID(B100,COLUMN(A:G),1),"一二三四五六七八九"),)*10^(7-COLUMN(A:G))),REPT(0,7))

复制代码

第三题：

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

复制代码

1. =SUBSTITUTE(TEXT(ROUND(C107,),"[DBNUM1]"),"十",)

复制代码

第四题：

1. =MATCH(C115,SUBSTITUTE(NUMBERSTRING(ROW($1:$2000),1),"一十","十"),)

复制代码

1. =MATCH(C115,SUBSTITUTE(TEXT(ROW($1:$2000),"[DBNUM1]"),"一十","十"),)

复制代码

第五题：

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

复制代码

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

复制代码

第六题：

1. =MID(LEFT(B131,SEARCHB("?",B131)/2),FIND("市",B131)+1,9)

复制代码

1. =MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))

复制代码

lisa19860622

【函中20130301】-c19-lisa19860622
题一:

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

复制代码

题二数组)

1. =IF(LEFT(B100)="零",0,)&SUM((MATCH(MID($B100,ROW($1:$7),1),{"零";"一";"二";"三";"四";"五";"六";"七";"八";"九"},)-1)*10^(7-ROW($1:$7)))&""

复制代码

题三:

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

复制代码

题四数组)

1. =IFERROR(MATCH(C115,TEXT(ROW($1:$1000),"[DBNUM1][$-411]G/通用格式"),),MATCH(C115,TEXT(ROW($1:$1000),"[dbnum1][$-412]g/通用格式"),))

复制代码

题五:

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

复制代码

题六:

1. =MID(MID(B131,FIND("市",B131),99),1,SEARCHB("?",MID(B131,FIND("市",B131),99))/2)

复制代码

学委,作业是搞完了,但感觉这式好像有简化的地方,但本人又不短如何去简化,试了好多种,都不成功.

qqyyh

【函中201301班】-C06-qqyyh
1.=IF(LEFT(B94,1)="0","零"&SUBSTITUTE(NUMBERSTRING($B94,3),"○","零"),SUBSTITUTE(NUMBERSTRING($B94,3),"○","零"))
   =MID(SUBSTITUTE(NUMBERSTRING("1"&B94,3),"○","零"),2,15)
2.=RIGHT(SUM(IFERROR((FIND(MID("一"&B100,COLUMN(A1:H1),1),"一二三四五六七八九○"))*10^(8-COLUMN(A1:H1)),0)),7)  2010版 数组公式
  =IF(LEFT(B100,1)="零","0"&SUM((FIND(MID(SUBSTITUTE(B100,"零","○"),COLUMN(A1:G1),1),"○一二三四五六七八九")-1)*10^(7-COLUMN(A1:G1))),SUM((FIND(MID(SUBSTITUTE(B100,"零","○"),COLUMN(A1:G1),1),"○一二三四五六七八九")-1)*10^(7-COLUMN(A1:G1)))) 数组公式
3.=NUMBERSTRING(ROUND(C107,0),3)
4.=MATCH(C122,TEXT(ROW($1:$2000),"[<20][DBNUM1][$-411]G/通用格式;[>=20][DBNUM1][$-404]G/通用格式"),0) 数组公式   2010版出现错误，不解？
5.=LEFT(B124,LEN(B124)*2-LENB(B124))
6.=MIDB(B131,SEARCHB("市",B131)+1,SEARCHB("?",B131)-SEARCHB("市",B131)-1)

小志

【函中201301班】-C16-小志
第一题：

1. =SUBSTITUTE(MID(NUMBERSTRING(1&B94,3),2,99),NUMBERSTRING(0,3),"零")

复制代码

第二题：

1. =MID(MATCH(LEFT("一"&B100,4),SUBSTITUTE(NUMBERSTRING(ROW($1000:$9999),3),NUMBERSTRING(0,3),"零"),)+999&MATCH(RIGHT(B100,4),SUBSTITUTE(NUMBERSTRING(ROW($1000:$9999),3),NUMBERSTRING(0,3),"零"),)+999,2,7)

复制代码

第三题：

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

复制代码

第四题：

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

复制代码

第五题：

1. =-LOOKUP(,-LEFT(B124,ROW($1:$9)))

复制代码

第六题：

1. =MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))

复制代码

pang1688

【函中201301班】-C14-pang1688
1、

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

复制代码

2、

1. =SUBSTITUTE(FIND(MID(B100,1,1),"一二三四五六七八九零")&FIND(MID(B100,2,1),"一二三四五六七八九零")&FIND(MID(B100,3,1),"一二三四五六七八九零")&FIND(MID(B100,4,1),"一二三四五六七八九零")&FIND(MID(B100,5,1),"一二三四五六七八九零")&FIND(MID(B100,6,1),"一二三四五六七八九零")&FIND(MID(B100,7,1),"一二三四五六七八九零"),"10","0")

复制代码

3、

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

复制代码

4、

1. =IF(LEFT(C115,1)="十",MATCH("一"&C115,NUMBERSTRING(ROW($A$1:$A$1000),1),0),MATCH(C115,NUMBERSTRING(ROW($A$1:$A$1000),1),0))  数组

复制代码

5、

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

复制代码

6、

1. =MID(B131,FIND("市",B131)+1,(SEARCHB("?",B131)-FIND("市",B131)*2)/2)

复制代码

kouhogliang

【函中201301班】-C08-kouhogliang

1

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

复制代码

下拉
2

1. =MID((SUM((FIND(MID(B100,8-ROW($1:$7),1),"零一二三四五六七八九")-1)*10^(ROW($1:$7)-1))+10^7),2,7)
   

复制代码

三键结束
下拉
3

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

复制代码

下拉
5

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

复制代码

下拉
6

1. =MID(MID(B131,FIND("市",B131)+1,99),1,MIN(SEARCH({"a";"b";"c"},MID(B131,FIND("市",B131)+1,99)&"abc",1)-1))
   

复制代码

下拉

xiaoni

【函中201301班】-C15-xiaoni上交第九课作业
第一题：
第二题=IF(LEFT(B100)<>"零",SUM((FIND(MID(B100,ROW(INDIRECT("1:"&LEN(B100))),1),"零一二三四五六七八九")-1)*10^(7-ROW($1:$7))),0&SUM((FIND(MID(B100,ROW(INDIRECT("1:"&LEN(B100))),1),"零一二三四五六七八九")-1)*10^(7-ROW($1:$7))))
第三题：=NUMBERSTRING(ROUND(C107,),3)
第四题：=MATCH(IF(LEFT(C115)="十",REPLACE(C115,1,,"一"),C115),NUMBERSTRING(ROW($1:$2000),1),)
第五题：=MIDB(B124,SEARCHB("?",B124),LEN(B124)*2-LENB(B124))
第六题：=MIDB(B131,FINDB("市",B131)+1,(SEARCHB("?",B131)-1)-FINDB("市",B131))

cir.s

【函中201301班】-C12-Cir.s
第一题   =MID(SUBSTITUTE(NUMBERSTRING(2&B94,3),NUMBERSTRING(0,3),"零"),2,16)
第二题   =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B100,"一","1"),"二","2"),"三","3"),"四","4"),"五","5"),"六","6"),"七","7"),"八","8"),"九","9"),"零","0")
第三题   =NUMBERSTRING(ROUND(C107,0),3)
第四题  
第五题   =LEFT(B124,(LEN(B124)*2-LENB(B124)))
第六题   =MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))