Слишком длинная ошибка формулы ряда в диаграммах VBA

Question

У меня есть следующие данные с 23 отделами. Ячейки отдела первого ряда объединены.

Мои данные аналогичны данным на картинке в этой ссылке. У меня просто есть [23 числа 'pcts' и 'actions'.

• Я пытаюсь сделать две диаграммы, используя VBA; один для фактического и один для% от общего по следующему коду.
• Я получаю два графика, пока в цикле For столбца i значения не превышают 35 и 36 соответственно вместо 29 и 30 соответственно.
• Когда я превышаю значения 35 и 36 соответственно, это показывает ошибку "Слишком длинная формула серии", и графики не отображаются.

Почему это происходит?

Sub TwoCharts11()

  Dim rChart1 As Range
  Dim rChart2 As Range
  Dim iColumn As Long
  Dim cht1 As Chart
  Dim cht2 As Chart

  Const StrtRow As Long = 2
  Const EndRow As Long = 6

  With ActiveSheet

   Set rChart1 = .Range(.Cells(StrtRow, "B"), .Cells(EndRow, "B"))
   Set rChart2 = .Range(.Cells(StrtRow, "B"), .Cells(EndRow, "B"))

   For iColumn = 3 To 29 Step 2
       Set rChart1 = Union(rChart1, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))
   Next

   For iColumn = 4 To 30 Step 2
       Set rChart2 = Union(rChart2, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))
   Next

   Set cht1 = .Shapes.AddChart.Chart
   Set cht2 = .Shapes.AddChart.Chart

   With cht1
    .Parent.Left = .Parent.Left - .Parent.Width / 2
    .ChartType = xlColumnClustered
    .SetSourceData rChart1
   End With

   With cht2
    .Parent.Left = .Parent.Left + .Parent.Width / 2
    .ChartType = xlColumnClustered
    .SetSourceData rChart2
   End With

  End With

End Sub

Я также разместил свой вопрос по следующим ссылкам.

Answer 1

Формула ряда диаграммы (выберите диаграмму и посмотрите на панель формул) выглядит следующим образом:

=SERIES(,Sheet1!$B$5:$F$5,Sheet1!$G$5:$K$5,1)

что значит

=SERIES({series name, optional},{x values},{y values},{plot order})

Когда вы объединяете несмежные диапазоны в значения X и Y, формула ряда становится большой:

=SERIES(,(Sheet1!$B$2,Sheet1!$D$2,Sheet1!$F$2,Sheet1!$H$2,Sheet1!$J$2),(Sheet1!$C$2,Sheet1!$E$2,Sheet1!$G$2,Sheet1!$I$2,Sheet1!$K$2),1)

Каждый из разделов формулы ряда имеет строгое ограничение на число символов, не превышающее 256 символов, и в этот момент произойдет сбой с сообщением об ошибке, которое вы видели.

@BonCodigo предлагает переместить данные из несмежного расположения (как в первой цветной строке на снимке экрана; фиолетовый = значения X, синий = значения Y) в смежное расположение (как во втором). Графики выглядят одинаково, но формулы во втором намного проще и не приведут к ошибке.