根据关键词拆分成每一行，汇总数据

hxj119

1. =LET(a,DROP(REDUCE("",A1:A7,LAMBDA(x,y,VSTACK(x,WRAPROWS(REGEXP(y,"[一-龟]+|\d+"),2)))),1),GROUPBY(CHOOSECOLS(a,1),CHOOSECOLS(a,2)/1,SUM,,0))

复制代码

cjc209

4楼为最佳方案 简单粗暴

jerrike

1. =LET(a,WRAPROWS(REGEXP(CONCAT(A1:A7),"[一-龢]+|[\d]+"),2),GROUPBY(TAKE(a,,1),TAKE(a,,-1)/1,SUM,0,0))

复制代码

龙逸凡

WPS公式：
=WRAPROWS(REGEXP(TEXTJOIN("、",,A1:A7),"[一-龟]+|[.0-9]+"),2)


Excel公式：
=WRAPROWS(REGEXEXTRACT(TEXTJOIN("、",,A1:A7),"[一-龟]+|[.0-9]+",1),2)

yellowhawk

龙逸凡 发表于 2024-12-18 10:25
WPS公式：
=WRAPROWS(REGEXP(TEXTJOIN("、",,A1:A7),"[一-龟]+|[.0-9]+"),2)

公式很好，我看还要加一个GROUPBY或PIVOTBY求和就完美了。

xtmyd5156

高冷cool

代码在模块里自取

守候_CJ

示例1

=LET(x,REGEXEXTRACT(TEXTSPLIT(TEXTJOIN("、",TRUE,A1:A7),,"、",TRUE),{"\D+","\d+"},1),GROUPBY(TAKE(x,,1),VALUE(TAKE(x,,-1)),SUM,0,0))

示例2

=LET(x,REGEXEXTRACT(TRANSPOSE(REGEXEXTRACT(CONCAT(A1:A7),"[^、\d]+\d+",1)),{"\D+","\d+"},1),GROUPBY(INDEX(x,,1),--INDEX(x,,2),SUM,0,0))

mykitami

=LET(s,SUBSTITUTE(CONCAT(A1:A7),"、",),a,TEXTSPLIT(s,,SEQUENCE(10)-1,1),b,--TEXTSPLIT(s,,a,1),DROP(GROUPBY(HSTACK(MATCH(a,a,),a),b,SUM,0,0,),,1))

mjm04

参与下

=GROUPBY(MAP(TEXTSPLIT(ARRAYTOTEXT(A1:A7),,{",","、"}),LAMBDA(a,REGEXP(a,"[一-龢]+"))),MAP(TEXTSPLIT(ARRAYTOTEXT(A1:A7),,{",","、"}),LAMBDA(a,REGEXP(a,"\d+")))*1,SUM,0)