如何更改在数据透视表中选择的源数据范围以用于数据透视表

时间：2021-05-02 16:01:25

Currently I have my pivot table which looks like this

目前我有我的数据透视表,看起来像这样

如何更改在数据透视表中选择的源数据范围以用于数据透视表

At the moment, one of the graphs which I create adds a series to a xlPie chart however It is selecting the data in Range("C6:C12") where I would prefer it selects the Second last column, Range("F6":"F12") as we are wanting the total values, not just the values for one month.

目前,我创建的其中一个图表在xlPie图表中添加了一个系列,但它选择Range(“C6:C12”)中的数据,我希望它选择第二个最后一列,Range(“F6”: “F12”)因为我们想要总价值,而不仅仅是一个月的价值。

This is the pie chart currently created for reference:

这是目前创建的饼图供参考:

如何更改在数据透视表中选择的源数据范围以用于数据透视表

To create this chart, I use the following code within the sheet this chart resides:

要创建此图表,我在此图表所在的工作表中使用以下代码:

Dim shpPie As Shape
Set shpPie = ActiveSheet.Shapes.AddChart(xlPie)

shpPie.Chart.SetSourceData Source:=ActiveWorkbook.Sheets("Pivot Table").PivotTables(1).TableRange1, _
                      PlotBy:=xlColumns
With Range("A1:F32")
    shpPie.Left = .Left
    shpPie.Top = .Top
    shpPie.Width = .Width
    shpPie.Height = .Height
End With
With shpPie.Chart.PivotLayout.PivotTable
    .PivotFields("Marketspace").Orientation = xlRowField 'Row Field is the Axis Fields
    .PivotFields("Page Name").Orientation = xlRowField
    .PivotFields("Year").Orientation = xlColumnField
    .PivotFields("Month").Orientation = xlColumnField 'Column Field is the Legend Fields
End With

I assumed that the issue is with this line here:

我认为问题出在这一行:

 shpPie.Chart.SetSourceData Source:=ActiveWorkbook.Sheets("Pivot Table").PivotTables(1).TableRange1, _
                          PlotBy:=xlColumns

In excel, that line gives me a source data selection of:

在excel中,该行为我提供了以下源数据选择:

=SERIES('Pivot Table'!$C$3:$C$5,'Pivot Table'!$A$6:$B$14,'Pivot Table'!$C$6:$C$14,1)

Does anyone know if there is anything I can do to make it select the 2nd last column, or another technique to do this? I would like it to select the 2015 total's not the January data.

有谁知道我能做些什么才能让它选择第二列,或者另外一种技术来做到这一点?我希望它选择2015年总数不是1月数据。

I have tried changing the source line generated from:

我试过更改生成的源代码行:

=SERIES('Pivot Table'!$C$3:$C$5,'Pivot Table'!$A$6:$B$14,'Pivot Table'!$C$6:$C$14,1)

to:

=SERIES('Pivot Table'!$C$3:$C$5,'Pivot Table'!$A$6:$B$14,'Pivot Table'!$F$6:$F$14,1)

just to see what happens but it will not allow me to apply that change.

只是看看会发生什么,但它不允许我应用这种改变。

I'm new to VB so I may be misunderstanding how these pivot tables/charts work

我是VB的新手,所以我可能会误解这些数据透视表/图表是如何工作的

1 个解决方案

#1

no need to extract the data to create new chart, just copy the worksheet containing the pivottable (as suggested by Rory), exclude the field "Month" from the Values in the copied pivottable.

无需提取数据以创建新图表,只需复制包含pivottable的工作表(如Rory所建议),从复制的pivottable中的值中排除字段“Month”。

Exclude the following line from your code

从代码中排除以下行

.PivotFields("Month").Orientation = xlColumnField 'Column Field is the Legend Fields

Then run you procedure for the copied worksheet

然后运行复制的工作表的过程

#1

no need to extract the data to create new chart, just copy the worksheet containing the pivottable (as suggested by Rory), exclude the field "Month" from the Values in the copied pivottable.

无需提取数据以创建新图表,只需复制包含pivottable的工作表(如Rory所建议),从复制的pivottable中的值中排除字段“Month”。

Exclude the following line from your code

从代码中排除以下行

.PivotFields("Month").Orientation = xlColumnField 'Column Field is the Legend Fields

Then run you procedure for the copied worksheet

然后运行复制的工作表的过程

标签：

相关文章