Excel Data Analysis 简明教程

Advanced Data Analysis - Data Consolidation

您可能会遇到需要提供合并数据的情况。数据的来源可能来自一处或多处。另一个挑战可能是数据可能不时由其他人更新。

You might have come across different situations wherein you have to present consolidated data. The source of the data could be from one place, or several places. Another challenge could be that the data might be updated by other people from time to time.

您需要知道：在任何时候，如何设置一个合并来自您设置的数据源的汇总工作表。在 Excel 中，您可以使用 Data Tool – Consolidate 轻松完成此任务，仅需几个步骤。

You need to know how you can set up a summary worksheet that consolidates the data from the sources that you set up, whenever you want. In Excel, you can easily perform this task in a few steps with the Data Tool – Consolidate.

Preparing Data for Consolidation

在开始合并数据之前，请确保数据源之间的一致性。这意味着要按照如下方式排列数据 −

Before you begin consolidating the data, make sure that there is consistency across the data sources. This means that the data is arranged as follows −

  1. Each range of data is on a separate worksheet.

  2. Each range of data is in list format, with labels in the first row.

  3. Additionally, you can have labels for the categories, if applicable, in the first column.

  4. All the ranges of data have the same layout.

  5. All the ranges of data contain similar facts.

  6. There are no blank rows or columns within each range.

在数据源是外部的情况下，确保将预定义的布局用作 Excel 模板的形式。

In case the data sources are external, ensure usage of a predefined layout in the form of an Excel template.

假设您拥有来自各个地区（东、北、南和西）的各种商品的销售数据。您可能需要合并这些数据，并不时按产品明智地汇总销售情况。准备工作包括以下内容：

Suppose you have the sales data of various commodities from each of the regions – East, North, South, and West. You might need to consolidate this data and present a product wise summary of sales from time to time. Preparation includes the following −

  1. One worksheet per region – i.e. four worksheets with names East, North, South, and West. These could be in the same workbook or different workbooks.

  2. Each worksheet has same layout, representing the details of product, number of units, and amount.

  3. You need to consolidate the data product wise. Hence, ensure that the column with the label Product is the first column and it contains the Product labels.

preparing data for consolidation

Consolidating Data in the Same Workbook

如果您要合并的所有数据都在同一个工作簿中，请按以下步骤操作：

If you have all the data, that you have to consolidate, in the same workbook, proceed as follows −

Step 1 − 确保每个地区的数据位于单独的工作表上。

Step 1 − Ensure that data of each region is on a separate worksheet.

separate worksheet

Step 2 − 添加一个新工作表并将其命名为“Summary”。

Step 2 − Add a new worksheet and name it Summary.

Step 3 − 单击“Summary”工作表。

Step 3 − Click the Summary worksheet.

Step 4 − 单击要放置汇总结果的单元格。

Step 4 − Click the cell where you want to place the summary results.

Step 5 − 单击功能区上的 DATA 选项卡。

Step 5 − Click the DATA tab on the Ribbon.

Step 6 − 单击 Data Tools 组中的 Consolidate 按钮。

Step 6 − Click the Consolidate button in the Data Tools group.

Consolidate 对话框会出现。

The Consolidate dialog box appears.

consolidate dialog box

Step 7 − 从 Function 下面的下拉列表中选择 Sum 。

Step 7 − Select Sum from the dropdown list under Function.

Step 8 − 如下所示从每个工作表中选择数据。

Step 8 − Select the data from each worksheet as follows.

  1. Click the icon in the box under Reference.

  2. Select the worksheet – East.

  3. Select the data range.

  4. Again, click the icon in the box under Reference.

选定的范围显示在参照方框中 −

The selected range appears in the Reference box −

reference box

Step 9 − 点击方框右边的 Add 按钮。在 All References 下的方框中显示选定的数据范围。

Step 9 − Click the Add button to the right of the box. The selected data range appears in the box under All References.

all references

Step 10 − 对剩余数据工作表（北方、南方和西方）重复步骤 1-5。合并对话框如下所示。

Step 10 − Repeat Steps 1-5 for the rest of the data worksheets – North, South, and West. The Consolidate dialog box looks as follows.

data worksheets

您会看到数据范围按照 All references 下的方框中的字母顺序以工作表的形式显示。

You can see that the data ranges appear worksheet wise in alphabetical order, in the box under All references.

Step 11 − 选中 Use labels in 下的方框 Top row 和 Left column 。点击确定。

Step 11 − Check the boxes Top row and Left column under Use labels in. Click OK.

use labels in

对于区域（东、北、南和西）的您的数据以产品顺序进行总结。

Your data is summarized product wise for the regions – East, North, South and West.

regions

每当需要时，您都可以重复上述步骤来手动刷新您的总结结果。

You can repeat the steps given above to refresh your summary results manually, whenever you need them.

Consolidating Data Automatically

假设您希望在数据发生更改时自动更新您的摘要表。为此，您需要链接到源数据。

Suppose you want your summary sheet to be updated automatically, whenever there are changes in the data. To accomplish this, you need to have links to the source data.

Step 1 − 选中合并对话框中的方框 - Create links to source data ，然后点击确定。

Step 1 − Check the box - Create links to source data in the Consolidate dialog box and click OK.

consolidating data automatically

您的摘要结果以如下轮廓显示 −

Your summary results appear with an outline as follows −

outline

您会看到产品列右侧插入了一列新列。

You will observe that a new column is inserted to the right of the column named Product.

Step 2 − 单击包含名为香皂的产品值的行的轮廓上的“+”号，您可以看到新列包含按区域划分的每组产品值合并值。

Step 2 − Click the + sign on the outline in the row containing the Product value named Soap. You can see that the new column contains the consolidated value for each set of product values, region wise.

consolidated value

Consolidating Data from Different Workbooks

在之前的示例中，您需要的所有总结数据都位于同一个工作簿中。然而，对于每个区域，其数据可能被分别维护和按区域进行更新。这种情况下，您可以合并数据，如下所示 −

In the previous example, all the data that you need to summarize is in the same workbook. However, it is likely that the data is maintained separately for each region and is updated region wise. In such a case, you can consolidate the data as follows −

Step 1 − 打开包含相应数据的工作簿，例如，工作簿 - East-Sales、North-Sales、South-Sales和West-Sales。

Step 1 − Open the workbooks containing the data, say, workbooks – East-Sales, North-Sales, South-Sales and West-Sales.

Step 2 − 打开一个新工作簿。

Step 2 − Open a new workbook.

Step 3 − 在新的工作簿上，点击您希望摘要显示的单元格。

Step 3 − On a new worksheet, click a cell where you want the summary to appear.

Step 4 − 点击功能区上的数据标签。

Step 4 − Click the DATA tab on the Ribbon.

Step 5 − 在数据工具方框中点击“合并”。

Step 5 − Click Consolidate in the Data Tools box.

Consolidate 对话框出现。在合并对话框中 −

A Consolidate dialog box appears. In the Consolidate dialog box −

  1. Select Sum from the dropdown list in the box under Function.

  2. Click the icon in the box under Reference.

  3. Select the workbook – East-Sales.xlsx.

  4. Select the data range.

  5. Again, click the icon in the box under Reference.

  6. Click the Add button to the right.

合并对话框显示如下 −

The Consolidate dialog box looks as follows −

consolidate

  1. Click the icon to the right of the box under References.

  2. Select the workbook – North-Sales.xlsx.

  3. Select the data range.

  4. Again, click the icon to the right of the box under References.

  5. Click Add.

Step 6 − 重复步骤 1-6 以从工作簿 – South-Sales.xlsx 和 West-Sales.xlsx 添加数据范围。

Step 6 − Repeat the steps 1–6 to add the data ranges from the workbooks – South-Sales.xlsx and West-Sales.xlsx.

Step 7 − 在 Use labels in 中，勾选以下方框。

Step 7 − Under Use labels in, check the following boxes.

  1. Top row.

  2. Left column.

Step 8 − 勾选 Create links to source data 方框。

Step 8 − Check the box Create links to source data.

合并对话框显示如下 −

Your Consolidate dialog box looks as follows −

create links

您的数据总结在您的工作簿中。

Your data is summarized in your workbook.

consolidate data summarized