在不拆分的情况下，能够用函数公式从组合装里面直接提取出各单品的汇总数量吗？

YtssBu

在不拆分的情况下，能够用函数公式从组合装里面直接提取出各单品的汇总数量吗？

part98

=LET(s,REDUCE("",$B$2:$B$71,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(TOCOL(DROP(REGEXEXTRACT(OFFSET(y,,-1),"[一-龟]+",1),,1),3)&"-"&REGEXEXTRACT(OFFSET(y,,-1),"[0-9A-Z]+$"),y),y)))),SUM(FILTER(TAKE(s,,-1),TAKE(s,,1)=D3)))
能做，不过要有正则的版本

inrever

=MAP(D3:D18,LAMBDA(x,SUM(TOCOL(--(DROP(REDUCE("",A2:A71,LAMBDA(m,n,LET(a,REGEXP(n,"[一-龟]+(?=[\w+])|\w+"),b,TAKE(a,,-1),c,DROP(DROP(a,,-1),,1),VSTACK(m,c&"-"&b)))),1)=x)*B2:B71,3))))好像统计的数量和你正确值不一样，在复核一下

YtssBu

inrever 发表于 2024-7-17 15:47
=MAP(D3:D18,LAMBDA(x,SUM(TOCOL(--(DROP(REDUCE("",A2:A71,LAMBDA(m,n,LET(a,REGEXP(n,"[一-龟]+(?=[\w+]) ...

尴尬了，复核了一下你的才是正解

YtssBu

inrever 发表于 2024-7-17 15:47
=MAP(D3:D18,LAMBDA(x,SUM(TOCOL(--(DROP(REDUCE("",A2:A71,LAMBDA(m,n,LET(a,REGEXP(n,"[一-龟]+(?=[\w+]) ...

这个公式可以用在WPS的金山文档吗？同步成在线文档的

inrever

YtssBu 发表于 2024-7-17 16:44
这个公式可以用在WPS的金山文档吗？同步成在线文档的

没试过，你可以试试看

gadxjyang

=SUM(SUMPRODUCT((IFERROR(FIND(LEFT(D3,2),IF(ISNUMBER(FIND(MID(D3,3,4),SUBSTITUTES($A$2:$A$71,{"色","灰"},{"色-","灰-"}))),SUBSTITUTES($A$2:$A$71,{"色","灰"},{"色-","灰-"}),""),HSTACK(ROW($A$2:$A$71)^0,IFERROR(FIND(LEFT(D3,2),IF(ISNUMBER(FIND(MID(D3,3,4),SUBSTITUTES($A$2:$A$71,{"色","灰"},{"色-","灰-"}))),SUBSTITUTES($A$2:$A$71,{"色","灰"},{"色-","灰-"}),""))+1,99))),0)>0)*$B$2:$B$71))

博爱风雨同舟

=SUM(BYROW(SIGN(ISNUMBER(FIND(TEXTSPLIT(D3,"-"),HSTACK($A$2:$A$71,MAP(RIGHT($A$2:$A$71,3),LAMBDA(X,MID(X,SEARCHB("?",X)/2+1,2*LEN(X)-LENB(X)))))))),LAMBDA(a,PRODUCT(a)))*$B$2:$B$71)

博爱风雨同舟

=SUM(ISNUMBER(FIND(TEXTBEFORE(D3,"-"),$A$2:$A$71))*(TEXTAFTER(D3,"-")=MAP(RIGHT($A$2:$A$71,3),LAMBDA(X,MID(X,SEARCHB("?",X)/2+1,2*LEN(X)-LENB(X)))))*$B$2:$B$71)

jerrike

1. =MAP(D3:D18,LAMBDA(m,SUM(MAP(A2:A71,LAMBDA(a,SUM((REGEXP(a,"[一-龟]+(?=[\w+])")&"-"&REGEXP(a,"\w+$")=m)/1)))*B2:B71)))

复制代码