Excel 2010:通过外部连接替换表格，而不会影响其他工作表 [英] Excel 2010: Replace a table by an external connection without impacting other sheets

问题描述

我有一本很大的工作簿，里面有很多数据.在一张纸上，我有一张桌子.该表用于其他工作表中使用的许多数据透视表，这些数据透视表也用于其他工作表中.

I have a big workbook with a lot of data. In one sheet I have a table. This table is used for many Pivot tables used in other sheets, which are also used in other sheets.

该表的数据是定期手动复制的.我现在有一个外部连接，可以自动导入数据，但我想避免影响其他工作表.

The data for the table was copied manually regularly. I now have a external connection to import automatically the data but I'd like to avoid impacting other sheets.

如何在不丢失桌子的情况下使用外部连接?

How can I use my external connection without losing my table?

谢谢您的帮助

推荐答案

这可能是一个有用的解决方法.我已经成功完成了此操作，但可能需要重新做一些工作，而不是您期望的确切答案. (我正在使用Excel 2010).

This might be a helpful workaround. I have successfully done this but it may require a bit of rework and not be the exact answer you expect. (I am using Excel 2010).

解决方法的原因是，我认为这是将常规表(而不是数据透视表)用作源表.不幸的是，我认为切换连接选项不适用于Excel 2010中的常规表.

The reason for a workaround is I assume this is for a regular table (not a pivot table) as the source table. Unfortunately, I don't believe the switching connection options work for regular tables in Excel 2010.

1. 在同一工作表中的另一个表的下面重新创建一个新表，方法是转到数据"选项卡>获取外部数据">现有连接"

1. Recreate a new table just below the other in the same worksheet by going to the Data tab > Get External Data > Existing Connections

选择数据连接(假设已创建)，然后选择查看数据的方式.即:表格

Select your data connection (assuming it has been created) then select how you want to view the data. ie: Table

放置在现有工作表中.使用旧表作为参考，将所有连接重新连接到其他工作表，然后删除旧表.

Place in your existing worksheet. Use your old table as a reference to reconnect all the connections to your other sheets, then delete the old table.

这时，我发现使用外部数据重建/复制到新表的现有链接更加容易.

At this point, I find it easier to rebuild/copy those existing links to the new table with external data.

好消息:如果源表是具有现有连接的数据透视表或数据透视表，则可以轻松地将表更改为具有外部数据连接(并在数据连接之间切换).

The good news: If your source table is a pivot table or pivot chart with an existing connection, you can easily change the table to have an external data connection (and switch between data connections).

1. 对于要更改现有连接的数据透视表，请在表中选择一个单元格.在上方数据透视表工具">选项"选项卡>更改数据源"中的功能区中，您可以选择它并在选择连接""下重新连接到另一个源.

1. For a pivot table with an existing connection you wish to change, select a cell in the table. In the ribbon above in 'PivotTable Tools' > Options tab > Change Data Source, you will be able to select this and reconnect to another source under 'Choose connection'.

对于带有静态数据的数据透视表以引入新连接，我建议上面采用类似的解决方法.

For a pivot table with static data to introduce a new connection, I would suggest a similar workaround above.

我希望这对您有用.

这篇关于Excel 2010:通过外部连接替换表格，而不会影响其他工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！