【函数初级201203班】练习1---公式汇总贴

laoda1228

解法：	验证：	字符数：	公式：
解法01：	良好	53	=LOOKUP(B4,{0,"不及格";60,"及格";70,"中等";80,"良好";90,"优秀"})
解法02：	良好	53	=LOOKUP(B4,{0,60,70,80,90;"不及格","及格","中等","良好","优秀"})
解法03：	良好	55	=LOOKUP(B4,{0,60,70,80,90},{"不及格","及格","中等","良好","优秀"})
解法04：	良好	55	=LOOKUP(B4,{0,60,70,80,90},{"不及格";"及格";"中等";"良好";"优秀"})
解法05：	良好	56	=VLOOKUP(B4,{0,"不及格";60,"及格";70,"中等";80,"良好";90,"优秀"},2)
解法06：	良好	58	=VLOOKUP(B4,{0,"不及格";60,"及格";70,"中等";80,"良好";90,"优秀"},2,1)
解法07：	良好	58	=HLOOKUP(B4,{0,60,70,80,90;"不及格","及格","中等","良好","优秀"},2,1)
解法08：	良好	59	=CHOOSE(IF(B4<60,6,B4/10-5),"及格","中等","良好","优秀","优秀","不及格")
解法09：	良好	61	=TEXT(TEXT(B4,"[>=90]优秀;[>=80]良好;0"),"[>=70]中等;[>=60]及格;不及格")
解法10：	良好	64	=INDEX({"不及格";"及格";"中等";"良好";"优秀";"优秀"},ROUNDUP((B4+0.1)/10,)-5)
解法11：	良好	66	=IF(B4<60,"不及格",IF(B4<70,"及格",IF(B4<80,"中等",IF(B4<90,"良好","优秀"))))
解法12：	良好	66	=IF(B4>89,"优秀",IF(B4>79,"良好",IF(B4>69,"中等",IF(B4>59,"及格","不及格"))))
解法13：	良好	70	=IF(B4>=90,"优秀",IF(B4>=80,"良好",IF(B4>=70,"中等",IF(B4>=60,"及格","不及格"))))
解法14：	良好	70	=IF(B4>=60,IF(B4>=70,IF(B4>=80,IF(B4>=90,"优秀","良好"),"中等"),"及格"),"不及格")
解法15：	良好	71	=IF(B4>=90,"优秀",IF(B4>=80,"良好",IF(B4>=70,"中等",IF(B4>=60,"及格","不及格"))))
解法16：	良好	76	=IF(B4>=60,(IF(B4>=70,(IF(B4>=80,(IF(B4>=90,"优秀","良好")),"中等")),"及格")),"不及格")
解法17：	良好	78	=CHOOSE((B4>=0)+(B4>=60)+(B4>=70)+(B4>=80)+(B4>=90),"不及格","及格","中等","良好","优秀")
解法18：	良好	80	=IF(B4>=90,"优秀",IF(B4>=80,"良好",IF(B4>=70,"中等",IF(B4>=60,"及格",IF(B4<60,"不及格")))))
解法19：	良好	93	=IF(B4<60,"不及格",VLOOKUP(INT(B4/10)-5,IF({1,0},ROW($1:$5),{"及格";"中等";"良好";"优秀";"优秀"}),2,))  数组
解法20：	良好	102	=IF(B4<60,"不及格",IF(AND(B4>=60,B4<70),"及格",IF(AND(B4>=70,B4<80),"中等",IF(AND(B4>=80,B4<90),"良好","优秀"))))

函初201203练习一-1228.rar (56.75 KB, 下载次数: 82)

2012-11-27 21:14 上传

点击文件名下载附件

hongqiqqq

看样子，以后真应该在减少字符上多下下功夫了

^-^绿叶

对函数一窍不通，真得加倍努力学习！

虎刺梅

这么多公式，可是我能看懂的没几个。

nicotine2012

下个全套的，全部抱起

大白梨

好好学习函数-------

guogao

慢慢看  学习下