本帖最后由 youer_ma 于 2020-7-18 19:52 编辑
参考了别人的,也写了一个:
=INDEX(IF(AND(B4>=100,OR($C$2="S",$C$2="A")),附表1!$B$3:$F$3,附表1!$V$3:$Z$3),MATCH(1=1,(RANK(B4,OFFSET($B$4,,,COUNTA($A$4:$A$100)))<=SUBTOTAL(9,OFFSET(附表1!$B$10,MATCH($A$1,附表1!$A$10:$A$18,0)-1,MATCH($C$2,附表1!$B$1:$Z$1,0)-1,,COLUMN(A:E)))),0))
附表1!$V$3的值改为“A”了。或
=MID(IF(AND(B4>=100,OR($C$2="S",$C$2="A")),"S","A")&"ABCD",MATCH(1=1,(RANK(B4,OFFSET($B$4,,,COUNTA($A$4:$A$100)))<=SUBTOTAL(9,OFFSET(附表1!$B$10,MATCH($A$1,附表1!$A$10:$A$18,0)-1,MATCH($C$2,附表1!$B$1:$Z$1,0)-1,,COLUMN(A:E)))),0),1)
|