表格内多种内容求和

cnmlgb9998

jisijie630 发表于 2024-6-19 20:35
=TEXTJOIN(",",1,UNIQUE(SUBSTITUTE(LEFT(TEXTSPLIT(CONCAT(B2:B5),,"箱"),MMULT(IFERROR(FIND({"水","膜"} ...

。。。。。。。。

天书。。。。。。。。。。。

jisijie630

cnmlgb9998 发表于 2024-6-19 20:38
。。。。。。。。

天书。。。。。。。。。。。

??????????????????????

逐渐陌生

参考公式：=LET(g,REDUCE("",A2:A5,LAMBDA(x,y,LET(z_1,VSTACK(x,LET(m,MAP(TEXTSPLIT(OFFSET(y,,1),,{"箱","、"},1),LAMBDA(z,-LOOKUP(1,-RIGHT(z,SEQUENCE(LEN(z)))))),t,TEXTSPLIT(OFFSET(y,,1),,VSTACK("箱","、",UNIQUE(m)),1),IFNA(HSTACK(y,HSTACK(IFNA(TEXTBEFORE(t,"L")&"L",t),m)),y))),GROUPBY(TAKE(z_1,,2),TAKE(z_1,,-1),SUM,0,0)))),REDUCE({"客户","合计"},UNIQUE(DROP(g,1,-2)),LAMBDA(g_1,g_2,VSTACK(g_1,HSTACK(g_2,CONCAT(BYROW(FILTER(DROP(g,,1),TAKE(g,,1)=g_2),CONCAT)&"箱，"))))))

1786024734

B6=LET(X,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("、",,B2:B5),"水","水/"),"膜","膜/"),"/","箱"&{"、",""},1),Y,INDEX(X,,1),Z,TRANSPOSE(UNIQUE(Y)),TEXTJOIN("、",,Z&MMULT(SEQUENCE(,COUNTA(Y))^0,(Y=Z)*INDEX(X,,2))&"箱"))
B9=LET(X,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("、",,FILTER($B$2:$B$5,$A$2:$A$5=A9)),"水","水/"),"膜","膜/"),"/","箱"&{"、",""},1),Y,INDEX(X,,1),Z,TRANSPOSE(UNIQUE(Y)),TEXTJOIN("、",,Z&MMULT(SEQUENCE(,COUNTA(Y))^0,(Y=Z)*INDEX(X,,2))&"箱"))

lr863019

1786024734 发表于 2024-6-19 21:28
B6=LET(X,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("、",,B2:B5),"水","水/"),"膜","膜/"),"/","箱"&{"、 ...

可以了， 非常感谢！

lr863019

博爱风雨同舟 发表于 2024-6-19 20:10
=SORT(WRAPROWS(TRIM(MID(SUBSTITUTE(TEXTJOIN("、",1,REDUCE(B2:B5,{"水","膜"},LAMBDA(X,Y,SUBSTITUTE(X, ...

可以了， 非常感谢！

lr863019

ericxzhou 发表于 2024-6-19 20:32
Python+Pandas:

import pandas as pd

可以了， 非常感谢！

lr863019

jisijie630 发表于 2024-6-19 20:35
=TEXTJOIN(",",1,UNIQUE(SUBSTITUTE(LEFT(TEXTSPLIT(CONCAT(B2:B5),,"箱"),MMULT(IFERROR(FIND({"水","膜"} ...

可以了， 非常感谢！

lr863019

ericxzhou 发表于 2024-6-19 20:32
Python+Pandas:

import pandas as pd

可以了， 非常感谢！

博爱风雨同舟

=LET(B,LET(A,MAP(OFFSET(B1,ROW(1:4),),LAMBDA(X,SUM(IF(MID(X,SEQUENCE(70),1)="、",1,"")))),XLOOKUP(SEQUENCE(SUM(A)+4),SCAN(0,A,LAMBDA(X,Y,1+X+Y)),A2:A5,"",1,-1)),C,HSTACK(B,WRAPROWS(TRIM(MID(SUBSTITUTE(TEXTJOIN("、",1,REDUCE(B2:B5,{"水","膜"},LAMBDA(X,Y,SUBSTITUTE(X,Y,Y&"、")))),"、",REPT(" ",99)),COLUMN(A:AD)*100-99,99)),2)),VSTACK({"客户","内容","数量"},SORT(C,2),HSTACK("合计","",SUM(IFERROR(--SUBSTITUTE(CHOOSECOLS(C,3),"箱",""),))&"箱")))