VBA添加系列重叠数据

Question

Excel 2013，赢10

我想要在一个图表上显示两个数据透视表。 （因为它的行太多，我不能合并的源数据。）所以我决定将系列添加到图表中VBA：

Sub createProductionChart() 
    Sheets("ProductionChart").Select 
    ActiveSheet.ChartObjects("Chart 2").Activate 
    ActiveChart.ChartArea.Select 
    For Each s In ActiveChart.SeriesCollection 
     s.Delete 
    Next s 

    ActiveChart.ChartType = xlAreaStacked 

    'Add history 
    For i = 2 To 41 
     If IsEmpty(Worksheets("History Pivot").Cells(6, i)) Then 
      Exit For 
     End If 


     Set tSeries = ActiveChart.SeriesCollection.NewSeries 
     tSeries.XValues = Worksheets("History Pivot").Range("$A$7:$A$300") 
     tSeries.Values = Worksheets("History Pivot").Range(Cells(7, i).Address, Cells(300, i).Address) 
     tSeries.Name = Worksheets("History Pivot").Cells(6, i).Value 
    Next i 
    ' add forecast: 
    For j = 2 To 200 
     If IsEmpty(Worksheets("Forecast Pivot").Cells(7, j)) Then 
      Exit For 
     End If 
     Set tSeries = ActiveChart.SeriesCollection.NewSeries 
     tSeries.XValues = Worksheets("Forecast Pivot").Range("$A$8:$A$300") 
     tSeries.Values = Worksheets("Forecast Pivot").Range(Cells(8, j).Address, Cells(300, j).Address) 
     If IsEmpty(Worksheets("Forecast Pivot").Cells(6, j)) Then 
      yr = Worksheets("Forecast Pivot").Cells(6, j - 1).Value 
     Else 
      yr = Worksheets("Forecast Pivot").Cells(6, j).Value 
     End If 
     tSeries.Name = yr & " " & Worksheets("Forecast Pivot").Cells(7, j).Value 
    Next j 



End Sub 

源数据系列是在X不相交的（他们是日期和这两个图表代表历史和预测数据）。
XL只是成堆他们在彼此的顶部：

的“预测”数据应该在历史正确的。

按照要求源数据的样本 - 他们是巨大的，因此，只有左上角所示： 历史：

预测： 任何想法？

Answer 1

原因的预测是坐在历史的顶部是因为堆积面积图基于数据的顺序（即使你的范围内的值是在未来）上始终对准它的数据。

您需要将历史记录中的所有日期也添加到您的预测XValues中（并且它就像是值为0）。

我不确定我是否清楚，请告知我是否需要更多说明。

无论如何，尝试下面的代码，它与测试中，我对我的工作簿和工作表做的工作：即建立这个图表都透视表数据的

Sub createProductionChart() 

Dim Rng_Hisotry   As Range 
Dim Rng_Forecast  As Range 
Dim Rng_Combined  As Range 

Sheets("ProductionChart").Select 
ActiveSheet.ChartObjects("Chart 2").Activate 
ActiveChart.ChartArea.Select 

For Each s In ActiveChart.SeriesCollection 
    s.Delete 
Next s 

ActiveChart.ChartType = xlAreaStacked 

Set Rng_Hisotry = Worksheets("Forecast Pivot").Range("$A$8:$A$300") 
Set Rng_Forecast = Worksheets("Forecast Pivot").Range("$A$8:$A$300") 

' add History with Forecast Range >> so the forecast data will start 
' from the correct position on the chart 
Set Rng_Combined = Range(Rng_Hisotry.Address & ":" & Rng_Forecast.Address) 

' Add History 
For i = 2 To 41 
    If IsEmpty(Worksheets("History Pivot").Cells(6, i)) Then 
     Exit For 
    End If 

    Set tSeries = ActiveChart.SeriesCollection.NewSeries 
    tSeries.XValues = Rng_Hisotry 
    tSeries.Values = Worksheets("History Pivot").Range(Cells(7, i).Address, Cells(300, i).Address) 
    tSeries.Name = Worksheets("History Pivot").Cells(6, i).Value 
Next i 

' Add Forecast 
For j = 2 To 200 
    If IsEmpty(Worksheets("Forecast Pivot").Cells(7, j)) Then 
     Exit For 
    End If 

    Set tSeries = ActiveChart.SeriesCollection.NewSeries 

    tSeries.XValues = Rng_Combined 
    tSeries.Values = Worksheets("Forecast Pivot").Range(Cells(8, j).Address, Cells(300, j).Address) 

    If IsEmpty(Worksheets("Forecast Pivot").Cells(6, j)) Then 
     yr = Worksheets("Forecast Pivot").Cells(6, j - 1).Value 
    Else 
     yr = Worksheets("Forecast Pivot").Cells(6, j).Value 
    End If 
    tSeries.Name = yr & " " & Worksheets("Forecast Pivot").Cells(7, j).Value 
Next j 

End Sub