vba 根据值在 Excel 中着色条形图

Coloring Bar Graph in Excel based on value

提问by socool111

I have the following data

我有以下数据

 Client              Week            Revenue

 Google              1               4000
 Microsoft           1               5000
 Intel               2               2000
 EvilCorporation     3               1000

You get the idea (the numbers and names have obviously been changed in the above data). I want the bar graph to be a certain color based on the week number. The data will change week to week, but there will always be a week 1, 2 and 3. So i need 3 colors, one for each week, that corresponds to the proper company. The x-values would label the companies, and Y values would be the revenue.

您明白了（上述数据中的数字和名称显然已更改）。我希望条形图是基于周数的某种颜色。数据每周都会改变，但总会有第 1、2 和 3 周。所以我需要 3 种颜色，每周一种，对应于适当的公司。x 值将标记公司，Y 值将是收入。

From my research, I found out that this is pretty much impossible in excel using the graph tools, and VBA is required. While I do know VBA functions, I have 0 experience with VBA graphing, and how and where would you call the function after creating it.

从我的研究中，我发现这在使用图形工具的 excel 中几乎是不可能的，并且需要 VBA。虽然我知道 VBA 函数，但我对 VBA 绘图的经验为 0，以及在创建函数后如何以及在何处调用该函数。

If there is a different type of graph this would be easier with, I'm all for it, as long as it is easy to look at.

如果有不同类型的图表，这会更容易，只要它易于查看，我都赞成。

回答by cint

Suppose you have a table in Excel as you provided (in A1:C5):

假设您在 Excel 中提供了一个表格（在 A1:C5 中）：

1. Select the range of your data in worksheet (i.e. A1:C5).
2. Open 'Microsoft Visual Basic? for Applications' (Press ALT + F11in Excel).
3. Create a new module on your current excel file project (Right click your VBA project > Insert > Module).
4. Paste the following code.
5. Run the code (Press F5).
6. Go back to your worksheet. Done!!!

1. 在工作表中选择数据范围（即 A1:C5）。
2. 打开“Microsoft Visual Basic? 应用程序”（ALT + F11在 Excel 中按）。
3. 在您当前的 excel 文件项目上创建一个新模块（右键单击您的 VBA 项目 > 插入 > 模块）。
4. 粘贴以下代码。
5. 运行代码（按F5）。
6. 回到你的工作表。完毕！！！

Following code will help you to: create the bar chart and formatting the color according to the 'week' value.

以下代码将帮助您：创建条形图并根据“周”值设置颜色格式。

Sub CreateChartFormattingbyPtValue()

Set rRng = Selection

'SET YOUR DESIRED COLOR HERE
week1ColorIndex = 3
week2ColorIndex = 4
week3ColorIndex = 5

'Chart Basic Setting
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=rRng
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetElement (msoElementChartTitleAboveChart)

'Force to create dummy Series to present Legend (Week value with color)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""Week 1"""
ActiveChart.SeriesCollection(2).Name = "=""Week 2"""
ActiveChart.SeriesCollection(1).Name = "=""Week 3"""
ActiveChart.SeriesCollection(1).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(week3ColorIndex)
ActiveChart.SeriesCollection(2).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(week2ColorIndex)
ActiveChart.SeriesCollection(3).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(week1ColorIndex)

'Size and Position of Chart
With ActiveChart.Parent
    .Height = 400
    .Width = 500
    .Top = 150
    .Left = 150
End With

'Axes label font size italic and bold
ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 10
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = False
ActiveChart.Axes(xlCategory).TickLabels.Font.Italic = True

'Chart Title
ActiveChart.ChartTitle.Text = "Your Title Here (Week1:Red , Week2:Green , Week3:Green)"
ActiveChart.ChartTitle.Font.Size = 12


With ActiveChart
    vX = .SeriesCollection(1).XValues
    vY = .SeriesCollection(1).Values

    'Formatting the interior color for each points
    For thisvY = 1 To UBound(vY)
        If vY(thisvY) = 1 Then .SeriesCollection(2).Points(thisvY).Interior.ColorIndex = week1ColorIndex
        If vY(thisvY) = 2 Then .SeriesCollection(2).Points(thisvY).Interior.ColorIndex = week2ColorIndex
        If vY(thisvY) = 3 Then .SeriesCollection(2).Points(thisvY).Interior.ColorIndex = week3ColorIndex
    Next thisvY

End With


End Sub

Remarks:

评论：

1. Remember to select the range of your data in worksheet before run.
2. This code is customized according to your data table. You can add new row as necessary, but if you modify the column order then you need to modify the code too.
3. If you want to change the week color, you just need to change the ColorIndexnumber at the beginning of the code: week1ColorIndex, week2ColorIndex, week3ColorIndex.
4. If you want to adjust the size or font, look into Height, Width, .Font.Size.
5. Code has been tested OK on Win 7 Office 2010.

1. 请记住在运行前选择工作表中数据的范围。
2. 此代码是根据您的数据表定制的。您可以根据需要添加新行，但如果您修改列顺序，那么您也需要修改代码。
3. 如果要更改星期颜色，只需更改ColorIndex代码开头的数字：week1ColorIndex, week2ColorIndex, week3ColorIndex。
4. 如果要调整大小或字体，请查看Height、 Width、.Font.Size。
5. 代码已在 Win 7 Office 2010 上测试正常。

回答by user3514930

I think it's impossible without VBA. A simple "MIX" can be this.
Following the scheme:

我认为没有 VBA 是不可能的。一个简单的“混合”可以是这样。
按照方案：

The column "B" have the Conditional formatting to Set the color you want in the graph. If you don't want see, copy the value (reference) to another column hidden...
In the VBA Manager ad the code below:

“B”列具有条件格式以设置您想要的图形颜色。如果您不想看到，请将值（引用）复制到另一列隐藏...
在 VBA 管理器广告中，代码如下：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim yy

    yy = ActiveCell.Address
    ActiveSheet.ChartObjects("Chart 1").Activate
    i = 1
    For Each xx In ActiveChart.SeriesCollection(1).Points
        i = i + 1
        xx.Format.Fill.ForeColor.RGB = Range("B" & i).DisplayFormat.Interior.Color   ' REF
    Next
    Range(yy).Activate
End Sub

To open VBA Manager use ALT+F11, after double click on Sheet1 (On the left) and paste the code...
If you have the same scheme in the sheet, it's not a problem. If you have other range change the line with REF, changing the column of the week...
The best way is TRY !!!

要打开 VBA 管理器，请使用 ALT+F11，在双击 Sheet1（左侧）并粘贴代码后...
如果您在工作表中有相同的方案，这不是问题。如果你有其他范围用REF改变行，改变一周的列......
最好的方法是TRY !!!