excel-vba
Графики и диаграммы

Создание диаграммы с диапазонами и фиксированное имя

Графики могут быть созданы путем непосредственной работы с объектом Series который определяет данные диаграммы. Чтобы перейти к Series без диаграммы exisitng, вы создаете ChartObject на данном Worksheet а затем получаете объект Chart из него. Поверхность работы с объектом « Series заключается в том, что вы можете установить Values и XValues , обратившись к объектам Range . Эти свойства данных будут правильно определять Series со ссылками на эти диапазоны. Недостатком этого подхода является то, что при настройке Name не обрабатывается одно и то же преобразование; это фиксированное значение. Он не будет корректироваться с базовыми данными в исходном Range . Проверка формулы SERIES и очевидно, что имя исправлено. Это необходимо обработать, создав формулу SERIES напрямую.

Код, используемый для создания диаграммы

Обратите внимание, что этот код содержит объявления дополнительных переменных для Chart и Worksheet . Они могут быть опущены, если они не используются. Они могут быть полезны, однако, если вы изменяете стиль или любые другие свойства диаграммы.

Sub CreateChartWithRangesAndFixedName()

    Dim xData As Range
    Dim yData As Range
    Dim serName As Range
    
    'set the ranges to get the data and y value label
    Set xData = Range("B3:B12")
    Set yData = Range("C3:C12")
    Set serName = Range("C2")
    
    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet
    
    'create a new ChartObject at position (48, 195) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(48, 195, 400, 300)
    
    'get reference to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    'create the new series
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries
    
    ser.Values = yData
    ser.XValues = xData
    ser.Name = serName
    
    ser.ChartType = xlXYScatterLines

End Sub

Исходные данные / диапазоны и итоговая Chart после Chart кода

Обратите внимание, что формула SERIES включает в себя "B" для названия серии вместо ссылки на Range который ее создал.

Создание пустой диаграммы

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

Ключ к ChartObject определяет его местоположение. Синтаксис вызова - ChartObjects.Add(Left, Top, Width, Height) . После создания ChartObject вы можете использовать его объект Chart для фактического изменения диаграммы. ChartObject ведет себя больше как Shape чтобы расположить диаграмму на листе.

Код для создания пустой диаграммы

Sub CreateEmptyChart()
    
    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet
    
    'create a new ChartObject at position (0, 0) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(0, 0, 400, 300)
    
    'get refernce to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    'additional code to modify the empty chart
    '...

End Sub

Результирующая диаграмма

Создание диаграммы путем изменения формулы SERIES

Для полного контроля над новым объектом Chart и Series (особенно для динамического названия Series ) вы должны прибегнуть к модификации формулы SERIES напрямую. Процесс создания объектов Range является простым, и основным препятствием является просто построение строки для формулы SERIES .

Формула SERIES принимает следующий синтаксис:

=SERIES(Name,XValues,Values,Order)

Это содержимое может быть предоставлено в виде ссылок или значений массива для элементов данных. Order представляет собой серию позиций в диаграмме. Обратите внимание, что ссылки на данные не будут работать, если они не полностью соответствуют имени листа. Для примера рабочей формулы щелкните любую существующую серию и проверьте панель формул.

Код для создания диаграммы и настройки данных с использованием формулы SERIES

Обратите внимание, что построение строки для создания формулы SERIES использует .Address(,,,True) . Это гарантирует, что ссылка внешнего диапазона используется так, чтобы был включен полный адрес с именем листа. Вы получите сообщение об ошибке, если имя листа исключено .

Sub CreateChartUsingSeriesFormula()

    Dim xData As Range
    Dim yData As Range
    Dim serName As Range
    
    'set the ranges to get the data and y value label
    Set xData = Range("B3:B12")
    Set yData = Range("C3:C12")
    Set serName = Range("C2")
    
    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet
    
    'create a new ChartObject at position (48, 195) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(48, 195, 400, 300)
    
    'get refernce to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    'create the new series
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries
    
    'set the SERIES formula
    '=SERIES(name, xData, yData, plotOrder)
    
    Dim formulaValue As String
    formulaValue = "=SERIES(" & _
        serName.Address(, , , True) & "," & _
        xData.Address(, , , True) & "," & _
        yData.Address(, , , True) & ",1)"
    
    ser.Formula = formulaValue
    ser.ChartType = xlXYScatterLines

End Sub

Исходные данные и итоговая диаграмма

Обратите внимание, что для этой диаграммы имя серии правильно задано с диапазоном до нужной ячейки. Это означает, что обновления будут распространяться на Chart .

Размещение диаграмм в сетке

Обычная работа с графиками в Excel - это стандартизация размера и компоновки нескольких диаграмм на одном листе. Если сделать это вручную, вы можете удерживать ALT при изменении размера или перемещении диаграммы, чтобы «придерживаться» границ ячеек. Это работает для пары диаграмм, но подход VBA намного проще.

Код для создания сетки

Этот код создаст сетку диаграмм, начинающихся с заданной (верхней, левой) позиции, с определенным количеством столбцов и определенным общим размером диаграммы. Графики будут размещены в том порядке, в котором они были созданы, и обернут вокруг края, чтобы сформировать новую строку.

Sub CreateGridOfCharts()

    Dim int_cols As Integer
    int_cols = 3
    
    Dim cht_width As Double
    cht_width = 250
        
    Dim cht_height As Double
    cht_height = 200
    
    Dim offset_vertical As Double
    offset_vertical = 195
    
    Dim offset_horz As Double
    offset_horz = 40

    Dim sht As Worksheet
    Set sht = ActiveSheet

    Dim count As Integer
    count = 0
    
    'iterate through ChartObjects on current sheet
    Dim cht_obj As ChartObject
    For Each cht_obj In sht.ChartObjects
        
        'use integer division and Mod to get position in grid
        cht_obj.Top = (count \ int_cols) * cht_height + offset_vertical
        cht_obj.Left = (count Mod int_cols) * cht_width + offset_horz
        cht_obj.Width = cht_width
        cht_obj.Height = cht_height

        count = count + 1

    Next cht_obj
End Sub

Результат с несколькими графиками

Эти снимки показывают исходную случайную компоновку диаграмм и результирующую сетку от запуска кода выше.

До

После