拆分行重组行

yooha

请教该如何将SHEET1各行拆解相应数据逐行填入SHEET2表中,谢谢 拆分行1.zip (10.94 KB, 下载次数: 15)

2018-9-3 14:17 上传

点击文件名下载附件

hcm19522

A2:A21{=IFERROR(INDIRECT(TEXT(SMALL(IF((MOD(COLUMN(Q$2:AA$9),2)=1)*(Q$2:AA$9>0),ROW(Q$2:AA$9)/1%+COLUMN(Q$2:AA$9)),ROW(A1)),"!R0C00"),),"")

B2:D21{=IF($A2="","",INDEX(L:L,INT(SMALL(IF((MOD(COLUMN($Q$2:$AA$9),2)=1)*($Q$2:$AA$9>0),ROW(Q$2:AA$9)/1%+COLUMN($Q2:$AA9)),ROW(A1))%)))

F2:F21{IF($A2="","",INDEX(O:O,INT(SMALL(IF((MOD(COLUMN($Q$2:$AA$9),2)=1)*($Q$2:$AA$9>0),ROW(S$2:AC$9)/1%+COLUMN($Q2:$AA9)),ROW(C1))%)))

改B2:B21{=IF(A2="","",INDEX(L:L,INT(SMALL(IF((MOD(COLUMN($Q$2:$AA$9),2)=1)*($Q$2:$AA$9>0),ROW(Q$2:AA$9)/1%+COLUMN($Q2:$AA9)),ROW(A1))%))&T(INDIRECT(TEXT(SMALL(IF((MOD(COLUMN(P2:Z9),2)=0)*(P$2:Z$9>0),ROW(P$2:Z$9)/1%+COLUMN(P2:Z9)),ROW(A1)),"!R0C00"),)))
Sheet1!資料複製到Sheet2!K2:AA9