辦公世代 文件不打開　Excel數(shù)據(jù)照樣引用

俞木發(fā)

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

方法一、自行創(chuàng)建引用公式

1粘貼鏈接獲取公式

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

2更改公式

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

3以條件格式核對(duì)數(shù)據(jù)

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

4保存為模板使用

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

這樣在完成替換后，上述工作簿中的公式就會(huì)自動(dòng)變?yōu)椤?'D：＼work＼[6月工資單.xlsx]Sheet1'！$A2”。小王只要在“D：＼work”目錄下收到小李發(fā)來(lái)的工資單文件，那么打開小王的工作簿后會(huì)提示是否需要更新鏈接數(shù)據(jù)，點(diǎn)擊“更新”，即可自動(dòng)更新引用的數(shù)據(jù)并進(jìn)行核對(duì)了（圖5）。

方法二、使用VBA腳本引用

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

關(guān)閉當(dāng)前VBA窗口，繼續(xù)依次點(diǎn)擊“開發(fā)工具→宏”，在宏的列表中選擇“數(shù)據(jù)核對(duì)”，點(diǎn)擊“運(yùn)行”。這樣執(zhí)行該宏后，在插入的A列中就會(huì)自動(dòng)引用“D：＼work＼5月工資單.xlsx”中的加班核對(duì)數(shù)據(jù)（圖7）。

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