化繁為簡 高效制作自動化報(bào)表

俞木發(fā)

制作綜合報(bào)表時(shí)，我們經(jīng)常要匯總多個(gè)工作表的數(shù)據(jù)。常規(guī)的方法是打開指定文件，然后將多個(gè)報(bào)表數(shù)據(jù)復(fù)制粘貼到一個(gè)表中，最后再進(jìn)行計(jì)算匯總。由于涉及的數(shù)據(jù)較多，這樣操作不僅麻煩，而且很容易出錯。借助恰當(dāng)?shù)墓剑瑒t可以高效、自動地完成。

案例介紹

公司技術(shù)部門每個(gè)月都要求制作一個(gè)復(fù)核報(bào)表，內(nèi)容是對質(zhì)檢部的檢驗(yàn)結(jié)果進(jìn)行復(fù)核。日常的檢驗(yàn)報(bào)表是每個(gè)部門各自獨(dú)立制作一份工作簿文件，最終要按特定格式匯總在“抽檢復(fù)核”報(bào)表中（圖1）。抽檢復(fù)核報(bào)表格式和每個(gè)子表并不同，因?yàn)楣镜漠a(chǎn)品型號非常多，每個(gè)月技術(shù)部只是對每個(gè)部門隨機(jī)抽取若干個(gè)品名，然后根據(jù)已有的結(jié)果進(jìn)行復(fù)核（圖2）。以前的做法是，每個(gè)月收集好各個(gè)車間統(tǒng)計(jì)數(shù)據(jù)，然后技術(shù)部確定隨機(jī)品名后再從中復(fù)制指定數(shù)據(jù)到報(bào)表中供復(fù)核比對，由于每個(gè)車間報(bào)表數(shù)據(jù)很多，抽檢的品名又不固定，手工操作很費(fèi)事。現(xiàn)在進(jìn)行改良，需要借助公式高效自動完成。

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

因?yàn)樽罱K目的是要自動生成，為了方便對工作簿中工作表的引用，要求每個(gè)單位制作的工作簿的名稱要和最終復(fù)核報(bào)表中部門的名稱一致，工作表名稱則統(tǒng)一使用默認(rèn)的Sheet1。如一車間的報(bào)表名稱要為“一車間.xlsx”，打開后其工作表的名稱為Sheet1，其他工作簿的要求類似（圖3）。

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

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

公式解釋

B3的值是搜索內(nèi)容，即從每個(gè)報(bào)表中查找出對應(yīng)的品名，這里是隨機(jī)選擇每個(gè)單位品名數(shù)值。

A2：D27則為每個(gè)報(bào)表的數(shù)據(jù)區(qū)域，即從這個(gè)區(qū)域里找出符合要求的數(shù)據(jù)，因?yàn)檫@里是需要從各個(gè)子表中查找數(shù)據(jù)，而每個(gè)子表的數(shù)據(jù)范圍可能不同，為了確保數(shù)據(jù)都可以找到，這里需要以數(shù)據(jù)范圍最廣的子表為準(zhǔn)。

Sheet1是每個(gè)工作簿對應(yīng)的工作表的名稱，這里統(tǒng)一引用第一個(gè)工作表的數(shù)據(jù)。

"["&A3&".xlsx]是一個(gè)文本函數(shù)，將A3單元格的內(nèi)容和“.xlsx”字符串聯(lián)合起來，組成一個(gè)新字符串。因?yàn)锳列單元格的內(nèi)容是和對應(yīng)工作簿的名稱一致的，這樣通過文本函數(shù)的聯(lián)合，就可以實(shí)現(xiàn)對不同工作簿的引用。這里注意報(bào)表文件都要放置在同一個(gè)目錄，工作簿文件名要用[]引用，工作表則用“！”引用。

INDIRECT：通過上述文本函數(shù)，INDIRECT可以實(shí)現(xiàn)對工作簿文件的動態(tài)引用，并將這個(gè)引用作為Vlookup函數(shù)查找范圍。

2表示引用子表中的2列的內(nèi)容即產(chǎn)量的數(shù)值。FALSE表示查找時(shí)要求完全匹配。

定位到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，F(xiàn)ALSE）”，依次引用工作簿的數(shù)據(jù)。

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

以后每個(gè)部門只要完成報(bào)表的輸入后傳輸?shù)焦蚕砟夸洠ㄎ募灰模蜷_“抽檢復(fù)核.xlsx”隨機(jī)輸入檢測品名，即可快速生成所需的報(bào)表了，各個(gè)工作簿數(shù)據(jù)的更改會自動被同步引用（圖6）。