Chuyển đổi bảng dữ liệu thành danh sách tổng hợp thể hiện:
- Gồm thông tin tên nhân viên và các công việc được giao
- Tiếp theo cần xác định mỗi công việc có trạng thái thực hiện ra sao.
- Trống "Ngày bắt đầu" nghĩa là công việc đang ở trạng thái "Đăng ký", "Ngày hết hạn" trống sẽ trạng thái "Bắt đầu", và cả 3 ngày đều có dữ liệu. Vậy trạng thái "Kết thúc".
Link: Onedrive
 |
| Thống kê thời gian và phân loại theo công việc nhân viên |
Công thức:
1.=LET(z,B2:D19,c,A2:A19,i,BYROW(z,COUNT),r,SEQUENCE(ROWS(z)),VSTACK({"Nhân viên","Công việc","Tình trạng","Thời gian"},FILTER(HSTACK(LOOKUP(r,r/(c=@+c),B2:B19),c,INDEX({"Đăng ký";"Bắt đầu";"Kết thúc"},i),INDEX(z,r,i)),i)))
2.=LET(c,A2:A19,pt,IFS(B2:B19,c),v,FILTER(B2:D19,ISNUMBER(XMATCH(c,pt))),VSTACK({"Nhân viên","Công việc","Tình trạng","Thời gian"},HSTACK(FILTER(SCAN(0,c,LAMBDA(a,v,IF(v=A2,@+DROP(v:B19,,1),a))),ISTEXT(pt)),TOCOL(pt,3),INDEX({"Đăng ký";"Bắt đầu";"Kết thúc"},BYROW(v,COUNT)),BYROW(v,MAX))))
3.=LET(a,A2:D19,b,A2:A19,c,SCAN(0,b,LAMBDA(x,y,IF(y=A2,OFFSET(y,,1),x))),d,SWITCH(BYROW(a,COUNT),3,"Kết thúc",2,"Bắt đầu","Đăng ký"),e,BYROW(a,MAX),VSTACK({"Nhân viên","Công việc","Tình trạng","Thời gian"},FILTER(HSTACK(c,b,d,e),e)))
4.=LET(k,HSTACK,z,REDUCE(F1:I1,B2:B19,LAMBDA(h,c,LET(L,LOOKUP,r,TAKE(c:D19,1),VSTACK(h,IFERROR(k(L("z",IFS(TAKE(A2:c,,1)=A2,B2:c)),@+A19:c,L(9^6,r,{"Đăng ký","Bắt đầu","Kết thúc"}),MAX(r)),""))))),FILTER(z,1-BYROW(ISNA(z),OR)))
5.=LET(a,A1:A19,b,B1:B19,m,LOOKUP(ROW(b),ROW(b)/(a=A2),b),c,BYROW(b:D1,COUNT),VSTACK(F1:I1,FILTER(HSTACK(m,a,SWITCH(c,3,"Kết thúc",2,"Bắt đầu","Đăng ký"),BYROW(b:D1,MAX)),c)))
6.=LET(a,B3:D19,b,BYROW(a,MAX),f,LAMBDA(s,TOCOL(IFS((b>0)*(b=a),s),3)),VSTACK(F1:I1,HSTACK(f(DROP(SCAN(,B2:B19,LAMBDA(x,v,IF(@+A19:v=A2,v,x))),1)),f(A3:A19),f({"Đăng ký","Bắt đầu","Kết thúc"}),f(a))))
7.=LET(a,B3:D19,f,LAMBDA(x,TOCOL(IFS(N(+a),x),3)),b,f(A3:A19),c,f(DROP(SCAN(,B2:B19,LAMBDA(x,v,IF(@+A19:v=A2,v,x))),1)),DROP(GROUPBY(HSTACK(XMATCH(b&c,b&c),c,b),f(a),HSTACK(LAMBDA(s,SWITCH(ROWS(s),3,"Kết thúc",2,"Bắt đầu",1,"Đăng ký")),MAX),,0),1,1))