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

天涯论剑 · 发表于 2024-12-17 16:30

要求：怎么把A列的数据源转成D-E列的格式，把每个人名分开，然后汇总金额

你是那块小饼干 · 发表于 2024-12-17 16:48

=LET(T,DROP(REDUCE("",A1:A7,LAMBDA(X,Y,VSTACK(X,TEXTSPLIT(Y,,"、",1)))),1),GROUPBY(REGEXP(T,"\D+"),REGEXP(T,"\d+")*1,SUM,0,0))

复制代码

无关岁月 · 发表于 2024-12-17 16:52

=LET(f,LAMBDA(f,TOCOL(REDUCE(,REGEXP(A1:A7,f,3),VSTACK),3)),GROUPBY(f("[一-龟]+"),--f("[\d.]+"),SUM,,0))

复制代码

zwzc · 发表于 2024-12-17 16:55

=LET(a,TEXTSPLIT(TEXTJOIN("、",,A1:A7),,"、"),GROUPBY(REGEXP(a,"\W+"),--REGEXP(a,"\d+"),SUM,0,0))

cnmlgb9998 · 发表于 2024-12-17 16:59

hugeinfo · 发表于 2024-12-17 17:02

本帖最后由 hugeinfo 于 2024-12-17 17:03 编辑

=LET(a,TEXTSPLIT(TEXTJOIN("、",1,A1:A7),,"、"),b,HSTACK(REGEXP(a,"\D+"),--REGEXP(a,"\d+")),GROUPBY(TAKE(b,,1),TAKE(b,,-1),SUM,,0))
或者
=LET(a,TEXTSPLIT(TEXTJOIN("、",1,A1:A7),,"、"),GROUPBY(REGEXP(a,"\D+"),--REGEXP(a,"\d+"),SUM,,0))

xxy1721 · 发表于 2024-12-17 17:05

HSTACK(TRANSPOSE(REGEXP(TEXTJOIN("、",TRUE,A1:A7),"[一-龙]+")),TRANSPOSE(REGEXP(TEXTJOIN("、",TRUE,A1:A7),"\d+")))

xxy1721 · 发表于 2024-12-17 17:06

HSTACK(TRANSPOSE(REGEXP(TEXTJOIN("、",TRUE,A1:A7),"[一-龙]+")),TRANSPOSE(REGEXP(TEXTJOIN("、",TRUE,A1:A7),"\d+")))

cnmlgb9998 · 发表于 2024-12-17 17:12

我的城市你来过 · 发表于 2024-12-17 18:22

=LET(a,UNIQUE(REGEXP(TEXTJOIN("、",,A1:A7),"[一-龟]+"),TRUE),HSTACK(TOCOL(a),TOCOL(BYCOL(IF(ISNUMBER(FIND(a,A1:A7)),REGEXP(A1:A7,"\d+"),)*1,SUM))))

		自动登录	找回密码
密码			免费注册

[求助] 根据关键词拆分成每一行，汇总数据

评分

评分

评分

评分