化繁為簡 高效制作自動化報表

俞木發

制作綜合報表時，我們經常要匯總多個工作表的數據。常規的方法是打開指定文件，然后將多個報表數據復制粘貼到一個表中，最后再進行計算匯總。由于涉及的數據較多，這樣操作不僅麻煩，而且很容易出錯。借助恰當的公式，則可以高效、自動地完成。

案例介紹

公司技術部門每個月都要求制作一個復核報表，內容是對質檢部的檢驗結果進行復核。日常的檢驗報表是每個部門各自獨立制作一份工作簿文件，最終要按特定格式匯總在“抽檢復核”報表中（圖1）。抽檢復核報表格式和每個子表并不同，因為公司的產品型號非常多，每個月技術部只是對每個部門隨機抽取若干個品名，然后根據已有的結果進行復核（圖2）。以前的做法是，每個月收集好各個車間統計數據，然后技術部確定隨機品名后再從中復制指定數據到報表中供復核比對，由于每個車間報表數據很多，抽檢的品名又不固定，手工操作很費事。現在進行改良，需要借助公式高效自動完成。

首先來分析復核報表的要求，需要復核時由技術部在B2：B8單元格根據生產部門品名，隨機輸入對應的復核品名如G1，然后在C2：D2區域依次填入“一車間xls”文件中和G1對應的數據，如果隨機輸入的是二車間的品名，則填入對應的“二車間xls”文件中的數據。也就是說要在復核報表文件中引用不同報表文件中對應的數據，這里可以借助VLOOKUP函數進行引用完成。

因為最終目的是要自動生成，為了方便對工作簿中工作表的引用，要求每個單位制作的工作簿的名稱要和最終復核報表中部門的名稱一致，工作表名稱則統一使用默認的Sheet1。如一車間的報表名稱要為“一車間.xlsx”，打開后其工作表的名稱為Sheet1，其他工作簿的要求類似（圖3）。

接著在自己的電腦上新建一個名為“K：＼檢驗報表文件”的目錄，右擊將其設置為“共享”，并且將共享目錄對Everyone設置讀寫權限，也就是企業內部局域網的用戶都可以訪問該共享目錄（圖4）。

完成上述要求后，以后每個部門完成自己的報表后只要上傳到該目錄中保存。打開“抽檢復核”報表定位到C 3，輸入公式“=VLOOKUP（B3，INDIRECT（"["&A3&".xlsx] sheet1！A2：D27"），2，FALSE）”并下拉，這樣即可完成對各個工作簿數據的引用。

公式解釋

B3的值是搜索內容，即從每個報表中查找出對應的品名，這里是隨機選擇每個單位品名數值。

A2：D27則為每個報表的數據區域，即從這個區域里找出符合要求的數據，因為這里是需要從各個子表中查找數據，而每個子表的數據范圍可能不同，為了確保數據都可以找到，這里需要以數據范圍最廣的子表為準。

Sheet1是每個工作簿對應的工作表的名稱，這里統一引用第一個工作表的數據。

"["&A3&".xlsx]是一個文本函數，將A3單元格的內容和“.xlsx”字符串聯合起來，組成一個新字符串。因為A列單元格的內容是和對應工作簿的名稱一致的，這樣通過文本函數的聯合，就可以實現對不同工作簿的引用。這里注意報表文件都要放置在同一個目錄，工作簿文件名要用[]引用，工作表則用“！”引用。

INDIRECT：通過上述文本函數，INDIRECT可以實現對工作簿文件的動態引用，并將這個引用作為Vlookup函數查找范圍。

2表示引用子表中的2列的內容即產量的數值。FALSE表示查找時要求完全匹配。

定位到C3輸入公式并下拉“=VLOOKUP（B3， INDIRECT（"["&A3&".xlsx]sheet1！A2：D27"）， 3 ， F A L S E ）”，定位到D 3輸入公式并下拉“=VLOOKUP（B3，INDIRECT（"["&A3&".xlsx] sheet1！A2：D27"），4，FALSE）”，依次引用工作簿的數據。

完成上述操作后，如果技術部需要使用這個報表進行復核，只要全選“K：＼檢驗報表文件”目錄下所有xlsx文件回車打開，這樣在“抽檢復核.xlsx”的A2開始隨機輸入一車間對應的品名，輸入數值后即可自動進行引用，最終只要在復核結果輸入復核的數值進行比對，報表即可自動生成了（圖5）。

以后每個部門只要完成報表的輸入后傳輸到共享目錄（文件名不要更改），打開“抽檢復核.xlsx”隨機輸入檢測品名，即可快速生成所需的報表了，各個工作簿數據的更改會自動被同步引用（圖6）。