【函中201301班】第九讲作业D组上交贴（已开帖）

顺⑦.zì繎。

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

yunqingzi

【函中201301班】- D13-yunqingzi
1、=SUBSTITUTE(NUMBERSTRING(B94,3),"○","零")
改为=IF(LEN(NUMBERSTRING(B94,3))<>LEN(B94),"零"&SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"),SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"))
2、{=RIGHT(0&SUM((FIND(MID(B100,ROW($1:$7),1),"零一二三四五六七八九")-1)*10^(7-ROW($1:$7))),7)}
3、=NUMBERSTRING(ROUND(C107,0),3)
4、{=MAX((TEXT(ROW($1:$1000),"[<28][DBNUM1]d;[DBNUM1]")=C115)*ROW($1:$1000))}
5、=MIDB(B124,SEARCHB("?",B124),2*LEN(B124)-LENB(B124))
6、=MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))

liziyuliziyu7

【函中201301班】-d04-liziyuliziyu7
=IF(--RIGHT(B94)=0,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B94,"[dbnum1]"),"百",),"十",),"千",),"万",),"○","零")&"零",IF(--LEFT(B94)=0,"零"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B94,"[dbnum1]"),"百",),"十",),"千",),"万",),"○","零"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B94,"[dbnum1]"),"百",),"十",),"千",),"万",),"○","零")))

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

=NUMBERSTRING(ROUND(C107,),3)

=MATCH(IF(LEFT(C115)="十","一"&C115,C115),NUMBERSTRING(ROW($1:$1000),1),)

=LEFT(B124,MATCH(1,0/MID(B124,ROW($1:$15),1)))

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

cg372101

【函中201301班】- D18-cg372101
题1：

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

复制代码

题2（数组公式）：

1. =TEXT(SUM((MATCH(MID(SUBSTITUTE(B100,"零","○"),COLUMN(A:G),1),NUMBERSTRING(ROW($1:$10)-1,3),)-1)*10^(7-COLUMN(A:G))),"0000000")

复制代码

题3：

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

复制代码

题4（数组公式）：

1. =MATCH(C115,TEXT(ROW($1:$9999),"[<20][DBNUM1]d;[DBNUM1]"),)

复制代码

1. =MAX((TEXT(ROW($1:$9999),"[<20][DBNUM1]d;[DBNUM1]")=C115)*ROW($1:$9999))

复制代码

题5：

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

复制代码

题6：

1. =REPLACE(LEFT(B131,SEARCHB("?",B131)/2),1,FIND("市",B131),)

复制代码

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

复制代码

题7：

笨笨四

【函中201301班】- D09-笨笨四；
1.=SUBSTITUTE(MID(TEXT("0."&B94&"01","[DBNUM1]"),3,7),"○","零")
2.=RIGHT(0&SUM((FIND(MID(B100,ROW($1:$7),1),"零一二三四五六七八九")-1)*10^(7-ROW($1:$7))),7)
3.=NUMBERSTRING(INT(C107+0.5),3)4.=MATCH(IF(LEFT(C115)="十","一"&C115,C115),TEXT(ROW($1:$9999),"[DBNUM1][$-404]G/通用格式"),)
5.=LEFT(B124,MAX((LENB(MID(B124,ROW(INDIRECT("1:"&LEN(B124))),1))=1)*ROW(INDIRECT("1:"&LEN(B124)))))
6.=MID(B131,FIND("市",B131)+1,MATCH(1>1,CODE(MID(B131,ROW($1:$99),1))>122,)-FIND("市",B131)-1)

岁月静好

【函中201301班】-D24-岁月静好
听了两遍了，但我一个也不会做寿，再加油一下

林玲玲

【函中201301班】- D11-林玲玲
1、=SUBSTITUTE(SUBSTITUTE(NUMBERSTRING("1"&B94,3),"○","零"),"一","",1)
2、=RIGHT(SUM(RIGHT(FIND(MID("一"&B100,ROW($1:$8),1),"一二三四五六七八九零"),1)*10^(8-ROW($1:$8))),7)
3、=NUMBERSTRING(ROUND(C107,),3)
4、=MATCH(IF(LEFT(C115,1)="十","一"&C115,C115),NUMBERSTRING(ROW($1:$1000),1),)
5、=LEFT(B124,LEN(B124)*2-LENB(B124))
6、=MIDB(B131,FINDB("市",B131),SEARCHB("?",B131)-FINDB("市",B131)

veryloveep

【函中201301班】- D22-veryloveep
1.=IF(LEN(SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"))<7,"零"&SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"),SUBSTITUTE(NUMBERSTRING(B94,3),"○","零"))
2.
3.=NUMBERSTRING(ROUND(C107,0),3)
4.=IFERROR(MATCH(C115,TEXT(ROW($1:$1000),"[dbnum1]"),0),18)
5.=MID(B124,1,2*LEN(B124)-LENB(B124))
6.

1982zyh

【函中201301班】- D16-1982zyh

1.=MID(SUBSTITUTE(NUMBERSTRING("1"&B94,3),"○","零"),2,LEN(B94))
2.=IF(MID(B100,1,1)="零","0",FIND(MID(B100,1,1),NUMBERSTRING(1234567890,3)))&IF(MID(B100,2,1)="零","0",FIND(MID(B100,2,1),NUMBERSTRING(1234567890,3)))&IF(MID(B100,3,1)="零","0",FIND(MID(B100,3,1),NUMBERSTRING(1234567890,3)))&IF(MID(B100,4,1)="零","0",FIND(MID(B100,4,1),NUMBERSTRING(1234567890,3)))&IF(MID(B100,5,1)="零","0",FIND(MID(B100,5,1),NUMBERSTRING(1234567890,3)))&IF(MID(B100,6,1)="零","0",FIND(MID(B100,6,1),NUMBERSTRING(1234567890,3)))&IF(MID(B100,7,1)="零","0",FIND(MID(B100,7,1),NUMBERSTRING(1234567890,3)))
3.=NUMBERSTRING(ROUND(C107,1),3)
5.=MIDB(B124,SEARCHB("?",B124),2*LEN(B124)-LENB(B124))
6.=MID(B131,FIND("市",B131)+1,SEARCHB("?",B131)/2-FIND("市",B131))

第四题不会，第二题等看最佳答案

ligh1298

1题

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

复制代码

2.数组

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

复制代码

3.

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

复制代码

4.数组

1. =MAX((TEXT(ROW(1:1000),"[<28][DBNUM1]d;[DBNUM1]")=C115)*ROW(1:1000))

复制代码

5.

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

复制代码

6.

1. =MID(B131,FIND("市",B131)+1,IF(ISNUMBER(SEARCH("a",B131)),SEARCH("a",B131),IF(ISNUMBER(SEARCH("b",B131)),SEARCH("b",B131),SEARCH("c",B131)))-FIND("市",B131)-1)

复制代码