数据区域在200行以内- =INDIRECT(LOOKUP(SMALL(IF(COUNTIF(OFFSET(INDIRECT({"A","B","C"}&"车间!B1"),,,ROW($1:$200)),T(INDIRECT({"A","B","C"}&"车间!B"&ROW($1:$200))))*COUNTIF(INDIRECT({"A","B","C"}&"车间!B"&ROW($1:$200)),"*一班*")=1,COLUMN(A:C)/1%%+ROW($1:$200),39999),ROWS($1:1)),ROUNDDOWN(ROW($200:$799)/2%,-4)+MOD(ROW($200:$799),200)+1,CHAR(64+INT(ROW($200:$799)/200))&"车间!B"&MOD(ROW($200:$799),200)+1))&""
复制代码 一班改为二班在二班输入
改为三班
在三班输入``
数组公式
组合键CTRL+SHIFT+回车结束
下拉即可
下面再弄个过7层嵌套
得07版本及以上版本才能用- =INDIRECT(REPLACE(TEXT(SMALL(IF(MMULT(COUNTIF(OFFSET(INDIRECT({"A","A","B","B","C","C"}&"车间!B1"),ROW($1:$200)*MOD(COLUMN(B:G),2),,ROW($1:$200)^MOD(COLUMN(A:F),2)),IF(MOD(COLUMN(A:F),2),T(OFFSET(INDIRECT({"A","A","B","B","C","C"}&"车间!B1"),ROW($1:$200),)),"*一班*")),(INT(ROW($2:$7)/2)=COLUMN(A:C))*(1000^MOD(ROW($1:$6),2)))=1001,COLUMN(A:C)/1%%+ROW($1:$200),39999),ROWS($1:1)),"[>3E4]C0;[>2E4]!B0;A0"),2,1,"车间!B"))&""
复制代码 很多想法
不过都是堆的
夜了还是去休息了`` |