【求解】对一列中多个单元格内的不规则内容进行提取并求和

AkatsukiHK

有点难度，最好用新函数

无关岁月

1. =LET(_a,CONCAT(A1:A6),GROUPBY(REGEXEXTRACT(_a,"[一-龟]+",1),--REGEXEXTRACT(_a,"\d+(\.\d+)?",1),SUM,,0))

复制代码

part98

=LET(s,REGEXEXTRACT(TEXTSPLIT(TEXTJOIN("，",,A1:A6),,"，"),HSTACK("[一-龟]+","[\d.]+"),1),GROUPBY(TAKE(s,,1),--TAKE(s,,-1),SUM,0,0))

AkatsukiHK

无关岁月 发表于 2024-6-24 22:45

完美

AkatsukiHK

part98 发表于 2024-6-24 22:54
=LET(s,REGEXEXTRACT(TEXTSPLIT(TEXTJOIN("，",,A1:A6),,"，"),HSTACK("[一-龟]+","[\d.]+"),1),GROUPBY(TA ...

太优秀了

jivy1212

1. =SUM(IFERROR(--SUBSTITUTE(MID(SUBSTITUTE($A$1:$A$6,"，",REPT(" ",66)),COLUMN(A:Z)*66-65,66),C2,),))

复制代码

老版，可乐结果不对

我的城市你来过

=WRAPROWS(TEXTSPLIT(TEXTJOIN(",",,LET(x,TEXTJOIN("，",,$A$1:$A$6),y,WRAPROWS(REGEXP(x,"[一-龟]+|[0-9.]+"),2),SCAN(0,UNIQUE(INDEX(y,,1)),LAMBDA(m,n,TEXTJOIN(",",,HSTACK(n,SUM(IF(INDEX(y,,1)=n,INDEX(y,,2)*1,0)))))))),","),2)

inrever

1. =LET(t,VSTACK(REGEXP(CONCAT(A1:A6),"[\x{4e00}-\x{9fa5}]+"),REGEXP(CONCAT(A1:A6),"[0-9.]+")),x,TAKE(t,1),y,TAKE(t,-1),z,UNIQUE(x,1),v,DROP(TOCOL(REDUCE("",z,LAMBDA(m,n,HSTACK(m,SUM(--FILTER(y,x=n))))),3),1),HSTACK(TOCOL(z),v))

复制代码

1786024734

去重后名称

1. =LET(X,TEXTSPLIT(TEXTJOIN("，",,A1:A6),,"，"),UNIQUE(LEFT(X,LENB(X)-LEN(X))))

复制代码

对应数值之和

1. =LET(X,TEXTSPLIT(TEXTJOIN("，",,A1:A6),"，"),MMULT(IFERROR(--SUBSTITUTE(X,C3#,),),SEQUENCE(COUNTA(X))^0))

复制代码

或

1. =SUM(IFERROR(--SUBSTITUTE(TEXTSPLIT(TEXTJOIN("，",,$A$1:$A$6),"，"),C3,),))

复制代码

下拉