用于生成迷你仪表板的自动化系统

问：

仪表板的数据

它应该是什么样子的

问题陈述

Sub CreateCharts()

    ' Define variables
    Dim ws As Worksheet
    Dim chtDemand As ChartObject
    Dim chtInventory As ChartObject
    Dim chtProdDemand As ChartObject
    Dim lastRow As Long
    
    ' Set the worksheet to work with
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Determine the last row of data
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Create the demand chart
    Set chtDemand = ws.ChartObjects.Add(Left:=400, Width:=400, Top:=0, Height:=250)
    With chtDemand.Chart
        .ChartType = xlLine
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)
        .HasTitle = True
        .ChartTitle.Text = "Historical Demand"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Month"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Demand"
    End With

    
    ' Create the inventory chart
    Set chtInventory = ws.ChartObjects.Add(Left:=850, Width:=400, Top:=0, Height:=250)
    With chtInventory.Chart
        .ChartType = xlLine
        .SetSourceData Source:=ws.Range("A1:C" & lastRow)
        .HasTitle = True
        .ChartTitle.Text = "Historical Inventory Levels"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Month"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Inventory"
        .SeriesCollection(2).AxisGroup = 2 ' Set the third series (Inventory) to use the secondary axis
    End With
    
    ' Create the production/demand chart
    Set chtProdDemand = ws.ChartObjects.Add(Left:=400, Width:=400, Top:=300, Height:=250)
    With chtProdDemand.Chart
        .ChartType = xlLine
        .SetSourceData Source:=ws.Range("A1:D" & lastRow)
        .HasTitle = True
        .ChartTitle.Text = "Historical Production vs. Demand"
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Month"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Units"
        .SeriesCollection(2).AxisGroup = 2 ' Set the third series (Production) to use the secondary axis
    End With

End Sub

我试图用这段代码制作一个仪表板，但它没有用。 此代码假定您的数据位于 A 到 D 列中，第一行包含标题。第一个图表将显示 A 列和 B 列，第二个图表将显示 A、B 和 C 列，第三个图表将显示 A 到 D 列。您可以通过修改“左”、“宽”、“顶”和“高”值来根据需要调整图表尺寸和位置。