把某某全天都做了啥找出来

summerlcb

黄色区域是数据区，请问绿色单元格怎么填写函数，根据名字自动判断该同学全天都做了什么？
比如E1填写“小张”，F2的函数查找黄色区域的”小张“在上午做了啥，下午做了啥，晚上做了啥。依次罗列，没找到的话直接跳过。

谢谢老师~

无关岁月

和模拟结果不太一致

1. =TEXTJOIN("、",,IFNA(A$2:A$4&REGEXP(B$2:B$4,E2&"\K[一-龟\d\[\]]+"),""))

复制代码

edwin11891

拉个火车，可以与模拟结果完全一致。

1. =LET(t,DROP(REDUCE("",A2:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TEXTSPLIT(OFFSET(y,,1),,"、")),y)))),1),s,DROP(REDUCE("",E2:E6,LAMBDA(x,y,VSTACK(x,LET(Sa,IFERROR(FILTER(t,ISNUMBER(FIND(y,TAKE(t,,-1)))),""),Sb,SUBSTITUTE(TAKE(Sa,,-1),y,""),Sc,TEXTJOIN("、",1,TAKE(Sa,,1)&Sb),Sc)))),1),s)

复制代码

vic9

1. =TEXTJOIN("、",1,MAP(A$2:A$4,B$2:B$4,LAMBDA(a,b,REGEXREPLACE(IFERROR(TOCOL(IF(ISNUMBER(FIND(E2,TEXTSPLIT(b,"、"))),TEXTSPLIT(b,"、"),NA()),2),""),E2,a))))

复制代码

mykitami

=LET(a,TEXTSPLIT(TEXTJOIN(";",1,$B$2:$B$4),"、",";"),b,IF(ISNUMBER(FIND(E2,a)),$A$2:$A$4,""),TEXTJOIN("、",,b&SUBSTITUTE(IF(ISNUMBER(FIND(E2,a)),a,""),E2,"")))

comeet

=TEXTJOIN(",",1,REDUCE("",$B$2:$B$4,LAMBDA(x,y,LET(t,TEXTSPLIT(y,"、"),VSTACK(x,IFNA(SUBSTITUTE(INDEX(t,,XMATCH(E2&"*",t,2)),E2,OFFSET(y,,-1)),""))))))

vic9

1. =TEXTJOIN("、",1,MAP(A$2:A$4,B$2:B$4,LAMBDA(a,b,REGEXREPLACE(IFERROR(TEXTJOIN("、",1,IF(ISNUMBER(FIND(E2,TEXTSPLIT(b,"、"))),TEXTSPLIT(b,"、"),"")),""),E2,a))))

复制代码