各位大佬，下面这种有什么表格函数可以搞定吗

=LET(x,TOCOL(REDUCE(,REGEXP(A2:A5,"[\d~]+",3),VSTACK),3),y,DROP(REDUCE(0,x,LAMBDA(牛,马,VSTACK(牛,ROW(INDIRECT("A"&REGEXP(马,"~",2,":A")))))),1),功,MAX(y)-1,德,FREQUENCY(y,SEQUENCE(功)),HSTACK(ARRAYTOTEXT(FILTER(SEQUENCE(MAX(y)),德>1)),ARRAYTOTEXT(FILTER(SEQUENCE(MAX(y)),德=0))))
@yky洋洋 已经说明了一切

1-教室就图中这么多？但凡加2个辅助列都简化很多，2-数据若不是这么多，那谁像这样录数据的？3-学校也没几百个教室吧？？4-这题实际作用似乎没那么大？5-原本用意是否可以再优化思路？6-后续还会这样作业？这功德抢不了一点

=LET(x,TOCOL(REDUCE(,REGEXP(A2:A5,"[\d~]+",3),VSTACK),3),y,DROP(REDUCE(0,x,LAMBDA(牛,马,VSTACK(牛,ROW(INDIRECT("A"&REGEXP(马,"~",2,":A")))))),1),功,MAX(y)-1,德,FREQUENCY(y,SEQUENCE(功)),HSTACK(IFERROR(ARRAYTOTEXT(FILTER(SEQUENCE(MAX(y)),德>1)),""),IFERROR(ARRAYTOTEXT(FILTER(SEQUENCE(MAX(y)),德=0)),"")))

现在复杂一点的问题就想着用vba做

=LET(a,REDUCE("","a"&SUBSTITUTE(REGEXEXTRACT(CONCAT(C4:C7),"[\d～]+",1),"～",":a"),LAMBDA(x,y,VSTACK(x,ROW(INDIRECT(y))))),b,ROW(1:99), MAP({9,1}, LAMBDA(z,ARRAYTOTEXT(FILTER(b,z=TEXT(DROP(FREQUENCY(a,b)-1,-1),"1;9;0")*(b<=MAX(a)))))))