产品分列

学习真好啊

求助：怎么讲产品分列，规则是提取规格中的产品 数量是数量列*规格里数量

edwin11891

1. =REDUCE(F1:H1,C2:C3,LAMBDA(x,y,LET(t,TEXTSPLIT(OFFSET(y,,1),,"+"),sname,TEXTBEFORE(t,"*"),sn,REGEXP(t,{"\d+","[一-龟]+"}),VSTACK(x,HSTACK(sname,y&"*"&CHOOSECOLS(sn,1),CHOOSECOLS(sn,2))))))

复制代码

逐渐陌生

只针对例子，参考公式：=REDUCE(F1:H1,D2:D3,LAMBDA(x,y,VSTACK(x,LET(t,TEXTSPLIT(y,,"+"),DROP(REDUCE("",t,LAMBDA(a,b,VSTACK(a,HSTACK(LEFT(b,1),OFFSET(y,,-1)&MID(b,2,2),RIGHT(b,1))))),1)))))

学习真好啊

edwin11891 发表于 2024-9-27 09:30

收到，谢谢

edwin11891

学习真好啊 发表于 2024-9-27 09:56
收到，谢谢

把公式中的 REGEXP(t,{"\d+",[一-龟]+"}) 换成 REGEXP(TEXTAFTER(t,"*"),{"\d+","\D+"})，可以适应任意商品名称（不一定是A、B，任意非数字形式的单位名称），即：

1. =REDUCE(F1:H1,C2:C3,LAMBDA(x,y,LET(t,TEXTSPLIT(OFFSET(y,,1),,"+"),sname,TEXTBEFORE(t,"*"),sn,REGEXP(TEXTAFTER(t,"*"),{"\d+","\D+"}),VSTACK(x,HSTACK(sname,y&"*"&CHOOSECOLS(sn,1),CHOOSECOLS(sn,2))))))

复制代码

hxj119

1. =LET(t,DROP(REDUCE("",D2:D3,LAMBDA(o,p,VSTACK(o,TEXTSPLIT(p,,"+")))),1),REDUCE(F1:H1,t,LAMBDA(x,y,VSTACK(x,HSTACK(TEXTBEFORE(y,"*"),REGEXP(SUBSTITUTES(y,B2:B3,C2:C3),"[^一-龟]+"),REGEXP(TEXTAFTER(y,"*"),"[一-龟]+"))))))

复制代码

sayhi95

1. =REDUCE(F1:H1,B2:B3,LAMBDA(x,y,LET(t,TEXTSPLIT(OFFSET(y,,2),,"+",1),VSTACK(x,IFNA(HSTACK(y,OFFSET(y,,1)&"*"&MID(t,3,1),RIGHT(t)),y)))))

复制代码

你是那块小饼干

1. =REDUCE({"商品名称(从规格中提取)","数量","单位"},D2:D3,LAMBDA(X,Y,LET(T,TEXTSPLIT(Y,"*","+",TRUE),VSTACK(X,HSTACK(INDEX(T,,1),OFFSET(Y,,-1)&"*"&LEFTB(INDEX(T,,2),2),RIGHTB(INDEX(T,,2),2))))))

复制代码

我走我流

学习老师们的 来个 reduce嵌套  =DROP(REDUCE("",D2:D3,LAMBDA(x,y,VSTACK(x,DROP(REDUCE("",TEXTSPLIT(y,,"+"),LAMBDA(a,b,VSTACK(a,HSTACK(CHOOSE({1,2,3},"",OFFSET(y,,-1),"")&REGEXREPLACE(b,"(.+)(\*\d+)(\D+)",{"$1","$2","$3"}))))),1)))),1)

mykitami

=DROP(REDUCE("",D2:D3,LAMBDA(x,y,LET(a,TEXTSPLIT(y,"*","+",1),b,TAKE(a,,1),c,TAKE(a,,-1),d,TEXTSPLIT(c,SEQUENCE(10)-1,,1),e,TEXTSPLIT(c,d,,1),VSTACK(x,HSTACK(b,OFFSET(y,,-1)&"*"&e,d))))),1)