办公世代 文件不打开 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”改为对应的文件名即可。