如何在VBA / Excel中作为点本身的X，Y坐标的函数来改变散点图上的点的颜色？ [英] How can I vary the colour of a point on a scatter-graph in VBA/Excel as a function of the X, Y co-ordinates of the point itself?

问题描述

我正在开发一个基于Excel的模拟器，运行一次特定的测试1000次。每个测试的结果是两个坐标。这些坐标被写入一个范围。然后根据该范围创建一个X-Y散点图。

I am developing an Excel-based simulator that runs a specific test 1,000 times. The result of each test is two co-ordinates. These co-ordinates are written to a range. I then create an X-Y Scatter graph based on that range.

我现在想要做的是循环遍历图表中的每一个点，并根据X和Y坐标改变该点的颜色的要点。具体来说，我想要获得X Mod（半最大X）和Y Mod（半最大Y），以便产生颜色的强度将随着靠近图中间的点而增加的效果。

What I now wish to do is to cycle through each of the 1,000 points of the graph, and change the colour of that point based on the X and Y co-ordinate of the point. Specifically, I want to take X Mod (Half Max X) and Y Mod (Half Max Y), in order to create the effect that the intensity of the colour will increase as the points near the middle of the graph.

我想使用的代码如下：

Temp = "'" + FirstCalcs.Name + "'!" + FirstCalcs.Cells(SOWD + 3, 2).Address + ":" + FirstCalcs.Cells(SOWD + 3, NumOfTests + 1).Address
Temp2 = "'" + FirstCalcs.Name + "'!" + FirstCalcs.Cells(SOWD + 4, 2).Address + ":" + FirstCalcs.Cells(SOWD + 4, NumOfTests + 1).Address
Set chtChart = Charts.Add
With chtChart

    Do Until .SeriesCollection.Count = 1     ' remove extra series, which Excel spawns like rabbits, for some reason
        .SeriesCollection(1).Delete
    Loop

    .Name = Institution + " summary"
    .ChartType = xlXYScatter
    'Link to the source data range.

    '.SetSourceData Source:=FirstCalcs.Range(Temp)

    .SeriesCollection(1).Values = Temp2
    .SeriesCollection(1).XValues = Temp

    For Each pts In .SeriesCollection(1).Points

            .Format.Fill.Solid

            'The next line is what I can't figure out
            .MarkerBackgroundColor = RGB(XFunctionOf(.pts.XCoor),  YFunctionOf(.pts.YCoor), 128)

            .MarkerStyle = xlMarkerStyleDiamond
            .MarkerSize = 10
    End With

    Next i

    .HasTitle = True
    .ChartTitle.Text = "Summary"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "T"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time before T achieved"
    .HasLegend = False

End With

但是，我无法找到一个方法来获得X AND Y值，以便对它们执行必要的功能。我可以根据点本身的数量（1到1000）改变颜色，但这不是我想要做的 - 颜色是每个点的X，Y坐标的函数。值得说明的是，X和Y坐标的范围是可变的，即测试不返回每次在相同尺度上的结果。

However I can't work out a way to get the X AND Y value in order to perform the requisite function on them. I can vary the colour according to the number of the point itself (1 to 1000), but that is not what I want to do - the colour is a function of the X, Y co-ordinate of each point. It is worth saying that the ranges of both the X and Y co-ordinates are variable, i.e. the test doesn't return results which are on the same scale each time.

任何帮助将非常感激和感谢你提前。

Any help would be enormously appreciated and thank-you in advance.

推荐答案

以下是一个简单的示例，说明如何访问每个点的x和y值：

Here's a simple example of how to access the x and y value for each point:

Sub Tester()

    Dim s As Series, i As Long, x, y

    Set s = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

    For i = 1 To s.Points.Count
        x = s.XValues(i)
        y = s.Values(i)
        If x > 3 And y < 40 Then
            s.Points(i).MarkerBackgroundColor = vbRed
        End If
    Next i

End Sub

这篇关于如何在VBA / Excel中作为点本身的X，Y坐标的函数来改变散点图上的点的颜色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！