vba 数据透视表：显示（多个项目）的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23888987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot Table: Showing value of (Multiple Items)
提问by usernolongerregistered
I am attempting to create a pivot table and pivot chart with a title and legend that will change based on the chosen selection. For example, if I have Apple and Banana as choices, if I choose Apple the title and legend should read Apple. This part was relatively easy, however, if I choose both Apple and Banana, the pivot table shows (Multiple Items) instead.
我正在尝试创建一个数据透视表和数据透视图，其标题和图例将根据所选的选择而改变。例如，如果我选择 Apple 和 Banana，如果我选择 Apple，则标题和图例应为 Apple。这部分相对容易，但是，如果我同时选择 Apple 和 Banana，数据透视表会显示 (Multiple Items)。
I would like this to instead show something like "Apple, Banana", but I haven't the slightest clue of how I can do this. I have looked around online and the few people I have found that share my issue end up not getting a response (i.e. Excel 2010 Pivot Table Filter - How to print multiple filter selection values)
我希望这能显示诸如“Apple, Banana”之类的东西，但我对如何做到这一点一无所知。我在网上环顾四周，发现分享我的问题的少数人最终没有得到响应（即Excel 2010 数据透视表过滤器 - 如何打印多个过滤器选择值）
Any help with this is very much appreciated!
非常感谢您对此的任何帮助！
EDIT: I'm willing to use a macro to get this to work, so let's see if the VBA community has any ideas.
编辑：我愿意使用宏来让它工作，所以让我们看看 VBA 社区是否有任何想法。
回答by SilverShotBee
Public Sub test()
Dim PVI As PivotItem
Dim Pname(100) As String
Dim MS As String
For Each PVI In ActiveSheet.PivotTables("PivotTable6").PivotFields("Name").PivotItems
Pname(i) = PVI.Value
If MS = "" Then
MS = Pname(i)
Else
MS = MS & ", " & Pname(i)
End If
i = i + 1
Next PVI
'MsgBox MS
ActiveSheet.ChartObjects(1).Chart. _
ChartTitle.Characters.Text = MS
End Sub
This code will cycle through the field name (in this case "Name") in the pivot table (in this case "PivotTable6") and will collect all of the data in the array Pname. It will then change your chart title to that array as a whole string with commas between.
此代码将循环遍历数据透视表（在本例中为“PivotTable6”）中的字段名称（在本例中为“名称”），并将收集数组中的所有数据Pname。然后它会将您的图表标题更改为该数组作为整个字符串，中间有逗号。
May need a little tweaking for you application, but Im sure you can figure it out
可能需要对您的应用程序进行一些调整，但我相信您可以弄清楚
回答by Instant Breakfast
Not a programming answer, but I think this does what you want. In my example I used a table with monthly sales figures for three types of fruit: apples, bananas, and oranges. From the ribbons select Insert > Pivot Table > Pivot Chart. It should look like this:
不是编程答案，但我认为这可以满足您的需求。在我的示例中，我使用了一个表格，其中包含三种水果的月销售数据：苹果、香蕉和橙子。从功能区中选择插入 > 数据透视表 > 数据透视图。它应该是这样的：
Click on the Pivot table placeholder (on the left in my picture) and the pivot field "play area" on the right should change to include row and column labels. Drag the item type you want on the legend in to Column Labels, drag the x-axis item to Row Labels and drag the data to be charted to Values. Change the chart type to a line graph and you should get something like this and the filter you want is directly above the legend:
单击数据透视表占位符（在我的图片左侧），右侧的数据透视字段“播放区域”应更改为包括行和列标签。将图例上所需的项目类型拖到列标签中，将 x 轴项目拖到行标签，然后将要绘制图表的数据拖到值中。将图表类型更改为折线图，您应该会得到如下内容，并且您想要的过滤器位于图例正上方：
Hope this does what you wanted!
希望这能满足您的需求！
回答by ExactaBox
The macro below loops through the pivot table's PageField to find the visible elements, creates a string with those elements, then updates the chart title:
下面的宏循环遍历数据透视表的 PageField 以查找可见元素，使用这些元素创建一个字符串，然后更新图表标题：
Sub UpdateChartTitle()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim co As ChartObject
Dim c As Chart
Dim s As String
s = ""
Set pt = ThisWorkbook.ActiveSheet.PivotTables(1) 'specify the correct PivotTable
Set pf = pt.PageFields.Item(1) 'should be the only PageField
For Each pi In pf.PivotItems 'loop through the fields and find only the visible ones
If pi.Visible Then s = s & pi.Name & ", "
Next
s = Left(s, Len(s) - 2)
Set co = ThisWorkbook.ActiveSheet.ChartObjects(1) 'specify the correct chart object
co.Chart.ChartTitle.Text = s 'change the title
End Sub
回答by Han Soalone
Here's the code to change the title bar, place it as is under a worksheet module (to make it be triggered upon any change). Only thing you have to do is change the pointers to point to your own table and chart -
这是更改标题栏的代码，将其按原样放置在工作表模块下（使其在任何更改时触发）。您唯一要做的就是更改指针以指向您自己的表格和图表 -
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As PivotField
Dim b As PivotItem
Dim c As ChartObject
Dim headerText As String
'set pointers to the objects, replace the pointers with our own !!!!!
Set a = Application.Worksheet("Sheet 1").PivotTables("PivotTable1").PivotFields("Type")
Set c = Application.Worksheet("Sheet 1").ChartObjects("Chart 1")
'loop trough the field items and save the visible values to a string.
'You could hone the code to change the separator for the items for it to appear the way you like.
'Also note that the title bar on the chart has to be visible in order for this to work,
'if not it will give you subscript out of range error.
For Each b In a.PivotItems
If b.Visible = True Then
headerText = headerText + b.Value + ", "
End If
Next
'Set the value to the title bar
c.Chart.ChartTitle.Text = headerText
End Sub
Ask if you need any detailed explanations or instructions to some part of the code.
询问您是否需要对代码的某些部分进行详细的解释或说明。
