当您在Microsoft Excel中工作时，您可能会发现您的数据变得有点难以理解，数据集分布在不同的工作表、数据透视表等中。但是，您并不总是需要使用多个工作表或Excel文件来处理您的数据，尤其是当您作为一个团队工作时。
为了帮助您保持数据井井有条，您可以在Excel中合并数据。您可以合并来自单独文件的工作表，将单独的Excel文件合并为一个，或者使用合并功能来合并您的数据。
下面介绍如何使用这些方法将Excel文件和数据合并在一起。
如何在 Excel 中移动或复制单个工作表(How To Move Or Copy Single Worksheets In Excel)
典型的Microsoft Excel文件被分解为不同的工作表（或工作表），这些工作表在(Microsoft Excel)Excel窗口底部以选项卡的形式列出。它们就像页面一样，允许您将数据分布在单个文件中的多个工作表中。
您可以在不同的Excel文件（或同一文件，如果您希望复制数据集）之间移动或复制工作表。
- 首先，打开您的Excel文件（或多个文件）。在要从中复制的Excel(Excel)文件的打开窗口中，单击Excel窗口底部要选择的工作表。您可以通过按住Shift并单击每个工作表选项卡来选择多个工作表。
- 要开始复制或移动工作表，请按顶部功能区栏中的主页选项卡。(Home)
- 在此处，单击Format > Move or Copy Sheet。
- 在“移动或复制(Move or Copy)”框中，从“目标框”(To Box)下拉菜单中选择要复制或移动到的Excel文件。(Excel)在“工作表前”(Before Sheet)部分中选择要放置工作表的位置。默认情况下，Excel将移动工作表——如果您希望复制它们， 请单击创建副本复选框。(Create a copy)
- 准备好后，按OK移动或复制工作表。
然后将根据需要移动或复制您选择的工作表，尽管这不会将它们完全合并在一起。
如何将多个 Excel 文件合并在一起(How To Merge Multiple Excel Files Together)
您可以使用 Excel 中的移动(Excel)或复制工作表(Move or Copy Sheet)工具将多个 Excel 文件中的数据合并在一起(Excel)。您可以通过选择文件中的所有活动工作表，然后将它们合并到单个目标文件中，对多个文件重复此过程来执行此操作。
- 为此，请打开您的Excel文件。在要移动或复制到另一个文件的Excel文件的打开窗口中，按住(Excel)Shift键并单击每个工作表选项卡，选择窗口底部的所有工作表选项卡。
- 接下来，从功能区栏中按Home > Format > Move or Copy Sheet
- 在打开的移动或复制(Move or Copy)对话框中，从“到框”(To Box)下拉菜单中选择要合并到的目标Excel文件。(Excel)在“工作表前(Before sheet)”部分中选择要放置合并工作表的位置。如果您想保持原始文件完好无损，请按创建副本(Create a copy)以复制工作表而不是移动它们，然后按确定(OK)开始移动或复制过程。
如果您有多个Excel文件，则需要重复这些步骤以将它们合并到一个文件中。
使用合并工具将 Excel 中的数据合并在一起(Using The Consolidate Tool To Merge Data In Excel Together)
使用上述方法，您可以在不同的Excel电子表格文件之间移动和复制工作表。这会移动数据，但并不能很好地集成数据——数据仍然保存在单独的工作表中。
要解决此问题，您可以使用Excel中的(Excel)合并(Consolidate)工具将多个工作表中的数字数据合并到一个新的单个工作表中。不幸的是，此过程不适用于使用文本的单元格 - 您需要手动剪切和粘贴此数据，或在 Excel 中创建一个 VBA 脚本(create a VBA script in Excel)来为您完成此操作。
为此，您的数据需要以相同的方式在工作表中显示，并带有匹配的标题标签。在开始之前，您还需要从数据中删除所有空白数据（例如，空单元格）。
- 要使用此方法在Excel中合并数据，请打开您的Excel文件，然后在用于合并数据的目标Excel文件中，通过按窗口底部工作表选项卡旁边的+ (plus) button创建一个新工作表。
- 在您的新工作表中，按Data > Consolidate。
- 在Consolidate窗口中，确保在Function下拉菜单中选择Sum 。单击(Click)参考(Reference)输入框旁边的按钮开始选择您的第一个数据集——这是您希望合并的数据。如果您愿意，也可以自己键入对单元格范围的引用。
- 要使用鼠标选择数据，请在“合并 - 参考(Consolidate – Reference)”框可见后单击包含工作表的工作表，选择数据，然后按“插入(Insert)”按钮。
- 单元格区域将出现在“参考(Reference)”输入框中——单击“添加(Add)”按钮将其添加到“所有参考(All References)”列表中。
- 重复上述步骤以选择其他数据集，单击“添加(Add)”将每个数据集添加到您的工作表中。如果您的数据具有列或行标签，请通过点击使用标签(Use labels)部分中的顶行(Top row)或左列(Left column )复选框来确保这些标签得到正确处理。
- 如果要继续编辑原始单独工作表中的数据，请单击以启用创建指向源数据的链接(Create links to source data)复选框。这将确保对原始数据的任何更改稍后都会反映在合并的工作表中。
- 您的合并数据将出现，行标签旁边有一个选项菜单，以便能够在不同的数据视图之间切换 - 单击这些选项（例如，1 按钮(1 button)）隐藏或查看您已部分合并的数据或在全。
如果您选择创建指向源数据的链接，您的新工作表将充当单独工作表和文件的镜像版本。否则，您的数据将被复制到此工作表中，供您直接编辑。
使用合并的 Excel 数据(Using Merged Excel Data)
无论您决定将Excel中的数据合并到单个工作表或文件中，还是希望将工作分散到多个文件中，这些提示都可以帮助您保持井井有条。准备就绪后，您可以开始与其他人共享您的 Excel 文件(share your Excel file)，以便作为一个团队有效地协作和分析您的数据。
如果您担心丢失对合并数据的更改跟踪，请不要担心 - 您可以使用Excel(track changes in Excel)的内置跟踪功能轻松跟踪更改。
在下面的评论部分让我们知道您的Excel数据分析技巧。
How To Merge Data In Multiple Excel Files
When you’re workіng in Microsoft Excel, you may find that your data has become а little hard to follow, with data sеts spread across separate sheets, pivot tables, and more. You don’t always need to use multiple worksheets or Excel files tо work on your dаta, however, eѕpecially if you’re working as a team.
To help you keep your data organized, you can merge data in Excel. You can merge worksheets from separate files, merge separate Excel files into one, or use the consolidate feature to combine your data instead.
Here’s how to merge Excel files and data together using these methods.
How To Move Or Copy Single Worksheets In Excel
A typical Microsoft Excel file is broken up into different sheets (or worksheets) which are listed as tabs at the bottom of the Excel window. They act like pages, allowing you to spread data across multiple sheets in a single file.
You can move or copy worksheets between different Excel files (or the same file, if you wish to duplicate your data sets).
- To start, open your Excel file (or files). In the open window of the Excel file you wish to copy from, click on the worksheet you wish to select at the bottom of the Excel window. You can select multiple sheets by holding Shift and clicking on each sheet tab.
- To begin copying or moving sheets, press the Home tab in the ribbon bar at the top.
- From here, click Format > Move or Copy Sheet.
- In the Move or Copy box, select the Excel file you wish to copy or move to from the To Box drop-down menu. Select where you wish to place the sheets in the Before Sheet section. By default, Excel will move the sheets—click the Create a copy checkbox if you’d prefer to copy them instead.
- Press OK to move or copy the worksheets once you’re ready.
The worksheets you selected will then be moved or copied, as desired, although this won’t merge them together entirely.
How To Merge Multiple Excel Files Together
You can use the Move or Copy Sheet tool in Excel to merge data in multiple Excel files together. You can do this by selecting all of the active worksheets in a file, then merging them into your single target file, repeating this process for multiple files.
- To do this, open your Excel files. In the open window of an Excel file you wish to move or copy into another file, select all of the sheet tabs at the bottom of the window by holding the Shift key and clicking on each sheet tab.
- Next, press Home > Format > Move or Copy Sheet from the ribbon bar.
- In the open Move or Copy dialog box, select the target Excel file to merge to from the To Box drop-down menu. Select where you wish to place your merged sheets in the Before sheet section. If you want to leave the original file intact, press Create a copy to copy the sheets rather than move them, then press OK to begin the moving or copying process.
If you have more than one Excel file, you’ll need to repeat these steps to merge them together into a single file.
Using The Consolidate Tool To Merge Data In Excel Together
Using the methods above, you can move and copy sheets between different Excel spreadsheet files. This moves the data, but it doesn’t integrate it particularly well—the data is still kept in separate sheets.
To get around this problem, you can use the Consolidate tool in Excel to merge numerical data together from multiple worksheets into a new, single worksheet. Unfortunately, this process doesn’t work with cells using text—you’ll need to cut and paste this data manually, or create a VBA script in Excel to do it for you.
For this to work, your data will need to be presented in the same way across your sheets with matching header labels. You’ll also need to delete any blank data (for instance, empty cells) from your data before you begin.
- To merge data in Excel using this method, open your Excel files and, in the target Excel file for merging data, create a new worksheet by pressing the + (plus) button next to the sheet tabs at the bottom of the window.
- In your new worksheet, press Data > Consolidate.
- In the Consolidate window, make sure that Sum is selected in the Function drop-down menu. Click the button next to the Reference entry box to begin selecting your first data set—this is the data you wish to merge. You can also type the reference to the cell range in yourself, if you’d prefer.
- To select the data using your mouse, click on the sheet containing your worksheet once the Consolidate – Reference box is visible, select the data, then press the Insert button.
- The cell range will appear in the Reference entry box—click the Add button to add it to the All References list.
- Repeat the steps above to select additional sets of data, clicking Add to add each set to your sheet. If your data has column or row labels, make sure that these are processed correctly by pressing the Top row or Left column checkboxes in the Use labels section.
- If you want to continue to edit the data in the original, separate worksheets, click to enable the Create links to source data checkbox. This will ensure that any changes to your original data are reflected in your merged sheet later.
- Once you’re ready to merge your data into a single sheet, press the OK button.
- Your merged data will appear, with an options menu next to the row labels to be able to switch between different data views—click on these options (for instance, the 1 button) to hide or view the data you’ve merged in part or in full.
If you choose to create links to the source data, your new worksheet will act as a mirrored version of your separate sheets and files. Otherwise, your data will be copied into this sheet for you to edit directly.
Using Merged Excel Data
Whether you decide to merge data in Excel into a single sheet or a file, or if you prefer to spread your working across multiple files, these tips should help you to keep organized. When you’re ready, you can begin to share your Excel file with others to collaborate and analyze your data effectively as a team.
If you’re worried about losing track of changes to your merged data, don’t worry—you can track changes in Excel easily using its built-in tracking feature.
Let us know your Excel data analysis tips in the comments section below.
