【函中201301班】第二讲C组作业上交帖【已开贴，评分完成】

CheryBTL

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

下附课程相关链接：
函中201301班第二讲课件课后完整版
函中201301班第二讲作业

xuyouxm

【函中201301班】-C21-xuyouxm

01题：

1. =IF(B4<=0,0,IF(B4<200,B4,200))

复制代码

02题：

1. =IF(B13<100,0,IF(B13<200,1,IF(B13<300,2,IF(B13<400,3,4))))

复制代码

03题：

1. =IF(B22=120,"S",IF(B22<60,"D",IF(B22<80,"C",IF(B22<100,"B","A"))))

复制代码

04题：

1. =IF(B34=C34,TRUE,FALSE)

复制代码

1. =B34=C34

复制代码

1. =IF(B34<>C34,FALSE,TRUE)

复制代码

05题：

1. =IF(B46<>0,"有","无")

复制代码

06题：

1. =SUM(IF((B56:B64>70)*(B56:B64<90),1))

复制代码

1. =SUM((B56:B64>70)*(B56:B64<90))

复制代码

1. =SUMPRODUCT((B56:B64>70)*(B56:B64<90))

复制代码

07题：

1. =SUM((B68:B76<60)+(B68:B76>90))

复制代码

1. =COUNTIF(B68:B76,"<60")+COUNTIF(B68:B76,">90")

复制代码

1. =SUMPRODUCT((B68:B76<60)+(B68:B76>90))

复制代码

1. =SUM(IF((B68:B76<60)+(B68:B76>90),1))

复制代码

cir.s

【函中201301班】-C12-cir.s
第一题：   =MAX(MIN(B4,200),0)
      或   =IF(B4>=200,200,IF((B4<200)*(B4>0),B4,0))
第二题：   =IF((B13>=0)*(B13<100),0,LEFT(B13,1)*1)
第三题：   =IF(B22=120,"S",IF((B22<120)*(B22>=100),"A",IF((B22<100)*(B22>=80),"B",IF((B22<80)*(B22>=60),"C","D"))))
第四题：   =IF(B34=C34,TRUE,FALSE)
       或  =B34=C34
第五题：=IF(B46<>0,"有","无")
第六题：=SUM(IF(((B56:B64)<90)*((B56:B64)>70),1,0))三键回车
第七题：=SUM(IF((((B68:B76)>90)+((B68:B76)<60)),1,0))三键回车

qqyyh

【函中201301班】-C06-qqyyh:
1.=IF(B4>200,200,IF(B4>0,B4,0))
   =IF(B4<0,0,IF(B4<200,B4,200))
2.=IF(B13<100,0,IF(B13<200,1,IF(B13<300,2,IF(LEN(B13)=3,LEFT(B13,1),LEFT(B13,2)))))
   =IF(B13>=300,IF(LEN(B13)=3,LEFT(B13,1),LEFT(B13,2)),IF(B13>=200,2,IF(B13>=100,1,0)))
3.=IF(B22=120,"S",IF((B22>=0)*(B22<60),"D",IF((B22>=60)*(B22<80),"C",IF((B22>=80)*(B22<100),"B",IF((B22>=100)*(B22<120),"A","?")))))
   =IF(B22=120,"S",IF((B22>=100)*(B22<120),"A",IF((B22>=80)*(B22<100),"B",IF((B22>=60)*(B22<80),"C",IF((B22>=0)*(B22<60),"D","?")))))
4.=IF(B34=C34,"TRUE","FALSE")
5.=IF(B46<>0,"有","无")
   =IF(B46=0,"无","有")
6.=SUM(IF((B56:B64>=70)*(B56:B64<90),1,0)) 数组公式
  =COUNTIF(B56:B64,">=70")-COUNTIF(B56:B64,">=90")
7.=SUM(IF((B68:B76<60)+(B68:B76>90),1,0))  数组公式
  =COUNTIF(B68:B76,"<60")+COUNTIF(B68:B76,">90")

xiaoni

【函中201301班】-C15-xiaoni上交第2讲作业
第一题：=(B4>=200)*200+(B4<200)*(B4>=0)*B4公式下拉
第二题：=(B13>=100)*(B13<200)*1+(B13<300)*(B13>=200)*2+(B13>=300)*3公式下拉
第三题：公式1=IF(B22<60,"D",IF(B22<80,"C",IF(B22<100,"B",IF(B22<120,"A","S"))))公式下拉或者是： 公式2==LOOKUP(B22,{0,60,80,100,120},{"D","C","B","A","S"})
第四题：=B34=C34
第五题：=IF(B46,"有","无")
第六题：=SUM(IF((B56:B64>=70)*(B56:B64<90),1))或者是：=SUM((B56:B64>=70)*(B56:B64<90)*1)
第七题：=SUM(IF((B68:B76<=60)+(B68:B76>90),1))或者是：=SUM(((B68:B76<60)+(B68:B76>90))*1)

womohe

1

1. =MAX(MIN(B4,200),0)

复制代码

2

1. =INT(B13/100)

复制代码

3

1. =IF(B22<60,"D",IF(B22<80,"C",IF(B22<100,"B",IF(B22<120,"A","S"))))

复制代码

4

1. =B34=C34

复制代码

5

1. =IF(B46,"有","无")

复制代码

6

1. =SUM(IF((B56:B64>=70)*(B56:B64<=90),1)) 三键

复制代码

7

1. =SUM(IF((B68:B76<60)+(B68:B76>90),1)) 三键

复制代码

孤单无所谓

【函中201301班】-C04孤单无所谓     以为作业都要用if。就全用了if。
1

1. =MEDIAN(B4,,200)

复制代码

1. =MAX(,MIN(200,B4))

复制代码

1. =IF(B4>200,200,(B4>0)*B4)

复制代码

1. =TEXT(B4,"[<0]!0;[<200]0;2!0!0")

复制代码

2

1. =INT(B13%)

复制代码

1. =IF((B13>=0)*(B13<100),0,IF((B13>=100)*(B13<200),1,IF((B13>=200)*(B13<300),2,3)))

复制代码

3

1. =MID("DCBAS",MAX(B22/20-1,1),1)

复制代码

1. =LOOKUP(B22,{0,60,80,100,120;"D","C","B","A","S"})

复制代码

1. =IF(B22=120,"S",IF((B22<120)*(B22>=100),"A",IF((B22<100)*(B22>=80),"B",IF((B22<80)*(B22>=60),"C","D"))))

复制代码

4

1. =IF(B34=C34,2=2,2>5)

复制代码

1. =B34=C34

复制代码

5

1. =IF(B46,"有","无")

复制代码

6

1. =SUM((B56:B64>70)*(B56:B64<90))

复制代码

1. =SUM(IF((B56:B64>70)*(B56:B64<90),1))

复制代码

7

1. =SUM(IF(B68:B76<60,1,IF(B68:B76>90,1)))

复制代码

1. =SUM((B68:B76<60)+(B68:B76>90))

复制代码

雨后的风

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

1. =IF(B4>=200,200,IF(B4<=0,0,B4))

复制代码

1. =TEXT(B4,"[>200]2!0!0;[>0];\0")

复制代码

1. =MIN(MAX(B4,),200)

复制代码

第二题：

1. =IF(B13<100,0,IF(B13<200,1,IF(B13<300,2,3)))

复制代码

1. =INT(B13*1%)

复制代码

1. =TEXT(TEXT(B13*1%,"[>=2]0;[>=1]1;!0"),"[>=3]3;[>=2]2;0")

复制代码

第三题：

1. =IF(B22=120,"S",IF(B22<60,"D",IF(B22<80,"C",IF(B22<100,"B","A"))))

复制代码

1. =LOOKUP(B22/10,{0,"D";6,"C";8,"B";10,"A";12,"S"})

复制代码

1. =MID("DDDCBAS",INT(B22/20)+1,1)

复制代码

第四题:

1. =IF(B34=C34,TRUE)

复制代码

1. =IFERROR(B34=C34,)

复制代码

1. =B34=C34

复制代码

第五题：

1. =IF(B46,"有","无")

复制代码

1. =TEXT(B46,"有;有;无")

复制代码

1. =IF(ISERR(0/B46),"无","有")

复制代码

第六题：
数组公式

1. =SUM(IF((B56:B64<=70)+(B56:B64>=90),,1))

复制代码

1. =SUM(IF((B56:B64>70)*(B56:B64<90),1))

复制代码

1. =COUNT(IF((B56:B64<=70)+(B56:B64>=90),"",1))

复制代码

第七题：
数组公式

1. =SUM(IF((B68:B76>90)+(B68:B76<60),1))

复制代码

1. =COUNT(IF((B68:B76>90)+(B68:B76<60),1))

复制代码

1. =MMULT(COLUMN(A:I)^0,IF((B68:B76>90)+(B68:B76<60),1,))

复制代码

xukaiiii

隐藏的那么深！我学习一下