如何同步Microsoft Excel电子表格-CSDN博客

You can sync Microsoft Excel spreadsheets to ensure that changes in one will automatically be reflected in another. It is possible to create links between different worksheets as well as separate Excel workbooks. Let’s look at three ways to do this.

您可以同步Microsoft Excel电子表格，以确保其中的一项更改将自动反映在另一项中。可以在不同的工作表以及单独的Excel工作簿之间创建链接。让我们看一下执行此操作的三种方法。

使用粘贴链接功能同步Excel电子表格 (Sync Excel Spreadsheets Using the Paste Link Feature)

The Paste Link functionality in Excel provides a simple way to sync Excel spreadsheets. In this example, we want to create a summary sheet of sales totals from multiple different worksheets.

Excel中的粘贴链接功能提供了一种同步Excel电子表格的简单方法。在此示例中，我们要从多个不同的工作表创建销售总额汇总表。

Start by opening your Excel spreadsheet, clicking on the cell that you want to link to, and then selecting the “Copy” button on the “Home” tab.

首先打开Excel电子表格，单击要链接的单元格，然后在“主页”选项卡上选择“复制”按钮。

Select the cell that you are linking from, click the “Paste” list arrow, then select “Paste Link.”

选择您要链接的单元格，单击“粘贴”列表箭头，然后选择“粘贴链接”。

The address that the cell it is synced to is shown in the Formula Bar. It contains the sheet name followed by the cell address.

与其同步的单元格的地址显示在编辑栏中。它包含工作表名称，后跟单元格地址。

Link to the source data in the Formula Bar

使用公式同步Excel电子表格 (Sync Excel Spreadsheets Using a Formula)

Another approach is to create the formula ourselves without using the Paste Link button.

另一种方法是在不使用“粘贴链接”按钮的情况下自行创建公式。

在不同的工作表上同步单元格 (Sync Cells on Different Worksheets)

First, click the cell you are creating the link from and type “=”.

首先，单击您要从中创建链接的单元格，然后键入“ =”。

Next, select the sheet containing the cell you want to link to. The sheet reference is shown in the Formula Bar.

接下来，选择包含要链接的单元格的工作表。工作表参考显示在公式栏中。

Finally, click the cell you want to link to. The completed formula is shown in the Formula Bar. Press the “Enter” key.

最后，单击要链接的单元格。完成的公式显示在公式栏中。按“ Enter”键。

在单独的工作簿上同步单元格 (Sync Cells on Separate Workbooks)

You can also link to a cell on the sheet of a different workbook entirely. To do this, you must first make sure that the other workbook is open before you begin the formula.

您也可以完全链接到其他工作簿的工作表上的单元格。为此，在开始公式之前，您必须首先确保另一个工作簿已打开。

Click the cell you want to link from and type “=”. Switch to the other workbook, select the sheet, then click on the cell to link to. The workbook name precedes the sheet name in the formula bar.

单击您要链接的单元格，然后键入“ =”。切换到另一个工作簿，选择工作表，然后单击要链接到的单元格。在编辑栏中，工作簿名称位于工作表名称之前。

If the Excel workbook you have linked to is closed, the formula will shown the complete path to the file.

如果您链接到的Excel工作簿已关闭，则公式将显示文件的完整路径。

And when the workbook containing the link to another workbook is opened, you will probably be greeted with a message to enable the update of links. This depends on your security settings.

并且当打开包含指向另一个工作簿的链接的工作簿时，可能会收到一条消息，提示您启用链接更新。这取决于您的安全设置。

Click “Enable Content” to ensure that updates in the other workbook are automatically reflected in the current one.

单击“启用内容”以确保另一工作簿中的更新自动反映在当前工作簿中。

使用查找功能同步Excel电子表格 (Sync Excel Spreadsheets Using a Lookup Function)

The previous methods of syncing two sheets or workbooks use links to a specific cell. Sometimes, this may not be good enough because the link will return the incorrect value if the data gets sorted and moved to a different cell. In these scenarios, using a lookup function is a good approach.

同步两个工作表或工作簿的先前方法使用指向特定单元格的链接。有时，这可能不够好，因为如果对数据进行排序并将其移至其他单元格，则链接将返回错误的值。在这些情况下，使用查找功能是一种很好的方法。

There are numerous lookup functions, but the most commonly used is VLOOKUP, so let’s use that.

查找功能很多，但是最常用的是VLOOKUP ，所以让我们使用它。

In this example, we have a simple list of employee data.

在此示例中，我们有一个简单的员工数据列表。

On another worksheet, we are storing training data about the employees. We want to search for and return the age of the employees for analysis.

在另一个工作表上，我们存储了有关员工的培训数据。我们想搜索并返回员工年龄进行分析。

This function requires four pieces of information: what to look for, where to look, the column number with the value to return, and what type of lookup you need.

此函数需要四项信息：查找内容，查找位置，具有要返回的值的列号以及所需的查找类型。

The following VLOOKUP formula was used.

使用了以下VLOOKUP公式。

=VLOOKUP(A2,Employees!A:D,4,FALSE)

VLOOKUP function to link to data on another worksheet

A2 contains the employee ID to look for on the Employees sheet in the range A:D. Column 4 of that range contains the age to return. And False specifies an exact lookup on the ID.

A2包含要在“员工”表上查找的员工ID，范围为A：D。该范围的第4列包含要返回的年龄。 False指定对ID的精确查找。

The method you choose to sync Excel spreadsheets together is largely decided by how your data is structured and how it is used.

您选择将Excel电子表格同步在一起的方法在很大程度上取决于数据的结构和使用方式。