[已开]201401函中课前练习11:生成新报价

xcnclzy

1. =IF(A2=0,,IFERROR(IF(ISERR(--LEFT(A2,3)),0,LEFT(A2,FIND("/",A2)-1)+100)&"/"&IF(ISERR(--RIGHT(A2,3)),0,MID(A2,FIND("/",A2)+1,5)+100),A2+100))

复制代码

meililin

函中目标，学完后，跳出只会if的怪圈

1. c2=IF(A2=0,0,IF(ISERROR(FIND("/",A2)),A2+100,IF(--LEFT(A2,FIND("/",A2)-1)=0,0,LEFT(A2,FIND("/",A2)-1)+100)&"/"&IF(--RIGHT(A2,LEN(A2)-FIND("/",A2))=0,0,RIGHT(A2,LEN(A2)-FIND("/",A2))+100)))

复制代码

yorkchenshunan

=IF(A2=0,0,IF(ISERROR(FIND("/",A2)),A2+100,IF(LEFT(A2,FIND("/",A2)-1)="0",-100,LEFT(A2,FIND("/",A2)-1))+100&"/"&IF(RIGHT(A2,LEN(A2)-FIND("/",A2))="0",-100,RIGHT(A2,LEN(A2)-FIND("/",A2)))+100))

AmoKat

1. =SUBSTITUTE(SUBSTITUTE(A2,-LOOKUP(,-LEFT(A2,{1,2,3,4,5})),TEXT(100-LOOKUP(,-LEFT(A2,{1,2,3,4,5})),"[>100];;!0")),-LOOKUP(,-RIGHT(A2,{1,2,3,4,5})),TEXT(100-LOOKUP(,-RIGHT(A2,{1,2,3,4,5})),"[>100];;!0"))

复制代码

AmoKat

1. =TEXT(LEFT(A2,FIND("/",A2&"/")-1)+100,"[>100];;!0") & IF(ISERR(FIND("/",A2)),"",TEXT(MID(A2,FIND("/",A2&"/")+1,9)+100,"[>100]!/0;;!/!0"))

复制代码

AmoKat

1. =TEXT(LEFT(A2,FIND("/",A2&"/")-1)+100,"[>100];;!0")&TEXT(TEXT(MID(A2,FIND("/",A2&"/")+1,9),"0;;0;-!9!9!9")+100,"[>100]!/0;;!/!0;")

复制代码

箫风

答案复杂了，请指正！

1. =IF(ISERROR(FIND("/",A2)),IF(A2=0,A2,A2+100),LEFT(A2,FIND("/",A2)-1)+AND(FIND("/",A2)>0,(LEFT(A2,FIND("/",A2)-1)*1>0))*100&"/"&RIGHT(A2,LEN(A2)-FIND("/",A2))+AND(FIND("/",A2)>0,(RIGHT(A2,LEN(A2)-FIND("/",A2))*1>0))*100)
   

复制代码

天棋

方法好笨好笨

air05

还好没有开贴交作业了，神秘的高手滴答滴帮我看看公式，好长啊！      

简化前

1. =IF(--LEFT(A2,FIND("/",A2&"/")-1)=0,0,--LEFT(A2,FIND("/",A2&"/")-1)+100)&IFERROR("/"&IF(IFERROR(--MID(A2,FIND("/",A2&"/")+1,5),""),IFERROR(--MID(A2,FIND("/",A2&"/")+1,5),"")+100,0),"")

复制代码

简化后

1. =IF(--LEFT(A2,FIND("/",A2&"/")-1)=0,0,--LEFT(A2,FIND("/",A2&"/")-1)+100)&IFERROR("/"&IF(--MID(A2,FIND("/",A2&"/")+1,9)=0,0,--MID(A2,FIND("/",A2&"/")+1,9)+100),"")

复制代码

车仁静

1. =IF(--LEFT(A2)>0,INT(SUBSTITUTE(A2,"/","."))+100,0)&IF(TYPE(A2)=1,"","/"&IF(RIGHT(A2,2)="/0",0,100+MID(A2,FIND("/",A2)+1,5)))

复制代码