Xoay chuyển bảng dữ liệu như ảnh.
Link: Onedrive
 |
| Xoay bảng dữ liệu |
Công thức:
1.=REDUCE(HSTACK(A1,TOROW(B2:B3&"-"&C1:F1,,1)),A2:A9,LAMBDA(a,v,IF(v>0,VSTACK(a,HSTACK(v,TOROW(TAKE(v:F9,2,-4),,1))),a)))
2.=LET(n,SCAN(,A2:A9,LAMBDA(a,v,IF(v>"",v,a))),REDUCE(HSTACK(A1,UNIQUE(TOROW(B2:B9&"-"&C1:F1,,1),1)),UNIQUE(n),LAMBDA(a,v,VSTACK(a,HSTACK(v,TOROW(FILTER(C2:F9,n=v),,1))))))
3.=LET(a,C2:F9,f,LAMBDA(s,TOCOL(IFS(a,s),3)),IFNA(IFS({1,0;0,0},A1),DROP(PIVOTBY(f(SCAN(,A2:A9,LAMBDA(x,v,IF(v>0,v,x)))),HSTACK(f(MID(C1:F1,2,1)*1),f(B2:B9&"-"&C1:F1)),f(a),SUM,,0,,0),1)))
4.=REDUCE(HSTACK(A1,TOROW(B2:B3&"-"&C1:F1,,1)),TOROW(A2:A9,1),LAMBDA(x,y,VSTACK(x,HSTACK(y,TOROW(CHOOSEROWS(C2:F9,XMATCH(y,A2:A9)+{0,1}),,1)))))
5.Python
df = xl("A1:F9",1).assign(Fruits=lambda d:d.Fruits.ffill()).pivot(index='Fruits',columns='Quarters')
df.columns=[f"{q}_{c}"for c,q in df.columns]
df.reset_index()