自动生成表格

whmomo

大家好，要把标黄色的表格自动生成下面的格式，用公式怎么实现？





补充内容 (2024-3-4 12:52):
已解决

mykitami

=IFS(COLUMN(A:C)=1,TOCOL(IF(B2:E7>0,B1:E1,x),2,1),COLUMN(A:C)=2,TOCOL(IF(B2:E7>0,A2:A7,x),2,1),1,TOCOL(IF(B2:E7>0,B2:E7,x),2,1))

数组三键

=LET(a,DROP(REDUCE("",B1:E1,LAMBDA(x,y,VSTACK(x,IFS(COLUMN(A:C)=1,y,COLUMN(A:C)=2,A2:A7,1,OFFSET(y,1,,ROWS(A2:A7)))))),1),FILTER(a,TAKE(a,,-1)>0))

这个是M365版本的

袁勇

=IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(1*100+COLUMN($B:$E)*100001)/($B$2:$E$7>0),ROW(A1)),10000),"r0c00"),),"")

=IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(ROW($2:$7)*100+COLUMN($B:$E)*100000+1)/($B$2:$E$7>0),ROW(A1)),10000),"r0c00"),),"")

=IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(ROW($2:$7)*100+COLUMN($B:$E)*100001)/($B$2:$E$7>0),ROW(A1)),10000),"r0c00"),),"")

quqiyuan

你想能自动更新的话，估计PQ比较合适

世界客都

朋友，升级到免费版wps吧，非常好用嘿嘿。
=HSTACK(TOCOL(IF(B2:E7,B1:E1,0/0),3,1),TOCOL(IF(B2:E7,A2:A7,0/0),3,1),TOCOL(IF(B2:E7,B2:E7,0/0),3,1))

世界客都

365越用越好用哈哈，虽然我就会几个简单的

miaojohn45

1. =SORT(FILTER(TEXTSPLIT(TEXTJOIN(";",,TOCOL(B1:E1&","&A2:A7)&","&TOCOL(B2:E7)),",",";"),TOCOL(B2:E7)<>0))

复制代码

whmomo

袁勇 发表于 2024-2-26 10:26
=IFERROR(INDIRECT(TEXT(MOD(AGGREGATE(15,6,(1*100+COLUMN($B:$E)*100001)/($B$2:$E$7>0),ROW(A1)),10000) ...

365的用不了

我走我流

=IFERROR(INDIRECT(TEXT(INDEX(MID(SMALL(IF($B$2:$E$7,ROW($2:$7)/1%+COLUMN($B:$E)),ROW(A1)),{3,1,1},{1,1,3})*10^{0,2,0}+{100,1,0},COLUMN(A1)),"R0C00"),),"")

菜狗一只

世界客都 发表于 2024-2-26 10:31
365越用越好用哈哈，虽然我就会几个简单的

老哥，请教下=IF(B2:E7,B2:E7,0/0) 这个公式运算逻辑是什么，为啥0值会返回#DIV/0!