把Ｎ张工作簿变成一个Ｎ行的数据汇总表.docVIP

把Ｎ张工作簿变成一个Ｎ行的数据汇总表 　　公司下属有20几个分项工程部，每个分项工程部每月都得上报一张建材用量表(如图1)，这是我事先做好下发的所以表格格式完全一样，数据也都是在“建材”工作表中。我每月都得把报来的这20几份Excel工作簿文件中的工程名称(B3)、各种材料用量(B5:B14)汇总到一张表中。汇总原理倒是简单，只要在汇总表中输入公式引用相应工作簿中的单元格即可。难的是这大量的引用公式既无法复制，也不能使用填充功能批量生成，数以百计的单元格若逐一输入公式还不累死?幸好我找到了一个简单的方法，下面就以Excel2007为例向大家介绍一下。 　　 　　整理T作簿 　　 　　把所有工作簿复制到同一个文件夹下(例如D:＼建材)，然后选中所有工作簿文件，右击其中的任一文件选择“重命名”，输入文件名“(1).xls”，回车确定就把这些文件按(1).xls、(2).xls、(3).xls……的序列重命名。必须在英文半角下输入文件名才能按序列批量重命名。 　　 　　 　　设计汇总表 　　 　　打开Excel新建一个建材汇总工作簿，在其中创建“汇总”工作表并设计好汇总表格。按住ctrl键拖动“汇总”工作表标签复制出一份。汇总(2)工作表，把它重命名为“临时”工作表，把A3的序号改成工作簿名，在A4输入(1).xls，选中A4，用鼠标左键按住填充柄向下拖动到第100行，在A4:A100中按序列填入要汇总的工作簿名。 　　 　　生成公式 　　 　　在“临时”工作表的B4单元格输入公式==D:＼建材＼[＆$A4]建材’!B3”，公式的作用就是在A4的文本前面加上“=D:＼建材＼[”后面加上“]建材!B3”显示出来。 　　选中B4向右拖动填充柄，把这公式复制填充至L4单元格。然后逐一选中C4、D4……L4单元格，把其公式中的B3分别改成B5、B6……B14，以对应(1).xls工作簿建材工作表中的相应单元格。选中B4:L4，拖动其填充柄向下复制填充至1D0行，这样在B4:U00就会自动显示出类似=D:＼建材＼[(1).xls]建材!B3这样的公式。 　　 　　引用数据 　　 　　在“临时”工作表B:L列中按汇总工作簿个数选中相等行数的公式，以汇总20个工作簿来说，则要选中B4:L23。按“Ctrl+c”键复制，打开windows的记事本，按“ctrl+V”把复制的内容粘贴到记事本中，再按“Ctrl+A”全选记事本中的内容按“Ctrl+c”复制。返回Excel，在“汇总表”工作表中选中B4单元格进行粘贴，就可以看到20张工作簿中数据的汇总了(如图2)。 　　 　　 　　一劳永逸 　　 　　OK，到此汇总表已经完全做好了。以后每月只要清空“D:＼建材＼”文件夹，按第1步操作把要汇总的新工作簿全部复制到这文件夹中按序列重命名。然后打开“汇总表”，单击“安全警告”栏上的“选项”按钮，在弹出的安全选项对话框中单击选中“启用此内容”单选项，确定更新链接后即可看到新的汇总数据。 　　如果有增加的工程项目，只要按第3步的操作从“临时”工作表中选中与工作簿数量相应行数的公式粘贴到“汇总”工作表中即可，若是工程项目减少，则只要在“汇总”工作表后面删掉减少的行数就行了。 　　在此为了介绍方便我们选用了一张相对比较简单的表格来进行汇总，其实不管原表格有多复杂，具体操作也没有什么不同，相差的只是在“把公式中的B3分别改成B5、B6……B14以对应(1).xls工作簿建材工作表的单元格”这一步会多花点时间而已。此外，虽然前面说了是以Excel2007为例介绍的，但其实这些操作在2003及以前版本的Excel中也同样适用。 3