Chuyển vị trí bảng dữ liệu như kết quả trong ảnh.
Link: Onedrive
 |
| Chuyển vị trí bảng dữ liệu |
Công thức:
1.=REDUCE(HSTACK(A2:B2,"Shift",TOROW(C1:L1,1)),L3:L11,LAMBDA(x,v,VSTACK(x,WRAPCOLS(HSTACK(LOOKUP("z",TAKE(A3:v,,1)),"",@+B11:v,"",C2:D2,TAKE(v:C11,1)),2))))
2.=REDUCE(HSTACK(A2:B2,"Shift",TOROW(C1:L1,1)),C3:C11,LAMBDA(a,v,VSTACK(a,WRAPCOLS(HSTACK(TAKE(TOCOL(TAKE(A3:v,,1),3),-1),"",@+B11:v,"",C2:D2,TAKE(v:L11,1)),2))))
3.=IFNA(REDUCE(HSTACK(A2:B2,"Shift",CHOOSECOLS(A1:L1,SEQUENCE(5,,3,2))),B3:B11,LAMBDA(a,v,LET(l,HSTACK(TAKE(TOCOL(TAKE(v:A3,,1),3),-1),v,TOCOL(C2:D2),WRAPCOLS(DROP(TAKE(v:L11,1),,1),2)),VSTACK(a,l)))),"")
4.=LET(c,A3:A11,d,C1:L1,k,HSTACK,v,TOCOL,REDUCE(k(A2:B2,"Shift",TOROW(d*(d/d),3)),L3:L11, LAMBDA(h,z,LET(r,TAKE(+B11:z,1),VSTACK(h,IFNA(k(LOOKUP(ROW(z)-2,v((ROW(c)-2)/(c>""),3),v(c,1)),@r,v(C2:D2),WRAPCOLS(DROP(r,,1),2)),""))))))