Dữ liệu doanh thu khách hàng hàng tháng. Bạn cần phân loại các khách vào các nhóm:
- New: Khách chưa từng mua hàng.
- Active: Khách có mua hàng tháng này và tháng trước.
- Inactive: Khách mua hàng tháng trước mà không có doanh thu tháng này.
- Returning: Khách mua hàng tháng này mà không có doanh thu tháng trước.
Ví dụ: Trong tháng 3 có các khách C, D, A và E. Trong đó
Link: Onedrive
 |
| Phân loại khách hàng vào các nhóm |
Công thức:
1.=LET(a,A2:A25,b,B2:B25,c,XLOOKUP(b,b,a)=a,d,ISNA(XMATCH(b&a-1,b&a)),VSTACK({"Month","New","Active","Inactive","Returning"},DROP(GROUPBY(a,HSTACK(REPT(b,c),REPT(b,1-d),MAP(a,LAMBDA(i,ARRAYTOTEXT(UNIQUE(FILTER(b,(a<i)*ISNA(XMATCH(b,FILTER(b,a=i))),""))))),REPT(b,d*(1-c))),LAMBDA(x,TEXTJOIN(", ",,UNIQUE(x))),,0),1)))
2.=LET(m,A2:A25,c,B2:B25,y,UNIQUE(m),x,TOROW(UNIQUE(c)),f,COUNTIFS,z,TOCOL(DROP(y&"|"&x&"|"&IFS(f(m,"<="&y,c,x),IF(f(m,"<"&y,c,x)=0,"New",IF(f(m,y,c,x),IF(f(m,y-1,c,x),"Active","Returning"),"Inactive"))),1),2),r,REGEXEXTRACT,IFNA(IFS({1,0;0,0},A1),CHOOSECOLS(PIVOTBY(r(z,"\w+"),r(z,"\w+$"),r(z,"\|\K\w+"),ARRAYTOTEXT,,0,,0),1,4,2,3,5)))
3.=LET(a,A2:A25,b,B2:B25,F,FILTER,X,XMATCH,REDUCE(E1:I1,ROW(2:8),LAMBDA(u,v,VSTACK(u,LET(h,LAMBDA(s,r,ARRAYTOTEXT(IFERROR(TOCOL(IFS(s,r),3),""))),c,UNIQUE(F(b,a<v)),d,F(b,a=v),e,F(b,a=v-1),m,ISNA(X(d,c)),HSTACK(v,h(m,d),h(X(e,d),e),h(ISNA(X(c,d)),c),h(ISNA(X(d,e))+m=1,d)))))))
4.=LET(a,A2:A25,b,B2:B25,d,MAP(a,b,LAMBDA(x,y,MAX(FILTER(a,(b=y)*(a<x),0)))),e,IFS(a-d=1,"Active",d=0,"New",1,"Returning"),f,GROUPBY(a,b,CONCAT,,0),g,DROP(f,,1),h,DROP(VSTACK("",SCAN(,g,CONCAT)),-1),i,IFERROR(MAP(g,h,LAMBDA(v,w,LET(p,MID(w,SEQUENCE(LEN(w)),1),ARRAYTOTEXT(UNIQUE(FILTER(p,ISERR(FIND(p,v)),"")))))),"Inactive"),IFNA(IFS({1,0;0,0},A1),CHOOSECOLS(HSTACK(PIVOTBY(a,e,b,ARRAYTOTEXT,,0,,0,,a>1),i),1,3,2,5,4)))