Tìm tên nhân viên cùng với ngày mà có sản lượng lớn nhất và nhỏ nhất.
Link: Onedrive
 | |
|
1.=LET(H,HSTACK,a,B2:K11,f,LAMBDA(x,H(TOCOL(IFS(a=x(a),A2:A11),3),TOCOL(IFS(a=x(a),B1:K1),3))),IFNA(VSTACK(H("Max",f(MAX)),H("Min",f(MIN))),""))
2.=LET(H,HSTACK,g,B2:K11,Q,LAMBDA(m,[x],TOCOL(IFS(g=ABS(MIN(m*g)),IF(x,A2:A11,B1:K1)),3)),IFNA(VSTACK(H("Category",A1,"Date"),H("Max",Q(-1,1),Q(-1)),H("Min",Q(1,1),Q(1))),""))
3.=LET(a,B2:K11,b,LAMBDA(x,y,TOCOL(IFS(x(a)=a,y),3)),c,A2:A11,d,B1:K1,e,HSTACK,IFNA(VSTACK({"Category","Name","Date"},e("Max",b(MAX,c),b(MAX,d)),e("Min",b(MIN,c),b(MIN,d))),""))
4.=LET(H,HSTACK,a,B2:K11,f,LAMBDA(s,TOCOL(IFS(a,s))),c,f(a),l,LAMBDA(r,FILTER(H(f(A2:A11),f(B1:K1)),c=r(c))),IFNA(VSTACK(H("Max",l(MAX)),H("Min",l(MIN))),""))
5.=LET(x,B2:K11,L,LAMBDA(f,LET(C,LAMBDA(a,TOCOL(IFS(x=f(x),a),3)),IFNA(HSTACK(PROPER(ARRAYTOTEXT(f)),C(A2:A11),C(B1:K1)),""))),VSTACK(L(MAX),L(MIN)))
6.=REDUCE(M1:O1,{1,2},LAMBDA(x,v,LET(a,B2:K11,f,LAMBDA(s,r,TOCOL(IFS(CHOOSE(v,MAX,MIN)(a)=a,r),3)),IFNA(VSTACK(x,HSTACK(LOOKUP(v,{1,"Max";2,"Min"}),f(v,A2:A11),f(v,B1:K1))),""))))
7.Python (Xem trong file)