Excel文件，有几个不同工作表。查找所有符合条件的行，并将整行复制到新的工作表中。

Excel文件，有几个不同工作表。查找所有符合条件的行，并将整行复制到新的工作表中。表格如下：是否完成文件名容量完成001100完成002200003150004120完... Excel文件，有几个不同工作表。查找所有符合条件的行，并将整行复制到新的工作表中。
表格如下：

是否完成文件名容量
完成 001 100
完成 002 200
003 150
004 120
完成 005 80
006 300

这是sheet1 。。。 sheet2,sheet3是同样的结构。。

要求把第一列“是否完成”中，值为“完成”的行，查找并复制到新的工作表（sheet4）中。
是否完成文件名容量
完成 001 100
完成 002 200
003 150
004 120
完成 005 80
006 300

希望能自动完成。。不用手动复制结果。。谢谢展开

 我来答

4个回答

#合辑# 面试问优缺点怎么回答最加分？

huaniangme
2010-10-10 · TA获得超过859个赞

知道小有建树答主

回答量：631

采纳率：100%

帮助的人：563万

我也去答题访问个人页

关注

展开全部

假定你有Sheet1,Sheet2,Sheet3三个工作表，所有数据从2行开始，在Sheet4中得出完成的列表，直接用公式解决的方法如下：

(1)三个源表中增加1列作为辅助列，我示例增加在D列，D2 =IF(A2="","",1) 公式下拉到有数据的最后一行

(2)Sheet4中增加辅助D列，D2 =IF(A2="","",IF(COUNTIF($A$2:$A2,$A2)>SUM(COUNTIF(Sheet1!$D$2:$D$1000,1),COUNTIF(Sheet2!$D$2:$D$1000,1)),"Sheet3",IF(COUNTIF($A$2:$A2,$A2)>SUM(COUNTIF(Sheet1!$D$2:$D$1000,1)),"Sheet2","Sheet1"))) 用它来判断当前行应该取值哪个工作表，公式向下填充到超过完成总数的行

(3)Sheet4的A2起输入“完成”，一直向下填充到超过完成总数的行

(4)Sheet4的B2 =INDIRECT($D2&"!"&ADDRESS(SUMPRODUCT(LARGE((INDIRECT($D2&"!$D$2:$D$1000")=1)*ROW(INDIRECT($D2&"!$D$2:$D$1000")),COUNTIF(INDIRECT($D2&"!$D$2:$D$1000"),1)-(COUNTIF($A$2:$A2,$A2)-COUNTIF($D$2:$D2,"<>"&$D2)-1))),COLUMN())) 其中1000为前三个表中最后有数据的行标，公式向右填充到C列，然后向下填充到超过完成总数的行，出现 #NUM! 表示数据已统计完

当在前三个表中增加完成时，只要对应D列为1，则Sheet4会在对应位置加入

如果分表超过3个表，只需修改总表中D列公式判断出当前行应该引用哪个工作表即可

经研究，前三表不用辅助列，Sheet4相关公式修改如下：

D2 =IF($A2="","",IF(COUNTIF($A$2:$A2,$A2)<=SUM(COUNTIF(Sheet1!$A$2:$A$1000,$A2)),"Sheet1",IF(COUNTIF($A$2:$A2,$A2)<=SUM(COUNTIF(Sheet1!$A$2:$A$1000,$A2),COUNTIF(Sheet2!$A$2:$A$1000,$A2)),"Sheet2","Sheet3")))

B2 =INDIRECT($D2&"!"&ADDRESS(SUMPRODUCT(LARGE((INDIRECT($D2&"!$A$2:$A$1000")=$A2)*ROW(INDIRECT($D2&"!$A$2:$A$1000")),COUNTIF(INDIRECT($D2&"!$A$2:$A$1000"),$A2)-(COUNTIF($A$2:$A2,$A2)-COUNTIF($D$2:$D2,"<>"&$D2)-1))),COLUMN()))

Sheet4仍保持辅助列以降低公式的复杂程度，其他步骤相同