Chuyển đổi bảng dữ liệu, tổng hợp theo từng loại quả. Thêm 1 dòng Total cho mỗi loại quả. Total = Tồn + KHSX.
Link: Onedrive
 |
| Chuyển đổi bảng dữ liệu |
Công thức:
1.=REDUCE(HSTACK(A1,"Quarters",UNIQUE(TEXTSPLIT(B1:I1,"-"),1)),I2:I8,LAMBDA(x,v,IFNA(VSTACK(x,LET(a,WRAPCOLS(TAKE(B8:v,1),2),HSTACK(@+A8:v,{"Tồn";"KHSX";"Total"},VSTACK(a,BYCOL(a,SUM))))),"")))
2.=LET(f,A2:A8,e,B1:I1,h,HSTACK,REDUCE(h(A1,"Quarters",UNIQUE(TEXTBEFORE(e,"-"),1)),f,LAMBDA(a,v,VSTACK(a,LET(d,WRAPCOLS(FILTER(B2:I8,f=v),2),IFNA(h(v,h(TOCOL(h(UNIQUE(TEXTAFTER(e,"-"),1),"Total")),VSTACK(d,BYCOL(d,SUM)))),""))))))
3.=LET(f,LAMBDA(r,LET(a,INDEX(A2:I8,r,),w,WRAPCOLS(DROP(a,,1),2),HSTACK(VSTACK(TAKE(a,,1),"",""),{"Tồn";"KHSX";"Total"},VSTACK(w,BYCOL(w,SUM))))),REDUCE(K1:P1,ROW(1:7),LAMBDA(x,v,VSTACK(x,f(v)))))
4.=REDUCE(HSTACK(A1,"Quarters",UNIQUE(TEXTSPLIT(B1:I1,"-"),1)),B2:B8,LAMBDA(a,v,LET(w,WRAPCOLS(TAKE(v:I8,1),2),VSTACK(a,HSTACK(EXPAND(@+v:A8,3,1,""),VSTACK(TOCOL(REGEXEXTRACT(B1:C1,"-\K.+")),"Total"),VSTACK(w,BYCOL(w,SUM)))))))