辦公世代 文件不打開　Excel數據照樣引用

俞木發

以總公司下屬每個子公司工資報表的“加班工資”數據為例。由于統計員經常計算錯誤，公司決定讓小李協助小王核算加班數據。具體操作是，小王制作工資表數據，同時讓小李再單獨計算加班數據，然后在小王的工作簿中引用小李工作簿中的“加班工資”數據進行核對。常規的操作是，先打開小王和小李的工作簿，接著復制小李工作簿中的統計數據到小王的工作簿中，再使用條件格式進行核對。由于需要核對的報表很多，這種方法顯然費時費力。下面筆者介紹兩種更高效的數據引用方法。這里假設“D：＼work”是小王電腦中的共享文件夾，小李制作的核對文件是“5月工資單.xlsx”，小李制作完成后放置在上述的共享文件夾中。下面的操作均是在小王的電腦中完成的。

方法一、自行創建引用公式

1粘貼鏈接獲取公式

在Excel中先打開兩個工作簿文件，接著在小王的工作簿中定位到M列設定一個核對數據列，然后切換到小李的工作簿（工作簿只有A列核對數據，格式和小王工作簿的F列一致），復制A 2單元格中的數據，再返回到小王的工作簿中，定位到M2單元格，依次點擊“開始→粘貼→選擇性粘貼→其他粘貼選項→粘貼鏈接”，這樣在M2單元格的地址欄中就可以看到類似“=[5月工資單.xlsx]Sheet1！$A$2”的公式（圖1）。

2更改公式

現在將小李的工作簿關閉，再返回到小王的工作簿窗口中。此時公式會自動變為“=' D：＼wo r k ＼ [5月工資單.x lsx]Sheet1'！$A$2”，即在原來的公式前加上了工作簿的路徑信息，表示這里引用的是“D：＼work＼5月工資單.xls”工作簿“sheet1”的A 2單元格中的數據。也就是說只要使用類似上述的公式，那么就可以在不打開文件的前提下實現對數據的引用。不過，原來公式中是絕對引用，需要稍加修改。繼續定位到小王的工作簿的M2單元格，將公式修改為“=' D：＼work＼[5月工資單.xlsx]Sheet1'！$A2”，即對指定數據行的相對引用，然后下拉填充到M6單元格（圖2）。

3以條件格式核對數據

選中F2：F6數據區域，依次點擊“開始→條件格式→新建規則→使用公式確定需要設置的單元格”，在“為符合此公式的值設置格式”下輸入公式“=$F2<>M2”，然后設置格式為單元格填充紅色（圖3）。這樣只要小王的加班數據和小李的輸入不同，就會自動填充紅色顯示了。此時就需要對紅色數據進行再次核對，核對完成后刪除M列數據即可。

4保存為模板使用

上述文件還可以作為模板使用。首先要求小李的工作簿名稱按照“X月工資單.xls”名稱規范命名，并且保存在共享文件夾“D：＼work”下。這樣小王在每次做完加班工資統計后，如做完6月份統計，按下“Ctrl+H”快捷鍵打開“查找和替換”對話框，在“查找內容”處輸入“5月工資單”、“替換為”處輸入“6月工資單”，最后點擊“全部替換”（圖4）。

這樣在完成替換后，上述工作簿中的公式就會自動變為“='D：＼work＼[6月工資單.xlsx]Sheet1'！$A2”。小王只要在“D：＼work”目錄下收到小李發來的工資單文件，那么打開小王的工作簿后會提示是否需要更新鏈接數據，點擊“更新”，即可自動更新引用的數據并進行核對了（圖5）。

方法二、使用VBA腳本引用

熟悉VBA代碼的朋友還可以使用VBA快速引用數據。先到“https：//share.weiyun.com/IrCwQJln”下載所需的代碼。接著打開小王的工作簿，在A列之前插入一個新列作為引用數據列（因為代碼引用的是未打開文件的A1：A6區域的核對數據到當前工作表的A1：A6區域，為了避免覆蓋原始數據，所以要插入一個新列），并將列顏色填充為黃色顯示。接著按下“Alt+F11”組合鍵打開VBA編輯窗口，依次點擊“插入→模塊”，在文本編輯框中粘貼下載到的代碼（圖6）。

關閉當前VBA窗口，繼續依次點擊“開發工具→宏”，在宏的列表中選擇“數據核對”，點擊“運行”。這樣執行該宏后，在插入的A列中就會自動引用“D：＼work＼5月工資單.xlsx”中的加班核對數據（圖7）。

其他操作同上，再設置條件格式進行數據標注，需要將圖3所示的公式改為“=$A2<>G2”（圖8）。同樣，如果發現G列的加班數據和A列核對數據不同，小王就需要進行重新核對，完成后將引用的A列數據刪除即可。以后如果需要引用其他工作簿中的數據，那么只要將代碼中的“5月工資單.xlsx”改為對應的文件名即可。