求大神帮我看看！！考勤表设置

沅沅123

如何能把格式1调整为格式2

yllmessi

清空B8:E25单元格内容
B8=HSTACK(B2&W7:W24,C2&W7:W24,TOCOL(E1:V1),TOCOL(E2:V2))

mykitami

=HSTACK(IF(SEQUENCE(COLUMNS(E2:V2)),B2:C2),TRANSPOSE(E1:V2))

沅沅123

mykitami 发表于 2024-11-9 11:27
=HSTACK(IF(SEQUENCE(COLUMNS(E2:V2)),B2:C2),TRANSPOSE(E1:V2))

大神，如果存在多行都要转换，请问应该怎么处理呢

mykitami

=HSTACK(B2:C2&TRANSPOSE(E3:V3),TRANSPOSE(E1:V2))

沅沅123

mykitami 发表于 2024-11-9 11:47
=HSTACK(B2:C2&TRANSPOSE(E3:V3),TRANSPOSE(E1:V2))

不行呢

mykitami

沅沅123 发表于 2024-11-9 11:46
大神，如果存在多行都要转换，请问应该怎么处理呢

=REDUCE(B7:E7,B2:B3,LAMBDA(x,y,VSTACK(x,HSTACK(IF(SEQUENCE(COLUMNS(E1:V1)),y:OFFSET(y,,1)),TRANSPOSE(E1:V1),TRANSPOSE(OFFSET(y,,3,,COLUMNS(E1:V1)))))))

=LET(fx,LAMBDA(x,y,TOCOL(IF(COLUMN(x)*ROW(x),y))),d,E2:V3,HSTACK(fx(d,B2:B3),fx(d,C2:C3),fx(d,E1:V1),fx(d,d)))

=REDUCE(B7:E7,B2:B3,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,OFFSET(y,,1),TRANSPOSE(E1:V1),TRANSPOSE(OFFSET(y,,3,,COLUMNS(E1:V1)))),HSTACK(y,OFFSET(y,,1))))))

沅沅123

yllmessi 发表于 2024-11-9 11:21
清空B8:E25单元格内容
B8=HSTACK(B2&W7:W24,C2&W7:W24,TOCOL(E1:V1),TOCOL(E2:V2))

大神，如果存在多行都要转换，请问应该怎么处理呢

沅沅123

mykitami 发表于 2024-11-9 12:23
=REDUCE(B7:E7,B2:B3,LAMBDA(x,y,VSTACK(x,HSTACK(IF(SEQUENCE(COLUMNS(E1:V1)),y:OFFSET(y,,1)),TRANSPO ...

哇，可以了，感谢

沅沅123

yllmessi 发表于 2024-11-9 11:21
清空B8:E25单元格内容
B8=HSTACK(B2&W7:W24,C2&W7:W24,TOCOL(E1:V1),TOCOL(E2:V2))

感谢感谢可以执行