公式列出出货明细

evluan

用公式列出出货明细

hxj119

第一感觉应该是用规划求解，公式暂时没想到方法

alan57

1. =LET(_a,CONCAT(REPT(A2:A11&",",B2:B11)),_b,TEXTSPLIT(REDUCE(_a,SCAN(0,E2:E7,SUM)-SEQUENCE(ROWS(E2:E7))+1,LAMBDA(x,y,SUBSTITUTE(x,",","/",y))),,"/",1),BYROW(_b,LAMBDA(x,LET(_c,TEXTSPLIT(@x,,",",1),_d,UNIQUE(_c),_e,MAP(_d,LAMBDA(x,SUM(N(_c=x)))),TEXTJOIN({"-",","},,HSTACK(_d,_e))))))

复制代码

tmplinshi

1. =LET(arrAB,OFFSET(A2,,,COUNT(B:B),2),arrE,OFFSET(E2,,,COUNT(E:E)),fn,LAMBDA(i,j,qtyRemain,qtyGot,LET(id,INDEX(arrAB,i,1),qty,IF(qtyRemain,qtyRemain,INDEX(arrAB,i,2)),qtyTarget,INDEX(arrE,j),qtyGot,qtyGot+qty,qtyRemain,IF(qtyGot>qtyTarget,qtyGot-qtyTarget,0),qtyCut,IF(qtyRemain,qty-qtyRemain,qty),str,id&"-"&qtyCut,newI,i+N(qtyRemain=0),newJ,j+N(qtyGot>=qtyTarget),finished,OR(newJ>ROWS(arrE),newI>ROWS(arrAB)),_qtyGot,IF(qtyGot>=qtyTarget,0,qtyGot),delimiter,IF(newJ>j,";",","),IF(finished,str,str&delimiter&fn(newI,newJ,qtyRemain,_qtyGot)))),TOCOL(TEXTSPLIT(fn(1,1,,),";")))

复制代码

世界客都

题目比较简单，我写的比较长，供参考哈

1. =TEXTJOIN(",",1,IFERROR(A$2:A$11&-1/MMULT(-TEXT(E3-TEXT(SUMIF(OFFSET(B$1,,,ROW($1:$10)+{0,1}),">0")-SUM(E$1:E2),"[<]!0"),"[<]!0"),{-1;1})^-1,""))

复制代码

世界客都

DDB简化一下：呃，其实也没简化多少。。。

1. =TEXTJOIN(",",1,IFERROR(A$2:A$11&-1/MMULT(-DDB(E2,DDB(SUMIF(OFFSET(B$1,,,ROW($1:$10)+{0,1}),">0"),SUM(E$1:E1),1,1),1,1),{-1;1})^-1,""))

复制代码