数据透视图系列颜色基于行字段 [英] Pivot Chart series color based on row fields

问题描述

新建枢纽图表及其关联的vba。但是我试图根据所属的省份来改变条形图的颜色。我一直无法在图表中使用条件格式，所以我尝试了一个宏。由于我无法上传图片，我的表格看起来有点像这样：

New to pivot charts and their associated vba. But I'm attempting to change the colours of my bar graph depending on which province they belong to. I've been unable to use conditional formatting in a chart so I attempted a macro. Since I can't upload pictures my table looks a bit like this:

Prov/Name/Period/Value
ON: Name1, Dec14, 100
    Name2, Dec13, 200
BC: Name1, Dec14, 400
    Name2, Dec13, 600
SK: Name1, Dec14, 100
    Name2, Dec13, 2000

我的透视图以条形图显示这些数据，每个栏是相同的颜色。我想让落在BC行字段下的所有系列显示黄色，以突出显示它们与其余的。到目前为止，我一直在做这个，我试图做一个vba宏，但甚至不知道从哪里开始参考图表的Prov行字段。

My pivot chart displays this data in a bar graph with each bar being the same colour. I'd like all series that fall under the BC row field to display yellow to highlight them against the rest. So far I've been doing this by hand, and I've attempted to make a vba macro, but don't even know where to start in referencing the Prov row field of the chart.

推荐答案

好的，所以你需要参考的图表上的项目是XValues。 XValues可以是数组或范围。由于某些原因，尽管您无法通过xvalues（x）循环遍历它。相反，您需要将其分配给变量，然后您可以循环使用它并检查特定值。
请考虑以下代码。
哦，您可能需要将代码附加到任何枢纽图更改的事件。

Ok, so the item on the chart that you need to reference is the XValues. XValues can be an array or a range. For some reason though you can't loop through it with xvalues(x). Instead you need to assign it to a variable and THEN you can loop through it and check for a specific value. Consider the following code. Oh and you may need to attached the code to any events where the pivotchart changes.

Dim ch As ChartObject, Counter As Integer, XValueArr() As Variant

Set ch = Sheet1.ChartObjects("Chart 1") 'this is your pivotchart

XValueArr() = ch.Chart.SeriesCollection(1).XValues 'assign the XValues property to an array

'loop through the array and check the value
For Counter = LBound(XValueArr) To UBound(XValueArr)
    If Left(XValueArr(Counter), 2) = "BC" Then
    ch.Chart.SeriesCollection(1).Points(Counter).Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
    End If
Next Counter

这篇关于数据透视图系列颜色基于行字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！