Sheet1!M3:O3 公式(辅助列):
=IF(INDEX(($A:$A,$E:$E,$I:$I),ROW(A3),,COLUMN(A1)),INDEX(($A:$A,$E:$E,$I:$I),ROW(A3),,COLUMN(A1)),M2)
Sheet3!A2 公式: =SMALL(Sheet1!M$3:O$178,ROW(A1))
Sheet3!B2 公式: =LOOKUP(A2,OFFSET(Sheet1!A:B,,4*(MATCH(TRUE,COUNTIF(OFFSET(Sheet1!L:L,,ROW($1:$3)),A2)<>0,)-1)))&""
Sheet3!C2:D2 数组公式:
=INDEX(Sheet1!$A:$L,
COUNTIF($A$1:$A1,$A2)+MATCH($A2,OFFSET(Sheet1!$L:$L,,MATCH(TRUE,COUNTIF(OFFSET(Sheet1!$L:$L,,ROW($1:$3)),$A2)<>0,)),),
MATCH(TRUE,COUNTIF(OFFSET(Sheet1!$L:$L,,ROW($1:$3)),$A2)<>0,)*4-2+COLUMN(A1)
)
※数组公式,請記得按Ctrl+Shift+Enter
祝順心,南無阿彌陀佛!
|