表格内多种内容求和

lr863019

表格内多种内容求和  详情见附件

无关岁月

1. =LET(_a,TEXTJOIN("、",,B2:B5),TEXTJOIN("、",,BYROW(GROUPBY(REGEXEXTRACT(_a,"[0-9.一-龟]+[L水膜]",1),--REGEXEXTRACT(_a,"\d+(?=箱)",1),SUM,,0)&{"","箱"},CONCAT)))

复制代码

1. =LET(_a,TEXTJOIN("、",,FILTER(B$2:B$5,A$2:A$5=A9)),TEXTJOIN("、",,BYROW(GROUPBY(REGEXEXTRACT(_a,"[0-9.一-龟]+[L水膜]",1),--REGEXEXTRACT(_a,"\d+(?=箱)",1),SUM,,0)&{"","箱"},CONCAT)))

复制代码

lr863019

无关岁月 发表于 2024-6-19 19:31

您好！  谢谢您！ 不过我发现复制代码过去后显示不了数据，可以帮看下吗，谢谢！

无关岁月

lr863019 发表于 2024-6-19 20:02
您好！  首先谢谢您！ 不过我发现复制代码过去后显示不了数据，可以帮看下吗，谢谢！

wps要把REGEXEXTRACT换成regexp，版本还得最新版

lr863019

无关岁月 发表于 2024-6-19 20:03
wps要把REGEXEXTRACT换成regexp，版本还得最新版

那还有别的方法吗

博爱风雨同舟

=SORT(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),1)
提供思路

cnmlgb9998

借楼，练一下PQ，是这个效果吗？我做成了二维表，没聚合。


PQ 难道就真的不香了吗？？？？？？？？？

lr863019

cnmlgb9998 发表于 2024-6-19 20:13
借楼，练一下PQ，是这个效果吗？我做成了二维表，没聚合。

关键是不会呀

ericxzhou

Python+Pandas:

import pandas as pd

pd.set_option('display.max_colwidth', 300)

df = pd.read_excel('pandas exercise/sample283.xlsx', usecols='a:b', nrows=4)
df['内容'] = df['内容'].str.split('、')
df1 = df.explode('内容')['内容'].str.extract(r'(.*(?:水|膜))(\d+)')
df1 = pd.concat([df, df1], axis=1).iloc[:, [0, 2, 3]]
df1[1] = df1[1].astype('int')
df1.groupby(['客户', 0]).sum().reset_index().groupby('客户', as_index=None).apply(lambda x: ', '.join(x[0] + x[1].astype('str') + '箱')).rename(columns={None: '合计'})

jisijie630

=TEXTJOIN(",",1,UNIQUE(SUBSTITUTE(LEFT(TEXTSPLIT(CONCAT(B2:B5),,"箱"),MMULT(IFERROR(FIND({"水","膜"},TEXTSPLIT(CONCAT(B2:B5),,"箱")),),{1;1})),"、",))&TEXT(MMULT(IF(IFERROR(FIND(UNIQUE(SUBSTITUTE(LEFT(TEXTSPLIT(CONCAT(B2:B5),,"箱"),MMULT(IFERROR(FIND({"水","膜"},TEXTSPLIT(CONCAT(B2:B5),,"箱")),),{1;1})),"、",)),"|"&TEXTSPLIT(CONCAT(B2:B5),"箱"))-1,),IFERROR(MID(TEXTSPLIT(CONCAT(B2:B5),"箱"),MMULT({1,1},IFERROR(FIND({"水";"膜"},TEXTSPLIT(CONCAT(B2:B5),"箱"))+1,)),9),)/1,0),1^ROW(INDIRECT("1:"&LEN(CONCAT(B2:B5))-LEN(SUBSTITUTE(CONCAT(B2:B5),"、",))+COUNTA(A2:A5)+1))),"0箱;;"))




=TEXTJOIN(",",1,UNIQUE(SUBSTITUTE(LEFT(TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),,"箱"),MMULT(IFERROR(FIND({"水","膜"},TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),,"箱")),),{1;1})),"、",))&TEXT(MMULT(IF(IFERROR(FIND(UNIQUE(SUBSTITUTE(LEFT(TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),,"箱"),MMULT(IFERROR(FIND({"水","膜"},TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),,"箱")),),{1;1})),"、",)),"|"&TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),"箱"))-1,),IFERROR(MID(TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),"箱"),MMULT({1,1},IFERROR(FIND({"水";"膜"},TEXTSPLIT(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),"箱"))+1,)),9),)/1,0),1^ROW(INDIRECT("1:"&LEN(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")))-LEN(SUBSTITUTE(CONCAT(IF(A$2:A$5=A9,B$2:B$5,"")),"、",))+COUNTIF(A$2:A$5,A9)+1))),"0箱;;"))