求解:excel中第1名和第2名得5分,第3名至第5名得4分,第6至7名得3分,这个公式怎么列?
求解:excel中第1名和第2名得5分,第3名至第5名得4分,第6至7名得3分,这个公式怎么列?
=IF(OR(A2=1,A2=2),5,IF(AND(A2>2,A2<6),4,IF(OR(A2=6,A2=7),3,"")))
=IF(OR(A2<1,A2>7),"",LOOKUP(A2,{1,3,6},{5,4,3}))
=IF(OR(A2<1,A2>7),"",(5-MAX(0,INT((A2-3)/3)+1)))
=IFERROR(CHOOSE(A2,5,5,4,4,4,3,3),"")
假设单元格A1记录名次:数值1-7
公式为:
=IF(A1<=0,"",IF(A1<=2,5,IF(A1<=5,4,IF(A1<=7,3,""))))
注意:当A1不是数值是,返回错误;当A1不为1-7的数值时,返回空。
=IF(A2=1 or A2=2,5,IF(A2=3 or A2=5,4,IF(A2=6 or A2=7,3,"")))
回答完毕保证正确
热门标签: