Microsoft Excel是一个非常强大的多功能工具，任何人都可以使用。但是，如果您是每天都在使用电子表格的人，那么您可能需要了解的不仅仅是使用Excel的基础知识。了解一些简单的技巧对Excel 大有帮助(Excel)。一个很好的例子是知道如何在工作表和工作簿之间链接Excel中的单元格。(Excel)
从长远来看，学习这一点将节省大量时间和混乱(time and confusion)。
为什么要在 Excel 中链接单元格数据 (Why Link Cell Data in Excel )
能够跨不同工作表引用数据是一项有价值的技能，原因有几个。
首先，它将使您的电子表格更容易组织。例如，您可以使用一个工作表或工作簿(sheet or workbook)来收集原始数据，然后为报告和/或汇总创建一个新选项卡或一个新工作簿。
将两者之间的单元格链接起来后，您只需在其中一个单元格中更改或输入新数据，结果将在另一个单元格中自动更改。所有这些都无需在不同的电子表格之间来回移动。
其次，这个技巧将避免在多个电子表格中重复相同的数字。这将减少您的工作时间和计算错误的可能性。
在下面的文章中，您将学习如何链接其他工作表中的单个单元格、链接一系列单元格，以及如何链接来自不同Excel文档的单元格。
如何链接两个单细胞(How to Link Two Single Cells)
让我们首先链接位于不同工作表（或选项卡）但在同一个Excel 文件(Excel file)中的两个单元格。为此，请按照以下步骤操作。
- 在Sheet2中，在单元格中键入等号(equal symbol)(=)。
- 转到另一个选项卡 ( Sheet1 ) 并单击要链接到的单元格。
- 按Enter完成公式。
现在，如果您单击Sheet2中的单元格，您将看到Excel在编辑(Excel)栏中(formula bar)为您写入路径。
例如，=Sheet1!C3，其中Sheet1是工作表的名称，C3是您要链接到的单元格，感叹号(exclamation mark)(!) 用作两者之间的分隔符。
使用这种方法，您可以手动链接而无需离开原始工作表。只需(Just)在单元格中直接 输入参考公式即可。(reference formula)
注意：如果工作表名称(sheet name)包含空格（例如Sheet 1），则在将引用键入单元格时需要将名称放在单引号中。喜欢=’Sheet 1′!C3。这就是为什么有时让Excel为您编写参考公式(reference formula)更容易、更可靠的原因。
如何链接一系列单元格(How to Link a Range of Cells)
在Excel(Excel)中链接单元格的另一种方法是链接来自不同Excel 选项卡(Excel tabs)的一系列单元格。当您需要将相同的数据存储在不同的工作表中而不必编辑两个工作表时，这很有用。
要在Excel(Excel)中链接多个单元格，请按照以下步骤操作。
- 在包含数据的原始选项卡 ( Sheet1 ) 中，突出显示要引用的单元格。
- 复制单元格（Ctrl / Command + C，或右键单击并选择Copy）。
- 转到另一个选项卡 ( Sheet2 )，然后单击要放置链接的单元格（或多个单元格）。
- 右键单击单元格（-s）并选择选择性粘贴...(Paste Special…)
- 在菜单的左下角选择(menu choose) Paste Link。
当您单击Sheet2中新链接的单元格时，您可以在公式选项卡中看到对(formula tab)Sheet1中单元格的引用。现在，每当您更改Sheet1(Sheet1)中所选单元格中的数据时，它都会自动更改Sheet2中链接单元格中的数据。
如何将单元格与函数链接
当您进行汇总并希望将它们保存在与原始原始数据分开的工作表上时，链接到一组单元格可能很有用。
假设您需要在Sheet2中编写一个(Sheet2)SUM 函数(SUM function)，该函数将链接到Sheet1 中(Sheet1)的多个单元格。为此，请转到Sheet2并单击要放置函数的单元格。像往常一样编写函数，但是在选择单元格范围时，请转到另一张表并如上所述突出显示它们。
您将拥有=SUM(Sheet1!C3:C7)，其中SUM 函数将(SUM function)Sheet1中单元格 C3:C7 的内容相加。按Enter完成公式。
如何链接来自不同 Excel 文件的单元格 (How to Link Cells From Different Excel Files )
不同Excel文件（或工作簿）之间的链接过程实际上与上述相同。除了，当您粘贴单元格时，将它们粘贴到不同的电子表格而不是不同的选项卡中。以下是如何通过 4 个简单的步骤进行操作。
- 打开两个 Excel 文档。
- 在第二个文件 ( Help Desk Geek ) 中，选择一个单元格并键入一个等号(equal symbol )(=)。
- 切换到原始文件（在线技术提示(Online Tech Tips)），然后单击要链接到的单元格。
- 按Enter完成公式。
现在，链接单元格的公式在方括号中 也有另一个工作簿名称。(workbook name)
如果您关闭原始Excel 文件并(Excel file and look)再次查看公式，您会发现它现在也具有整个文档的位置。这意味着如果您将链接到另一个地方的原始文件移动或重命名(place or rename)，链接将停止工作。这就是为什么将所有重要数据保存在同一个Excel 文件(Excel file)中更可靠的原因。
成为专业的 Microsoft Excel 用户(Become a Pro Microsoft Excel User)
在工作表之间链接单元格只是您如何在Excel中过滤数据并保持电子表格井井有条的一个示例。查看我们汇总的其他一些Excel 提示和技巧(Excel tips and tricks)，以帮助您成为高级用户。
您还知道并使用过哪些其他简洁的Excel生活窍门？你知道在Excel中链接单元格的任何其他创造性方法吗？在下面的评论部分(comment section)与我们分享(Share)。
Link Cells Between Sheets and Workbooks In Excel
Microsoft Excel is a very powerful multi-purpoѕe tool thаt anyone can use. But if you’re someonе who works with spreadsheets every day, you might need to know more than just the basics of using Excel. Knowing a few simple tricks can go a long way with Excel. A good example is knowing how to link cells in Excel between sheets and workbooks.
Learning this will save a lot of time and confusion in the long run.
Why Link Cell Data in Excel
Being able to reference data across different sheets is a valuable skill for a few reasons.
First, it will make it easier to organize your spreadsheets. For example, you can use one sheet or workbook for collecting raw data, and then create a new tab or a new workbook for reports and/or summations.
Once you link the cells between the two, you only need to change or enter new data in one of them and the results will automatically change in the other. All without having to move back and forth between different spreadsheets.
Second, this trick will avoid duplicating the same numbers in multiple spreadsheets. This will reduce your working time and the possibility of making calculation mistakes.
In the following article, you’ll learn how to link single cells in other worksheets, link a range of cells, and how to link cells from different Excel documents.
How to Link Two Single Cells
Let’s start by linking two cells located in different sheets (or tabs) but in the same Excel file. In order to do that, follow these steps.
- In Sheet2 type an equal symbol (=) into a cell.
- Go to the other tab (Sheet1) and click the cell that you want to link to.
- Press Enter to complete the formula.
Now, if you click on the cell in Sheet2, you’ll see that Excel writes the path for you in the formula bar.
For example, =Sheet1!C3, where Sheet1 is the name of the sheet, C3 is the cell you’re linking to, and the exclamation mark (!) is used as a separator between the two.
Using this approach, you can link manually without leaving the original worksheet at all. Just type the reference formula directly into the cell.
Note: If the sheet name contains spaces (for example Sheet 1), then you need to put the name in single quotation marks when typing the reference into a cell. Like =’Sheet 1′!C3. That’s why it’s sometimes easier and more reliable to let Excel write the reference formula for you.
How to Link a Range of Cells
Another way you can link cells in Excel is by linking a whole range of cells from different Excel tabs. This is useful when you need to store the same data in different sheets without having to edit both sheets.
In order to link more than one cell in Excel, follow these steps.
- In the original tab with data (Sheet1), highlight the cells that you want to reference.
- Copy the cells (Ctrl/Command + C, or right click and choose Copy).
- Go to the other tab (Sheet2) and click on the cell (or cells) where you want to place the links.
- Right click on the cell(-s) and select Paste Special…
- At the bottom left corner of the menu choose Paste Link.
When you click on the newly linked cells in Sheet2 you can see the references to the cells from Sheet1 in the formula tab. Now, whenever you change the data in the chosen cells in Sheet1, it will automatically change the data in the linked cells in Sheet2.
How to Link a Cell With a Function
Linking to a cluster of cells can be useful when you do summations and want to keep them on a sheet separate from the original raw data.
Let’s say you need to write a SUM function in Sheet2 that will link to a number of cells from Sheet1. In order to do that, go to Sheet2 and click on the cell where you want to place the function. Write the function as normal, but when it comes to choosing the range of cells, go to the other sheet and highlight them as described above.
You will have =SUM(Sheet1!C3:C7), where the SUM function sums the contents from cells C3:C7 in Sheet1. Press Enter to complete the formula.
How to Link Cells From Different Excel Files
The process of linking between different Excel files (or workbooks) is virtually the same as above. Except, when you paste the cells, paste them in a different spreadsheet instead of a different tab. Here’s how to do it in 4 easy steps.
- Open both Excel documents.
- In the second file (Help Desk Geek), choose a cell and type an equal symbol (=).
- Switch to the original file (Online Tech Tips), and click on the cell that you want to link to.
- Press Enter to complete the formula.
Now the formula for the linked cell also has the other workbook name in square brackets.
If you close the original Excel file and look at the formula again, you will see that it now also has the entire document’s location. Meaning that if you move the original file that you linked to another place or rename it, the links will stop working. That’s why it’s more reliable to keep all the important data in the same Excel file.
Become a Pro Microsoft Excel User
Linking cells between sheets is only one example of how you can filter data in Excel and keep your spreadsheets organized. Check out some other Excel tips and tricks that we put together to help you become an advanced user.
What other neat Excel lifehacks do you know and use? Do you know any other creative ways to link cells in Excel? Share them with us in the comment section below.
