Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как создать биржевую диаграмму в Excel?

11.9 Работа с диаграммами: объект Chart

Одно из основных применений Excel — анализ данных. А для анализа данных часто удобно использовать графики с их особыми свойствами, такими как тренды. На практике очень часто возникает задача автоматизации создания множества однотипных графиков (обычно на основе информации, извлеченной из базы данных).

Существует некоторая двусмысленность в терминологии, используемой в диаграммах Excel. То, что в русском графическом интерфейсе Excel называется диаграммой (меню Вставка -> Диаграмма), в английском языке называется Chart, и ему соответствует объект Chart. Аналогично, объектная модель Excel содержит объект Diagram, но он представляет собой диаграмму отношений (которую можно добавить в русский интерфейс Excel, выбрав в меню Insert->Diagram). Точно так же создатели русского Excel — диаграммы — будут понимать диаграмму в этом разделе.

Для создания диаграмм в Excel используется объект Chart.

Предпочтительнее сначала объявить этот объект:

Dim oChart как График

Следующий шаг — создание диаграммы. Диаграмма создается путем вызова метода Add() коллекции Charts — прием, который мы уже использовали много раз:

Set oChart = ActiveWorkbook.Charts.Add(, ActiveSheet)

Поскольку никакие свойства не определены, диаграмма выглядит как пустой лист, поскольку она была создана в принципе. Чтобы она обрела содержание, необходимо выполнить еще несколько действий:

Первым (и единственным обязательным) действием является определение источника данных графика, для которого предназначен метод SetSourceData(). Источником может быть только объект Range (он передается в качестве первого и единственного обязательного параметра для этого метода). Второй параметр (необязательный) определяет порядок, в котором должны быть прочитаны данные — сначала по столбцам или сначала по строкам. Например, в нашем случае это может выглядеть следующим образом:

В принципе, если запустить созданный код на выполнение, то диаграмма уже будет создана. Для всех остальных параметров будут приняты значения по умолчанию. Однако на практике нужно определить еще хотя бы тип диаграммы (по умолчанию она будет выглядеть как "обычная гистограмма", то есть ряд из столбиков разной длины). Для этой цели используется свойство ChartType, для которой разработчиками предусмотрено целых 73 значения. Например, чтобы преобразовать диаграмму в обычный график, можно использовать код вида:

Другой очень распространенной задачей является добавление дополнительных рядов на диаграмму. Для этого необходимо создать и сослаться на объект Series — ряд, а затем установить свойство Values для ряда (оно передается в объект Range в качестве значения):

Dim oSeries As Series

Set oSeries = oChart.SeriesCollection.NewSeries

Пользователи часто говорят, что им нужно создавать диаграммы не на отдельном листе, а на том же листе, что и данные. По умолчанию график создается в оперативной памяти и размещается на отдельном листе. Если вы хотите разместить его на существующем листе, его необходимо сначала создать на отдельном листе, а затем переместить с помощью метода Place. Отдельный лист, созданный для диаграммы, автоматически исчезнет:

Читайте так же:
Как скрыть общую сумму в сводной таблице Excel?

oChart.Location xlLocationAsObject, "Лист1"

Обратите внимание, что метод Location принимает в качестве первого параметра одну из констант (xlLocationAsNewSheet – переместить на специально создаваемый новый лист, xlLocationAsObject – переместить на объект, то есть лист), а в качестве второго – не объект листа, как можно было бы подумать, а обязательно его имя. Если код предполагается использовать и в русской, и в английской версии Excel, то предпочтительнее получить имя листа программным образом.

Большая неприятность, связанная с методом Location, заключается в том, что после перемещения диаграммы внутрь листа объектная ссылка на эту диаграмму теряется, и надо находить объект этой диаграммы заново. При попытке повторного обращения к объекту Chart выдается сообщение "Automation Error". Лучше всего поэтому вызов метода Location помещать в самый конец кода, посвященного диаграмме. В противном случае нам придется разыскивать созданную нами диаграмму и заново получать на нее объектную ссылку, например так:

Дим оСерия Яко Серия

Set oSeries = Worksheets(1).ChartObjects(1).Chart.SeriesCollection.NewSeries

Удобств, конечно, меньше.

Остальные параметры графа настраиваются через свойства и методы объектов графа:

  • свойство ChartArea — это свойство возвращает одноименный объект ChartArea, который представляет собой область, занимаемую диаграммой и используется для настройки внешнего вида диаграммы (свойства Font, Interior и т.п.). Если необходимо настроить внешний вид не всей диаграммы, а той ее части, которая используется непосредственно для вывода графика, используется схожее свойство PlotArea. По умолчанию диаграмма размещается прямо по центру листа. Если необходимо ее переместить в точно определенное место листа, используются знакомые на свойства Top, Height, Left и Width для объекта ChartArea.
  • свойство ChartTitle возвращает одноименный объект, при помощи которого можно настроить заголовок диаграммы (с такими свойствами, как Text, Font, Border и т.п.);
  • ChartType — важнейшее свойство, про которое мы уже говорили. Определяет тип диаграммы;
  • HasDataTable — если установить это свойство в True, то в нижней части диаграммы (по умолчанию) появится таблица с числами, на основе которых была создана диаграмма. Одновременно будет создан программный объект DataTable, при помощи которого можно настроить представление этой таблицы. Схожим образом действуют свойства HasLegend, HasPivotFields и HasTitle.
  • Name — это свойство позволяет настроить имя диаграммы (как название вкладки в Excel). По умолчанию диаграммы называются последовательно "Диаграмма1", "Диаграмма2" и т.п.
  • SizeWithWindow — если поставить значение этого свойства в True (по умолчанию False), то размер диаграммы будет подогнан таким образом, чтобы точно соответствовать размеру листа.
  • Tab — свойство, о котором мало кто подозревает. Оно позволяет настроить при помощи одноименного объекта внешний вид вкладки в книге Excel для диаграммы (или просто листа). Например, чтобы пометить вкладку зеленым, можно воспользоваться кодом
Читайте так же:
Как сжать пустые строки / столбцы в Excel?

oChart.Tab.Color = RGB(0, 255, 0)

  • Visible — возможность спрятать диаграмму без ее удаления.

Остальные свойства в основном касаются конфигурации отображения 3D-диаграмм и защиты диаграммы от модификации пользователем.

Теперь о наиболее важных методах объекта Chart:

  • метод Activate() используется очень часто. Он позволяет сделать диаграмму активной (то есть просто перейти на нее);
  • метод ApplyCustomType() позволяет поместить создать диаграмму своего собственно пользовательского типа (для этого необходимо вначале создать шаблон для этого типа и поместить его в галерею);
  • метод ApplyDataLabels() позволяет поместить на диаграмму метки для размещенных на ней данных. Этот метод принимает множество параметров, которые позволяют настроить отображение данных меток (показывать или не показывать значения и т.п.);
  • метод Axes() возвращает объект, представляющий оси диаграммы. Затем этот объект можно использовать для настройки данных осей;
  • ChartWizard() — этот метод позволяет быстро переформатировать диаграмму, как будто бы прошли на графическом экране при помощи мастера построения диаграмм и передали ему значения. Позволяет при помощи одной строки кода добиться того, что другими способами потребовало бы минимум несколько строк;
  • Copy() — позволяет скопировать диаграмму в другое место книги (например, чтобы создать новую диаграмму, использовав в качестве основы существующую). Для переноса существующей диаграммы в другое место можно воспользоваться методами Location() или Move().
  • CopyPicture() — замечательный метод, который позволяет поместить диаграмму в буфер обмена как изображение. Затем это изображение можно вставить, например, в документ Word или в любое другое место. Другой вариант — воспользоваться методом Export(), который позволяет создать рисунок, представляющий диаграмму, в виде файла на диске.
  • Delete() — просто удаляет диаграмму;
  • Evaluate() — как обычно, этот метод позволяет найти нужную диаграмму в книге по ее имени;
  • PrintOut() — возможность отправить диаграмму на печать. Этот метод принимает множество параметров, которые позволяют настроить такой вывод;
  • Refresh() — возможность обновить диаграмму, если изменились данные, на основе которых она строилась;
  • Select() — возможность выделить диаграмму (равносильно щелчку по ней мышью). Deselect() — снятие выделения (равносильно нажатию на <Esc>);
  • SetBackgroundPicture() — возможность "подложить" под диаграмму фоновый рисунок. Конечно, он должен быть не очень ярким;
  • SetSourceData() — важнейший метод, который позволяет определить данные, на основе которых строится диаграмма. Про него мы уже говорили.
Читайте так же:
Как скрыть адреса гиперссылок в Excel?

Для объекта Chart предусмотрено также события "на все случаи жизни" — реакция на щелчки мышью, на выделение/снятие выделения, активизацию, пересчет данных, изменение размера и т.п., однако используются такие события на практике нечасто.

Как сделать диаграмму в Excel?

Excel — отличный инструмент из пакета Microsoft Office для создания и манипулирования табличными данными различной сложности. В некоторых случаях табличного представления данных недостаточно для интерпретации закономерностей и взаимосвязей в числовых таблицах. Особенно если они содержат десятки или даже сотни рядов. В этом случае на помощь приходят диаграммы, которые очень легко и удобно создаются в Excel.

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

исходные значения для построения диаграммы исходные значения для построения диаграммы
выбор группы «Диаграммы» из вкладки «Вставка» выбор группы «Диаграммы» из вкладки «Вставка»

  • В гистограмме категории данных обычно располагаются на горизонтальной оси, а значения — на вертикальной. В объемных гистограммах категории данных отображаются на горизонтальной оси и оси глубины, а вертикальная ось показывает значение данных.
  • На графиках, отображающих изменения данных во времени, категории данных отображаются на горизонтальной оси, а значения — на вертикальной.
  • Круговые диаграммы показывают только один набор данных, поэтому они строятся по простейшему принципу: вклад каждого сектора в пирог зависит от значения каждой группы данных в целом.
  • На линейных диаграммах категории данных располагаются на вертикальной оси, а их значения — на горизонтальной.
  • Точечные диаграммы изначально не различают типы информации, размещенной на вертикальной и горизонтальной осях. Показывая взаимосвязь между числовыми значениями ряда данных, они игнорируют различия на осях. При необходимости их можно изменить, и диаграмма не потеряет своей информационной ценности.
  • Диаграммы обмена являются наиболее сложным типом диаграмм в соответствии с принципом создания информации. При построении диаграмм обмена учитываются взаимосвязи и закономерности при изменении нескольких значений.
  • Пузырьковые диаграммы используются, когда необходимо отобразить данные из электронной таблицы. Два столбца используются для размещения значений вдоль осей X и Y, а размер пузырьков зависит от числовых значений в соседних столбцах.

выбор типа диаграммы выбор типа диаграммы
итоговая диаграмма итоговая диаграмма

Как создать биржевую диаграмму в Excel?

В статье рассматриваются простые способы придать диаграммам более презентабельный вид.
По крайней мере, вы узнаете как избежать распространённых ошибок при их оформлении.

При представлении данных очень важно, говоря техническим языком, «уменьшить шум и выделить несущий сигнал». Начнем с негативного отношения к сеткам в таблицах. Даже на больших презентациях, где поначалу на них не обращаешь внимания, со временем они начинают раздражать. И это главная проблема шума: он отвлекает от того, что действительно важно.

Читайте так же:
Как создать календарь с выпадающим списком (выбор даты) в Excel?

Избавиться от сетки на графике — это элементарно. Если вы хотите отформатировать какой-либо элемент в Excel (в диаграмме или таблице), просто выделите его и нажмите Ctrl+1 (для Mac: Command+1): откроется диалог Формат для выбранных объектов.

В нашем случае щёлкаете одну из линий сетки на диаграмме (любую, но верхняя выделит всю область графика) и открываете диалоговое окно форматирования. Дальше выбираемЦвет линии > Нет линий (для Mac: Линия > Сплошная > Цвет: Без линии ).

2. Переместите легенду

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

Для этого откройте окно Format Settings (вы уже должны знать, как это сделать!) и выберите нужный элемент в Legend Settings (на Mac Location > нужный элемент).

Не отменяя выделения надписи, немедленно увеличьте размер шрифта до 12. Выделять сам текст не нужно, достаточно выделенного прямоугольника. Посмотрите сами, что вам больше подходит…

3. Удалите легенду с единственным рядом

4. Добавьте описание в название диаграммы

Распространенной ошибкой в информационных бюллетенях маркетологов является отсутствие четкого заголовка. Пока вы один собираете данные, совершенно ясно, что вы пытаетесь показать. Но для остальных все не так однозначно.

Например, в случае с приведенной ниже диаграммой написание «Выставки» в заголовке не будет информативным:

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

Мы считаем, что этот момент заслуживает особого внимания. Графики, созданные на основе несортированных данных, гораздо сложнее читать и интерпретировать.

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

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

6. Не заставляйте людей наклонять голову

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

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

Читайте так же:
Как сделать диаграмму накопительного среднего в Excel?

7. Приведите в порядок оси

Перед их внедрением давайте удалим линии сетки и легенду. Далее мы остановимся на пяти распространенных ошибках, которые встречаются в осях диаграмм.

Для групп чисел нет разделителей

Если данные содержат числа больше 999, необходимо включить разделитель групп чисел. Лучше всего форматировать данные непосредственно на графике. График будет обновлен автоматически. В противном случае флажок Ссылка на источник в настройках оси Формат должен быть снят.

Чтобы активировать групповой разделитель, выделите столбец, содержащий данные, и нажмите кнопку 000 в группе Число. Появится разделитель, и Excel по умолчанию добавит два десятичных знака.

Чтобы открыть диалоговое окно формата, нажмите на стрелку в правом нижнем углу группы Число.

Беспорядочные оси

На верхней диаграмме неумеренное количество ненужных подписей загромождает вертикальную ось. Это можно исправить, выбрав ось и вызвав диалоговое окно. В разделе Параметры оси установите переключатель Цена основной единицы измерения в положение Фиксированная (для Mac_: Формат оси > Масштаб > Цена основной единицы измерения_). На нижней диаграмме этот параметр был изменен с 20 000 на 40 000.

Если нужно более детальное разбиение, подберите шаг любым удобным способом.

Лишние десятичные знаки

Никогда не включайте десятичные дроби, если все числа являются целыми (другими словами, в них нет дробных частей). Чаще всего это встречается в денежном формате, где появляются такие подписи: $20,000.00, $30,000.00 и т. д. Это совершенно не нужно и только замедляет работу.

Десятичные дроби вместо процентов

Если на оси показаны проценты, форматируйте данные как проценты, не выводите их десятичными дробями. Чем меньше времени придётся тратить на интерпретацию данных, тем более привлекательной будет графика. И даже с процентами не забывайте убрать ненужные дроби. Иначе говоря, не делайте так: 10,00%, 20,00%… Преобразуйте к такому виду: 10%, 20%…

Вызывающий мурашки нулевой формат

Последний недостаток заключается в том, что в начале вертикальной оси вместо 0 вставляется линия. Это очень распространенное явление. В Интернете можно найти информацию о том, как работать с пользовательским форматированием чисел. Он охватывает некоторые интересные опции, такие как возможность добавления текста к форматированию с сохранением числовых значений ячейки.

В этом случае достаточно изменить формат на 0. Затем выделите столбец, из которого берутся данные, откройте диалоговое окно и на вкладке Число, в пункте (все форматы), найдите строку «свой», и уберите из нее дефис.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector