443字符,累死了
=INDEX(A:A,2+MOD(100*MAX(MOD(SMALL(MMULT(IF(A2:J10>"啊",0,A2:J10),N(ROW($1:$10)<=COLUMN($A:$J)))+COLUMN(A2:J10)*10000+ROW(A2:J10)*100000,ROW(1:90))-INDEX(SMALL(MMULT(IF(A2:J10>"啊",0,A2:J10),N(ROW($1:$10)<=COLUMN($A:$J)))+COLUMN(A2:J10)*10000+ROW(A2:J10)*100000,ROW(1:90)),N(IF(1,LOOKUP(ROW(1:90),IF(MOD(SMALL((IF(A2:J10>"啊",0,A2:J10))+COLUMN($A:$J)*10000+ROW(A2:J10)*100000,ROW(1:90)),10000)=0,ROW(1:90),0/0))))),10000)+(ROW(1:90)-1)%),100)/10)
思路:
1、L2 =MAX(MMULT(IF(A2:J2>"啊",0,A2:J2),N(ROW($1:$10)<=COLUMN(A:J)))-INDEX(MMULT(IF(A2:J2>"啊",0,A2:J2),N(ROW($1:$10)<=COLUMN(A:J))),N(IF(1,LOOKUP(COLUMN(A:J),IF(IF(A2:J2>"啊",0,A2:J2)=0,COLUMN(A:J),0/0))))))
2、使用small( xxx, row(1:90) ) 将二维内存数组转换为一维内存数组,再应用第一步的思路,结合行列号信息,得到结果