求助大神，根据姓名学科查找成绩

雅之希希 · 发表于 2024-11-27 18:05

问题描述：根据蓝色区域的数据，在黄色区域查询，得到绿色区域那样的结果。

zwzc · 发表于 2024-11-27 19:13

=TEXTJOIN(",",,LET(a,FILTER($F$2:$K$6,$F$1:$K$1=B2),FILTER(TAKE(a,,1),TAKE(a,,-1)=A2)))

leepoison · 发表于 2024-11-27 19:24

=MAP(B2:B7,LAMBDA(x,TEXTJOIN(",",1,IF(OFFSET(E2:E6,,MATCH(x,E1:K1,0))=OFFSET(x,,-1),OFFSET(E2:E6,,MATCH(x,E1:K1,0)-1),""))))

复制代码

无关岁月 · 发表于 2024-11-27 19:31

=LET(_a,MATCH(B2,F$1:K$1,),_b,E$2:E$6,TEXTJOIN(",",,FILTER(OFFSET(_b,,_a),OFFSET(_b,,_a+1)=A2)))

复制代码

ww87725244 · 发表于 2024-11-27 19:55

=TEXTJOIN(",",,TOCOL(IF($G$2:$K$6=A2,$F$2:$J$6,0/0),3))

mykitami · 发表于 2024-11-27 20:19

本帖最后由 mykitami 于 2024-11-27 20:25 编辑

=TEXTJOIN(",",,IF(OFFSET($E$2:$E$6,,MATCH(B2,$F$1:$K$1,)+1)=A2,OFFSET($E$2:$E$6,,MATCH(B2,$F$1:$K$1,)),""))

=TEXTJOIN(",",,IF(($G$2:$K$6=A2)*($G$1:$K$1=B2),$F$2:$J$6,""))

pgp5866 · 发表于 2024-11-27 20:37

=LET(r,OFFSET(G$2,,MATCH(B2,H$1:M$1,),5,2),TEXTJOIN(",",,FILTER(TAKE(r,,1),DROP(r,,1)=A2)))

复制代码

yllmessi · 发表于 2024-11-27 20:38

=TEXTJOIN(",",1,INDEX(IF($F$2:$K$6=$A2,OFFSET($F$2:$K$6,,-1),""),,MATCH($B2,$F$1:$K$1,0)+1))

一杯绿茶来了 · 发表于 2024-11-27 21:10

=TEXTJOIN(",",,TOCOL(IF(($G$2:$K$6=A2)*($F$1:$J$1=B2),$F$2:$J$6,\),3))

忙到飞起 · 发表于 2024-11-27 21:17

=LET(a,FILTER($F$2:$K$6,$F$1:$K$1=B2),TEXTJOIN(",",,FILTER(TAKE(a,,1),TAKE(a,,-1)=A2)))

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

[求助] 求助大神，根据姓名学科查找成绩

评分

评分