把班级对应的名单填进表格，一对多的查找替换

nikeena

需要做成员名单表。
成员名单里面不能填班号，需要填成员名字，请问怎样把班级对应的人名单填过去
特色班.zip (11 KB, 下载次数: 19)

2023-10-20 10:04 上传

点击文件名下载附件

沈默00

函数有难度，VBA写了一个，

沈默00

结果放在H列，试一下，

GG函数

zpy2

create temp table aa as
select f01 父,f02 子 from 基础连接查询合并 where f01!='';
//select * from aa;
create temp table bb as
select f03 父,f04 子 from 基础连接查询合并 where f03!='';
//select * from bb;
create temp table cc as
with recursive under_alice as (
select 父,子,0 level from aa union all
select a.父,a.子,b.level+1 from bb a join under_alice b on a.父=b.子 order by level desc)
select * from under_alice;
//select * from cc;
select iif(regexp('\d',父),(select distinct  父 from cc a where b.父=a.子),父) 兴趣班,子 成员 from cc b where not regexp('\d',子);

剑指E

nikeena

剑指E 发表于 2023-10-20 13:00

谢谢，很有帮助呢！

gwjkkkkk

参与一 下。。。

烟笼寒水客

剑指E 发表于 2023-10-20 13:00

这位老师厉害，这个公式最少够我学习理解一个礼拜了。

hcm19522

D2:G12{=IFERROR(INDEX($C:$C,SMALL(IF($C2=$B$15:$B$23,ROW($15:$23)),COLUMN(A1))),IF(B$1=$B$1,C2,""))

L2{=IFERROR(INDEX(B:B,RIGHT(SMALL(IF(D$2:G$12<>"",MATCH(B$2:B$12,B:B,)/1%+ROW($2:$12)),ROW(A1)),2)),"")

M2{=INDIRECT(TEXT(SMALL(IF((D$2:G$12<>"")*(B$2:B$12=L2),ROW($2:$12)/1%+{4,5,6,7},9999),COUNTIF(L$2:L2,L2)),"!R0C00"),)&""

I2{=IFERROR(INDEX(B:B,RIGHT(SMALL(IF(TEXT(COUNTIF(B$15:B$23,C$2:C$12),"0;;1")-{1,2,3,4,5}>=0,ROW($2:$12)+MATCH(B$2:B$12,B:B,)/1%),ROW(A1)),2)),"")

J2{=INDEX(C:C,SMALL(TEXT((B$2:B$12=I2)*IF(COUNTIF(B$15:B$23,C$2:C$12),(C$2:C$12=TRANSPOSE(B$15:B$23))*COLUMN(O:W),ROW($2:$12)*(COLUMN(O:W)=15)),"0;;99")*1,COUNTIF(I$2:I2,I2)))&""