gpt4 book ai didi

vba - XY 散点图和字典问题

转载 作者:行者123 更新时间:2023-12-04 20:17:50 28 4
gpt4 key购买 nike

我有一个函数可以根据任意数量的字典(每个字典代表图表上的一条线)生成 XY 散点图,每个字典都包含一个日期键和一个数值。到目前为止,这些值似乎在 Y 轴上有效,但日期轴 (X) 似乎已损坏。每次我从字典中将系列添加到图表中时,当我特别想要一个散点图时,它都会将其强制为条形图。如果我在分配后将其强制返回散点图,则它完全拒绝显示日期轴。

这里有些例子。

我希望图表看起来像这样
enter image description here

如果我告诉它不要使用日期,图表看起来像这样

enter image description here

当我专门将系列的数据类型设置为 xlDate 时,图形变为此。它神秘地变成了条形图

enter image description here

如果我在将其设置为使用 xlDate 后专门将其更改回散点图,它看起来像这样

enter image description here

任何帮助将不胜感激。这是我的 VBA 代码

Sub GenerateProgressGraph()


Dim Dictionaries(1 To 2) As New Dictionary

Dictionaries(1).Add DateValue("1/2/2012"), 1
Dictionaries(1).Add DateValue("2/2/2012"), 2
Dictionaries(1).Add DateValue("3/2/2012"), 3
Dictionaries(1).Add DateValue("4/2/2012"), 4

Dictionaries(2).Add DateValue("1/2/2012"), 1
Dictionaries(2).Add DateValue("2/2/2012"), 1
Dictionaries(2).Add DateValue("3/2/2012"), 3
Dictionaries(2).Add DateValue("4/2/2012"), 4

Call ProcessProgressGraph(Dictionaries)
End Sub

Sub ProcessProgressGraph(Dict() As Dictionary)

Dim Graph As Shape
Dim GraphRange As Range

With ActiveSheet

'set graph area
Set GraphRange = Application.Range("E4:P21")

'add a new chart
Set Graph = Shapes.AddChart(xlXYScatterLinesNoMarkers, GraphRange.Left, _
GraphRange.Top, GraphRange.Width, GraphRange.Height)

With Graph.Chart

With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Characters.Text = "Dates"
End With

.HasTitle = True
.ChartTitle.Text = "Chart Title"
.ChartType = xlXYScatterLinesNoMarkers

'clear all chart data
'(Excel has a tendency to give us silly resultsets by default)
For Each srs In .SeriesCollection
srs.Delete
Next

For Each Dictionary In Dict
Dim ss As Series
Set ss = .SeriesCollection.NewSeries
ss.Name = "Values"
ss.XValues = Dictionary.Keys
ss.Type = xlDate
.ChartType = xlXYScatterLinesNoMarkers 'this forces it back into a scatter plot since it auto makes a bar graph
ss.Values = Dictionary.Items
Next
End With
End With
End Sub

最佳答案

问题在于 Excel 对 X 轴值的 native 处理。老实说,我不知道为什么会这样,但我知道如何解决它:

  • 获取您的 X 轴日期值并将它们转换为 Long 类型,如下所示:
    ReDim longDates(Dictionary.Count) as Long
    For i = LBound(Dictionary.Keys) to UBound(Dictionary.Keys)
    longDates(i) = Dictionary.Keys(i)
    Next
  • 使用 ss.XValues = longDates 将 longDates 分配为 X 轴值
  • 将 TickLabel 数字格式设置为 的日期。结束函数使用:
    .Axes(xlCategory).TickLabels.NumberFormat = "d/mm/yyyy"

  • 那应该工作

    关于vba - XY 散点图和字典问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17712352/

    28 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com