本帖最后由 mjm04 于 2024-8-8 09:53 编辑
一步到位的,WPS 可用,如不能请升级WPS到最新版本
=REDUCE({"对话ID","对话开始时间","对话原文"},UNIQUE(Sheet1!$A$2:$A$36029&"#"&Sheet1!$B$2:$B$36029),LAMBDA(_a,_b,VSTACK(_a,HSTACK(TEXTSPLIT(_b,"#"),TEXTJOIN(CHAR(10),,BYROW(FILTER(Sheet1!$C$2:$D$36029,Sheet1!$A$2:$A$36029&"#"&Sheet1!$B$2:$B$36029=_b,""),LAMBDA(c,TEXTJOIN(":",,c))))))))
==============================================================
同时聚合 “对话开始时间” 与 “对话原文”
=REDUCE({"对话ID","对话开始时间","对话原文"},UNIQUE(Sheet1!A2:A36029),LAMBDA(_a,_b,VSTACK(_a,HSTACK(_b,TEXTJOIN(CHAR(10),,UNIQUE(FILTER(Sheet1!B2:B36029,Sheet1!A2:A36029=_b,""))),TEXTJOIN(CHAR(10),,BYROW(FILTER(Sheet1!C2:D36029,Sheet1!A2:A36029=_b,""),LAMBDA(_c,TEXTJOIN(":",,_c))))))))
图片为 双聚合 效果
|