Tính tổng doanh thu khách hàng sau khi trừ tiền chiết khấu và các loại thuế.
- Total Amount = Quantity * (Price - All Discount - All Tax).
- Thêm dòng Tổng cho toàn bộ doanh thu các khách hàng.
Link: Onedrive
 |
| Tổng doanh thu mỗi khách hàng |
Công thức:
1.=LET(a,A2:A27,b,B2:B27,c,ROW(a),d,LOOKUP(c,c/(b>""),b),e,LEFT(a),CHOOSECOLS(GROUPBY(HSTACK(d,XMATCH(d,d)),XLOOKUP(d&A3,d&a,b)*IFS(e="q",0,e="p",1,1,-1)*N(+b),SUM,,,-1),1,3))
2.=LET(a,SCAN("",A2:A27,LAMBDA(x,y,IF(y="Customer",@+DROP(y:B27,,1),x))),b,REDUCE(HSTACK(A2,"Total Amount"),UNIQUE(a),LAMBDA(x,y,VSTACK(x,HSTACK(y,LET(a,DROP(FILTER(A2:B27,a=y),1),z,LAMBDA(p,FILTER(DROP(a,,1),ISNUMBER(SEARCH(p,DROP(a,,-1))))),z("quantity")*(z("price")-SUM(IFERROR(z("disc"),0),IFERROR(z("tax"),0)))))))),VSTACK(b,HSTACK("Tổng",SUM(DROP(b,1,1)))))
3.=LET(d,A2:A27,z,B2:B27,r,ROW(z),c,LOOKUP(r,r/(z>""),z),e,REDUCE(HSTACK(A2,"Total Amount"),UNIQUE(c),LAMBDA(a,v,VSTACK(a,HSTACK(v,SUM(REGEXTEST(d,{"P","T|D"})*{1,-1}*N(+z)*(c=v))*XLOOKUP(v&"Q",c&d,z,,3))))),VSTACK(e,HSTACK("Tổng",SUM(e))))
4.=LET(b,B1:B27,c,ROW(b),d,LOOKUP(c,c/(b>""),b),e,CODE(A1:A27),GROUPBY(d,XLOOKUP(d&81,d&e,b)*SWITCH(e,81,,80,1,-1)*N(+b),SUM,1,,2))
5.=LET(t,A2:A27,d,B2:B27,a,SCAN(,IF(t="Customer",d,""),LAMBDA(x,y,IF(y="",x,y))),b,IF(REGEXTEST(t,"^(Discount|Tax)(\d+)?$"),-d,IF(t="Price",d,0)),c,XLOOKUP(a,UNIQUE(a),FILTER(d,t="Quantity")),CHOOSECOLS(GROUPBY(HSTACK(a,XMATCH(a,a)),b*c,SUM,,,-1),1,3))
6.=LET(a,A2:A27,r,ROW(a),b,B2:B27,d,DROP(PIVOTBY(INDEX(b,LOOKUP(r, r/(a=A2))-1),a,b,SUM,1,0,,0,,N(+b)),1),q,INDEX(d,,6),p,INDEX(d,,5),s,q*(2*p-BYROW(N(d),SUM)+q),HSTACK(VSTACK(A2,TAKE(d,,1),"Tổng"),VSTACK("Total Amount",s,SUM(s))))
7.Python (Xem trong file)