M2:P13=IF(ROW(A1)>11,"",LOOKUP(1,0/(OFFSET($A$1:$K$1,MAX(COLUMN(A1)*2),)<>"")/(ROW(A1)>=COLUMN($A:$K)),OFFSET($A$1:$K$1,COLUMN(A1)*2,)))
另解=IF(ROW(A1)>11,"",LOOKUP(1,0/(OFFSET($A$1,MATCH(M$1,$A:$A,),,,11)<>"")/(ROW(A1)>=COLUMN($A:$K)),OFFSET($A$1,MATCH(M$1,$A:$A,),,,11)))
另解=IFERROR(INDEX(LOOKUP(COLUMN($A:$K),COLUMN($A:$K)/(OFFSET($A$1,MATCH(M$1,$A:$A,),,,11)<>""),OFFSET($A$1,MATCH(M$1,$A:$A,),,,11)),ROW(A1)),"")
Q2:Q13=IF(M2="","",HLOOKUP(M2,A$3:K$11,9,)/COUNTIF(M:M,M$2:M$12))
留言
張貼留言