G1:P1=IF(COLUMN(A1)>$E2,"",COLUMN(A1)&" 班")
G2:P8{=IF(G$1="","",IFERROR(INDEX($B:$B,RIGHT(LARGE($C$2:$C$26/1%%+ROW($2:$26),IF(MOD(ROW(A1),2),(ROW(A1)-1)*$E$2+COLUMN(A1),$E$2*ROW(A1)-COLUMN(A1)+1)),4)),""))
G12:P18=IF(G2="","",RANK(VLOOKUP(G2,$B:$C,2,),$C:$C))
G2:P8另解{=IF(G$1="","",IFERROR(VLOOKUP(IF(MOD(ROW(A1),2),(ROW(A1)-1)*$E$2+COLUMN(A1),$E$2*ROW(A1)-COLUMN(A1)+1),IF({1,0},RANK($C$2:$C$26,$C:$C),$B$2:$B$26),2,),""))
留言
張貼留言