excel - 如何为图表制作从每周星期一开始的刻度标签？

Question

我试图用 Excel 图表根据每周的进度来显示一段时间内的趋势。使用 VBA，如何强制 xlCategory 轴的刻度标签对齐数据集中的每个星期一？这可能吗？我知道如何格式化轴，但不知道如何告诉它从每个星期一开始。谢谢!

我知道有一个选项可以更改主要单位和次要单位，也许我可以以某种方式使用它们？这是我到目前为止所拥有的:

On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0

Set metricschart = sht.Shapes.AddChart.Chart

With metricschart
    .Parent.Name = "metricschart"
    .HasTitle = True
    .ChartTitle.Text = "Business Requirements Tested Over Time"
    .ChartTitle.Characters.Font.Size = 14
    .ChartType = xlXYScatterSmoothNoMarkers
    .SetSourceData Source:=sht2.Range("A1:DA2")
    .Location where:=xlLocationAsObject, Name:=sht.Name
    .Parent.Height = 325
    .Parent.Width = 600
    .Parent.Top = 70
    .Parent.Left = 350
    .Legend.LegendEntries(1).Delete
    .Axes(xlCategory).MinimumScale = sht2.Range("A1")
    .Axes(xlCategory).MaximumScale = sht2.Range("DA1")
    .Axes(xlCategory).TickLabels.NumberFormat = "m/d"
    .Axes(xlCategory).MajorUnit = vbMonday 'this doesn't work, shows way too many days
End With

上面的代码使轴像这样(每 2 天):

这就是我的数据:

Answer 1

技巧是将类别轴设置为时间刻度，确定星期一作为起始值，然后在每第七天添加刻度。

Sub Test()
Dim cht As Chart
Dim ax As Axis

Set cht = Worksheets(1).ChartObjects(1).Chart
Set ax = cht.Axes(xlCategory)

With ax
    .CategoryType = xlTimeScale 'For dates
    .MajorUnit = 7 'Only 1 tick every 7 days
    .AxisBetweenCategories = False 'Tick exactly on the date, not between the values.
    .CrossesAt = 42975 'the integer value of the date you wish to be the first one. In this case 2017-08-28.
End With

End Sub