=LET(h,A2:A18,i,B2:B18,j,C2:C18,f,LAMBDA(z,DROP(REDUCE(0,z,LAMBDA(x,y,VSTACK(x,y&0&FILTER(i,h=TEXTAFTER(0&y,0,-1),"")))),1)),REDUCE({"出货产品","最终材料","数量","日期","材料路径"},E2:E5,LAMBDA(a,b,VSTACK(a,LET(c,MAP(f(f(f(f(f(b))))),LAMBDA(w,TEXTJOIN("->",,TEXTSPLIT(w,0)))),d,TEXTAFTER(c,">",-1),CHOOSE({1,2,3,4,5},b,d,MAP(c,LAMBDA(q,LET(k,DROP(TEXTSPLIT(q,"->",,1),,1),PRODUCT(IFNA(XLOOKUP(b&k,h&i,j),VLOOKUP(k,B1:C18,2,))))))*OFFSET(b,,1),OFFSET(b,,2),c))))))
excel吧 关注：282,439贴子：1,560,482
- 10回复贴，共1页
不感兴趣
开通SVIP免广告
=LET(f,LAMBDA(k,c,v,d,p,f,IFERROR(DROP(REDUCE(0,MAP(FILTER(B:.B,A:.A=c),FILTER(C:.C,A:.A=c),LAMBDA(x,y,LAMBDA(f(k,x,v*y,d,p&"->"&x,f)))),LAMBDA(a,x,VSTACK(a,x()))),1),HSTACK(k,c,v,d,p))),REDUCE({"出货产品","最终材料","数量","日期","材料路径"},MAP(E2:E5,F2:F5,TEXT(G2:G5,"e/m/d"),LAMBDA(k,v,d,LAMBDA(f(k,k,v,d,k,f)))),LAMBDA(a,x,VSTACK(a,x()))))
=REDUCE({"出货产品","最终材料","数量","日期","材料路径"},G2:G5,LAMBDA(w,z,VSTACK(w,LET(r,T(z:E5),s,"->",f,LAMBDA(z,DROP(REDUCE(0,z,LAMBDA(x,y,VSTACK(x,y&s&FILTER(C2:C18&s&B2:B18,A2:A18=TEXTAFTER(s&y,s,-1),"")))),1)),t,TEXTSPLIT(f(f(f(f(f(f(r)))))),"->-"),CHOOSE({1,2,3,4,5},r,TEXTAFTER(t,s,-1),MAP(t,LAMBDA(p,PRODUCT(TOCOL(--TEXTSPLIT(p,s),3))))*N(z:F5),z,REGEXREPLACE(t, ">\d+-", ""))))))
=LET(f,LAMBDA(x,TOCOL(REDUCE(x,SEQUENCE(6),LAMBDA(x,y,LET(a,TEXTAFTER(x,"->",-1,,,x),b,a=A2:A18,c,IFERROR(FILTER(x,NOT(BYCOL(b,OR))),1),d,IF(a=A2:A18,x&"->"&B2:B18,1),e,TOCOL(HSTACK(c,d),3),TOROW(FILTER(e,e<>1))))))),REDUCE({"出货产品","最终材料","数量","日期","材料路径"},E2:E5,LAMBDA(x,y,LET(g,f(y),arr,MAP(g,LAMBDA(m,LET(o,TEXTSPLIT(m,,"->"),PRODUCT(XLOOKUP(DROP(o,-1)&DROP(o,1),A2:A18&B2:B18,--C2:C18)))))*OFFSET(y,,1),VSTACK(x,IFERROR(HSTACK(TEXTBEFORE(g,"->"),TEXTAFTER(g,"->",-1),arr,OFFSET(y,,2),g),OFFSET(y,,2)))))))
