Excel 2007根据另一个字段更改单个系列中条形的颜色 [英] Excel 2007 Change colour of bars in a single series, based on another field

问题描述

我有一个数据透视表，其数据类似如下：

I have a pivot table with data like this:

Chain     Store               Units
Bob's     BB Los Angeles        10
Bob's     BB San Diego          12
Tom's     TM Los Angeles        12
Tom's     TM San Francisco      18
Kate's    K Monterey            11

目前我有一个条形图，只是按降序显示所有商店和单位，所以TM旧金山是第一，然后是TM洛杉矶和BB圣地亚哥，然后是K蒙特雷等。这是我想要查看的数据。

Currently I have a bar chart just showing all stores and units in descending order, so TM San Francisco is first, then TM Los Angeles and BB San Diego, then K Monterey etc. This is how I want to see the data.

但我也想要颜色代码的链，所以鲍勃的商店有一个红色的酒吧，汤姆的蓝色，凯特的绿色。

But I also want to colour code the chains, so Bob's stores have a red bar, Tom's have blue, and Kate's are green.

有可能做到这一点吗？我尝试了各种方式设置透视表，但它最终看起来一团糟，我找不到一种方法来基于每个酒吧的颜色在链字段。

Is it possible to do this? I've tried various ways of setting out the pivot table but it ends up looking a mess and I can't find a way of basing the each bar's colour on the 'chain' field.

=

我测试宏解决方案，但如果任何人有一个非宏解决方案，那么请仍然回答。 ：）

Am testing the macro solution, but if anyone has a non-macro solution then do please still answer. :)

推荐答案

如果你正在寻找一个基于宏/ VBA的解决方案，这很容易操纵图表的数据点格式化。假设系列XValues = Chain，你可以这样做：

If you're looking for a macro/VBA-based solution, this is pretty easy to manipulate the chart's data point formatting. Assuming that the series XValues = Chain, you could do this:

Sub SetColors()
Dim cht As Chart
Dim srs As Series
Dim xVals As Variant
Dim p As Long

Set cht = ActiveSheet.ChartObjects(1).Chart '<-- Modify as needed'
Set srs = cht.SeriesCollection(1) '<-- Modify as needed'

xVals = srs.XValues  '<-- modify as needed, if your Chain is not part of series data you will need to build this array another way'

For p = 1 To UBound(xVals)
    Select Case xVals(p)
        Case "Bob's"
            srs.Points(p).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        Case "Tom's"
            srs.Points(p).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
        Case "Kate's"
            srs.Points(p).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
    End Select
Next
End Sub

如果链不是系列数据，您必须更改 xVals 的设置方式，但一般原则仍然适用：迭代系列中的点，检查参考列表，并根据参考项目应用特定颜色。

If the Chain is NOT part of the series data, you'd have to change the way that xVals is set, but the general principle should still apply: Iterate over the points in the series, check against a reference list, and apply a specific color based on the reference item.

这篇关于Excel 2007根据另一个字段更改单个系列中条形的颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！