Диаграммы. Язык визуального программирования приложений Visual Basic for Application

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

В Excel различаются два вида диаграмм: внедренная на рабочий лист и созданная на специальном листе для диаграмм. Итак, семейство sheets (листы) включает в себя семейства: worksheets (рабочие листы) и charts (листы диаграмм). Объектами семейства charts являются листы диаграмм, но это семейство не включает в себя диаграммы, непосредственно встроенные в рабочие листы. Такие диаграммы принадлежат семейству chartobjects. Таким образом, объект ChartObject встроен В Объект Worksheet, a Chart — В workbook. Иерархия этих объектов показана на рис. 5.1. Свойства и методы этих объектов в основном аналогичны.

Перечислим основные свойства объекта chart и семейства charts.


	ChartArea	Возвращает объект ChartArea — область на листе, отведенная под диаграмму. В следующем примере устанавливается цвет элементов объекта ChartArea. With Charts { "Диаграмма") .ChartArea . Interior . Colorlndex = 3 . Border . Colorlndex = 5 End With
	PlotArea	Возвращает объект PlotArea — часть области ChartArea, отведенная непосредственно под диаграмму. В следующем примере устанавливается Цвет объекта PlotArea: Charts (1) . PlotArea . Interior . Colorlndex = 8
	ChartTitle	Возвращает объект ChartTitle, являющийся заголовком диаграммы. В следующем примере задается заголовок диаграммы отчет : With Charts ( "Отчет") .HasTitle = True . ChartTitle . Text = "Продажи за май" End With
	ChartType	Возвращает тип диаграммы: xlArea, xlBar,xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xlSDBar, xl3DColumn, xl3DLine, xl3DPie, xlSDSurface, xlDoughnut
	Legend	Возвращает объект Legend. Используется для изменения легенды. Например: ChartObjects (1).Chart. Legend. Font .Bold = True
	Rotation	Возвращает угол поворота 3-мерной диаграммы вокруг z-оси. Допустимыми являются значения от 0° до 360°. По умолчанию принимается угол, равный 20°
	Elevation	Возвращает угол возвышения для обзора трехмерной диаграммы. Допустимыми являются значения от —90° до 90°. По умолчанию принимается угол, равный 15°


	PlotBy	Допустимые значения: xlCoiumns (столбцы используются как ряды данных при построении диаграммы), xlRows (строки используются как ряды данных при построении диаграммы)
	PlotVisibleOnly	Допустимые значения: True (диаграмма строится только по видимым ячейкам), False (в противном случае)
	Floor, Corners, Walls	Возвращают соответственно объекты Floor, Corners, walls, используемые при построении трехмерных диаграмм. При отображении таких диаграмм для создания иллюзии трехмерности диаграмма отображается на фоне открытого куба, основание которого задается объектом Floor, углы — Corners, а две боковые стенки — wails. В следующем примере устанавливаются цвета объектов Floor и walls и выбирается объект corners: With Charts (1) .. Floor . Interior . Colorlndex = 5 .Walls. Border. Colorlndex = 3 /Corners . Select End With
	AutoScaling	Допустимые значения: True (трехмерная диаграмма автоматически масштабируется так, чтобы по размеру совпадать с двумерной), False (диаграмма не масштабируется)
	DepthPercent, Height Per cent	Применяется только к трехмерным диаграммам. Возвращает глубину и высоту диаграммы в процентах к ее ширине
	DisplayBlasnksAs	Допустимые значения: xlNotPiotted (при построении диаграммы пустые ячейки игнорируются) xlInterpoiated (значения в пустых ячейках интерполируются) xlZero (значения в пустых ячейках считаются равными нулю)
	Protect Contents, ProtectData, ProtectDrawingObjects, Protect Formatting, ProtectGoalSeek, Protect ionMode, ProtectionSelection,	Возвращает True, если указанный элемент диаграммы имеет защиту, и False — в противном случае
	HasAxis, HasDAtaTable, HasLegend, HasTitle	Возвращает True, если указанный элемент имеется на диаграмме, и False — в противном случае

В данном разделе перечислены основные методы объекта chart.

Метод chartwizard применяется для построения и изменения существующей диаграммы. С помощью метода chartwizard, в отличие от команды Мастер диаграмм (ChartWizard), используемой для построения диаграммы вручную, нельзя задать все возможные свойства диаграммы. Он позволяет выполнить основную работу, а детали строятся с привлечением других свойств и методов объекта chart.


	Source	Диапазон, по которому строится диаграмма
	Gallery	Тип диаграммы. Допустимые значения: xiArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut или xl Default Auto Format
	Format	Формат для данного типа диаграммы. Каждый тип имеет до десяти форматов
	PlotBy	Допустимые значения: XIROWS (строки) и xlColumns (столбцы)
	CategoryLabels	Указывает число строк (столбцов) с метками категорий (т. е. данных, отводимых под ось абсцисс)
	SeriesLabels	Указывает число строк (столбцов) с метками рядов (т. е. данных, отводимых под заголовки)
	HasLegend	Допустимые значения: True (отображать легенду) и False (не отображать легенду)


	Title	Название диаграммы
	CategoryTitle	Название оси абцисс
	ValueTitle	Название оси ординат
	ExtraTitle	Название z-оси для трехмерных диаграмм

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


	Axes	Возвращает семейство Axes, предназначенное для установки различных свойств осей. Для возвращения уникальной оси необходимо использовать следующий синтаксис метода: Axes (Type, Group) Аргументы: Tуре — определяет тип оси. Допустимые значения: xlCategory, xlSeries И xlValue Group — допустимые значения: xi Primary и xlSecondary Пример: With Charts (1) .Axes (xlCategory) .HasTitle = True . AxisTitle. Caption = "Доход" End With
	SeriesCol lection	Возвращает семейство объектов series или объект этого семейства. Синтаксис (для одного объекта из семейства): SeriesCollection ( Index) Синтаксис (для семейства объектов): SeriesCollection Index — имя или номер элемента Пример: Charts (1) . SeriesCollection (1) .HasDataLabels = True
	SetSourceData	Устанавливает источник данных диаграммы. Синтаксис: SetSourceData (Source, PlotBy) source — диапазон, на основе которого строится диаграмма PlotBy — допустимые значения: xlColumns (данные расположены по столбцам) и XIROWS (данные расположены по строкам) Пример: ActiveChart . SetSourceData Source :=Sheets (1) .Range ("A1 : F1") , PlotBy:=xlRows


	Export	Экспортирует диаграмму в графический формат. Синтаксис: Export (FileName, FilterName, Interactive) FileName — имя файла, в который будет записана диаграмма в графическом формате FilterName — имя графического фильтра в том виде, как он записан в графическом формате Iinteractive — допустимые значения: True (для того чтобы показать диалоговое окно в процессе фильтрации) и False (в противном случае) Пример экспорта диаграммы в GIF-файл: ActiveChart . Export FileName : ="График . gif " , FilterName := "GIF"
	Location	Передвигает диаграмму на новое место. Синтаксис: Location (Where, Name) where — указывает, будет ли диаграмма располагаться на новом листе диаграмм (xlLocationAsNewSheet), внедряться как объект на рабочий лист (xiLocationAsobject) или ее местоположение будет определяться автоматически (xlLocationAutomatic) Name — имя листа диаграммы, если аргумент where принимает значение xlLocationAsNewSheet, либо имя рабочего листа, если аргумент where принимает значение xlLocationAsobject

Приведем несколько наиболее часто используемых методов семейства ChartObjects.


	Add	Создает новую диаграмму. Синтаксис: Add (Left, Top, Width, Height) Left, Top — координаты левого верхнего угла диаграммы width, Height — ширина и высота диаграммы
	Delete	Удаляет элемент семейства


	CopyPicture	Копирует диаграмму в буфер обмена как рисунок. Синтаксис: CopyPicture (Appearance, Format, Size) Appearance — устанавливает, каким образом диаграмма копируется. Допустимые значения: xlScreen (диаграмма копируется в том виде, в котором она отображается на экране) и xlPrinter (диаграмма копируется так, как она выглядит после вывода на печать) Format — формат рисунка. Допустимые значения: xlPicture И xlBitmap
	BringToFront	Отображает диаграмму на переднем плане
	SendToBack	Отображает диаграмму на заднем плане
	Select	Выбирает диаграмму

Приведем соответствие между построением графика вручную и аргументами метода ChartWizard на примере построения диаграммы из рис. 5.2.

Рис. 5.4. Диалоговое окно первого шага работы мастера диаграмм


	Шаг 1	Нажмите кнопку Мастер диаграмм (Chart Wizard) чтобы отобразить на экране диалоговое окно первого шага мастера диаграмм (рис. 5.4). Выберите тип и вид диаграммы на первом шаге работы мастера диаграмм. Выбранный тип диаграммы вводится в аргумент Gallery, а тип — в аргумент Format. В данном случае аргументу Gallery присваивается значение xlLine, а аргументу Format — 1. Нажмем кнопку Далее > (Next >) для перехода к следующему шагу построения диаграммы.

Шаг 2

На вкладке Диапазон данных (Data Range) (рис. 5.5) второго шага работы мастера диаграмм при помощи переключателей Ряды в (Data Series In) укажите, как расположены ряды данных (в столбцах или строках), на основе которых строится диаграмма. 'Выбранное расположение вводится в аргумент piotBy. В данном случае аргументу PiotBy присваивается значение xicoiumns (в столбцах). В поле Диапазон (Data Range) вводится диапазон, на основе которого строится диаграмма. В данном случае =лист1 ! $A$1 : $B$11 .
На, вкладке Ряд (Series) (рис. 5.6) второго шага работы мастера диаграмм исключите РЯД! из списка Ряд (Series), нажав кнопку Удалить (Delete), т. к. первый ряд из диапазона данных будет отведен под метки оси абсцисс. Переменной categoryLabeis присваивается число столбцов (строк) диапазона данных, отводимых под значения, которые будут отображены на оси абсцисс. В данном случае аргументу CategoryLabeis присваивается значение 1, т. к. только первый столбец (A1:A11) отводится под значения, отображаемые на оси абсцисс. Кроме того, тот же диапазон данных =Лист1 ! $А$1 : $А$11 вводится в поле Подписи оси X (Category (X) Axis Labels). П В поле Значения (Values) приводится ссылка на диапазон, содержащий значения ряда данных. В данном случае =Лист1!$В$1:$В$11. В Переменную SeriesLabels водится число строк (столбцов) данных, отводимых под название различных ветвей диаграмм. Обычно эта переменная используется совместно с легендой для того, чтобы различать ветви диаграмм, построенные в одной и той же системе координат не только по цвету или внешнему виду, но и по этим названиям. В данном случае аргументу SeriesLabels присваивается значение 0, т. к. строится только одна диаграмма.

Нажмите кнопку Далее > (Next >) для перехода к следующему шагу построения диаграммы.

Рис. 5.5. Вкладка Диапазон данных диалогового окна второго шага работы мастера диаграмм

Рис. 5.6. Вкладка Ряд диалогового окна второго шага работы мастера диаграмм


	ШагЗ	На вкладке Заголовки (Titles) (рис. 5.7) третьего шага работы мастера диаграмм заполняются поля Название диаграммы (Chart Title), Ось X (категорий) (Category (X) Axis) и Ось Y(значений) (Value (Y) Axis) для отображения названий диаграммы и осей. Эти названия вводятся в аргументы Title, CategoryTitie и vaiueTitie соответственно. В данном случае этим аргументам присвоены значения график, Аргумент и Функция. На вкладке Легенда (Legend) третьего шага работы мастера диаграмм с помощью флажка Добавить легенду (Add Legend) устанавливается, надо ли отображать легенду. Результат установки вводится в аргумент Has Legend. В данном случае сбросим флажок Добавить легенду (Add Legend), после чего аргументу HasLegend присваивается значение False, т. к. легенда не отображается. Нажмите кнопку Далее >(Next >) для перехода к следующему шагу построения диаграммы.

Рис. 5.7.Вкладка Заголовки диалогового окна третьего шага работы мастера диаграмм


	ШагЗ	На четвертом шаге работы мастера диаграмм выбирается, строится ли диаграмма как внедренный объект в рабочий лист, или на отдельном листе диаграмм. Для создания диаграммы на отдельном листе выберите переключатель отдельном (As New Sheet), а для создания внедренной диаграммы — переключатель имеющемся (As Object In) (рис. 5.8). Выберем переключатель имеющемся (As Object In). Результат выбора не отображается в методе ChartWizard. Нажатие кнопки Готово (Finish) завершает процесс создания диаграммы. Подытожим произведенные присвоения аргументам метода ChartWizard. ' Добавляется диаграмма в семейство диаграмм на активном ' рабочем листе ' ActiveSheet.ChartObjects.Add(195, 30, 200, 190). Select ' ' Производится построение диаграммы методом ChartWizard ActiveChart. ChartWizard Source : =Range ("A1:B11") , Gallery:= xlLine, Format :=1, PlotBy:= xlColumns, CategoryLabels : =1, SeriesLabels :=0, HasLegend: =False, Title := "График", CategoryTitle : = "Аргумент", ValueTitle : ="Функция" Ту же самую диаграмму, не используя метод ChartWizard, можно построить с помощью следующей последовательности инструкций: Charts . Add ActiveChart. ChartType = xlLine ActiveChart . SetSourceData Source :=Sheets('ЛИCT3"). Range ( "Bl : Bll" ), PlotBy : =xlColumns ActiveChart. SeriesCollection (1) .XValues = "=Лист3 !R1C1:R11C1" ActiveChart .Location Where:=xlLocationAsObject, Nаmе:="ЛистЗ" With ActiveChart .HasTitle = True . ChartTitle. Characters. Text = "График" .Axes (xlCategory, xlPrimary) .HasTitle = True .Axes (xlCategory, xlPrimary) .AxisTitle. Characters. Text = "Аргумент" .Axes (xlValue, xlPrimary) .HasTitle = True .Axes (xlValue, xlPrimary) .AxisTitle. Characters. Text = "Функция" .HasLegend = False End With

Рис. 5.8. Диалоговое окно четвертого шага работы мастера диаграмм

С помощью диаграммы легко можно анализировать тренды и делать прогнозы. Вручную линия тренда на диаграмме добавляется с помощью команды Диаграмма, Добавить линию тренда (Chart, Add Trendline).

Все линии тренда, соответствующие данному ряду данных, образуют семейство Trendlines, элементами которого являются объекты Trendline (линия тренда).

Основными методами семейства Trendiines являются метод Add, добавляющий новый элемент в семейство, и метод item, возвращающий конкретный элемент семейства.

Приведем описание метода Add семейства Trendiines.

Add

Добавляет новый элемент в семейство Trendlines.

Синтаксис:

Add (Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

Tуре — устанавливает тип линии тренда. Допустимые значения: xlLinear (линейная), xlLogarithmic (логарифмическая), xiExponential (экспоненциальная), xlPolynomial (полиномиальная) , xlMovingAvg (скользящее среднее) и xlPower (степенная)
Oorder — устанавливает порядок линии тренда. Допустимые значения: целые числа в интервале от 2 до 6. Используется, если аргумент туре принимает значение xlPolynomial
Period — период тренда. Допустимые значения: целое не меньше 1, но меньше числа точек, по которым строится тренд.

Используется, если аргумент туре принимает значение xlMovingAvg.

Forward — прогнозируемое число точек на будущее (вперед), для предсказания значений в соответствии с трендом
Backward — прогнозируемое число точек в прошлом (назад), для предсказания значений в соответствии с трендом
intercept — пересечение с осью ординат
DisplayEquation — допустимые значения: True (уравнение тренда отображается на диаграмме) и False (в противном случае)
DisplayRSquared допустимые значения: True (отображается квадрат коэффициента корреляции) и False (в противном случае)
Name — строка, задающая имя линии тренда

Из свойств семейства Trendlines отметим только свойство count, возвращающее число элементов этого семейства.

Приведем соответствие между построением линии тренда вручную и аргументами метода Add семейства Trendiines.


	Шаг 1	Активизируйте диаграмму, где надо построить линию тренда. Выберите команду Диаграмма, Добавить линию тренда (Chart, Add Trendline) (рис. 5.9).
	Шаг 2	На вкладке Тип (Туре) появившегося диалогового окна Линия тренда (Trendline) (рис. 5.10): В списке Построен на ряде (Based on Series) выбирается ряд данных, по которому строится линия тренда. В данном случае ряд данных только один. В группе Построение линии тренда (аппроксимация и сглаживание) (Trend/Regression Type) выбирается один из шести видов линии тренда. Выберите Линейная (Linear).
	ШагЗ	На вкладке Параметры (Options) диалогового окна Линия тренда (Trendline) (рис. 5.11) устанавливаются параметры линии тренда: В группе Лазвание аппроксимирующей (сглаженной) кривой (Trendline Name) переключатель автоматическое (Automatic) устанавливает имя линии тренда такое же, как и у ряда данных, а переключатель другое (Custom) устанавливает имя, введенное в соответствующем поле. В данном случае выберем переключатель автоматическое (Automatic). В группе Прогноз (Forecast) счетчики вперед на (Forward) и назад на (Backward) определяют, на сколько периодов вперед или назад надо прогнозировать данные при помощи линии тренда. В данном случае не будем заниматься прогнозом, и поэтому оба эти счетчика установим равными 0. Флажок пересечение кривой с осью Y в точке (Set Intercept) позволяет установить конкретную точку, в которой тренд должен пересекать ось Y. В данном случае такого значения устанавливать не будем и поэтому сбросим этот флажок. Флажок показывать уравнение на диаграмме (Display Equation on Chart) определяет, надо ли отображать уравнение линии тренда на диаграмме. В данном случае будем выводить уравнение, и, следовательно, установим этот флажок. Флажок поместить на диаграмму величину достоверности аппроксимации (R^2) (Display R-squared) определяет, надо ли отображать квадрат коэффициента корреляции на диаграмме. Установим этот флажок, тем самым обеспечив вывод квадрата коэффициента корреляции на диаграмму.

Рис. 5.9. Построение линии тренда для объема продаж по неделям

Рис. 5.10. Вкладка Тип диалогового окна Линия тренда

Рис. 5.11. Вкладка Параметры диалогового окна Линия тренда


	Шаг 0	Выделите диапазон, на основе которого будет строиться диаграмма (рис. 5.3). Этот диапазон вводится в аргумент source. В данном примере аргументу source присваивается значение Range ("A1:B11") .


	Шаг 4	Нажмите кнопку ОК. Линия тренда построена на диаграмме (рис. 5.12). Программно линия тренда строится с помощью следующих инструкций: ActiveSheet . ChartObjects ( "Диагр.1").Activate ActiveChart . ChartArea . Select ActiveChart . SeriesCollection ( 1 ) . Trendlines . Add (Type : =xlLinear , Forward :=0, Backward :=0, DisplayEquation:=True, DisplayRSquared:=True) .Select