各位大神，有没有办法自动生成课表？

lflihao

袁勇

F2:

1. =LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(D$1,,,ROW($1:2)))*4,A$2:A3)

复制代码

G2:

1. =VLOOKUP(F2,A:B,2,0)+INT(COUNTIF(F$1:F1,F2)/4)

复制代码

H2:

1. =TEXT(MOD(ROW(A1)-1,4)+1,"第[dbnum1]0节")

复制代码

满坛皆为吾师

开个火车

1. =REDUCE(F1:H1,D2:D4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,,-3),SEQUENCE(y*4,,OFFSET(y,,-2),1/4),TEXT(MOD(SEQUENCE(y*4)-1,4)+1,"[DBnum1]第0节")),OFFSET(y,,-3)))))

复制代码

ericxzhou

=REDUCE(F1:H1,D2:D4,LAMBDA(x,y,LET(a,OFFSET(y,,-3),b,OFFSET(y,,-2),VSTACK(x,IFNA(HSTACK(a,DROP(REDUCE("",SEQUENCE(y)-1,LAMBDA(m,n,VSTACK(m,IFNA(HSTACK(b+n,"第"&SEQUENCE(4)&"节"),b+n)))),1)),a)))))
发现嵌套的REDUCE内层不能直接用offset，需要用LET赋值

quqiyuan

这种用PQ最省事吧。

dim麻辣君

F2输入公式，右拉下拉

1. =CHOOSE(COLUMN(A1),OFFSET($A$1,MIN(IF(COUNTIF($F$1:$F1,$A$2:$A$4)<$D$2:$D$4*4,ROW($1:$3),99)),),VLOOKUP(E2,$A:$B,2,)+INT((COUNTIF(E$1:E2,E2)-1)/4),TEXT(MOD(ROW(A4),4)+1,"第[dbnum1]0节"))

复制代码

ericxzhou

满坛皆为吾师 发表于 2023-6-25 11:05
开个火车

老师，3楼的解法跟我5楼的还不太一样，我用了2个REDUCE：=REDUCE(F1:H1,D2:D4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,,-3),DROP(REDUCE("",SEQUENCE(y)-1,LAMBDA(m,n,VSTACK(m,IFNA(HSTACK(OFFSET(y,,-2)+n,"第"&SEQUENCE(4)&"节"),OFFSET(y,,-2)+n)))),1)),OFFSET(y,,-3)))))
发现内层的REDUCE不能用offset，麻烦帮忙看看有啥问题

lflihao

感谢诸位帮忙，都是大牛，太牛了

博爱风雨同舟

=REDUCE({"班次全称","日期","时段"},A2:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TOCOL(ROW(INDIRECT(OFFSET(y,,1)&":"&OFFSET(y,,2)))*TRANSPOSE(SEQUENCE(4)^0)),TEXT(MOD(SEQUENCE(OFFSET(y,,3)*4)-1,4)+1,"[DBnum1]第0节")),y))))