Chuyển đổi bảng dữ liệu như ảnh.
Link: Onedrive
 |
| Chuyển đổi vị trí bảng dữ liệu |
Công thức:
1.=REDUCE(HSTACK(A1,"Quarters",UNIQUE(TEXTAFTER(B1:I1,"-"),1)),A2:A5,LAMBDA(x,v,IFNA(VSTACK(x,HSTACK(v,TOCOL(TEXTSPLIT(B1:C1,"-")),WRAPCOLS(FILTER(B2:I5,A2:A5=v),2))),"")))
2.=REDUCE(A8:F8,SEQUENCE(4),LAMBDA(a,v,VSTACK(a,LET(I,INDEX(B2:I5,v,),w,WRAPCOLS(I,2),IFNA(HSTACK(INDEX(A2:A5,v,),TOCOL(TEXTSPLIT(B1:C1,"-")),w),"")))))
3.=REDUCE(A8:F8,B2:B5,LAMBDA(a,v,IFNA(VSTACK(a,HSTACK(@+v:A5,TOCOL(TEXTSPLIT(B1:C1,"-")),WRAPCOLS(TAKE(v:I5,1),2))),"")))
4.=LET(a,B2:I5,f,LAMBDA(s,TOCOL(IFS(a,s))),b,IFNA(IFS({1,1;0,0},{"Fruits","Quarters"}),PIVOTBY(HSTACK(f(A2:A5),f(TEXTSPLIT(B1:I1,"-"))),f(TEXTAFTER(B1:I1,"-")),f(a),SUM,,0,,0)),c,TAKE(b,,1),HSTACK(REPT(c,SEQUENCE(ROWS(c))=XMATCH(c,c)),DROP(b,,1)))