如何根据列值为 xy 散点图中的点着色? [英] How can I color dots in a xy scatterplot according to column value?

问题描述

考虑以下工作表:

     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

我已经使用 Excel 中的散点图函数创建了以下图表:

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

但是，图表中的每个点都有一个附加属性:GROUP.有四组:red、orange、black 和 green.我想相应地为每个点着色，以便我可以看到一个模式(例如，green 组几乎总是在图表的左侧).因为我的列表有 500 行长，所以我无法手动执行此操作.如何自动执行此操作?

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?

推荐答案

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

I answered a very similar question:

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

您只需要遍历系列的 .Points 集合，然后您可以根据任何内容分配点的 .Format.Fill.ForeColor.RGB 值你需要的标准.

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.

更新

下面的代码将根据屏幕截图为图表着色.这仅假设使用了三种颜色.您可以为其他颜色值添加额外的 case 语句，并将 myColor 的分配更新为每个颜色值的适当 RGB 值.

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.

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

这篇关于如何根据列值为 xy 散点图中的点着色?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！