如何在Excel中更改数据透视表数据源?

I want to change it from one database to another.

我想把它从一个数据库变成另一个数据库。

There don't appear to be any options to do this on the pivot table context menu

在pivot表上下文菜单上似乎没有任何选项可以这样做

10 个解决方案

#1

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.

只要找到它，就可以在表中的任何地方点击，然后到页面顶部的选项卡并选择options——从那里您将看到一个更改数据源选择。

#2

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版本。我使用的是2007版本，当您右键单击该表时，它不提供向导选项。您需要单击pivot表，以使额外的“数据透视表工具”出现在屏幕顶部的其他选项卡的右边。点击出现在这里的“选项”选项卡，然后在丝带中间有一个大图标，名为“更改数据源”。

#3

right click on the pivot table in excel choose wizard click 'back' click 'get data...' in the query window File - Table Definition

右键单击excel中的pivot表，选择向导，单击“返回”，单击“获取数据”……在查询窗口中文件表定义

then you can create a new or choose a different connection

然后您可以创建一个新的或选择一个不同的连接

#4

In order to change the data source from the ribbon in excel 2007...

为了从excel 2007中的ribbon中更改数据源……

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.

单击工作表中的pivot表。转到功能区，上面写着Pivot表工具和选项卡。选择Change数据源按钮。将出现一个对话框。

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.

得到正确的范围,避免错误消息……选择现有的内容字段,删除它,然后切换到新数据源表强调数据区域(所有的对话框将掌握windows)。一旦你选择了新数据源正确,它将填补空白的领域(你之前删除)在对话框中。单击OK。切换回你的数据透视表,它应该有新数据更新的新来源。

#5

• Right click on the pivot table, choose PivotTable Wizard.
• 右键单击pivot表，选择数据透视表向导。
• Click the 'back' button twice.
• 点击“后退”按钮两次。
• Choose External Data Source,click next.
• 选择外部数据源，单击next。
• Click Get Data
• 点击获取数据
• In the first tab, Databases the first option is 'New Data Source'
• 在第一个选项卡中，数据库第一个选项是“新数据源”

#6

This Add-on will do the trick for you.

这个附加组件将为您实现这个功能。

http://www.contextures.com/xlPivotPlayPlus01.html

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).

它显示连接字符串，并允许对其进行更改。如果需要，也不要忘记更改查询SQL(使用相同的工具)。

#7

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.

对于任何不涉及从头创建数据透视表的解决方案，要小心一点。您的pivot字段的选项名称可能与它们向数据库显示的值不同步。

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.

例如，在我正在处理的一个工作簿中，如果您试图选择“20-24”年龄组选项，Excel实际上会显示25-29岁的数据。当然，它不会告诉你它在做什么。

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.

请参阅下面的编程(VBA)方法来解决这个问题。我认为它相当完整/健壮，但我不太使用数据透视表，所以希望得到反馈。

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

#8

right click on the pivot table in excel choose wizard click 'back' click 'get data...' in the query window File - Table Definition

右键单击excel中的pivot表，选择向导，单击“返回”，单击“获取数据”……在查询窗口中文件表定义

then you can create a new or choose a different connection

然后您可以创建一个新的或选择一个不同的连接

worked perfectly.

完美的工作。

the get data button is next to the tiny button with a red arrow next to the range text input box.

get data按钮在小按钮旁边，红色箭头在range文本输入框旁边。

#9

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

对于MS excel 2000 office版本，单击pivot表将在ribon上面找到一个名为Pivottable tool的选项卡——单击该选项卡可以从data选项卡更改数据源

#10

In case of Excel 2007 You can change datasource in Options menu /Change Data Source

在excel2007中，您可以在Options菜单中更改数据源/更改数据源