Есть 3 способа сделать это:
1. Определите имена строк непосредственно
Right-click на диаграмме и нажмите Select Data , затем отредактируйте названия серий непосредственно, как показано ниже.
Вы можете указать значения непосредственно, например. Series 1 или указать диапазон, например. =A2
2. Создайте диаграмму, определяющую верхние метки серии и оси
Просто выберите свой диапазон данных (в том же формате, что и я), и создайте простую гистограмму. Этикетки должны быть определены автоматически.
3. Определите легенду (названия серий) с помощью VBA
Аналогично вы можете определять имена серий динамически с помощью VBA. Простой пример ниже:
ActiveChart.ChartArea.Select ActiveChart.FullSeriesCollection(1).Name = "=""Hello"""
Это переопределит название first . Просто измените индекс с (1) на, например. (2) и т.д., чтобы изменить названия следующих серий. Что делает VBA выше? Он устанавливает имя серии Hello , поскольку "=""Hello""" переводит на ="Hello" (" должен быть экранирован предыдущим ").
Занятие 7.1
Табличный редактор Excel.
Диаграммы
Преимущества диаграмм. Как не оформляйте таблицу, ее данные будут менее читабельны, чем их графическое представление в диаграммах. Для примера посмотрите на рисунок:
По данным таблицы не сразу заметишь, в каком месяце были доходы фирмы наибольшие, а в каком наименьшие. Можно, конечно же, применить инструмент «Сортировка», но тогда теряется общее представление о сезонности деятельности фирмы. Здесь общее представление графика позволяет проследить сезонность активности продаж, которые приносят большую или меньшую прибыль в определенные периоды года. Данные записанные в таблицу прекрасно подходят для детальных расчетов и вычислений. Но диаграммы имеют такие преимущества:
· улучшают читабельность данных;
· упрощают общую ориентацию по большим объемам данных;
· позволяют создавать качественные презентации отчетов.
ПОСТРОЕНИЕ ДИАГРАММ В EXCEL
Для презентации достижений фирмы лучше использовать диаграммы. Диаграммы хорошо подходят для анализа относительных данных. Например, прогноз динамики увеличения продаж или оценка общей тенденции роста потенциала предприятия.
Для создания диаграммы выполните следующие шаги:
1. Диапазон ячеек A1:C4 заполните значениями так как показано на рисунке:
2. Выделите диапазон A1:C4 и выберите инструмент на закладке «Вставка»-«Гистограмма»-«Гистограмма с группировкой».
3. Щелкните по гистограмме чтобы активировать ее и вызвать дополнительное меню «Работа с диаграммами». Там же доступны три закладки инструментов: «Конструктор», «Макет», «Формат».
4. Чтобы поменять оси в диаграмме выберите закладку «Конструктор», а на ней инструмент-переключатель «Строка/столбец». Таким образом, вы меняете значения: строки на столбцы.
5. Щелкните по любой ячейке чтобы снять выделение с диаграммы и таким образом дезактивировать режим его настройки. Теперь можно работать в обычном режиме.
Теперь построим круговую диаграмму, которую нужно подписать заголовком:
1. Выделите в исходной таблице диапазон A1:B4.
2. Выберите «Вставка»-«Круговая». Из группы разных типов диаграмм выберите «Разрезная круговая».
3. Подпишите заголовок вашей диаграммы. Для этого сделайте по заголовку двойной щелчок левой кнопкой мышки и введите текст как показано на рисунке:
После подписи нового заголовка щелкните по любой ячейке, чтобы дезактивировать настройки диаграммы и перейти в обычный режим работы.
ИЗМЕНЕНИЕ ДИАГРАММЫ
Далеко не всегда удается сразу создать диаграмму в Excel соответствующий всем требованиям пользователя.
Изначально сложно определить в каком типе диаграмм лучше представить данные: в объемно разрезной диаграмме, в цилиндрической гистограмме с накоплением или графике с маркерами.
Иногда легенда больше мешает, чем помогает в представлении данных и лучше ее отключить. А иногда к диаграмме нужно подключить таблицу с данными для подготовки презентации в других программах (например, PowerPoint). Поэтому стоит научиться пользоваться настройками диаграмм в Excel.
Создайте табличку с данными как ниже на рисунке. Выделите таблицу с данными и выберите инструмент «Вставка»-«Гистограмма»-«Гистограмма с группировкой».
Эту диаграмму нужно отредактировать следующим образом:
· удалить легенду;
· добавить таблицу;
· изменить тип графика.
Удалить легенду с диаграммы
1. Щелкните левой кнопкой мышки по графику, чтобы активировать его (выделить) и выберите инструмент: «Работа с диаграммами»-«Макет»-«Легенда».
2. Из выпадающего списка опций инструмента «Легенда», укажите на опцию: «Нет (Не добавлять легенду)». И легенда удалится из графика.
Скорее всего, вас не устроит та диаграмма, которая генерируется по умолчанию. Модуль Microsoft Graph предоставляет широчайшие возможности форматирования. Вы можете выбирать тип диаграммы, изменять цвет рядов данных, добавлять заголовки, перемещать легенду, настраивать оси, их шкалы и метки делений, добавлять подписи данных и изменять множество дополнительных параметров, влияющих на способ представления данных. В этом упражнении на примере диаграммы, построенной на базе таблицы Word, будут рассмотрены лишь некоторые из доступных приемов форматирования.
1. Чтобы модифицировать диаграмму, нужно снова открыть модуль Microsoft Graph. Для этого дважды щелкните на диаграмме.
2. Щелчком на кнопке Режим таблицы скройте таблицу данных Graph. Сейчас мы не будем изменять данные. Таблица данных Graph похожа на лист Excel.
3. В панели инструментов Стандартная раскройте палитру кнопки типа диаграммы и выберите подходящий тип, например График .
Примечание Способы форматирования диаграмм Microsoft Graph похожи на приемы оформления диаграмм Excel.
Примечание Если вас не устраивают типы диаграмм, представленные в палитре кнопки панели инструментов, выберите команду Диаграмма > Тип диаграммы . Откроется окно диалога, первая вкладка которого предлагает на выбор больше сотни стандартных вариантов диаграмм, а вторая дает пользователю возможность сконструировать свой собственный тип диаграммы.
4. Легенда диаграммы расположена неудобно, давайте переместим ее вниз. Для этого щелкните на легенде правой кнопкой мыши, выберите в контекстном меню команду Формат легенды и на вкладке Размещение открывшегося окна диалога выберите положение переключателя Внизу , посередине. Затем щелкните на кнопке ОК .
5. Захватите мышью правый нижний маркер габаритного прямоугольника диаграммы и перетащите его вправо вниз, чтобы сделать диаграмму немного крупнее, как показано на рис. 7.19.
Теперь давайте приукрасим подписи осей, изменим сетку, на фоне которой выводится график, и увеличим маркеры данных, чтобы они были лучше видны.
6. Дважды щелкните на числах меток вертикальной оси графика. В открывшемся окне диалога разверните вкладку Шкала , показанную на рис. 7.20.
7. Сбросьте флажки Максимальное значение и Цена основных делений , чтобы отключить автоматическую настройку этих параметров.
8. В одноименные поля после флажков введите числа 80 и 20 соответственно.
9. Щелкните на кнопке ОК . Четыре другие вкладки окна диалога форматирования осей позволяют настраивать вид осей, шрифт меток делений, числовой формат меток и режим их выравнивания.
10. Чтобы убрать серый фон графика, выберите команду Диаграмма >, которая открывает окно диалога параметров диаграммы. Шесть вкладок этого окна содержат следующие параметры:
Рис. 7.19. Форматирование диаграммы в Word
* Заголовки
- названия диаграммы, вертикальных и горизонтальных осей;
* Оси
- режимы отображения осей категорий и значений;
* Линии сетки
- флажки включения и отключения вертикальных и горизонтальных линий сетки фона диаграммы;
* Легенда
- режим отображения и способ расположения легенды диаграммы;
* Подписи данных
- переключатель, дающий возможность выводить различные подписи рядом с маркерами данных;
* Таблица данных
- флажки настройки режима вывода таблицы отображаемых данных под горизонтальной осью диаграммы.
11. На вкладке Линии сетки , показанной на рис. 7.21, сбросьте флажок Основные линии в разделе Ось Y (значений).
12. Установите одноименный флажок в разделе Ось Х (категорий).
13. Щелчком на кнопке ОК закройте окно диалога. Горизонтальные линии сетки будут заменены на вертикальные.
14. Щелкните на области построения диаграммы. В меню Формат выберите первую команду. Ее название начинается со слова Выделенный , после которого следует название выделенного элемента. В данной ситуации это будет команда Выделенная область построения диаграммы .
Рис. 7.20. Форматирование осей диаграммы
15. В открывшемся окне диалога выберите положение Прозрачная переключателя Заливка , чтобы отменить заливку диаграммы. Затем щелкните на кнопке ОК .
Рис. 7.21. Параметры диаграммы
16. Чтобы выйти из режима редактирования диаграммы и закрыть модуль Microsoft Graph, щелкните на свободном пространстве документа Word. Теперь таблица и график будут выглядеть так, как показано на рис. 7.22.
17. Сохраните документ, он пригодится в контрольном упражнении.
Рис. 7.22. Окончательный вид диаграммы в Word
Последовательность действийПоследовательность действий
Настройка легенды диаграммы
Можно настроить легенду следующим образом:
Можно скрывать или показывать легенду, а также изменять ее положение относительно диаграммы, ее области или с помощью специального выражения отчета. Например, на гистограмме, показывающей доход по каждой товарной линии по странам, можно использовать выражение [Страна] = "Канада" для расположения легенды относительно столбца для Канады.
Можно изменить заголовок, отображающийся над легендой.
Если легенда включает слишком длинные элементы, их можно сократить до определенного количества символов.
Например, если требуется, чтобы на конце каждого усеченного элемента легенды отображалось многоточие (...), следует ввести ... в поле Усеченный текст .
Последовательность действий
Нажмите Дополнительно и нажмите кнопку многоточие (...).
Для отображения легенды на установленном расстоянии от сторон области диаграммы из списка Привязка выберите Относительно диаграммы .
Для отображения легенды на установленном расстоянии от сторон основной области диаграммы из списка Привязка выберите Относительно основной области диаграммы .
Для отображения легенды на установленном расстоянии от выражения отчета из списка Привязка выберите Выражение отчета , нажмите кнопку многоточие (...) рядом с пунктом Выражение и введите выражение в диалоговом окне Выражение отчета .
Для привязки задайте горизонтальное и вертикальное расстояния.
Выберите объект легенды.
В области Свойства щелкните Усеченный текст .
Чтобы указать количество символов, при котором производится усечение элементов легенды, нажмите Вручную и введите количество символов в поле Максимальное число знаков .
Чтобы подогнать шрифт текста элемента легенды, чтобы текст помещался в легенду, установите флажок Подогнать шрифт по месту .
Чтобы указать текст, который нужно показывать в конце обрезанных элементов легенды, введите его в поле Усеченный текст .
В области Свойства щелкните Легенда .
Чтобы показать легенду на диаграмме, установите флажок Показать легенду .
Чтобы выбрать предустановленное расположение легенды, щелкните Предустановка и щелкните расположение на диаграмме.
Примечание: При выборе предварительно установленного положения легенды она располагается во внутренних границах объекта диаграммы. При других положениях легенды она располагается за пределами внутренних границ.
Чтобы выбрать пользовательское расположение легенды, выполните следующие действия:
Для изменения текста, отображающегося в заголовке легенды, выберите объект легенды и в области Свойства щелкните Заголовок легенды и укажите заголовок.
Для усечения элементов в легенде выполните следующие действия:
Добавление примечания к диаграмме
Добавьте примечание к диаграмме для указания дополнительных сведений. Примечания появляются в виде текста на диаграмме.
По умолчанию примечания выравниваются по левому верхнему углу объекта диаграммы. Можно изменять положение примечания относительно объекта диаграммы или ее области или с помощью выражения отчета. Например, на гистограмме, на которой показан доход по линейкам продуктов по странам, можно ввести [Страна] = "Канада", чтобы расположить примечание относительно полоы Канада.
Примечания заменяют любые располагающиеся под ними элементы, поэтому располагайте их в правильном месте.
Если применяется несколько примечаний, убедитесь, что у каждого примечания свое место в отчете, чтобы они не перекрывали друг друга. Кроме того, можно указывать последовательность их отображения при выполнении отчета. При наличии двух примечаний с одинаковыми координатами первой отображается та, которая первой приведена в списке, затем сверху первой отображается следующая.
Последовательность действий
Добавление к диаграмме базовой линии
Базовые линии - это горизонтальные или вертикальные линии, которые пересекают диаграмму, указывая основное разделение данных. Например, можно добавить базовую линию, чтобы показать торговые квоты или порог рентабельности.
Каждая базовая линия представляет значение на оси.
В зависимости от типа диаграммы можно использовать следующие параметры для расположения базовой линии.
| Параметр | Описание |
Числовое значение | Используется постоянное числовое значение. |
Среднее | Используется статистическое среднее, плюс или минус значение стандартных отклонений, на основе всех изображенных на диаграмме значений данных на указанной оси. |
Процентиль (%) | Используется указанный процентиль. |
Процентное отношение на осевой линии (%) | Использует процент полного диапазона шкалы. Например, если диапазон шкалы от -500 до 1100, значение Процентное отношение вдоль осевой линии (%) 25 размещает базовую линию на уровне значения -100 (25% диапазона, то есть, 1600). |
Если применяется несколько базовых линий, можно указать последовательность их отображения при выполнении отчета. Они отображаются сверху вниз. При наличии двух базовых линий с одинаковыми координатами, первой отображается та, которая первой приведена в списке, затем сверху первой отображается следующая.
По умолчанию базовая линия и ее подпись появляются в легенде.
Базовые линии не поддерживают прозрачность цвета.
Последовательность действий
Отображение линий тренда на диаграммах, используемых по умолчанию
Линии тренда, также известные как линии наилучшего приближения или линии регрессии, графически иллюстрируют тренды в последовательности данных и обычно используются для построения графиков прогнозов. Линия тренда обычно представляет собой линию или кривую, соединяющую или проходящую через несколько точек в последовательности, представляющую тренд.
Можно показывать линии тренда на гистограммах, линейных и пузырьковых диаграммах, а также на диаграммах с областями и диаграммах рассеивания.
Можно использовать следующие виды линий тренда:
- Полиномиальная
Используйте полиномиальную линию тренда, если значения данных попеременно возрастают и убывают. Например, если на диаграмме показано как увеличение, так и уменьшение дохода по линейкам продуктов во времени, вам может подойти полиномиальная линия тренда.
- Логарифмическая
Используйте логарифмическую линию тренда, если значения данных сначала быстро растут или убывают, а затем постепенно стабилизируются. Например, если на диаграмме показано резкое уменьшение дохода по линейкам продуктов во времени, а затем его стабилизация, вам может подойти логарифмическая линия тренда.
- Скользящее среднее (за период)
Используйте линию тренда скользящего среднего, если есть колебания значений данных и необходимо сгладить исключения, чтобы посмотреть тренд. Например, если на диаграмме видны сильные колебания значений дохода по линейкам продуктов во времени, но известно, что некоторые точки являются исключениями, вам может подойти линия тренда скользящего среднего.
Линейная
Используйте линейную линию тренда, если значения данных увеличиваются или уменьшаются вдоль прямой линии с постоянной скоростью. Например, если на диаграмме показано постоянное увеличение дохода по линейкам продуктов во времени, вам может подойти линейная линия тренда.
Если вы не знаете, какой вид линии тренда следует использовать, попробуйте их все, чтобы найти линию, наилучшим образом приближающую точки ваших данных. Например, линейная линия тренда не подойдет для большинства точек на диаграмме рассеивания с широким распределением точек данных.
Линии тенденции не поддерживают прозрачность цвета.
Последовательность действий
Отображение значений данных и подписей в диаграмме
Можно отобразить подписи или значения данных внутри сегментов или точек круговой, кольцевой диаграммы. Например, в круговой диаграмме в каждом сегменте отражаются значения данных, что позволяет иметь точное представление о размере каждого сегмента круга.
Последовательность действий
При выборе Скрыть значения данных не показываются.
При выборе Абсолютные отображаются абсолютные значения данных.
При выборе Процентные показывается доля сектора от всей круговой диаграммы.
Абсолютное и процентное показывает процент сегмента круга от всего круга как абсолютное значение.
В области Свойства щелкните Подписи данных .
Для отображения подписей данных для каждого сегмента в разделе Отобразить установите флажок Названия сегментов .
Чтобы показать линии, соединяющие подписи данных и сектора круговой диаграммы, к которым они относятся, установите флажок Показать выносные линии .
Для указания формата подписи данных, щелкните Скрыть , Абсолютное , Процентное или Абсолютное и процентное в списке Значения .
Для указания размещения подписей данных в списке Положение выберите Вокруг всего круга или С выравниванием слева и справа от круга .
Последовательность действий
Режим Нет указывает, что подписи отображаются в положении по умолчанию и могут перекрываться.
Режим Обычный указывает, что подписи располагаются непосредственно над соответствующими маркерами данных или объектами диаграммы. Наложение отсутствует и подписи могут перекрываться.
Режим Грубый шахматный порядок указывает, что подписи располагаются уступами рядом с маркерами данных, вследствие чего они не перекрываются. Данный метод конфликта требует меньше времени для отображения, чем режим Точный шахматный порядок , однако может получиться так, что подписи находятся далеко от соответствующих маркеров данных.
Режим Точный шахматный порядок указывает, что подписи размещены так, что перекрытие отсутствует. Подписи находятся в возможной близости к маркерам данных без перекрытия. Данный метод конфликта требует больше времени для отображения, чем режим Грубый шахматный порядок , однако позволяет разместить подписи на более близком расстоянии от соответствующих маркеров данных.
В области Свойства щелкните Подписи данных .
Чтобы показать линии, соединяющие подписи и маркеры данных диаграммы, к которым они относятся, установите флажок Выносные линии .
Для указания вида отображения подписей, положение которых в диаграмме перекрывается, в списке Метод конфликта выберите один из следующих режимов:
Далеко не всегда удается сразу создать график и диаграмму в Excel соответствующий всем требованиям пользователя.
Изначально сложно определить в каком типе графиков и диаграмм лучше представить данные: в объемно разрезной диаграмме, в цилиндрической гистограмме с накоплением или графике с маркерами.
Иногда легенда больше мешает, чем помогает в представлении данных и лучше ее отключить. А иногда к графику нужно подключить таблицу с данными для подготовки презентации в других программах (например, PowerPoint). Поэтому стоит научиться пользоваться настройками графиков и диаграмм в Excel.
Изменение графиков и диаграмм
Создайте табличку с данными как ниже на рисунке. Вы уже знаете, как построить график в Excel по данным. Выделите таблицу с данными и выберите инструмент «Вставка»-«Гистограмма»-«Гистограмма с группировкой».
Получился график, который нужно отредактировать:
- удалить легенду;
- добавить таблицу;
- изменить тип графика.
Легенда графика в Excel
Можно легенду добавить на график. Для решения данной задачи выполняем следующую последовательность действий:
- Щелкните левой кнопкой мышки по графику, чтобы активировать его (выделить) и выберите инструмент: «Работа с диаграммами»-«Макет»-«Легенда».
- Из выпадающего списка опций инструмента «Легенда», укажите на опцию: «Нет (Не добавлять легенду)». И легенда удалится из графика.
Таблица на графике
Теперь нужно добавить в график таблицу:
- Активируйте график щелкнув по нему и выберите инструмент «Работа с диаграммами»-«Макет»-«Таблица данных».
- Из выпадающего списка опций инструмента «Таблица данных», укажите на опцию: «Показывать таблицу данных».
Типы графиков в Excel
- Выберите инструмент «Работа с диаграммами»-«Конструктор»-«Изменить тип диаграммы».
- В появившимся диалоговом окне «Изменение типа диаграммы» укажите в левой колонке названия групп типов графиков - «С областями», а в правом отделе окна выберите – «С областями и накоплением».
Для полного завершения нужно еще подписать оси на графике Excel. Для этого выберите инструмент: «Работа с диаграммами»-«Макет»-«Название осей»-«Название основной вертикальной оси»-«Вертикальное название».
Возле вертикальной оси появилось место для ее заголовка. Чтобы изменить текст заголовка вертикальной оси, сделайте по нему двойной щелчок левой кнопкой мышки и введите свой текст.
Удалите график, чтобы перейти к следующему заданию. Для этого активируйте его и нажмите клавишу на клавиатуре – DELETE.
Как изменить цвет графика в Excel?
На основе исходной таблицы снова создайте график: «Вставка»-«Гистограмма»-«Гистограмма с группировкой».
Теперь наша задача изменить заливку первой колонки на градиентную:
Для вас теперь доступны инструменты для сложного оформления градиентной заливки на графике:
- название заготовки;
- направление;
- угол;
- точки градиента;
- цвет;
- яркость;
- прозрачность.
Поэкспериментируйте с этими настройками, а после чего нажмите «Закрыть». Обратите внимание в «Название заготовки» доступны уже готовые шаблоны: пламя, океан, золото и др.
Как изменить данные в графике Excel?
График в Excel не является статической картинкой. Между графиком и данными существует постоянная связь. При изменении данных «картинка» динамически приспосабливается к изменениям и, таким образом, отображает актуальные показатели.
Динамическую связь графика с данными продемонстрируем на готовом примере. Измените значения в ячейках диапазона B2:C4 исходной таблицы и вы увидите, что показатели автоматически перерисовываются. Все показатели автоматически обновляются. Это очень удобно. Нет необходимости заново создавать гистограмму.
