Объекты Excel

Как получить объект Chart

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

• Диаграмма встроена в рабочий лист. На рабочем листе может находиться несколько встроенных диаграмм. Коллекция ChartObjects задает совокупность объектов - контейнеров, содержащих эти диаграммы. Зная индекс или имя диаграммы, можно получить доступ к нужному элементу этой коллекции, а свойство Chart позволяет получить объект, задающий диаграмму. Так что вызов

  ThisWorkbook.Worksheet(3).ChartObjects(1).Chart

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

  ThisWorkbook.Worksheets(3).ChartObjects(1).Activate
  ActiveChart.ChartArea.Select

  Так что заметьте, в этом случае приходится часто использовать оба объекта - ChartObjects и Chart для решения возникающих задач.
• Диаграмма расположена на отдельном листе диаграммы. Специальные листы диаграмм рабочей книги составляют, как уже говорилось, коллекцию Charts. Хочу обратить внимание на два момента. Хотя коллекция представляет собой совокупность специальных листов рабочей книги, элементы этой коллекции являются не столько листами, сколько объектами Chart, задающими диаграммы. Эта ситуация не является типичной для коллекций. Связано это с тем, что на таком листе располагается только один объект Chart. Именно поэтому лист отождествляется с диаграммой. Вызов, который я сделал в отладочном окне Immediate:

  ThisWorkbook.Charts(1).Select
  ?ActiveChart.ChartTitle.Text

  выделил в текущей рабочей книге первый лист с диаграммой, а, следовательно, сделал активной и саму диаграмму. После чего стало возможным получить заголовок диаграммы. Заметьте, в данной ситуации метод Select работает без проблем. Для получения диаграммы я использовал коллекцию Charts, но заметьте, эта коллекция является частью коллекции Sheets, поэтому тот же результат можно получить, используя эту коллекцию:

  ThisWorkbook.Sheets(2).Select
  ?ActiveChart.ChartTitle.Text
• Активная диаграмма. Добраться до объекта Chart, задающего активную диаграмму, можно с использованием свойства ActiveChart, как показано в только что приведенных примерах. Еще раз хочу обратить внимание на то, что активизация листа диаграммы приводит и к активизации самой диаграммы. При работе с такими диаграммами можно использовать с тем же успехом и свойство ActiveSheet. Что же касается встроенных диаграмм, то там необходимо предварительно активизировать соответствующий контейнер.
• Несколько диаграмм на одном листе диаграммы. Excel позволяет указать один и тот же лист диаграммы, как место расположения нескольких диаграмм. В этом случае новая диаграмма накладывается на область, отведенную первой диаграмме. Руками можно работать с несколькими диаграммами на таком листе, например, изменяя их параметры. Но программная работа возможна только с одним объектом, определяющим первую диаграмму. Более того, если, используя MacroRecorder, записать макрос, повторяющий работу руками с несколькими диаграммами одного листа, то этот макрос не будет корректно выполняться при его запуске. Суммируя это, рекомендую в своей работе придерживаться стратегии - одна диаграмма на листе диаграмм. Это обеспечит корректную программную работу с такими листами.

Источники данных и структура объекта Chart

Диаграмма Excel, предоставляя пользователям широкий спектр возможностей по отображению данных, не может быть просто устроена, - она имеет достаточно сложную внутреннюю структуру. Соответственно, такую же сложную структуру имеет и объект Chart. Прежде чем переходить к деталям, давайте рассмотрим общую картину. В диаграмме можно выделить:

• общую область, занятую диаграммой. Понятно, что можно менять размеры, внешний вид и другие характеристики общей области.
• Область данных, в которой и строится собственно диаграмма.
• Источник данных и данные этого источника. Эти объекты играют центральную роль, и о них я расскажу подробнее.
• Заголовок диаграммы и другие подписи.
• Оси.
• Легенду.

Всем этим понятиям соответствуют целый ряд объектов, их свойств и методов. Рассмотрим более подробно источники данных и организацию данных при построении диаграммы. Одним из самых простых и наиболее употребительных источников данных является таблица Excel. Вот как выглядит таблица, используемая для построения диаграмм, показанных на рис. 3.11-3.14:

Рис. 3.15. Таблица, являющаяся источником данных для построения диаграмм

Взгляните еще на одну таблицу Excel и диаграмму, для которой данная таблица выступает источником данных:

Рис. 3.16. Диаграмма с источником данных

Для того чтобы можно было построить диаграмму, необходимо иметь, по крайней мере, один ряд данных. Напомню специальные термины, применяемые при построении диаграмм. Ось X называются осью категорий и значения, откладываемые на этой оси, называются категориями. Значения отображаемых в диаграмме функций и гистограмм составляют ряд данных. Ряд данных, представляющий последовательность числовых значений, является одним из центральных понятий в построении диаграмм. Этому понятию соответствует объект Series. Поскольку при построении диаграммы, как правило, используется несколько рядов данных, то важную роль играет понятие совокупности рядов данных, которой с объектной точки зрения соответствует коллекция - объект SeriesCollection. Замечу сразу, что все ряды в одной коллекции должны иметь одну и ту же размерность. Excel позволяет строить смешанные диаграммы, где на одной диаграмме одновременно строятся несколько различного типа диаграмм, например, графики и гистограммы. В такой смешанной диаграмме каждому типу соответствует своя группа данных. Группы одной диаграммы составляют коллекцию ChartGroups, элементы которой принадлежат классу ChartGroup. Итак, каждое значение, отображаемое на диаграмме, является членом некоторого ряда данных, ряды объединятся в коллекции. Коллекция рядов связывается с группой, каждая из которых позволяет отобразить диаграмму определенного вида. Группы объединяются в коллекцию групп. В общем случае, каждый ряд данных может иметь свой источник - свое положение, например диапазон ячеек некоторой рабочей книги Excel.

Чаще всего, хотя и не всегда, стараются делать так, чтобы у диаграммы был один источник данных, в роли которого выступает таблица Excel. Каждая из таблиц, показанных на двух предыдущих рисунках, служит единственным источником данных для соответствующих диаграмм. Поскольку построенные диаграммы не являются смешанными, то все данные таблицы составляют одну группу - одну совокупность рядов данных. Заметьте, удобно, как это сделано и в наших примерах, чтобы ряды в таблице были именованными. Если нужно задать значения категорий, то они должны составлять первый ряд таблицы, который должен быть неименованным, по крайней мере, в тех случаях, когда он состоит из числовых значений. Это позволяет отличить ряд категорий от обычных рядов данных. Значения категорий можно не задавать, тогда по умолчанию в этой роли выступает начальный отрезок натурального ряда - 1, 2, 3 и так далее. Заметьте также, в качестве рядов данных можно рассматривать как строки, так и столбцы таблицы. Можно построить диаграмму в предположении, что строки таблицы являются рядами данных, а можно полагать, что таковую роль играют столбцы. В наших примерах в первой таблице, отражающей динамику продаж, в качестве рядов использовались именованные столбцы таблицы; фамилии дилеров, осуществляющих продажу, выступают в качестве имен столбцов источника данных. Во второй таблице именованные строки задают ряды данных. Обе таблицы в качестве первого ряда задают значения, располагаемые на оси категорий.

Тип диаграммы влияет на ее структуру и предъявляет определенные требования к рядам данных. Так, для построения круговой диаграммы всегда используется только один ряд данных, для трехмерных диаграмм необходимо не менее трех рядов с учетом значений оси категорий. При построении двумерной диаграммы, отображающей графики, показанные на рис. 3.16, используются три ряда данных - один из них задает значения координат на оси X, два других - соответствующий значения двух функций.

Хотя детальное обсуждение объектной модели Chart еще предстоит, хочу, предваряя это обсуждение привести пример программного построения диаграммы, где в качестве нескольких источников данных будут использоваться различные диапазоны ячеек рабочего листа Excel, а не единая таблица. По ходу дела в этом примере встретятся многие объекты, упоминавшиеся в этом обзоре, - Series, SeriesCollection и другие.

Public Sub CreatingChart()
'Эта процедура строит диаграмму
'Добавить новую диаграмму на лист
 Dim MySh As Worksheet, MyChO As ChartObject, MyCh As Chart
 Dim Gr As ChartGroup, SC As SeriesCollection, Sr As Series
 Dim CG As ChartGroups
	Set MySh = ThisWorkbook.Worksheets(4)
	Set MyChO = MySh.ChartObjects.Add(50, 520, 320, 150)
	Set MyCh = MyChO.Chart
	
	'Добавить ряды данных
	Set SC = MyCh.SeriesCollection
	SC.Add Source:=MySh.Range("B26:F27"), RowCol:=xlRows
	SC.Add Source:=MySh.Range("B29:F29"), RowCol:=xlRows
	'Установить тип диаграммы для рядов данных
	Set Sr = MyCh.SeriesCollection(1)
	Sr.ChartType = xlColumnClustered
	Set Sr = MyCh.SeriesCollection(2)
	Sr.ChartType = xlColumnClustered
	Set Sr = MyCh.SeriesCollection(3)
	Sr.ChartType = xlLineMarkers
	
	'Добавить свою ось для графика
	Sr.AxisGroup = xlSecondary
		
	 'Изменить ось категорий
	 MyCh.Axes(xlCategory).CategoryNames = _
	Range("C31:F31")
	 'Array("янв.", "февр.", "март", "апр.")
	 
	 'Работа с группами
	 Set CG = MyCh.ChartGroups
	 Set Gr = CG(2)
	 Debug.Print CG.Count
	 Debug.Print ; Gr.SeriesCollection(1).Name
	
	'Изменить внешний вид - цвет графика
	With Sr.Border
		.ColorIndex = 30
		.Weight = xlThin
		.LineStyle = xlContinuous
	End With
	Sr.MarkerBackgroundColorIndex = xlColorIndexNone
	Sr.MarkerForegroundColorIndex = 30
End Sub

Прокомментируем работу этой программы:

• На первом шаге ее работы создается сам объект Chart, представляющий диаграмму, встроенную в рабочий лист. Как обычно, для создания этого объекта вначале создается контейнер.
• Теперь задаются ряды данных, и формируется коллекция SeriesCollection. Ряды создаются методом Add этой коллекции. Обратите внимание, для создания трех рядов данных используются два различных диапазона ячеек рабочей книги.
• Для каждого ряда задается тип диаграммы, отображающей этот ряд. Для первых двух рядов, задающих планируемые и фактические продажи продукции, выбрано представление в виде гистограммы, для третьего ряда - график, задающий изменение цен.
• Стандартные значения оси категорий меняются на названия месяцев, взятые из некоторого диапазона ячеек. Закомментированная строка показывает, что для этой цели может использоваться и массив строковых значений. Здесь также демонстрируется работа с объектом Axis - элементом коллекции Axes, - задающим ось категорий.
• Следующий шаг демонстрирует работу с группами. Поскольку создана смешанная диаграмма с двумя разными типами, то и групп будет две. Отладочная печать показывает, что групп действительно две, и ряд, связанный со второй группой имеет имя "Цена".
• На заключительном шаге работы изменяется цвет графика и маркеров, отмечающих точки на графике.
• В заключение осталось взглянуть, как выглядит построенная диаграмма.

увеличить изображение
Рис. 3.17. Программно построенная диаграмма с несколькими источниками данных

В заключение этого параграфа следует сказать, что сегодня Excel интенсивно используется для проведения анализа данных, хранящихся в различных базах и хранилищах данных, в том числе для анализа OLAP - кубов, представляющих многомерные источники данных. Как правило, в этом случае для анализа таких данных в Excel строится сводная таблица. Источником данных для нее являются внешние источники - хранилища данных, а она сама является источником данных для построения сводной диаграммы, интерактивно изменяющейся в процессе работы со сводной таблицей. О сводных таблицах и сводных диаграммах - этом мощном инструменте анализа данных более подробный разговор пойдет в других главах этой книги.

После этих общего обзора перейдем к более детальному рассмотрению диаграмм с объектной точки зрения.