把Ｎ張工作簿變成一個Ｎ行的數(shù)據(jù)匯總表

公司下屬有20幾個分項工程部，每個分項工程部每月都得上報一張建材用量表(如圖1)，這是我事先做好下發(fā)的所以表格格式完全一樣，數(shù)據(jù)也都是在“建材”工作表中。我每月都得把報來的這20幾份Excel工作簿文件中的工程名稱(B3)、各種材料用量(B5:B14)匯總到一張表中。匯總原理倒是簡單，只要在匯總表中輸入公式引用相應(yīng)工作簿中的單元格即可。難的是這大量的引用公式既無法復(fù)制，也不能使用填充功能批量生成，數(shù)以百計的單元格若逐一輸入公式還不累死?幸好我找到了一個簡單的方法，下面就以Excel2007為例向大家介紹一下。

整理T作簿

把所有工作簿復(fù)制到同一個文件夾下(例如D:＼建材)，然后選中所有工作簿文件，右擊其中的任一文件選擇“重命名”，輸入文件名“(1).xls”，回車確定就把這些文件按(1).xls、(2).xls、(3).xls……的序列重命名。必須在英文半角下輸入文件名才能按序列批量重命名。

設(shè)計匯總表

打開Excel新建一個建材匯總工作簿，在其中創(chuàng)建“匯總”工作表并設(shè)計好匯總表格。按住ctrl鍵拖動“匯總”工作表標簽復(fù)制出一份。匯總(2)工作表，把它重命名為“臨時”工作表，把A3的序號改成工作簿名，在A4輸入(1).xls，選中A4，用鼠標左鍵按住填充柄向下拖動到第100行，在A4:A100中按序列填入要匯總的工作簿名。

生成公式

在“臨時”工作表的B4單元格輸入公式=\"='D:＼建材＼[\"＆$A4\"]建材’!B3”，公式的作用就是在A4的文本前面加上“='D:＼建材＼[”后面加上“]建材'!B3”顯示出來。

選中B4向右拖動填充柄，把這公式復(fù)制填充至L4單元格。然后逐一選中C4、D4……L4單元格，把其公式中的B3分別改成B5、B6……B14，以對應(yīng)(1).xls工作簿建材工作表中的相應(yīng)單元格。選中B4:L4，拖動其填充柄向下復(fù)制填充至1D0行，這樣在B4:U00就會自動顯示出類似='D:＼建材＼[(1).xls]建材'!B3這樣的公式。

引用數(shù)據(jù)

在“臨時”工作表B:L列中按匯總工作簿個數(shù)選中相等行數(shù)的公式，以匯總20個工作簿來說，則要選中B4:L23。按“Ctrl+c”鍵復(fù)制，打開windows的記事本，按“ctrl+V”把復(fù)制的內(nèi)容粘貼到記事本中，再按“Ctrl+A”全選記事本中的內(nèi)容按“Ctrl+c”復(fù)制。返回Excel，在“匯總表”工作表中選中B4單元格進行粘貼，就可以看到20張工作簿中數(shù)據(jù)的匯總了(如圖2)。

一勞永逸

OK，到此匯總表已經(jīng)完全做好了。以后每月只要清空“D:＼建材＼”文件夾，按第1步操作把要匯總的新工作簿全部復(fù)制到這文件夾中按序列重命名。然后打開“匯總表”，單擊“安全警告”欄上的“選項”按鈕，在彈出的安全選項對話框中單擊選中“啟用此內(nèi)容”單選項，確定更新鏈接后即可看到新的匯總數(shù)據(jù)。

如果有增加的工程項目，只要按第3步的操作從“臨時”工作表中選中與工作簿數(shù)量相應(yīng)行數(shù)的公式粘貼到“匯總”工作表中即可，若是工程項目減少，則只要在“匯總”工作表后面刪掉減少的行數(shù)就行了。

在此為了介紹方便我們選用了一張相對比較簡單的表格來進行匯總，其實不管原表格有多復(fù)雜，具體操作也沒有什么不同，相差的只是在“把公式中的B3分別改成B5、B6……B14以對應(yīng)(1).xls工作簿建材工作表的單元格”這一步會多花點時間而已。此外，雖然前面說了是以Excel2007為例介紹的，但其實這些操作在2003及以前版本的Excel中也同樣適用。