H2:I17{=IFERROR(IF(LOOKUP(1,0/(H$1:H1<>""),H$1:H1)=LOOKUP("嫻",OFFSET(A$2,,,MAX(SMALL(IF(TEXT(COUNTIF(OFFSET($C$1:$E$1,ROW($1:$10),),">0"),"0;;1")-{1,2,3}>=0,ROW($1:$10)),ROW(A1))))),"",LOOKUP("嫻",OFFSET(A$2,,,MAX(SMALL(IF(TEXT(COUNTIF(OFFSET($C$1:$E$1,ROW($1:$10),),">0"),"0;;1")-{1,2,3}>=0,ROW($1:$10)),ROW(A1)))))),"")
J2:J17{=IFERROR(VLOOKUP(LOOKUP(1,0/(I$2:I2<>""),I$2:I2),B:E,SMALL(IF((LOOKUP(1,0/(I$2:I2<>""),I$2:I2)=B$2:B$11)*(C$2:E$11<>""),{2,3,4}),ROW(A1)-LOOKUP(1,0/(I$2:I2<>""),ROW($1:1))+1),),"")
另解H2:I17{=IFERROR(IF(COUNTIF(H$1:H1,LOOKUP("嫻",OFFSET(A$2,,,MAX(SMALL(IF(TEXT(COUNTIF(OFFSET($C$1:$E$1,ROW($1:$10),),">0"),"0;;1")-{1,2,3}>=0,ROW($1:$10)),ROW(A1)))))),"",LOOKUP("嫻",OFFSET(A$2,,,MAX(SMALL(IF(TEXT(COUNTIF(OFFSET($C$1:$E$1,ROW($1:$10),),">0"),"0;;1")-{1,2,3}>=0,ROW($1:$10)),ROW(A1)))))),"")
另解 (Another explanation)
H2:I17=IFERROR(INDEX(A$2:A$11,MATCH(ROW(A1)-1,MMULT(N(ROW($1:$66)>COLUMN($A:$J)),TEXT(MMULT(N($C$2:$E$11<>""),{1;1;1}),"[=]!1")*1),))&"","")
另解 H2:I17{=IFERROR(INDEX(A:A&"",SMALL(IF(N(MMULT(N($C$2:$E$11>0),{1;1;1}))>={0,2,3},ROW($2:$11)+{0,5,5}%),ROW(A1))&"."),"")
留言
張貼留言