如何根据列值在xy散点图中画点颜色?

Consider the following worksheet:

考虑以下工作表:

     A       B        C        D
1 COMPANY  XVALUE   YVALUE   GROUP
2 Apple     45       35       red
3 Xerox     45       38       red
4 KMart     63       50       orange
5 Exxon     53       59       green

I have used the scatterplot function in Excel to create the following chart:

我在Excel中使用了scatterplot函数来创建如下图:

However, each point in the chart has an additional property: GROUP. There are four groups: red, orange, black and green. I would like to color each dot accordingly, so that I could perhaps see a pattern (group greenbeing almost always on the left side of the chart, for instance). Because my list is 500 rows long, I cannot do this manually. How can I do this automatically?

然而，图表中的每个点都有一个额外的属性:GROUP。有四组:红色，橙色，黑色和绿色。我想把每个点都涂上颜色，这样我就可以看到一个图案(例如，在图表的左边几乎总是绿色的)。因为我的列表有500行长，所以我不能手动操作。我怎么能自动做到呢?

5 个解决方案

#1

I answered a very similar question:

我回答了一个非常相似的问题:

https://*.com/a/15982217/1467082

https://*.com/a/15982217/1467082

You simply need to iterate over the series' .Points collection, and then you can assign the points' .Format.Fill.ForeColor.RGB value based on whatever criteria you need.

您只需要迭代该系列的. points集合，然后您就可以分配这些点的。format . fill.forecolor。RGB值基于您需要的任何标准。

UPDATED

更新

The code below will color the chart per the screenshot. This only assumes three colors are used. You can add additional case statements for other color values, and update the assignment of myColor to the appropriate RGB values for each.

下面的代码将根据屏幕截图给图表着色。这里只使用了三种颜色。您可以为其他颜色值添加额外的case语句，并将myColor的赋值更新到相应的RGB值。

Option Explicit
Sub ColorScatterPoints()
    Dim cht As Chart
    Dim srs As Series
    Dim pt As Point
    Dim p As Long
    Dim Vals$, lTrim#, rTrim#
    Dim valRange As Range, cl As Range
    Dim myColor As Long

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set srs = cht.SeriesCollection(1)

   '## Get the series Y-Values range address:
    lTrim = InStrRev(srs.Formula, ",", InStrRev(srs.Formula, ",") - 1, vbBinaryCompare) + 1
    rTrim = InStrRev(srs.Formula, ",")
    Vals = Mid(srs.Formula, lTrim, rTrim - lTrim)
    Set valRange = Range(Vals)

    For p = 1 To srs.Points.Count
        Set pt = srs.Points(p)
        Set cl = valRange(p).Offset(0, 1) '## assume color is in the next column.

        With pt.Format.Fill
            .Visible = msoTrue
            '.Solid  'I commented this out, but you can un-comment and it should still work
            '## Assign Long color value based on the cell value
            '## Add additional cases as needed.
            Select Case LCase(cl)
                Case "red"
                    myColor = RGB(255, 0, 0)
                Case "orange"
                    myColor = RGB(255, 192, 0)
                Case "green"
                    myColor = RGB(0, 255, 0)
            End Select

            .ForeColor.RGB = myColor

        End With
    Next


End Sub

#2

Non-VBA Solution:

You need to make an additional group of data for each color group that represent the Y values for that particular group. You can use these groups to make multiple data sets within your graph.

您需要为代表该特定组的Y值的每个颜色组添加一个额外的数据组。您可以使用这些组在您的图中创建多个数据集。

Here is an example using your data:

下面是一个使用数据的例子:

     A       B        C        D                    E                        F                            G
----------------------------------------------------------------------------------------------------------------------
1| COMPANY  XVALUE   YVALUE   GROUP                 Red                     Orange                       Green
2| Apple     45       35       red         =IF($D2="red",$C2,NA()) =IF($D2="orange",$C2,NA()) =IF($D2="green",$C2,NA())
3| Xerox     45       38       red         =IF($D3="red",$C3,NA()) =IF($D3="orange",$C3,NA()) =IF($D3="green",$C3,NA())
4| KMart     63       50       orange      =IF($D4="red",$C4,NA()) =IF($D4="orange",$C4,NA()) =IF($D4="green",$C4,NA())
5| Exxon     53       59       green       =IF($D5="red",$C5,NA()) =IF($D5="orange",$C5,NA()) =IF($D5="green",$C5,NA())

It should look like this afterwards:

之后应该是这样的:

     A       B        C        D          E           F          G
---------------------------------------------------------------------
1| COMPANY  XVALUE   YVALUE   GROUP       Red         Orange     Green
2| Apple     45       35       red         35         #N/A       #N/A    
3| Xerox     45       38       red         38         #N/A       #N/A
4| KMart     63       50       orange     #N/A         50        #N/A
5| Exxon     53       59       green      #N/a        #N/A        59

Now you can generate your graph using different data sets. Here is a picture showing just this example data:

现在您可以使用不同的数据集生成图形。这里有一幅图，展示了这个例子的数据:

You can change the series (X;Y) values to B:B ; E:E, B:B ; F:F, B:B ; G:G respectively, to make it so the graph is automatically updated when you add more data.

你可以将级数(X, Y)的值改为B:B;艾凡:E、B:B;F:F,B:B;G:G，为了使图形在你添加更多数据时自动更新。

#3

I see there is a VBA solution and a non-VBA solution, which both are really good. I wanted to propose my Javascript solution.

我看到有一个VBA解决方案和一个非VBA解决方案，两者都非常好。我想提出我的Javascript解决方案。

There is an Excel add-in called Funfun that allows you to use javascript, HTML and css in Excel. It has an online editor with an embedded spreadsheet where you can build your chart.

有一个Excel插件叫做Funfun，它允许你在Excel中使用javascript、HTML和css。它有一个在线编辑器和一个嵌入式电子表格，你可以在那里建立你的图表。

I have written this code for you with Chart.js:

我已经为你写了这段代码。

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

https://www.funfun.io/1/ /编辑/ 5 a61ed15404f66229bda3f44

To create this chart, I entered my data on the spreadsheet and read it with a json file, it is the short file.

为了创建这个图表，我在电子表格上输入了我的数据，并使用json文件读取它，这是一个简短的文件。

I make sure to put it in the right format, in script.js, so I can add it to my chart:

我一定要用正确的格式，在脚本中。js，我可以把它添加到我的图表中:

var data = [];
var color = [];
var label = [];

for (var i = 1; i < $internal.data.length; i++)
{
    label.push($internal.data[i][0]);
    data.push([$internal.data[i][1], $internal.data[i][2]]);
    color.push($internal.data[i][3]);
}

I then create the scatter chart with each dot having his designated color and position:

然后我创建一个散点图，每个点有他指定的颜色和位置:

 var dataset = [];
  for (var i = 0; i < data.length; i++) {   
    dataset.push({
      data: [{
        x: data[i][0],
        y: data[i][1] 
      }],
      pointBackgroundColor: color[i],
      pointStyle: "cercle",
      radius: 6  
    });
  }

After I've created my scatter chart I can upload it in Excel by pasting the URL in the funfun Excel add-in. Here is how it looks like with my example:

在我创建了散点图之后，我可以通过在funfun Excel插件中粘贴URL来在Excel中上传它。下面是我的例子:

Once this is done You can change the color or the position of a dot instantly, in Excel, by changing the values in the spreadsheet.

一旦完成，您可以通过更改电子表格中的值，在Excel中立即更改一个点的颜色或位置。

If you want to add extra dots in the charts you just need to modify the radius of data in the short json file.

如果您想在图表中添加额外的点，您只需要在短json文件中修改数据的半径。

Hope this Javascript solution helps !

希望这个Javascript解决方案有帮助!

Disclosure : I’m a developer of funfun

信息披露:我是funfun的开发者。

#4

Try this:

试试这个:

Dim xrndom As Random
    Dim x As Integer
    xrndom = New Random

    Dim yrndom As Random
    Dim y As Integer
    yrndom = New Random
    'chart creation
    Chart1.Series.Add("a")
    Chart1.Series("a").ChartType = DataVisualization.Charting.SeriesChartType.Point
    Chart1.Series("a").MarkerSize = 10
    Chart1.Series.Add("b")
    Chart1.Series("b").ChartType = DataVisualization.Charting.SeriesChartType.Point
    Chart1.Series("b").MarkerSize = 10
    Chart1.Series.Add("c")
    Chart1.Series("c").ChartType = DataVisualization.Charting.SeriesChartType.Point
    Chart1.Series("c").MarkerSize = 10
    Chart1.Series.Add("d")
    Chart1.Series("d").ChartType = DataVisualization.Charting.SeriesChartType.Point
    Chart1.Series("d").MarkerSize = 10
    'color
    Chart1.Series("a").Color = Color.Red
    Chart1.Series("b").Color = Color.Orange
    Chart1.Series("c").Color = Color.Black
    Chart1.Series("d").Color = Color.Green
    Chart1.Series("Chart 1").Color = Color.Blue

    For j = 0 To 70
        x = xrndom.Next(0, 70)
        y = xrndom.Next(0, 70)
        'Conditions
        If j < 10 Then
            Chart1.Series("a").Points.AddXY(x, y)
        ElseIf j < 30 Then
            Chart1.Series("b").Points.AddXY(x, y)
        ElseIf j < 50 Then
            Chart1.Series("c").Points.AddXY(x, y)
        ElseIf 50 < j Then
            Chart1.Series("d").Points.AddXY(x, y)
        Else
            Chart1.Series("Chart 1").Points.AddXY(x, y)
        End If
    Next

#5

Recently i had to do something similar and i resolved it with the code below. Hope it helps!

最近我不得不做一些类似的事情，我用下面的代码解决了这个问题。希望它可以帮助!

Sub ColorCode()
Dim i As Integer
Dim j As Integer
i = 2
j = 1

Do While ActiveSheet.Cells(i, 1) <> ""


If Cells(i, 5).Value = "RED" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(255, 0, 0)



Else

If Cells(i, 5).Value = "GREEN" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(0, 255, 0)

Else

If Cells(i, 5).Value = "GREY" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(192, 192, 192)

Else

If Cells(i, 5).Value = "YELLOW" Then
ActiveSheet.ChartObjects("YourChartName").Chart.FullSeriesCollection(1).Points(j).MarkerForegroundColor = RGB(255, 255, 0)

End If
End If
End If
End If

i = i + 1
j = j + 1

Loop



End Sub