数据整理请问

hxj119

1. =LET(a,FILTER($B$3:$B$27,$A$3:$A$27=$G3),b,TEXTSPLIT(TEXTJOIN("、",,REGEXP($E$1,"(?<="&TEXT(H$2,"m月d日：")&")"&"[一-龟、]+")),,"、"),c,REDUCE("",b,LAMBDA(x,y,VSTACK(x,IFERROR(INDEX(a,MATCH("*"&y&"*",a,0)),"")))),TEXTJOIN("、",,c))

复制代码

hxj119

更新公式：

1. =LET(a,FILTER($B$3:$B$27,$A$3:$A$27=$G3),b,TEXTSPLIT(TEXTJOIN("、",,REGEXP($E$1,"(?<="&TEXT(H$2,"m月d日：")&")"&"[一-龟、]+")),,"、"),c,REDUCE("",b,LAMBDA(x,y,VSTACK(x,IFERROR(FILTER(a,ISNUMBER(FIND(y,a))),"")))),TEXTJOIN("、",,c))

复制代码

悠扬的旋律

无关岁月

365参与一下，如果没有REGEXTEST可以用REGEXP代替

1. =LET(_a,TEXTSPLIT($E$1,{"：","、"},CHAR(10)),_b,FILTER($B$3:$B$27,$A$3:$A$27=$G3),TEXTJOIN("、",,IF(REGEXTEST(_b,TOROW(FILTER(DROP(_a,,1),INDEX(_a,,1)=TEXT(H$2,"m月d日")),2)),_b,"")))

复制代码