Tìm khách hàng và số lượng có mua lại hàng trong 1 cửa hàng của từng năm. Khách sẽ phải có số lần mua > 1 lần tại 1 cửa hàng bất kỳ trong năm mới được tính.
Công thức:
Ví dụ:
- Năm 2021 khách I có mua 3 lần nhưng lại mua mỗi cửa hàng 1 lần nên sẽ không thỏa mãn điều kiện cần loại bỏ.
- Năm 2024 khách O có mua 3 lần ở cửa hàng S2, và 2 lần ở cửa hàng S1. Nhưng chỉ tính 1 (liệt kê khách và đếm số lượng nên sẽ không được trùng lặp lại cùng tên khách).
Link: Onedrive
 |
| Tìm khách hàng và đếm số lượng thỏa mãn điều kiện |
1.=LET(a,YEAR(A2:A89),b,B2:B89,c,C2:C89,d,UNIQUE(a),e,MAP(a,b,c,LAMBDA(x,y,z,SUM((a=x)*(b=y)*(c=z)))),REDUCE({"Year","Count","Customers"},d,LAMBDA(u,v,VSTACK(u,LET(p,UNIQUE(FILTER(c,(a=v)*(e>1))),HSTACK(v,ARRAYTOTEXT(p),COUNTA(p)))))))
2.=LET(g,GROUPBY(HSTACK(YEAR(A2:A89),C2:C89,B2:B89),A2:A89,ROWS,,0),h,UNIQUE(FILTER(TAKE(g,,2),DROP(g,,3)>1)),DROP(GROUPBY(TAKE(h,,1),DROP(h,,1),HSTACK(ARRAYTOTEXT,ROWS),,0),1))
3.=LET(a,YEAR(A2:A89),b,B2:B89,c,C2:C89,U,UNIQUE,DROP(GROUPBY(a,c,HSTACK(LAMBDA(x,ARRAYTOTEXT(SORT(U(x)))),LAMBDA(x,ROWS(U(x)))),,0,,MAP(a,b,c,LAMBDA(x,y,z,SUM((a=x)*(b=y)*(c=z))))>1),1))
4.=LET(g,GROUPBY(HSTACK(YEAR(A2:A89),B2:C89),C2:C89,ROWS,,0),DROP(GROUPBY(TAKE(g,,1),INDEX(g,,3),HSTACK(LAMBDA(n,TEXTJOIN(", ",,SORT(UNIQUE(n)))),LAMBDA(x,ROWS(UNIQUE(x)))),0,0,,TAKE(g,,-1)>1),1))
5.=LET(y,YEAR(A2:A89),c,C2:C89,d,y&B2:B89&c,u,UNIQUE,n,BYROW(N(d=TOROW(d)),SUM),DROP(GROUPBY(y,c,HSTACK(LAMBDA(i,ARRAYTOTEXT(u(SORT(i)))),LAMBDA(i,ROWS(u(i)))),,0,,n>1),1))
6.=LET(z,C2:C89,a,YEAR(A2:A89),b,a&B2:B89&z,c,XMATCH(b,b),d,UNIQUE(FILTER(HSTACK(a,z),DROP(FREQUENCY(c,c),-1)>1)),DROP(GROUPBY(TAKE(d,,1),DROP(d,,1),HSTACK(ARRAYTOTEXT,ROWS),,0),1))
7.Python
df = xl("A1:C89",1)
r = (df.groupby([df["Date"].dt.year, "Store", "Customer"]).size().reset_index(name="Count"))
(r[r["Count"] > 1].drop_duplicates(["Date", "Customer"]).groupby("Date").agg(Customers=("Customer", lambda x: ", ".join(sorted(x))),Count=("Count", "size")).reset_index()).rename(columns={"Date": "Year"})