excel - 时间序列值与我的 Excel 图表上的水平轴不匹配
问题描述
通过使用 XY 图代替折线图解决
我想在 Excel 中创建一个图表,其中包含多个按日期链接的系列(即一个系列于 2018 年 12 月 31 日结束,下一个系列于 2018 年 12 月 31 日开始)。原始数据取自一张有两列的表格;日期列和值列。
此处讨论的图表称为“Beta”,包含 3 个时间序列。这是我的尝试:
Sub UpdateGraph
Dim ws As Worksheet
Dim rng_NF3 As Range
Dim rng_Barra As Range
Dim rng_NF3_Date As Range
Dim rng_Barra_Date As Range
Dim rng_Total_Date As Range
Dim rng_Total_Val As Range
Dim cht_Name As String
Dim ArrDate As Variant
Dim arrValues As Variant
cht_Name = "Beta" ' Example
Set ws = Activesheet
Set cht = ws.ChartObjects(cht_Name)
Set rng_NF3 = ws.Range(ws.Cells(2, 4), ws.Cells(200, 4)) ' Set range of values from NF3 (GEM3)
Set rng_Barra = ws.Range(ws.Cells(201, 4), ws.Cells(500, 4)) ' Set range of values Barra
Set rng_NF3_Date = rng_NF3.Offset(0, -1) ' Set range of date for NF3 observations
Set rng_Barra_Date = rng_Barra.Offset(0, -1) ' Set range of date for Barra observations
Set rng_Total_Date = Union(rng_NF3_Date, rng_Barra_Date)
Set rng_Total_Val = Union(rng_NF3, rng_Barra)
ArrDate = rng_Total_Date.Value
arrValues = rng_Total_Val.Value
With cht.Chart
.FullSeriesCollection(1).Format.Line.ForeColor.RGB = ws.Cells(1, 20).Interior.Color
.FullSeriesCollection(2).Format.Line.ForeColor.RGB = ws.Cells(2, 20).Interior.Color
.FullSeriesCollection(1).Values = rng_NF3 ' Value series for NF3
.FullSeriesCollection(2).Values = rng_Barra ' Value series for Barra
.FullSeriesCollection(1).XValues = rng_NF3_Date
.FullSeriesCollection(2).XValues = rng_Barra_Date
If cht_Name = "Beta" Then ' Defining Beta = 1
.FullSeriesCollection(3).Format.Line.ForeColor.RGB = ws.Cells(1, 21).Interior.Color ' Color
.FullSeriesCollection(3).Values = 1 ' Should be a range with the same number in all elements, not working but another issue.
.FullSeriesCollection(3).XValues = rng_Total_Date
End If
' X-axis
With cht.Chart.Axes(xlCategory)
.CategoryType = xlTimeScale
'.MajorUnitScale = xlMonths
.MinimumScale = ArrDate(LBound(ArrDate, 1), 1)
.MaximumScale = ArrDate(UBound(ArrDate, 1), 1)
.MajorUnit = 3 ' Manually set x-axis unit scale
'.MajorUnitIsAuto = True
End With
' Y-axis
With cht.Chart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MajorUnitIsAuto = True
End With
End With
End sub
我希望这个系列一个接一个地继续,但它们都从横轴的第一个日期开始。我怀疑 Excel 无法识别我将时间序列日期与 x 轴日期匹配的尝试。
解决方案
@TimWilliams 提供的答案,如果他没有创建帖子,我会尽可能将其标记为答案。
解决方案是使用 XY 散点图(可用线选项)而不是我最初使用的折线图。