如何在Excel中控制各个饼图段的颜色？

I would like to be able to control the color of the sections in a pie chart programmically. Ideally my chart would be based on a 3-column table with the columns being: The Data Value, The Label, and the Pie Chart Color Value. The color values would be that same numbers one sees in Access form properties.

我希望能够以编程方式控制饼图中各部分的颜色。理想情况下，我的图表将基于一个3列表，其列为：数据值，标签和饼图颜色值。颜色值将是在Access表单属性中看到的相同数字。

Thanks,

谢谢，

Steve

史蒂夫

4 个解决方案

#1

Sub a()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points.Item(1).Interior.ColorIndex = 7
End Sub

You can learn the color represented by ColorIndex with the trick I posted in This Other Answer

您可以使用我在其他答案中发布的技巧来学习ColorIndex表示的颜色

#2

Perhaps something on these lines?

也许在这些方面的东西？

Dim ws As Worksheet
Dim sh As Shape

Set ws = Sheet2
Set sh = ws.Shapes.AddChart '.Select

With sh.Chart
    .ChartType = xlPie
    .SetSourceData Source:=Range("Sheet1!$A$1:$B$3")

    For i = 1 To .SeriesCollection(1).Points.Count
        With .SeriesCollection(1).Points(i).Format.Fill
            .ForeColor.RGB = Range("C" & i).Interior.Color

            .Solid
        End With
    Next
End With

This will allow you to add a colour to a cell using the fill button and have it used for a segment.

这将允许您使用填充按钮向单元格添加颜色，并将其用于细分。

#3

First off, I know it can be done. BeGraphic have a freebie Excel add-in with a feature to set the chart element colours in the spreadhseet. See:

首先，我知道可以做到。 BeGraphic有一个免费的Excel加载项，其功能是在spreadhseet中设置图表元素颜色。看到：

http://www.begraphic.com/gauges-and-meters.html
http://www.begraphic.com/gauges-and-meters.html

This use an add-in. I must say the results are impressive. For myself I needed something more Excel-native, many others there will find this option top-shelf.

这使用加载项。我必须说结果令人印象深刻。对于我自己，我需要更多的Excel本机，其他许多人会发现这个选项是*的。

While the VB option from Remou and begraphic's add-in do the deed. For me, or the current project at least, I need an Excel based solution (as much as is practical and is possible). In the meantime, you fine people and myself can find work-arounds via VB, VBA, .Net and/or macros. Visit Jon P's site for available options.

虽然来自Remou的VB选项和begraphic的加载项做了契约。对我来说，或者至少是当前的项目，我需要一个基于Excel的解决方案（尽可能多的是可行的）。与此同时，您和我自己可以通过VB，VBA，.Net和/或宏找到解决方法。访问Jon P的网站了解可用选项。

http://peltiertech.com/WordPress/example-charts
http://peltiertech.com/WordPress/example-charts

If I discover 'more' -- I'll pass it back on this query.

如果我发现'更多' - 我会在此查询中将其传回去。

aloha,
Will

阿罗哈，威尔

#4

Maybe it can be done in using VBA, but here I propose another solution that let you do this using JavaScript. You could use a free Excel add-in called Funfun that allows you to use JavaScript code directly in Excel. That being said, you are able to use libraries like HighCharts.js or D3.js to draw a pie chart and control the color individually. Here is an example that I made based on your description.

也许它可以在使用VBA时完成，但在这里我提出了另一种解决方案，可以让你使用JavaScript来实现。您可以使用名为Funfun的免费Excel加载项，它允许您直接在Excel中使用JavaScript代码。话虽这么说，你可以使用像HighCharts.js或D3.js这样的库来绘制饼图并单独控制颜色。这是我根据您的描述制作的示例。

The colors of different parts of the pie chart are determined by the data of the third column in the spreadsheet. In this example, I used HighCharts.js to control draw this chart. The specific code that controls the color could be seen as below.

饼图不同部分的颜色由电子表格中第三列的数据确定。在这个例子中，我使用HighCharts.js来控制绘制这个图表。控制颜色的具体代码如下所示。

  var pieColors =[];
  for(var i=1;i<data.length;i++){
    switch(parseInt(data[i][2])){
      case 0:
        pieColors.push('blue');
        break;
      case 1:
        pieColors.push('green');
        break;
      case 2:
        pieColors.push('orange');
        break;
      case 3:
        pieColors.push('purple');
        break;
      case 4:
        pieColors.push('red');
        break;
      case 5:
        pieColors.push('brown');
        break;
    }
  }

In this example, I just manually picked six colors, but you could easily generate random color or color in a specific range by JavaScript code.

在这个例子中，我只是手动选择了六种颜色，但您可以通过JavaScript代码轻松生成特定范围内的随机颜色或颜色。

The Funfun add-in also has an online editor in which you could test with your code and result. You could check the detailed code and this example on the link below.

Funfun加载项还有一个在线编辑器，您可以使用您的代码和结果进行测试。您可以在下面的链接中查看详细代码和此示例。

https://www.funfun.io/1/#/edit/5a4f4680c3a8a526caeec989

Once you are satisfied with your result, you could easily load the result into your Excel using the link above. But of course, first, you need to add the Funfun add-in into your Excel by Insert - Office Add-ins. Here are some screenshots showing how you load the example into you Excel.

对结果感到满意后，可以使用上面的链接轻松地将结果加载到Excel中。但是，当然，首先，您需要通过插入 - Office加载项将Funfun加载项添加到Excel中。以下是一些屏幕截图，显示了如何将示例加载到Excel中。

Disclosure: I'm a developer of Funfun

披露：我是Funfun的开发者

#1

Sub a()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Points.Item(1).Interior.ColorIndex = 7
End Sub