一、small large 意思相反
= small/ large(array,k)
=SMALL(C2:C16,3) 76
=large(C2:C16,3) 96
二、choose
=choose(index_num,value1,...)
=CHOOSE(3,A1,A3,C2,B3) 显示100
=SUM(CHOOSE(2,A1:B2,A5:B6,A9:B10,A13:B14)) 计算第二个数组的和
判断等级 =CHOOSE(IF(B2>=90,1,IF(B2>=75,2,IF(B2>=60,3,IF(B2<60,4)))),"优秀","良好","及格","不及格")
三、choose与vlookup组合
=VLOOKUP($E$2,CHOOSE({1,2,3,4},$A$2:$A$8,$B$2:$B$8,$C$2:$C$8,$D$2:$D$8),2, 0)
=VLOOKUP($E$2,CHOOSE({1,2,3,4},$A$2:$A$8,$B$2:$B$8,$C$2:$C$8,$D$2:$D$8),4, 0)
閱讀更多 淡墨留餘香 的文章