如何根据列值在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 。有四组：红色，橙色，黑色和绿色。我想要相应地对每个点进行颜色，以便我可以看到一个图案（例如，组绿色几乎总是在图表的左侧）。因为我的列表是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 集合，然后可以分配点基于您需要的任何条件的值。

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.

UPDATED

下面的代码将为屏幕截图绘制图表。这只假设使用三种颜色。您可以为其他颜色值添加其他案例语句，并将 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屋！