考虑以下工作表：
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
然而，图表中的每个点都有一个额外的属性：GROUP。有四个组： red，orange，black和green。我想相应地着色每个点，以便我可以看到模式（例如，组 green 几乎始终在图表的左侧）。因为我的列表有500行，所以我无法手动完成这项操作。如何自动完成这项操作？您需要为每个颜色组制作一个附加数据组，以代表该特定组的Y值。 您可以使用这些组在图表中创建多个数据集。
以下是使用您的数据的示例：
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())
之后它应该看起来像这样：
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
现在您可以使用不同的数据集生成您的图表。这里是一张照片，显示了这个示例数据：
您可以将系列(X;Y)的值更改为B:B ; E:E、B:B ; F:F、B:B ; G:G，以便在添加更多数据时自动更新图表。
=IF($D2=E$1,$C2,NA())，然后您只需将相同的公式复制/粘贴到所有组（列E、F和G）中即可。 - ChaimG我回答了一个非常相似的问题：
https://stackoverflow.com/a/15982217/1467082
你只需要遍历系列的.Points集合，然后可以根据任何条件分配点.Format.Fill.ForeColor.RGB值。
更新
下面的代码将按照屏幕截图的颜色着色图表。这只假设使用三种颜色。您可以为其他颜色值添加其他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
https://www.funfun.io/1/#/edit/5a61ed15404f66229bda3f44
为了创建这个图表，我在电子表格上输入了我的数据，并用一个json文件（名为short）读取了它。script.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]);
}
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
});
}
试试这个：
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
最近我也遇到了类似的问题，我用下面的代码解决了它。希望能帮到你！
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