powerpivot_来自相同Excel文件的PowerPivot-CSDN博客

powerpivot

You can use the PowerPivot add-in for Excel 2010 to create a report from multiple Excel workbooks or worksheets, by joining the tables using the Primary and the Foreign key, such as 'ProductID' in a Sales table and a Pricing table.

您可以使用Excel 2010的PowerPivot加载项，通过使用主键和外键(例如Sales表和Pricing表中的“ ProductID”)联接表，从而从多个Excel工作簿或工作表创建报表。

In this example though, we want to combine the data in two Excel files that have an identical structure -- sales data for the East and West regions. In this case, we can't use a key to connect the tables; instead, we want to create one combined table from all the data.

但是，在此示例中，我们希望将数据合并到两个具有相同结构的Excel文件中-东部和西部地区的销售数据。在这种情况下，我们不能使用键来连接表。相反，我们希望根据所有数据创建一个组合表。

The following technique allows you to import more than a million records from Excel, despite the fact that one worksheet can only contain up to 1,048,576 rows.

尽管一个工作表最多只能包含1,048,576行，但以下技术使您可以从Excel导入超过一百万条记录。

At least that's possible in theory -- on my computer it imported about 1.2 million, then gave up, after whining about memory resources.

至少从理论上讲这是可能的-在我的计算机上，它在抱怨内存资源后导入了约120万个，然后放弃了。

Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip with us. You can see more of Kirill's work in last week's posts on Combining Data from Two Excel Files in a Pivot Table.

感谢Excel MVP，Kirill Lapin与我们分享了这个非常有用的技巧。您可以在上周有关在数据透视表中合并两个Excel文件中的数据的文章中看到Kirill的更多工作。

在工作簿中创建连接 (Create a Connection in the Workbook)

The key to this technique is to start by creating a workbook connection, before you launch PowerPivot.

该技术的关键是在启动PowerPivot之前先创建工作簿连接。

On the Excel Ribbon's Data tab, click Connections.
在Excel功能区的“数据”选项卡上，单击“连接”。
In the Workbook Connections window, click Add
在“工作簿连接”窗口中，单击“添加”。
At the bottom of the Existing Connections window, click Browse for More.
在“现有连接”窗口的底部，单击“浏览更多”。
Navigate to the folder where your files are located.
导航到文件所在的文件夹。
Select one of the files that you want to import -- EastSales.xlsx in this example -- and click Open.
选择要导入的文件之一(在此示例中为EastSales.xlsx)，然后单击“打开”。
Select a table to import, and click OK.
选择要导入的表，然后单击“确定”。
The new connection appears in the Workbook Connections window.
新的连接出现在“工作簿连接”窗口中。

在PowerPivot中合并数据 (Combine the Data in PowerPivot)

Close the Workbook Connections window, and on the Ribbon, click the PowerPivot tab.
关闭“工作簿连接”窗口，然后在功能区上，单击“ PowerPivot”选项卡。
Click PowerPivot Window, to launch the PowerPivot add-in.
单击“ PowerPivot窗口”以启动PowerPivot加载项。

Note: If you're using Windows XP, the PowerPivot window has a menu bar. If you're using Vista or Windows 7, you'll see a Ribbon instead.

注意：如果您使用的是Windows XP，则PowerPivot窗口具有一个菜单栏。如果您使用的是Vista或Windows 7，则会看到一个功能区。

On the Table menu, click Existing Connections, or, on the Ribbon, click Design, then Existing Connections.
在“表”菜单上，单击“现有连接”，或者在功能区上，单击“设计”，然后单击“现有连接”。
At the bottom of the Existing Connections window, under Workbook Connections, click on the connection that you added, and click Open.
在“现有连接”窗口的底部，在“工作簿连接”下，单击添加的连接，然后单击“打开”。
In the Table Import Wizard, click Next, then select the table, and click Finish
在表导入向导中，单击“下一步”，然后选择表，然后单击“完成”。
After the data is successfully imported, click Close.
成功导入数据后，单击“关闭”。

更改SQL语句 (Change the SQL Statement)

Now that the first table has been imported, you can change its properties, to combine it with data from the second table.

现在已经导入了第一个表，您可以更改其属性，以将其与第二个表中的数据合并。

On the Table menu, click Table Properties, or on the Ribbon, click the Design tab, then click Table Properties.
在表格菜单上，单击表格属性，或在功能区上，单击设计选项卡，然后单击表格属性。
At the right, from the Switch To drop down list, select Query Editor.
在右侧的“切换到”下拉列表中，选择“查询编辑器”。
Edit the SQL statement, to create a union query, combining the two tables. In this example, the SQL statement is:
编辑SQL语句，以创建合并查询，将两个表组合在一起。在此示例中，SQL语句为：

SELECT [EastSales$].* FROM [EastSales$] UNION ALL SELECT * FROM 'C:\_TESTWestSales.xlsx'.[WestSales$]

选择[EastSales $]。*从[EastSales $] UNION ALL中选择*从'C：\ _ TESTWestSales.xlsx'。[WestSales $]

After you change the SQL statement, click the Validate button, to verify that the statement is correct, then click Save.

更改SQL语句后，单击“验证”按钮，以验证该语句正确，然后单击“保存”。

Note: The SQL query string can also be edited in the Excel workbook connection window, by selecting the connection, and clicking Properties. However, there's no Validate feature there.

注意：也可以在Excel工作簿连接窗口中通过选择连接并单击“属性”来编辑SQL查询字符串。但是，那里没有验证功能。

创建数据透视表 (Create the Pivot Table)

Next, you can create a pivot table from the combined data.

接下来，您可以根据组合数据创建数据透视表 。

On the Toolbar, click the Create a PivotTable button, or on the Ribbon, click the Home tab, then click PivotTable.
在工具栏上，单击“创建数据透视表”按钮，或者在功能区上，单击“主页”选项卡，然后单击“数据透视表”。
Select a location for the pivot table, and click OK.
选择数据透视表的位置，然后单击“确定”。
Add fields to the pivot table layout, to see a summary of the data.
将字段添加到数据透视表布局中，以查看数据摘要。

Here's the pivot table that was created from the combined data, with columns for the East and West regions. The Report Layout is Tabular, and Number format is used, with thousands separator and zero decimals.

这是根据组合数据创建的数据透视表，其中包含东部和西部地区的列。报告布局为表格格式，并使用数字格式，带有千位分隔符和零位小数。

详细说明和示例文件 (Detailed Instruction and Sample Files)

To see detailed instructions for this technique, with more screen shots, visit the PowerPivot from Identical Structure Excel Files page on the Contextures website.

若要查看有关此技术的详细说明以及更多屏幕截图，请访问Contextures网站上的“ 来自相同结构的Excel文件的PowerPivot”页。

That page also has a link for downloading the East and West sales data that I used in this example.

该页面上还有一个链接，用于下载在此示例中使用的东西方销售数据。