将后面sheet中不为0的数据按照一定格式要求填列到前面汇总表里

落语归尘

求助：将后面sheet中不为0的数据按照一定格式要求填列到前面汇总表里，附件已上传，请大神帮忙！！

mmlzh

B3

1. =INDIRECT("'"&$A3&"'!R7C"&COLUMN(G:G),0)

复制代码

右拉拉，下拉拉

123-456789

这是你想要的结果吗？

落语归尘

123-456789 发表于 2017-11-14 12:34
这是你想要的结果吗？

你好感谢回复，我可能没表达清楚，汇总表的B:E我要用来放别的，A、F、G是我想要输出的结果

落语归尘

mmlzh 发表于 2017-11-14 12:32
B3右拉拉，下拉拉

你好感谢回复，我可能没表达清楚，汇总表的B:E我要用来放别的，A、F、G是我想要输出的结果

mmlzh

数组公式A3

1. ="sheet"&SMALL(IF(N(INDIRECT("'sheet"&ROW($1:$8)&"'!R7C"&COLUMN($G:$J),0))>0,ROW($1:$8),100),ROW(1:1))

复制代码

下拉
F3

1. =INDIRECT("'"&$A3&"'!R7C"&SMALL(IF(N(INDIRECT("'"&$A3&"'!R7C"&COLUMN($G:$J),0))>0,COLUMN($G:$J),100),COUNTIF($A$2:A3,A3)),0)

复制代码

下拉
G3

1. =INDIRECT("'"&$A3&"'!R5C"&SMALL(IF(N(INDIRECT("'"&$A3&"'!R7C"&COLUMN($G:$J),0))>0,COLUMN($G:$J),100),COUNTIF($A$2:A3,A3)),0)

复制代码

下拉

落语归尘

mmlzh 发表于 2017-11-14 13:21
数组公式A3下拉
F3下拉
G3下拉

成功了，非常非常感谢，再问下如果sheet名称不规律的话A列应该怎么写呢

mmlzh

操作比较麻烦一些，
1、“汇总”表的G7到J7的数字先清空。
2、定义名称：SHT1, 公式：=GET.WORKBOOK(1)
3、“汇总”表A3数组公式

1. =IFERROR(INDEX(SHT1,SMALL(IF(N(INDIRECT(TRANSPOSE(SHT1)&"!R7C"&COLUMN($G:$J),0))>0,ROW($1:$9)),ROW(1:1))),"")

复制代码

下拉
4、将A列复制，选择性粘贴，粘贴为数值，可自行删除多余的工作簿名字符（用替换功能）

mmlzh

5、数组公式F3

1. =IFERROR(INDIRECT($A3&"!R7C"&SMALL(IF(N(INDIRECT($A3&"!R7C"&COLUMN($G:$J),0))>0,COLUMN($G:$J)),COUNTIF($A$2:$A3,$A3)),0),"")

复制代码

G3

1. =IFERROR(INDIRECT($A3&"!R5C"&SMALL(IF(N(INDIRECT($A3&"!R7C"&COLUMN($G:$J),0))>0,COLUMN($G:$J)),COUNTIF($A$2:$A3,$A3)),0),"")

复制代码

下拉

mmlzh

另一方法
把工作表名放到K2~K9(注意要完全匹配)
A3

1. =IFERROR(INDEX($K$2:$K$9,SMALL(IF(N(INDIRECT($K$2:$K$9&"!R7C"&COLUMN($G:$J),0))>0,ROW($1:$8)),ROW(1:1))),"")

复制代码