图表未调整到较大范围 [英] Chart not Sizing to larger range

问题描述

我记录了一个宏，它导致了下面的代码可以正常工作:

I recorded a macro and it resulted in the following code which worked as desired:

ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "=Graph!$B$1"
ActiveChart.FullSeriesCollection(1).Values = "=Graph!$B$3:$B$170"
ActiveChart.FullSeriesCollection(1).XValues = "=Graph!$A$3:$A$170"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "=Graph!$H$1"
ActiveChart.FullSeriesCollection(2).Values = "=Graph!$H$3:$H$367"
ActiveChart.FullSeriesCollection(2).XValues = "=Graph!$G$3:$G$367"

我围绕该宏构建了代码，如下所示:

I built my code around that macro as such:

Dim LastRow As Long, LastRow1 As Long, LastRow2 As Long
Dim P1Rating As Range
Dim P2Rating As Range
Dim DateRange As Range
Dim ShName As String

With ActiveSheet
' gets the lengths of each row
LastRow1 = .Range("B" & .Rows.Count).End(xlUp).Row
LastRow2 = .Range("G" & .Rows.Count).End(xlUp).Row


' checks to see which one is longer    
    If LastRow1 >= LastRow2 Then
        LastRow = LastRow1
        Else
    LastRow = LastRow2
    End If

    ' assigns the longer count to each range
    Set P1Rating = .Range("B3:B" & LastRow)
    Set date1range = .Range("A3:A" & LastRow)
    Set P2Rating = .Range("H3:H" & LastRow)
    Set date2range = .Range("G3:G" & LastRow)
    ShName = .Name
End With

ActiveSheet.Shapes.AddChart2(227, xlLine, 600, 20).Select
indexofchart = ActiveChart.Parent.Index
ActiveSheet.ChartObjects(indexofchart).Activate
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = P1Name & " vs " & P2Name
ActiveChart.HasLegend = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = P1Name
ActiveChart.FullSeriesCollection(1).Values = P1Rating
ActiveChart.FullSeriesCollection(1).XValues = date1range
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = P2Name
ActiveChart.FullSeriesCollection(2).Values = P2Rating
ActiveChart.FullSeriesCollection(2).XValues = date2range

我正在使用:如果LastRow1> = LastRow2然后LastRow = LastRow1别的LastRow = LastRow2

强制对所有变量使用最大范围，并验证使用了正确的值.如您所见，我只是用变量替换了宏中的硬范围不幸的是，如果date1range是一个较小的范围，则图形将在该点停止(两个系列在图形中均显示为100％，但是系列2中超过该点的所有数据都将丢失)但是在记录中，该系列也只有167个长，并且该图显示了整个364个条目(范围更长).第一个系列在整个图表的1/3处结束如果该范围较大，则将按预期显示.如果没有强制图首先使用更长的范围，该怎么办?

to force using the largest range for all of the variables and verified the proper value is used. As you can see, I simply replaced the hard ranges from the macro with the variables Unfortunately, if date1range is a smaller range, then the graph stops at that point (Both series are shown 100% across the graph, but any data from series 2 past that point is lost) But in the recording, that series is only 167 long as well and the graph displays the whole 364 entries (the longer range). with the first series ending 1/3 of the way across the graph If that range is the larger one, then it displays as expected. Short of forcing the graph to use the longer range first, what can I do?

推荐答案

我找到了一个简单而优雅的解决方案，应该可以很好地工作.我在这里找到了答案: https://peltiertech.com/plot具有不同日期的两个时间系列/

I found a simple and elegant solution that should work wonderfully. I found the answer here:https://peltiertech.com/plot-two-time-series-with-different-dates/

只需将所有日期值放在一栏中！|2015年9月15日|1400 |一个||||||||2015年9月16日|1398 |一个||||||||2015年9月16日|1399 |d ||||||||2015年9月20日|1401 |b ||||||||2015年9月15日|||1400 |一个||||||2015年9月15日|||1398 |c ||||||2015年9月20日|||1406 |c ||||||2015年9月20日|||1407 |d ||||||2015年9月20日|||1408 |b |||||

Simply put all date values into one column! | 9/15/2015 | 1400 | a | | | | | | | | 9/16/2015 | 1398 | a | | | | | | | | 9/16/2015 | 1399 | d | | | | | | | | 9/20/2015 | 1401 | b | | | | | | | | 9/15/2015 | | | 1400 | a | | | | | | 9/15/2015 | | | 1398 | c | | | | | | 9/20/2015 | | | 1406 | c | | | | | | 9/20/2015 | | | 1407 | d | | | | | | 9/20/2015 | | | 1408 | b | | | | |

这篇关于图表未调整到较大范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！