反向查找对应数据

summerlcb

黄色区域是数据区，代表某某老师在某一天带某某学生。

请问绿色单元格函数怎么写，能反向查找，某某学生被哪几位老师带过。【这是我的基本需求，老师帮忙写这个函数即可】

如果有空的话，将蓝色单元格也帮忙完善下函数，这个多要求细化时间，有点难。


谢谢

我的城市你来过

=HSTACK(TEXTJOIN(",",,UNIQUE(TOCOL(IF(ISNUMBER(FIND(J14,$B$2:$G$4)),$A$2:$A$4,"")))),TEXTJOIN(",",,UNIQUE(TOCOL(IF(ISNUMBER(FIND(J14,$B$2:$G$4)),$A$2:$A$4&"["&$B$1:$G$1&"]","")))))

vic9

=UNIQUE(TOROW(IF(ISNUMBER(FIND(A9,$B$2:$G$4)),$A$2:$A$4,NA()),2),1)

caisijie

学习大神操作

cnmlgb9998

来个全自动的。。。。。。。。。。。。。。。

jisijie630

=TEXTJOIN("、",,UNIQUE(TOCOL(T(OFFSET(A$1,IF(ISERR(FIND(J2,B$2:G4)),9,ROW($1:3)),))&IFNA("["&TEXT(IF(ISERR(FIND(J2,B$2:G4)),NA(),COLUMN(A:F)),"星期[DBNum1]")&"]",""))))

edwin11891

1. =LET(t,DROP(REDUCE("",A2:A4,LAMBDA(XX,YY,VSTACK(XX,DROP(REDUCE("",OFFSET(YY,,1,,6),LAMBDA(x,y,VSTACK(x,CHOOSE({1,2,3},YY,INDIRECT(ADDRESS(1,COLUMN(y))),TEXTSPLIT(y,,"、",1))))),1)))),1),s,FILTER(t,1-ISERR(TAKE(t,,-1))),sa,GROUPBY(TAKE(s,,-1),TAKE(s,,1),LAMBDA(x,ARRAYTOTEXT(UNIQUE(x))),0,0),sb,GROUPBY(TAKE(s,,-1),TAKE(s,,1)&"["&CHOOSECOLS(s,2)&"]",ARRAYTOTEXT,0,0),HSTACK(sa,DROP(sb,,1)))

复制代码

mjm04

我的城市你来过 发表于 2024-12-8 16:28
=HSTACK(TEXTJOIN(",",,UNIQUE(TOCOL(IF(ISNUMBER(FIND(J14,$B$2:$G$4)),$A$2:$A$4,"")))),TEXTJOIN(",",,U ...

思路与你的一样， 换了WPS的 正则 来写，省掉了 TOCOL、ISNUMBER 与 FIND
供参考

=HSTACK(TEXTJOIN(",",1,UNIQUE(IF(REGEXP($B$2:$G$4,$J2,1),$A$2:$A$4,""),1,0)),TEXTJOIN(",",1,UNIQUE(IF(REGEXP($B$2:$G$4,$J2,1),$A$2:$A$4&"["&$B$1:$G$1&"]",""),1,0)))

下拉

mjm04

edwin11891 发表于 2024-12-8 22:04

可以写的更短些的，供参考

=DROP(REDUCE("",UNIQUE(TOCOL(REGEXP(TEXTJOIN("、",1,$B$2:$G$4),"[一-龢]+"))),LAMBDA(a,b,VSTACK(a,HSTACK(b,TEXTJOIN(",",1,UNIQUE(IF(REGEXP($B$2:$G$4,b,1),$A$2:$A$4,""),1,0)),TEXTJOIN(",",1,UNIQUE(IF(REGEXP($B$2:$G$4,b,1),$A$2:$A$4&"["&$B$1:$G$1&"]",""),1,0)))))),1)

我要成为专家

绿色l2下拉：

1. =TEXTJOIN("、",,UNIQUE(IF(ISNUMBER(FIND(K2,$B$2:$G$4)),$A$2:$A$4,"")))

复制代码

蓝色M2下拉：

1. =LET(s,ISNUMBER(FIND(K2,$B$2:$G$4)),p,IF(s,$A$2:$A$4,""),k,IF(s,"["&$B$1:$G$1&"]",""),TEXTJOIN("、",,p&k))

复制代码