Just figured it out-click anywhere in the table, then go to the tabs at the top of the page and select Options-from there you'll see a Change Data Source selection.
Looks like this depends heavily on your version of Excel. I am using the 2007 version and it offers no wizard option when you right click on the table. You need to click on the pivot table to make extra 'PivotTable Tools' appear to the right of the other tabs at the top of the screen. Click the 'options' tab that appears here then there is a big icon on the middle of the ribbon named 'change data source'.
In order to change the data source from the ribbon in excel 2007...
Click on your pivot table in the worksheet. Go to the ribbon where it says Pivot Table Tools, Options Tab. Select the Change Data Source button. A dialog box will appear.
To get the right range and avoid an error message... select the contents of the existing field and delete it, then switch to the new data source worksheet and highlight the data area (the dialog box will stay on top of all windows). Once you've selected the new data source correctly, it will fill in the blank field (which you deleted before) in the dialog box. Click OK. Switch back to your pivot table and it should have updated with the new data from the new source.
Sub SwapSources()
strOldSource = "2010 Data"
strNewSource = "2009 Data"
Dim tmpArrOut
For Each wsh In ThisWorkbook.Worksheets
For Each pvt In wsh.PivotTables
tmpArrIn = pvt.SourceData
' row 1 of SourceData is the connection string.
' rows 2+ are the SQL code broken down into 255-byte chunks.
' we need to concatenate rows 2+, replace, and then split them up again
strSource1 = tmpArrIn(LBound(tmpArrIn))
strSource2 = ""
For ii = LBound(tmpArrIn) + 1 To UBound(tmpArrIn)
strSource2 = strSource2 & tmpArrIn(ii)
Next ii
strSource1 = Replace(strSource1, strOldSource, strNewSource)
strSource2 = Replace(strSource2, strOldSource, strNewSource)
ReDim tmpArrOut(1 To Int(Len(strSource2) / 255) + 2)
tmpArrOut(LBound(tmpArrOut)) = strSource1
For ii = LBound(tmpArrOut) + 1 To UBound(tmpArrOut)
tmpArrOut(ii) = Mid(strSource2, 255 * (ii - 2) + 1, 255)
Next ii
' if the replacement SQL is invalid, the PivotTable object will throw an error
Err.Clear
On Error Resume Next
pvt.SourceData = tmpArrOut
On Error GoTo 0
If Err.Number <> 0 Then
MsgBox "Problems changing SQL for table " & wsh.Name & "!" & pvt.Name
pvt.SourceData = tmpArrIn ' revert
ElseIf pvt.RefreshTable <> True Then
MsgBox "Problems refreshing table " & wsh.Name & "!" & pvt.Name
Else
' table is now refreshed
' need to ensure that the "display name" for each pivot option matches
' the actual value that will be fed to the database. It is possible for
' these to get out of sync.
For Each pvf In pvt.PivotFields
'pvf.Name = pvf.SourceName
If Not IsError(pvf.SourceName) Then ' a broken field may have no SourceName
mismatches = 0
For Each pvi In pvf.PivotItems
If pvi.Name <> pvi.SourceName Then
mismatches = mismatches + 1
pvi.Name = "_mismatch" & CStr(mismatches)
End If
Next pvi
If mismatches > 0 Then
For Each pvi In pvf.PivotItems
If pvi.Name <> pvi.SourceName Then
pvi.Name = pvi.SourceName
End If
Next
End If
End If
Next pvf
End If
Next pvt
Next wsh
End Sub
Be a bit wary of any solution that doesn't involve re-creating the PivotTable from scratch. It is possible for your pivot fields' option names to get out of sync with the values they present to the database.
For example, in one workbook I'm dealing with involving demographic data, if you try to select the "20-24" age band option, Excel actually presents you with the figures for ages 25-29. It doesn't tell you it's doing this, of course.
See below for a programmatic (VBA) approach to the problem that solves this issue among others. I think it's fairly complete/robust, but I don't use PivotTables much so would appreciate feedback.
Sub SwapSources()
strOldSource = "2010 Data"
strNewSource = "2009 Data"
Dim tmpArrOut
For Each wsh In ThisWorkbook.Worksheets
For Each pvt In wsh.PivotTables
tmpArrIn = pvt.SourceData
' row 1 of SourceData is the connection string.
' rows 2+ are the SQL code broken down into 255-byte chunks.
' we need to concatenate rows 2+, replace, and then split them up again
strSource1 = tmpArrIn(LBound(tmpArrIn))
strSource2 = ""
For ii = LBound(tmpArrIn) + 1 To UBound(tmpArrIn)
strSource2 = strSource2 & tmpArrIn(ii)
Next ii
strSource1 = Replace(strSource1, strOldSource, strNewSource)
strSource2 = Replace(strSource2, strOldSource, strNewSource)
ReDim tmpArrOut(1 To Int(Len(strSource2) / 255) + 2)
tmpArrOut(LBound(tmpArrOut)) = strSource1
For ii = LBound(tmpArrOut) + 1 To UBound(tmpArrOut)
tmpArrOut(ii) = Mid(strSource2, 255 * (ii - 2) + 1, 255)
Next ii
' if the replacement SQL is invalid, the PivotTable object will throw an error
Err.Clear
On Error Resume Next
pvt.SourceData = tmpArrOut
On Error GoTo 0
If Err.Number <> 0 Then
MsgBox "Problems changing SQL for table " & wsh.Name & "!" & pvt.Name
pvt.SourceData = tmpArrIn ' revert
ElseIf pvt.RefreshTable <> True Then
MsgBox "Problems refreshing table " & wsh.Name & "!" & pvt.Name
Else
' table is now refreshed
' need to ensure that the "display name" for each pivot option matches
' the actual value that will be fed to the database. It is possible for
' these to get out of sync.
For Each pvf In pvt.PivotFields
'pvf.Name = pvf.SourceName
If Not IsError(pvf.SourceName) Then ' a broken field may have no SourceName
mismatches = 0
For Each pvi In pvf.PivotItems
If pvi.Name <> pvi.SourceName Then
mismatches = mismatches + 1
pvi.Name = "_mismatch" & CStr(mismatches)
End If
Next pvi
If mismatches > 0 Then
For Each pvi In pvf.PivotItems
If pvi.Name <> pvi.SourceName Then
pvi.Name = pvi.SourceName
End If
Next
End If
End If
Next pvf
End If
Next pvt
Next wsh
End Sub
for MS excel 2000 office version, click on the pivot table you will find a tab above the ribon, called Pivottable tool - click on that You can change data source from Data tab
发布评论
评论（10）
刚刚弄清楚 - 单击表中的任意位置，然后转到页面顶部的选项卡并选择“选项” - 从那里您将看到“更改数据源”选项。
Just figured it out-click anywhere in the table, then go to the tabs at the top of the page and select Options-from there you'll see a Change Data Source selection.
看起来这在很大程度上取决于您的 Excel 版本。 我使用的是 2007 版本，当您右键单击表格时，它不提供向导选项。 您需要单击数据透视表，以使额外的“数据透视表工具”出现在屏幕顶部其他选项卡的右侧。 单击此处显示的“选项”选项卡，然后功能区中间有一个名为“更改数据源”的大图标。
Looks like this depends heavily on your version of Excel. I am using the 2007 version and it offers no wizard option when you right click on the table. You need to click on the pivot table to make extra 'PivotTable Tools' appear to the right of the other tabs at the top of the screen. Click the 'options' tab that appears here then there is a big icon on the middle of the ribbon named 'change data source'.
右键单击 Excel 中的数据透视表
选择向导
点击“返回”
单击“获取数据...”
在查询窗口中
文件 - 表定义
然后您可以创建新的或选择不同的连接
right click on the pivot table in excel
choose wizard
click 'back'
click 'get data...'
in the query window
File - Table Definition
then you can create a new or choose a different connection
为了从 Excel 2007 中的功能区更改数据源...
单击工作表中的数据透视表。 转到功能区，其中显示“数据透视表工具”、“选项选项卡”。 选择更改数据源按钮。 将出现一个对话框。
为了获得正确的范围并避免出现错误消息...选择现有字段的内容并将其删除，然后切换到新的数据源工作表并突出显示数据区域（该对话框将保留在所有窗口的顶部）。 正确选择新数据源后，它将填充对话框中的空白字段（之前已删除）。 单击“确定”。 切换回数据透视表，它应该已使用新源中的新数据进行更新。
In order to change the data source from the ribbon in excel 2007...
Click on your pivot table in the worksheet. Go to the ribbon where it says Pivot Table Tools, Options Tab. Select the Change Data Source button. A dialog box will appear.
To get the right range and avoid an error message... select the contents of the existing field and delete it, then switch to the new data source worksheet and highlight the data area (the dialog box will stay on top of all windows). Once you've selected the new data source correctly, it will fill in the blank field (which you deleted before) in the dialog box. Click OK. Switch back to your pivot table and it should have updated with the new data from the new source.
这个附加组件将为您解决问题。
http://www.contextures.com/xlPivotPlayPlus01.html
它显示连接字符串，并且允许它被改变。
如果需要的话，不要忘记更改查询 SQL（使用相同的工具）。
This Add-on will do the trick for you.
http://www.contextures.com/xlPivotPlayPlus01.html
It shows the connection string, and allows it to be changed.
Don't forget to change the Query SQL as well, if needed (with the same tool).
对于任何不涉及从头开始重新创建数据透视表的解决方案，请保持警惕。 您的数据透视字段的选项名称可能与它们呈现给数据库的值不同步。
例如，在我正在处理的一本涉及人口统计数据的工作簿中，如果您尝试选择“20-24”年龄范围选项，Excel 实际上会向您显示 25-29 岁的数字。 当然，它不会告诉你它正在这样做。
请参阅下文，了解解决该问题的编程 (VBA) 方法以及其他问题。 我认为它相当完整/强大，但我不太使用数据透视表，所以希望得到反馈。
Be a bit wary of any solution that doesn't involve re-creating the PivotTable from scratch. It is possible for your pivot fields' option names to get out of sync with the values they present to the database.
For example, in one workbook I'm dealing with involving demographic data, if you try to select the "20-24" age band option, Excel actually presents you with the figures for ages 25-29. It doesn't tell you it's doing this, of course.
See below for a programmatic (VBA) approach to the problem that solves this issue among others. I think it's fairly complete/robust, but I don't use PivotTables much so would appreciate feedback.
工作得很好。
获取数据按钮位于范围文本输入框旁边带有红色箭头的小按钮旁边。
worked perfectly.
the get data button is next to the tiny button with a red arrow next to the range text input box.
对于 MS Excel 2000 Office 版本，
单击数据透视表
您会在功能区上方找到一个选项卡，称为“数据透视工具” - 单击该选项卡
您可以从“数据”选项卡更改数据源
for MS excel 2000 office version,
click on the pivot table
you will find a tab above the ribon, called Pivottable tool - click on that
You can change data source from Data tab
如果是 Excel 2007，您可以在“选项”菜单/“更改数据源”中更改数据源
In case of Excel 2007 You can change datasource in Options menu /Change Data Source