本帖最后由 lisa19860622 于 2013-5-6 10:22 编辑
【函中201301班】-c19-lisa19860622
1.- =IF(B4>=200,200,IF(B4>0,B4,0))
复制代码 2.
法1:- =if(b13<>"",int(b13/100),)
复制代码 法2:3.
法1:lookup- =LOOKUP(B22,{0,60,80,100,120},{"d","c","b","a","s"})
复制代码 法2:vlookup- =VLOOKUP(B22,{0,"d";60,"c";80,"b";100,"a";120,"s"},2,1)
复制代码 法3:if- =IF(B22>=120,"s",IF(B22>=100,"a",IF(B22>=80,"b",IF(B22>=60,"c","d"))))
复制代码 4.
法1:5.
法1:text法2:if6.
法1:countifs- =COUNTIFS(B56:B64,">=70",B56:B64,"<=90")
复制代码 法2:SUMPRODUCT- =SUMPRODUCT((B56:B64>=70)*(B56:B64<=90))
复制代码 法3:sum+if数组- =SUM(IF(B56:B64>70,1,))-SUM(IF(B56:B64>90,1,))
复制代码 7.
法1:countif- =COUNTIF(B68:B76,"<60")+COUNTIF(B68:B76,">90")
复制代码 法2:SUMPRODUCT- =SUMPRODUCT((B68:B76<60)*1)+SUMPRODUCT((B68:B76>90)*1)
复制代码 法3:sum+if数组- =SUM(IF(B68:B76<60,1))+SUM(IF(B68:B76>90,1,))
复制代码 作业是全做了,有些题可能用了取巧的方法,但好像解题没做要求,我也就这么写上去了.
开始没有用全if,后面在班里问了学员,说全要用if作答,才回来再次编辑. |