数据透视表数据源引用无效_数据透视表源数据文章-CSDN博客

本文详细介绍了Excel数据透视表的源数据管理，包括如何设置和查找源数据，如何在不同源数据间切换，以及解决源数据问题的技巧。强调了源数据的组织和规范化对数据透视表灵活性的重要性，并提供了在源数据变动时保持数据透视表同步的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据透视表数据源引用无效

The key to success with Excel pivot tables is having good source data. I've written many articles with pivot table data source tips, and this list will help you find the information that you need. Some of the articles are on my Contextures website, and others are on my Pivot Table blog.

Excel数据透视表成功的关键是拥有良好的源数据。我写了许多有关数据透视表数据源技巧的文章，该列表将帮助您找到所需的信息。其中一些文章在我的Contextures网站上，其他文章在我的数据透视表博客上。

Pivot Table Data Source Tips http://blog.contextures.com/

设置源数据 (Set Up the Source Data)

No matter which version of Excel you're using, the source data needs to be in a well-organized list, that will grow or shrink as you add or remove data.

无论您使用的是哪个版本的Excel，源数据都必须位于组织良好的列表中，并且在添加或删除数据时，源数据会不断增长或收缩。

In Excel 2007 and later, that list can be set up as a named table, which will expand automatically, as new data is added.
在Excel 2007及更高版本中，该列表可以设置为命名表，随着添加新数据，该表将自动扩展。
For earlier versions, or if you don't want to use a named table, create a dynamic named range.
对于较早的版本，或者如果您不想使用命名表，请创建一个动态的命名范围。

Be sure to normalize the source data, so the pivot table is as flexible as possible.

确保对源数据进行规范化，以便数据透视表尽可能灵活。

If the source data is on multiple sheets, there are a few techniques that you can use to combine it into a single pivot table.

如果源数据在多张纸上，则可以使用几种技术将其组合到单个数据透视表中。

查找源数据 (Find the Source Data)

If you've inherited a pivot table, and aren't sure where the source data is located, use the tips on the following pages to find it.

如果您继承了数据透视表，并且不确定源数据位于何处，请使用以下页面上的提示进行查找。

Or, if you received a workbook with a pivot table, but no source data, you might be able to re-create the source data.

或者，如果您收到的工作簿带有数据透视表，但是没有源数据，则可以重新创建源数据 。

切换到不同的源数据 (Switch to Different Source Data)

After you create a pivot table, you can change it, so it uses a different data source.

创建数据透视表后，您可以对其进行更改，因此它使用其他数据源。

It's a little trickier, but you can change a pivot chart's data source too, so it is based on a different pivot table.

这有点棘手，但是您也可以更改数据透视图的数据源，因此它基于不同的数据透视表。

源数据问题 (Source Data Problems)

If you add, remove or edit the source data, the pivot table should show the revised data, the next time it is refreshed. However, some changes might cause problems. For example, if you change field names in the source data, the pivot table won't automatically detect those changes.

如果添加，删除或编辑源数据，则数据透视表应在下次刷新时显示修改后的数据。但是，某些更改可能会导致问题。例如，如果您更改源数据中的字段名称，则数据透视表将不会自动检测到这些更改。

Another problem that can occur is protecting the source data, so people can only view their data, as filtered in the pivot table. You'll have to take special steps, to protect it.

可能发生的另一个问题是保护源数据，因此人们只能查看数据，如数据透视表中所过滤。您必须采取特殊步骤来保护它。

When you're saving a workbook that contains a pivot table, you might not be sure if it's best to save the source data with the file. This article explains the pros and cons of each option.

保存包含数据透视表的工作簿时，可能不确定是否最好将源数据与文件一起保存。本文介绍了每种选择的优缺点。