blocks|key|267485|text|下面的VBA代码将更改单个工作表上所有透视表的数据源。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|267486|您需要使用新的透视表将Sheet2参数更新为工作表的名称，并将Data2参数更新为新的命名范围。|offset|length|style|CODE|267487|Sub+Change_Pivot_Source()

++++Dim+pt+As+PivotTable

++++For+Each+pt+In+ActiveWorkbook.Worksheets("Sheet2").PivotTables
+++++++++++++pt.ChangePivotCache+ActiveWorkbook.PivotCaches.Create+_
++++++++++++++++(SourceType:=xlDatabase,+SourceData:="Data2")
++++Next+pt

End+Sub|code-block|syntax|javascript|267488|entityMap^0|0|B|6|V|5|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

The following VBA code will change the data source of all pivot tables on a single worksheet.

You will need to update the <code>Sheet2</code> parameter to the name of the sheet with your new pivot tables and the <code>Data2</code> parameter to your new named range.

<pre class="lang-vb prettyprint-override"><code>Sub Change_Pivot_Source()

 Dim pt As PivotTable

 For Each pt In ActiveWorkbook.Worksheets("Sheet2").PivotTables
 pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
 (SourceType:=xlDatabase, SourceData:="Data2")
 Next pt

End Sub
</code></pre>

blocks|key|1496430|text|假设您愿意使用VBA，那么this可能是相关的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1496431|如果您遍历了每个工作表上的PivotTable集合，您应该能够使用该文章中所示的方法来修改数据源。语法应该非常类似于使用命名区域而不是单元格区域。|1496432|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mrexcel.com/forum/showpost.php?p=509341&postcount=2^0|D|4|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Assuming you're willing to use VBA, <a href="http://www.mrexcel.com/forum/showpost.php?p=509341&amp;postcount=2" rel="nofollow noreferrer">this</a> might be relevant.

If you iterate through the PivotTable collection on each sheet, you should be able to use the method shown in that post to amend the data source. The syntax should be very similar to use a named range rather than a range of cells.

blocks|key|2905617|text|我将上面的代码组合在一起，现在使用下面的代码，您可以对源数据使用表或范围引用。您所要做的就是通过替换*符号将源数据放入其中，您就可以开始工作了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2905618|Sub+Change_Pivot_Source()

Dim+pt+As+PivotTable
Dim+ws+As+Worksheet

For+Each+ws+In+ActiveWorkbook.Worksheets

For+Each+pt+In+ws.PivotTables
+++++++++pt.ChangePivotCache+ActiveWorkbook.PivotCaches.Create+_
++++++++++++(SourceType:=xlDatabase,+SourceData:="*****")
Next+pt
Next+ws

End+Sub|code-block|syntax|javascript|2905619|entityMap^0|1E|1|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

I combined both the above codes and now with the below code you can use either table or range reference for your source data. All you have to do is put in your source data by replacing the <code>*</code> sign and you're good to go.

<pre><code>Sub Change_Pivot_Source()

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables
 pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
 (SourceType:=xlDatabase, SourceData:="*****")
Next pt
Next ws

End Sub
</code></pre>

blocks|key|1496513|text|改编自Dynamically+Change+A+Pivot+Table's+Data+Source+Range+With+This+VBA+Macro+Code|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1496514|1496515|+PivotTable.SourceData属性可以通过ChangePivotCache方法设置。|ordered-list-item|style|CODE|1496516|要创建新代码，调用ActiveWorkbook.PivotCaches.Create|1496517|You'll需要传入<+PivotCache+>d17和Range作为SourceData。|1496518|最后，更新后，请确保调用RefreshTable来应用更改。|1496519|1496520|这是一个自动查找工作簿中的每个透视表并更新它的示例。|1496521|Sub+AdjustPivotDataRange()
++++Dim+pt+As+PivotTable,+pc+As+PivotCache
++++Dim+dataSheet+As+Worksheet,+ws+As+Worksheet
++++Dim+startPoint+As+Range,+dataSource+As+Range,+newRange+As+String

++++'+get+worksheet+with+data
++++Set+dataSheet+=+ThisWorkbook.Worksheets("Sheet1")

++++'+Dynamically+Retrieve+Range+Address+of+Data
++++Set+startPoint+=+dataSheet.Range("A1")
++++Set+dataSource+=+dataSheet.Range(startPoint,+startPoint.SpecialCells(xlLastCell))
++++newRange+=+dataSheet.Name+&+"!"+&+dataSource.Address(ReferenceStyle:=xlR1C1)

++++'+create+new+PivotCache
++++Set+pc+=+ActiveWorkbook.PivotCaches.Create(+_
+++++++++++++++SourceType:=xlDatabase,+_
+++++++++++++++SourceData:=newRange)

++++'+loop+through+all+tables+in+all+sheets
++++For+Each+ws+In+ActiveWorkbook.Worksheets
++++++++For+Each+pt+In+ws.PivotTables

++++++++++++'+update+pivot+source+and+refresh
++++++++++++pt.ChangePivotCache+pc
++++++++++++pt.RefreshTable

++++++++Next+pt
++++Next+ws

End+Sub|code-block|syntax|javascript|1496522|只需将"Sheet1"替换为您的数据源所在的位置。|1496523|entityMap|0|LINK|mutability|MUTABLE|url|http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range|1|https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable|2|https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable.SourceData|3|https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable.ChangePivotCache|4|https://docs.microsoft.com/en-us/office/vba/api/Excel.Application.ActiveWorkbook|5|https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotCaches|6|https://docs.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create|7|https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotCache|8|https://docs.microsoft.com/en-us/office/vba/api/Excel.Range(object)|9|https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable.RefreshTable^0|3|25|0|0|0|1|L|S|G|1|A|1|C|A|2|S|G|3|0|9|X|9|E|4|O|B|5|10|6|6|0|C|A|S|5|Z|A|C|A|7|S|5|8|0|C|C|C|C|9|0|0|0|0|3|8|0^^$0|@$1|2|3|4|5|6|7|1P|8|@]|9|@$A|1Q|B|1R|1|1S]]|C|$]]|$1|D|3|-4|5|6|7|1T|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|1U|8|@$A|1V|B|1W|H|I]|$A|1X|B|1Y|H|I]]|9|@$A|1Z|B|20|1|21]|$A|22|B|23|1|24]|$A|25|B|26|1|27]]|C|$]]|$1|J|3|K|5|G|7|28|8|@$A|29|B|2A|H|I]]|9|@$A|2B|B|2C|1|2D]|$A|2E|B|2F|1|2G]|$A|2H|B|2I|1|2J]]|C|$]]|$1|L|3|M|5|G|7|2K|8|@$A|2L|B|2M|H|I]|$A|2N|B|2O|H|I]|$A|2P|B|2Q|H|I]]|9|@$A|2R|B|2S|1|2T]|$A|2U|B|2V|1|2W]]|C|$]]|$1|N|3|O|5|G|7|2X|8|@$A|2Y|B|2Z|H|I]]|9|@$A|30|B|31|1|32]]|C|$]]|$1|P|3|-4|5|6|7|33|8|@]|9|@]|C|$]]|$1|Q|3|R|5|6|7|34|8|@]|9|@]|C|$]]|$1|S|3|T|5|U|7|35|8|@]|9|@]|C|$V|W]]|$1|X|3|Y|5|6|7|36|8|@$A|37|B|38|H|I]]|9|@]|C|$]]|$1|Z|3|-4|5|6|7|39|8|@]|9|@]|C|$]]]|10|$11|$5|12|13|14|C|$15|16]]|17|$5|12|13|14|C|$15|18]]|19|$5|12|13|14|C|$15|1A]]|1B|$5|12|13|14|C|$15|1C]]|1D|$5|12|13|14|C|$15|1E]]|1F|$5|12|13|14|C|$15|1G]]|1H|$5|12|13|14|C|$15|1I]]|1J|$5|12|13|14|C|$15|1K]]|1L|$5|12|13|14|C|$15|1M]]|1N|$5|12|13|14|C|$15|1O]]]]

Adapted from <a href="http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range" rel="nofollow noreferrer">Dynamically Change A Pivot Table's Data Source Range With This VBA Macro Code</a>:

<ol>
<li>The <code><a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable" rel="nofollow noreferrer">PivotTable</a>.<a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable.SourceData" rel="nofollow noreferrer">SourceData</a></code> property can be set which is set via the <a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable.ChangePivotCache" rel="nofollow noreferrer"><code>ChangePivotCache</code></a> method.</li>
<li>To create a new <a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotCache" rel="nofollow noreferrer"><code>PivotCache</code></a>, call <code><a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.Application.ActiveWorkbook" rel="nofollow noreferrer">ActiveWorkbook</a>.<a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotCaches" rel="nofollow noreferrer">PivotCaches</a>.<a href="https://docs.microsoft.com/en-us/office/vba/api/excel.pivotcaches.create" rel="nofollow noreferrer">Create</a></code></li>
<li>You'll need to pass in a <code>SourceType</code> and a <a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.Range(object)" rel="nofollow noreferrer"><code>Range</code></a> as <code>SourceData</code>. </li>
<li>Finally, once updated, make sure to call <a href="https://docs.microsoft.com/en-us/office/vba/api/Excel.PivotTable.RefreshTable" rel="nofollow noreferrer"><code>RefreshTable</code></a> to apply the changes.</li>
</ol>

Here's an example that will automatically find every pivot table in your workbook and update it.

<pre class="lang-vb prettyprint-override"><code>Sub AdjustPivotDataRange()
 Dim pt As PivotTable, pc As PivotCache
 Dim dataSheet As Worksheet, ws As Worksheet
 Dim startPoint As Range, dataSource As Range, newRange As String

 ' get worksheet with data
 Set dataSheet = ThisWorkbook.Worksheets("Sheet1")

 ' Dynamically Retrieve Range Address of Data
 Set startPoint = dataSheet.Range("A1")
 Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell))
 newRange = dataSheet.Name &amp; "!" &amp; dataSource.Address(ReferenceStyle:=xlR1C1)

 ' create new PivotCache
 Set pc = ActiveWorkbook.PivotCaches.Create( _
 SourceType:=xlDatabase, _
 SourceData:=newRange)

 ' loop through all tables in all sheets
 For Each ws In ActiveWorkbook.Worksheets
 For Each pt In ws.PivotTables

 ' update pivot source and refresh
 pt.ChangePivotCache pc
 pt.RefreshTable

 Next pt
 Next ws

End Sub
</code></pre>

Just replace <code>"Sheet1"</code> with wherever your datasource is located.

Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time?

All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range.

Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand.

Any suggestions?

Updating data source on multiple pivot tables within Excel

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

有没有一种简单的方法可以同时更新单个Excel工作表上的多个透视表的数据源？所有的透视表都引用相同的命名范围，但我需要创建第二个工作表，该工作表具有相同的透视表，但访问不同的命名范围。理想情况下，我希望能够执行某种搜索和替换操作(就像您可以对公式执行的操作一样)，而不是手动更新每个单独的透视表。有什么建议吗？

问在Excel中更新多个数据透视表上的数据源
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Excel中更新多个数据透视表上的数据源EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Excel中更新多个数据透视表上的数据源
EN